* Query Structure
* Mulitple Table Queries
* Aggregation
* Creating Views
---
background-image: url(images/reg_background.png)
## The SQL Query Structure
* Query Structure
```sql
--Simple query
SELECT column1, column2, ...
FROM table_name;
--With conditions
SELECT column1, column2, ...
FROM table_name WHERE condition1 AND condition2 OR condition3 ...;
--With more options
SELECT column1, column2, ...
FROM table_name WHERE condition1 UNION
SELECT... GROUP BY... ORDER BY... ;
```
* We can have SQL subqueries and even loops (not covered in this unit).
---
background-image: url(images/reg_background.png)
## The 'WHERE' Clause.
* The 'WHERE' clause has all the comparision and logical operators of a regular programming language.
| | Logical | Example | |:----------:|:-----------------:| | BETWEEN | a BETWEEN x AND y | | AND | a>=x AND a>=y | | OR | a>=x OR a<=y | | NOT | a IS NOT NULL | | IS | a IS DISTINCT | | LIKE | a LIKE '%wordPart%'| |
* RIGHT OUTER JOIN
* LEFT OUTER JOIN
* FULL OUTER JOIN
* JOIN syntax (inner):
```sql
--Query structure
SELECT column_1, column_2,..., column_n
FROM table_1 INNER JOIN table_2 ON table1.column_1 = table_2.column_2;
--Compact syntax
SELECT t_1.column_1, t_2.column_2,..., t_n.column_n
FROM table1 AS t_1 JOIN table2 as t_2 ON t_1.column_1 = t_2.column_2;
```
---
background-image: url(images/reg_background.png)
## Multiple Table Results Using Inner Joins
* Display customers with a student account in Armidale branch.
* Will include tables:
* customer
* customer_account
* account
* bank_branch
```sql
SELECT c.cname, a.atype, b.b_address FROM customer AS c
JOIN customer_account AS ca ON c.ssn = ca.cssn
JOIN account AS a ON ca.ano = a.anum
JOIN bank_branch AS b ON a.bno = b.bnum
WHERE b.b_address LIKE '%Armidale%'
AND a.atype LIKE '%Student%';
```
---
background-image: url(images/reg_background.png)
## Multiple Table Results Using Inner Joins
* The WHERE clause can be used as an alternative to INNER JOINs.
* This produces the same results, but is more compact.
* But this may be harder to read.
* WHERE syntax (join):
```sql
--Query structure
SELECT column_1, column_2,..., column_n
FROM table_1, table_2 WHERE table1.column_1 = table_2.column_2;
--Compact syntax
SELECT t_1.column_1, t_2.column_2,..., t_n.column_n
FROM table1 AS t_1, table2 AS t_2 WHERE t_1.column_1 = t_2.column_2;
```
---
background-image: url(images/reg_background.png)
## Multitable Table Results Using the Where Clause
* Display customers with a student account in Armidale branch.
* Will include tables:
* customer
* customer_account
* account
* bank_branch
```sql
SELECT name,atype,b_address FROM customer,customer_account, account,bank_branch
WHERE customer.ssn=customer_account.cssn
AND customer_account.ano=account.anum
AND account.bno=bank_branch.bnum
AND account.atype='Student'
AND bank_branch.b_address LIKE '%Armidale%';
```
* INNER JOINS and WHERE clauses are often interchangeable.
---
background-image: url(images/reg_background.png)
## Aggregate Functions
* Aggregae functions inlucde:
* MIN( )
* MAX( )
* AVG( )
* COUNT( )
* SUM( )
* A *GROUP BY* is required to pivot around an attribute.
* The HAVING clause is used instead of WHERE to compare results.
```sql
SELECT t1.column_1, AVG(t2.column_2) AS Average,SUM(t2.column_3) AS total, COUNT(t2.column_4)
FROM table_1 AS t1, table_2 AS t2
WHERE t1.column_5 = t2.column_6 GROUP BY t1.column_1 HAVING AVG(column2) > 100;
```
---
background-image: url(images/reg_background.png)
## Aggregate Functions
* Lets display average, total and count of balances per branch.
* Will include tables:
* bank_branch
* account
* customer_account
```sql
SELECT b_address,AVG(balance) AS Average,SUM(balance) AS total, COUNT(balance)
FROM bank_branch,account,customer_account
WHERE account.bno=bank_branch.bnum AND account.anum=customer_account.ano
GROUP BY b_address;
```
---
background-image: url(images/reg_background.png)
## Aggregate Functions
* List the number of accounts and names of customers who have more than one bank account.
* Will include tables:
* customer
* customer_account
```sql
SELECT cname, COUNT(ca.ano)
FROM customer AS c, customer_account AS ca
WHERE c.ssn=ca.cssn
GROUP BY ssn
HAVING COUNT(ca.ano) > 1;
```
---
background-image: url(images/reg_background.png)
## Creating Views
* PostgreSQL can be used to create different user views.
* Accessing views is similar to accessing tables.
* The \dv command can be used instead of \dt.
```sql
--Syntax
CREATE VIEW view_name AS
SELECT column1,column2... FROM table_name...;
```
---
background-image: url(images/reg_background.png)
## Creating Views
* A view of customer_details that lists:
* Customer name
* Account balance
* Customer address
```sql
--- '$'||''||' converts our balance to a string and adds a dollar sign.
CREATE VIEW customer_details AS
SELECT cname,'$'||''||balance as Balance,c_address
FROM customer,customer_account
WHERE customer.ssn=customer_account.cssn;
```
---
background-image: url(images/reg_background.png)
## Creating Views
* A view of customer_loans that lists:
* Customer name
* Loan amount
* Loan type
```sql
CREATE VIEW loan_details AS
SELECT name,amount,ltype as loan_type
FROM customer,loan,customer_loan
WHERE customer.ssn=customer_loan.cssn
AND customer_loan.lno=loan.lnum;
```
---
background-image: url(images/reg_background.png)
class: middle, center
# Questions?