catalogue
1. Query the names of all students and their year of birth
3. Query the student number of the elective course
4. Query the names and genders of students not majoring in mathematics or computer science
6. Inquire about each student and their elective courses
8. Inquire about students studying in the same department as Liu Chen
2, P71 exercise 5 table building query
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
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;