SQL EXERCISE - 1

Ex.No: 20
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"

CREATE DATABASE STUDENTS;

(b) Write a Query to Open the database "STUDENTS"

USE STUDENTS;

(c) Write a Query to create the above table called “STU”.

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.

SHOW DATABASES;

(e) Write a Query to List all the tables that exists in the current database.
SHOW TABLES;
 SQL EXERCISE - 2
Ex.No: 21
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 insert all the rows of above table into Info table.
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);

(b) Write a Query to display all the details from the above table 'STU'.

SELECT * FROM STU;

(c) Write a query to display Rollno, Name and Department of the students from STU table.

SELECT ROLLNO, NAME, DEPT FROM STU;

(d) Write a Query to select distinct Department from STU table.

SELECT DISTICT(DEPT) FROM STU;

(e) Write a query to show all information about students of History department.

SELECT * FROM STU WHERE DEPT='HISTORY';

********************************************************************************************
Ex.No: 22 SQL 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 list the names of female students in Hindi Department.

SELECT NAME FROM STU WHERE DEPT='HINDI' AND GENDER='F';

(b) Write a Query to list name of the students whose ages are between 18 to 20.

SELECT NAME FROM STU WHERE AGE BETWEEN 18 AND 20;

(c) Write a Query to display the name of the students whose name is starting with 'A'.

SELECT NAME FROM STU WHERE NAME LIKE 'A%';

(d) Write a query to list the names of those students whose name have second alphabet 'n' in their names.

SELECT NAME FROM STU WHERE NAME LIKE '_N%';

Ex.No: 23 SQL EXERCISE - 4

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 8.

DELETE FROM STU WHERE ROLLNO=8;

(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.

UPDATE STU SET FEES=170 WHERE ROLLNO=1 AND FEES<130;

(c) Write a Query to add a new column Area of type varchar in table STU.

ALTER TABLE STU ADD AREA VARCHAR(20);

(d) Write a Query to Display Name of all students whose Area Contains NULL.

SELECT NAME FROM STU WHERE AREA IS NULL;

(e) Write a Query to delete Area Column from the table STU.

ALTER TABLE STU DROP AREA;

(f) Write a Query to delete table from Database.

DROP TABLE STU;

*******************************************************************************************
Ex.No: 24 SQL EXERCISE– 5
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 Stationaries
103 Classic Stationaries
102 Indian Book House

(a) To display the total Unit price of all the products whose Dcode is 102.

SELECT SUM(UNITPRICE) FROM STOCK WHERE DCODE=102;

Sum(UNITPRICE)
33

(b) To display details of all products in the stock table in descending order of Stock date.

SELECT * FROM STOCK ORDER BY STOCKDATE DESC;

Pno Pname Dcode Qty UnitPrice StockDate

5009 Gel pen classic 103 160 8 2022-01-19
5005 Ball point pen 102 100 10 2021-03-31
5002 Pencil 101 125 4 2021-02-18
5003 Gel pen 102 150 15 2021-01-01
premium
5001 Eraser 102 210 3 2020-03-19
5006 Scale 101 200 6 2020-01-01
(c) To display maximum unit price of products for each dealer individually as per dcode from the table
Stock.

SELECT DCODE,MAX(UNITPRICE) FROM STOCK GROUP BY DCODE;

DECODE MAX(UNITPRICE)

102 15

101 6

103 8

(d) To display the Pname and Dname from table stock and dealers.

SELECT PNAME,DNAME FROM STOCK S,DEALERS D WHERE S.DCODE=D.DCODE;

PNAME DNAME
Pencil Sakthi Stationaries
Scale Sakthi Stationaries
Gel Pen Classic Classic Stationaries
Ball point pen Indian Book House
Gel pen premium Indian Book House
Eraser Indian Book House
Sharpner Indian Book House

*************************************************************************************************