Scribd
Upload
29 views

ISC 305 Assignment

Database definition of terms

Uploaded by

annmell921
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 views

ISC 305 Assignment

Database definition of terms

Uploaded by

annmell921
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/ 3

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;

You might also like