SQL-WorkSheet.

📌 Table: ORDERS

OID CUSTOMER PRODUCT QTY PRICE

1 Amit Pen 10 20

2 Neha Pencil 15 5

3 Raj Notebook 8 50

4 Amit Pen 12 20

5 Neha Pencil 20 5

6 Simran Eraser 5 8

❓ Q1. Display the total quantity ordered for each product.

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS TotalQty

FROM ORDERS

GROUP BY PRODUCT;

Output:

PRODUCT TotalQty

Pen 22

Pencil 35

Notebook 8

Eraser 5

❓ Q2. Display the number of orders placed by each customer.

✅ Answer:

SELECT CUSTOMER, COUNT(*) AS NoOfOrders

FROM ORDERS

GROUP BY CUSTOMER;

Output:

CUSTOMER NoOfOrders

Amit 2
SQL-WorkSheet.

CUSTOMER NoOfOrders

Neha 2

Raj 1

Simran 1

❓ Q3. Display the total quantity of each product where total quantity is more than 10.

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS TotalQty

FROM ORDERS

GROUP BY PRODUCT

HAVING SUM(QTY) > 10;

Output:

PRODUCT TotalQty

Pen 22

Pencil 35

❓ Q4. Display products whose average price is greater than or equal to 10.

✅ Answer:

SELECT PRODUCT, AVG(PRICE) AS AvgPrice

FROM ORDERS

GROUP BY PRODUCT

HAVING AVG(PRICE) >= 10;

Output:

PRODUCT AvgPrice

Pen 20.0

Notebook 50.0

❓ Q5. Display product name, total quantity and number of orders for each product where number
of orders is more than 1.

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS TotalQty, COUNT(*) AS NoOfOrders

SQL-WorkSheet.

FROM ORDERS

GROUP BY PRODUCT

HAVING COUNT(*) > 1;

Output:

PRODUCT TotalQty NoOfOrders

Pen 22 2

Pencil 35 2

📌 Table: ORDERS

OID CUSTOMER PRODUCT QTY PRICE

1 Amit Pen 10 20

2 Neha Pencil 15 5

3 Raj Notebook 8 50

4 Amit Pen 12 20

5 Neha Pencil 20 5

6 Simran Eraser 5 8

🎯 CBSE-Style Questions with Answers

Q1. Write a query to display Product and Total Quantity ordered for each Product.

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS "TOTAL QUANTITY"

FROM ORDERS

GROUP BY PRODUCT;

Q2. Write a query to display Customer name and Number of Orders placed by each Customer.

✅ Answer:

SELECT CUSTOMER, COUNT(*) AS "NO. OF ORDERS"

SQL-WorkSheet.

FROM ORDERS

GROUP BY CUSTOMER;

Q3. Write a query to display Product and Total Quantity where Total Quantity is more than 10.

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS "TOTAL QUANTITY"

FROM ORDERS

GROUP BY PRODUCT

HAVING SUM(QTY) > 10;

Q4. Write a query to display Product name and Average Price where Average Price is greater than
or equal to 10.

✅ Answer:

SELECT PRODUCT, AVG(PRICE) AS "AVERAGE PRICE"

FROM ORDERS

GROUP BY PRODUCT

HAVING AVG(PRICE) >= 10;

Q5. Write a query to display Product, Total Quantity and Number of Orders for each Product
where Number of Orders is more than 1.

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS "TOTAL QUANTITY", COUNT(*) AS "NO. OF ORDERS"

FROM ORDERS

GROUP BY PRODUCT

HAVING COUNT(*) > 1;

Q6. Write a query to display Product and Maximum Quantity ordered for each Product.

✅ Answer:

SELECT PRODUCT, MAX(QTY) AS "MAXIMUM QUANTITY"

FROM ORDERS

GROUP BY PRODUCT;
 SQL-WorkSheet.

Q7. Write a query to display Product name and Total Quantity where Total Quantity is between 10
and 30 (inclusive).

✅ Answer:

SELECT PRODUCT, SUM(QTY) AS "TOTAL QUANTITY"

FROM ORDERS

GROUP BY PRODUCT

HAVING SUM(QTY) BETWEEN 10 AND 30;

Q8

Consider the following table ITEMS:

ITEMNO ITEMNAME PRICE QTY

101 Pen 20 10

102 Pencil 10 20

103 Eraser 5 50

104 File 50 5

Write SQL commands to:

(i) Display ITEMNAME and PRICE in descending order of PRICE.
(ii) Display the total quantity of all items.

ALTER QUERY

1. Adding a new column :

ALTER TABLE STUDENT ADD PHONENO INTEGER;

2. Adding a column with default value

ALTER TABLE STUDENT ADD CITY CHAR(6) DEFAULT “DELHI”;

3. Modifying an existing column

ALTER TABLE STUDENT MODIFY CITY VARCHAR(10);

4. Renaming a column

ALTER TABLE STUDENT CHANGE CITY STATE VARCHAR(10);

5. Removing a column

ALTER TABLE STUDENT DROP STATE;

6. Adding Primary Key Constraint

ALTER TABLE STUDENT ADD PRIMARY KEY(ROLLNO);

 SQL-WorkSheet.

7. Deleting Primary Key Constraint

ALTER TABLE EMPLOYEE DROP PRIMARY KEY;

8. Delete a constraint

ALTER TABLE Persons DROP CONSTRAINT UC_Person;

9. Adding a constraint

ALTER TABLE table_name ADD CONSTRAINT New_mail UNIQUE(mail);

PRACTICE QUESTIONS

1. BOOKNO, BOOKNAME, QUANTITY, PRICE, AUTHOR

Write the SQL statement to add a new column REVIEW to store the reviews of the book.

ALTER TABLE BOOKS ADD REVIEW VARCHAR (255);

2. Consider the table Persons whose fields are P_ID, LastName, FirstName, Address,

City. Add a new row but add data only in the P_Id, LastName and FirstName as 5, Peterson, Kari
respectively.

INSERT INTO Persons(P_ID, LastName, FirstName) VALUES(5, 'Peterson', 'Kari')

3. If a database "Employee" exists, which MySql command helps you to start working in that database?

USE EMPLOYEE;

4. An attribute A of datatype varchar(20) has the value “Amit” .The Attribute B of datatype char(20) has
value ”Karanita” . How many characters are occupied in attribute A ? How many characters are occupied in
attribute B?

4 characters occupied in attribute A . 20 characters in attribute B

5. Ms. Shruti has create a table GAMES having attributes ID, GAME, NO_PLAYERS. Later she wants to add a
new attribute DURATION of integer type in the table. Write the query for it.

ALTER TABLE GAMES ADD DUARTION INTEGER;

6. Benjamin a database administrator created a table with few columns. He wants to stop duplicating the
data in the table. Suggest how he can do so.

He can use primary key for a column

7. Write an SQL command to change the data type of a column named price

to number (10,2) in a table named stationary

Alter table stationary modify price number(10,2);

 SQL-WorkSheet.

8. Write an SQL command to change the values of all the rows of the column price of table stationary to
Null

Update table stationary set price=Null;

9. What constraint should be applied on a column of a table so that it becomes compulsory to insert the
value

NOT NULL / PRIMARY KEY

10. Write an SQL command to assign F_id as primary key in the table named flight

ALTER TABLE flight ADD PRIMARY KEY(F_id);

11. Write an SQL command to remove the column remarks from the table name customer.

ALTER TABLE CUSTOMER DROP REMARKS;

12. What constraint/s should be applied to the column in a table to make it as alternate key?

UNIQUE , NOT NULL

13. What constraint should be applied to a table column to ensure that all values in that column must be
unique and not NULL?

Use the UNIQUE constraint along with the NOT NULL / PRIMARY KEY constraint

14. What constraint should be applied to a table column to ensure that it can have multiple NULL values but
cannot have any duplicate non-NULL values?

Use the UNIQUE constraint alone

15. Write an SQL command to drop the unique constraint named unique_email from a column named
email in a table called Users.

ALTER TABLE Users DROP CONSTRAINT unique_email;

16. Write an SQL command to add a unique constraint to the email column of an existing table named
 SQL-WorkSheet.

Users, ensuring that all email addresses are unique.

ALTER TABLE Users ADD CONSTRAINT unique_email UNIQUE (email);

17. What constraint should be applied on a table column so that NULL is not allowed in that column, but
duplicate values are allowed.

NOT NULL

18. What constraint should be applied on a table column so that duplicate values are not allowed in that
column, but NULL is allowed.

UNIQUE

19. Write an SQL command to remove the Primary Key constraint from a table, named MOBILE. M_ID is the
primary key of the table.

ALTER TABLE MOBILE DROP PRIMARY KEY;

20. Write an SQL command to make the column M_ID the Primary Key of an already existing table, named
MOBILE

ALTER TABLE MOBILE ADD PRIMARY KEY (M_ID);

JOINS

 CARTESIAN PRODUCT

o SELECT * FROM T1,T2 (degree – no. of columns in T1 + no. of columns in T2)(cardinality –

no. of rows in T1 * no. of rows in T2)

 EQUI JOIN

o SELECT * FROM T1,T2 WHERE T1.NO = T2.NO;

 NATURAL JOIN

o SELECT * FROM T1 NATURAL JOIN T2

 There a difference between UNIQUE and PRIMARY KEY constraint. - Unique allows NULL values
whereas PRIMARY KEY does not allow.

 Count(<column name>) does not take NULL values if column is specified. (Count(rollno))

 Count(*) takes the NULL values also

 Order of flow – where , group by, having, order by

 SQL-WorkSheet.

DATE FORMAT

 Select * from t1 where year(DOB) = 2019;

 Select * from t1 where DOB like “2019%”;

 Select * from t1 where DOB BETWEEN 2019-01-01 AND 2020-01-01

Differentiate between PRIMARY KEY and UNIQUE constraint in SQL.

Select sum(SALARY), city from PERSONS group by PERSONS having

count(CITY)<=2;
Select FIRSTNAME from PERSONS where GENDER=’M’ order by SALARY desc;
Select * from PERSONS where CITY like ‘%l%’;
Select SALARY+(SALARY*5)/100 from PERSONS;

(4 x 1 mark for each correct query)

OR
 SQL-WorkSheet.

Consider the table EMPLOYEE:

EID ENAME DEPT SALARY

1 Ravi HR 30000

2 Neha IT 45000

3 Raj IT 55000

4 Anu HR 25000

Write SQL commands to:

(i) Increase salary of employees in IT department by 5000.
(ii) Delete details of employees working in HR department.
 SQL-WorkSheet.

Consider the table PRODUCTS:

PID PNAME CATEGORY PRICE QTY

1 Pen Stationery 20 50

2 File Stationery 100 10

3 Mouse Electronics 500 5

4 Laptop Electronics 55000 2

Write the output of the following queries:

a)SELECT PNAME, PRICE FROM PRODUCTS WHERE CATEGORY='Stationery' AND PRICE > 50;

b)SELECT CATEGORY, SUM(QTY) FROM PRODUCTS GROUP BY CATEGORY;

c)SELECT PNAME FROM PRODUCTS WHERE PNAME LIKE '%e';

Saman has been entrusted with the management of Law University

Database. He needs to access some information from FACULTY and

COURSES tables for a survey analysis. Help him extract the following

information by writing the desired SQL queries as mentioned below.

Table: FACULTY

F_ID FName LName Hire_Date Salary

102 Amit Mishra 12-10-1998 12000

103 Nitin Vyas 24-12-1994 8000

104 Rakshit Soni 18-5-2001 14000

105 Rashmi Malhotra 11-9-2004 11000

106 Sulekha Srivastava 5-6-2006 10000

Table: COURSES
 SQL-WorkSheet.

C_ID F_ID CName Fees

C21 102 Grid Computing 40000

C22 106 System Design 16000

(4)

Page: 9/11

C23 104 Computer Security 8000

C24 106 Human Biology 15000

C25 102 Computer Network 20000

C26 105 Visual Basic 6000

(I) To display complete details (from both the tables) of those Faculties

whose salary is less than 12000.

(II) To display the details of courses whose fees is in the range of 20000

to 50000 (both values included).

(III) To increase the fees of all courses by 500 which have "Computer"

in their Course names.

(IV) (A) To display names (FName and LName) of faculty taking System

Design.

OR

(B) To display the Cartesian Product of these two tables.

Consider the table ORDERS as given below

O_Id C_Name Product Quantity Price

1001 Jitendra Laptop 1 12000

1002 Mustafa Smartphone 2 10000

1003 Dhwani Headphone 1 1500

Note: The table contains many more records than shown here.

A) Write the following queries:

 SQL-WorkSheet.

(I) To display the total Quantity for each Product, excluding

Products with total Quantity less than 5.

(II) To display the orders table sorted by total price in descending

order.

(III) To display the distinct customer names from the Orders table.

Consider the following tables:

TABLE: CUSTOMER

CID CNAME CITY

C01 Ravi Delhi

C02 Neha Mumbai

C03 Raj Chennai

C04 Anu Delhi

TABLE: ORDERS

OID CID AMOUNT

101 C01 1500

102 C03 2000

103 C01 2500

104 C02 3000

Write SQL commands for the following:

a) Display CNAME and AMOUNT of all customers who have placed orders.
b) Display CNAME and CITY of customers who have not placed any order.
c) Display CITY-wise total order AMOUNT.

Consider the table EMPLOYEE:

EID ENAME DEPT SALARY DOJ

101 Ravi HR 30000 2023-02-10

 SQL-WorkSheet.

102 Neha IT 45000 2022-07-15

103 Raj IT 55000 2024-03-20

104 Anu HR 25000 2023-12-05

105 Meera Sales 40000 2021-06-12

Write SQL commands for the following:

a) Display names and department of employees whose salary is more than 35000.
b) Display department-wise average salary.
c) Display names of employees who joined after ‘2023-01-01’.
d) Display number of employees in each department having more than 1 employee.
e) Display all employee details sorted by ENAME in ascending order.

Consider the following tables: 5

TABLE: CUSTOMER

CID CNAME CITY

C01 Ravi Delhi

C02 Neha Mumbai

C03 Raj Chennai

C04 Anu Delhi

TABLE: ORDERS

OID CID AMOUNT

16
101 C01 1500

102 C03 2000

103 C01 2500

104 C02 3000

Write SQL commands for the following:

a) Display CNAME and AMOUNT of all customers who have placed orders.
b) Display CITY-wise total order amount.
c) Display details of customers who have not placed any order.
d) Display customer names and amount of orders greater than 2000.
e) Display names of customers in alphabetical order.
 SQL-WorkSheet.

Consider the following table BOOKS: 4

BID TITLE AUTHOR PRICE QTY

101 Python Basics Sumita Arora 450 10

102 Learn SQL Rajesh Mal 300 5

103 AI Revolution John Smith 600 8

104 DBMS Concepts Sumita Arora 500 12

105 Cyber Safety Neha Sharma 250 15

Write the output of the following SQL queries:

(a)

SELECT TITLE, AUTHOR

FROM BOOKS

WHERE PRICE > 400;

(b)

SELECT AUTHOR, COUNT(*)

FROM BOOKS

GROUP BY AUTHOR;

(c)

SELECT TITLE, PRICE*QTY AS TOTAL_VALUE

FROM BOOKS

WHERE QTY > 8;

(d)

SELECT TITLE

FROM BOOKS

ORDER BY PRICE DESC;