DA201: Relational Database Management Systems Lab
Lab # 05 (1 Questions, 100 Marks) No. of Pages: 3
Instructor: Dr. Manas Khatua Dept. of CSE, IIT Guwahati
a. This lab assignment is based on the following main concepts covered in the theory class.
i. Aggregate Functions, Nested Queries,
ii. Substring matching, Temporary table creation.
iii. SQL clauses: Group By, Having, Order By, With, Join.
b. You can refer to the text book for SQL syntax.
Question 1: (100 marks)
Using MySQL, perform the following tasks:
Task 01 - (1 mark) Create a database named week05
Task 02 Create tables
a. (3 marks) A table student containing the following
1st column cid string of characters of fixed size 10
2nd column roll_number string of characters of fixed size 10
3rd column name string of characters of fixed size 50
th
4 column approval status string of characters of fixed size 10
5th column credit status string of characters of fixed size 7
with the following constraints:
• roll_number and cid as primary key.
• name cannot take null values
Note: credit status takes two values Credit and Audit.
approval status takes two values Approved and Pending.
b. (2 marks) A table course containing the following
1st column cid string of characters of fixed size 10
nd
2 column name string of characters of fixed size 100
with the following constraints:
• cid as primary key.
• name cannot take null values
Page 1
c. (3 marks) A table credit containing the following
1st column cid string of characters of fixed size 10
2nd column l (lecture) integer
3rd column t (tutorial) integer
4th column p (practical) integer
5th column c (credits) float
with the following constraints:
• cid as primary key.
• l cannot take null values
• t cannot take null values
• p cannot take null values
• c cannot take null values
d. (3 marks) A table faculty containing the following
1st column cid string of characters of fixed size 10
2nd column name string of characters of fixed size 20
e. (3 marks) A table semester containing the following
1st column dept string of characters of fixed size 4
2nd column number string of characters of fixed size 4
3rd column cid string of characters of fixed size 10
Task 03 Populate data
a. (3 marks) populate data from the file [Link] into table student
b. (3 marks) populate data from the file [Link] into table course
c. (3 marks) populate data from the file [Link] into table credit
d. (3 marks) populate data from the file [Link] into table faculty
e. (3 marks) populate data from the file [Link] into table semester
Use MySQL load statement to populate data.
Task 04 Answer the following
f. (5 marks) Compute the total number of lectures offered
g. (5 marks) Compute the total number of hours in lectures, tutorials and practical classes
offered by ECE department as a column named total_hours
h. (5 marks) Compute the total practical hours involved in design department
Task 05 Answer the following
a. (5 marks) List cid and number of students crediting in each of the non-minor courses
b. (5 marks) List the department and associated total number of credits offered by that
department
Page 2
Hint: For department name, extract the first two characters as substring from cid and use the
same in the query.
Task 06 Answer the following
a. (5 marks) List the course name and number of students auditing the course in which at least
five audit students are in the course. The course name should be sorted alphabetically.
b. (5 marks) List the cid and name of course and number of faculty involved in teaching such
that the number of faculty is involved in teaching the course is at least one and at most three.
The course name should be sorted alphabetically.
c. (5 marks) List the name of the faculty and number of courses the faculty is teaching such
that the number of courses teaching is more than one course. The faculty name should be
sorted reverse alphabetically.
Task 07 Answer the following
a. (5 marks) List the course cid and name which offers maximum number of credits. Sort
the course name alphabetically.
b. (5 marks) List the cid, faculty name and the corresponding course credit of the faculties
teaching in the CSE department (cid starts with CS) who are teaching courses with credits
above the average credit offered in t he CSE department.
Task 08 Answer the following
a. (5 marks) List semester numbers of Design department (department name is DD) where the
total credits offered in those semesters is less than ALL of the semester’s total credits offered
by the BSBE department (department name is BSBE).
b. (5 marks) List semester numbers of BSBE department where the total credits offered in
those semesters is greater than or equal to ANY of the semester’s total credits offered by
design department.
Hint: You may create a temporary table using the MySQL with statement which is result of a select
query using an SQL statement (note: this is a representative SQL query only) and use the
temporary table further in your main query.
Page 3