-- Part A: DDL
1)CREATE DATABASE SchoolDB;
2) CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Class VARCHAR(10),
Marks INT
);
3)CREATE TABLE Teachers (
TID INT PRIMARY KEY,
TName VARCHAR(50),
Subject VARCHAR(30)
);
4)ALTER TABLE Students ADD Email VARCHAR(100);
5)ALTER TABLE Teachers ADD Phone VARCHAR(15);
6)ALTER TABLE Students MODIFY Marks FLOAT;
7)RENAME TABLE Students TO Pupils;
8)RENAME TABLE Teachers TO Faculty;
9)ALTER TABLE Pupils DROP COLUMN Email;
10)ALTER TABLE Faculty DROP COLUMN Phone;
11)CREATE TABLE Courses (
CID INT PRIMARY KEY,
CName VARCHAR(50),
Duration INT
);
12)ALTER TABLE Pupils MODIFY Name VARCHAR(50)
NOT NULL;
13)ALTER TABLE Faculty ADD CONSTRAINT
uniq_tname UNIQUE (TName);
14)ALTER TABLE Pupils ALTER Age SET DEFAULT 18;
15)ALTER TABLE Pupils ADD CONSTRAINT
chk_marks CHECK (Marks > 0);
16)ALTER TABLE Pupils DROP CHECK chk_marks;
17)CREATE TABLE Library (
BookID INT PRIMARY KEY,
Title VARCHAR(100),
Author VARCHAR(50)
);
18)ALTER TABLE Pupils ADD CONSTRAINT fk_course
FOREIGN KEY (ID) REFERENCES Courses(CID);
19)ALTER TABLE Pupils DROP FOREIGN KEY
fk_course;
20)DROP TABLE Courses;
21)DROP TABLE Library;
22)TRUNCATE TABLE Faculty;
23)TRUNCATE TABLE Pupils;
24)SHOW DATABASES;
25)SHOW TABLES;
-- Part B: DML
26)INSERT INTO Pupils VALUES
(1,'Karan',16,'9B',72),
(2,'Sneha',17,'10C',89),
(3,'Ali',18,'11A',95),
(4,'Neeraj',17,'12B',66),
(5,'Pooja',16,'9A',81);
27)INSERT INTO Faculty VALUES
(1,'Sonal','Biology'),
(2,'Deepak','Physics'),
(3,'Farah','History');
28)INSERT INTO Courses VALUES
(201,'Economics',5),
(202,'Geography',6),
(203,'Computer Science',4);
29)UPDATE Pupils SET Marks=90 WHERE ID=2;
30)UPDATE Pupils SET Age=18 WHERE
Name='Karan';
31)UPDATE Faculty SET Subject='Chemistry' WHERE
TID=1;
32)DELETE FROM Pupils WHERE ID=4;
33)DELETE FROM Faculty WHERE TName='Farah';
34)SELECT * FROM Pupils;
35)SELECT * FROM Faculty;
36)SELECT Name, Marks FROM Pupils;
37)SELECT TName, Subject FROM Faculty;
38)SELECT * FROM Pupils WHERE Marks > 80;
39)SELECT * FROM Pupils WHERE Age < 17;
40)SELECT * FROM Faculty WHERE Subject='Physics';
41)DELETE FROM Pupils;
42)DELETE FROM Faculty;
43)SELECT COUNT(*) FROM Pupils;
44)SELECT COUNT(*) FROM Faculty;
45)SELECT AVG(Marks) FROM Pupils;
46)SELECT MAX(Marks) FROM Pupils;
47)SELECT MIN(Marks) FROM Pupils;
48)SELECT * FROM Pupils ORDER BY Marks DESC;
49)SELECT * FROM Faculty ORDER BY TName ASC;
50)SELECT DISTINCT Class FROM Pupils;
51) SELECT * FROM Pupils WHERE Marks BETWEEN
50 AND 80;
52)SELECT * FROM Pupils WHERE Name LIKE 'A%';
