100+ Important SQL Queries with Answers

100+ Important SQL Queries

SQL Queries

Q1. Write SQL queries for the following based on table : TEACHER

Table : TEACHER

T_ID	T_Name	Subject	DOJ	Salary	Workload	Gender
101	Suman	English	12/03/2012	55000	20	F
103	Ravi	History	12/06/2000	70000	22	M
104	Alok	Computer	07/02/2014	60000	21	M
105	Rani	English	06/06/2015	50000		F

i) Display details of teachers with workload 20.

ii) Display the name of Computer Teacher.

iii) Display details of teachers in increasing order of salary.

iv) Display the total number of English teacher.

v) Increase the salary of History teacher by 10%.

vi)Display the detail of teacher with maximum workload.

SQL Queries

vii) Display the total salary paid to English Teacher.

viii) Display the Salary of Alok.

ix) Display the total number of subject from “Teacher” Table.

x) Display the details of all female teachers.

xi) Display the detail of Teacher whose workload is unknown.

xii) Display the detail of female teacher whose salary is more than 60000.

SQL Queries

Q2. Write SQL queries for the following based on table : ADMIN

Table : ADMIN

ID	Name	Designation	Contact	Email
101	Suman	Coordinator	325689	suman@gmail.com
103	Ravi	HOD	325654	ravi@gmail.com
104	Alok	IT Coordinator	325698	alok@yahoo.com

i) Display the detail of all Head of the Department (HOD)

ii) Display the detail of all staff who has Gmail account.

iii) Change the contact number of Alok to 555555.

iv) Display the designation and Email id of Suman.

v) Display the detail of staff whose name starts from alphabet ‘A’.

SQL Queries

Q3. Write SQL queries for the following :

Table : STIPEND

S_id	Name	Amount	Subject	Percentage	Position
1	Anuj	4000	English	65	P1
2	Ashu	4500	History	60	P2
3	Ruby	3500	English	55	P2
4	Raman	3800	Math	58	P3

a) Display the names of those students who has Position ‘P1’ sorted by NAME.

b) Display Name, Subject and Amount received in a year (as Annual Amount) assuming that monthly amount is given in table.

c) Count the number of students whose percentage is less than 60.

d) Display the different subjects from the table.

e) Display the detail of student whose name ends with ‘j’

f) Display the subject wise amount paid.

g) Give the output of following SQL statement based on table STIPEND:

1. Select MIN(Percentage) from Stipend where Subject=”English”;

2. Select SUM(Amount) from Stipend WHERE Position = ‘P2’;

3. Select AVG(Amount) from Stipend where Percentage>=60;

4. Select COUNT(distinct (SUBJECT)) from Stipend;

SQL Queries

Q4. Consider the following tables Sender and Receiver. Write SQL commands for the statements (a) to (d) and give the outputs for SQL queries (e) to (h).

Table : Sender

S_id	SName	S_Add	S_City
S101	Mr. Ram	201 Dayanand Vihar	New Delhi
S103	Mr. Kumar	125 TownHall	Mumbai
S104	Ms. Soni	20 New Hall	Mumbai
S106	Mr. Sinha	105 K Aptt	Kerela

Table : Receiver

R_id	S_id	RName	R_Add	R_City
R231	S101	Mr. Shikhar	H-131 AV Vihar	New Delhi
R235	S103	Mr. Sethi	A12 HK Nagar	Mumbai
R236	S101	Mr. Gautam	A-75 VM Nagar	Kolkata
R241	S106	Ms. Grover	M-91 Old Town	New Delhi

a) To display the Address of all Senders from New Delhi

b) To display the Receiver id, Sender Name, Sender Address , Receiver City, Receiver Address for every Receiver.

c) To display Sender details in descending order of Sender City.

d. To display number of Receiver from each city.

e) Select Count(S_City) from Sender;

f) SELECT S.SName, R.RName, S.S_Add From Sender S, Recipient R Where S.S_id = R.S_id AND R.R_City =’Mumbai’;

g) Select R_id, R_Add from Receiver Where R_City NOT IN (‘Mumbai’, ‘New Delhi’) ;

h) Select R_id, RName from Receiver Where S_id = ’S101’ or S_id=’S103’;

SQL Queries

Q5. Consider the following table Employee. Write SQL commands for the statements (a) to (d) and give the outputs for SQL queries (e) to (h).

Table : Employee

F_ID	Fname	Lname	Hire_date	Salary
102	Amit	Sethi	12-10-1998	12000
103	Suman	Vyas	24-12-1994	10000
104	Rakhi	Sinha	18-5-2001	12000
105	Rashmi	Malhotra	11-9-2004
106	Sulekha	Srivastava	5-6-2006	10000

a) To display details of those employees whose salary is greater than 12000.

b) Display the detail of “Rashmi”

c) Display the detail of Employee whose Salary is NULL.

d) Display the details of employees whose First name contains alphabet ‘a’.

e) Select avg(Salary) from Employee;

f) Select Fname, max(Salary) from Employee;

g) Select count(Salary) from Employee;

h) Select distinct(Salary) from Employee;

SQL Queries

Q6. Consider the following table Product. Write SQL commands for the statements (a) to (ag) and give the outputs for SQL queries (ah) to (ao).

TABLE : Product

P_id	P_name	Price	Qty	DOP	Profit (%)	Discount(%)
1	Keyboard	730	15	19-07-2020	20	10
2	Mouse	550	20	20-06-2019	15	5
3	RAM	2250	11	14-05-2017	10	5
4	Monitor	4500	25	3-05-2018	18	8
5	CPU	5000	10	06-06-2019	NULL	10

a) Display details of all products.

b) Display product id, product name and DOP of all products.

c) Display details of all products whose price is less than 3000.

d) Display the details of all products whose DOP(Date of Purchase) is before 01-01-2019

e) Display Profit and Discount of products whose DOP is after 25-06-2018.

f) Display the detail of all products whose profit is NULL.

g) Display the Product id, Product Name of all products whose profit is not NULL.

h) Display the Price of various products. A Price should appear only once.

i) Display the detail of all products whose profit is more than 10 and discount is less than 15.

j) Display product name and product price whose product id is not 3.

SQL Queries

k) Display product name, Quantity of all those product whose price is not equal to 3000 or profit is 10.

l) Display the detail of all products which are purchased in 2018.

m) Display the details of all products whose price is more than 2000 and less than 4000.

n) Display Product id, Product Name and Quantity of all those product whose Quantity is between 10 and 20 (including both)

SQL Queries

o) Display the detail of Keyboard, Mouse and RAM.

p) Display Product Name and Quantity of product whose price is 730 or 550.

q) Display Profit, Date of Purchase of products whose name ends with ‘d’

r) Display details of all those products whose price is more than 2000 and name contains ‘a’ alphabet.

s) Display Product name, Profit and Discount of all those product whose name does not contain ‘e’ as Second alphabet.

SQL Queries

t) Display detail of all products in ascending order of price.

u) Increase the price by 2 of all products whose profit is less than 10.

v) Display the Total Amount (price * Quantity) for all products.

w) Delete all the products which are purchased in year 2018.

x) Delete all product which are purchased after January 2018 and profit is less than 15.

y) Delete the record of product Keyboard and Mouse.

z) Delete all the records from table product.

aa) Display the structure of table.

ab) Add column Tax of type integer(2) in table product.

ac) Delete the column Tax from Product table.

ad) Display the total of price column from product table.

SQL Queries

ae) Count the product whose price is more than 1000.

af) Display the detail of costliest product.

ag) Display the detail of cheapest product.

ah) Select count(*) from Product ;

ai) Select count(Profit) from Product ;

aj) Select P_name, Price where Profit > 10 and Discount < 10;

ak) Select sum(Price) from Product ;

al) Select avg(Profit) from Product ;

am) Select min(DOP) from Product;

an) Select min(Price) from Product where P_name in (“CPU”, “RAM”, “Mouse”) ;

ao) Select P_name from Product where P_name like “%M% ;

Q7. Consider the following table Timetable. Write SQL commands for the following statements.(C_teacher = Class Teacher)

Table : Timetable

T_id	T_name	Subject	Workload	C_teacher	Gender	Class
1	Anita	Hindi	20	Yes	F	VIII
2	Aman	English	22	Yes	M	VII
3	Ruby	History	24	No	F	VIII
4	Seema	Hindi	20	Yes	F	IX

a) Display the detail of all teachers in ascending order of workload.

b) Display the detail of Class Teacher of Class VIII.

c) Display Name and Subject of all the Class teachers.

d) Display Name and Workload of Hindi Teacher.

e) Display the detail of teacher having maximum workload.

f) Display the detail of teacher having minimum workload.

g) Display the detail of female teacher teaching class VII.

h) Display total of Workload.

i) Display detail of teachers whose name ends with ‘e’

j) Display the class wise workload

k) Display subject wise number of teachers.

l) Display the detail of “Anita” and “Aman” (using IN operator )

m) Display the detail of Class VIII teacher whose workload is more than 20;

n) Display Name, Class and Workload of teacher whose workload is between 20 and 30 (including both )

o) Increase the workload of all Male teachers by 1.

p) Change the subject of Ruby from “History “to “Math”.

q) Delete the record of “Seema”

r) Delete the column C_teacher.

---

Disclaimer : I tried to give you the correct answers of “SQL Queries ” , but if you feel that there is/are mistakes in the answers of “SQL Queries “ given above, you can directly contact me at csiplearninghub@gmail.com.

---