SQL COMMANDS EXERCISE - 1
[Link]: 1
DATE:
AIM:
To write Queries for the following Questions based on the given table:
Rollno Name Gender Age Dept DOA Fees
1 Arun M 24 COMPUTER 1997-01-10 120
2 Ankit M 21 HISTORY 1998-03-24 200
3 Anu F 20 HINDI 1996-12-12 300
4 Bala M 19 NULL 1999-07-01 400
5 Charan M 18 HINDI 1997-09-05 250
6 Deepa F 19 HISTORY 1997-06-27 300
7 Dinesh M 22 COMPUTER 1997-02-25 210
8 Usha F 23 NULL 1997-07-31 200
(a) Write a Query to Create a new database in the name of "STUDENTS"
Sol:mysql> CREATE DATABASE STUDENTS;
(b) Write a Query to Open the database "STUDENTS"
Sol:mysql> USE STUDENTS;
(c) Write a Query to create the above table called: Info
Sol:
mysql> CREATE TABLE STU(Rollno int Primary key,Name varchar(10),Gender varchar(3),
Age int,Dept varchar(15),DOA date,Fees int);
(d) Write a Query to list all the existing database names.
Sol:
mysql> SHOW DATABASES;
Output:
(e) Write a Query to List all the tables that exists in the current database.
Sol:
mysql> SHOW TABLES;
Output:
(f) Write a Query to insert all the rows of above table into Info table.
Sol:
INSERT INTO STU VALUES (1,'Arun','M', 24,'COMPUTER','1997-01-10', 120);
INSERT INTO STU VALUES (2,'Ankit','M', 21,'HISTORY','1998-03-24', 200);
INSERT INTO STU VALUES (3,'Anu','F', 20,'HINDI','1996-12-12', 300);
INSERT INTO STU VALUES (4,'Bala','M', 19, NULL,'1999-07-01', 400);
INSERT INTO STU VALUES (5,'Charan','M', 18,'HINDI','1997-06-27', 250);
INSERT INTO STU VALUES (6,'Deepa','F', 19,'HISTORY','1997-06-27', 300);
INSERT INTO STU VALUES (7,'Dinesh','M', 22,'COMPUTER','1997-02-25', 210);
INSERT INTO STU VALUES (8,'Usha','F', 23, NULL,'1997-07-31', 200);
(g) Write a Query to display all the details of the Employees from the above table 'STU'.
Sol:
mysql> SELECT * FROM STU;
Output:
(h) Write a query to Rollno, Name and Department of the students from STU table.
Sol:
mysql> SELECT ROLLNO,NAME,DEPT FROM STU;
[Link]: 2 SQL COMMANDS EXERCISE – 2
DATE:
AIM:
To write Queries for the following Questions based on the given table:
Rollno Name Gender Age Dept DOA Fees
1 Arun M 24 COMPUTER 1997-01-10 120
2 Ankit M 21 HISTORY 1998-03-24 200
3 Anu F 20 HINDI 1996-12-12 300
4 Bala M 19 NULL 1999-07-01 400
5 Charan M 18 HINDI 1997-09-05 250
6 Deepa F 19 HISTORY 1997-06-27 300
7 Dinesh M 22 COMPUTER 1997-02-25 210
8 Usha F 23 NULL 1997-07-31 200
(a) Write a Query to select distinct Department from STU table.
Sol:
mysql> SELECT DISTICT(DEPT) FROM STU;
Output:
(b) To show all information about students of History department.
Sol:
mysql>SELECT * FROM STU WHERE DEPT='HISTORY';
Output:
(c) Write a Query to list name of female students in Hindi Department.
Sol:
mysql> SELECT NAME FROM STU WHERE DEPT='HINDI' AND GENDER='F';
Output:
(d) Write a Query to list name of the students whose ages are between 18 to 20.
Sol:
mysql> SELECT NAME FROM STU WHERE AGE BETWEEN 18 AND 20;
Output:
(e) Write a Query to display the name of the students whose name is starting with 'A'.
Sol:
mysql> SELECT NAME FROM STU WHERE NAME LIKE 'A%';
Output:
(f) Write a query to list the names of those students whose name have second alphabet 'n' in their
names.
Sol:
mysql> SELECT NAME FROM STU WHERE NAME LIKE '_N%';
********************************************************************************************************
[Link]: 3 SQL COMMANDS EXERCISE - 3
DATE:
AIM:
To write Queries for the following Questions based on the given table:
Rollno Name Gender Age Dept DOA Fees
1 Arun M 24 COMPUTER 1997-01-10 120
2 Ankit M 21 HISTORY 1998-03-24 200
3 Anu F 20 HINDI 1996-12-12 300
4 Bala M 19 NULL 1999-07-01 400
5 Charan M 18 HINDI 1997-09-05 250
6 Deepa F 19 HISTORY 1997-06-27 300
7 Dinesh M 22 COMPUTER 1997-02-25 210
8 Usha F 23 NULL 1997-07-31 200
(a) Write a Query to delete the details of Roll number is 8.
Sol:
mysql> DELETE FROM STU WHERE ROLLNO=8;
Output (After Deletion):
(b) Write a Query to change the fess of Student to 170 whose Roll number is 1, if the existing fess
is less than 130.
Sol:
mysql> UPDATE STU SET FEES=170 WHERE ROLLNO=1 AND FEES<130;
Output(After Update):
(c) Write a Query to add a new column Area of type varchar in table STU.
Sol:
mysql> ALTER TABLE STU ADD AREA VARCHAR(20);
Output:
(d) Write a Query to Display Name of all students whose Area Contains NULL.
Sol:
mysql> SELECT NAME FROM STU WHERE AREA IS NULL;
Output:
(e) Write a Query to delete Area Column from the table STU.
Sol:
mysql> ALTER TABLE STU DROP AREA;
Output:
(f) Write a Query to delete table from Database.
Sol:
mysql> DROP TABLE STU;
Output:
*******************************************************************************************
[Link]: 4 SQL COMMANDS EXERCISE - 4
DATE:
AIM:
To write Queries for the following Questions based on the given table:
TABLE: STOCK
Pno Pname Dcode Qty UnitPrice StockDate
5005 Ball point pen 102 100 10 2021-03-31
5003 Gel pen premium 102 150 15 2021-01-01
5002 Pencil 101 125 4 2021-02-18
5006 Scale 101 200 6 2020-01-01
5001 Eraser 102 210 3 2020-03-19
5004 Sharpner 102 60 5 2020-12-09
5009 Gel pen classic 103 160 8 2022-01-19
TABLE: DEALERS
Dcode Dname
101 Sakthi Stationeries
103 Classic Stationeries
102 Indian Book House
(a) To display the total Unit price of all the products whose Dcode as 102.
Sol:
mysql> SELECT SUM(UNITPRICE) FROM STOCK GROUP BY DCODE HAVING
DCODE=102;
Output:
(b) To display details of all products in the stock table in descending order of Stock date.
Sol:
mysql> SELECT * FROM STOCK ORDER BY STOCKDATE DESC;
Output:
(c) To display maximum unit price of products for each dealer individually as per
dcode from the table Stock.
Sol:
mysql> SELECT DCODE,MAX(UNITPRICE) FROM STOCK GROUP BY DCODE;
Output:
(d) To display the Pname and Dname from table stock and dealers.
Sol:
mysql> SELECT PNAME,DNAME FROM STOCK S,DEALERS D WHERE
[Link]=[Link];
Output:
*************************************************************************************************
8
[Link]: 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);
i) Display NAME of all doctors who are in “MEDICINE” having more than 10
9
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
iv) To display distinct department from the table doctor.
10
select distinct(DEPT) from DOCTOR;
DEPT
ENT
ORTHOPEDIC
CARDIOLOGY
SKIN
MEDICINE
RESULT:
The given program is executed successfully and the result is verified.
11
[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.
12
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
13
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.
14
[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
15
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.
16