DBMS Queries

Click Here To Download All DBMS Queries

S.No	Topic	Pg.No
	Questions on emp and dept Tables
1.	List ename,sal,PF,HRA,DA and gross.Order the lost in ascending order of gross	3
2.	List the maximum sal of the employee working as a “salesman”	4
3.	List the deptno and total sal payable in each department	5
4.	List total, minimum, maximum & average sal of emp jobwise for deptno=20	6
5.	List the employees belonging to the department of “MILLER”	7
6.	List the name of the employee drawing the highest salary	8
	Altering, Drop, Truncate and Rename
7.	Add a column called telephone to the client master table	9
8.	Change the size of selling price to 10,2	10
9.	Destroy table Client master with its data	11
10.	Change name of Salesman master table to Salesman	12
11.	Empty the Product table	13
	Adding Constraints to the Table
12.	Add a PK constraint on client no in the client master table.	14
13.	Add a constraints that the client no should start with ‘C’	15
14.	Modify the table client master so that name column does not take null values	16
15.	Drop and recreate table product with prodno as PK and it should start with ‘P’	17
16.	Add constraint to SP and CP in product table that will not accept null and 0	18
17.	Make necessary modifications to salesman master table so that salesman no should be PK & start with ‘S’ & tgttoget and sal should not be null and > 0	19
	Simple Queries
18.	Find out the names of all clients	20
19.	Display the name, city and state of all clients and salesman	21
20.	Display all the products	22
21.	Display all the clients located in Mumbai	23
	Updation
22.	Change the city of client no ‘C103’ to ‘Chennai’	24
23.	Modify the Balance due for client ‘C101’to Rs.10000	25
24.	Change the cost price of product ‘Shirt’ to Rs.950	26
25.	Change the city of all the salesman to ‘Pune’	27
	Deletion
26.	Delete salesman from salesman master table whose sal are equal to Rs.3500	28
27.	Delete all products whose qoh is less than 100	29
28.	Delete the clients who belong to state ‘AP’	30
	Complex Queries
29.	Find the names of salesman who have salary=3000	31
30.	List the names of all clients having ‘a’ as the second letter in their names	32
31.	List the clients who stay in a city starting with ‘h’	33
32.	List all clients who stay in ‘Mumbai’ or ‘Hyderabad’	34
33.	List all clients who have balance due is greater than 10000	35
34.	List the products whose SP  is greater than 500 and less than or equal to 750	36
35.	List products whose cost price is > 500.Calculate new cost price as costprice * 100 and change the column as newcostprice.	37
36.	List the names of all clients who are not in ‘AndhraPradesh’	38
37.	Count the total number of orders.	39
38.	Calculate the average price of all the products	40
39.	Determine the maximum and minimum product rates.	41
40.	Count the no of products having price less than or equal to 500	42
	Group by and Having
41.	Print the desc and total qty sold for each product	43
42.	Find the value of each product sold	44
43.	Calculate average qty sold for each client that has a max order value of 15000	45
	Joins and Correlation
44.	Find out the products which have been sold to client with name ‘Satya’	46
45.	Find the names of clients who have purchased ‘Shirts’	47
46.	List products & orders from customers who have ordered < 5 pieces of ‘Shirts’	48
47.	Find the products and qty for orders placed by clientno ‘C0034’ and ‘C0099’	49
Subqueries
48.	Find the Product no and Description of non-moving Products	50
49.	List customer name,address,city,state for client who placed order no O0034	51
50.	List the Clients who have placed order worth more than 10,000	52