1 / 4

Exercises of SQL

Exercises of SQL. Answer the following questions, based on the database below. Supplier ( SNO , SNAME, STATUS, CITY) Part( PNO , PNAME, COLOR, WEIGHT) Project( JNO , JNAME, CITY) SPJ( SNO , PNO , JNO , QTY)

naomi
Download Presentation

Exercises of SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exercises of SQL • Answer the following questions, based on the database below. • Supplier(SNO, SNAME, STATUS, CITY) • Part(PNO, PNAME, COLOR, WEIGHT) • Project(JNO, JNAME, CITY) • SPJ(SNO, PNO, JNO, QTY) • The schema has four relations. The key attributes for each relation are shown in red.

  2. 1.Give suitable declarations for each relation. • 2.Write the following queries: • (1)Find the name and city of all the Supplier. • (2)Find the name, color and weight of all the parts. • (3)Find the number of all the projects using the parts that provided by S1. • (4)Find the name and quantity of all the parts used by J2. • (5)Find the number of all the parts made in ShangHai. • (6)Find the name of all the projects which have used the parts made in ShangHai.

  3. (7)Find the number of all the projects that didn’t used the parts made in TianJin. • (8)Update all the parts which color is red with blue. • (9)Update the Supplier S5 of the part P6 used by J4 with Supplier S3. • (10)Delete all the records about S2 from relation Supplier, and delete corresponding records from relation SPJ. • (11)Insert a new record(S2, J6, P4, 200) into relation Supplier. • (12)Grant the INSERT privilege on table Supplier to user John, and he includes the grant option with this privilege. • (13)Grant the SELECT privilege on table SPJ and UPDATE privilege on attribute QTY of SPJ to user Allice.

  4. 3.Construct a view ThirdProj giving the Supplier number(Sno), part number(Pno), supporting quantity(QTY) of all Supplier who provide parts for “Third Project”. Write each of the queries using this view. • (1)Find the part number and supporting quantity of all parts used by “Third Project”. • (2)Find the supporting relation of S1.

More Related