Table Creation and Data Manipulation Commands

Checkpoint 14.1

Question 1

Which command is used for creating tables?

Answer

In SQL, CREATE TABLE command is used for creating tables in database.

Question 2

What is a constraint? Name some constraints that you can apply to enhance database integrity.

Answer

A constraint is a condition or check applicable on a field or set of fields.
Some constraints that we can apply to enhance database integrity are:

Unique constraint.
Primary key constraint.
Default constraint.
Check constraint.
Foreign key constraint.

Question 3

What is the role of UNIQUE constraint? How is PRIMARY KEY constraint different from UNIQUE constraint?

Answer

UNIQUE constraint ensures that no two rows have the same value in the specified column(s).
There are differences between UNIQUE and PRIMARY KEY constraints. Though both ensure unique values for each row in a column, but UNIQUE allows NULL values whereas PRIMARY KEY does not. Also, there can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 4

What is primary key? What is PRIMARY KEY constraint?

Answer

A primary key is a unique identifier for each record in a table, and it must be unique and not null. A PRIMARY KEY constraint declares a column or one group of columns as the primary key of the table. This constraint must be applied to columns declared as NOT NULL.

Question 5

What is NOT NULL constraint? What is DEFAULT constraint?

Answer

The NOT NULL constraint is used in SQL to ensure that a column cannot contain NULL (i.e., empty) values.

A DEFAULT constraint is used in SQL to specify a default value for a column in a table. When the user does not enter a value for the column (having default value), automatically the defined default value is inserted in the field.

Question 6

When a column's value is skipped in an INSERT command, which value is inserted in the database?

Answer

The columns that are not listed in the INSERT command will have their default value, if it is defined for them, otherwise, NULL value. If any other column (that does not have a default value and is defined NOT NULL) is skipped or omitted, an error message is generated and the row is not added.

Question 7

Can a column defined with NOT NULL constraint, be skipped in an INSERT command?

Answer

If a column defined with a NOT NULL constraint is skipped in an INSERT command, it will result in an error, and the row will not be added to the table.

Question 8

How would you view the structure of table Dept?

Answer

To view the structure of a table in SQL, we use DESC[RIBE] command of MySQL * Plus. The syntax of this command is as follows: DESC[RIBE] <tablename> ;.

For example, the command to view the structure of table Dept is DESCRIBE Dept; or DESC Dept ;.

Question 9

Table Empl has same structure as that of table EMPL. Write a query statement to insert data from table NewEmpl into EMPL where salary and comm is more than Rs. 4000.

Answer

INSERT INTO Empl

SELECT *

FROM NewEmpl

WHERE SAL > 4000 AND COMM > 4000;

Question 10

What is the error in following statement?

UPDATE EMPL;

Answer

The error in the statement UPDATE EMPL; is that it is incomplete. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. Therefore, the statement is incomplete as it lacks both the SET and WHERE clauses.

Question 11

Identify the error:

DELETE ALL FROM TABLE EMPL;

Answer

The statement DELETE ALL FROM TABLE EMPL; is in error due to the misuse of the keyword ALL and the unnecessary inclusion of TABLE before the table name. In SQL, the syntax of DELETE statement is:

DELETE FROM<TABLENAME>

[WHERE <PREDICATE>];

According to this syntax, the correct command to remove all the contents of EMPL table is:

DELETE FROM EMPL;

Question 12

Differentiate between DDL and DML.

Answer

Data Definition Language (DDL)	Data Manipulation Language (DML)
DDL provides a set of definitions to specify the storage structure and access methods used by the database system.	DML is a language that enables users to access or manipulate data as organized by the appropriate data model.
DDL commands are used to perform tasks such as creating, altering, and dropping schema objects. They are also used to grant and revoke privileges and roles, as well as for maintenance commands related to tables.	DML commands are used to retrieve, insert, delete, and modify data stored in the database.
Examples of DDL commands are CREATE, ALTER, DROP, GRANT, ANALYZE etc.	Examples of DML commands are INSERT, UPDATE, DELETE, SELECT etc.

Multiple Choice Questions

Question 1

Consider the following SQL statement. What type of statement is this?

CREATE TABLE employee (name VARCHAR, id INTEGER)

DML
DDL
DCL
Integrity constraint

Answer

DDL

Reason — The SQL statement CREATE TABLE employee (name VARCHAR, id INTEGER) is a Data Definition Language (DDL) statement. DDL statements are used to define, modify, and delete database objects such as tables, views, indexes, etc. In this case, the statement is creating a new table named employee with columns, name of type VARCHAR and id of type INTEGER.

Question 2

The data types CHAR(n) and VARCHAR(n) are used to create ..............., and ............... length types of string/text fields in a database.

Fixed, equal
Equal, variable
Fixed, variable
Variable, equal

Answer

Fixed, variable

Reason — CHAR datatype specifies a fixed length string. Defining a length is not required, but the default is 1. While VARCHAR datatype specifies a variable length string. Defining a length is required.

Question 3

A table Table1 has two text fields defined as below:

Name1 varchar(20),

Name2 char(20),

If Name1 stores value as 'Ana' and Name2 stores value as 'Anuj', then Name1 will consume .............. characters' space and Name2 will consume ............... characters' space.

3, 20
20, 4
20, 20
3, 4

Answer

3, 20

Reason — For the field Name1 with VARCHAR(20) datatype, storing the value 'Ana' will consume 3 character's space because VARCHAR(n) column can have a maximum size of n bytes and it stores values exactly as specified without adding blanks for shorter lengths. Exceeding n bytes results in an error message. Whereas for the field Name2 with CHAR(20) datatype, storing the value 'Anuj' will consume 20 characters' space because CHAR(n) ensures that all values stored in that column are of length n bytes, padding shorter values with blanks while maintaining a fixed size of n bytes.

Question 4

Consider the following SQL statement. What type of statement is this?

INSERT INTO instructor VALUES (10211, ‘Shreya’, 'Biology', 66000) ;

Procedure
DML
DCL
DDL

Answer

DML

Reason — The above SQL statement is Data Manipulation Language (DML) statement. DML statements are used to access and manipulate data in tables. The DML commands include SELECT, LOCK TABLE, UPDATE, INSERT INTO, DELETE. In this case, the INSERT INTO statement is used to insert a new row of data into the instructor table.

Question 5

In the given query which keyword has to be inserted?

INSERT INTO employee ............... (1002, Kausar, 2000);

Table
Values
Relation
Field

Answer

Values

Reason — The syntax of INSERT INTO command is :

INSERT INTO <tablename> [<column List>]

Values (<value>, <value> ...............) ;

According to this syntax, Values keyword is used to specify the values that will be inserted into the specified columns of the table.

Question 6

Which of the following is/are the DDL statements?

Create
Drop
Alter
All of these

Answer

All of these

Reason — DDL (Data Definition Language) commands are used to create and define tables and other database objects in SQL (Structured Query Language). DDL commands such as CREATE, ALTER, and DROP, are used to create, define, change and delete objects like tables, indexes, views, and constraints.

Question 7

In SQL, which command(s) is(are) used to change a table's structure / characteristics ?

ALTER TABLE
MODIFY TABLE
CHANGE TABLE
All of these

Answer

ALTER TABLE

Reason — The ALTER TABLE command in SQL is used to change the definitions of existing tables. It allows for various operations such as adding a new column, redefining a column, and adding an integrity constraint. Therefore, it changes the structure of the table.

Question 8

Which of the following commands will delete the table from MYSQL database?

DELETE TABLE
DROP TABLE
REMOVE TABLE
ALTER TABLE

Answer

DROP TABLE

Reason — The DROP TABLE command in SQL will delete the table from the MYSQL database. Once this command is executed, the table and all its associated data are removed from the database. After dropping the table, the table name is no longer recognized within the database system, and no further commands can be executed on that object.

Question 9

............... defines rules regarding the values allowed in columns and is the standard mechanism for enforcing database integrity.

Column
Constraint
Index
Trigger

Answer

Constraint

Reason — Constraint defines rules regarding the values allowed in columns and is the standard mechanism for enforcing database integrity. Once an integrity constraint is enabled, all data in the table must confirm to the rule that it specifies.

Question 10

Fill in the blank :

............... command is used to remove primary key from a table in SQL.

update
remove
alter
drop

Answer

alter

Reason — To remove a primary key constraint from a table the ALTER command is used. The DROP clause of ALTER TABLE command is used with syntax ALTER TABLE <TABLENAME> DROP PRIMARY KEY ;.

Question 11

Which command defines its columns, integrity constraint in create table:

Create table command
Drop table command
Alter table command
All of these

Answer

Create table command

Reason — The CREATE TABLE command is used to define a new table in SQL, and it allows to define the columns of the table along with any integrity constraints such as primary keys, foreign keys, unique constraints, etc.

Question 12

Which command is used for removing a table and all its data from the database:

Create table command
Drop table command
Alter table command
All of these

Answer

Drop table command

Reason — The DROP TABLE command is used to delete a table and all its data from the database. Once this command is given, the table name is no longer recognized and no more commands can be given on the object.

Question 13

Which of the following is not a DDL command?

UPDATE
TRUNCATE
ALTER
None of these

Answer

UPDATE

Reason — Data Definition Language (DDL) statements are used to define, modify, and delete database objects such as tables, views, indexes, etc. The DDL commands are CREATE, ALTER, TRUNCATE, DROP etc. But the UPDATE command is Data Manipulation Language (DML) command, used to modify existing data in a table.

Question 14

Which of the following is not a legal constraint for a CREATE TABLE command?

Primary key
Foreign key
Unique
Distinct

Answer

Distinct

Reason — The legal constraints for a CREATE TABLE command include the Primary key constraint, Foreign key constraint, Unique constraint, Check constraint, Default constraint. However, the Distinct is not a valid option for a CREATE TABLE command.

Fill in the Blanks

Question 1

A database can be opened with USE <database> command.

Question 2

To list the names of existing database, you can use SHOW DATABASES command.

Question 3

A constraint is a condition or check applicable on a field or a set of fields.

Question 4

The REFERENCES constraint creates a foreign key.

Question 5

Issue COMMIT command to make changes to a table permanent.

Question 6

To increase the size of a column in an existing table, use command ALTER TABLE.

Question 7

To remove table data as well table structure, use command DROP TABLE.

Question 8

To define a column as a primary key, primary key constraint is used in CREATE TABLE.

True/False Questions

Question 1

Constraints can be defined with CREATE TABLE command.

Answer

True

Reason — In SQL, the CREATE TABLE command is used to define a new table in SQL, and it allows to define the columns of the table along with any integrity constraints such as primary keys, foreign keys, unique constraints, etc.

Question 2

Constraints can only be defined with CREATE TABLE command.

Answer

False

Reason — Constraints can be defined with the CREATE TABLE command in SQL, but they can also be added or altered later using the ALTER TABLE command. The ALTER TABLE command allows to modify an existing table by adding, modifying, or dropping columns, as well as adding an integrity constraints.

Question 3

Unique and Primary key constraints are the same.

Answer

False

Reason — UNIQUE and PRIMARY KEY constraints are not the same and there are differences between them. Though both ensure unique values for each row in a column, but UNIQUE allows NULL values whereas PRIMARY KEY does not. Also, there can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 4

DELETE from <table> command is same as DROP TABLE command.

Answer

False

Reason — DELETE from <table> command is used to remove all the contents from the table, leaving it empty. On the other hand, the DROP TABLE command is used to delete the table from the database along with all its data and structure.

Question 5

Conditional updates in table data are possible through UPDATE command.

Answer

True

Reason — Conditional updates in table data are possible through the UPDATE command in SQL. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. This enables to perform updates selectively based on specific conditions defined in the WHERE clause.

Assertions and Reasons

Question 1

Assertion. The PRIMARY KEY and UNIQUE constraints are the same.

Reason. The columns with PRIMARY KEY or UNIQUE constraints have unique values for each row.

Answer

(d)

Assertion is false but Reason is true.

Explanation
UNIQUE and PRIMARY KEY constraints are not the same and there are differences between them. Though both ensure unique values for each row in a column, but UNIQUE allows NULL values whereas PRIMARY KEY does not. Also, there can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 2

Assertion. The treatment of NULL values is different with PRIMARY KEY and UNIQUE constraints.

Reason. The column(s) with PRIMARY KEY do not allow the NULL value even in a single row but UNIQUE constraint allows NULL for one of the rows.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The treatment of NULL values is different with PRIMARY KEY and UNIQUE constraints. The UNIQUE constraint allows NULL values for one of the rows, while the PRIMARY KEY does not allow the NULL value in any row.

Question 3

Assertion. There is no restriction on the number of columns that can have PRIMARY KEY constraint or UNIQUE constraints.

Reason. There can be multiple columns with UNIQUE constraint but PRIMARY KEY constraint can be defined only once for one or more columns.

Answer

(d)

Assertion is false but Reason is true.

Explanation
There can exist multiple columns with UNIQUE constraints in a table, but there can exist only one column or one combination with PRIMARY KEY constraint.

Question 4

Assertion. There are different commands for creating and changing table design.

Reason. The CREATE TABLE command creates the tables while ALTER TABLE command changes the design of an existing table.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The CREATE TABLE command is used to create tables in a database, specifying the table's structure, including column names, data types, and constraints. Conversely, the ALTER TABLE command is used to modify the structure of an existing table, such as adding, removing, or modifying columns, constraints, or indexes.

Question 5

Assertion. Both DELETE and DROP TABLE carry out the same thing — deletion in tables.

Reason. The DELETE command deletes the rows and DROP TABLE deletes the whole table.

Answer

(d)

Assertion is false but Reason is true.

Explanation
The DELETE command removes rows from a table while leaving the table structure intact. It does not delete the entire table, instead, it removes specific rows within it. On the other hand, the DROP TABLE command in SQL deletes a table from the database, including its structure and all its data. So, while both commands involve deletion, they operate on different levels: the DELETE command removes rows, while the DROP TABLE command removes the entire table.

Question 6

Assertion. Both UPDATE and ALTER TABLE commands are similar.

Reason. The UPDATE command as well ALTER TABLE command make changes in the table.

Answer

(e)

Both Assertion and Reason are false.

Explanation
The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. On the other hand, the ALTER TABLE command is used to change the definitions of existing tables. It can add columns, integrity constraints, and redefine columns. While both commands involve making changes, they operate on different aspects of the table - UPDATE command modifies data, while ALTER TABLE command modifies the table structure.

Type A: Short Answer Questions/Conceptual Questions

Question 1

What are different divisions of SQL and commands ? Give examples of commands in each division.

Answer

SQL commands can be divided into following categories :

Data Definition Language (DDL) commands — CREATE, ALTER, DROP, TRUNCATE etc.
Data Manipulation Language (DML) commands — INSERT, UPDATE, DELETE etc.
Transaction Control Language (TCL) commands — COMMIT, ROLLBACK, SAVEPOINT etc.
Session Control Commands
System Control Commands

Question 2

What is foreign key ? How do you define a foreign key in your table ?

Answer

A non-key attribute, whose values are derived from the primary key of some other table, is known as foreign key in its current table. Defining a foreign key in a table involves specifying the relationship between the tables and setting up rules for data integrity. When two tables are related by a common column or set of columns, the related column(s) in the parent table (or primary table) should be either declared as a primary key or unique key. Meanwhile, the related column(s) in the child table (or related table) should have a foreign key constraint referencing the primary or unique key in the parent table.

Question 3

How is FOREIGN KEY commands different from PRIMARY KEY command ?

Answer

The PRIMARY KEY is a set of one or more attributes that can uniquely identify tuples within the relation. A primary key column cannot contain NULL values, and it must have unique values for each row. Only one primary key constraint can exist per table. Conversely, the FOREIGN KEY command establishes a relationship between two tables by linking a column or set of columns in one table to the primary key or a unique key in another table. It enforces referential integrity, ensuring that values in the foreign key column(s) of the referencing table match values in the referenced table's primary key or unique key column(s). A foreign key can allow NULL values, indicating that the relationship is optional. Multiple foreign key constraints can exist in a table, each referencing a different parent table.

Question 4

How is FOREIGN KEY commands related to the PRIMARY KEY ?

Answer

FOREIGN KEY commands establish relationships between tables by linking columns in one table to the PRIMARY KEY or a unique key in another table. This linkage ensures referential integrity, meaning that values in the FOREIGN KEY column(s) of the referencing table must match values in the PRIMARY KEY or unique key column(s) of the referenced table. Therefore, FOREIGN KEY commands are directly related to PRIMARY KEY commands as they rely on the unique identification provided by PRIMARY KEY constraints in other tables.

Question 5

How do you enforce business rules on a database ?

Answer

Database constraints enforce business rules on a database. These include PRIMARY KEY for unique identifiers, FOREIGN KEY for maintaining relationships between tables, UNIQUE for ensuring uniqueness, CHECK constraint limit values that can be inserted into a column of a table, and default constraints are utilized to specify a default value for a column when no value is explicitly provided during an insert operation.

Question 6

What are table constraints ? What are column constraints ? How are these two different ?

Answer

Table constraints are rules or conditions applied to an entire table in a database. They are defined when creating or altering a table's schema.

Column constraints are rules or conditions applied to individual columns within a database table. They are specified at the column level when creating or altering a table's schema.

The difference between the two is that column constraints apply only to individual columns, whereas table constraints apply to groups of one or more columns.

Question 7

What is default value ? How do you define it ? What is the default value of column for which no default value is define ?

Answer

A default value is a predefined value assigned to a column in a database table. It can be specified using the DEFAULT clause when defining the table's schema. If no default value is defined for a column, and a new row is inserted into the table without providing a value for that column, the column's default value will be NULL, unless the column is defined with a NOT NULL constraint. In such cases, an error will occur if a value is not provided.

Question 8(i)

Differentiate between DROP TABLE, DROP DATABASE.

Answer

DROP TABLE	DROP DATABASE
This command is used to delete a specific table from the database along with all its data, indexes, triggers, and constraints.	This command is used to delete an entire database including all its tables, views, stored procedures, triggers, and other objects.
The syntax is : DROP TABLE table_name;.	The syntax is : DROP DATABASE database_name;.

Question 8(ii)

Differentiate between DROP TABLE, DROP clause of ALTER TABLE.

Answer

DROP TABLE	DROP clause of ALTER TABLE
This command is used to delete a specific table from the database along with all its data, indexes, triggers, and constraints.	This command is used to remove a specific component of a table, such as columns, constraints, or indexes.
The syntax is : DROP TABLE table_name;	The syntax is : ALTER TABLE table_name DROP COLUMN column_name;

Type B: Application Based Questions

Question 1

Insert all those records of table Accounts into table Pending where amt_outstanding is more than 10000.

Answer

INSERT INTO Pending

SELECT * FROM Accounts

WHERE amt_outstanding > 10000;

Question 2

Increase salary of employee records by 10% (table employee).

Answer

Table employee

ID	First_Name	Last_Name	User_ID	Salary
1	Dim	Joseph	Jdim	5000
2	Jaganath	Mishra	jnmishra	4000
3	Siddharth	Mishra	smishra	8000
4	Shankar	Giri	sgiri	7000
5	Gautam	Buddha	bgautam	2000

UPDATE employee

SET Salary = (Salary * 0.1) + Salary ;

Output

To view all the details (all columns and rows) of the "employee" table the below query is executed :

SELECT * FROM employee ;

+----+------------+-----------+----------+--------+

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
8369	SMITH	CLERK	8902	1990-12-18	800	NULL	20
8499	ANYA	SALESMAN	8698	1991-02-20	1600	300	30
8521	SETH	SALESMAN	8698	1991-02-22	1250	500	30
8566	MAHADEVAN	MANAGER	8839	1991-04-02	2985	NULL	20
8654	MOMIN	SALESMAN	8698	1991-09-28	1250	1400	30
8698	BINA	MANAGER	8839	1991-05-01	2850	NULL	30
8839	AMIR	PRESIDENT	NULL	1991-11-18	5000	NULL	10
8844	KULDEEP	SALESMAN	8698	1991-09-08	1500	0	30
8882	SHIAVNSH	MANAGER	8839	1991-06-09	2450	NULL	10
8886	ANOOP	CLERK	8888	1993-01-12	1100	NULL	20
8888	SCOTT	ANALYST	8566	1992-12-09	3000	NULL	20
8900	JATIN	CLERK	8698	1991-12-03	950	NULL	30
8902	FAKIR	ANALYST	8566	1991-12-03	3000	NULL	20
8934	MITA	CLERK	8882	1992-01-23	1300	NULL	10

+----+------------+-----------+----------+--------+

| 1 | Dim | Joseph | Jdim | 5500 |

+----+------------+-----------+----------+--------+

Question 3

Give commission of Rs.500 to all employees who joined in year 1982 (table Empl).

Answer

Table Empl

UPDATE Empl

SET COMM = 500

WHERE YEAR(HIREDATE) = 1982;

Explanation

Since there are no employees who joined in the year 1982 according to the data provided in the "Empl" table, executing the given SQL query will result in no changes to the "COMM" column.

Question 4

Allocate the department situated in BOSTON to employee with employee number 7500 (tables EMPL, Dept)

Answer

UPDATE EMPL

SET DEPTNO = (

SELECT DEPTNO

FROM Dept

WHERE LOC = 'BOSTON'

)

WHERE EMPNO = 7500;

Question 5

Given the following tables :

Orders (OrdNo, Ord_date, ProdNo#, Qty)

Product (ProdNo, Descp, Price)

Payment (OrdNo, Pment)

Write a query to delete all those records from table Orders whose complete payment has been made.

Answer

DELETE FROM Orders

WHERE OrdNo IN (

SELECT Payment.OrdNo

FROM Payment

WHERE Payment.Pment = 'COMPLETE');

Question 6

Enlist the names of all tables created by you.

Answer

SHOW TABLES ;

Question 7

Write Query statements for following transaction : (Consider tables of question 12)

Increase price of all products by 10%.
List the details of all orders whose payment is pending as per increased price.
Decrease prices by 10% for all those products for which orders were placed 10 months before.

Answer

The following tables are considered :

Orders (OrdNo, Ord_date, ProdNo#, Qty)

Product (ProdNo, Descp, Price)

Payment (OrdNo, Pment)

UPDATE product

SET price = (price * 0.1) + price ;

SELECT *

FROM Orders

JOIN Payment ON Orders.OrdNo = Payment.OrdNo

WHERE Payment.Pment = 'Pending';

UPDATE Product

SET Price = Price - (Price * 0.1)

WHERE ProdNo IN (

SELECT ProdNo#

FROM Orders

WHERE YEAR(Ord_date) = YEAR(CURDATE()) - 1

AND MONTH(Ord_date) = MONTH(CURDATE()) - 10

);

Question 8

Modify table Empl, add another column called Grade of VARCHAR type, size 1 into it.

Answer

ALTER TABLE Empl

ADD (Grade VARCHAR(1)) ;

Question 9

In the added column Grade, assign grades as follows :

if sal is in range 700 — 1500, Grade is 1 ;
if sal is in range 1500 — 2200, Grade is 2 ;
if sal is in range 2200 — 3000, Grade is 3 ;
if sal is in range 3000 — Grade is 4 ;

Answer

UPDATE Empl

SET Grade = '1'

WHERE Sal >= 700 AND Sal <= 1500;

UPDATE Empl

SET Grade = '2'

WHERE Sal > 1500 AND Sal <= 2200;

UPDATE Empl

SET Grade = '3'

WHERE Sal > 2200 AND Sal <= 3000;

UPDATE Empl

SET Grade = '4'

WHERE Sal > 3000;

Question 10

Add a constraint (NN-Grade) in table Empl that declares column Grade not null.

Answer

ALTER TABLE Empl

ADD CONSTRAINT NN_Grade

(Grade NOT NULL) ;

Question 11

Insert a record of your choice in table Empl. Make sure not to enter Grade.

Answer

INSERT INTO Empl (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

VALUES (12345, 'DEEPAK', 'CLERK', 8902, '1990-12-18', 8000, 200, 10);

Question 12

Modify the definition of column Grade. Increase its size to 2.

Answer

ALTER TABLE Empl

MODIFY (Grade VARCHAR(2)) ;

Question 13

Drop the table Empl.

Answer

DROP TABLE IF EXISTS Empl;

Column Name	ID	Name
Data Type	NUMBER	VARCHAR
Length	8	25

Question 14

Create the table Department table based on the following table instance chart.

Answer

CREATE TABLE Department (

ID NUMBER(8),

Name VARCHAR(25)

);

Question 15

Populate the table Department with data from table dept. Including only required columns.

Answer

INSERT INTO Department (ID, Name)

SELECT ID, Name

FROM dept ;

Question 16

Create the table Employee based on the following table instance chart.

Column Name	Data Type	Length
ID	NUMBER	8
First_Name	VARCHAR	25
Last_Name	VARCHAR	25
Dept_ID	NUMBER	8

Answer

CREATE TABLE Employee (

ID NUMBER(8),

First_Name VARCHAR(25),

Last_Name VARCHAR(25),

Dept_ID NUMBER(8)

);

Question 17

Drop table Employee and Department.

Answer

DROP TABLE IF EXISTS Employee ;

DROP TABLE IF EXISTS Department ;

Question 18

Create table Customer as per following Table Instance Chart.

Column Name	Data Type	Length
Cust_ID	NUMBER	7
Cust_Name	VARCHAR	30
Cust_Address1	VARCHAR	20
Cust_Address2	VARCHAR	30
Pincode	NUMBER	6
Cust_Phone	VARCHAR	10

Answer

CREATE TABLE Customer (

Cust_ID NUMBER(7),

Cust_Name VARCHAR(30),

Cust_Address1 VARCHAR(20),

Cust_Address2 VARCHAR(30),

Pincode NUMBER(6),

Cust_Phone VARCHAR(10)

);

Question 19

Add one column Email of data type VARCHAR and size 30 to the table Customer.

Answer

ALTER TABLE Customer

ADD (Email VARCHAR(30)) ;

Question 20

Add one more column CustomerIncomeGroup of datatype VARCHAR(10).

Answer

ALTER TABLE Customer

ADD (CustomerIncomeGroup VARCHAR(10));

Question 21

Insert few records with relevant information, in the table.

Answer

INSERT INTO Customer (Cust_ID, Cust_Name, Cust_Address1, Cust_Address2, Pincode, Cust_Phone, Email, CustomerIncomeGroup)

VALUES

(11, 'Amit', '1st Main Street', 'Mumbai', 12345, '5551234121', 'amit@gmail.com', 'High'),

(24, 'Vidya', '4th Main Street', 'Bangalore', 54321, '5234325678', 'vidya24@gmail.com', 'Medium'),

(39, 'Amruta', '78th Main Street', 'Goa', 98765, '5976539012', 'amruta78@gmail.com', 'Low');

Question 22

Drop the column CustomerIncomeGroup from table Customer.

Answer

ALTER TABLE Customer

DROP COLUMN CustomerIncomeGroup ;

Question 23

Create table Department as per following Table Instance Chart.

Column Name	DeptID	DeptName
Key Type	Primary
Nulls/Unique		NOT NULL
Datatype	NUMBER	VARCHAR
Length	2	20

Answer

CREATE TABLE Department (

DeptID NUMBER(2) PRIMARY KEY,

DeptName VARCHAR(20) NOT NULL

);

Question 24

Create table Employee as per following Table Instance Chart.

Column Name	EmpID	EmpName	EmpAddress	EmpPhone	EmpSal	DeptID
Key Type	Primary					Foreign
Nulls/Unique		NOT NULL
Fk Table						Department
Fk Column						Dept_ID
Datatype	NUMBER	VARCHAR	VARCHAR	VARCHAR	NUMBER	VARCHAR
Length	6	20	30	10	9, 2	2

Answer

CREATE TABLE Employee (

EmpID NUMBER(6) PRIMARY KEY,

EmpName VARCHAR(20) NOT NULL,

EmpAddress VARCHAR(30),

EmpPhone VARCHAR(10),

EmpSal NUMBER(9, 2),

DeptID VARCHAR(2),

FOREIGN KEY (DeptID) REFERENCES Department (Dept_ID)

ON DELETE CASCADE ON UPDATE CASCADE

);

Question 25

View structures of all tables created by you.

Answer

DESCRIBE <TABLENAME> ;

Multiple Choice Questions

Question 1

A ............... is a query that retrieves rows from more than one table or view:

Start
End
Join
All of these

Answer

Join

Reason — A join is a query that combines rows from two or more tables or views. In a join-query, more than one table are listed in FROM clause.

Question 2

The HAVING clause does which of the following ?

Acts EXACTLY like a WHERE clause.
Acts like a WHERE clause but is used for columns rather than groups.
Acts like a WHERE clause but is used for groups rather than rows.
Acts like a WHERE clause but is used for rows rather than columns.

Answer

Acts like a WHERE clause but is used for groups rather than rows.

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows.

Question 3

Aggregate functions can be used in the select list or the ............... clause of a select statement. They cannot be used in a ............... clause.

Where, having
Having, where
Group by, having
Group by, where

Answer

Having, where

Reason — Aggregate functions can be used in the select list or the HAVING clause of a select statement. But they cannot be used in a WHERE clause. The reason for this is that the WHERE clause filters rows before any grouping or aggregation occurs, while HAVING clause applies conditions to groups after the data has been grouped using the GROUP BY clause.

Question 4

SQL applies conditions on the groups through ............... clause after groups have been formed.

Group by
With
Where
Having

Answer

Having

Reason — The HAVING clause applies conditions to groups after the data has been grouped using the GROUP BY clause in SQL queries.

Question 5

Which clause is used with "aggregate functions" ?

GROUP BY
SELECT
WHERE
Both (1) and (3)

Answer

GROUP BY, SELECT

Reason — Aggregate functions are used with the GROUP BY clause to perform calculations on groups of rows. However, they are also used in the SELECT clause to display the results of aggregate calculations.

Question 6

What is the meaning of "HAVING" clause in SELECT query ?

To filter out the summary groups
To filter out the column groups
To filter out the row and column values
None of the mentioned

Answer

To filter out the summary groups

Reason — The HAVING clause is used to filter out summary groups in a SELECT query that involves aggregate functions and the GROUP BY clause.

Question 7

Where and Having clauses can be used interchangeably in SELECT queries ?

True
False
Only in views
With order by

Answer

False

Reason — The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on individual rows. While WHERE conditions cannot include aggregate functions, HAVING conditions can include aggregate functions. Hence, WHERE and HAVING clauses cannot be used interchangeably in SELECT queries.

Question 8

The operation whose result contains all pairs of tuples from the two relations, regardless of whether their attribute values match.

Join
Cartesian product
Intersection
Set difference

Answer

Cartesian product

Reason — In an unrestricted join or Cartesian product of two relations, all possible combinations are formed by pairing each row from the first table with every row from the second table, regardless of whether their attribute values match. This operation returns n1 * n2 rows, where n1 is the number of rows in the first table, and n2 is the number of rows in the second table.

Question 9

Which SQL function is used to count the number of rows in a SQL query ?

COUNT()
NUMBER()
SUM()
COUNT(*)

Answer

COUNT(*)

Reason — The SQL function COUNT(*) is used to count the number of rows in an SQL query, including duplicates and rows with NULL values.

Question 10

Which of the following is not an aggregate function ?

Avg
Sum
With
Min

Answer

With

Reason — Aggregate functions in SQL include AVG, COUNT, MAX, MIN, and SUM.

Question 11

All aggregate functions except ............... ignore null values in their input collection.

Count(attribute)
Count(*)
Avg
Sum

Answer

Count(*)

Reason — All aggregate functions, except for COUNT(*), ignore null values in their input collection. COUNT(*) returns all rows, including duplicates and nulls.

Question 12

Which of the following is a SQL aggregate function ?

LEFT
AVG
JOIN
LEN

Answer

AVG

Reason — Aggregate functions in SQL include AVG, COUNT, MAX, MIN, and SUM.

Question 13

Which of the following group functions ignore NULL values ?

MAX
COUNT
SUM
All of the above

Answer

All of the above

Reason — All aggregate functions, except for COUNT(*), ignore null values in their input collection.

Question 14

The functions which work with individual rows' data are called ............... function.

Single row
Multiple rows
Aggregate
None of these

Answer

Single row

Reason — Single-row functions in SQL work with one row at a time and return a result for every row of a queried table.

Question 15

Function count() is a/an ............... function.

Single row
Multiple rows
Aggregate
None of these

Answer

Aggregate

Reason — The COUNT() function is an aggregate function in SQL.

Question 16

The sum(), if used in a condition, is used with ............... clause.

Group by
With
Where
Having

Answer

Having

Reason — When using the SUM() function in a condition, it is used with the HAVING clause. The HAVING clause is used to apply conditions to groups of rows after the GROUP BY operation has been performed, making it suitable for conditions involving aggregate functions like SUM().

Question 17

Which clause cannot be used with "aggregate functions" ?

GROUP BY
SELECT
WHERE
Both (1) and (3)

Answer

WHERE

Reason — Aggregate functions cannot be used with the WHERE clause in SQL because the WHERE clause filters rows before any grouping or aggregation occurs.

Question 18

What is the meaning of "WHERE" clause in a GROUP BY query ?

To filter out the summary groups
To filter out the column groups
To filter out the row and column values before creating groups
None of the mentioned

Answer

To filter out the row and column values before creating groups

Reason — The WHERE clause is used to filter rows and columns values before any grouping occurs in a query with a GROUP BY clause.

Question 19

The following SQL is which type of join :

SELECT CUSTOMER.CUST_ID, ORDER.CUST_ID, NAME, ORDER_ID

FROM CUSTOMER, ORDER

WHERE CUSTOMER.CUST_ID = ORDER.CUST_ID?

Equi-join
Natural join
Outer join
Cartesian product

Answer

Equi-join

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator. In the given SQL query, the WHERE clause specifies the condition CUSTOMER.CUST_ID = ORDER.CUST_ID, which compares the CUST_ID column from the CUSTOMER table with the CUST_ID column from the ORDER table. Therefore, it is an equi-join.

Question 20

The following SQL is which type of join :

SELECT CUSTOMER.CUST_ID, ORDER.CUST_ID, NAME, ORDER_ID

FROM CUSTOMER, ORDER

Equi-join
Natural join
Outer join
Cartesian product

Answer

Cartesian product

Reason — This query uses a comma-separated list of table names in the FROM clause without specifying any join condition. In SQL, when we list multiple tables in the FROM clause separated by commas without specifying a join condition, it results in a Cartesian product.

Question 21

Which product is returned in a join query have no join condition ?

Equijoins
Cartesian product
Both (1) and (2)
None of the mentioned

Answer

Cartesian product

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

Question 22

Which is a join condition contains an equality operator ?

Equijoins
Cartesian product
Both (1) and (2)
None of the mentioned

Answer

Equijoins

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator.

Fill in the Blanks

Question 1

To specify condition with a GROUP BY clause, HAVING clause is used.

Question 2

Only aggregate functions are used with GROUP BY.

Question 3

A JOIN is a means for combining fields from two tables by using values common to each.

Question 4

The equi join uses = operator in the join condition.

Question 5

Natural join joins two tables on the basis of a common field.

Question 6

The SQL built-in function SUM totals values in numeric columns.

Question 7

The SQL built-in function AVG computes the average of values in numeric columns.

Question 8

The SQL built-in function MAX obtains the largest value in a numeric column.

Question 9

The SQL built-in function MIN obtains the smallest value in a numeric column.

Question 10

The SQL built-in function COUNT computes the number of rows in a table.

Question 11

The SELECT clause GROUP BY is used to collect those rows that have the same value in a specified column.

Question 12

To compare an aggregate value in a condition, HAVING clause is used.

Question 13

To create a summary of records based on the common value in a field in different rows of the table GROUP BY clause is used.

Question 14

To get data from two or more tables having some common fields, join query is created.

Question 15

In equi-join, the join condition joins the two table using = operator.

True/False Questions

Question 1

The HAVING clause acts like a WHERE clause, but it identifies groups that meet a criterion, rather than rows.

Answer

True

Reason — The HAVING clause in SQL is used to filter groups based on specified conditions, while the WHERE clause filters individual rows. This means that the HAVING clause works with grouped data, applying conditions to groups that meet certain criteria, whereas the WHERE clause applies conditions to individual rows before any grouping occurs.

Question 2

Data manipulation language (DML) commands are used to define a database, including creating, altering, and dropping tables and establishing constraints.

Answer

False

Reason — Data Definition Language (DDL) commands are used to define a database, including creating, altering, and dropping tables and establishing constraints.

Question 3

The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.

Answer

True

Reason — The SQL keyword GROUP BY clause instructs the DBMS to combine all those rows that have identical values in a particular column or a group of columns.

Question 4

Equi join can use any operator for joining two tables.

Answer

False

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator.

Question 5

Missing join condition in join query produces cartesian product.

Answer

True

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

Question 6

COUNT(field_name) tallies only those rows that contain a value; it ignores all null values.

Answer

True

Reason — When we use COUNT(field_name), it counts only the rows where the specified field (field_name) is not null. It does ignore null values for that specific field during counting.

Question 7

SUM, AVG, MIN, and MAX can only be used with numeric columns.

Answer

True

Reason — The aggregate functions SUM, AVG, MIN, and MAX are designed to work with numeric columns in SQL. They expect numeric values as arguments and return numeric results.

Question 8

The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.

Answer

True

Reason — The SQL keyword GROUP BY instructs the DBMS to combine all those rows that have identical values in a particular column or a group of columns.

Question 9

The HAVING and WHERE clauses are interchangeable.

Answer

False

Question 10

The HAVING clauses can take any valid SQL function in its condition.

Answer

False

Reason — The HAVING clause can contain either a simple boolean expression (i.e., an expression or condition that results into true or false) or use aggregate function in the having condition.

Question 11

HAVING clause can only be used if the SELECT query has GROUP BY clause.

Answer

True

Reason — The HAVING clause in SQL works with grouped data, applying conditions to groups that meet certain criteria. Therefore, HAVING clause can only be used if the SELECT query has GROUP BY clause.

Question 12

With GROUP BY, the select-list of the SELECT statement can only take the group-field and/or aggregate function.

Answer

True

Reason — When using the GROUP BY clause in SQL, the select-list of the SELECT statement can only include the grouping columns (group-fields) and/or aggregate functions. MySQL would not produce any error even if we include a non-group field in the select-list. However, it will return the value from first record of the group for that non-group field.

Question 13

A join query without the join condition produces a cartesian product.

Answer

True

Reason — When a join query has no join condition specified, it results in a Cartesian product. This means that every row from the first table is combined with every row from the second table.

Question 14

You can specify any type of condition, using any comparison operator in an equi-join.

Answer

False

Reason — An equi-join is a type of join where columns from two or more tables are compared for equality using the "=" operator.

Question 15

Join can only be created from two tables.

Answer

False

Reason — A join in SQL is a query that combines rows from two or more tables.

Assertions and Reasons

Question 1

Assertion. SQL SELECT's GROUP BY clause is used to divide the result in groups.

Reason. The GROUP BY clause combines all those records that have identical values in a particular field or in group by fields.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The GROUP BY clause combines all those records that have identical values in a particular field or a group of fields. This clause is utilised in SELECT statements to divide the result set into groups based on the values in the specified columns. Grouping can be done by a column name, or with aggregate functions in which case the aggregate produces a value for each group.

Question 2

Assertion. A GROUP BY query can also include functions.

Reason. ALL SQL functions can be used in a GROUP BY query.

Answer

(c)

Assertion is true but Reason is false.

Explanation
A GROUP BY query can include functions. This is true because SQL allows the use of aggregate functions like COUNT(), SUM(), etc., within a GROUP BY query to perform calculations on grouped data. However, it's important to note that not all SQL functions can be used within a GROUP BY query. Only aggregate functions or functions that operate on grouped data can be used effectively in this context.

Question 3

Assertion. SQL SELECT query can also fetch rows from multiple tables.

Reason. A join is a query that combines rows from two or more tables.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
SQL SELECT queries can fetch rows from multiple tables using JOIN operations. This allows retrieving data from related tables simultaneously in a single query. A join in SQL is a query that combines rows from two or more tables based on a specified condition, such as matching values in a common column.

Question 4

Assertion. Generally, a join query combines rows from multiple tables having some common column(s) and a joining condition, but not always.

Reason. A Cartesian product is an unrestricted join where all possible combinations of rows from multiple tables are created without any condition on them.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
In SQL, a join is a query that combines rows from two or more tables based on a specified condition, such as matching values in a common column. However, this matching condition is not always required; for instance, a Cartesian product is an unrestricted join where all possible combinations of rows from multiple tables are generated without any specific condition. This means that every row from one table is combined with every row from another table.

Question 5

Assertion. The join, in which columns are compared for equality, is called Equi-join.

Reason. The join queries only produce combined rows from tables having some common column, when compared for equality.

Answer

(c)

Assertion is true but Reason is false.

Explanation
The join, in which columns are compared for equality, is called Equi-join. Join queries in SQL can produce combined rows based on various conditions, not just equality comparisons. For example, join conditions can involve inequality comparisons (<, >, <=, >=), as well as other logical operators and functions.

Question 6

Assertion. In the result produced by a join query, there may be multiple identical columns in the final result, but not always.

Reason. The join in which only one of the identical columns (coming from the joined tables) exists, is called a Natural join.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
In the result produced by a join query, there may be multiple identical columns in the final result, but this is not always the case. Depending on the type of join and the columns involved, duplicate columns may or may not appear in the result set. A Natural join is a type of join in SQL where only one of the identical columns (coming from the joined tables) exists in the final result.

Question 7

Assertion. In order to carry out the join operation, there should be a governing condition.

Reason. A join condition may be based on equality, less than, greater than or non-equality.

Answer

(d)

Assertion is false but Reason is true.

Explanation
In order to carry out the join operation, there should be a governing condition is false. This is because a Cartesian join is an example of a join operation that does not require a specific condition; it combines all rows from one table with all rows from another table without any matching condition. A join condition may be based on equality, less than, greater than, or non-equality depending on the requirements of the query.

Type A: Short Answer Questions/Conceptual Questions

Question 1

What is the difference between HAVING and WHERE clause?

Answer

HAVING clause	WHERE clause
HAVING conditions are applicable to groups formed by GROUP BY clause.	WHERE conditions are applicable to individual rows.
HAVING conditions can include aggregate functions.	WHERE conditions cannot include aggregate functions.
It allows conditions to be applied to grouped data.	It filters rows based on specified conditions.

Question 2

What is the use of GROUP BY clause ?

Answer

The GROUP BY clause in SQL is used to combine all records that have identical values in a particular field or group of fields. This grouping results in one summary record per group if group functions, such as aggregate functions, are used with it.

Question 3

What are aggregate functions? What is their use? Give some examples.

Answer

Aggregate functions in SQL work with data from multiple rows at a time and return a single aggregated value. They are used to perform calculations across multiple rows and return a summary result for that group.

Examples of aggregate functions include SUM(), COUNT(), MAX(), MIN(), AVG() etc.

Question 4

What type of functions can you use with GROUP BY and HAVING clauses ?

Answer

Aggregate functions are used with the GROUP BY and HAVING clauses to apply conditions on grouped data.

Question 5

What is sql join ? How is it useful ?

Answer

A SQL join is a query that combines rows from two or more tables. Join allows retrieving data from related tables simultaneously in a single query. Joins enable the combination of data from different tables to obtain a comprehensive view of the information.

Question 6

What are most common types of SQL joins ?

Answer

The most common types of SQL joins are as follows :

Equi-Join
Non-Equi-Join
Natural join

Type B: Application Based Questions

Question 1

Table BOOK_INFORMATION

Column Name

BOOK_ID

BOOK_TITLE

PRICE

Which SQL statement allows you to find the highest price from the table BOOK_INFORMATION?

SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION;
SELECT MAX(PRICE) FROM BOOK_INFORMATION;
SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION;
SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC;

Answer

SELECT MAX(PRICE) FROM BOOK_INFORMATION;

Explanation

SELECT BOOK_ID, BOOK_TITLE, MAX(PRICE) FROM BOOK_INFORMATION; — This query selects the BOOK_ID, BOOK_TITLE, and maximum PRICE from the BOOK_INFORMATION table. However, the requirement is to find the highest price only.
SELECT MAX(PRICE) FROM BOOK_INFORMATION; — This query selects the maximum PRICE from the BOOK_INFORMATION table using the MAX() aggregate function. This option is correct because it directly retrieves the highest price among all the books listed in the BOOK_INFORMATION table, which is what the question asks for.
SELECT MAXIMUM(PRICE) FROM BOOK_INFORMATION; — There is no MAXIMUM() function in SQL.
SELECT PRICE FROM BOOK_INFORMATION ORDER BY PRICE DESC; — This query selects all prices from the BOOK_INFORMATION table and orders them in descending order using ORDER BY PRICE DESC but it doesn't directly give the highest price.

Question 2

Table SALES

Column Name

STORE_ID

SALES_DATE

SALES_AMOUNT

Which SQL statement lets you find the sales amount for each store?

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID;

Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID;

Explanation

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table. It does not group the results by store ID, so it will return a single row with the total sales amount across all stores.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES ORDER BY STORE_ID; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table and uses an ORDER BY clause to sort the results by store ID. However, it still doesn't group the results by store_ID.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID; — This statement selects the store_ID and calculates the total sales amount using SUM() aggregate function from the SALES table and uses the GROUP BY clause to group the results by store ID. It calculates the total sales amount for each store ID separately. As a result, it calculates the total sales amount for each unique store ID separately, providing a breakdown of sales amounts for each store in the dataset.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES HAVING UNIQUE STORE_ID; — This statement is incorrect because the HAVING clause is used for filtering grouped data based on a condition, not for identifying unique values. Also, "UNIQUE STORE_ID" is not a valid condition in SQL.

Question 3

Table SALES

Column Name

STORE_ID

SALES_DATE

SALES_AMOUNT

Which SQL statement lets you list all stores whose total sales amount is over 5000 ?

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID;

Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES

GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000;

Explanation

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SUM(SALES_AMOUNT) > 5000; — This statement selects the store ID and calculates the total sales amount for each store using the SUM() aggregate function. The GROUP BY STORE_ID clause ensures that the results are grouped by store ID. The HAVING SUM(SALES_AMOUNT) > 5000 condition then filters the grouped data, selecting only those stores whose total sales amount is over 5000.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING SALES_AMOUNT > 5000; — This option is incorrect because the HAVING clause cannot directly reference SALES_AMOUNT without an aggregate function like SUM() since SUM(SALES_AMOUNT) is used in the SELECT statement.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SUM(SALES_AMOUNT) > 5000 GROUP BY STORE_ID; — This option is incorrect because aggregate functions like SUM(SALES_AMOUNT) cannot be used directly in the WHERE clause. The WHERE clause is used for filtering individual rows before grouping.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE SALES_AMOUNT > 5000 GROUP BY STORE_ID; — This option is incorrect because it tries to filter individual sales amounts (SALES_AMOUNT) directly without using the SUM() aggregate function to calculate the total sales amount for each store. The GROUP BY STORE_ID clause is used for grouping after the filtering, which is not the correct approach for filtering based on the total sales amount.

Question 4

Table SALES

Column Name

STORE_ID

SALES_DATE

SALES_AMOUNT

Which SQL statement lets you find the total number of stores in the SALES table?

SELECT COUNT(STORE_ID) FROM SALES;
SELECT COUNT(DISTINCT STORE_ID) FROM SALES;
SELECT DISTINCT STORE_ID FROM SALES;
SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID;

Answer

SELECT COUNT(DISTINCT STORE_ID) FROM SALES;

Explanation

SELECT COUNT(STORE_ID) FROM SALES; — This query uses the COUNT() aggregate function with the STORE_ID column in the SELECT statement. It counts the number of non-null values in the STORE_ID column, and this count includes duplicates.
SELECT COUNT(DISTINCT STORE_ID) FROM SALES; — This option uses COUNT(DISTINCT STORE_ID) to count the number of unique store IDs in the SALES table. The DISTINCT keyword ensures that only distinct (unique) values are counted, avoiding overcounting due to duplicates.
SELECT DISTINCT STORE_ID FROM SALES; — This option selects distinct (unique) store IDs from the SALES table but does not count or provide the total number of stores.
SELECT COUNT(STORE_ID) FROM SALES GROUP BY STORE_ID; — This option attempts to count the number of occurrences of each store ID by using COUNT(STORE_ID) and grouping by store ID with GROUP BY STORE_ID. However, this results in a count for each unique store ID separately, not the total number of stores in the table.

Question 5

Table SALES

Column Name

STORE_ID

SALES_DATE

SALES_AMOUNT

Which SQL statement allows you to find the total sales amount for Store ID 25 and the total sales amount for Store ID 45?

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
GROUP BY STORE_ID HAVING STORE_ID IN (25, 45);
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45);
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES
WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID;

Answer

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES

WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID;

Explanation

SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45) GROUP BY STORE_ID; — This query uses the IN operator to filter rows where the STORE_ID is either 25 or 45. It then calculates the total sales amount for each store ID using SUM(SALES_AMOUNT) and groups the results by STORE_ID. This query correctly finds the total sales amount for Store ID 25 and Store ID 45 separately.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES GROUP BY STORE_ID HAVING STORE_ID IN (25, 45); — This query will also give the required output but it is inefficient because it first retrieves all rows from the "SALES" table, then groups the results by store ID, and finally filters the result set to include only store IDs 25 and 45. The inefficiency arises from the fact that it processes all rows in the "SALES" table before filtering out the unnecessary data. This means that it processes more data than necessary, which can be wasteful in terms of computational resources and time. A more efficient approach would be to select only the rows having store IDs 25 and 45 first (using WHERE clause), and then perform the aggregation.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID IN (25, 45); — This query filters rows where the STORE_ID is either 25 or 45 and calculates the total sales amount for these store IDs using SUM(SALES_AMOUNT). However, it doesn't include a GROUP BY clause, so it would return a single row with the total sales amount for both Store ID 25 and Store ID 45 combined.
SELECT STORE_ID, SUM(SALES_AMOUNT) FROM SALES WHERE STORE_ID = 25 AND STORE_ID = 45 GROUP BY STORE_ID; — This query filter rows where the STORE_ID is both 25 and 45 simultaneously using STORE_ID = 25 AND STORE_ID = 45. However, this condition is impossible to satisfy because a single value cannot be both 25 and 45 at the same time. Therefore, this query would not return any results.

Question 6

Table EXAM_RESULTS

STU ID	FNAME	LNAME	EXAM ID	EXAM_SCORE
10	LAURA	LYNCH	1	90
10	LAURA	LYNCH	2	85
11	GRACE	BROWN	1	78
11	GRACE	BROWN	2	72
12	JAY	JACKSON	1	95
12	JAY	JACKSON	2	92
13	WILLIAM	BISHOP	1	70
13	WILLIAM	BISHOP	2	100
14	CHARLES	PRADA	2	85

What SQL statement do we use to find the average exam score for EXAM_ID = 1?

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS;
SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1;
SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;
SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1;

Answer

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;

Output

+-----------------+

| AVG(EXAM_SCORE) |

+-----------------+

| 83.2500 |

+-----------------+

Explanation

SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS; — This statement calculates the average exam score across all exam IDs in the EXAM_RESULTS table.
SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID WHERE EXAM_ID = 1; — This statement is incorrect because the WHERE clause should come before the GROUP BY clause. Additionally, grouping by EXAM_ID and then trying to filter by EXAM_ID = 1 within the GROUP BY clause will result in an error because grouping should be done before filtering.
SELECT AVG(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1; — This query groups the exam results by EXAM_ID and then calculates the average exam score for each group. The HAVING clause filters the groups and returns only those where the EXAM_ID is equal to 1, giving us the average exam score for the exam with EXAM_ID equal to 1.
SELECT COUNT(EXAM_SCORE) FROM EXAM_RESULTS WHERE EXAM_ID = 1; — This statement calculates the count of exam scores for EXAM_ID = 1, but it doesn't calculate the average score.

Question 7

Table EXAM_RESULTS

STU ID	FNAME	LNAME	EXAM ID	EXAM_SCORE
10	LAURA	LYNCH	1	90
10	LAURA	LYNCH	2	85
11	GRACE	BROWN	1	78
11	GRACE	BROWN	2	72
12	JAY	JACKSON	1	95
12	JAY	JACKSON	2	92
13	WILLIAM	BISHOP	1	70
13	WILLIAM	BISHOP	2	100
14	CHARLES	PRADA	2	85

Which SQL statement do we use to find out how many students took each exam?

SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;
SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

Answer

SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID;

Output

+---------+------------------------+

| EXAM_ID | COUNT(DISTINCT STU_ID) |

+---------+------------------------+

| 1 | 4 |

| 2 | 5 |

+---------+------------------------+

Explanation

SELECT COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. However, the result set does not include EXAM_ID.
SELECT EXAM_ID, MAX(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and then selects the maximum STU_ID for each exam. However, this doesn't provide the count of students who took each exam, as it gives the maximum student ID instead of counting the distinct student IDs.
SELECT EXAM_ID, COUNT(DISTINCT STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — It groups the EXAM_RESULTS table by EXAM_ID and uses the COUNT(DISTINCT STU_ID) function to count the number of distinct student IDs for each exam. The result set includes the EXAM_ID and the count of students who took each exam.
SELECT EXAM_ID, MIN(STU_ID) FROM EXAM_RESULTS GROUP BY EXAM_ID; — This query groups the results by EXAM_ID and selects the minimum STU_ID for each exam. It does not provide information about the number of students who took each exam.

Question 8

Table EXAM_RESULTS

STU ID	FNAME	LNAME	EXAM ID	EXAM_SCORE
10	LAURA	LYNCH	1	90
10	LAURA	LYNCH	2	85
11	GRACE	BROWN	1	78
11	GRACE	BROWN	2	72
12	JAY	JACKSON	1	95
12	JAY	JACKSON	2	92
13	WILLIAM	BISHOP	1	70
13	WILLIAM	BISHOP	2	100
14	CHARLES	PRADA	2	85

What SQL statement do we use to print out the record of all students whose last name starts with 'L'?

SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;
SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L';
SELECT * FROM EXAM_RESULTS WHERE LNAME 'L';
SELECT * FROM EXAM_RESULTS WHERE LNAME <> 'L';

Answer

SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%' ;

Output

+--------+-------+-------+---------+------------+

+--------+-------+-------+---------+------------+

| 10 | LAURA | LYNCH | 1 | 90 |

| 10 | LAURA | LYNCH | 2 | 85 |

+--------+-------+-------+---------+------------+

Explanation

SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L%'; — The LIKE operator is used for pattern matching in SQL. '%' is a wildcard character that matches zero or more characters. 'L%' specifies that the last name (LNAME) should start with 'L' followed by zero or more characters. The SELECT * statement retrieves all columns from the EXAM_RESULTS table for the matching records.
SELECT * FROM EXAM_RESULTS WHERE LNAME LIKE 'L'; — This query attempts to select all columns (*) from the EXAM_RESULTS table where the last name (LNAME) is exactly equal to 'L'. However, when using the LIKE operator in SQL for pattern matching, we use wildcard characters (%) to represent unknown parts of a string.
SELECT * FROM EXAM_RESULTS WHERE LNAME 'L'; — This statement contains a syntax error. In SQL, when using the WHERE clause to filter records based on a specific condition, we need to use comparison operators or functions to define the condition properly.
SELECT * FROM EXAM_RESULTS WHERE LNAME <> 'L'; — This query retrieves records where the last name is not equal to 'L'. It does not specifically look for last names starting with 'L', so it's not the correct option for the given requirement.

Question 9

Table EXAM_RESULTS

STU ID	FNAME	LNAME	EXAM ID	EXAM_SCORE
10	LAURA	LYNCH	1	90
10	LAURA	LYNCH	2	85
11	GRACE	BROWN	1	78
11	GRACE	BROWN	2	72
12	JAY	JACKSON	1	95
12	JAY	JACKSON	2	92
13	WILLIAM	BISHOP	1	70
13	WILLIAM	BISHOP	2	100
14	CHARLES	PRADA	2	85

What is the result of the following SQL statement ?

SELECT MAX(EXAM_SCORE) FROM EXAM_RESULTS GROUP BY EXAM_ID HAVING EXAM_ID = 1;

Answer

Output

+-----------------+

| MAX(EXAM_SCORE) |

+-----------------+

| 95 |

+-----------------+

Explanation

The above SQL query calculates the maximum exam score for EXAM_ID 1 from the EXAM_RESULTS table, by grouping results based on EXAM_ID and filtering using HAVING.

Question 10

Given the following table :

Table : CLUB

COACH-ID	COACHNAME	AGE	SPORTS	DATOFAPP	PAY	SEX
1	KUKREJA	35	KARATE	27/03/1996	1000	M
2	RAVINA	34	KARATE	20/01/1998	1200	F
3	KARAN	34	SQUASH	19/02/1998	2000	M
4	TARUN	33	BASKETBALL	01/01/1998	1500	M
5	ZUBIN	36	SWIMMING	12/01/1998	750	M
6	KETAKI	36	SWIMMING	24/02/1998	800	F
7	ANKITA	39	SQUASH	20/02/1998	2200	F
8	ZAREEN	37	KARATE	22/02/1998	1100	F
9	KUSH	41	SWIMMING	13/01/1998	900	M
10	SHAILYA	37	BASKETBALL	19/02/1998	1700	M

Give the output of following SQL statements :

SELECT COUNT(DISTINCT SPORTS) FROM Club ;
SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ;
SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ;
SELECT SUM(Pay) FROM CLUB WHERE Datofapp > '1998-01-31' ;

Answer

Output

+------------------------+

| COUNT(DISTINCT SPORTS) |

+------------------------+

| 4 |

+------------------------+

Explanation

The SQL query SELECT COUNT(DISTINCT SPORTS) FROM Club ; calculates the count of unique values in the 'SPORTS' column of the 'Club' table. This query helps us to get information about the number of sports offered by the club.

Output

+----------+

| MIN(Age) |

+----------+

| 34 |

+----------+

Explanation

The SQL query SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ; retrieves the minimum Age from the 'CLUB' table where the 'Sex' column has the value 'F'. This query gives us the age of the youngest female coach in the club.

Output

+-----------+

| AVG(Pay) |

+-----------+

| 1100.0000 |

+-----------+

Explanation

The SQL query SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ; calculates the average value of the 'Pay' column from the 'CLUB' table where the 'Sports' column has the value 'KARATE'. This query helps us to get information about the average pay of karate coaches in the club.

Output

+----------+

| SUM(Pay) |

+----------+

| 7800 |

+----------+

Explanation

The SQL query SELECT SUM(Pay) FROM CLUB WHERE Dateofapp > '1998-01-31'; calculates the sum of the 'Pay' column from the 'CLUB' table where the 'Dateofapp' column has a date value greater than '1998-01-31'. This query gives us the total pay of all the coaches who joined after 31/01/1998.

Question 11

In a Database, there are two tables given below :

Table : EMPLOYEE

EMPLOYEEID	NAME	SALES	JOBID
E1	SUMIT SINHA	1100000	102
E2	VIJAY SINGH TOMAR	1300000	101
E3	AJAY RAJPAL	1400000	103
E4	MOHIT RAMNANI	1250000	102
E5	SHAILJA SINGH	1450000	103

Table : JOB

JOBID	JOBTITLE	SALARY
101	President	200000
102	Vice President	125000
103	Administration Assistant	80000
104	Accounting Manager	70000
105	Accountant	65000
106	Sales Manager	80000

Write SQL Queries for the following :

To display employee ids, names of employees, job ids with corresponding job titles.
To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.
To display names and corresponding job titles of those employees who have 'SINGH' (anywhere) in their names.
Identify foreign key in the table EMPLOYEE.
Write SQL command to change the JOBID to 104 of the EMPLOYEE with ID as E4 in the table 'EMPLOYEE'.

Answer

SELECT EMPLOYEE.EMPLOYEEID, EMPLOYEE.NAME, EMPLOYEE.JOBID, JOB.JOBTITLE

FROM EMPLOYEE, JOB

WHERE EMPLOYEE.JOBID = JOB.JOBID;

Output

+------------+-------------------+-------+--------------------------+

+------------+-------------------+-------+--------------------------+

| E1 | SUMIT SINHA | 102 | VICE PRESIDENT |

| E2 | VIJAY SINGH TOMAR | 101 | PRESIDENT |

| E3 | AJAY RAJPAL | 103 | ADMINISTARTION ASSISTANT |

| E4 | MOHIT RAMNANI | 102 | VICE PRESIDENT |

| E5 | SHAILJA SINGH | 103 | ADMINISTARTION ASSISTANT |

+------------+-------------------+-------+--------------------------+

SELECT EMPLOYEE.NAME, EMPLOYEE.SALES, JOB.JOBTITLE

FROM EMPLOYEE, JOB

WHERE EMPLOYEE.JOBID = JOB.JOBID

AND EMPLOYEE.SALES > 1300000;

Output

+---------------+---------+--------------------------+

| NAME | SALES | JOBTITLE |

+---------------+---------+--------------------------+

| AJAY RAJPAL | 1400000 | ADMINISTARTION ASSISTANT |

| SHAILJA SINGH | 1450000 | ADMINISTARTION ASSISTANT |

+---------------+---------+--------------------------+

SELECT EMPLOYEE.NAME, JOB.JOBTITLE

FROM EMPLOYEE, JOB

WHERE EMPLOYEE.JOBID = JOB.JOBID

AND EMPLOYEE.NAME LIKE '%SINGH%';

Output

+-------------------+--------------------------+

| NAME | JOBTITLE |

+-------------------+--------------------------+

| VIJAY SINGH TOMAR | PRESIDENT |

| SHAILJA SINGH | ADMINISTARTION ASSISTANT |

+-------------------+--------------------------+

4. In the given tables, EMPLOYEE and JOB, the JOBID column in the EMPLOYEE table is a foreign key referencing the JOBID column in the JOB table.

UPDATE EMPLOYEE

SET JOBID = 104

WHERE EMPLOYEEID = 'E4';

Output

SELECT * FROM EMPLOYEE ;

+------------+-------------------+---------+-------+

+------------+-------------------+---------+-------+

| E1 | SUMIT AINHA | 1100000 | 102 |

| E2 | VIJAY SINGH TOMAR | 1300000 | 101 |

| E3 | AJAY RAJPAL | 1400000 | 103 |

| E4 | MOHIT RAMNANI | 1250000 | 104 |

| E5 | SHAILJA SINGH | 1450000 | 103 |

+------------+-------------------+---------+-------+

Question 12

Consider the following tables Employee and Salary. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (vii)

Table : Employee

Eid	Name	Depid	Qualification	Gender
1	Deepali Gupta	101	MCA	F
2	Rajat Tyagi	101	BCA	M
3	Hari Mohan	102	B.A.	M
4	Harry	102	M.A.	M
5	Sumit Mittal	103	B.Tech.	M
6	Jyoti	101	M.Tech.	F

Table : Salary

Eid	Basic	D.A	HRA	Bonus
1	6000	2000	2300	200
2	2000	300	300	30
3	1000	300	300	40
4	1500	390	490	30
5	8000	900	900	80
6	10000	300	490	89

To display the frequency of employees department wise.
To list the names of those employees only whose name starts with 'H'
To add a new column in salary table. The column name is Total_Sal.
To store the corresponding values in the Total_Sal column.
Select max(Basic) from Salary where Bonus > 40 ;
Select count(*) from Employee group by Sex ;
Select Distinct Depid from Employee ;

Answer

SELECT Depid, COUNT(*) AS Frequency

FROM Employee

GROUP BY Depid;

SELECT Name

FROM Employee

WHERE Name LIKE 'H%';

ALTER TABLE Salary

ADD COLUMN Total_Sal FLOAT;

UPDATE Salary

SET Total_Sal = Basic + `D.A.` + HRA + Bonus;

SELECT MAX(Basic)

FROM Salary

WHERE Bonus > 40;

Output

+------------+

| MAX(Basic) |

+------------+

| 10000 |

+------------+

SELECT Sec as sex, COUNT(*) AS Count

FROM Employee

GROUP BY Sec ;

Output

+-----+-------+

| sex | Count |

+-----+-------+

| F | 2 |

| M | 4 |

+-----+-------+

SELECT DISTINCT Depid FROM Employee ;

Output

+-------+

| Depid |

+-------+

| 101 |

| 102 |

| 103 |

+-------+

Question 13

With reference to following relations PERSONAL and JOB answer the questions that follow :

Create following tables such that Empno and Sno are not null and unique, date of birth is after '12-Jan-1960', name is never blank, Area and Native place is valid, hobby, dept is not empty, salary is between 4000 and 10000.

Table : Personal

Empno	Name	Dobirth	Native-place	Hobby
123	Amit	23-Jan-1965	Delhi	Music
127	Manoj	12-dec-1976	Mumbai	Writing
124	Abhai	11-aug-1975	Allahabad	Music
125	Vinod	04-apr-1977	Delhi	Sports
128	Abhay	10-mar-1974	Mumbai	Gardening
129	Ramesh	28-oct-1981	Pune	Sports

Table : Job

Sno	Area	App_date	Salary	Retd_date	Dept
123	Agra	25-jan-2006	5000	25-jan-2026	Marketing
127	Mathura	22-dec-2006	6000	22-dec-2026	Finance
124	Agra	19-aug-2007	5500	19-aug-2027	Marketing
125	Delhi	14-apr-2004	8500	14-apr-2018	Sales
128	Pune	13-mar-2008	7500	13-mar-2028	Sales
129	Bangalore	21-july-2003	7000	21-july-2023	Finance

(a) Show empno, name and salary of those who have Sports as hobby.

(b) Show name of the eldest employee.

(d) Show youngest employees from each Native place.

(e) Show Sno, Name, Hobby and Salary in descending order of Salary.

(f) Show the hobbies of those whose name pronounces as 'Abhay'.

(g) Show the appointment date and native place of those whose name starts with 'A' or ends in 'd'.

(h) Show the salary expense with suitable column heading of those who shall retire after 20-jan-2006.

(i) Show additional burden on the company in case salary of employees having hobby as sports, is increased by 10%.

(j) Show the hobby of which there are 2 or more employees.

(k) Show how many employee shall retire today if maximum length of service is 20 years.

(l) Show those employee name and date of birth who have served more than 17 years as on date.

(m) Show names of those who earn more than all of the employees of Sales dept.

(n) Increase salary of the employees by 5 % of their present salary with hobby as Music or they have completed atleast 3 years of service.

(o) Write the output of :

Select distinct hobby from personal ;
Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;
Select count(distinct Native_place) from personal.
Select name, max(salary) from Personal, Job where Personal.Empno = Job.Sno;

(p) Add a new tuple in the table Personal essentially with hobby as Music.

(q) Insert a new column email in Job table

(r) Create a table with values of columns empno, name, and hobby.

(s) Create a view of Personal and Job details of those who have served less than 15 years.

(t) Erase the records of employee from Job table whose hobby is not Sports.

(u) Remove the table Personal.

Answer

(a)

SELECT P.EMPNO, P.NAME, J.Salary

FROM PERSONAL P, JOB J

WHERE P.EMPNO = J.Sno AND P.Hobby = 'Sports';

Output

+-------+--------+--------+

| EMPNO | NAME | Salary |

+-------+--------+--------+

| 125 | Vinod | 8500 |

| 129 | Ramesh | 7000 |

+-------+--------+--------+

(b)

SELECT name

FROM personal

WHERE dobirth = (

SELECT MIN(dobirth)

FROM personal

);

Output

+------+

| name |

+------+

| Amit |

+------+

(c)

SELECT Area, COUNT(Sno) AS Employee_Count

FROM Job

GROUP BY Area;

Output

+-----------+----------------+

| Area | Employee_Count |

+-----------+----------------+

| Agra | 2 |

| Delhi | 1 |

| Mathura | 1 |

| Pune | 1 |

| Bangalore | 1 |

+-----------+----------------+

(d)

SELECT Name, `Native-place`, dobirth

FROM personal

WHERE dobirth = (SELECT MAX(dobirth)

FROM personal p2

WHERE personal.`Native-place` = p2.`Native-place` ) ;

Output

+--------+--------------+------------+

| Name | Native-place | dobirth |

+--------+--------------+------------+

| Abhai | Allahabad | 1975-08-11 |

| Vinod | Delhi | 1977-04-04 |

| Manoj | Mumbai | 1976-12-12 |

| Ramesh | Pune | 1981-10-28 |

+--------+--------------+------------+

(e)

SELECT SNO, NAME, HOBBY, SALARY

FROM PERSONAL, JOB

WHERE PERSONAL.EMPNO = JOB.SNO

ORDER BY SALARY DESC;

Output

+-----+--------+-----------+--------+

+-----+--------+-----------+--------+

| 125 | Vinod | Sports | 8500 |

| 128 | Abhay | Gardening | 7500 |

| 129 | Ramesh | Sports | 7000 |

| 127 | Manoj | Writing | 6000 |

| 124 | Abhai | Music | 5500 |

| 123 | Amit | Music | 5000 |

+-----+--------+-----------+--------+

(f)

SELECT HOBBY

FROM PERSONAL

WHERE Name = 'abhay' or Name = 'abhai' ;

Output

+-----------+

| HOBBY |

+-----------+

| Music |

| Gardening |

+-----------+

(g)

SELECT App_date, nativeplace

FROM personal, job

WHERE personal.empno = job.sno

AND (Name LIKE 'A%' OR Name LIKE '%d') ;

Output

+------------+--------------+

| App_date | native-place |

+------------+--------------+

| 2006-01-25 | Delhi |

| 2007-08-19 | Allahabad |

| 2004-04-14 | Delhi |

| 2008-03-13 | Mumbai |

+------------+--------------+

(h)

SELECT Salary AS "Salary Expense"

FROM Job

WHERE `Retd_date` > '2006-01-20';

Output

+----------------+

| Salary Expense |

+----------------+

| 5000 |

| 5500 |

| 8500 |

| 6000 |

| 7500 |

| 7000 |

+----------------+

(i)

SELECT SUM(Salary * 0.1) AS "Additional Burden"

FROM PERSONAL, JOB

WHERE PERSONAL.EMPNO = JOB.SNO AND HOBBY = 'SPORTS' ;

Output

+-------------------+

| Additional Burden |

+-------------------+

| 1550.0 |

+-------------------+

(j)

SELECT Hobby

FROM PERSONAL

GROUP BY Hobby

HAVING COUNT(*) >= 2;

Output

+--------+

| Hobby |

+--------+

| Music |

| Sports |

+--------+

(k)

SELECT COUNT(*)

FROM Job

WHERE DATEDIFF(CURDATE(), App_date) >= 20 * 365;

Output

+----------+

| COUNT(*) |

+----------+

| 0 |

+----------+

(l)

SELECT P.Name, P.Dobirth

FROM Personal P, Job J

WHERE P.Empno = J.Sno AND J.Retd_date > CURDATE() AND DATEDIFF(CURDATE(), J.App_date) > 17 * 365;

Output

+-------+------------+

| Name | Dobirth |

+-------+------------+

| Amit | 1965-01-23 |

| Manoj | 1976-12-12 |

+-------+------------+

(m)

SELECT Name

FROM Personal, job

where personal.Empno = job.Sno

and job.Salary > ( select max(salary)

from job

where dept = 'sales');

Explanation

There will be no output because there are no employees whose salary is greater than the highest salary in the Sales department.

(n)

UPDATE Job J, Personal P

SET J.Salary = (J.Salary * 0.05 ) + J.Salary

WHERE J.Sno = P.Empno

AND (P.Hobby = 'Music'

OR DATEDIFF(CURDATE(), J.App_date) >= 3 * 365);

Output

+-----+-----------+------------+--------+------------+-----------+

+-----+-----------+------------+--------+------------+-----------+

| 123 | Agra | 2006-01-25 | 5250 | 2026-01-25 | Marketing |

| 124 | Agra | 2007-08-19 | 5775 | 2027-08-19 | Marketing |

| 125 | Delhi | 2004-04-14 | 8925 | 2018-04-14 | Sales |

| 127 | Mathura | 2006-12-22 | 6300 | 2026-12-22 | Finance |

| 128 | Pune | 2008-03-13 | 7875 | 2028-03-13 | Sales |

| 129 | Bangalore | 2003-07-21 | 7350 | 2023-07-21 | Finance |

+-----+-----------+------------+--------+------------+-----------+

(o)

Select distinct hobby from personal ;

Output

+-----------+

| hobby |

+-----------+

| Music |

| Sports |

| Writing |

| Gardening |

+-----------+

Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;

Output

+-------------+

| AVG(SALARY) |

+-------------+

| 6650.0000 |

+-------------+

Select count(distinct Native_place) from personal;

Output

+--------------------------------+

| COUNT(DISTINCT `NATIVE-PLACE`) |

+--------------------------------+

| 4 |

+--------------------------------+

Select name, max(salary)

from Personal, Job where Personal.Empno = Job.Sno ;

Output

+------+-------------+

| name | max(salary) |

+------+-------------+

| Amit | 8500 |

+------+-------------+

Explanation

The given query retrieves the maximum salary from the 'Job' table and pairs it with the corresponding employee name from the 'Personal' table based on the matching Empno and Sno values. However, including a non-group field like 'name' in the select-list means it will return the value from the first record of the group for the 'name' field. Therefore, 'Amit' is the first record in the 'Personal' table, the query returns 'Amit' as the value for the 'name' field.

(p)

INSERT INTO Personal (Empno, Name, Dobirth, `Native-place`, Hobby)

VALUES (130, 'Amruta', '1990-05-15', 'Chennai', 'Music');

Output

select * from personal;

+-------+--------+------------+--------------+-----------+

+-------+--------+------------+--------------+-----------+

+-------+--------+------------+--------------+-----------+

(q)

ALTER TABLE Job

ADD COLUMN Email VARCHAR(55);

(r)

insert into empdetails(empno, name, hobby)

select empno, name, hobby

from personal ;

Output

select * from empdetails ;

+-------+--------+-----------+

| Empno | Name | Hobby |

+-------+--------+-----------+

| 123 | Amit | Music |

| 124 | Abhai | Music |

| 125 | Vinod | Sports |

| 127 | Manoj | Writing |

| 128 | Abhay | Gardening |

| 129 | Ramesh | Sports |

| 130 | Amruta | Music |

+-------+--------+-----------+

(s)

CREATE VIEW LessThan15YearsView AS

SELECT * FROM Personal p, Job j

WHERE p.Empno = j.Sno AND

DATEDIFF(CURDATE(), J.App_date) < 15 * 365;

(t)

DELETE j

FROM Job j, Personal p

WHERE j.Sno = p.Empno AND p.Hobby <> 'Sports';

(u)

DROP TAbLE Personal;

Question 14a

With reference to the table below, answer the questions that follow :

Table : Employees

Empid	Firstname	Lastname	Address	City
010	Ravi	Kumar	Raj nagar	GZB
105	Harry	Waltor	Gandhi nagar	GZB
152	Sam	Tones	33 Elm St.	Paris
215	Sarah	Ackerman	440 U.S. 110	Upton
244	Manila	Sengupta	24 Friends street	New Delhi
300	Robert	Samuel	9 Fifth Cross	Washington
335	Ritu	Tondon	Shastri Nagar	GZB
400	Rachel	Lee	121 Harrison St.	New York
441	Peter	Thompson	11 Red Road	Paris

Table : EmpSalary

Empid	Salary	Benefits	Designation
010	75000	15000	Manager
105	65000	15000	Manager
152	80000	25000	Director
215	75000	12500	Manager
244	50000	12000	Clerk
300	45000	10000	Clerk
335	40000	10000	Clerk
400	32000	7500	Salesman
441	28000	7500	Salesman

Write the SQL commands for the following using above tables :

(i) To show firstname, lastname, address and city of all employees living in Pairs.

(ii) To display the content of Employees table in descending order of Firstname.

(iii) To display the firstname, lastname and total salary of all managers from the tables Employes and EmpSalary, where total salary is calculated as Salary + Benefits.

(iv) To display the maximum salary among managers and clerks from the table EmpSalary.

Answer

(i)

SELECT Firstname, Lastname, Address, City

FROM Employees

WHERE City = 'Paris';

Output

+-----------+----------+-------------+-------+

+-----------+----------+-------------+-------+

+-----------+----------+-------------+-------+

(ii)

SELECT *

FROM Employees

ORDER BY Firstname DESC;

Output

+-------+-----------+----------+-------------------+------------+

+-------+-----------+----------+-------------------+------------+

+-------+-----------+----------+-------------------+------------+

(iii)

SELECT e.Firstname, e.Lastname,

(s.Salary + s.Benefits) AS TotalSalary

FROM Employees e, EmpSalary s

WHERE e.Empid = s.Empid AND s.Designation = 'Manager';

Output

+-----------+----------+-------------+

| Firstname | Lastname | TotalSalary |

+-----------+----------+-------------+

| RAVI | KUMAR | 90000 |

| HARRY | WALTOR | 80000 |

| SARAH | ACKERMAN | 87500 |

+-----------+----------+-------------+

(iv)

SELECT Designation, MAX(Salary)

FROM EmpSalary

WHERE Designation IN ('Manager', 'Clerk')

group by designation;

Output

+-------------+-------------+

| Designation | MAX(Salary) |

+-------------+-------------+

| MANAGER | 75000 |

| CLERK | 50000 |

+-------------+-------------+

Question 14b

With reference to the table below, answer the questions that follow :

Table : Employees

Empid	Firstname	Lastname	Address	City
010	Ravi	Kumar	Raj nagar	GZB
105	Harry	Waltor	Gandhi nagar	GZB
152	Sam	Tones	33 Elm St.	Paris
215	Sarah	Ackerman	440 U.S. 110	Upton
244	Manila	Sengupta	24 Friends street	New Delhi
300	Robert	Samuel	9 Fifth Cross	Washington
335	Ritu	Tondon	Shastri Nagar	GZB
400	Rachel	Lee	121 Harrison St.	New York
441	Peter	Thompson	11 Red Road	Paris

Table : EmpSalary

Empid	Salary	Benefits	Designation
010	75000	15000	Manager
105	65000	15000	Manager
152	80000	25000	Director
215	75000	12500	Manager
244	50000	12000	Clerk
300	45000	10000	Clerk
335	40000	10000	Clerk
400	32000	7500	Salesman
441	28000	7500	Salesman

Give the Output of following SQL commands :

(i) Select firstname, Salary from Employees, Empsalary where Designation = 'Salesman' and Employees.Empid = Empsalary.Empid ;

(ii) Select count(distinct designation) from EmpSalary ;

(iii) Select designation, sum(salary) from EmpSalary group by designation having count(*) > 2 ;

(iv) Select sum(Benefits) from EmpSalary where Designation = 'Clerk' ;

Answer

(i)

Output

+-----------+--------+

| FIRSTNAME | SALARY |

+-----------+--------+

| RACHEL | 32000 |

| PETER | 28000 |

+-----------+--------+

(ii)

Output

+-----------------------------+

| COUNT(DISTINCT DESIGNATION) |

+-----------------------------+

| 4 |

+-----------------------------+

(iii)

Output

+-------------+-------------+

| DESIGNATION | SUM(SALARY) |

+-------------+-------------+

| MANAGER | 215000 |

| CLERK | 135000 |

+-------------+-------------+

(iv)

Output

+---------------+

| SUM(BENEFITS) |

+---------------+

| 32000 |

+---------------+

Question 15

Show the average salary for all departments with more than 3 people for a job.

Answer

SELECT job, AVG(Sal) AS AvgSalary

FROM Empl

GROUP BY job HAVING COUNT(*) > 3;

Output

+----------+-----------+

| job | AvgSalary |

+----------+-----------+

| CLERK | 1037.5 |

| SALESMAN | 1400 |

+----------+-----------+

Question 16

Display only the jobs with maximum salary greater than or equal to 3000.

Answer

SELECT Job

FROM Empl

GROUP BY Job HAVING MAX(Sal) >= 3000;

Output

+-----------+

| Job |

+-----------+

| PRESIDENT |

| ANALYST |

+-----------+

Question 17

Find out number of employees having "Manager" as Job.

Answer

SELECT COUNT(*) AS NumManagers

FROM EmpSalary

WHERE Designation = 'Manager';

Output

+-------------+

| NumManagers |

+-------------+

| 3 |

+-------------+

Question 18

List the count of employees grouped by deptno. (table EMPL)

Answer

SELECT deptno, COUNT(*) AS employee_count

FROM EMPL

GROUP BY deptno;

Output

+--------+----------------+

| deptno | employee_count |

+--------+----------------+

| 20 | 5 |

| 30 | 6 |

| 10 | 3 |

+--------+----------------+

Question 19

List the sum of employees' salaries grouped by department. (table EMPL)

Answer

SELECT deptno, SUM(sal) AS total_salary

FROM EMPL

GROUP BY deptno;

Output

+--------+--------------+

| deptno | total_salary |

+--------+--------------+

| 20 | 10885 |

| 30 | 9400 |

| 10 | 8750 |

+--------+--------------+

Question 20

List the maximum salary of employee grouped by their department number.

Answer

SELECT deptno, MAX(sal) AS max_salary

FROM EMPL

GROUP BY deptno;

Output

+--------+------------+

| deptno | max_salary |

+--------+------------+

| 20 | 3000 |

| 30 | 2850 |

| 10 | 5000 |

+--------+------------+

Question 21

Below are the customer and order tables :

Customers

customer id (PK)

first_name

last_name

address

city

state

zip

Orders

order id (PK)

order_date

amount

customer_id (FK)

List the total of customers' orders grouped by customer (id).

Answer

SELECT c.customer_id, COUNT(o.order_id) AS total_orders

FROM Customers c, orders o

WHERE c.customer_id = o.customer_id

GROUP BY c.customer_id;

Question 22

Below are the customer and order tables :

Customers

customer id (PK)

first_name

last_name

address

city

state

zip

Orders

order id (PK)

order_date

amount

customer_id (FK)

List the sum of the totals of orders grouped by customer and state.

Answer

SELECT c.customer_id, c.state, SUM(o.amount) AS total_order_amount

FROM Customers c, orders o

WHERE c.customer_id = o.customer_id

GROUP BY c.customer_id, c.state;

Question 23

Below are the customer and order tables :

Customers

customer id (PK)

first_name

last_name

address

city

state

zip

Orders

order id (PK)

order_date

amount

customer_id (FK)

List the customers (name) and the total amount of all their orders.

Answer

SELECT CONCAT(c.first_name, ' ', c.last_name) AS customer_name,

SUM(o.amount) AS total_order_amount

FROM Customers c, Orders o

WHERE c.customer_id = o.customer_id

GROUP BY c.customer_id;

Question 24

Schemas of tables EMPL, Dept, SalaryGrade are being shown below :

EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SALARYGRADE (Lowsal, Highsal, Grade)

DEPT (Deptno, DeptName, Location)

List the department names and the number of their employees.

Answer

SELECT d.DeptName AS Department_Name, COUNT(e.EMPNO) AS Number_of_Employees

FROM DEPT d, EMPL e

WHERE d.Deptno = e.DEPTNO

GROUP BY d.DeptName;

Question 25

Schemas of tables EMPL, Dept, SalaryGrade are being shown below :

EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

SALARYGRADE (Lowsal, Highsal, Grade)

DEPT (Deptno, DeptName, Location)

List the employee names and the name of their departments.

Answer

SELECT e.ENAME AS Employee_Name, d.DeptName AS Department_Name

FROM EMPL e, DEPT d

WHERE e.DEPTNO = d.Deptno;

Checkpoint 16.1

Question 1

How is database connectivity useful ?

Answer

When designing real-life applications, it's common to encounter scenarios where data stored in a database needs to be manipulated, retrieved, or updated through the application's interface. Database connectivity allows the application to establish a connection with the database, enabling seamless communication and interaction between the two.

Question 2

What is a connection ?

Answer

A connection (database connection object) controls the connection to the database. It represents a unique session with a database connected from within a script/program.

Question 3

What is a result set ?

Answer

The result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

Question 4

What is the package used for creating a Python database connectivity application.

Answer

mysql.connector is the package used for creating a Python database connectivity application.

Question 5

Which function/method do you use for establishing connection to database ?

Answer

The connect() function of mysql.connector is used for establishing connection to a MYSQL database.

Question 6

Which function/method do you use for executing an SQL query ?

Answer

The execute() function with cursor object is used for executing an SQL query.

Question 7

Which method do you use to fetch records from the result set ?

Answer

The fetchall() method, fetchmany() method, or fetchone() method can be used to fetch records from the result set.

Multiple Choice Questions

Question 1

In order to open a connection with MySQL database from within Python using mysql.connector package, ............... function is used.

open()
database()
connect()
connectdb()

Answer

connect()

Reason — The connect() function of mysql.connector is used for establishing connection to a MYSQL database.

Question 2

A database ............... controls the connection to an actual database, established from within a Python program.

database object
connection object
fetch object
query object

Answer

connection object

Reason — A database connection object controls the connection to the database. It represents a unique session with a database connected from within a script/program.

Question 3

The set of records retrieved after executing an SQL query over an established database connection is called ............... .

table
sqlresult
result
resultset

Answer

resultset

Reason — The result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

Question 4

A database ............... is a special control structure that facilitates the row by row processing of records in the resultset.

fetch
table
cursor
query

Answer

cursor

Reason — A database cursor is a special control structure that facilitates the row by row processing of records in the resultset, i.e., the set of records retrieved as per query.

Question 5

Which of the following is not a legal method for fetching records from database from within Python?

fetchone()
fetchtwo()
fetchall()
fetchmany()

Answer

fetchtwo()

Reason — The fetchall() method, fetchmany() method, or fetchone() method are the legal methods used for fetching records from the result set.

Question 6

To obtain all the records retrieved, you may use <cursor>. ............... method.

fetch()
fetchmany()
fetchall()
fetchmultiple()

Answer

fetchall()

Reason — The <cursor>.fetchall() method will return all the rows from the resultset in the form of a tuple containing the records.

Question 7

To fetch one record from the resultset, you may use <cursor>. ............... method.

fetch()
fetchone()
fetchtuple()
none of these

Answer

fetchone()

Reason — The <cursor>.fetchone() method will return only one row from the resultset in the form of a tuple containing a record.

Question 8

To fetch multiple records from the resultset, you may use <cursor>. ............... method.

fetch()
fetchmany()
fetchmultiple()
fetchmore()

Answer

fetchmany()

Reason — The <cursor>.fetchmany(<n>) method will return only the <n> number of rows from the resultset in the form of a tuple containing the records.

Question 9

To run an SQL query from within Python, you may use <cursor>. ............... method().

query()
execute()
run()
all of these

Answer

execute()

Reason — The <cursor>.execute() method is used to run an SQL query from within Python.

Question 10

To reflect the changes made in the database permanently, you need to run <connection>. ............... method.

done()
reflect()
commit()
final()

Answer

commit()

Reason — The <connection>.commit() method is used to permanently reflect the changes made in the database when working with database connections in Python.

Fill in the Blanks

Question 1

A database connection object controls the connection to the database. It represents a unique session with a database connected from within a script/program.

Question 2

A database cursor is a special control structure that facilitates the row by row processing of records in the resultset, i.e., the set of records retrieved as per query.

Question 3

The resultset refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

Question 4

After importing mysql.connector, first of all database connection is established using connect().

Question 5

After establishing database connection, database cursor is created so that the sql query may be executed through it to obtain resultset.

Question 6

The cursor.rowcount returns how many rows have been fetched so far using various fetch methods.

Question 7

The running of sql query through database cursor results into all the records returned in the form of resultset.

Question 8

A connectivity package such as mysql.connector must be imported before writing database connectivity Python code.

Question 9

connect() method establishes a database connection from within Python.

Question 10

cursor() method creates a cursor from within Python.

Question 11

execute() method executes a database query from within Python.

True/False Questions

Question 1

With creation of a database connection object from within a Python program, a unique session with database starts.

Answer

True

Reason — A database connection object controls the connection to the database, representing a unique session initiated from within a script or program.

Question 2

The sql query upon execution via established database connection returns the result in multiple chunks.

Answer

False

Reason — When an SQL query is executed via an established database connection, the result is returned as a single result set. The result set may contain multiple rows of data, but it is presented as a single unit rather than in multiple chunks.

Question 3

The cursor.rowcount gives the count of records in the resultset.

Answer

False

Reason — The cursor.rowcount returns how many rows have been so far retrieved through fetch...() methods from the cursor.

Question 4

The cursor.rowcount returns how many rows have been so far retrieved through fetch..() methods from the cursor.

Answer

True

Reason — The cursor.rowcount returns how many rows have been so far retrieved through fetch...() methods from the cursor.

Question 5

A DELETE or UPDATE or INSERT query requires commit() to reflect the changes in the database.

Answer

True

Reason — We need to run commit() with the connection object for DELETE, UPDATE, or INSERT queries that change the data of the database table, so that the changes are reflected in the database.

Assertions and Reasons

Question 1

Assertion. A database connection object controls the connection to a database.

Reason. A connection object represents a unique session with a database, connected from within a script/program.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
A database connection object controls the connection to the database, ensuring that the script or program can communicate effectively with the database. This connection object represents a unique session with a database connected from within a script/program.

Question 2

Assertion. A database cursor receives all the records retrieved as per the query.

Reason. A resultset refers to the records in the database cursor and allows processing of individual records in it.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
A database cursor is a special control structure that facilitates the row-by-row processing of records in the result set, which is the set of records retrieved as per the query. On the other hand, the result set refers to a logical set of records fetched from the database by executing an SQL query. The database cursor facilitates the processing of these records by allowing access to them individually.

Question 3

Assertion. The database cursor and resultset have the same data yet they are different.

Reason. The database cursor is a control structure and the resultset is a logical set of records.

Answer

(a)

Both Assertion and Reason are true and Reason is the correct explanation of Assertion.

Explanation
The database cursor and result set both have data from the database but serve different purposes and are distinct entities. A database cursor is a special control structure that facilitates the row-by-row processing of records in the result set, i.e., the set of records retrieved as per the query. On the other hand, the result set refers to a logical set of records that are fetched from the database by executing an SQL query and made available to the application program.

Question 4

Assertion. One by one the records can be fetched from the database directly through the database connection.

Reason. The database query results into a set of records known as the resultset.

Answer

(d)

Assertion is false but Reason is true.

Explanation
Records can be fetched from the database using a database connection. To fetch multiple records from the result set, we use the .fetchmany() method. To fetch one record from the result set, we use the .fetchone() method. To fetch all the records, we use the .fetchall() method. The result set refers to a logical set of records fetched from the database by executing an SQL query and made available to the application program.

Question 5

Assertion. The cursor rowcount returns how many rows have been retrieved so far through fetch...() methods.

Reason. The number of rows in a resultset and the rowcount are always equal.

Answer

(c)

Assertion is true but Reason is false.

Explanation
The cursor.rowcount returns how many rows have been so far retrieved through fetch...() methods from the cursor. However, the number of rows in a result set and the rowcount may not always be equal. This is because the rowcount attribute of the cursor only reflects the number of rows fetched by the fetch...() methods, not necessarily the total number of rows in the entire result set.

Type A: Short Answer Questions/Conceptual Questions

Question 1

What are the steps to connect to a database from within a Python application ?

Answer

The steps to connect to a database from within a Python application are as follows :

Step 1 : Start Python.

Step 2 : Import the packages required for database programming.

Step 3 : Open a connection.

Step 4 : Create a cursor instance.

Step 5 : Execute a query.

Step 6 : Extract data from result set.

Step 7 : Clean up the environment.

Question 2

Write code to connect to a MySQL database namely School and then fetch all those records from table Student where grade is ' A' .

Answer

Table Student of MySQL database School

rollno	name	marks	grade	section	project
101	RUHANII	76.8	A	A	PENDING
102	GEOGRE	71.2	B	A	SUBMITTED
103	SIMRAN	81.2	A	B	EVALUATED
104	ALI	61.2	B	C	ASSIGNED
105	KUSHAL	51.6	C	C	EVALUATED
106	ARSIYA	91.6	A+	B	SUBMITTED
107	RAUNAK	32.5	F	B	SUBMITTED

import mysql.connector as mysql

db_con = mysql.connect(

host = "localhost",

user = "root",

password = "tiger",

database = "School"

)

cursor = db_con.cursor()

cursor.execute("SELECT * FROM Student WHERE grade = 'A'")

student_records = cursor.fetchall()

for student in student_records:

print(student)

db_con.close()

Output

(101, 'RUHANII', 76.8, 'A', 'A', 'PENDING')

(103, 'SIMRAN', 81.2, 'A', 'B', 'EVALUATED')

Question 3

Predict the output of the following code :

import mysql.connector

db = mysql.connector.connect(....)

cursor = db.cursor()

sql1 = "update category set name = '%s' WHERE ID = %s" % ('CSS',2)

cursor.execute(sql1)

db.commit()

print("Rows affected:", cursor.rowcount)

db.close()

Answer

Table category

id	name
1	abc
2	pqr
3	xyz

Output

Rows affected: 1

SELECT * FROM category ;

+----+------+

| id | name |

+----+------+

| 1 | abc |

| 2 | CSS |

| 3 | xyz |

+----+------+

Explanation

This Python script uses the mysql.connector module to connect to MySQL database. It updates the 'name' field in the 'category' table where ID is 2 to 'CSS'. The cursor.execute() method executes the SQL query, db.commit() commits the changes, and cursor.rowcount gives the number of affected rows. Finally, db.close() closes the database connection, ending the Python interface with the MySQL database.

Question 4

Explain what the following query will do ?

import mysql.connector

db = mysql.connector.connect(....)

cursor = db.cursor()

person_id = input("Enter required person id")

lastname = input("Enter required lastname")

db.execute("INSERT INTO staff (person_id, lastname) VALUES ({}, '{}')".format(person_id, lastname))

db.commit()

db.close()

Answer

This Python script uses the mysql.connector package to connect to MySQL database. Then it prompts users for person ID and last name, inserts these values into the 'staff' table, using the INSERT INTO SQL statement. After that, it executes the SQL query using the db.execute method. The changes made by the query are then committed to the database using db.commit(), ensuring that the changes are saved permanently. Finally, db.close() closes the database connection, ending the Python interface with the MySQL database.

Question 5

Explain what the following query will do ?

import mysql.connector

db = mysql.connector.connect(....)

cursor = db.cursor()

db.execute("SELECT * FROM staff WHERE person_id in {}".format((1, 3, 4)))

db.commit()

db.close()

Answer

This Python script uses the mysql.connector package to connect to MySQL database. It executes an SQL SELECT query on the 'staff' table, retrieving all rows where the 'person_id' is 1, 3, 4 (using the IN clause). The db.commit() is unnecessary for a SELECT query since it doesn't modify the database, and db.close() closes the database connection, ending the Python interface with the MySQL database.

Type B: Application Based Questions

Question 1

Design a Python application that fetches all the records from Pet table of menagerie database.

Answer

import mysql.connector

db_con = mysql.connector.connect(host = "localhost",

user = "root",

passwd = "lion",

database = "menagerie")

cursor = db_con.cursor()

cursor.execute("SELECT * FROM Pet")

records = cursor.fetchall()

for record in records:

print(record)

db_con.close()

Output

('Fluffy', 'Harold', 'cat', 'f', datetime.date(1993, 2, 4), None)

('Claws', 'Gwen', 'cat', 'm', datetime.date(1994, 3, 17), None)

('Buffy', 'Harold', 'dog', 'f', datetime.date(1989, 5, 13), None)

('Fang', 'Benny', 'dog', 'm', datetime.date(1990, 8, 27), None)

('Bowser', 'Diane', 'dog', 'm', datetime.date(1979, 8, 31), datetime.date(1995, 7, 29))

('Chirpy', 'Gwen', 'bird', 'f', datetime.date(1998, 9, 11), None)

('Whistler', 'Gwen', 'bird', None, datetime.date(1997, 12, 9), None)

('Slim', 'Benny', 'snake', 'm', datetime.date(1996, 4, 29), None)

Question 2

Design a Python application that fetches only those records from Event table of menagerie database where type is Kennel.

Answer

import mysql.connector

db_con = mysql.connector.connect(host = "localhost",

user = "root",

passwd = "lion",

database = "menagerie")

cursor = db_con.cursor()

cursor.execute("SELECT * FROM event WHERE type = 'kennel'")

records = cursor.fetchall()

for record in records:

print(record)

db_con.close()

Output

('Bowser', datetime.date(1991, 10, 12), 'kennel', None)

('Fang', datetime.date(1991, 10, 12), 'kennel', None)

Question 3

Schema of table EMPL is shown below :

EMPL (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

Design a Python application to obtain a search criteria from user and then fetch records based on that from empl table. (given in chapter 13, Table 13.5)

Answer

Table Empl

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
8369	SMITH	CLERK	8902	1990-12-18	800	NULL	20
8499	ANYA	SALESMAN	8698	1991-02-20	1600	300	30
8521	SETH	SALESMAN	8698	1991-02-22	1250	500	30
8566	MAHADEVAN	MANAGER	8839	1991-04-02	2985	NULL	20
8654	MOMIN	SALESMAN	8698	1991-09-28	1250	1400	30
8698	BINA	MANAGER	8839	1991-05-01	2850	NULL	30
8839	AMIR	PRESIDENT	NULL	1991-11-18	5000	NULL	10
8844	KULDEEP	SALESMAN	8698	1991-09-08	1500	0	30
8882	SHIAVNSH	MANAGER	8839	1991-06-09	2450	NULL	10
8886	ANOOP	CLERK	8888	1993-01-12	1100	NULL	20
8888	SCOTT	ANALYST	8566	1992-12-09	3000	NULL	20
8900	JATIN	CLERK	8698	1991-12-03	950	NULL	30
8902	FAKIR	ANALYST	8566	1991-12-03	3000	NULL	20
8934	MITA	CLERK	8882	1992-01-23	1300	NULL	10

import mysql.connector

db_con = mysql.connector.connect(host = "localhost",

user = "root",

passwd = "fast",

database = "employeedb")

cursor = db_con.cursor()

search_criteria = input("Enter search criteria : ")

sql1 = "SELECT * FROM EMPL WHERE {}".format(search_criteria)

cursor.execute(sql1)

records = cursor.fetchall()

print("Fetched records:")

for record in records:

print(record)

db_con.close()

Output

Enter search criteria : job = 'clerk'

Fetched records:

(8369, 'SMITH', 'CLERK', 8902, datetime.date(1990, 12, 18), 800.0, None, 20)

(8886, 'ANOOP', 'CLERK', 8888, datetime.date(1993, 1, 12), 1100.0, None, 20)

(8900, 'JATIN', 'CLERK', 8698, datetime.date(1991, 12, 3), 950.0, None, 30)

(8934, 'MITA', 'CLERK', 8882, datetime.date(1992, 1, 23), 1300.0, None, 10)

Search This Blog

My Homework

Table Creation and Data Manipulation Commands

Comments

Post a Comment

Popular posts from this blog

The Road Not Taken

Chapter 4 The Basic Writing Skills

The Fun They Had