Download dbms assignment questions set 1 and set 2 and more Exams Database Management Systems (DBMS) in PDF only on Docsity!
SET I
Create the following tables with constraints specified and given data: Vendor vendor_id vendor_name City state Bdate Primary key Check that name should be not null V 1000 ANAND Kanchipuram Tamilnadu 10 - sep- 1966 V 1010 PRADEEP Rajahmundry Andhra Pradesh 08 - jun- 1975 V 1015 VARGHEESE Calicut ,Kerala 07 - aug- 1964 V 1018 MADHAVANAN Erode Tamilnadu 22 - may- 1960 V 1005 PRASAD Erode Andhra Pradesh 10 - jul- 1971 V 1022 SUNAND Salem Tamilnadu 22 - oct- 1973 Material material_id material_name material_category Quantity Primary key
Check Material name
should be only X1,X2,X
or X
Check Material name should
be only A ,A++,A+
Check
Quantity
M 1001 X 1 A 210
M 1002 X 2 A++ 105
M 1003 X 3 A 250
M 1004 X 4 A+ 155
M 1005 X 1 A+ 145
M 1006 X 1 A++ 110
M 1 XS 4 X 4 A+ 155
Supply vendor_id material_id unit_cost
Foreign key Foreign Key Check Unit cost > 10
V 1000 M 1005 145. 50
V 1010 M 1001 121. 60
V 1005 M 1004 142. 75
V 1015 M 1002 210. 50
V 1018 M 1006 215. 75
V1018 M1002 230.
V 1022 M 1003 120. 55
V 1000 M 1001 121. 60
V 1005 M 1004 142. 75
V1006 M1007 143.
1. Create the tables and insert the given records [1]
2. Enforce all the constraints mentioned on the tables[2]
3. Display the Vendor Name of all Vendors who supply products
with a unit cost greater than sum of unit cost of all products
supplied by vendors from calicut [2]
4. Display the city which has the maximum number of vendors who
are above 25 years of age.[ 2 ]
5. Create a View to display the vendor name and the count of A++
materials supplied by each vendor[2]
6. Display the count of materials supplied by vendors who are
above 40 years of age[2]
7. Display the vendor names of all vendors who supply materials
which has a quantity greater than the maximum material quantity
supplied from vendors who are from Salem.[2]
8. Display vendor names and total quantity of materials supplied by
each vendor where the total quantity is greater than 200 units[2]
- - ------------------------------------------------ End of SET I-------------------------------------
1. Create the tables and insert the given records [1]
2. Enforce all the constraints mentioned on the tables[2]
3. Display the course name which has got the maximum number of
enrollments from the students who have joined in the year
2008.[2]
4. Display the student name and total number of credits enrolled by
them where total number of credits is greater than 2 credits[ 2 ]
5. Display the student id, student name ,course Id Course name of
all students whose date of joining between 2007 and 2008[2]
6. Display the student id student name course id of all students
who have registered for a course and student id and student
name of all students who have not registered for a course.[2]
7. Display the number of courses registered by students for each
level .For instance the number of courses registered by students
at SJR level is 1.[2]
8. Display the names of all the students who have got total number
of credits greater than the maximum credits enrolled by students
at SJR Course Level.[2]
- - ------------------------------------------------ End of SET II-------------------------------------
