DA201: Relational Database Management Systems Lab
Lab # 04 (1 Question, 100 Marks) No. of Pages: 3
Instructor: Dr. Manas Khatua Dept. of CSE, IIT Guwahati
a. This lab assignment is based on the f o l l o w i n g m a i n concepts covered in the
previous theory class.
i. Basic SQL query involving single table
ii. Queries involving multiple tables
iii. Rename Operation, String operation,
iv. Basic Aggregation, Nested Query
b. You can refer to the text book for SQL syntax.
Question 1: (100 marks)
Using MySQL, perform the following tasks:
Task 01 (1 marks) Create a database named week04
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 string of characters of fixed size 10
rd
3 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 10
with the following constraints:
• roll 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. (3 marks) A table course containing the following
1st column cid string of characters of fixed size 10
2nd 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
Task 03 Populate data
a. (5 marks) populate data from the file [Link] into table student
ignoring the csv header line
b. (5 marks) populate data from the file [Link] into table course ignoring the
csv header line
c. (5 marks) populate data from the file [Link] into table credit ignoring the
csv header line
Use MySQL load statement to populate data. Note that each line in the CSV files ends
with the special characters ‘\r\n’.
Task 04 Queries involving single table
a. (5 marks) List all the columns of the student table pertaining to the student with name
Aabesh Ghosh
b. (5 marks) List cid, student name and credit_status of students who are
crediting course CL 699 and are approved to credit the same
c. (5 marks) List the columns cid, roll, credit_status, approval_status of
students whose course registration is pending and are crediting the course
d. (5 marks) List cid, l, t, p, c of course which are greater than 6 credits
e. (5 marks) List unique student roll and name who are auditing courses and who got
approved for audit
Task 05 Queries involving multiple tables
a. (5 marks) List course name, l, t, p, c having the maximum credits
b. (5 marks) List course name, l, t, p, c having tutorial and practical components
and credit above the average credit of all courses
c. (5 marks) List course cid, name, l, t, p, c with credits in range (6, 12), both
limits excluded, and not having credit structure 3-1-0 (correspond to l-t-p)
d. (5 marks) List the cid, course name, student name, l, t, p, c of each course
registered by student with last name Venkatesh and the lab component is not
the minimum
Page 2
e. (5 marks) List the student roll, name, cid, course name, l, t, p, c of students
crediting courses with 6 credits and course structure different from 3-0-0
(correspond to l-t-p) and course are offered by CSE department (whose course
id starts with CS);
Task 06 String operations
a. (5 marks) List cid and student name in which ADARSH appears anywhere
in the student name. Use UPPER function before string comparison
b. (5 marks) List roll, name, credit_status and course name of student whose
course name starts with introduction to. Use LOWER function before string
comparison
c. (5 marks) List the number of students who have taken courses of CSE department
(whose course id starts with CS) and EEE department (whose course id starts with
EE)
d. (5 marks) List the cid and course name of courses offered by the EEE department
(whose course id starts with EE) if the institute in which the last character should
be ‘2’. That is a course of the form EE 132, EE 742 or EE 892
e. (5 marks) List student name, cid, course name and credit_status is Credit by
student whose student name starts with “S” ends with “S” and has at least one “S”
in between. Use UPPER function before string comparison
Instructions Adhere to the following
SQL statements Write the SQL statements corresponding to each task in a text file.
File naming text file name should be [Your roll number].sql
Submission Procedure You should upload all the SQL files.
Marking Scheme Provided for each task separately
Page 3