[go: up one dir, main page]
Scribd
Upload
11 views4 pages

DB Lab Task-03.docx

The document outlines a SQL lab exercise at Air University focused on creating and managing a Student Management Database. It includes instructions for creating a database, tables, inserting sample data, and executing various SQL queries for data retrieval, updating, and deletion. The expected outcome is a practical understanding of SQL operations, including joins and subqueries, along with a requirement for submission of the executed SQL script and screenshots of results.

Uploaded by

rubab.rhk
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
11 views4 pages

DB Lab Task-03.docx

The document outlines a SQL lab exercise at Air University focused on creating and managing a Student Management Database. It includes instructions for creating a database, tables, inserting sample data, and executing various SQL queries for data retrieval, updating, and deletion. The expected outcome is a practical understanding of SQL operations, including joins and subqueries, along with a requirement for submission of the executed SQL script and screenshots of results.

Uploaded by

rubab.rhk
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

Air University, Aviation and Aerospace, Kamra Campus

Lab 03:
SQL

Lab Instructor:
Amala Masood
Date: 24-02-2025
Introduction to SQL

Objective:

To understand the different issues involved in the design and implementation of a


database system. To understand and use data definition language to write query for a
database.

Instructions:

Step 1: Create a Database

Create a new database named StudentDB.


CREATE DATABASE StudentDB; USE StudentDB;

Step 2: Create Tables

Create tables: Students, Courses, and Enrollments.

CREATE TABLE Students (


StudentID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Email VARCHAR(100) UNIQUE
);

CREATE TABLE Courses (


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

CREATE TABLE Enrollments (


EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Step 3: Insert Sample Data
Populate the tables with sample records.
INSERT INTO Students (FirstName, LastName, DateOfBirth, Email) VALUES
('Alice', 'Johnson', '2000-05-15', 'alice@example.com'),
('Bob', 'Smith', '1999-08-20', 'bob@example.com'),
('Charlie', 'Brown', '2001-02-10', 'charlie@example.com');

INSERT INTO Courses (CourseName, Credits) VALUES


('Database Systems', 3),
('Computer Networks', 4),
('Software Engineering', 3);

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate) VALUES


(1, 1, '2024-01-10'),
(2, 2, '2024-01-12'),
(3, 1, '2024-01-15'),
(1, 3, '2024-01-18');
Step 4: Execute SQL Queries
Perform the following queries:
1.​ Retrieve all students and their details.
SELECT * FROM Students;

1.​ Find the courses a specific student (Alice) is enrolled in.

SELECT Students.FirstName, Students.LastName,


Courses.CourseName
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID
WHERE Students.FirstName = 'Alice';

2.​ Count the number of students enrolled in each course.

SELECT Courses.CourseName, COUNT(Enrollments.StudentID) AS


StudentCount
FROM Courses
LEFT JOIN Enrollments ON Courses.CourseID =
Enrollments.CourseID
GROUP BY Courses.CourseName;

3.​ Update a student’s email.

UPDATE Students
SET Email = 'alice.new@example.com'
WHERE FirstName = 'Alice' AND LastName = 'Johnson';

4.​ Delete a student record (Charlie).


DELETE FROM Students
WHERE FirstName = 'Charlie' AND LastName = 'Brown';

Bonus Challenge:

1.​ Create a view that displays all students along with their enrolled courses.
CREATE VIEW StudentCourses AS
SELECT Students.StudentID, Students.FirstName,
Students.LastName, Courses.CourseName
FROM Students
JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

2.​ Retrieve all students who are not enrolled in any course.
SELECT * FROM Students
WHERE StudentID NOT IN (SELECT StudentID FROM Enrollments);

Expected Outcome:

●​ Understanding of SQL queries for creating, retrieving, updating, and deleting records.
●​ Hands-on practice with joins, group by, views, and subqueries.
●​ A functioning Student Management Database with relational integrity.

Submission:

●​ Submit your .sql script with all queries executed successfully.


●​ Take screenshots of query results and include them in a report.

You might also like