Database textbook SQL Chapter Three answers

3 . Create the 4 tables in the second chapter of exercise 5 with the sQL statement .

For:

For s table: s (SNO, SNAME, STATUS, city);

Build S table:

CREATE TABLE S (Sno C (2) UNIQUE,Sname C (6) ,Status C (2),CityC (4) );

For P table: P (PNO, PNAME, COLOR, WEIGHT);

Build P table:

CREATE TABLE P (Pno C (2) UNIQUE,Pname C (6),COLOR C (2), WEIGHT int);

For J table: J (Jno, Jname,city) ;

Building J table:

CREATE TABLE J (Jno C (2) Unlque,jname C (8), city C (4))

For SPJ table: SPJ (SNo, PNo, Jno, QTY) ;

Jian SPJ table:SPJ (sno,pno,jno,qty)

CREATE TABLE SPJ (Sno C (2),Pno C (2),jno C (2), QTY INT))

4. For the 4 Tables established in the previous topic , try the SQL language to complete the query in Exercise 5 of chapter two.

(l ) Supply Engineering Jl parts supplier number SNO;

SELECT DIST SNO from SPJ WHERE jno= ' J1 '

(2 ) Supply Engineering Jl parts Pl supplier number SNO;

SELECT DIST SNO from SPJ WHERE jno= ' J1 ' and pno= ' P1 '

(3 ) Supply Engineering Jl parts for the red supplier number SNO;

SELECT SNO from spj,p WHERE jno= ' J1 ' and SPJ. Pno=p.pno and color= ' red '

(4 ) The project number Jno of red parts produced by Tianjin supplier is not used ;

SELECT DIST Jno from SPJ where Jno isn't in (Sele jno from spj,p,s WHERE s.city= ' Tianjin ' and color= ' red ' and S.SNO=SPJ. SNO and P.PNO=SPJ. PNO).

(5 ) to use at least the supplier Sl Supply of all parts of the project number Jno;

Because VFP does not allow subqueries to be nested too deeply, the query is divided into two steps

A, query S1 supplier Supply Part number

SELECT DIST PNO from SPJ WHERE sno= ' S1 ' result is (P1,P2)

B, query which project uses both P1 parts and P2 parts.

SELECT Jno from SPJ WHERE pno= ' P1 '

and Jno in (SELECT jno from SPJ WHERE pno= ' P2 ')

5. try the SQL language for the four tables in exercise 3 to do the following:

(1) find out the names of all suppliers and the city in which they are located.

SELECT sname,city from S

(2) Find out the name, color, and weight of all the parts.

SELECT Pname,color,weight from P

(3) find out how to use suppliers S1 The engineering number of the supplied part.

SELECT DIST Jno from SPJ WHERE sno= ' S1 '

(4) Find Projects J2 The names and quantities of the various parts used.

SELECT Pname,qty from Spj,p

WHERE P.PNO=SPJ. PNO and SPJ. jno= ' J2 '

(5) Find all the part numbers supplied by the Shanghai manufacturer.

SELECT PNO from Spj,s WHERE S.SNO=SPJ. SNO and city= ' Shanghai '

(6) The project name of the part that uses the seafood.

SELECT Jname from Spj,s,j

WHERE S.SNO=SPJ. SNO and s.city= ' Shanghai ' and J.JNO=SPJ. Jno

(7) Find out the engineering number of the parts not used in Tianjin.

Note : The Select DISP jno from SPJ where Jno isn't in (select DIST jno from Spj,s where S.SNO=SPJ. SNO and s.city= ' Tianjin ') applies to situations where Jno is unique or not unique .

Note : SELECT DIST jno from Spj,s WHERE S.SNO=SPJ. SNO and S.city<> ' Tianjin ' apply to Jno is the only case

(8) change the color of all red parts to blue.

UPDATE P SET color= ' blue ' WHERE color= ' red '

(9) by S5 Supply J4 the parts P6 replaced by S3 Supply.

UPDATE SPJ SET sno= ' S3 ' WHERE sno= ' S5 ' and jno= ' J4 ' and pno= ' P6 '

(10) To remove a vendor number from a vendor relationship is S2 record and delete the corresponding record from the supply case relationship.

A,DELETE from S WHERE sno=' S2 '

B,DELETE from SPJ WHERE sno='S2'

(11) please add (S2,J6,P4,$) Insert a supply case relationship.

INSERT into SPJ VALUES('S2', 'J6', 'P4', )

Database textbook SQL Chapter Three answers