Database Administration & Management Lab 02
Tasks
Muhammad Abdullah
F2022105208
Database Schema:
create database umt;
use umt;
create table students (
studentid int primary key,
firstname varchar(50),
lastname varchar(50),
email varchar(50),
phonenumber varchar(50),
admissiondate date
);
create table course (
courseid int primary key,
coursename varchar(100),
coursecode varchar(20),
instructor varchar(50)
);
create table enrollment (
enrollmentid int primary key,
studentid int,
courseid int,
enrollmentdate date,
status varchar(20),
foreign key (studentid) references students(studentid),
foreign key (courseid) references course(courseid)
);
insert into students (studentid, firstname, lastname, email, phonenumber, admissiondate)
values
(1, 'Ali', 'Abbas', '[Link]@[Link]', '+923223523520', '2020-02-02'),
(2, 'Ahmed', 'Nazir', '[Link]@[Link]', '+923224323234', '2020-03-05'),
(3, 'Raza', 'Saleem', '[Link]@[Link]', '+923423652320', '2020-03-06');
insert into course (courseid, coursename, coursecode, instructor)
values
(2352, 'Database', 'CS2341', 'Adeel Ashraf'),
(2354, 'Operating System', 'CS4643', 'Fahad Ali'),
(2355, 'Linear Algebra', 'CS6523', 'Dr. Irfan');
insert into enrollment (enrollmentid, studentid, courseid, enrollmentdate, status)
values
(1, 1, 2352, '2023-09-01', 'Active'),
(2, 1, 2354, '2023-09-05', 'Completed'),
(3, 2, 2355, '2023-09-10', 'Active'),
(4, 3, 2352, '2023-09-15', 'Inactive'),
(5, 2, 2354, '2023-09-20', 'Active');
select * from students;
select count(*) as totalstudents from students;
select distinct coursename from course;
select * from course where instructor = 'Dr. Smith';
select * from enrollment where status = 'Active';
select * from course limit 3;
select * from students order by admissiondate desc;
select avg(enrollmentcount) as avgenrollmentsperstudent
from (
select count(*) as enrollmentcount
from enrollment
group by studentid
) as enrollmentcounts;
select status, count(*) as enrollmentcount
from enrollment
group by status;
select firstname, lastname, email
from students
where admissiondate > '2024-01-01';
select status, count(*) as enrollmentcount
from enrollment
group by status
having count(*) > 3;
select courseid, count(*) as activeenrollmentcount
from enrollment
where status = 'Active'
group by courseid
having count(*) > 5;
select studentid, count(*) as totalenrollments
from enrollment
group by studentid
order by totalenrollments desc;
select status, count(*) as studentcount
from enrollment
group by status
having count(*) > 1;
[Link] the database and select it
[Link] the students table
[Link] the course table
[Link] the enrollment table with foreign key references
INSERTION:
Query 5: Select all columns from the students table
Query 6: Count the total number of students in the students table
Query 7: Retrieve distinct course names from the course table
Query 8: Find all courses taught by "Dr. Smith"
Query 9: List all enrollments where the status is 'Active'
Query 10: Retrieve the first 3 records from the course table
Query 11: Retrieve all students, ordered by their admission date from newest
to oldest
Query 12: Calculate the average number of enrollments per student
Query 13: Group the enrollments by status and count the number of
enrollments in each status category
Query 14: Retrieve the first name, last name, and email of students who were
admitted after January 1, 2024
Query 15: Count the number of enrollments for each status and show only
those statuses that have more than 3 enrollments
Query 16: Group the enrollments by course and count the number of active
enrollments only, displaying only those courses with more than 5 active
enrollments
Query 17: Get the total number of courses each student is enrolled in and
order the results by the total number of enrollments in descending order
Query 18: Group students by their status and count how many students are in
each status. Show only statuses with more than 1 student.
