SUDHARSANAM

VIDYAASHRAM
(CBSE Affiliation No.1930715)
ISO 9001:2015 Certified Institution

COMPUTER SCIENCE
CLASS 12TH SCIENCE (CBSE)

Submitted by :N.J.ELANGOVAN
Reg.No :

1
 MY SQL QUERIES

SUBMITTED TO CBSE FOR THE FULFILLMENT OF REQUIREMENT

OF AISSCE 2021-22

GUIDED BY SUBMITTED BY
Mr. D. MUKESH KANNA N.J.ELANGOVAN
(PGT Computer Science) CLASS-XII SEC- C2
REGISTER NO-

YEAR - 2021-2022
SUDHARSANAM VIDYAASHRAM
VEERARAGHAVAPURAM
THIRUVALLUR

2
 BONAFIDE CERTIFICATE

REGISTER NUMBER

This is to certify that ______________, a student of class XII has

successfully completed the MY SQL QUERIES under the guidance of

Mr. D.MUKESH KANNA (PGT-COMPUTER SCIENCE) during the

year 2021-22.

Submitted for All India Senior Secondary School Certificate for

Practical Examination held on ____________

Internal Examiner External Examiner

Date: Signature of Principal

3
1. Create a table worker45 with a following attributes:
ecode,name,design,plevel,doj,dob.
SQL> create table worker45(ecodeint,name char(15),design char(12),plevel char(4), dojdate,dob date);

Table created.

2. Insert values for the table worker45.

SQL> insert into worker45 values(11,'radhe shyam','supervisor','p001','13-sep-2004','23-aug-1981');

1 row created.

SQL> insert into worker45 values(12,'chander nath','operator','p002','22-feb-1981','12-july-1987');

1 row created.

SQL> insert into worker45 values(13,'fizza','operator','p003','14-june-2010','14-oct-1983');

1 row created.

SQL> insert into worker45 values(14,'ameen ahmed','mechanic','p004','21-aug-2006','13-mar-1984');

1 row created.

SQL> insert into worker45 values(15,'sanya','clerk','p005','19-dec-2005','09-jun-1983');

1 row created.

3. create a table paylevel45 with the following

attributes:plevel,pay,allowance.
SQL> create table paylevel45 (plevelchar(4),pay int,allowanceint);

Table created.

4
4. Insert the values for the table paylevel45.
SQL> insert into paylevel45 values('p001',26000,12000);

1 row created.

SQL> insert into paylevel45 values('p002',22000,10000);

1 row created.

SQL> insert into paylevel45 values('p003',12000,6000);

1 row created.

5. Display all the tuples of the table worker45.

SQL> select*from worker45;

ECODE NAME DESIGN PLEV DOJDOB

---------- --------------- ------------------ -------------- -------------

11. radheshyamsupervisor p001 13-SEP-0423-AUG-81

12. chandernathoperator p002 22-FEB-8112-JUL-87

13. fizza operator p003 14-JUN-1014-OCT-83

14. ameenahmedmechanic p004 21-AUG-06 13-MAR-84

15. sanyaclerk p005 19-DEC-0509-JUN-83

5
6. Display all the tuples of the table paylevel45.
SQL> select*from paylevel45;

PLEV PAY ALLOWANCE

-------------- ----------

p001 26000 12000

p002 22000 10000

p003 12000 6000

7. Display distinct designation from the table worker45.

SQL> select distinct design from worker45;

DESIGN

------------

clerk

mechanic

supervisor

operator

8. Display all the values of the attributes designation from the worker45.
SQL> select all design from worker45;

DESIGN

------------

supervisor

operator

operator

mechanic

6
clerk

9. To display the details of name,designation whose plevel is p003.

SQL> select name,design from worker45 where plevel='p003';

NAME DESIGN

------------ ------------

fizza operator

10.To display the details of name and designation whose plevel is either p001
or p002.
SQL> select name,design from worker45 where plevel='p001' or plevel='p002';

NAME DESIGN

--------------- ------------

radheshyamsupervisor

chandernathoperator

11.To display the details of the name and designation whose plevel are p001
and p002.
SQL> select name,design from worker45 where plevel in('p001','p002');

NAME DESIGN

--------------- ------------

radheshyam supervisor

chandernath operator

7
12.To display the details ofthe name and designation whose plevel are not
p001 and p002.
SQL> select name,design from worker45 where plevel not in('p001','p002');

NAME DESIGN

--------------- ------------

fizzaoperator

ameenahmed mechanic

sanyaclerk

13. Display all details from paylevel45 whose allowances ranges from 5000 to
10000(inclusive of both values)

SQL> select*from paylevel45 where allowance between 5000 and 10000;

PLEV PAY ALLOWANCE

------------ ----------

p002 22000 10000

p003 12000 6000

14.Display all details from paylevel45 whose allowances ranges from 5000 to
10000(exclusive of both values)
SQL> select*from paylevel45 where allowance >5000 and allowance <10000;

PLEV PAY ALLOWANCE

------------- -----------------
p003 12000 6000

8
15.Display all details from paylevel45 whose allowances not in the range from
5000 to 10000.
SQL> select*from paylevel45 where allowance not between 5000 and 10000;

PLEV PAY ALLOWANCE

---- ---------- ----------

p001 26000 12000

16. Display all details from worker45 whose name starts with r.

SQL> select*from worker45 where name like 'r%';

ECODE NAME DESIGN PLEV DOJ DOB

---------- --------------- ------------ ------------- ---------

11 Radheshyamsupervisor p00113-SEP-0423-AUG-81

17.Display all the details of worker45 whose name not starts with r.
SQL> select*from worker45 where name not like 'r%';

ECODE NAME DESIGN PLEV DOJ DOB

---------- --------------- ------------ ---- --------------------

12 chandernath operator p002 22-FEB-81 12-JUL-87

13 fizza operator p003 14-JUN-10 14-OCT-83

14 ameenahmed mechanic p004 21-AUG-06 13-MAR-84

15sanyaclerk p005 19-DEC-0509-JUN-83

9
18. Display all the details of worker45 in descending order of designation.
SQL> select*from worker45 order by design desc;

ECODE NAME DESIGN PLEVDOJ DOB

---------- --------------- ------------ ---- --------- ---------

11radheshyamsupervisor p00113-SEP-04 23-AUG-81

12chandernathoperator p002 22-FEB-81 12-JUL-87

13fizzaoperator p003 14-JUN-10 14-OCT-83

14ameenahmedmechanic p00421-AUG-06 13-MAR-84

15sanyaclerk p005 19-DEC-05 09-JUN-83

19.To display the details of all worker45 in ascending order with name whose
ecode is more than 13
SQL> select*from worker45 where ecode>13 order by name;

ECODE NAME DESIGN PLEVDOJ DOB

----------------------- ---------------- --------- --------

14ameenahmedmechanic p004 21-AUG-06 13-MAR-84

15sanyaclerk p00519-DEC-05 09-JUN-83.

10
20.Display the details of all workers in ascending order of name and
descending order of design.
SQL> select*from worker45 order by name asc,designdesc;

ECODE NAME DESIGN PLEV DOJ DOB

---------- --------------- ------------ ---- -----------------------

14 ameenahmedmechanic p00421-AUG-0613-MAR-84

12chandernathoperator p00222-FEB-81 12-JUL-87

13fizzaoperator p003 14-JUN-1014-OCT-83

11radheshyamsupervisor p001 13-SEP-0423-AUG-81

15sanyaclerk p005 19-DEC-0509-JUN-83

21.Display the total amount of allowance from paylevel45 whose pay is more
than 15000.
SQL> select sum(allowance) from paylevel45 where pay>15000;

SUM(ALLOWANCE)

--------------

22000

11
22.Display the number of records present in the table worker45 and
paylevel45
SQL> select count(*) from worker45;

COUNT(*)

----------

SQL> select count(*) from paylevel45;

COUNT(*)

----------

23.Display the maximum and the minimum pay of the worker45 in paylevel.
SQL> select max(pay),min(pay) from paylevel45;

MAX(PAY) MIN(PAY)

------------------------

26000 12000

24 .Display the average allowances from the paylevel.

SQL> select avg(allowance) from paylevel45;

AVG(ALLOWANCE)

--------------------------

9333.3333

12
25. Display the number of designation of the worker that belongs to different
plevel.
SQL> select count(distinct design) from worker45;

COUNT(DISTINCTDESIGN)

--------------------------------------

26. Display the number of names present in the table worker.

SQL> select count(all name) from worker45;

COUNT(ALLNAME)

--------------------------

27. Display the number of plevel of each worker from each designation.
SQL> select count(plevel),plevel from worker45 group by plevel;

COUNT(PLEVEL) PLEV

----------------------- -----

1 p004

1 p001

1 p002

1 p005

1 p003

13
28. Display the number of designation of each worker whose count is more
than 1.
SQL> select count(design),design from worker45 group by design having count(design)>1;

COUNT(DESIGN)DESIGN

----------------------- ------------

2 operator

29. Display plevel,pay+allowance from the table paylevel45.

SQL> select plevel,pay+allowance from paylevel45;

PLEVPAY+ALLOWANCE

------- --------------------------

p001 38000

p002 32000

p003 18000

30. Display plevel,pay+allowance from the table paylevel45 by placing the

symbol $.
SQL> select plevel,pay+allowance,'$' from paylevel45;

PLEVPAY+ALLOWANCE '

---- --------------------------

p001 38000 $

p002 32000 $

p003 18000 $

14
31. Create a table workerinfofrom table worker with the attributesdoj,dob
and name .
SQL> create table workerinfo AS (select name, doj, dob from worker45 where name =
chandernath)

SQL> select*from workerinfo45;

NAME DOJ DOB

--------------- --------- ---------

chandernath22-FEB-8112-JUL-87

32. Add a column experience in the table worker45.

SQL> alter table worker45 add experience int;

Table altered.

33. Change the datatype of allowance in paylevel table to decimal.

alter table paylevel45 modify (allowance dec(7,2));

table modified.

15
34.Insert the values for the column experience and display the table worker45.
SQL> update worker45 set experience=10 where ecode=11;

1 row updated.

SQL> update worker45 set experience=12 where ecode=12;

1 row updated.

SQL> update worker45 set experience=11 where ecode=13;

1 row updated.

SQL> update worker45 set experience=9 where ecode=14;

1 row updated.

SQL> update worker45 set experience=10 where ecode=15;

1 row updated.

SQL> select* from worker45;

ECODE NAME DESIGN PLEVDOJ DOB EXPERIENCE

---------- --------------------------- --------- ------------------ --------------

11 radheshyamsupervisor p001 13-SEP-04 23-AUG-81 10

12 chandernathoperator p002 22-FEB-8112-JUL-87 12

13 fizzaoperator p003 14-JUN-1014-OCT-83 11

14 ameenahmed mechanic p00421-AUG-06 13-MAR-84 9

15sanyaclerk p005 19-DEC-05 09-JUN-83 15

16
35.To display the name and pay of your worker45 whose ecode is less than 13.
SQL> selectname,pay from worker45 w,paylevel45 p where w.plevel=p.plevel and w.ecode<13;

NAME PAY

--------------- ----------

radheshyam26000

chandernath 22000

36. Display the result by calculating 125*43.

SQL> select 125*43 from dual;

125*43

----------

5375

37. Display the lower case of the screen “WELCOME”.

SQL> select lower('WELCOME') from dual;

LOWER('

------------

Welcome

38.Display thanks in upperscreen.

SQL> select upper('thanks') from dual;

UPPER(

----------

THANKS

17
39.Delete the values of the table workerinfo whose names starts with c.
SQL> delete from workerinfo where name like 'c%';

1 row deleted.

40.Drop the table workerinfo.

SQL> drop table workerinfo;

Table dropped.

18