Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

dbms assignment questions set 1 and set 2, Exams of Database Management Systems (DBMS)

dbms assisgnment question set 1 and set 2 problems

Typology: Exams

2018/2019

Uploaded on 09/25/2019

sylviagabriel
sylviagabriel 🇮🇳

1 document

1 / 4

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
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
V1000
ANAND
Kanchipuram
Tamilnadu
10-sep-1966
V1010
PRADEEP
Rajahmundry
Andhra
Pradesh 08-jun-1975
V1015
VARGHEESE
Calicut
,Kerala
07-aug-1964
V1018
MADHAVANAN
Erode
Tamilnadu
22-may-196
0
V1005
PRASAD
Erode
Andhra
Pradesh 10-jul-1971
V1022
SUNAND
Salem
Tamilnadu
22-oct-1973
Material
material_id
material_name
material_category
Quantity
Primary
key
Check Material name
should be only X1,X2,X3
or X4
Check Material name should
be only A ,A++,A+
Check
Quantity
>=0
M1001
X1
A
210
M1002 X2 A++ 105
M1003 X3 A 250
M1004
X4
A+
155
M1005
X1
A+
145
M1006
X1
A++
110
M1XS4
X4
A+
155
Supply
vendor_id
material_id
unit_cost
Foreign key
Foreign Key
Check Unit cost > 10
V1000
M1005
145.50
V1010
M1001
121.60
V1005
M1004
142.75
V1015
M1002
210.50
V1018
M1006
215.75
V1018 M1002 230.45
V1022
M1003
120.55
V1000
M1001
121.60
V1005
M1004
142.75
V1006 M1007 143.56
pf3
pf4

Partial preview of the text

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