Program no : 18 SQL COMMANDS – (WORKER, PAYLEVEL) Date : 12.01.
2022
Problem definition:
1. Consider the following tables WORKER and PAYLEVEL and answer the questions that
follows:
TABLE : WORKER
ECODE NAME DESIGN PLEVEL DOJ DOB
11 RadheShyam Supervisor P001 2004-09-12 1981-08-23
12 Chandernath Operator P003 2010-02-22 1987-07-12
13 Fizza Operator P003 2009-06-14 1983-10-14
14 Ameen Ahmed Mechanic P002 2006-08-21 1984-03-13
15 Sanya Clerk P002 2005-12-19 1983-06-09
18 Sarsa Supervisor P001 2010-01-20 1982-02-01
TABLE : PAYLEVEL
PLEVEL PAY ALLOWANCE
P001 26000 12000
P002 22000 10000
P003 12000 6000
Write SQL commands for the following statements:
1. To display the details of all the WORKERS in descending order of their DOJ.
Query:
SELECT * FROM WORKER ORDER BY DOJ DESC;
Output:
2. To display the NAME and DESIG of those WORKERS whose PLEVEL is either P001 or
P002.
Query:
SELECT NAME,DESIGN FROM WORKER WHERE PLEVEL = 'P001' OR PLEVEL = 'P002';
Output:
3. To display the number of workers whose PAY+ALLOWANCE is more than 30000 for every
PLEVEL.
Query:
select plevel,count(*) from paylevel where (pay+allowance)>30000 group by plevel ;
Output:
4. To increase the ALLOWANCE by 1000 where the pay is greater than 20000.
Query:
update paylevel set allowance=allowance+1000 where pay>20000;
Output:
5. To display the number of Workers designation wise.
Query:
select design, count(*) from worker group by design;
Output:
Program no : 19 SQL COMMANDS – (SPORTS,COACH) Date : 20.01.2022
Problem definition:
Consider the following tables SPORTS and COACH and answer the questions that
follows:
TABLE : SPORTS
Scode Sportsname Participants Prizemoney Scheduledate
101 Carrom 2 5000 2012-01-23
102 Badminton 2 12000 2011-12-12
103 Table Tennis 4 8000 2012-02-14
105 Chess 2 9000 2012-01-01
108 Lawn Tennis 4 25000 2012-03-19
TABLE : COACH
Code Name Scode
1 Ravi 101
2 Mohan 108
3 Sameer 101
4 Shikhar 103
Write SQL commands for the following statements:
(i) To display scode, the number of coaches for each scode from the table
coach and display scode in descending order.
Query:
select scode,count(*) from coach group by scode order by scode desc;
Output:
SCODE COUNT(*)
108 1
103 1
101 2
(ii) To display details of those sports and coachname which are having
Prizemoney more than 9000 and coachname ends with ‘n’.
Query:
select sportsname,name from sports s,coach c where s.scode=c.scode and
prizemoney>9000 ;
Output:
SPORTSNAME NAME
Lawn Tennis Mohan
(iii) To display the contents of the sports table with their coachname whose
schedule date is in the year 2012.
Query:
select s.scode,sportsname,participants,prizemoney,scheduledate,name from sports
s,coach c where scheduledate like '%2012' and s.scode=c.scode;
Output:
Scode Sportsname Participants Prizemoney Scheduledate Name
101 Carrom 2 5000 2012-01-23 Ravi
108 Lawn Tennis 4 25000 2012-03-19 Mohan
101 Carrom 2 5000 2012-01-23 Sameer
103 Table Tennis 4 8000 2012-02-14 Shikhar
(iv) To display number of different participants from the table sports.
Query:
select distinct(participants) from sports;
Output:
PARTICIPANTS
2
4
(v) Increase the Participants by 6 for the sports carom, chess and badminton.
Query:
update sports set participants=participants+6 where sportsname in
('carrom','chess','badminton');
Output:
Scode SPORTSNAME PARTICIPANTS PRIZEMONEY SCHEDULEDATE
101 Carrom 8 5000 2012-01-23
102 Badminton 8 12000 2011-12-12
103 Tabletennis 4 8000 2012-02-14
105 Chess 8 9000 2012-01-01
108 Lawntennis 4 25000 2012-03-19
Program no : 20 SQL COMMANDS – (COMPANY,CUSTOMER) Date : 27.01.2022
Problem definition:
Consider the following tables SPORTS and COACH and answer the questions that
follows:
TABLE :COMPANY
Cid Name City ProductName
111 Sony Delhi TV
222 Nokia Mumbai Mobile
333 Onida Delhi TV
444 Sony Mumbai Mobile
555 BlackBerry Madras Mobile
666 Dell Delhi Laptop
TABLE :CUSTOMER
Custid Name Price Qty Cid
101 Rohan Sharma 70000 20 222
102 Deepak Kumar 50000 10 666
103 Mohan Kumar 30000 5 111
104 SahilBansal 35000 3 333
105 NehaSoni 25000 7 444
106 SonalAggarwal 20000 5 333
107 Arjun Singh 50000 15 666
i) To display those company name which are having prize less than 30000.
Query:
select company.name from company,customer where company.cid=customer.cid
and price<30000;
Output:
ii) To display the name of the companies in reverse alphabetical order.
Query:
select name from company order by name desc;
Output:
iii) To increase the prize by 1000 for those customer whose name starts with ‘S’.
Query:
update customer set price = price+1000 where name like 'S%';
Output:
iv) To add one more column totalprice with decimal(10,2) to the table customer.
Query:
alter table customer add totalprice decimal(10,2);
Output:
v) Display the product name, city, price where the productname is mobile.
Query:
select productname,city,price from customer, company where productname
='mobile' and customer.cid = company.cid;
Output:.
