DA201: Relational Database Management Systems Lab
Lab # 06 (1 Question, 76 Marks) No. of Pages: 5
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. View creation, View update,
ii. View with row selection, distinct value selection, group by clause,
iii. Check clause, Constraint naming, Default values,
iv. Type conversion, Join clause variants.
b. You can refer to the text book for SQL syntax.
Question 1: (76 points)
Using MySQL perform the following tasks:
Task 01 – (1 mark) Create a database named week06
Task 02 (3 marks) Create tables
a. (1 mark) A table student18a containing the following
1st column name string of characters of fixed size 100
2nd column roll_number string of characters of fixed size 10
with roll_number as primary key.
b. (1 mark) A table course18a containing the following
1st column semester integer
2nd column cid string of characters of fixed size 7
rd
3 column name string of characters of fixed size 100
th
4 column l integer
th
5 column t integer
th
6 column p integer
th
7 column c integer
with cid as primary key.
c. (1 mark) A table grade18a containing the following
1st column roll_number string of characters of fixed size 10
2 nd column cid string of characters of fixed size 7
3rd column letter_grade string of characters of fixed size 2
with roll_number and cid together form primary key
Task 03 (3 marks) Populate data
Page 1
a. (1 mark) Populate data from the file [Link] into table student18a
b. (1 mark) Populate data from the file [Link] into table course18a
c. (1 mark) Populate data from the file [Link] into table grade18a
Task 04 (13 marks) Views
a. View involving row selection
i. (3 marks) Create a view containing three columns roll_number, cid,
letter_grade with students who have taken major course.
ii. (1 mark) Insert a record 240123456, DA 201, AB into grade18a table.
iii. (1 mark) Query the view for the roll_number = 240123456. Is the view
created above materialized?
b. View providing distinct values
i. (3 marks) Create a view containing cid and letter_grade columns
having distinct values from the grade18a table.
ii. (1 mark) Insert a record DA 2 01, DA into the created view. Is the view
updatable/not updatable? Why?
c. View involving group by clause
i. (3 marks) Create a view containing cid, letter_grade, number of students
obtained within the given letter_grade
ii. (1 mark) Insert a record DA 201, NP, 10 into the created view. Why Is the
view updatable/not updatable?
Task 05 (14 marks) The check clause
a. (1 mark) Create course18b table whose specification is given in Task 02 (b).
At the time of table creation specify the following constraint:
i. (3 marks) semester should take values from the set of integers {1, 2, 3, 4}
ii. (1 mark) Insert a row (5, ‘DA 201, ‘Introduction to Chat GPT’, 3, 1, 1,
3) into course18b. Is the above constraint honored?
b. (1 mark) Create table allowable_letter_grade with the following details:
1st column grade string of characters of fixed size 2
2nd column value integer
Page 2
and having rows:
allowable letter grade
grade value
AS 10
AA 10
AB 9
BB 8
BC 7
CC 6
CD 5
DD 4
FP 0
FA 0
NP 0
PP 0
I 0
X 0
Do not use load statement to populate this data.
c. (1 mark) Create table grade18b whose specification is given in Task 02 (c).
At the time of table creation specify the constraint:
i. (3 marks) The column letter_grade must take one of the values as given
in table allowable_letter_grade column grade.
ii. (1 mark) Populate data from the file [Link] into table grade18b
iii. (1 mark) Update grades of students in grades18b who registered for minor
course XX102M and obtained ‘DD’, to convert to grade ‘DA’.
Is the update statement above honoring the constraint Task 05(c.i)? Why?
Task 06 (10 marks) Naming constraints
a. (3 marks) Create student18c table whose column specifications are as given
in Task 02 (a). Make roll_ number primary key. Name this primary key
constraint.
b. (4 marks) create a grade18c table whose column specifications are as given
in Task 02 (c). Make roll_ number and cid a primary key. Provide a name
for this primary key constraint. Make roll_ number foreign key pointing to
student18c table. Give a name to this foreign key constraint.
c. (3 marks) Remove the foreign key constraint created above (Task 06 (b)).
Page 3
Task 07 (8 marks) Type conversion
a. (1 mark) Create a table student18d whose description is identical to
specification given in Task 01 (a).
b. (1 mark) Populate data from the file [Link] into student18d
c. (3 marks) Find the sum, minimum, maximum and average of roll numbers
from the table student18d by converting roll_number data type to unsigned
int.
d. (3 marks) List the roll_number by converting it into datetime data type from
the student18d table.
Task 08 (6 marks) Table extensions
a. (3 marks) Using table extensions, create course18e table with similar
description as that given in course18a of Task 02 (b)
b. (3 marks) Populate the data from course18a table into course18e table
Hint: Use insert statement along with subquery select
Task 09 (20 marks) Join variants
a. Create the following three tables:
i. (1 mark) A table student18f containing the following
1st column roll_number string of characters of fixed size 10
2nd column name string of characters of fixed size 100
3rd column redundant01 integer
with following constraints:
roll_number as primary key
redundant01’s default value is 10
ii. (1 mark) A table course18f containing the following
1st column semester integer
2nd column cid string of characters of fixed size 7
rd
3 column name string of characters of fixed size 100
4th column l integer
5th column t integer
6th column p integer
7th column c integer
8th column redundant01 integer
with following constraints:
cid as primary key
redundant01’s default value is 10
Page 4
iii. (1 mark) A table grade18f containing the following
1st column roll_number string of characters of fixed size 10
2nd column cid string of characters of fixed size 7
rd
3 column letter_grade string of characters of fixed size 2
4th column redundant01 integer
with following constraints:
roll_number, cid as primary key
redundant01’s default value is 10
b. populate data
i. (3 marks) Populate data from the file [Link] into table student18f
such that the default constraint is honored
ii. (3 marks) Populate data from the file [Link] into table course18f
such that the default constraint is honored
iii. (3 marks) Populate data from the file [Link] into table grade18f
such that the default constraint is honored
c. (3 marks) Join: Using the tables above, list student roll_number,
name and letter_grade obtained with course having credit structure
2-1-0-6
d. (1 mark) Delete all record from grade18f
e. (1 mark) Populate data from the file [Link] into table grade18f
such that default constraint is honored
f. (3 marks) List all the students’ roll_numbers, name, and corresponding
letter_grade obtained from the tables student18f and grade18f.
Instructions: Adhere to the following
SQL statements: Write the SQL statements corresponding to each task in a text file.
Marking Scheme Mentioned against each task.
Page 5