SQL QUERY 1 DATE: 14-9-09 Q. Create the tables FURNITURE and ARRIVALS in SQL and enter the records in the table.
SQL> Create table Furniture(No number(2),Itemname varchar2(20),Type varchar2(17),DateOfStock date , Price number(8),Discount number(8),Discount number(3)); Table created SQL> Insert into Furniture values(1,'White Lotus','Double Bed','23-feb-2002',30000,25); 1 row created. SQL> Insert into Furniture values(2,'Pink feather','Baby cot','20-jan-2002',7000,20); 1 row created. SQL> Insert into Furniture values(3,'Dolphin','Baby cot','19-feb-2002',9500,20); 1 row created. SQL> Insert into Furniture values(4,'Decent','Office table','01-jan-2002',25000,25); 1 row created. SQL> Insert into Furniture values(5,'Comfort zone','Double bed','12-jan-2002',25000,25); 1 row created. SQL> Insert into Furniture values(6,'Donald','Baby cot','24-feb-2002',6500,15); 1 row created. SQL> Insert into Furniture values(7,'Royal finish','Office table','20-feb-2002',18000,30); 1 row created. SQL> Insert into Furniture values(8,'Royal tiger','Sofa','22-feb-2002',31000,30); 1 row created. SQL> Insert into Furniture values(9,'Econo sitting','Sofa','13-dec-2002',9500,25); 1 row created. SQL> Insert into Furniture values(10,'Eating paradise','Dining table','19-feb-2002',11500,25); 1 row created. SQL> create table Arrivals(No number(2),Itemname v archar2(17),Type varchar2(17),Dateofstock date,Price number(6),Discount number(2)); Table created. SQL> Insert into Arrivals values (11,'Wood comfort','Double bed','23-mar-2003',25000,25); 1 row created.
SQL> Insert into Arrivals values (12,'Old fox','Sofa','20-feb-2003',17000,20); 1 row created. SQL> Insert into Arrivals values (13,'Micky','Baby cot','21-feb-2003',7500,15); 1 row created. Write SQL commands for the following statements: (i) To show all information about the Baby cots from the FURNITURE table.
SQL> Select * from Furniture where Type=’Baby cot’; NO ITEMNAME TYPE DATEOFSTO PRICE DISCOUNT --------- -------------------- ----------------- --------- --------- --------2 Pink feather Baby cot 20-JAN-02 7000 20 3 Dolphin Baby cot 19-FEB-02 9500 20 6 Donald Baby cot 24-FEB-02 6500 15
(ii)
To list the ITEMNAME which are priced at more than 15000 from the FURNITURE table.
SQL> Select Itemname from Furniture where Price>15000; ITEMNAME -------------------White Lotus Decent Comfort zone Royal finish Royal tiger (iii)
To list ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02 from the FURNITURE table in descending order of ITEMNAME.
SQL> select ITEMNAME, TYPE from FURNITURE where DATEOFSTOCK < (22-01-02) order by ITEMNAME desc; ITEMNAME TYPE -------------------- ----------------Pink feather Baby cot Econo sitting Sofa Decent Office table Comfort zone Double bed
(iv)
To display ITEMNAME and DATEOFSTOCK of those items, in which the DISCOUNT percentage is more than 25 from FURNITURE table.
SQL> Select Itemname, DateOfStock from Furniture where Discount>25;
ITEMNAME -------------------Royal finish Royal tiger
DATEOFSTOCK --------20-FEB-02 22-FEB-02
SQL QUERY 2 DATE: 14-9-09 Q. Create the tables, DOCTOR and SALARY in SQL and enter the records in the table.
SQL> create table DOCTOR (ID number(10), NAME varchar2(10), DEPT varchar(10), SEX char(5), EXPERIENCE number(5)); Table created. SQL> insert into DOCTOR values(101, 'John', 'ENT','M', 12); 1 row created. SQL> insert into DOCTOR values(104, 'Smith', 'ORTHOPEDIC', 'M', 12); 1 row created. SQL> insert into DOCTOR values(107, 'George', 'CARDIOLOGY', 'M', 10); 1 row created. SQL> insert into DOCTOR values(114, 'Lara', 'SKIN', 'F', 3); 1 row created. SQL> insert into DOCTOR values(109, 'K George', 'MEDICINE', 'F', 9); 1 row created. SQL> insert into DOCTOR values(105, 'Johnson', 'ORTHOPEDIC', 'M', 10); 1 row created. SQL> insert into DOCTOR values(117, 'Lucy', 'ENT', 'F', 3); 1 row created. SQL> insert into DOCTOR values(111, 'Bill', 'MEDICINE', 'F', 12); 1 row created. SQL> insert into DOCTOR values(130, 'Morphy', 'ORTHOPEDIC', 'M', 15); 1 row created. SQL> create table SALARY(ID number(10), BASIC number(10), ALLOWANCE number(10),CONSULTATION number(10)); Table created. SQL> insert into SALARY values(101 , 12000, 1000, 300); 1 row created. SQL> insert into SALARY values(104 , 23000, 2300, 500);
1 row created. SQL> insert into SALARY values(107 , 32000, 4000, 500);1 row created. SQL> insert into SALARY values(114 , 12000, 5200, 100); 1 row created. SQL>insert into SALARY values(109 , 42000, 1700, 200); 1 row created. SQL> insert into SALARY values(105 , 18900, 1690, 300); 1 row created. SQL>insert into SALARY values(130 , 21700, 2600, 300); 1 row created.
Write SQL commands for the following statements: (i)
Display NAME of all doctors who are in “MEDICINE” having more than 10 years experience from the table DOCTOR. SQL> select NAME from DOCTOR where DEPT='MEDICINE' and EXPERIENCE>10; NAME ---------Bill
(ii)
Display the average salary of all doctors working in “ENT” department using the table DOCTOR and SALARY. (Salary=BASIC + ALLOWANCE)
SQL> select avg(salary.basic+salary. ALLOWANCE) from Doctor, Salary where doctor.dept='ENT' and doctor.id=salary.id; AVG(SALARY.BASIC+SALARY.ALLOWANCE) ---------------------------------13000 (iii)
Display the minimum ALLOWANCE of female doctors.
SQL> select min(salary.ALLOWANCE) from doctor, salary where doctor.sex='F' and doctor.id=salary.id; MIN(SALARY.ALLOWANCE) --------------------1700 (iv)
Display the highest consultation fee among all male doctors.
SQL> select max(salary.CONSULTATION)from doctor, salary where doctor.sex='M' and doctor.id=salary.id; MAX(SALARY.CONSULTATION) -----------------------500
SQL QUERY 3 DATE: 14-9-09 Q. Create the tables, WORKERS and DESIG in SQL and enter the records in the tables.
SQL> create table DESIG(W_ID number(10), SALARY number(10), BENEFITS number(10), DESIGNATION varchar2(20)); Table created. SQL> insert into DESIG values(102 ,75000 ,15000 ,'Manager'); 1 row created. SQL> insert into DESIG values(105 ,85000 ,25000 ,'Director'); 1 row created. SQL> insert into DESIG values(144 ,70000 ,15000 ,'Manager'); 1 row created. SQL> insert into DESIG values(210 ,75000 ,12500 ,'Manager'); 1 row created. SQL> insert into DESIG values(255 ,50000 ,12000 ,'Clerk'); 1 row created. SQL> insert into DESIG values(300 ,45000 ,10000 ,'Clerk'); 1 row created. SQL> insert into DESIG values(335 ,40000 ,10000 ,'Clerk'); 1 row created. SQL> insert into DESIG values(400 ,32000 ,7500 ,'Salesman'); 1 row created. SQL> insert into DESIG values(451 ,28000 ,7500 ,'Salesman'); 1 row created. SQL> create table WORKERS(W_ID number(5), FIRSTNAME v archar2(20), LASTNAME varchar2(20),ADDRESS varchar2(20), CITY varchar2(20)); Table created. SQL> insert into WORKERS values(102 , 'Sam', 'Tones’, ‘33 Elm St.', 'Paris'); 1 row created. SQL> insert into WORKERS values(105 , 'Sarah', 'Ackerman', '440 U .S. 110', 'New York'); 1 row created.
SQL> insert into WORKERS values(144 , 'Manila', 'Sengupta', '24 Friends Street', 'New Delhi'); 1 row created. SQL> insert into WORKERS values(210 , 'George', 'Smith', '83 First Street', 'Howard'); 1 row created. SQL> insert into WORKERS values(255 , 'Mary', 'Jones', '842Vine Ave.', 'Losantiville'); 1 row created. SQL> insert into WORKERS values(300 , 'Robert', 'Samuel', '9 Fifth Cross', 'Washington'); 1 row created. SQL> insert into WORKERS values(335 , 'Henry', 'Williams', '12 Moore Street', 'Boston'); 1 row created. SQL> insert into WORKERS values(403 , 'Ronny', 'Lee', '121 Harrison St.', 'New York'); 1 row created. SQL> insert into WORKERS values(451 , 'Pat', 'Thompson', '11 Red Road', 'Paris'); 1 row created.
Write SQL commands for the following statements: (i) To display W_ID, FIRSTNAME, ADDRESS and CITY of all employees living in New York from the table WORKERS.
SQL> select W_ID, FIRSTNAME, ADDRESS, CITY from WORKERS where CITY= 'New York'; W_ID --------105 403
(ii)
FIRSTNAME -------------------Sarah Ronny
ADDRESS -------------------440 U.S. 110 121 Harrison St.
CITY -------------------New York New York
To display the content of WORKERS table in ascending order of LASTNAME.
SQL> select * from WORKERS order by LASTNAME; W_ID FIRSTNAME LASTNAME ADDRESS CITY --------------------------------------------------------------------------------105 Sarah Ackerman 440 U.S. 110 New York 255 Mary Jones 842Vine Ave. Losantiville 403 Ronny Lee 121 Harrison St. New York
300 Robert 144 Manila 210 George 451 Pat 102 Sam 335 Henry
Samuel Sengupta Smith Thompson Tones Williams
9 Fifth Cross 24 Friends Street 83 First Street 11 Red Road 33 Elm St. 12 Moore Street
Washington New Delhi Howard Paris Paris Boston
9 rows selected. (iii)
To display the FIRSTNAME, LASTNAME, and Total Salary of all Clerks from the tables WORKERS and DESIG, where Total Salary is calculated as Salary + Benefits.
SQL> select WORKERS.FIRSTNAME, WORKERS.LASTNAME, SALARY+BENEFITS "Total Salary" from WORKERS, DESIG where DESIG.Designation='Clerk' and WORKERS.W_ID=DESIG.W_ID; FIRSTNAME -------------------Mary Robert Henry (iv)
LASTNAME -------------------Jones Samuel Williams
Total Salary -----------62000 55000 50000
To display the Minimum salary among Managers and Clerks from the table DESIG. SQL> select MIN(salary) from DESIG where DESIGNATION='Manager' OR DESIGNATION='Clerk'; MIN(SALARY) ----------40000
SQL QUERY 4 DATE: 14-9-09 Q. Create the tables, STATIONERY and CONSUMER, in SQL and enter the records in the table.
SQL> create table STATIONARY( S_ID varchar2(5), StationaryName varchar2(20), Company varchar2(5), price integer); Table created. SQL> insert into STATIONARY values ('DP01' , 'Dot Pen','ABC',10); 1 row created. SQL>insert into STATIONARY values ('PL02' , 'Pencil' , 'XYZ' , 6); 1 row created. SQL> insert into STATIONARY values('ER05' , 'Eraser', 'XYZ' , 7); 1 row created. SQL> insert into STATIONARY values ('PL01' , 'Pencil' , 'CAM' ,5); 1 row created. SQL> insert into STATIONARY values ('GP02', 'Gel Pen','ABC',15); 1 row created.
SQL> CREATE TABLE CONSUMER( C_ID INT EGER, ConsumerName VARCHAR2(20),Address VARCHAR2(20),S_ID VARCHAR2(4)); Table created. SQL> INSERT INTO consumer VALUES ('01','Good Learner', 'Delhi', 'PL01' ); 1 row created. SQL> INSERT INTO consumer VALUES ('06','Write Well','Mumbai','GP02'); 1 row created. SQL> INSERT INTO consumer VALUES ('12','Topper','Delhi','DP01'); 1 row created. SQL> INSERT INTO consumer VALUES ('15','Write & Draw', 'Delhi', 'PL02'); 1 row created. SQL> INSERT INTO consumer VALUES ('16', 'Motivation', 'Bangalore', 'PL01') 1 row created.
Write SQL commands for the following statements: (i)
To display the details of those Consumers whose Address is Delhi.
SQL> select * from consumer where address='Delhi';
C_ID --------1 12 15
(ii)
CONSUMERNAME -------------------Good Learner Topper Write & Draw
ADDRESS -------------------Delhi Delhi Delhi
S_ID ---PL01 DP01 PL02
To display the details of Stationery whose Price is in the range of 8 to15 (Both values included).
SQL> select * from stationary where Price between 8 and 15; S_ID STATIONARYNAME ----- -------------------DP01 Dot Pen GP02 Gel Pen
(iii)
COMPA PRICE ----- --------ABC 10 ABC 15
To display the ConsumerName, Address from Table Consumer, and Company and Price from table Stationery, with their corresponding matching S_ID.
SQL> select Consumer.consumername, consumer.address, Stationary.company, stationary.price from consumer, stationary where consumer.s_id=stationary.s_id;
CONSUMERNAME ADDRESS COMPA PRICE -------------------- -------------------- ----- --------Topper Delhi ABC 10 Write Well Mumbai ABC 15 Good Learner Delhi CAM 5 Motivation Bangalore CAM 5 Write & Draw Delhi XYZ 6 (iv)
To increase the Price of all Stationery by 2.
SQL> update stationary set price=price+2; 5 rows updated. SQL> select * from stationary;
S_ID STATIONARYNAME COMPA ----- -------------------- ----- --------DP01 Dot Pen ABC PL02 Pencil XYZ ER05 Eraser XYZ PL01 Pencil CAM GP02 Gel Pen ABC
PRICE 12 8 9 7 17
