Practical No 02 B

Name : Siddhi Vinayak Narke

Roll No : 13267

Batch : B3

Database Management System Laboratory

Problem Statement : - Write at least 10 SQL queries on the suitable database

application using SQL DML statements.

show databases;
+--------------------+
| Database |
+--------------------+
| college |
| information_schema |
| priyadb1 |
| mysql |
| performance_schema |
| sys |
+--------------------+

mysql> create database university;

mysql> use university;

Database changed

2. Create Tables

a. students table
mysql> CREATE TABLE students (student_id INT AUTO_INCREMENT PRIMARY KEY, name
VARCHAR(100), email VARCHAR(100) UNIQUE, enrollment_date DATE);

mysql> INSERT INTO students (name, email, enrollment_date) VALUES ('Aman',

'aman@example.com', '2025-08-01'), ('Dhanashree', 'dhanashree@example.com', '2025-08-
02'), ('Rohan', 'rohan@example.com', '2025-08-03'), ('Sarvesh', 'sarvesh@example.com',
'2025-08-04'), ('Priya', 'priya@example.com', '2025-08-05'), ('Unnati',
'unnati@example.com', '2025-08-06')(‘Kiran’,’kiran@example.com’,’2025-08-07’);

mysql> select * from students;

+------------+------------+------------------------+-----------------+
| student_id | name | email | enrollment_date |
+------------+------------+------------------------+-----------------+
| 1 | Aman | aman@example.com | 2025-08-01 |
| 2 | Dhanashree | dhanashree@example.com | 2025-08-02 |
| 3 | Rohan | rohan@example.com | 2025-08-03 |
| 4 | Sarvesh | sarvesh@example.com | 2025-08-04 |
| 5 | Priya | priya@example.com | 2025-08-05 |
| 6 | Unnati | unnati@example.com | 2025-08-06 |
| 7 | kiran | kiran@example.com | 2025-08-07 |
+------------+------------+------------------------+-----------------+

Index on Email
mysql> CREATE INDEX idx_students_email ON students(email);
mysql> SHOW INDEX FROM students;
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
Expression |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+---------+------------+
| students | 0 | PRIMARY | 1 | student_id | A | 6 | NULL |
NULL | | BTREE | | | YES | NULL |
| students | 0 | email | 1 | email |A | 6 | NULL | NULL |
YES | BTREE | | | YES | NULL |
| students | 1 | idx_students_email | 1 | email |A | 7 | NULL |
NULL | YES | BTREE | | | YES | NULL |
+----------+------------+--------------------+--------------+-------------+-----------+-------------+----------
+--------+------+------------+---------+---------------+---------+------------+

b.instructors table
mysql> CREATE TABLE instructors (instructor_id INT AUTO_INCREMENT PRIMARY KEY, name
VARCHAR(100), department VARCHAR(50) );

mysql> INSERT INTO instructors (name, department) VALUES ('Dr. Mehta', 'Computer
Science'),
('Prof. Sharma', 'Electronics'), ('Dr. Iyer', 'Mathematics'), ('Prof. Khan', 'Physics'), ('Dr. Desai',
'Information Technology'),('Prof. Reddy', 'Mechanical Engineering');

mysql> select * from instructors;

+---------------+--------------+------------------------+
| instructor_id | name | department |
+---------------+--------------+------------------------+
| 1 | Dr. Mehta | Computer Science |
| 2 | Prof. Sharma | Electronics |
| 3 | Dr. Iyer | Mathematics |
| 4 | Prof. Khan | Physics |
| 5 | Dr. Desai | Information Technology |
| 6 | Prof. Reddy | Mechanical Engineering |
+---------------+--------------+------------------------+

c.courses table
mysql> CREATE TABLE courses (course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(100),credits INT);

mysql> INSERT INTO courses (course_name, credits) VALUES ('Database Systems', 4),
('Operating Systems', 3), ('Computer Networks', 3),('Software Engineering', 4), ('Data
Structures', 4), ('Machine Learning', 3);

mysql> select * from courses;

+-----------+----------------------+---------+
| course_id | course_name | credits |
+-----------+----------------------+---------+
| 1 | Database Systems | 4|
| 2 | Operating Systems | 3|
| 3 | Computer Networks | 3|
| 4 | Software Engineering | 4|
| 5 | Data Structures | 4|
| 6 | Machine Learning | 3|
+-----------+----------------------+---------+

d.enrollment table
mysql> CREATE TABLE enrollments (enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,course_id INT,semester VARCHAR(10),grade DECIMAL(5,2),FOREIGN KEY
(student_id) REFERENCES students(student_id),FOREIGN KEY (course_id) REFERENCES
courses(course_id));
mysql> INSERT INTO enrollments (student_id, course_id, semester, grade) VALUES(1, 1,
'Sem 3', 88.50), (2, 2, 'Sem 3', 91.00), (3, 3, 'Sem 3', 78.25), (4, 1, 'Sem 3', 85.00), (5, 4, 'Sem
3', 92.75), (6, 2, 'Sem 3', 89.00);

mysql> select * from enrollments;

+---------------+------------+-----------+----------+-------+
| enrollment_id | student_id | course_id | semester | grade |
+---------------+------------+-----------+----------+-------+
| 1| 1| 1 | Sem 3 | 88.50 |
| 2| 2| 2 | Sem 3 | 91.00 |
| 3| 3| 3 | Sem 3 | 78.25 |
| 4| 4| 1 | Sem 3 | 85.00 |
| 5| 5| 4 | Sem 3 | 92.75 |
| 6| 6| 2 | Sem 3 | 89.00 |
+---------------+------------+-----------+----------+-------+

- Index on course_id for faster joins/queries

CREATE INDEX idx_enrollments_course_id ON enrollments(course_id);

mysql> show index from enrollments;

+-------------+------------+---------------------------+--------------+---------------+-----------+-------------
+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
| Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible
| Expression |
+-------------+------------+---------------------------+--------------+---------------+-----------+-------------
+----------+--------+------+------------+---------+---------------+---------+------------+
| enrollments | 0 | PRIMARY | 1 | enrollment_id | A | 6|
NULL | NULL | | BTREE | | | YES | NULL |
| enrollments | 1 | student_id | 1 | student_id | A | 6|
NULL | NULL | YES | BTREE | | | YES | NULL |
| enrollments | 1 | idx_enrollments_course_id | 1 | course_id | A |
4 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------------+------------+---------------------------+--------------+---------------+-----------+-------------
+----------+--------+------+------------+---------+---------------+---------+------------+

e.teaches table
mysql> CREATE TABLE teaches ( instructor_id INT,course_id INT,PRIMARY KEY
(instructor_id, course_id),FOREIGN KEY (instructor_id) REFERENCES
instructors(instructor_id),FOREIGN KEY (course_id) REFERENCES courses(course_id));
mysql> INSERT INTO teaches (instructor_id, course_id) VALUES (1, 1), (1, 2), (2, 3), (3, 1), (3,
4);

mysql> select * from teaches;

+---------------+-----------+
| instructor_id | course_id |
+---------------+-----------+
| 1| 1|
| 3| 1|
| 1| 2|
| 2| 3|
| 3| 4|
+---------------+-----------+

mysql> CREATE VIEW student_performance_view AS SELECT s.name AS student_name,

c.course_name, e.grade, e.semester FROM students s JOIN enrollments e ON s.student_id =
e.student_id JOIN courses c ON e.course_id = c.course_id;

mysql> SELECT * FROM student_performance_view;

+--------------+----------------------+-------+----------+
| student_name | course_name | grade | semester |
+--------------+----------------------+-------+----------+
| Aman | Database Systems | 88.50 | Sem 3 |
| Dhanashree | Operating Systems | 91.00 | Sem 3 |
| Rohan | Computer Networks | 78.25 | Sem 3 |
| Sarvesh | Database Systems | 85.00 | Sem 3 |
| Priya | Software Engineering | 92.75 | Sem 3 |
| Unnati | Operating Systems | 89.00 | Sem 3 |

mysql> CREATE VIEW course_statistics_view AS SELECT c.course_name,

COUNT(e.student_id) AS total_students, AVG(e.grade) AS average_grade, MAX(e.grade) AS
highest_grade FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;

mysql> SELECT * FROM course_statistics_view;

+----------------------+----------------+---------------+---------------+
| course_name | total_students | average_grade | highest_grade |
+----------------------+----------------+---------------+---------------+
| Database Systems | 2 | 86.750000 | 88.50 |
| Operating Systems | 2 | 90.000000 | 91.00 |
| Computer Networks | 1 | 78.250000 | 78.25 |
| Software Engineering | 1 | 92.750000 | 92.75 |
| Data Structures | 0| NULL | NULL |
| Machine Learning | 0| NULL | NULL |
+----------------------+----------------+---------------+---------------+

• Average grade per course in "sem3" semester

mysql> SELECT
c.course_name,
AVG(e.grade) AS average_grade
FROM
courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE
e.semester = 'Sem 3'
GROUP BY
c.course_name;

+----------------------+---------------+
| course_name | average_grade |
+----------------------+---------------+
| Database Systems | 86.750000 |
| Operating Systems | 90.000000 |
| Computer Networks | 78.250000 |
| Software Engineering | 92.750000 |
+----------------------+---------------+

• Students who scored more than 85 in any course

mysql> SELECT
s.student_id,
s.name AS student_name,
c.course_name,
e.grade
FROM
students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE
e.grade > 85;
+------------+--------------+----------------------+-------+
| student_id | student_name | course_name | grade |
+------------+--------------+----------------------+-------+
| 1 | Aman | Database Systems | 88.50 |
| 2 | Dhanashree | Operating Systems | 91.00 |
| 5 | Priya | Software Engineering | 92.75 |
| 6 | Unnati | Operating Systems | 89.00 |
+------------+--------------+----------------------+-------+

3.Instructors teaching more than 2 courses

mysql> SELECT
i.instructor_id,
i.name AS instructor_name,
COUNT(t.course_id) AS total_courses
FROM
instructors i
JOIN teaches t ON i.instructor_id = t.instructor_id
GROUP BY
i.instructor_id, i.name
HAVING
COUNT(t.course_id) > 2;

4.Top 3 students with highest average grade

SELECT
s.student_id,
s.name AS student_name,
AVG(e.grade) AS average_grade
FROM
students s
JOIN enrollments e ON s.student_id = e.student_id
GROUP BY
s.student_id, s.name
ORDER BY
average_grade DESC
LIMIT 3;

• INNER JOIN: Student names with the courses they enrolled in

mysql> SELECT
s.name AS student_name,
c.course_name,
e.semester,
e.grade
FROM
students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;
+--------------+----------------------+----------+-------+
| student_name | course_name | semester | grade |
 +--------------+----------------------+----------+-------+
| Aman | Database Systems | Sem 3 | 88.50 |
| Dhanashree | Operating Systems | Sem 3 | 91.00 |
| Rohan | Computer Networks | Sem 3 | 78.25 |
| Sarvesh | Database Systems | Sem 3 | 85.00 |
| Priya | Software Engineering | Sem 3 | 92.75 |
| Unnati | Operating Systems | Sem 3 | 89.00 |
+--------------+----------------------+----------+-------+

• LEFT JOIN: All students and their grades (even if not enrolled)
mysql> SELECT
s.name AS student_name,
c.course_name,
e.grade
FROM
students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;
+--------------+----------------------+-------+
| student_name | course_name | grade |
+--------------+----------------------+-------+
| Aman | Database Systems | 88.50 |
| Dhanashree | Operating Systems | 91.00 |
| Rohan | Computer Networks | 78.25 |
| Sarvesh | Database Systems | 85.00 |
| Priya | Software Engineering | 92.75 |
| Unnati | Operating Systems | 89.00 |
| kiran | NULL | NULL |
+--------------+----------------------+-------+

• JOIN with Aggregation: Count of courses each instructor teaches

mysql> SELECT
i.name AS instructor_name,
COUNT(t.course_id) AS courses_taught
FROM
instructors i
JOIN teaches t ON i.instructor_id = t.instructor_id
GROUP BY
i.name;
+-----------------+----------------+
| instructor_name | courses_taught |
+-----------------+----------------+
| Dr. Mehta | 2|
| Dr. Iyer | 2|
| Prof. Sharma | 1|
+-----------------+----------------+

• JOIN with WHERE + Operator: Students scoring above 85

mysql> SELECT
s.name AS student_name,
c.course_name,
e.grade
FROM
students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE
e.grade > 85;
+--------------+----------------------+-------+
| student_name | course_name | grade |
+--------------+----------------------+-------+
| Aman | Database Systems | 88.50 |
| Dhanashree | Operating Systems | 91.00 |
| Priya | Software Engineering | 92.75 |
| Unnati | Operating Systems | 89.00 |
+--------------+----------------------+-------+

• JOIN to Show Courses Not Yet Assigned to Any Instructor

mysql> SELECT
c.course_name
FROM
courses c
LEFT JOIN teaches t ON c.course_id = t.course_id
WHERE
t.instructor_id IS NULL;
+------------------+
| course_name |
+------------------+
| Data Structures |
| Machine Learning |
+------------------+