create database university; use university; create table classroom ( building varchar(15) , room_no

int, capacity int, primary key(building,room_no) );

create table department( dept_name varchar(20) primary key, building varchar(15), budget int
check(budget>0) );

create table course( course_id varchar(7), title varchar(50), dept_name varchar(20), credits int
check(credits>0), primary key(course_id), foreign key(dept_name) references
department(dept_name) on delete set null );

create table instructor( ID varchar(5), name varchar(20) not null, dept_name varchar(20), salary float
check(salary>29000), primary key(ID), foreign key (dept_name) references department(dept_name)
on delete set null ); create table section( course_id varchar(8), sec_id varchar(8), semester varchar(6)
check (semester in ('Fall','Winter','Spring','Summer')), year int check(year>1701 and year<2100),
building varchar(15), room_no int, time_slot_id varchar(4), primary
key(course_id,sec_id,semester,year), foreign key(course_id) references course(course_id) on delete
cascade, foreign key (building,room_no) references classroom(building,room_no) on delete set null );

create table teaches( ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year
int, primary key(ID,course_id,sec_id,semester,year), foreign key(course_id,sec_id,semester,year)
references section(course_id,sec_id,semester,year) on delete cascade, foreign key(ID) references
instructor(ID) on delete cascade );

create table student( ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred int
check(tot_cred>=0), primary key(ID), foreign key(dept_name) references department(dept_name) on
delete set null );

create table takes( ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year
int, grade varchar(2), primary key(ID,course_id,sec_id,semester,year), foreign
key(course_id,sec_id,semester,year) references section(course_id,sec_id,semester,year) on delete
cascade, foreign key(ID) references student(ID) on delete cascade );

create table advisor( s_ID varchar(5), i_ID varchar(5), primary key(s_ID), foreign key(i_id) references
instructor(ID) on delete set null, foreign key(s_id) references student(ID) on delete cascade );

create table prereq( course_id varchar(8), prereq_id varchar(8), primary key(course_id,prereq_id),

foreign key(course_id) references course(course_id) on delete cascade, foreign key(prereq_id)
references course(course_id) );

create table timeslot( time_slot_id varchar(4), day varchar(1) check( day in ('M','T','W','R','F','S','U')),
start_time time, end_time time, primary key(time_slot_id,day,start_time) );

INSERT INTO classroom (building, room_no, capacity) VALUES ('Packard', '101', 500), ('Painter', '514',
10), ('Taylor', '3128', 70), ('Watson', '100', 30), ('Watson', '120', 50);

INSERT INTO department (dept_name, building, budget) VALUES ('Biology', 'Watson', 90000), ('Comp.
Sci.', 'Taylor', 100000), ('Elec. Eng.', 'Taylor', 85000), ('Finance', 'Painter', 120000), ('History', 'Painter',
50000), ('Music', 'Packard', 80000), ('Physics', 'Watson', 70000);

INSERT INTO course (course_id, title, dept_name, credits) VALUES ('BIO-101', 'Intro. to Biology',
'Biology', 4), ('BIO-301', 'Genetics', 'Biology', 4), ('BIO-399', 'Computational Biology', 'Biology', 3), ('CS-
101', 'Intro. to Computer Science', 'Comp. Sci.', 4), ('CS-190', 'Game Design', 'Comp. Sci.', 4), ('CS-315',
'Robotics', 'Comp. Sci.', 3), ('CS-319', 'Image Processing', 'Comp. Sci.', 3), ('CS-347', 'Database System
Concepts', 'Comp. Sci.', 3), ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', 3), ('FIN-201', 'Investment
Banking', 'Finance', 3), ('HIS-351', 'World History', 'History', 3), ('MU-199', 'Music Video Production',
'Music', 3), ('PHY-101', 'Physical Principles', 'Physics', 4);

INSERT INTO instructor (ID, name, dept_name, salary) VALUES ('10101', 'Srinivasan', 'Comp. Sci.',
65000), ('12121', 'Wu', 'Finance', 90000), ('15151', 'Mozart', 'Music', 40000), ('22222', 'Einstein',
'Physics', 95000), ('32343', 'El Said', 'History', 60000), ('33456', 'Gold', 'Physics', 87000), ('45565',
'Katz', 'Comp. Sci.', 75000), ('58583', 'Califieri', 'History', 62000), ('76543', 'Singh', 'Finance', 80000),
('76766', 'Crick', 'Biology', 72000), ('83821', 'Brandt', 'Comp. Sci.', 92000), ('98345', 'Kim', 'Elec. Eng.',
80000);

INSERT INTO section (course_id, sec_id, semester, year, building, room_no, time_slot_id) VALUES
('BIO-101', '1', 'Summer', 2017, 'Painter', '514', 'B'), ('BIO-301', '1', 'Summer', 2018, 'Painter', '514',
'A'), ('CS-101', '1', 'Fall', 2017, 'Packard', '101', 'H'), ('CS-101', '1', 'Spring', 2018, 'Packard', '101', 'F'),
('CS-190', '1', 'Spring', 2017, 'Taylor', '3128', 'E'), ('CS-190', '2', 'Spring', 2017, 'Taylor', '3128', 'A'), ('CS-
315', '1', 'Spring', 2018, 'Watson', '120', 'D'), ('CS-319', '1', 'Spring', 2018, 'Watson', '100', 'B'), ('CS-
319', '2', 'Spring', 2018, 'Taylor', '3128', 'C'), ('CS-347', '1', 'Fall', 2017, 'Taylor', '3128', 'A'), ('EE-181',
'1', 'Spring', 2017, 'Taylor', '3128', 'C'), ('FIN-201', '1', 'Spring', 2018, 'Packard', '101', 'B'), ('HIS-351',
'1', 'Spring', 2018, 'Painter', '514', 'C'), ('MU-199', '1', 'Spring', 2018, 'Packard', '101', 'D'), ('PHY-101',
'1', 'Fall', 2017, 'Watson', '100', 'A');

INSERT INTO teaches (ID, course_id, sec_id, semester, year) VALUES ('10101', 'CS-101', '1', 'Fall',
2017), ('10101', 'CS-315', '1', 'Spring', 2018), ('10101', 'CS-347', '1', 'Fall', 2017), ('12121', 'FIN-201',
'1', 'Spring', 2018), ('15151', 'MU-199', '1', 'Spring', 2018), ('22222', 'PHY-101', '1', 'Fall', 2017),
('32343', 'HIS-351', '1', 'Spring', 2018), ('45565', 'CS-101', '1', 'Spring', 2018), ('45565', 'CS-319', '1',
'Spring', 2018), ('76766', 'BIO-101', '1', 'Summer', 2017), ('76766', 'BIO-301', '1', 'Summer', 2018),
('83821', 'CS-190', '1', 'Spring', 2017), ('83821', 'CS-190', '2', 'Spring', 2017), ('83821', 'CS-319', '2',
'Spring', 2018), ('98345', 'EE-181', '1', 'Spring', 2017);

INSERT INTO student (ID, name, dept_name, tot_cred) VALUES ('00128', 'Zhang', 'Comp. Sci.', 102),
('12345', 'Shankar', 'Comp. Sci.', 32), ('19991', 'Brandt', 'History', 80), ('23121', 'Chavez', 'Finance',
110), ('44553', 'Peltier', 'Physics', 56), ('45678', 'Levy', 'Physics', 46), ('54321', 'Williams', 'Comp. Sci.',
54), ('55739', 'Sanchez', 'Music', 38), ('70557', 'Snow', 'Physics', 0), ('76543', 'Brown', 'Comp. Sci.', 58),
('76653', 'Aoi', 'Elec. Eng.', 60), ('98765', 'Bourikas', 'Elec. Eng.', 98), ('98988', 'Tanaka', 'Biology', 120);

INSERT INTO takes (ID, course_id, sec_id, semester, year, grade) VALUES ('00128', 'CS-101', '1', 'Fall',
2017, 'A'), ('00128', 'CS-347', '1', 'Fall', 2017, 'A-'), ('12345', 'CS-101', '1', 'Fall', 2017, 'C'), ('12345', 'CS-
190', '2', 'Spring', 2017, 'A'), ('12345', 'CS-315', '1', 'Spring', 2018, 'A'), ('12345', 'CS-347', '1', 'Fall',
2017, 'A'), ('19991', 'HIS-351', '1', 'Spring', 2018, 'B'), ('23121', 'FIN-201', '1', 'Spring', 2018, 'C+'),
('44553', 'PHY-101', '1', 'Fall', 2017, 'B-'), ('45678', 'CS-101', '1', 'Fall', 2017, 'F'), ('45678', 'CS-101', '1',
'Spring', 2018, 'B+'), ('45678', 'CS-319', '1', 'Spring', 2018, 'B'), ('54321', 'CS-101', '1', 'Fall', 2017, 'A-'),
('54321', 'CS-190', '2', 'Spring', 2017, 'B+'), ('55739', 'MU-199', '1', 'Spring', 2018, 'A-'), ('76543', 'CS-
101', '1', 'Fall', 2017, 'A'), ('76543', 'CS-319', '2', 'Spring', 2018, 'A'), ('76653', 'EE-181', '1', 'Spring',
2017, 'C'), ('98765', 'CS-101', '1', 'Fall', 2017, 'C-'), ('98765', 'CS-315', '1', 'Spring', 2018, 'B'), ('98988',
'BIO-101', '1', 'Summer', 2017, 'A'), ('98988', 'BIO-301', '1', 'Summer', 2018, NULL);

INSERT INTO advisor (s_id, i_id) VALUES ('00128', '45565'), ('12345', '10101'), ('23121', '76543'),
('44553', '22222'), ('45678', '22222'), ('76543', '45565'), ('76653', '98345'), ('98765', '98345'),
('98988', '76766');
INSERT INTO timeslot (time_slot_id, day, start_time, end_time) VALUES ('A', 'M', '08:00', '08:50'), ('A',
'W', '08:00', '08:50'), ('A', 'F', '08:00', '08:50'), ('B', 'M', '09:00', '09:50'), ('B', 'W', '09:00', '09:50'), ('B',
'F', '09:00', '09:50'), ('C', 'M', '11:00', '11:50'), ('C', 'W', '11:00', '11:50'), ('C', 'F', '11:00', '11:50'), ('D',
'M', '13:00', '13:50'), ('D', 'W', '13:00', '13:50'), ('D', 'F', '13:00', '13:50'), ('E', 'T', '10:30', '11:45'), ('E',
'R', '10:30', '11:45'), ('F', 'T', '14:30', '15:45'), ('F', 'R', '14:30', '15:45'), ('G', 'M', '16:00', '16:50'), ('G',
'W', '16:00', '16:50'), ('G', 'F', '16:00', '16:50'), ('H', 'W', '10:00', '12:30');

INSERT INTO prereq (course_id, prereq_id) VALUES ('BIO-301', 'BIO-101'), ('BIO-399', 'BIO-101'), ('CS-
190', 'CS-101'), ('CS-315', 'CS-101'), ('CS-319', 'CS-101'), ('CS-347', 'CS-101'), ('EE-181', 'PHY-101');

use university; SELECT name FROM instructor WHERE dept_name ='Biology';

SELECT title FROM course WHERE dept_name='Comp. Sci.' AND credits =3;

SELECT t.course_id, c.title FROM takes as t, course as c WHERE c.course_id=t.course_id AND

ID='12345';

SELECT sum(credits) FROM takes as t,course as c WHERE c.course_id=t.course_id AND ID='12345';

SELECT t.ID,sum(credits) FROM takes as t,course as c WHERE t.course_id=c.course_id GROUP BY t.ID;

SELECT distinct name FROM student as s, takes as t WHERE s.ID=t.ID AND course_id like "CS%";

SELECT i.ID FROM instructor as i WHERE i.ID NOT IN( SELECT t.ID FROM teaches as t);

SELECT i.ID,name FROM instructor as i WHERE i.ID NOT IN( SELECT t.ID FROM teaches as t);

use university;

select * from student; select s.ID from student as s natural left outer join takes where course_id is
null;

-- 2 select s.ID from student as s left outer join advisor as a on s.ID= a.s_id where a.i_id is null;

insert into course(course_id, title, credits) values ('CS-001', 'Weekly Seminar', 1);

insert into section(course_id, sec_id, semester, year) values ('CS-001','1','Fall','2017');

-- c insert into takes(ID, course_id, sec_id, semester, year, grade) select s.ID, 'CS-001','1','Fall','2017',
null from student as s where s.dept_name='Comp. Sci.';

-- d delete from takes where ID='12345' AND course_id = 'CS-001' AND sec_id='1' AND
semester='Fall' AND year='2017';

-- e delete from course where course_id='CS-001';

-- f delete from takes where course_id in( select course_id from course where title like %advanced
% );

-- 4 select dept_name from department where budget >( select budget from department where
dept_name='Biology' ) order by dept_name;

-- 5 select t.ID, t.course_id from takes as t where (select count(*) from takes as t1 where t.ID = t1.ID
and t.course_id= t1.course_id)>=2 order by t.course_id, t.ID;

-- assignment4 -- 7 create table grade_points( grade char(2), points int ); -- a insert into
grade_points(grade,points) values ('A',4),('A-',3.7),('B+',3.3),('B',3),('B-',2.7),('C+',2.3),('C',2),('C-',1.7),
('D+',1.3),('D',1),('D-',0.7),('E',0.3),('F',0); SELECT
TAKES.ID,SUM(COURSE.CREDITS*GRADE_POINTS.POINTS) AS POINTS_EARNED FROM COURSE
NATURAL JOIN TAKES NATURAL JOIN GRADE_POINTS GROUP BY TAKES.ID HAVING TAKES.ID='23121';

-- b SELECT TAKES.ID,SUM(COURSE.CREDITS*GRADE_POINTS.POINTS)/sum(credits) AS
AVG_POINTS_EARNED FROM COURSE NATURAL JOIN TAKES NATURAL JOIN GRADE_POINTS GROUP
BY TAKES.ID HAVING TAKES.ID='23121';

-- c SELECT TAKES.ID,SUM(COURSE.CREDITS*GRADE_POINTS.POINTS)/sum(credits) AS
AVG_POINTS_EARNED FROM COURSE NATURAL JOIN TAKES NATURAL JOIN GRADE_POINTS GROUP
BY TAKES.ID;

-- d SELECT TAKES.ID,SUM(COURSE.CREDITS*GRADE_POINTS.POINTS)/sum(credits) AS
AVG_POINTS_EARNED FROM COURSE NATURAL JOIN TAKES JOIN GRADE_POINTS on
takes.grade=grade_points.grade GROUP BY TAKES.ID union select takes.id,0 from takes where grade
is null;

create database movie; use movie; create table actors( AID varchar(10) PRIMARY KEY, name
varchar(20) );

create table movies( MID varchar(10) PRIMARY KEY, title varchar(50) );

create table actor_role( AID varchar(10), MID varchar(10), rolename varchar(50), primary
key(AID,MID,rolename), foreign key(AID) references actors(AID) ON DELETE CASCADE, foreign
key(MID) references movies(MID) ON DELETE CASCADE ); -- 10 insert into actors values("1","charlie
chaplin"),("2","ryan gosling"),("3","nina dobrev"),("4","ian"),("5","matthew"); insert into movies
values("M1","limelight"),("M2","interstellar"),("M3","friends"),("M4","chaplin"),("M5","TVD"); insert
into actor_role values("1","M1","Calvero"),("1","M1","ambrose"),("3","M5","elena"),
("3","M5","katherine"),("4","M5","damon"),("5","M2","copper"); use movie; SELECT * FROM
actor_role; -- 11 SELECT m.title, count(r.AID) FROM movies as m natural join actor_role as r natural
join actors as a WHERE a.name="charlie chaplin" GROUP BY m.title; -- 12 SELECT a.name, m.titles
FROM actors as a, movies as m WHERE AID NOT IN( SELECT AID FROM actor_role); -- 13
select a.name as actor_name, (select group_concat(distinct m.title separator ',') from movies as m,
actor_role as r where a.AID = r.AID and m.MID = r.MID) as movie_Titles from actors as a;

-- 13 List names of actors, along with titles of movies they have acted in. If they have not acted in any
movie, show the movie title as null. SELECT distinct a.name, m.title FROM actors AS a LEFT OUTER
JOIN actor_role AS r ON a.AID = r.AID LEFT OUTER JOIN movies AS m ON r.MID = m.MID;

create database employee1; use employee1; create table employee( id varchar(5) primary key,
person_name varchar(15), street varchar(15), city varchar(15) );

create table company( company_name varchar(50) primary key, city varchar(15) );

create table works( id varchar(5) primary key, company_name varchar(50), salary numeric(12,0),
foreign key(id) references employee(id) on delete cascade, foreign key(company_name) references
company(company_name) on delete set null );

create table manages( id varchar(5) primary key, manager_id varchar(5), foreign key(id) references
employee(id) on delete cascade );

-- Insert into employee INSERT INTO employee (id, person_name, street, city) VALUES ('E001', 'Alice',
'5th Ave', 'New York'), ('E002', 'Bob', 'Main St', 'Los Angeles'), ('E003', 'Charlie', 'State St', 'Chicago'),
('E004', 'David', 'Elm St', 'Houston'), ('E005', 'Emma', 'Sunset Blvd', 'Miami'), ('E006', 'Frank',
'Broadway', 'New York'), ('E007', 'Grace', 'Pine St', 'San Francisco');

-- Insert into company INSERT INTO company (company_name, city) VALUES ('First Bank
Corporation', 'New York'), ('Small Bank Corporation', 'Los Angeles'), ('Tech Corp', 'Chicago'), ('Finance
Inc', 'Houston'), ('Retail Ltd', 'Miami');

-- Insert into works INSERT INTO works (id, company_name, salary) VALUES ('E001', 'First Bank
Corporation', 12000), ('E002', 'Small Bank Corporation', 9000), ('E003', 'Tech Corp', 15000), ('E004',
'Finance Inc', 18000), ('E005', 'Retail Ltd', 11000), ('E006', 'First Bank Corporation', 8000), ('E007',
'Tech Corp', 16000);

-- Insert into manages INSERT INTO manages (id, manager_id) VALUES ('E001', 'E004'), -- Alice is
managed by David ('E002', 'E003'), -- Bob is managed by Charlie ('E003', 'E004'), -- Charlie is managed
by David ('E004', 'E005'), -- David is managed by Emma ('E005', 'E006'), -- Emma is managed by Frank
('E006', 'E007'), -- Frank is managed by Grace ('E007', NULL); -- Grace has no manager

-- 2 -- a select e.id,e.person_name,e.city from employee as e natural join (select w.id from works as w
where company_name="First Bank Corporation") as t;

-- b select e.id,e.person_name,e.city from employee as e natural join (select w.id from works as w
where company_name="First Bank Corporation" and salary>10000) as t;

-- c select e.id from employee as e where e.id not in (select id from works where
company_name="First Bank Corporation");

-- d SELECT w1.id FROM works w1 WHERE w1.salary > (SELECT MAX(w2.salary) FROM works w2
WHERE w2.company_name = 'Small Bank Corporation');

-- e SELECT c1.company_name FROM company c1 WHERE NOT EXISTS ( SELECT c2.city FROM
company c2 WHERE c2.company_name = 'Small Bank Corporation' AND NOT EXISTS ( SELECT
c3.company_name FROM company c3 WHERE c3.city = c2.city AND c3.company_name =
c1.company_name));

-- f SELECT w.company_name FROM works w GROUP BY w.company_name HAVING COUNT(w.id) =

( SELECT MAX(employee_count) FROM (SELECT COUNT(id) AS employee_count FROM works GROUP
BY company_name) AS subquery ); -- 3 -- a UPDATE employee SET city = 'Newtown' WHERE id =
'12345'; select * from employee;

-- b UPDATE works SET salary = CASE WHEN salary * 1.10 <= 100000 THEN salary * 1.10 ELSE salary *
1.03 END WHERE id IN (SELECT manager_id FROM manages) AND company_name = 'First Bank
Corporation';

-- 5 -- a SELECT e.id, e.person_name FROM employee e JOIN works w ON e.id = w.id JOIN company c
ON w.company_name = c.company_name WHERE e.city = c.city;

-- b SELECT e.id, e.person_name FROM employee e JOIN manages m ON e.id = m.id JOIN employee
mngr ON m.manager_id = mngr.id WHERE e.city = mngr.city AND e.street = mngr.street;

-- c SELECT e.id, e.person_name FROM employee e JOIN works w1 ON e.id = w1.id WHERE w1.salary
> ( SELECT AVG(w2.salary) FROM works w2 WHERE w2.company_name = w1.company_name );

-- d SELECT w.company_name FROM works w GROUP BY w.company_name ORDER BY SUM(w.salary)

ASC LIMIT 1;
-- 6 -- a UPDATE works SET salary = salary * 1.10 WHERE company_name = 'First Bank Corporation';
SELECT * FROM works;

-- b UPDATE works SET salary = salary * 1.10 WHERE id IN (SELECT manager_id FROM manages) AND
company_name = 'First Bank Corporation'; SELECT * FROM works;

-- c DELETE FROM works WHERE company_name = 'Small Bank Corporation'; SELECT * FROM works;

use university; -- 1 SELECT MAX(enrollment) AS max_enrollment, MIN(enrollment) AS

min_enrollment FROM (SELECT course_id, sec_id,semester, year, COUNT(ID) AS enrollment FROM
takes GROUP BY course_id,sec_id,semester,year) AS section_enrollment;

-- 2 SELECT course_id,sec_id,semester,year, COUNT() as enrollment FROM takes GROUP BY

course_id,sec_id,semester,year HAVING COUNT() = (SELECT MAX(enrollment) FROM (SELECT
COUNT(*) AS enrollment FROM takes GROUP BY course_id,sec_id,semester,year) AS
max_enrollment);

-- 3 -- a select max(sub.enrollment), min(sub.enrollment) from ( select sec.course_id, sec.sec_id,

sec.semester, sec.year,( select count(t.id) from takes as t where sec.course_id = t.course_id and
sec.sec_id = t.sec_id and sec.semester = t.semester and sec.year = t.year group by t.course_id,
t.sec_id, t.semester, t.year ) as enrollment from section as sec ) as sub;

-- b select max(sub.enrollment), min(sub.enrollment) from ( select sec.course_id, sec.sec_id,

sec.semester, sec.year, count(t.ID) as enrollment from section sec left outer join takes t on
sec.course_id = t.course_id and sec.sec_id = t.sec_id and sec.semester = t.semester and sec.year =
t.year group by sec.course_id, sec.sec_id, sec.semester, sec.year ) as sub;

-- 4 SELECT course_id,title FROM course WHERE course_id like'CS-1%';

-- 5 -- a SELECT i.id,i.name FROM instructor i WHERE EXISTS( (SELECT course_id FROM course WHERE
course_id like 'CS-1%') EXCEPT (SELECT t.course_id FROM teaches t WHERE i.ID=t.ID) );

-- b SELECT i.id,i.name FROM instructor i,teaches t WHERE i.ID=t.ID AND t.course_id like 'CS-1%'
GROUP BY i.id,i.name HAVING COUNT(t.course_id)=( SELECT COUNT(*) FROM course WHERE
course_id like 'CS-1%');

-- 6 DELETE FROM instructor where id='76543';

INSERT INTO student (ID, name, dept_name, tot_cred) SELECT ID, name, dept_name, 0 FROM
instructor;

SELECT * FROM student;

-- 7 SET SQL_SAFE_UPDATES=0; DELETE FROM student WHERE ID IN( SELECT ID FROM instructor)
AND tot_cred=0;

SELECT * FROM student;

-- 8

-- 9 UPDATE instructor SET salary= salary+ (SELECT COUNT(ID)*10000 FROM teaches WHERE
teaches.ID=instructor.ID);

SELECT * FROM instructor;

-- 10
 CREATE DATABASE salesDB;

USE salesDB; CREATE TABLE CLIENT_MASTER ( CLIENTNO VARCHAR(6) PRIMARY KEY CHECK
(CLIENTNO LIKE 'C%'), NAME VARCHAR(20) NOT NULL, ADDRESS1 VARCHAR(30), ADDRESS2
VARCHAR(30), CITY VARCHAR(15), PINCODE INT(8), STATE VARCHAR(15), BALDUE FLOAT(10,2) );

CREATE TABLE PRODUCT_MASTER ( PRODUCTNO VARCHAR(6) PRIMARY KEY CHECK (PRODUCTNO

LIKE 'P%'), DESCRIPTION VARCHAR(15) NOT NULL, PROFITPERCENT DECIMAL(4,2) NOT NULL,
UNITMEASURE VARCHAR(10) NOT NULL, QTYONHAND INT(8) NOT NULL, REORDERLVL INT(8) NOT
NULL, SELLPRICE DECIMAL(8,2) NOT NULL CHECK (SELLPRICE > 0), COSTPRICE DECIMAL(8,2) NOT
NULL CHECK (COSTPRICE > 0) );

CREATE TABLE SALESMAN_MASTER ( SALESMANNO VARCHAR(6) PRIMARY KEY CHECK (SALESMANNO

LIKE 'S%'), SALESMANNAME VARCHAR(20) NOT NULL, ADDRESS1 VARCHAR(30) NOT NULL,
ADDRESS2 VARCHAR(30), CITY VARCHAR(20), PINCODE INT(8), STATE VARCHAR(20), SALAMT
FLOAT(8,2) NOT NULL CHECK (SALAMT > 0), TOTTOGET FLOAT(6,2) NOT NULL CHECK (TOTTOGET > 0),
YTDSALES FLOAT(6,2) NOT NULL, REMARKS VARCHAR(60) );

CREATE TABLE SALES_ORDER ( ORDERNO VARCHAR(6) PRIMARY KEY CHECK (ORDERNO LIKE 'O%'),
CLIENTNO VARCHAR(6), ORDERDATE DATE NOT NULL, DELYADDR VARCHAR(25), SALESMANNO
VARCHAR(6), DELYTYPE CHAR(1), CHECK (DELYTYPE IN ('P', 'F')), BILLYN CHAR(1), DELYDATE DATE,
CHECK (DELYDATE >= ORDERDATE), ORDERSTATUS VARCHAR(10), CHECK (ORDERSTATUS IN ('In
Process', 'Fulfilled', 'BackOrder', 'Cancelled')), FOREIGN KEY (CLIENTNO) REFERENCES
CLIENT_MASTER(CLIENTNO), FOREIGN KEY (SALESMANNO) REFERENCES
SALESMAN_MASTER(SALESMANNO) );

CREATE TABLE SALES_ORDER_DETAILS( ORDERNO VARCHAR(6), PRODUCTNO VARCHAR(6),

QTYORDERED INT(8), QTYDISP INT(8), PRODUCTRATE FLOAT(10,2), FOREIGN KEY (ORDERNO)
REFERENCES SALES_ORDER(ORDERNO), FOREIGN KEY (PRODUCTNO) REFERENCES
PRODUCT_MASTER(PRODUCTNO) );

INSERT INTO CLIENT_MASTER (CLIENTNO, NAME, ADDRESS1, ADDRESS2, CITY, PINCODE, STATE,
BALDUE) VALUES ('C00001', 'John Doe', '123 Main St', 'Apt 4', 'Mumbai', 400001, 'Maharashtra',
5000), ('C00002', 'Jane Smith', '456 Park Ave', 'Suite 3', 'Delhi', 110001, 'Delhi', 12000), ('C00003',
'Amit Kumar', '789 MG Road', '', 'Bangalore', 560001, 'Karnataka', 8000), ('C00004', 'Priya Sharma',
'101 Patel Nagar', '', 'Mangalore', 575001, 'Karnataka', 15000), ('C00005', 'Rahul Verma', '202
Connaught Place', 'Block B', 'Chennai', 600001, 'Tamil Nadu', 9000);

INSERT INTO PRODUCT_MASTER (PRODUCTNO, DESCRIPTION, PROFITPERCENT, UNITMEASURE,

QTYONHAND, REORDERLVL, SELLPRICE, COSTPRICE) VALUES ('P00001', 'Laptop', 15.00, 'Unit', 10, 5,
525, 400), ('P07965', 'Smartphone', 20.00, 'Unit', 15, 10, 8400, 7000), ('P07885', 'Tablet', 18.00, 'Unit',
8, 3, 5250, 4200), ('P07868', 'Monitor', 12.00, 'Unit', 20, 8, 3150, 2500), ('P0345', 'Keyboard', 25.00,
'Unit', 30, 10, 1050, 800), ('P03453', 'Mouse', 30.00, 'Unit', 50, 20, 1050, 750), ('P06734', 'Printer',
10.00, 'Unit', 5, 2, 12000, 10000), ('P07975', 'Scanner', 15.00, 'Unit', 12, 5, 1050, 850);

INSERT INTO SALESMAN_MASTER (SALESMANNO, SALESMANNAME, ADDRESS1, ADDRESS2, CITY,

PINCODE, STATE, SALAMT, TOTTOGET, YTDSALES, REMARKS) VALUES ('S00001', 'Ramesh Gupta', '201
MG Road', 'Block A', 'Mumbai', 400002, 'Maharashtra', 30000, 2000, 1500, 'Top performer'),
('S00002', 'Suresh Mehta', '456 Hill View', 'Sector 12', 'Delhi', 110002, 'Delhi', 25000, 1800, 1400,
'Experienced'), ('S00003', 'Anita Joshi', '678 Jayanagar', 'Block D', 'Bangalore', 560002, 'Karnataka',
28000, 1900, 1300, 'Consistent performer'), ('S00004', 'Neha Kapoor', '789 Green Street', 'Flat 7',
'Chennai', 600002, 'Tamil Nadu', 26000, 1750, 1200, 'New joinee');

INSERT INTO SALES_ORDER (ORDERNO, CLIENTNO, ORDERDATE, SALESMANNO, DELYTYPE, BILLYN,

DELYDATE, ORDERSTATUS) VALUES ('O19001', 'C00001', '2002-07-20', 'S00001', 'F', 'N', '2004-06-12',
'In Process'), ('O19002', 'C00002', '2002-06-27', 'S00002', 'P', 'N', '2004-06-25', 'Cancelled'),
('O46865', 'C00003', '2002-02-20', 'S00003', 'F', 'Y', '2004-02-18', 'Fulfilled'), ('O19003', 'C00001',
'2002-04-07', 'S00001', 'F', 'Y', '2004-04-03', 'Fulfilled'), ('O46866', 'C00004', '2002-05-22', 'S00002',
'P', 'N', '2004-05-20', 'Cancelled'), ('O19008', 'C00005', '2002-07-26', 'S00004', 'F', 'N', '2004-05-24',
'In Process');

INSERT INTO SALES_ORDER_DETAILS (ORDERNO, PRODUCTNO, QTYORDERED, QTYDISP,

PRODUCTRATE) VALUES ('O19001', 'P00001', 4, 4, 525), ('O19001', 'P07965', 2, 1, 8400), ('O19001',
'P07885', 2, 1, 5250), ('O19002', 'P00001', 10, 0, 525), ('O46865', 'P07868', 3, 3, 3150), ('O46865',
'P07885', 3, 1, 5250), ('O46865', 'P00001', 10, 10, 525), ('O46865', 'P0345', 4, 4, 1050), ('O19003',
'P03453', 2, 2, 1050), ('O19003', 'P06734', 1, 1, 12000), ('O46866', 'P07965', 1, 0, 8400), ('O46866',
'P07975', 1, 0, 1050), ('O19008', 'P00001', 10, 5, 525), ('O19008', 'P07975', 5, 3, 1050);

-- 3(I) -- a SELECT NAME FROM CLIENT_MASTER WHERE NAME LIKE '_a%';

-- b SELECT CLIENTNO, NAME, CITY FROM CLIENT_MASTER WHERE CITY LIKE 'M%';

-- c SELECT * FROM CLIENT_MASTER WHERE CITY IN ('Bangalore', 'Mangalore');

-- d SELECT * FROM CLIENT_MASTER WHERE BALDUE > 10000;

-- e SELECT * FROM SALES_ORDER WHERE MONTH(ORDERDATE) = 6;

-- f SELECT * FROM SALES_ORDER WHERE CLIENTNO IN ('C00001', 'C00002');

-- g SELECT * FROM PRODUCT_MASTER WHERE SELLPRICE > 500 AND SELLPRICE <= 750;

-- h SELECT DESCRIPTION, SELLPRICE, SELLPRICE * 1.15 AS new_price FROM PRODUCT_MASTER

WHERE SELLPRICE > 500;

-- i SELECT NAME,CITY,STATE FROM CLIENT_MASTER WHERE STATE NOT IN ('Maharashtra');

-- j SELECT COUNT(ORDERNO) AS TOTAL_ORDERS FROM SALES_ORDER;

-- K SELECT DESCRIPTION, AVG(SELLPRICE) as avg_sellPrice, AVG(COSTPRICE) AS avg_costPrice FROM

PRODUCT_MASTER GROUP BY DESCRIPTION;

-- l SELECT MAX(SELLPRICE) AS max_price, MIN(SELLPRICE) AS min_price FROM PRODUCT_MASTER;

-- m SELECT COUNT(PRODUCTNO) FROM PRODUCT_MASTER WHERE SELLPRICE <= 500;

-- n SELECT * FROM PRODUCT_MASTER WHERE QTYONHAND < REORDERLVL;

-- 3(II) -- a SELECT ORDERNO, DAYNAME(ORDERDATE) FROM SALES_ORDER;

-- b SELECT MONTHNAME(DELYDATE), DAY(DELYDATE) FROM SALES_ORDER;

-- c SELECT DATE_FORMAT(ORDERDATE, '%d-%M-%y') FROM SALES_ORDER;

-- d SELECT DATE_ADD(CURDATE(), INTERVAL 15 DAY);

-- 4
use university;

-- 1 create view NonOverriddenFails as select * from takes where grade = 'F' and ID not in ( select t.ID
from takes t where t.grade in ('A', 'B', 'C', 'D') ); select * from NonOverriddenFails;

-- 2 drop view multipleFails; create view multipleFails as select ID from takes where grade ='F' and ID
not in ( select t.ID from takes t where t.grade in ('A', 'B', 'C', 'D') ) group by ID having count(*)>2;
select * from multipleFails;

-- 3 create table grade_points( grade varchar(5), points numeric ); insert into grade_points (grade,
points) values ('A', 10), ('B', 8), ('C', 6), ('D', 4), ('F', 0);

create view StudentCPI as select s.ID, coalesce(SUM(g.points * t.credits) / SUM(t.credits), null) as CPI
FROM student s LEFT JOIN takes t ON s.ID = t.ID LEFT JOIN grade_points g ON t.grade = g.grade
GROUP BY s.ID; select * from StudentCPI;

-- 4 drop view rooms;

create view rooms as select distinct s1.sec_id as section1 , s2.sec_id as section2, s1.room_no,
s1.time_slot_id from section s1 join section s2 where s1.room_no = s2.room_no and s1.time_slot_id
= s2.time_slot_id and s1.sec_id <> s2.sec_id; select * from rooms;

-- 5 create view faculty as select ID, name, dept_name from instructor; select * from faculty;

-- 6 create view CSinstructors as select * from instructor where dept_name= 'Comp. Sci.'; select *
from CSinstructors;

-- 7 insert into faculty(ID, name, dept_name) values(10001,'John', 'Physics'); select * from faculty;

insert into CSinstructors (ID, name, dept_name, salary) values (20202, 'Alice', 'Comp. Sci.', 90000);
select * from CSinstructors;

insert into CSinstructors(ID, name, dept_name, salary) values(20001, 'Smith', 'Music', 40000); select
* from CSinstructors;

-- 8

-- 9 grant select on faculty to PUBLIC;

-- Student Table CREATE TABLE student ( ID INT PRIMARY KEY, name VARCHAR(100) NOT NULL,
dept_name VARCHAR(50), tot_cred INT );

-- Employee Table with Age Constraint CREATE TABLE employee ( emp_id INT PRIMARY KEY, name
VARCHAR(100), age INT CHECK (age >= 18) );

-- Course Table CREATE TABLE course ( course_id VARCHAR(10) PRIMARY KEY, title VARCHAR(50),
dept_name VARCHAR(50), credits INT CHECK (credits > 0) );

-- Prerequisite Table with Foreign Key CREATE TABLE prereq ( course_id VARCHAR(10), prereq_id
VARCHAR(10), PRIMARY KEY (course_id, prereq_id), FOREIGN KEY (course_id) REFERENCES
course(course_id) ON DELETE CASCADE );

-- Instructor Table with Salary Constraint CREATE TABLE instructor ( ID INT PRIMARY KEY, name
VARCHAR(100), dept_name VARCHAR(50), salary INT CHECK (salary > 30000) );
-- Takes Table with Composite Primary Key CREATE TABLE takes ( student_id INT, course_id
VARCHAR(10), sec_id VARCHAR(10), semester VARCHAR(10), year INT, grade CHAR(1), PRIMARY KEY
(student_id, course_id, sec_id, semester, year) );

-- Events Table for Date/Time Queries CREATE TABLE events ( event_id INT PRIMARY KEY, event_name
VARCHAR(100), event_date DATE, percentage FLOAT, present_days INT );

-- 1. Create NOT NULL constraint (already applied in student table) -- 2. Add UNIQUE constraint ALTER
TABLE student ADD CONSTRAINT unique_name UNIQUE (name);

-- 3. Create Employee table with CHECK constraint (already applied) -- 4. Insert a record violating NOT
NULL constraint (already tested) -- 5. Define FOREIGN KEY in prereq table (already applied) -- 6. Drop
FOREIGN KEY ALTER TABLE prereq DROP CONSTRAINT fk_course;

-- 7. Modify table to add salary CHECK constraint (already applied) -- 8. Demonstrate ON DELETE
CASCADE DELETE FROM course WHERE course_id = 'CS101'; -- Also deletes from prereq

-- 9. Create an ASSERTION (if supported) CREATE ASSERTION student_credit_limit CHECK (NOT EXISTS
(SELECT * FROM student WHERE tot_cred > 200));

-- 10. Create a Composite Primary Key (already applied in takes table)

-- 11. Create a table using DATE and TIME types (already done in events) -- 12. Insert a record with
DATE and TIME (already done) -- 13. Use CAST to convert a string to a DATE SELECT CAST('2025-03-
25' AS DATE);

-- 14. Extract the year from DATE SELECT event_name, EXTRACT(YEAR FROM event_date) AS
event_year FROM events;

-- 15. DEFAULT constraint for timestamp CREATE TABLE logs ( log_id INT PRIMARY KEY, log_time
TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- 16. Use INTERVAL for date calculations SELECT event_name, event_date, event_date + INTERVAL
'10 days' AS new_event_date FROM events;

-- 17. ENUM Data Type (If Supported) CREATE TABLE course_level ( level ENUM('Beginner',
'Intermediate', 'Advanced') );

-- 18. Convert FLOAT to INTEGER SELECT event_name, percentage, CAST(percentage AS INT) AS

rounded_percentage FROM events;

-- 19. BOOLEAN Data Type CREATE TABLE attendance ( student_id INT, present BOOLEAN DEFAULT
FALSE );

-- 20. COALESCE to handle NULLs SELECT event_name, COALESCE(present_days, 0) AS

attendance_days FROM events;

-- 21. Create an index CREATE INDEX idx_student_name ON student(name);

-- 22. Function-based index (if supported) CREATE INDEX idx_lower_name ON

student(LOWER(name));

-- 23. UNIQUE index CREATE UNIQUE INDEX idx_unique_student_name ON student(name);

-- 24. Drop an existing index DROP INDEX idx_student_name;

-- 25. EXPLAIN query performance EXPLAIN ANALYZE SELECT * FROM student WHERE name = 'Alice';

-- 26. Composite index CREATE INDEX idx_name ON student(name, dept_name);

-- 27. Partial index CREATE INDEX idx_active_students ON student(name) WHERE tot_cred > 0;

-- 28. Query performance before and after index EXPLAIN ANALYZE SELECT * FROM student WHERE
dept_name = 'CSE';

-- 29 ALTER TABLE student ADD CONSTRAINT unique_student_name UNIQUE (name);

-- 30 CREATE FULLTEXT INDEX idx_fulltext ON student(name);

-- 31. Grant SELECT to user1 GRANT SELECT ON student TO user1;

-- 32. Revoke INSERT from user1 REVOKE INSERT ON student FROM user1;

-- 33. Grant ALL to admin GRANT ALL ON department TO admin;

-- 34. Grant SELECT and UPDATE to multiple users GRANT SELECT, UPDATE ON student TO user1,
user2;

-- 35. Create Role CREATE ROLE student_manager; GRANT SELECT, UPDATE ON student TO
student_manager;

-- 36. Show privileges SHOW GRANTS FOR user1;

-- 37. Restricted View CREATE VIEW student_view AS SELECT name, dept_name FROM student;
GRANT SELECT ON student_view TO user1;

-- 38. WITH GRANT OPTION GRANT SELECT ON student TO user1 WITH GRANT OPTION;

-- 39. Assign Role GRANT manager TO user1;

-- 40. Revoke DELETE from manager REVOKE DELETE ON student FROM manager; take reference
from these queries and make a snippet covering all types of queries in general, there maybe a case I
can get to write the same query for different database so calling the query should be easier by
general name