SQL Server Data Query-complex query

/*************************************** *****

Example 1 query the status of each student and their optional courses

**************************************** *****/

The code is as follows:	Copy code
Select student. *, SC. * from SC, student where SC. SNO = STUDENT. SNO;

/*************************************** *****

Use a natural connection to connect to Example 1

**************************************** *****/

The code is as follows:	Copy code
Select student. SNAME, STUDENT. SSEX, STUDENT. SAGE, STUDENT. SDEPT, SC .* From SC, STUDENT Where SC. SNO = STUDENT. SNO;

/*************************************** **

Query the indirect preference of each course

**************************************** */

 

The code is as follows:	Copy code
Select sy. CNO, SX. cpno from course sx, course sy where sx. CNO = SY. CPNO

/*************************************** *****

Connect to instance 1 with left outer join

**************************************** *****/

The code is as follows:	Copy code
Select student. SNAME, STUDENT. SSEX, STUDENT. SAGE, STUDENT. SDEPT, SC .* From student left join SC on (SC. SNO = STUDENT. SNO );

/*************************************** *****

Query the student's student ID, name, elective course name, and score ************************** *******************/

The code is as follows:	Copy code
Select student. SNO, STUDENT. SNAME, COURSE. CNAME, COURSE. CCREDIT From SC, STUDENT, COURSE Where SC. CNO = COURSE. cno and SC. SNO = STUDENT. SNO;

/*************************************** *****

Query students in the same department as Liu Chen

**************************************** *****/

The code is as follows:	Copy code
Select sx .* FROM STUDENT SX Where sx. sdept in ( Select sy. sdept from student sy where sy. SNAME = 'Liu Chen' ); /************

Or:

The code is as follows:	Copy code
Select sx .* From student sx, STUDENT SY Where sx. SDEPT = SY. sdept and sy. SNAME = 'Liu Chen'; or: SELECT * FROM STUDENT SX Where exists ( SELECT * from student sy where sy. SDEPT = SX. sdept and sy. SNAME = 'Liu Chen' );**************/

 

/*************************************** *****

Query the student ID and name ************************** *******************/

 

The code is as follows:	Copy code
Select sno, SNAME FROM STUDENT Where sno in ( SELECT SNO FROM SC Where cno in ( SELECT CNO FROM COURSE Where cname = 'information system'                     ) );

/*************************************** *****

Find the course number of each student that exceeds the average score of his or her elective course

**************************************** *****/

 

The code is as follows:	Copy code
Select sx. SNO, SX. CNO FROM SC SX Where sx. GRADE> ( Select avg (GRADE) FROM SC SY Where sx. SNO = SY. SNO ); /*************************************** **************

Query the names and ages of a student younger than a student in another computer science department.

**************************************** ***************/

The code is as follows:	Copy code
Select sname, SAGE FROM STUDENT Where sage <ANY ( SELECT SAGE FROM STUDENT Where sdept = 'CS'                     ) And sdept <> 'CS ';

/*************************************** **************

Query the names and ages of students younger than all students in the computer science department.

**************************************** ***************/

The code is as follows:	Copy code
Select sname, SAGE FROM STUDENT Where sage <ALL ( SELECT SAGE FROM STUDENT Where sdept = 'CS'                     ) And sdept <> 'CS ';

/*************************************** **************

Query the names of all students who have selected course 1.

**************************************** ***************/

The code is as follows:	Copy code
SELECT SNAME FROM STUDENT Where exists ( SELECT * FROM SC Where SC. SNO = STUDENT. sno and SC. CNO = '1' );

/*************************************** ***

Query the names of students who have selected all courses

**************************************** ***/

The code is as follows:	Copy code
SELECT SNAME FROM STUDENT Where not exists ( SELECT * FROM COURSE Where not exists ( SELECT * FROM SC Where student. SNO = SC. sno and course. CNO = SC. CNO                                         ) );

/*************************************** **************

Query the number of all students whose courses are selected at least 200215122

**************************************** *************/

The code is as follows:	Copy code
SELECT DISTINCT SNO FROM SC X Where not exists ( SELECT * FROM SC Y Where sno = '200' and not exists ( SELECT * FROM SC Z Where z. SNO = X. sno and y. CNO = Z. CNO                                                            ) );

/*************************************** *******************

Select a record from the table, modify a field, and insert it back to the table.

Here, insert into can be used to INSERT subqueries ******************************* **************************/

The code is as follows:	Copy code
Insert into course (CNO, CNAME, CPNO, CCREDIT) SELECT '8', CNAME, CPNO, ccredit from course where cno = '2 ';