Scribd
Upload
10 views4 pages

Lab 3

The document outlines the creation of a database named 'NileUniversityDB' with three tables: Students, Courses, and Enrollments, including their respective fields and constraints. It also includes various SQL queries to analyze student data, course credits, enrollment years, and average grades. The queries are designed to provide insights into student majors, course enrollments, and performance metrics.

Uploaded by

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

Lab 3

The document outlines the creation of a database named 'NileUniversityDB' with three tables: Students, Courses, and Enrollments, including their respective fields and constraints. It also includes various SQL queries to analyze student data, course credits, enrollment years, and average grades. The queries are designed to provide insights into student majors, course enrollments, and performance metrics.

Uploaded by

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

Abdelrahman Hisham Khalil

23100360

Database

Lab 3

CREATE DATABASE NileUniversityDB;

CREATE TABLE Students (

student_id INT AUTO_INCREMENT PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,

major VARCHAR(50),

enrollment_year INT NOT NULL CHECK (enrollment_year >= 2000),

status VARCHAR(20) DEFAULT 'Enrolled'

);
CREATE TABLE Courses (

course_id INT AUTO_INCREMENT PRIMARY KEY,

course_name VARCHAR(100) NOT NULL,

course_code VARCHAR(20) UNIQUE NOT NULL,

instructor VARCHAR(50),

credits INT NOT NULL CHECK (credits > 0),

is_elective BOOLEAN DEFAULT FALSE

);

CREATE TABLE Enrollments (

enrollment_id INT AUTO_INCREMENT PRIMARY KEY,

student_id INT,

course_id INT,

enrollment_date DATE NOT NULL,

grade VARCHAR(2),

FOREIGN KEY (student_id) REFERENCES Students(student_id),

FOREIGN KEY (course_id) REFERENCES Courses(course_id)

);

SELECT major, COUNT(*)

FROM Students

GROUP BY major;

SELECT AVG(credits)

FROM Courses;
SELECT MIN(enrollment_year), MAX(enrollment_year)

FROM Students;

SELECT Students.major, SUM(Courses.credits)

FROM Students

JOIN Enrollments ON Students.student_id = Enrollments.student_id

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY Students.major;

SELECT CONCAT(Students.first_name, ' ', Students.last_name) AS full_name,


GROUP_CONCAT(Courses.course_name)

FROM Students

JOIN Enrollments ON Students.student_id = Enrollments.student_id

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY full_name;

SELECT Courses.course_name, AVG(CAST(Enrollments.grade AS DECIMAL))

FROM Enrollments

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY Courses.course_name;

SELECT Courses.course_name, COUNT(*)

FROM Enrollments

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY Courses.course_name;
SELECT CONCAT(Students.first_name, ' ', Students.last_name) AS full_name, COUNT(*) AS
num_courses

FROM Students

JOIN Enrollments ON Students.student_id = Enrollments.student_id

GROUP BY full_name

ORDER BY num_courses DESC

LIMIT 1;

SELECT CONCAT(Students.first_name, ' ', Students.last_name) AS full_name,


SUM(Courses.credits)

FROM Students

JOIN Enrollments ON Students.student_id = Enrollments.student_id

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY full_name;

SELECT Courses.course_name, AVG(CAST(Enrollments.grade AS DECIMAL)) AS avg_grade

FROM Enrollments

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY Courses.course_name

ORDER BY avg_grade DESC

LIMIT 1;

SELECT Courses.course_name, AVG(CAST(Enrollments.grade AS DECIMAL)) AS avg_grade

FROM Enrollments

JOIN Courses ON Enrollments.course_id = Courses.course_id

GROUP BY Courses.course_name

HAVING avg_grade > 3.0;

You might also like