Practical No.
1. Create Database
CREATE DATABASE university;
USE university;
TABLE CREATION QUERIES
1. department
CREATE TABLE department (
dept_name VARCHAR(20),
building VARCHAR(15),
budget INT,
PRIMARY KEY (dept_name)
);
2. course
CREATE TABLE course (
course_id VARCHAR(15) NOT NULL,
course_name VARCHAR(20),
dept_name VARCHAR(20),
credits INT,
PRIMARY KEY (course_id),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
3. instructor
CREATE TABLE instructor (
inst_id VARCHAR(5),
inst_name VARCHAR(20) NOT NULL,
dept_name VARCHAR(20),
salary INT,
PRIMARY KEY (inst_id),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
4. section
CREATE TABLE section (
course_id VARCHAR(15),
sec_id VARCHAR(8),
semester VARCHAR(6),
year INT,
building VARCHAR(15),
PRIMARY KEY (course_id, sec_id, semester, year),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);
5. teaches
CREATE TABLE teaches (
inst_id VARCHAR(5),
course_id VARCHAR(15),
sec_id VARCHAR(8),
semester VARCHAR(6),
year INT,
PRIMARY KEY (inst_id, course_id, sec_id, semester, year),
FOREIGN KEY (inst_id) REFERENCES instructor(inst_id)
1
);
6. student
CREATE TABLE student (
stud_id VARCHAR(5),
stu_name VARCHAR(10),
dept_name VARCHAR(20),
tot_credit INT,
PRIMARY KEY (stud_id),
FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
7. takes
CREATE TABLE takes (
stud_id VARCHAR(5),
course_id VARCHAR(15),
sec_id VARCHAR(8),
semester VARCHAR(6),
year INT,
grade INT,
PRIMARY KEY (stud_id, course_id, sec_id, semester, year),
FOREIGN KEY (stud_id) REFERENCES student(stud_id),
FOREIGN KEY (course_id, sec_id, semester, year)
REFERENCES section(course_id, sec_id, semester, year)
);
INSERT QUERIES
department
INSERT INTO department VALUES ('Biology','Watson',90000);
INSERT INTO department VALUES ('Comp_Sci','Taylor',100000);
INSERT INTO department VALUES ('Elec_Engg','Taylor',85000);
INSERT INTO department VALUES ('Finance','Painter',120000);
INSERT INTO department VALUES ('Music','Packard',50000);
INSERT INTO department VALUES ('Physics','Watson',78000);
INSERT INTO department VALUES ('History','Painter',50000);
course
INSERT INTO course VALUES ('BIO-101','Intro. to Biology','Biology',4);
INSERT INTO course VALUES ('CS-101','DBMS','Comp_Sci',4);
INSERT INTO course VALUES ('BIO-301','Genetics','Biology',3);
INSERT INTO course VALUES ('CS-190','Python for DS','Comp_Sci',4);
INSERT INTO course VALUES ('MU-199','Music Video Production','Music',3);
INSERT INTO course VALUES ('PHY-101','Physics Principles','Physics',4);
INSERT INTO course VALUES ('HIS-351','World History','History',3);
INSERT INTO course VALUES ('EE-181','Intoduction to digital systems','Elec_Engg',3);
instructor
INSERT INTO instructor VALUES ('10101','Srinivasan','Comp_Sci',65000);
INSERT INTO instructor VALUES ('12121','Wu','Finance',90000);
INSERT INTO instructor VALUES ('22222','Einstain','Physics',950000);
INSERT INTO instructor VALUES ('32343','EI Said','History',60000);
INSERT INTO instructor VALUES ('45565','Katz','Comp_Sci',75000);
INSERT INTO instructor VALUES ('98345','Kim','Elec_Engg',80000);
INSERT INTO instructor VALUES ('76766','Crick','Biology',72000);
INSERT INTO instructor VALUES ('15151','Mozart','Music',80000);
2
section
INSERT INTO section VALUES ('BIO-101','1','Summer',2009,'Painter');
INSERT INTO section VALUES ('BIO-301','1','Summer',2010,'Painter');
INSERT INTO section VALUES ('CS-101','1','Fall',2009,'Packard');
INSERT INTO section VALUES ('CS-101','1','Spring',2010,'Packard');
INSERT INTO section VALUES ('CS-190','1','Spring',2009,'Taylor');
INSERT INTO section VALUES ('CS-190','2','Spring',2009,'Taylor');
INSERT INTO section VALUES ('EE-181','1','Spring',2009,'Taylor');
INSERT INTO section VALUES ('MU-199','1','Spring',2010,'Packard');
INSERT INTO section VALUES ('PHY-101','1','Fall',2009,'Watson');
INSERT INTO section VALUES ('HIS-351','1','Spring',2010,'Painter');
teaches
INSERT INTO teaches VALUES ('10101','CS-101','1','Fall',2009);
INSERT INTO teaches VALUES ('12121','FIN-201','1','Spring',2010);
INSERT INTO teaches VALUES ('15151','MU-199','1','Spring',2010);
INSERT INTO teaches VALUES ('22222','PHY-101','1','Fall',2009);
INSERT INTO teaches VALUES ('32343','HIS-351','1','Spring',2010);
INSERT INTO teaches VALUES ('98345','EE-181','1','Spring',2009);
INSERT INTO teaches VALUES ('76766','BIO-301','1','Summer',2010);
student
INSERT INTO student VALUES ('12345','Shankar','Comp_Sci',32);
INSERT INTO student VALUES ('19991','Brandt','History',80);
INSERT INTO student VALUES ('23121','Chavez','Finance',110);
INSERT INTO student VALUES ('45678','Levy','Physics',46);
INSERT INTO student VALUES ('55739','Sanchez','Music',38);
INSERT INTO student VALUES ('76653','Aoi','Elec_Engg',60);
INSERT INTO student VALUES ('98988','Tanaka','Biology',120);
takes
INSERT INTO takes VALUES ('12345','BIO-101','1','Summer',2009,NULL);
INSERT INTO takes VALUES ('12345','BIO-301','1','Summer',2010,NULL);
INSERT INTO takes VALUES ('23121','CS-101','1','Fall',2009,NULL);
INSERT INTO takes VALUES ('23121','CS-190','1','Spring',2009,NULL);
INSERT INTO takes VALUES ('76653','EE-181','1','Spring',2009,NULL);
INSERT INTO takes VALUES ('55739','MU-199','1','Spring',2010,NULL);
INSERT INTO takes VALUES ('45678','PHY-101','1','Fall',2009,NULL);
ALTER TABLE COMMANDS
ALTER TABLE student_test ADD address VARCHAR(20);
ALTER TABLE student_test DROP address;
ALTER TABLE student_test MODIFY address VARCHAR(25);
ALTER TABLE student_test RENAME COLUMN rollno TO roll_no;
ALTER TABLE student_test RENAME TO student_data;
ALTER TABLE student_test ADD PRIMARY KEY(rollno);
ALTER TABLE student_test DROP PRIMARY KEY;
ALTER TABLE student_test ADD FOREIGN KEY (dept_name) REFERENCES
dept_test(dept_name);
TRUNCATE AND DROP
TRUNCATE TABLE test;
DROP TABLE test;
3
========================================================================
===========
Practical No. 3
Aim:
To study & implement DML Commands.
Software Required:
MySQL 8.0 Command Line Client
Database Used:
Sample University Database
Tables Considered
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (inst_id, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (inst_id, course_id, sec_id, semester, year)
student (stu_id, name, dept_name, tot_cred)
takes (stu_id, course_id, sec_id, semester, year, grade)
Queries & Their Correct SQL Answers
1) Department names of all instructors
SELECT dept_name
FROM instructor;
2) Department names without duplicates
SELECT DISTINCT dept_name
FROM instructor;
3) Titles of Comp. Sci. courses with 3 credits
SELECT course_id, title
FROM course
WHERE dept_name = 'Comp. Sci.' AND credits = 3;
4) Instructors in Comp. Sci. with salary > 70,000
SELECT name
FROM instructor
WHERE dept_name = 'Comp. Sci.' AND salary > 70000;
5) Departments in the Watson building
SELECT dept_name
4
FROM department
WHERE building = 'Watson';
6) Departments where building starts with ‘Wat’
SELECT dept_name
FROM department
WHERE building LIKE 'Wat%';
7) Departments where building ends with ‘son’
SELECT dept_name
FROM department
WHERE building LIKE '%son';
8) Departments with 6-letter building names
SELECT dept_name, building
FROM department
WHERE building LIKE '______';
(6 underscores = 6 characters)
9) Instructor name, dept, and building
Note: NATURAL JOIN works since both tables share dept_name.
SELECT name, dept_name, building
FROM instructor NATURAL JOIN department;
10) Names & course_id of instructors
SELECT name, course_id
FROM instructor NATURAL JOIN teaches;
11) Same as above, but without AS
SELECT [Link], teaches.course_id
FROM instructor NATURAL JOIN teaches;
(Removed AS instructor_name, AS instructor_course_id as requested.)
12) Instructors who taught at least one course
SELECT name, course_id
FROM instructor NATURAL JOIN teaches;
13) Courses taught in Fall 2017 or Spring 2018 (UNION)
SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
UNION
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018;
14) Courses taught in both Fall 2017 AND Spring 2018
5
(Intersection)
SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
AND course_id IN (
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018
);
15) Courses taught in Fall 2017 BUT NOT in Spring 2018
(Except)
SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
AND course_id NOT IN (
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018
);
14) Find all courses taught in both Fall 2017 and Spring 2018 (Intersection).
SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
INTERSECT
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018;
15) Find all courses taught in Fall 2017 but not in Spring 2018 (Except).
SELECT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
EXCEPT
SELECT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018;
========================================================================
======
Practical No. 4
Page No.: _____
Aim:
6
To study & implement DML Commands – I
Software Required:
MySQL 8.0 Command Line Client
Sample University Database Tables:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (inst_id, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (inst_id, course_id, sec_id, semester, year)
takes (stu_id, course_id, sec_id, semester, year, grade)
student (stu_id, name, dept_name, tot_cred)
advisor (s_id, i_id)
prereq (course_id, prereq_id)
Queries & Correct SQL Outputs
1) Average salary of instructors in Comp. Sci.
SELECT AVG(salary)
FROM instructor
WHERE dept_name = 'Comp. Sci.';
2) Total number of instructors who teach a course in Spring 2017
SELECT COUNT(DISTINCT ID)
FROM teaches
WHERE semester = 'Spring' AND year = 2017;
(Because in your teaches table the column is ID, not inst_id.)
3) Total number of tuples in course
SELECT COUNT(*)
FROM course;
4) Maximum salary of Physics instructors
SELECT MAX(salary)
FROM instructor
7
WHERE dept_name = 'Physics';
5) Minimum salary of Comp. Sci. instructors
SELECT MIN(salary)
FROM instructor
WHERE dept_name = 'Comp. Sci.';
6) Total number of departments in course
SELECT COUNT(DISTINCT dept_name)
FROM course;
7) Average salary in each department
SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name;
8) Departments whose average salary > 42,000
SELECT dept_name, AVG(salary)
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 42000;
9) All Physics instructors in alphabetical order
SELECT name
FROM instructor
WHERE dept_name = 'Physics'
ORDER BY name;
(ASC is default)
10) All instructor salaries in descending order; tie → name ascending
SELECT name, salary
FROM instructor
ORDER BY salary DESC, name;
11) Instructors with salary between 90,000 and 100,000
SELECT name
FROM instructor
WHERE salary BETWEEN 90000 AND 100000;
8
========================================================================
=====
Practical No. 5
Experiment No.: _____
Date: _____
Page No.: _____
Aim:
To study & implement DML Commands – II
Software Required:
MySQL 8.0 Command Line Client
Sample University Database Tables:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (inst_id, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (inst_id, course_id, sec_id, semester, year)
student (stu_id, name, dept_name, tot_cred)
takes (stu_id, course_id, sec_id, semester, year, grade)
advisor (s_id, i_id)
prereq (course_id, prereq_id)
Queries & Correct SQL Answers
1) Find all courses taught in both Fall 2017 and Spring 2018 semesters.
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
AND course_id IN (
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018
);
2) Find all courses taught in Fall 2017 but NOT in Spring 2018.
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year = 2017
9
AND course_id NOT IN (
SELECT DISTINCT course_id
FROM section
WHERE semester = 'Spring' AND year = 2018
);
3) Find names of instructors whose names are neither “Mozart” nor “Einstein”.
SELECT DISTINCT name
FROM instructor
WHERE name NOT IN ('Mozart', 'Einstein');
4) Find names of instructors whose salary is greater than at least one instructor in Biology department.
SELECT name
FROM instructor
WHERE salary > ANY (
SELECT salary
FROM instructor
WHERE dept_name = 'Biology'
);
(ANY = greater than minimum salary of Biology instructors)
5) Find names of instructors whose salary is greater than every instructor in Biology department.
SELECT name
FROM instructor
WHERE salary > ALL (
SELECT salary
FROM instructor
WHERE dept_name = 'Biology'
);
(ALL = greater than maximum salary in Biology)
6) Find the department(s) having the highest average salary.
SELECT dept_name
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) = (
SELECT MAX(avg_salary)
FROM (
SELECT AVG(salary) avg_salary
FROM instructor
GROUP BY dept_name
) temp
);
========================================================================
Practical No. 6
10
Aim:
Write SQL commands to perform modifications to the database.
Software Required:
MySQL 8.0 Command Line Client
Sample University Database Tables:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (inst_id, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (inst_id, course_id, sec_id, semester, year)
student (stu_id, name, dept_name, tot_cred)
takes (stu_id, course_id, sec_id, semester, year, grade)
advisor (s_id, i_id)
prereq (course_id, prereq_id)
Queries & Proper SQL Answers
1) Delete instructors belonging to the Finance department.
DELETE FROM instructor
WHERE dept_name = 'Finance';
2) Delete all instructors with salary between $13,000 and $15,000.
DELETE FROM instructor
WHERE salary BETWEEN 13000 AND 15000;
3) Delete all instructors associated with departments located in the Watson building.
DELETE FROM instructor
WHERE dept_name IN (
SELECT dept_name
FROM department
WHERE building = 'Watson'
);
4) Delete records of instructors whose salary is below the university average salary.
DELETE FROM instructor
WHERE salary < (
SELECT AVG(salary)
FROM instructor
);
11
5) Insert a new course CS-347 titled “Database Systems” in Comp. Sci with 4 credits.
INSERT INTO course
VALUES ('CS-347', 'Database Systems', 'Comp. Sci.', 4);
6) Make every Music department student (who has >144 credits) an instructor with salary = $18,000.
INSERT INTO instructor (ID, name, dept_name, salary)
SELECT ID, name, dept_name, 18000
FROM student
WHERE dept_name = 'Music'
AND tot_cred > 144;
7) Increase salary of all instructors by 5%.
UPDATE instructor
SET salary = salary * 1.05;
8) Increase salary by 5% for instructors who earn less than $70,000.
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < 70000;
9) Give a 5% raise to instructors whose salary is less than the average instructor salary.
UPDATE instructor
SET salary = salary * 1.05
WHERE salary < (
SELECT AVG(salary)
FROM instructor
);
========================================================================
Practical No.: 7(down)ONNN
Page No.: ____
Date: ____
Aim:
Write SQL commands to create Views and Indexes.
Software Required:
MySQL 8.0 Command Line Client
Sample Database Tables:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
12
instructor (inst_id, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (inst_id, course_id, sec_id, semester, year)
student (stu_id, name, dept_name, tot_cred)
takes (stu_id, course_id, sec_id, semester, year, grade)
advisor (s_id, i_id)
prereq (course_id, prereq_id)
PART A: VIEWS
1) Create a view that lists all instructors whose department is ‘History’.
CREATE VIEW history_instructor AS
SELECT *
FROM instructor
WHERE dept_name = 'History';
2) Create a view that lists id, name, and building of each instructor.
(Join instructor with department)
CREATE VIEW all_instructor AS
SELECT instructor.inst_id, [Link], [Link]
FROM instructor
JOIN department
ON instructor.dept_name = department.dept_name;
**3) Create a view that lists course_id, section_id, building, and room number
for all Physics department courses offered in Fall 2009.**
CREATE VIEW course_list AS
SELECT c.course_id, s.sec_id, [Link], s.room_number
FROM course c
JOIN section s
ON c.course_id = s.course_id
WHERE c.dept_name = 'Physics'
AND [Link] = 'Fall'
AND [Link] = 2009;
PART B: INDEXES
1) Select instructors from Computer Science department.
SELECT *
FROM instructor
WHERE dept_name = 'Comp. Sci';
2) Explain the execution plan of the above query.
EXPLAIN SELECT *
FROM instructor
WHERE dept_name = 'Comp. Sci';
13
3) Show existing indexes on instructor table.
SHOW INDEXES FROM instructor;
4) Create an index on dept_name column of instructor table.
CREATE INDEX index_deptname
ON instructor (dept_name);
5) Show indexes after creation.
SHOW INDEXES FROM instructor;
6) Drop the created index.
DROP INDEX index_deptname
ON instructor;
========================================================================
===
Practical No. 7
Practical No.: 7
Page No.: ____
Date: ____
Aim:
Write SQL commands to create Views and Indexes.
Software Required:
MySQL 8.0 Command Line Client
Sample Database Tables:
classroom (building, room_number, capacity)
department (dept_name, building, budget)
course (course_id, title, dept_name, credits)
instructor (inst_id, name, dept_name, salary)
section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches (inst_id, course_id, sec_id, semester, year)
student (stu_id, name, dept_name, tot_cred)
takes (stu_id, course_id, sec_id, semester, year, grade)
advisor (s_id, i_id)
prereq (course_id, prereq_id)
PART A: VIEWS
1) Create a view that lists all instructors whose department is ‘History’.
14
CREATE VIEW history_instructor
AS
SELECT *
FROM instructor
WHERE dept_name = 'History';
2) Create a view that lists id, name, and building of each instructor.
CREATE VIEW instructor_details
AS
SELECT [Link], [Link], [Link]
FROM instructor
JOIN department
ON instructor.dept_name = department.dept_name;
**3) Create a view that lists course_id, section_id, building, and room number
for all Physics department courses offered in Fall 2009.**
CREATE VIEW physics_courses_fall2009
AS
SELECT course.course_id, section.sec_id, [Link], section.room_number
FROM course
JOIN section
ON course.course_id = section.course_id
WHERE course.dept_name = 'Physics'
AND [Link] = 'Fall'
AND [Link] = 2009;
PART B: INDEXES
1) Select instructors from Computer Science department.
SELECT *
FROM instructor
WHERE dept_name = 'Comp. Sci.';
2) Explain the execution plan of the above query.
EXPLAIN SELECT *
FROM instructor
WHERE dept_name = 'Comp. Sci.';
3) Show existing indexes on instructor table.
SHOW INDEXES FROM instructor;
4) Create an index on dept_name column of instructor table.
CREATE INDEX index_deptname
ON instructor(dept_name);
5) Show indexes after creation.
SHOW INDEXES FROM instructor;
6) Drop the created index.
DROP INDEX index_deptname
15
ON instructor;
========================================================================
========
Practical No.8
1. Student Table Schema (for BEFORE INSERT trigger)
s_id | s_name | dept_name | year | marks
2. BEFORE INSERT TRIGGER Query
Convert every student’s name to uppercase before inserting:
CREATE TRIGGER capitalize
BEFORE INSERT
ON student
FOR EACH ROW
SET NEW.s_name = UPPER(NEW.s_name);
3. Employee Table (for AFTER UPDATE trigger)
employee table example data
Emp_ID | Emp_Name | Salary
1001 | Sneha Agrawal | 45000
1002 | Pratik Jain | 55000
1003 | Pankaj Mahajan | 42000
1004 | Neelam Patil | 38000
1005 | Swati Kadu | 65000
4. emp_audit Table Schema
CREATE TABLE emp_audit (
ID INT PRIMARY KEY AUTO_INCREMENT,
Emp_id INT,
old_sal INT,
new_sal INT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
5. AFTER UPDATE TRIGGER Query
Creates a log entry whenever salary is updated:
DELIMITER ##
CREATE TRIGGER sal_updates
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
INSERT INTO emp_audit (emp_id, old_sal, new_sal)
VALUES ([Link], [Link], [Link]);
END;
##
DELIMITER ;
======================================]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
1) STUDENT TABLE
16
CREATE TABLE student (
s_id INT PRIMARY KEY,
s_name VARCHAR(50),
dept_name VARCHAR(50),
year INT,
marks INT
);
2) BEFORE INSERT TRIGGER
DELIMITER ##
CREATE TRIGGER capitalize
BEFORE INSERT
ON student
FOR EACH ROW
BEGIN
SET NEW.s_name = UPPER(NEW.s_name);
END ##
DELIMITER ;
3) EMPLOYEE TABLE
CREATE TABLE employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Salary INT
);
INSERT INTO employee VALUES
(1001, 'Sneha Agrawal', 45000),
(1002, 'Pratik Jain', 55000),
(1003, 'Pankaj Mahajan', 42000),
(1004, 'Neelam Patil', 38000),
(1005, 'Swati Kadu', 65000);
4) emp_audit TABLE
CREATE TABLE emp_audit (
ID INT PRIMARY KEY AUTO_INCREMENT,
Emp_id INT,
old_sal INT,
new_sal INT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
5) AFTER UPDATE TRIGGER
DELIMITER ##
CREATE TRIGGER sal_updates
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
INSERT INTO emp_audit (emp_id, old_sal, new_sal)
VALUES (OLD.Emp_ID, [Link], [Link]);
17
END ##
DELIMITER ;
Testing Query (Optional)
UPDATE employee SET Salary = 70000 WHERE Emp_ID = 1003;
SELECT * FROM emp_audit;
========================================================================
====
Practical No. 9
Expt. No.:
Date:
Page No.:
Aim:
To use Windows Functions in MySQL.
Software/Requirements (S/R):
MySQL Workbench
Queries
1) Rank students based on marks in each department
SELECT
ID,
name,
dept_name,
marks,
RANK() OVER (PARTITION BY dept_name ORDER BY marks DESC)
FROM student;
2) Find student details with the third-highest marks
SELECT
ID,
name,
marks
FROM (
SELECT
ID,
name,
marks,
DENSE_RANK() OVER (ORDER BY marks DESC) rnk
FROM student
) ranked_students
WHERE rnk = 3;
3) Compare every student's marks with the next student
(Label each row as LESS, MORE, EQUAL, or NO NEXT STUDENT)
18
SELECT
ID,
name,
dept_name,
marks,
LEAD(marks) OVER (ORDER BY ID),
CASE
WHEN marks < LEAD(marks) OVER (ORDER BY ID) THEN 'LESS'
WHEN marks > LEAD(marks) OVER (ORDER BY ID) THEN 'MORE'
WHEN marks = LEAD(marks) OVER (ORDER BY ID) THEN 'EQUAL'
ELSE 'NO NEXT STUDENT'
END
FROM student;
4) Select student details with the 5th lowest marks
SELECT
ID,
name,
marks
FROM (
SELECT
ID,
name,
marks,
DENSE_RANK() OVER (ORDER BY marks) rnk
FROM student
) ranked_students
WHERE rnk = 5;
19