18 SQL QUERY - 1
To write SQL-Queries for the following Questions based on the given table
Roll No Name Gender Age Dept DOA Fees
1 Arun M 24 COMPUTER 1997-01- 120
10
2 Ankit M 21 HISTORY 1998-03- 200
24
3 Anu F 20 HINDI 1996-12- 300
12
4 Bala M 19 NULL 1999-07- 400
01
5 Charan M 18 HINDI 1997-09- 250
05
6 Deepa F 19 HISTORY 1997-06- 300
27
7 Dinesh M 22 COMPUTER 1997-02- 210
25
8 Usha F 23 NULL 1997-07- 200
31
(a)Create a new database named "STUDENTS"
CREATE DATABASE STUDENTS;
(b) Open (Use) the "STUDENTS" database
USE STUDENTS;
(c) Create the table STU
CREATE TABLE STU (
Rollno INT PRIMARY KEY,
Name VARCHAR(10),
Gender VARCHAR(3),
Age INT,
Dept VARCHAR(15),
DOA DATE,
Fees INT
);
(d) List all existing databases
SHOW DATABASES;
(e) List all tables in the current database
SHOW TABLES;
(f) Insert all rows into the STU 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);
(g) Display all records from the table STU
SELECT * FROM STU;
(h) Display only Rollno, Name, and Department
SELECT Rollno, Name, Dept FROM STU;
19. SQL QUERY - 2
To write SQL-Queries for the following Questions based on the given table
Rolln Name Gende Age Dept DOA Fees
o r
1 Arun M 24 COMPUTE 1997-01-10 120
R
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 COMPUTE 1997-02-25 210
R
8 Usha F 23 NULL 1997-07-31 200
(a) Delete the details where Roll No is 8
DELETE FROM STU WHERE ROLLNO = 8;
(b) Update the Fees to 170 for Roll No 1, only if current Fees < 130
UPDATE STU SET FEES = 170 WHERE ROLLNO = 1 AND FEES < 130;
(c) Add a new column Area of type VARCHAR
ALTER TABLE STU ADD AREA VARCHAR(20);
(d) Display Names of students where Area is NULL
SELECT NAME FROM STU WHERE AREA IS NULL;
(e) Delete the Area column from the table
ALTER TABLE STU DROP AREA;
(f) Delete the entire table STU from the database
DROP TABLE STU;
20. SQL QUERY - 3
To write Queries for the following Questions based on the given two table
STOCK Table
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
DEALERS Table
Dcode Dname
101 Sakthi Stationeries
103 Classic Stationeries
102 Indian Book House
(a) List All Products with Dcode = 102 and Total (with ORDER BY).
SELECT DCODE, PNAME, UNITPRICE FROM STOCK WHERE DCODE = 102
ORDER BY UNITPRICE DESC;
(b) Display all product details in descending order of StockDate
SELECT * FROM STOCK ORDER BY STOCKDATE DESC;
(c) Display maximum UnitPrice for each dealer (grouped by Dcode)
SELECT DCODE, MAX(UNITPRICE)FROM STOCK GROUP BY DCODE;
(d) Display Pname and Dname by joining STOCK and DEALERS
SELECT PNAME, DNAME FROM STOCK S, DEALERS D WHERE [Link] =
[Link];