Using Advanced SQL

Department of Information Systems School of Information and Technology

The SELECT command

To retrieve data from a table, the table is queried An SQL SELECT statement is used to do this. The statement is divided into:
A select list (the part that lists the columns to be returned), A table list (the partthat lists the tables from which to retrieve the data) An optional qualication (the part that species any restrictions) For example, to retrieve all the rows of table Customer: SELECT * FROM Cutomer;

The SELECT command format

The DISTINCT Clause

[ALL | DISTINCT [ON (expression [,...] ) ] ]
ALL = all records returned in the result set (default) Default: DISTINCT only eliminates records that are complete duplicates ON option to define which column to compare for duplicates

select distinct on ("City") "City", "State" from store."Customer";

The SELECT List

* | expression [AS output_name ] [,... ]
AS option allows you to change the column heading label in the output to a value different from the column name

select "CustomerID" as "ID", "LastName" as "Family", "FirstName" as "Person" from store."Customer";

The FROM Clause

FROM from_list [,...] The most complex part of the SELECT command

Standard Table Names

[ONLY ] table_name [ * ] [ [ AS ] alias [ (column_alias [,...] ) ] ]
ONLY option directs PostgreSQL to search only the table specified, and not any tables that inherit the specified table * directs PostgreSQL to search all child tables of the specified table

SubThe Sub-select
( select ) [ AS ] alias [ (column_alias [,...] ) ] select * from (select "CustomerID", "FirstName" from store."Customer") as test ("ID", "Name");

Functions
The result set of the declared function is used as the input to the first SELECT command

Joins
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING (join_column [,...]) ]
NATURAL keyword is used to join tables on common column names USING keyword to define specific matching column names in both tables ON keyword to define a join condition

select "Customer"."LastName", "Customer"."FirstName", "Product"."ProductName", "Order"."TotalCost" from store."Order" natural inner join store."Customer natural inner join store."Product";

10

Inner Joins
Queries have only accessed one table at a time. Queries can access multiple tables at once Access the same table in such a way that multiple rows of the table are being processed at the same time => called a join query Select the pairs of rows where these values match: SELECT * FROM Product, Order WHERE Product.ProductID = Order.ProductID ; OR SELECT * FROM Product INNER JOIN Order ON (Product.ProductID = Order.ProductID ;)

11

Outer Joins
SELECT * FROM Order LEFT OUTER JOIN Product ON (Order.ProductID= Product.ProductID); This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no righttable match, empty (null) values are substituted for the right-table columns. There are also right outer joins and full outer joins
12

Self Join
We can also join a table against itself. This is called a self join. Example: suppose we wish to nd all the weather records that are in the temperature range of other weather records, so we need to compare the temp_lo and temp_hi columns of each weather row to the temp_lo and temp_hi columns of all other weather rows: SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high, W2.city, W2.temp_lo AS low, W2.temp_hi AS high FROM weather W1, weather W2 WHERE W1.temp_lo < W2.temp_lo AND W1.temp_hi > W2.temp_hi;

13

Aggregate Functions
An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows. SELECT max(UnitPrice) FROM Product; SELECT ProductName FROM Product WHERE UnitPrice = max(UnitPrice); //WRONG TRUE: => using a subquery: SELECT ProductName FROM Product WHERE UnitPrice = (SELECT max(UnitPrice ) FROM Product );
14

Aggregate Functions
Aggregates are also very useful in combination with GROUP BY clauses: SELECT OrderID, max(TotalCost) FROM Order GROUP BY ProductID;

15

The WHERE Clause

WHERE condition [,...] Arbitrary Boolean operators (AND, OR, and NOT) are allowed in the qualication of a query: SELECT * FROM Product WHERE ProductName= Soup AND UnitPrice> 30;

16

The GROUP BY Clause

GROUP BY expression [,...] GROUP BY clause be always used with a PostgreSQL function that aggregates values from similar records select sum("Order"."Quantity"), "Order"."ProductID" from store."Order" group by "ProductID"; Be careful with the GROUP BY and ORDER BY clauses
GROUP BY clause groups similar records BEFORE the rest of the SELECT command is evaluated ORDER BY clause orders records AFTER the SELECT commands are processed
17

The HAVING Clause

HAVING condition [,...] The HAVING clause is similar to the WHERE clause, in that it is used to define a filter condition to limit records used in the GROUP BY clause Records that do not satisfy the WHERE conditions are not processed by the GROUP BY clause The HAVING clause filters the records contained in the result set after the GROUP BY clause groups the records.

18

The Set Operation Clauses

select1 [ (UNION | INTERSECT | EXCEPT ]) [ ALL ] select2 The Set Operation clause types are
UNION Display all result set records in both select1 and select2 INTERSECT Display only result set records that are in both select1 and select2 EXCEPT Display only result set records that are in select1 but not in select2

By default, duplicate records in the output set are not displayed ALL

19

The ORDER BY Clause

[ ORDER BY expression [ ASC | DESC | USING operator ] [,...] ] By default, the ORDER BY clause orders records in ascending order The USING parameter declares an alternative operator to use for ordering
(<) is equivalent to the ASC keyword (>) is equivalent to the DESC keyword

20

The LIMIT Clause

[ LIMIT ( count | ALL ) ] [ OFFSET start ] The LIMIT clause specifies a maximum number of records to return in the result set The default behavior is LIMIT ALL, which returns all records in the result set The OFFSET parameter allows you to specify the number of result set records to skip before displaying records in the output
first record in the result set is at start value 0 Start value 1 is the second record

21

The FOR Clause

[ FOR (UPDATE | SHARE ) [ OF table_name [,...] [ NOWAIT ] ] FOR UPDATE locks the records (viewing, deleting, or modifying) NOWAIT parameter, the SELECT command does not wait, but instead exits with an error stating that the records are locked FOR SHARE clause allows other users to view the records If you do not want to lock all of the records returned in the result set, combine the FOR clause with the LIMIT clause

22

Views
Have covered the basics of using SQL to store and access our data in PostgreSQL. Here, discuss some more advanced features of SQL that simplify management and prevent loss or corruption of data. Suppose the combined listing of specified records is of particular interest to your application, but you dont want to type the query each time you need it. Create a view over the query, which gives a name to the query that you can refer to like an ordinary table CREATE VIEW myview AS SELECT ProductName, Model, OrderID, Quantity, TotalCost FROM Product, Order WHERE ProductID= 2000; SELECT * FROM myview;

23

Views
Making liberal use of views is a key aspect of good SQL database design Views allow to encapsulate the details of the structure of our tables, which may change as our application evolves, behind consistent interfaces. Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.

24

Transactions
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions. if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all

25

Transactions
For example: consider a bank database that contains balances for various customer accounts, as well as total deposit balances for branches. Suppose that we want to record a payment of $100.00 from Alices account to Bobs account. Simplifying outrageously, the SQL commands for this might look like:
UPDATE accounts SET balance = balance - 100.00 WHERE name = Alice; UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = Alice) UPDATE accounts SET balance = balance + 100.00 WHERE name = Bob; UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = Bob);

26

Transactions
There are several separate updates involved to accomplish this rather simple operation. Banks ofcers will want to be assured that either all these updates happen, or none of them happen. We need a guarantee that if something goes wrong partway through the operation, none of the steps executed so far will take effect. Grouping the updates into a transaction gives us this guarantee. A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.
27

Transactions
Another important property of transactional databases is closely related to the notion of atomic updates: when multiple transactions are running concurrently, each one should not be able to see the incomplete changes made by others. So transactions must be all-or-nothing not only in terms of their permanent effect on the database, but also in terms of their visibility as they happen. In PostgreSQL, a transaction is set up by surrounding the SQL commands of the transaction with BEGIN and COMMIT commands. So our banking transaction would actually look like:

28

Transactions
BEGIN; UPDATE accounts SET balance = balance - 100.00 WHERE name = Alice; -- etc etc COMMIT; If, partway through the transaction, we decide we dont want to commit (perhaps we just noticed that Alices balance went negative), we can issue the command ROLLBACK instead of COMMIT, and all our updates so far will be canceled.

29

30