Page 169 - SQL

Results: 10 records.

Id ProductName UnitPrice Package
38 Côte de Blaye 263.50 12 - 75 cl bottles
29 Thüringer Rostbratwurst 123.79 50 bags x 30 sausgs.
9 Mishi Kobe Niku 97.00 18 - 500 g pkgs.
20 Sir Rodney's Marmalade 81.00 30 gift boxes
18 Carnarvon Tigers 62.50 16 kg pkg.
59 Raclette Courdavault 55.00 5 kg pkg.
51 Manjimup Dried Apples 53.00 50 - 300 g pkgs.
62 Tarte au sucre 49.30 48 pies
43 Ipoh Coffee 46.00 16 - 500 g tins
28 Rössle Sauerkraut 45.60 25 - 825 g cans

Vendor Nuances:

It is important to note that the TOP in Microsoft SQL operates after the WHERE clause and will return
the specified number of results if they exist anywhere in the table, while ROWNUM works as part of the
WHERE clause so if other conditions do not exist in the specified number of rows at the beginning of
the table, you will get zero results when there could be others to be found.

Selecting with table alias

SELECT e.Fname, e.LName
FROM Employees e

The Employees table is given the alias 'e' directly after the table name. This helps remove
ambiguity in scenarios where multiple tables have the same field name and you need to be
specific as to which table you want to return data from.

SELECT e.Fname, e.LName, m.Fname AS ManagerFirstName
FROM Employees e
JOIN Managers m ON e.ManagerId = m.Id

Note that once you define an alias, you can't use the canonical table name anymore. i.e.,

SELECT e.Fname, Employees.LName, m.Fname AS ManagerFirstName
FROM Employees e
JOIN Managers m ON e.ManagerId = m.Id

would throw an error.

It is worth noting table aliases -- more formally 'range variables' -- were introduced into the SQL
language to solve the problem of duplicate columns caused by INNER JOIN. The 1992 SQL
standard corrected this earlier design flaw by introducing NATURAL JOIN (implemented in mySQL,
PostgreSQL and Oracle but not yet in SQL Server), the result of which never has duplicate column
names. The above example is interesting in that the tables are joined on columns with different
names (Id and ManagerId) but are not supposed to be joined on the columns with the same name (
LName, FName), requiring the renaming of the columns to be performed before the join:

https://riptutorial.com/ 151