SQL query 7-step solution and grammatical difference analysis of an interview question

This article is about a detailed syntax for a SQL interview question. About the execution process (on where), inner connection, outer connection (left and right) on the utility. For these basic grammatical knowledge, please refer to my previous SQL basic syntax.

This table is named Sc,sno, cno Course number, Scgrade score. Requirements: List the results of all student numbers and their "1" and "2" courses in the "1" course with a higher performance than the "2" course.

Requirements: List The results of all student numbers and their "1" and "2" courses in the "1" course with a higher performance than the "2" course.

1, find out all the list of 1 2nd courses (including number, course, score) respectively.

2, Condition 1 The grade of the timetable >2 the grade of the timetable . Two tables connect the query.

3, the condition of the hermit, the numbers of the two tables are equal. Two tables connect the query.

4,select the serial number of table 1 or Table 2, table 1. Results, table 2. Results.

5, respectively, from the alias.

SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECT * fromScWHERECNO=1) asA Cross JOIN(SELECT *  fromScWHERECNO=2) asbWHEREA.scgrade>B.scgrade andA.sno=B.sno;SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECTSno,scgrade fromScWHERECNO=1) asA Cross JOIN(SELECTSno,scgrade fromScWHERECNO=2) asbWHEREA.scgrade>B.scgrade andA.sno=B.sno;SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECTSno,scgrade fromScWHERECNO=1) asAINNER JOIN(SELECTSno,scgrade fromScWHERECNO=2) asb onA.scgrade>B.scgrade andA.sno=B.sno;SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECTSno,scgrade fromScWHERECNO=1) asAINNER JOIN(SELECTSno,scgrade fromScWHERECNO=2) asb onA.scgrade>B.scgradeWHEREA.sno=B.sno;SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECTSno,scgrade fromScWHERECNO=1) asA Left JOIN(SELECTSno,scgrade fromScWHERECNO=2) asb onA.scgrade>B.scgrade andA.sno=B.sno;SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECTSno,scgrade fromScWHERECNO=1) asA Left JOIN(SELECTSno,scgrade fromScWHERECNO=2) asb onA.scgrade>B.scgradeWHEREA.sno=B.sno;SELECTA.sno as 'School Number', A.scgrade as 'Course No. 1th', B.scgrade as 'Course No. 2nd'  from(SELECTSno,scgrade fromScWHERECNO=1) asA Right JOIN(SELECTSno,scgrade fromScWHERECNO=2) asb onA.scgrade>B.scgrade andA.sno=B.sno;

All Methods

The first type:

Select A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (select *from SC WHERE cno=1) as A cross JOIN (SELECT * FR OM SC where cno=2) as B where A.scgrade>b.scgrade and A.sno=b.sno;

The second type:

Select  A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (SELECT sno,scgrade from SC WHERE cno=1) as A cross JO In (SELECT Sno,scgrade from SC where cno=2) as B where A.scgrade>b.scgrade and A.sno=b.sno;

　　

Third Kind

Select  A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (SELECT sno,scgrade from SC WHERE cno=1) as A INNER JO In (SELECT sno,scgrade from SC WHERE cno=2) as B on A.scgrade>b.scgrade and A.sno=b.sno;

　

The fourth type:

Select  A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (SELECT sno,scgrade from SC WHERE cno=1) as A INNER JO In (SELECT Sno,scgrade from SC where cno=2) as B on A.scgrade>b.scgrade WHERE A.sno=b.sno;

　　

The fifth type:

Select  A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (SELECT sno,scgrade from SC WHERE cno=1) as A left JOI N (SELECT sno,scgrade from SC WHERE cno=2) as B on A.scgrade>b.scgrade and A.sno=b.sno;

　　

The sixth type:

Select  A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (SELECT sno,scgrade from SC WHERE cno=1) as A left JOI N (SELECT sno,scgrade from SC where cno=2) as B on A.scgrade>b.scgrade WHERE A.sno=b.sno;

　

The seventh type:

Select  A.sno as ' study number ', A.scgrade as ' 1th course ', B.scgrade as ' 2nd Course ' from (SELECT sno,scgrade from SC WHERE cno=1) as A right JO In (SELECT sno,scgrade from SC WHERE cno=2) as B on A.scgrade>b.scgrade and A.sno=b.sno;

　　

The difference between methods 1 and 2 is the redundancy of the query fields for two join tables.

Method 3 demonstrates the use of inner jion and on connections, and differs from cross jion.

Methods 4 and 3 demonstrate the sequence of inner join processes, starting from within (containing on), and then where. The inner join and on where function the same, but in different order.

Method 5 demonstrates pure left join and on multi-condition usage.

Methods 6 and 5 demonstrate the left-link process sequence, starting from within (containing on), and then where. The inner join and on where function the same, but in different order.

Method 7 use on multiple conditions for right connection.

These distinctions have been distinguished very clearly. Specific syntax please see my previous several basic grammar articles about the database.

Of course, these aren't cutting-edge technologies, but they're meticulous, right?

　　

SQL query 7-step solution and grammatical difference analysis of an interview question