Intra-Link sub-query in outer joins

Cartesian product query: (meaningless)

Grammar:

* SELECT * from a B;

* The result of the Cartesian product query is not the desired result!!!

Internal connection Query

Grammar:

* SELECT * from A inner join B on condition; ---inner can be omitted!!!

* SELECT * from A join B on condition;

Case:

* SELECT * FROM dept join emp on dept.did = Emp.dno;

Equivalent to

* SELECT * from dept,emp where dept.did = emp.dno;---implicit inner join

Outer JOIN query for LEFT OUTER JOIN query

Grammar:

* SELECT * from A LEFT outer join B on condition; ---outer can be omitted

* SELECT * from A LEFT join B on condition;

Case:

* SELECT * FROM dept LEFT OUTER join emp on dept.did = Emp.dno;

Right outer join query

Grammar:

* SELECT * from A right outer join B on condition;---outer can be omitted

* SELECT * from A right join B on condition;

Case:

* SELECT * FROM Dept right outer join emp on dept.did = Emp.dno;

Sub-query:

Subquery: The result of one SQL query needs to depend on the results of another SQL query!

To create a table:

CREATE TABLE Exam (

ID int primary KEY auto_increment,

Name varchar (20),

math int,

中文版 int

);

INSERT INTO exam values (NULL, ' AAA ', 59,61);

INSERT INTO exam values (NULL, ' BBB ', 62,81);

INSERT INTO exam values (NULL, ' CCC ', 73,73);

INSERT INTO exam values (null, ' DDD ', 84,64);

INSERT INTO exam values (null, ' Eee ', 91,58);

INSERT INTO exam values (NULL, ' FFF ', 69,92);

INSERT INTO exam values (null, ' GGG ', 75,83);

* Query the math score on the math average score above the classmate information:

* Calculate the average score for mathematics: Select AVG (math) from exam;

* Find math scores greater than average: SELECT * from exam where math > average score;

* Average is the result of a SQL query.

* SELECT * from exam where math > (select AVG (math) from exam);

You can use the keyword any, all in subqueries.

* Any: arbitrary

* > Any:select * from exam where 中文版 > any (select math from exam);

* Any: one value. The smallest one can be.

* < Any:select * FROM exam where 中文版 < any (select math from exam);

* Any: one value. The maximum value comparison is possible.

* All: All

* > All:select * from exam where 中文版 > All (select Math from exam);

* < All:select * FROM exam where 中文版 < All (select math from exam);

* Multi-Table query:

* Count the number of people by department name!

* Select D.dname,count (*) from dept d,emp e where d.did = E.dno GROUP by D.dname;

* Statistics per department average salary!

* Select D.dname,avg (eprice) from Dept d,emp e where d.did = E.dno GROUP by D.dname;

* Check which employees pay more than the average salary of any department:

* SELECT * from emp where eprice > A (select AVG (eprice) from Dept d,emp e where d.did = E.dno Group by d.dname);

* Check which employees pay more than the average salary of all departments:

* SELECT * from emp where eprice > All (select AVG (eprice) from Dept d,emp e where d.did = E.dno Group by d.dname);

Intra-Link sub-query in outer joins