Ex.
No: 5 SQL COMMANDS EXERCISE - 5
DATE:
AIM:
Create a Doctors and Salary table and insert data. Implement the following SQL commands on the
Doctors and Salary
TABLE:DOCTOR
ID NAME DEPT SEX EXPERIENCE
101 John ENT M 12
104 Smith ORTHOPEDIC M 5
107 George CARDIOLOGY M 10
114 Lara SKIN F 3
109 K George MEDICINE F 9
105 Johnson ORTHOPEDIC M 10
117 Lucy ENT F 3
111 Bill MEDICINE F 12
130 Morphy ORTHOPEDIC M 15
TABLE: SALARY
ID BASIC ALLOWANCE CONSULTATION
101 12000 1000 300
104 23000 2300 500
107 32000 4000 500
114 12000 5200 100
109 42000 1700 200
105 18900 1690 300
130 21700 2600 300
CREATE TABLE DOCTOR(ID int NOT NULL PRIMARY KEY, NAME char(25) , DEPT char(25) , SEX char ,
EXPERIENCE int);
INSERT INTO DOCTOR VALUES(101,”John”, “ENT”,’M’,12);
INSERT INTO DOCTOR VALUES(104,”Smith”, “ORTHOPEDIC”,’M’,5);
CREATE TABLE SALARY(ID int, BASIC int, ALLOWANCE int, CONSULTATION int);INSERT INTO SALARY
VLAUES(101, 12000,1000,300);
INSERT INTO SALARY VLAUES(104, 23000,2300,500);
1
i) Display NAME of all doctors who are in “MEDICINE” having more than 10
years experience from table DOCTOR
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
tables DOCTOR and SALARY. (Salary=BASIC+ALLOWANCE)
select avg(BASIC+ALLOWANCE) “avg salary” from DOCTOR , SALARY where
[Link]=[Link] and DEPT=”ENT”;
Avg salary
13000.00
iii) Display minimum ALLOWANCE of female doctors.
min(ALLOWANCE)
1700
select min(ALLOWANCE) from SALARY, DOCTOR where SEX=’F’ and
[Link]=[Link];
iv) Display [Link] , NAME from the table DOCTOR and BASIC ,
ALLOWANCE fromthe table SALARY with their corresponding
matching ID.
select [Link], NAME, BASIC ,ALLOWANCE from DOCTOR,SALARYwhere
[Link]=[Link];
ID NAME BASIC ALLOW
ANCE
101 John 12000 1000
104 Smith 23000 2300
107 George 32000 4000
109 K George 42000 1700
114 Lara 12000 5200
130 Morphy 21700 2600
2
iv) To display distinct department from the table doctor.
select distinct(DEPT) from DOCTOR;
DEPT
ENT
ORTHOPEDIC
CARDIOLOGY
SKIN
MEDICINE
RESULT:
The given program is executed successfully and the result is verified.
3
[Link]: 6 SQL COMMANDS EXERCISE – 6
DATE:
AIM:
To create two tables for company and customer and execute the given commands using SQL.
TABLE : COMPANY
TABLE : CUSTOMER
1. To display those company name which are having price less than 30000.
2. To display the name of the companies in reverse alphabetical order.
3. To increase the price by 1000 for those customer whose name starts with ‘S’
4. To add one more column totalprice with decimal (10,2) to the table customer
5. To display the details of company where productname as mobile.
4
CREATE TABLE COMPANY(cid int(3) , name varchar(15) , city varchar(10), productname varchar(15));
INSERT INTO COMPANY VALUES(111, ‘SONA’, ‘DELHI’, ‘TV’);
CREATE TABLE CUSTOMER(custid int(3), name varchar(15), price int(10),qty int(3) , cid int(3));
INSERT INTO CUSTOMER VALUES(101, ‘ROHAN SHARMA’, 70000,20,222);
OUTPUT :
1. select name from company where [Link]=customer. cid andprice < 30000;
NAME
NEHA SONI
2. select name from company order by name desc;
NAME
SONY
ONIDA
NOKIA
DELL
BLACKBERRY
[Link] customer set price = price + 1000 where name like ‘s%’;select * from
customer;
CUSTID NAME PRICE QT Y CID
101 ROHAN 70000 20 222
SHARMA
102 DEEPAK 50000 10 666
KUMAR
103 MOHAN 30000 5 111
KUMAR
104 SAHIL 36000 3 333
BANSAL
105 NEHA SONI 25000 7 444
106 SONA 21000 5 333
AGARWAL
107 ARUN SINGH 50000 15 666
5
4. alter table customer add totalprice decimal(10,2);Select * from customer;
CUS TID NAME PRICE QTY CID TOTAL
PRICE
101 ROHAN SHARMA 70000 20 222 NULL
102 DEEPAK KUMAR 50000 10 666 NULL
103 MOHAN KUMAR 30000 5 111 NULL
104 SAHIL BANSAL 36000 3 333 NULL
105 NEHA SONI 25000 7 444 NULL
106 SONA AGARWAL 21000 5 333 NULL
107 ARUN SINGH 50000 15 666 NULL
5. select * from company where productname=’mobile’;
CID NAME CITY PRODUCTN
AME
222 NOKIA MUMBAI MOBILE
444 SONY MUMBAI MOBILE
555 BLACKBER MADRAS MOBILE
RY
RESULT:
The given program is executed successfully and the result is verified.
6
[Link]: 7 SQL COMMANDS EXERCISE – 7
DATE:
AIM: To create table for teacher and execute the given commands using SQL.
TABLE : TEACHER
No Name Ag e Departm ent DateofJo in Salary Se x
1 Jishnu 34 Computer 10/01/97 12000 M
2 Sharmila 31 History 24/03/98 20000 F
3 Santhosh 32 Maths 12/12/96 30000 M
4 Shanmathi 35 History 01/07/99 40000 F
5 Ragu 42 Maths 05/09/97 25000 M
6 Shiva 50 History 27/02/97 30000 M
7 Shakthi 44 Computer 25/02/97 21000 M
8 Shriya 33 Maths 31/07/97 20000 F
1. To show all information about the teacher of history department.
2. To list the names of female teacher who are in Maths department.
3. To list names of all teachers with their date of joining in ascending order.
4. To count the number of teachers with age>35.
5. To count the number of teachers department wise.
CREATE TABLE TEACHER(No int(2), Name varchar(15), Age int(3) , Department varchar(15), Dateofjoin
varchar(15) , Salary int(7) , Sex char(1));
INSERT INTO TEACHER VALUES(1,’Jishnu’,34,’Computer’,’10/01/97’,12000,’M’);
OUTPUT:
1. select * from teacher where Department=’History’;
No Name Age Departm DateofJ Salary Se
ent o in x
2 Sharmila 31 History 24/03/98 20000 F
4 Shanmat 35 History 01/07/99 40000 F
hi
6 Shiva 50 History 27/02/97 30000 M
7
2. select Name from teacher where Department=’Maths’ and Sex=’F’;
Name
Shriya
3. select Name , Dateofjoin from teacher order by Dateofjoin asc;
Name Dateofjoin
Santhosh 12/12/96
Jishnu 10/01/97
Shakthi 25/02/97
Shiva 27/02/97
Shriya 31/07/97
Ragu 05/09/97
Sharmila 24/03/98
Shanmathi 01/07/99
4. select count(*) from teacher where Age>35;
count(*)
3
5. select Department , count(*) from teacher group by department;
Department count(*)
Computer 2
History 3
Maths 3
RESULT:
The given program is executed successfully and the result is verified.