catalogue

1, Textbook example query

1. Query the names of all students and their year of birth

2. The name, year of birth and Department of all students shall be queried, and the Department name shall be expressed in lowercase letters

  3. Query the student number of the elective course

4. Query the names and genders of students not majoring in mathematics or computer science

  5. Query the student number and grades of students who have taken No. 3 course, and the query results are arranged in descending order of scores

6. Inquire about each student and their elective courses

7. Query the indirect prerequisite course of each course (i.e. the prerequisite course of the prerequisite course)

8. Inquire about students studying in the same department as Liu Chen

9. Query the names and ages of students in other departments who are younger than some students in the information department

2, P71 exercise 5 table building query

① Table S:

② Table P:  

③ Table J:

④ SPJ table:

1,   Query the supplier number SNO of Engineering J1 parts

2. Query the supplier number SNO of supply engineering J1 part P1

3. Query the supplier number SNO with red supply engineering J1 parts

4,   Query the engineering No. JNO of red parts produced by Tianjin suppliers   

5. The engineering number JNO of at least all parts supplied by supplier S1 is used

3, P130 question 5, complete the following operations for the four tables

1. Query the names and cities of all suppliers

2. Query the name, color and weight of all parts

3,   Query the engineering number of parts supplied by supplier S1

4. Query the names and quantities of various parts used in project J2

5. Query all part numbers supplied by Shanghai manufacturers

6. Query the project name of parts made in Shanghai

7,   Query the engineering number of parts not made in Tianjin

  8. Change the color of all red parts to blue

  9. Part P6 of J4 supplied by S5 is changed to S3

  10. Delete the record of S2 from the supplier relationship and delete the corresponding record from the supply relationship

11. Insert (S2, J6, p4200) into the supply relationship

1, Textbook example query

Table created:

Student table:

Course table:

SC table:

1. Query the names of all students and their year of birth

SELECT Sname,2021-Sage BIRTHDAY
FROM Student;

2. The name, year of birth and Department of all students shall be queried, and the Department name shall be expressed in lowercase letters

SELECT Sname NAME, 2021-Sage BIRTHDAY,LOWER(Sdept)
FROM Student;

 

  3. Query the student number of the elective course

SELECT DISTINCT Sno
FROM  SC;

4. Query the names and genders of students not majoring in mathematics or computer science

SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN('MA','CS');

 

  5. Query the student number and grades of students who have taken No. 3 course, and the query results are arranged in descending order of scores

SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

6. Inquire about each student and their elective courses

SELECT Student.*,Cno,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno

7. Query the indirect prerequisite course of each course (i.e. the prerequisite course of the prerequisite course)

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;

8. Inquire about students studying in the same department as Liu Chen

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
           (SELECT Sdept
		    FROM Student
			WHERE Sname='Liu Chen');

9. Query the names and ages of students in other departments who are younger than some students in the information department

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
			   WHERE Sdept='IS');

2, P71 exercise 5 table building query

Establish S table, P table, J table and SPJ table in SPJDATABASE database with SQL language:

/*  Create S table  */
CREATE TABLE S
(SNO CHAR(10) PRIMARY KEY,
SNAME CHAR(10),
STATUS SMALLINT,
CITY CHAR(10)
);

/*  Create P table  */

CREATE TABLE P
(PNO CHAR(10) PRIMARY KEY,
PNAME CHAR(10),
COLOR CHAR(10),
WEIGHT SMALLINT
);

/*  Create J table  */
CREATE TABLE J
(JNO CHAR(10) PRIMARY KEY,
JNAME CHAR(10),
CITY CHAR(10)
);

/*  Create SPJ table  */
CREATE TABLE SPJ
(SNO CHAR(10),
PNO CHAR(10),
JNO CHAR(10),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO)REFERENCES S(SNO),
FOREIGN KEY(PNO)REFERENCES P(PNO),
FOREIGN KEY(JNO)REFERENCES J(JNO),
);

① Table S:

② Table P:  

③ Table J:

④ SPJ table:

1,   Query the supplier number SNO of Engineering J1 parts

SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1';

3

2. Query the supplier number SNO of supply engineering J1 part P1

SELECT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1';

 

3. Query the supplier number SNO with red supply engineering J1 parts

SELECT SNO
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND JNO='J1' AND P.COLOR='red';

4,   Query the engineering No. JNO of red parts produced by Tianjin suppliers   

SELECT DISTINCT JNO
FROM J
WHERE NOT EXISTS
      (SELECT *
	  FROM S,P,SPJ
	  WHERE	S.SNO=SPJ.SNO AND
	        P.PNO=SPJ.PNO AND
			J.JNO=SPJ.JNO AND
			S.CITY='Tianjin' AND
			P.COLOR='red');
	        

5. The engineering number JNO of at least all parts supplied by supplier S1 is used

SELECT DISTINCT PNO    /*Find out all parts produced by supplier S1 first*/
FROM SPJ
WHERE SNO='S1';

SELECT JNO
FROM SPJ
WHERE SNO='S1' AND PNO='P1' AND JNO IN
                               (SELECT JNO
							   FROM SPJ
							   WHERE SNO='S1' AND PNO='P2');

3, P130 question 5, complete the following operations for the four tables

1. Query the names and cities of all suppliers

SELECT SNAME,CITY
FROM S;

2. Query the name, color and weight of all parts

SELECT PNAME,COLOR,WEIGHT
FROM P;

3,   Query the engineering number of parts supplied by supplier S1

SELECT JNO
FROM SPJ
WHERE SNO='S1';

4. Query the names and quantities of various parts used in project J2

SELECT PNAME,QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND
      JNO='J2';

 

5. Query all part numbers supplied by Shanghai manufacturers

/* FA Yi */
SELECT DISTINCT PNO
FROM SPJ,S
WHERE S.SNO=SPJ.SNO AND 
      CITY ='Shanghai';

/*Method II*/
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO IN
      (SELECT SNO
	  FROM S
	  WHERE CITY='Shanghai');

6. Query the project name of parts made in Shanghai

/*FA Yi*/ 
SELECT DISTINCT JNAME
FROM J,SPJ,S
WHERE J.JNO=SPJ.JNO AND 
      S.SNO=SPJ.SNO AND
      S.CITY='Shanghai';
/*Method II*/
SELECT JNAME
FROM J
WHERE JNO IN
          (SELECT JNO
		  FROM SPJ
		  WHERE SNO IN
		       (SELECT SNO
			   FROM S
			   WHERE CITY='Shanghai'));

7,   Query the engineering number of parts not made in Tianjin

SELECT JNO
FROM SPJ
WHERE JNO NOT IN
          (SELECT JNO
		  FROM S,SPJ
		  WHERE S.SNO=SPJ.SNO AND
		        CITY='Tianjin');

  8. Change the color of all red parts to blue

UPDATE P
SET COLOR ='blue'
WHERE COLOR ='red';

SELECT * 
FROM P;

  9. Part P6 of J4 supplied by S5 is changed to S3

UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

SELECT *
FROM SPJ;

  10. Delete the record of S2 from the supplier relationship and delete the corresponding record from the supply relationship

The main code SNO of table s imposes external code constraints on SPJ. You need to delete S2 of SPJ before deleting S2 of S.

DELETE 
FROM SPJ
WHERE SNO='S2';

DELETE
FROM S
WHERE SNO='S2';

SELECT * FROM S;
SELECT * FROM SPJ;

11. Insert (S2, J6, p4200) into the supply relationship

INSERT  /*S2 of table s has been deleted in question 10. If there is no S2 in the main code of table s, this group of data cannot be inserted into SPJ table*/
INTO S
VALUES('S2','Sheng Xi',20,'Tianjin');

INSERT
INTO SPJ(SNO,PNO,JNO,QTY)
VALUES('S2','P4','J6',200);

SELECT * FROM SPJ;