STRUCTURED QUERY LANGAUAGE
SQL> create database db1;
SQL> use db1;
SQL> create table emp1
2 (
3 empid number(2),
4 empname char(20),
5 empsal number(7,2)
6 );
SQL> desc emp1;
Name Null? Type
------
EMPID NUMBER(2)
EMPNAME CHAR(20)
EMPSAL NUMBER(7,2)
SQL> alter table emp1 add(empaddvarchar(20));
Table altered.
SQL> desc emp1;
Name Null? Type
------
EMPID NUMBER(2)
EMPNAME CHAR(20)
EMPSAL NUMBER(7,2)
EMPADD VARCHAR2(20)
SQL> alter table emp1 modify(empname char(30));
Table altered.
SQL> desc emp1;
Name Null? Type
------
EMPID NUMBER(2)
EMPNAME CHAR(30)
EMPSAL NUMBER(7,2)
EMPADD VARCHAR2(20)
SQL> insert into emp1 values(1,'raja',99999.99,'mayur vihar');
1 row created.
SQL> insert into emp1 values(2,’Rishabh',98990.90,'Mayur vihar 1');
SQL> select * from emp1;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 99999.99 mayurvihar
2 rishabh 98989.99 mayurviahr 2
3 harshika 90000.9 mayurvihar 2
4 ekansh 98789.9 pandavnagar
SQL> select empid, empname from emp1;
EMPID EMPNAME
------
1 raja
2 rishabh
3 harshika
4 ekansh
SQL> select * from emp1
2 where
3 empsal >98000;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 99999.99 mayurvihar
2 rishabh 98989.99 mayurviahr 2
4 ekansh 98789.9 pandavnagar
SQL> select * from emp1
2 where
3 empsal >98000 and empsal <99000;
EMPID EMPNAME EMPSAL EMPADD
------
2 rishabh 98989.99 mayurviahr 2
4 ekansh 98789.9 pandavnagar
SQL> select empname, empsal as "current sal", empsal+empsal*.10 as "increment sal"
2 from
3 emp1;
EMPNAME current sal increment sal
------
raja 99999.99 109999.989
rishabh 98989.99 108888.989
harshika 90000.9 99000.99
ekansh 98789.9 108668.89
SQL> select * from emp1
2 where
3 empname like 'r%';
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 99999.99 mayurvihar
2 rishabh 98989.99 mayurviahr 2
1 select * from emp1
2 where
3* empname like '%a%'
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 99999.99 mayurvihar
2 rishabh 98989.99 mayurviahr 2
3 harshika 90000.9 mayurvihar 2
4 ekansh 98789.9 pandavnagar
SQL 1 select * from emp1
2 where
3* empname like '_a%'
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 99999.99 mayurvihar
3 harshika 90000.9 mayurvihar 2
SQL 1 select * from emp1
2 where
3* empname like '__a%'
EMPID EMPNAME EMPSAL EMPADD
------
4 ekansh 98789.9 pandavnagar
SQL 1 update emp1
2 set
3 empsal = 90000
4 where
5* empid =1;
1 row updated.
SQL> select * from emp1;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
3 harshika 70000 mayurvihar 2
4 ekansh 80000 pandavnagar
SQL 1 select * from emp1
2 where
3* empsal = 70000 or empsal =80000 orempsal=90000;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
3 harshika 70000 mayurvihar 2
4 ekansh 80000 pandavnagar
SQL 1 select * from emp1
2 where
3* empsalin(70000,80000,90000);
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
3 harshika 70000 mayurvihar 2
4 ekansh 80000 pandavnagar
SQL> select * from emp1
2 whereempsal >=70000 and empsal <=90000;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
3 harshika 70000 mayurvihar 2
4 ekansh 80000 pandavnagar
SQL 1 select * from emp1
2* where empsal between 70000 and 90000;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
3 harshika 70000 mayurvihar 2
4 ekansh 80000 pandavnagar
SQL> select empsal from emp1
2 where
3 empname ='harshika';
EMPSAL
------
70000
SQL> select * from emp1
2 where
3 empsal >70000;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
4 ekansh 80000 pandavnagar
SQL> select * from emp1
2 where
3 empsal > (select empsal from emp1 where empname ='harshika');
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
4 ekansh 80000 pandavnagar
SQL> select * from emp1
2 order by empsal;
EMPID EMPNAME EMPSAL EMPADD
------
3 harshika 70000 mayurvihar 2
2 rishabh 80000 mayurviahr 2
4 ekansh 80000 pandavnagar
1 raja 90000 mayurvihar
SQL 1 select * from emp1
2* order by empsaldesc;
EMPID EMPNAME EMPSAL EMPADD
------
1 raja 90000 mayurvihar
2 rishabh 80000 mayurviahr 2
4 ekansh 80000 pandavnagar
3 harshika 70000 mayurvihar 2
SQL> create table dept1
2 (
3 deptid number(2) primary key,
4 deptname char(20)
5 );
Table created.
SQL> insert into dept1 values(11,'sales');
1 row created.
SQL> alter table emp1 add(deptid number(2) references dept1(deptid));
Table altered.
SQL> update emp1 set deptid=11 where empid <3;
2 rows updated.
SQL 1* update emp1 set deptid=12 where empid>2;
2 rows updated.
SQL> select * from emp1;
EMPID EMPNAME EMPSAL EMPADD DEPTID-
1 raja 90000 mayurvihar 11
2 rishabh 80000 mayurviahr 2 11
3 harshika 70000 mayurvihar 2 12
4 ekansh 80000 pandavnagar 12
SQL> select * from emp1,dept1
2 where
3 emp1.deptid=dept1.deptid;;
EMPID EMPNAME EMPSAL EMPADD DEPTID DEPTID DEPTNAME
1 raja 90000 mayurvihar 11 11 sales
2 rishabh 80000 mayurviahr 2 11 11 sales
3 harshika 70000 mayurvihar 2 12 12 production
4 ekansh 80000 pandavnagar 12 12 production
SQL 1 select empname,deptname from emp1,dept1
2 where
3* emp1.deptid=dept1.deptid
EMPNAME DEPTNAME
------
raja sales
rishabh sales
harshika production
ekansh production
SQL> delete from emp1
2 where
3 empid =4;
1 row deleted.
SQL> select * from emp1;
EMPID EMPNAME EMPSAL EMPADD DEPTID
1 raja 90000 mayurvihar 11
2 rishabh 80000 mayurviahr 2 11
3 harshika 70000 mayurvihar 2 12
SQL> select distinct deptid from emp1;
DEPTID
------
11
12
SQL> select count(distinct deptid) from emp1;
COUNT(DISTINCTDEPTID)
------
2
SQL> select deptid, count(*) from emp1
2 group by deptid;
DEPTID COUNT(*)
------
11 2
12 1
SQL> alter table emp1 add(doj date);
Table altered.
SQL> update emp1 set doj='12-jan-2012';
3 rows updated.
SQL> select * from emp1;
EMPIDEMPNAMEEMPSALEMPADDDEPTIDDOJ
1raja90000mayurvihar1112-JAN-12
2rishabh80000mayurviahr 21112-JAN-12
3harshika70000mayurvihar 21212-JAN-12
SQL 1 select empsal, count(*) as "total no of employees" from emp1
2 group by empsal
3* order by empsal
EMPSAL total no of employees
------
70000 1
80000 1
90000 1
Q. 1. Consider the following tables GARMENT and FABRIC .Write SQL commands for the
statements (i) to (iv)
Table : GARMENT
GCODE / DESCRIPTION / PRICE / FCODE / READYDATE10023 / PENCIL SKIRT / 1150 / F03 / 19-DEC-08
10001 / FORMAL SHIRT / 1250 / F01 / 12-JAN-08
10012 / INFORMAL SHIRT / 1550 / F02 / 06-JUN-08
10024 / BABY TOP / 750 / F03 / 07-APR-07
10090 / TULIP SKIRT / 850 / F02 / 31-MAR-07
10019 / EVENING GOWN / 850 / F03 / 06-JUN-08
10009 / INFORMAL PANT / 1500 / F02 / 20-OCT-08
10007 / FORMAL PANT / 1350 / F01 / 09-MAR-08
10020 / FROCK / 850 / F04 / 09-SEP-07
10089 / SLACKS / 750 / F03 / 31-OCT-08
Table : FABRIC
FCODE / TYPEF04 / POLYSTER
F02 / COTTON
F03 / SILK
F01 / TERELENE
(i)To display GCODE and DESCRIPTION of each GARMENT in descending order of GCODE .
(ii)To display the details of all the GARMENT s, which have READYDATE in between 08-DEC-07 and 16-JUN-08 (inclusive of both the dates).
(iii)To display the average PRICE of all the GARMENTs which are made up of FABRIC with FCODE as F03
(iv)To display FEBRIC wise highest and lowest price of GARMENT s from GARMENT table.(Display FCODE of each GARMENT along with highest and lowest price).
Q.2. Given the following tables for a database LIBRARY :
Table : BOOKS
Book_ID / Book_Name / Author_Name / Publishers / Price / Type / Qty.C0001 / Fast Cook / Lata Kapoor / EPB / 355 / Cookery / 5
F0001 / The Tears / Willam Hopkins / First Publ. / 650 / Fiction / 20
T0001 / My First C++ / Brian & Brooke / EPB / 350 / Text / 10
T0002 / C++ Brainworks / A.W.Rossaine / TDH / 350 / Text / 15
F0002 / Thunderbolts / Anna Roberts / First Publ. / 750 / Fiction / 50
Table : ISSUED
Book_Id / Quantity_IssuedT0001 / 4
C0001 / 5
F0001 / 2
Write SQL queries for (a) to (f) :
(a)To show Book name , Auther name and Price of books of First Publ. publishers.
(b)To list the names from books of Text type.
(c)To display the names and price from books in ascending order of their price
(d)To increase the price of all books of EPB Publishers by 50.
(e)To display the book_Id ,Book_Name and Quantity_Issued for all books which have been issued.( The query will require contents from both the tables.)
(f)To insert a new row in the table Issued having thr following data : “F0003”, 1
(g)Give the outputs of the following queries baesd on the above tables :
(i) SELECT COUNT (*) FROM Books ;
(ii) SELECT MAX(Price) FROM Books WHERE Quantity >=15;
(iii) SELECT Book_name, Author_Name FROM Books WHEREPublishERS = “EPB”;
(iv) SELECT COUNT (DISTINCT Publishers) FROM Books WHERE Price>=400 ;