MySQL Database Tasks for Mafunzo University
1. Create a Database
CREATE DATABASE Training;
2. Batch SQL Files
i. Create the Database Table Structures
USE Training;
-- Students Table
CREATE TABLE Students (
RegNo VARCHAR(25) PRIMARY KEY,
StudName VARCHAR(50) NOT NULL,
DateOfBirth,
DegreeCourse VARCHAR(100) NOT NULL
);
-- Courses Table
CREATE TABLE Courses (
CourseCode VARCHAR(10) PRIMARY KEY,
Title VARCHAR(100) NOT NULL,
Dept VARCHAR(50)
);
-- Registration Table
CREATE TABLE Registration (
CourseCode VARCHAR(10),
RegNo VARCHAR(15),
TotalMark INT
);
ii. Populate the Tables
USE Training;
-- put data in Students Table
INSERT INTO Students (RegNo, StudName, DateOfBirth, DegreeCourse) VALUES
('J12/1234/2001', 'Mary Atieno', '1990-01-01', 'BSc Comp Science'),
('J25/9999/2001', 'Peter Kamau', '1989-04-07', 'BSc Maths'),
('P21/4564/2001', 'Mohammed Aziz', '1991-06-07', 'BSc Chem'),
('P21/4565/2001', 'Jane Waswa', '1991-03-04', 'BSc Chem'),
('P21/4566/2001', 'Moses Araka', '1990-05-17', 'BSc Chem'),
('Z100/76786/2001', 'Joseph Musyoka', '1990-04-04', 'BSc Maths, Chem');
-- put data in Courses Table
INSERT INTO Courses (CourseCode, Title, Dept) VALUES
('SCH301', 'Physical Chemistry', 'Chemistry'),
('SCH302', 'Inorganic Chemistry', 'Chemistry'),
('SCH303', 'Thermodynamics', 'Chemistry'),
('SCO206', 'Database Systems', 'CIT'),
('SCO207', 'Computer Programming', 'CIT'),
('SMA100', 'Basic Maths', 'Mathematics'),
('SMA200', 'Linear Algebra I', 'Mathematics');
-- put tdata to Registration Table
INSERT INTO Registration (CourseCode, RegNo, TotalMark) VALUES
('SCH301', 'P21/4564/2001', 62),
('SCH301', 'P21/4565/2001', 71),
('SCH302', 'P21/4564/2001', 41),
('SCH302', 'P21/4565/2001', 72),
('SCH302', 'Z100/76786/2001', 35),
('SCH303', 'J25/9999/2001', 50),
('SCH303', 'P21/4565/2001', 75),
('SCO206', 'J12/1234/2001', 30),
('SCO206', 'J25/9999/2001', 40),
('SCO207', 'J25/9999/2001', 50),
('SMA200', 'J25/9999/2001', 60),
('SMA200', 'Z100/76786/2001', 70);
3. SQL Queries
i. Retrieve the name of each student who registered in the course titled 'Database Systems'
SELECT DISTINCT Students.StudName
FROM Students
JOIN Registration ON Students.RegNo = Registration.RegNo
JOIN Courses ON Registration.CourseCode = Courses.CourseCode
WHERE Courses.Title = 'Database Systems';
ii. Retrieve the title of the course along with the number of students who registered in this
course in descending order
SELECT Courses.Title, COUNT(Registration.RegNo) AS NumberOfStudents
FROM Courses
JOIN Registration ON Courses.CourseCode = Registration.CourseCode
GROUP BY Courses.Title
ORDER BY NumberOfStudents DESC;
iii. Retrieve for each student the average mark attained for the courses registered for
SELECT Students.StudName, AVG(Registration.TotalMark) AS AverageMark
FROM Students
JOIN Registration ON Students.RegNo = Registration.RegNo
GROUP BY Students.StudName;
