KEMBAR78
DBMS Lab-7 | PDF | Databases | Sql
Scribd
Upload
29 views7 pages

DBMS Lab-7

The document is a lab report for a Database Management System course, authored by Neeraj Rahul. It includes SQL queries for various tasks such as displaying top students by GPA, counting course enrollments, and recommending courses based on GPA. Additionally, it outlines the creation of a database, tables for students, courses, and enrollments, and provides sample data insertion queries.

Uploaded by

neerajrahul2004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
29 views7 pages

DBMS Lab-7

The document is a lab report for a Database Management System course, authored by Neeraj Rahul. It includes SQL queries for various tasks such as displaying top students by GPA, counting course enrollments, and recommending courses based on GPA. Additionally, it outlines the creation of a database, tables for students, courses, and enrollments, and provides sample data insertion queries.

Uploaded by

neerajrahul2004
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

DBMS

LAB 7
REPORT

Name - Neeraj Rahul


Roll no - 23bcs085
Q1. Write an SQL query to display the names of the
top 3 students based on their GPA

Q2. Find the names of courses along with the number


of students enrolled, sorted in descending order of
enrollment count.

Q3. Write an SQL query to display the names of


students who have not enrolled in any course.

Q4. Create an SQL query to recommend courses to


students with a GPA higher than 3.5.

Q5. Find the names of students who are enrolled in


more than one course.

Q6. Write an SQL query to display the names of


students with the highest GPA in each department.

CREATE DATABASE UNIVERSITYDB;


USE UNIVERSITYDB

CREATE TABLE Students (


StudentID INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Department VARCHAR(100) NOT NULL,
GPA DECIMAL(3,2),
PRIMARY KEY (StudentID)
);

CREATE TABLE Courses (


CourseID INT NOT NULL,
CourseName VARCHAR(100) NOT NULL,
Credits INT NOT NULL,
PRIMARY KEY (CourseID)
);

CREATE TABLE Enrollments (


EnrollmentID INT NOT NULL,
StudentID INT NOT NULL,
CourseID INT NOT NULL,
Semester VARCHAR(50) NOT NULL,
PRIMARY KEY (EnrollmentID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
INSERT INTO Students (StudentID, Name, Department, GPA)
VALUES
(101, 'John Doe', 'Computer Science', 4.0),
(102, 'Alice Smith', 'Mathematics', 3.8),
(103, 'Bob Johnson', 'Engineering', 3.9),
(104, 'Sarah Lee', 'Biology', 3.2);

INSERT INTO Courses (CourseID, CourseName, Credits)


VALUES
(201, 'Introduction to CS', 4),
(202, 'Calculus I', 3),
(203, 'Physics I', 4),
(204, 'Organic Chemistry', 5);

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Semester)


VALUES
(301, 101, 201, 'Fall 2023'),
(302, 101, 202, 'Fall 2023'),
(303, 102, 202, 'Fall 2023'),
(304, 103, 203, 'Spring 2024'),
(305, 104, 203, 'Spring 2024');

1.
SELECT Name
FROM Students
ORDER BY GPA DESC
LIMIT 3;
2.
SELECT c.CourseName, COUNT(e.StudentID) AS EnrollmentCount
FROM Courses c
LEFT JOIN Enrollments e ON c.CourseID = e.CourseID
GROUP BY c.CourseID, c.CourseName
ORDER BY EnrollmentCount DESC;

3.
SELECT Name
FROM Students
WHERE StudentID NOT IN (
SELECT DISTINCT StudentID
FROM Enrollments
);
4.
SELECT s.Name AS StudentName, c.CourseName
FROM Students s
CROSS JOIN Courses c
WHERE s.GPA > 3.5;

5.
SELECT s.Name
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
GROUP BY s.StudentID, s.Name
HAVING COUNT(e.CourseID) > 1;
6.
SELECT s.Name, s.Department, s.GPA
FROM Students s
WHERE s.GPA = (
SELECT MAX(GPA)
FROM Students
WHERE Department = s.Department
);

You might also like