SQL Server Data Query-complex Query

SQL Server Data Query-for more information about complex queries, see.

SQL Server Data Query-for more information about complex queries, see.

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

Example 1 query the status of each student and their Optional Courses

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

The Code is as follows:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
Insert into course (CNO, CNAME, CPNO, CCREDIT) SELECT '8', CNAME, CPNO, ccredit from course where cno = '2 ';