Page 170 - SQL

SELECT Fname, LName, ManagerFirstName
FROM Employees
NATURAL JOIN
( SELECT Id AS ManagerId, Fname AS ManagerFirstName
FROM Managers ) m;

Note that although an alias/range variable must be declared for the dervied table (otherwise SQL
will throw an error), it never makes sense to actually use it in the query.

Select rows from multiple tables

SELECT *
FROM
table1,
table2

SELECT
table1.column1,
table1.column2,
table2.column1
FROM
table1,
table2

This is called cross product in SQL it is same as cross product in sets

These statements return the selected columns from multiple tables in one query.

There is no specific relationship between the columns returned from each table.

Selecting with Aggregate functions

Average

The AVG() aggregate function will return the average of values selected.

SELECT AVG(Salary) FROM Employees

Aggregate functions can also be combined with the where clause.

SELECT AVG(Salary) FROM Employees where DepartmentId = 1

Aggregate functions can also be combined with group by clause.

If employee is categorized with multiple department and we want to find avg salary for every
department then we can use following query.

SELECT AVG(Salary) FROM Employees GROUP BY DepartmentId

https://riptutorial.com/ 152