100+ Important SQL Queries
Q1. Write SQL queries for the following based on table : TEACHER
Table : TEACHER
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.
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.
Q2. Write SQL queries for the following based on table : ADMIN
Table : ADMIN
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’.
Q3. Write SQL queries for the following :
Table : STIPEND
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:
- 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;
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
|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
|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’;
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
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;
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
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.
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)
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.
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.
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
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 firstname.lastname@example.org.
Class 12 Computer Science Sample Paper 2020-2021.
Class 12 Computer Science Sample Paper Marking Scheme
Class 12 Computer Science Test Series