02 advanced querying

7/28/2019 02 Advanced Querying

1/60

NEBC Database Course 2008

Advanced Querying


2/60

In this section

Data types

Operators and Functions

Joins Subqueries

Union and Intersect


3/60

Data Types


4/60

Data Types

Basic data types are

Numerical

Character/Text

Date/Time

Boolean


5/60

Data Types

Any data value in a relational database must have adata type

The type of a value limits the kinds of operationsyou can do on a value

Columns in a table must have a data type

Certain data types are common to most RDBMS

Additional RDBMS-specific datatypes


6/60

Numerical

integer,int Whole numbers/integers

float

Floating point number (e.g. 21.6531 or 5555548.78) numeric(p,s)

'p' number of digits

's' number of digits after decimal point


7/60

String/Text

varchar(n)

Variable length character string of max length n

text

Variable length character string of unlimited length

PostgreSQL specific


8/60

Date/Time

timestamp

Date and time

date

Calender date (day, month and year)


9/60

Boolean

boolean Single true or false value

Supported true or false constants:

TRUE FALSE

true false

't' 'f'

'true' 'false'

'y' 'n''yes' 'no'

'1' '0'

all integers are 'true' apart from 0


10/60

Null values

You can have missing values in column of data theseare know as null values

Strictly speaking null means 'does not apply' but is oftenused for unknown values

Saves having to insert a value in all rows with awkwardconsequences. For example, if you inserted zero for allnull numbers then average would be inaccurate.

Take care with Nulls

If you add a number to a null number value the result

is null If you concatenate a string to a null string value the

result is null


11/60

Booleans and Nulls

Like other data types, booleans can be set to NULL

Be aware that NULL does not mean 'false'.

Negating NULL does not evaluate to 'true'

AND and OR operations with NULL return NULL, apartfrom (NULL or true) = true


12/60

Booleans and Nulls

Take particular care in the WHERE clause of queries

SELECT ... WHERE some_col = true;

SELECT ... WHERE some_col = false;

SELECT ... WHERE some_col != true; SELECT ... WHERE NOT some_col;

SELECT ... WHERE some_col = NULL;

SELECT ... WHERE some_col != NULL;

SELECT ... WHERE some_col IS NULL;

SELECT ... WHERE NOT coalesce(some_col, false);


13/60

Operators

Numeric Operators (+ - / * ^ % etc.)


14/60

Operators

String Operators Concatenation


15/60

Operators

String Comparisons =

!=

LIKE (with wildcard %)


16/60

Operators

Logical Operators AND

OR

NOT


17/60

Functions

There are a variety useful functions available in SQL

eg. log(x)

sin(x)

sqrt(x)

Other functions exist for manipulation of dates, stringsetc.

http://www.postgresql.org/docs/8.3/interactive/functions.html


18/60

Date/Time Functions

current_timestamp, current_date


19/60

Date/Time Functions

Converting dates to strings Actually a data type conversion function

to_char(datecolumn, dateformat)


20/60

Date/Time Functions

Converting strings to dates to_date(string,dateformat)

Useful for non-standard date formats and insertingdata from a text file


21/60

Aggregate functions

Aggregate functions gather records into a singleaggregate row.


22/60

Aggregate functions

The 'group by' clause allows you to refine youraggregate query


23/60

Aggregate functions

Other aggregate functions include: sum(columnname)


24/60

Aggregate functions

avg(columnname)


25/60

Distinct

DISTINCT a shortcut to unique results Not a function


26/60

Mathematical functions

Only operate on numerical data types Examples include:

log(x)

sin(x)

sqrt(x) trunc(x,s) returnsxwith any digits past s decimal

points truncated


27/60

trunc()

trunc(x,s) returnsxwith any digits past s decimalpoints truncated


28/60

Character String Functions

substr(s,n,l) returns sub-string of string s,starting at character n of length l

upper(column) converts string to uppercase lower(column) converts string to lowercase


29/60

Coalesce

Function available for replacing null values with anothervalue

coalesce(column,value)


30/60

Typecasting

You can change the data type of a field in a query Known as 'typecasting'


31/60

Conditional functions

You may want to print certain values in certain cases Use the 'CASE' function


32/60

Joins

Until now we have being querying single tables

Need to ask more complex questions acrossmultiple tables e.g. How many comedy videos hasTim Booth rented this month?

Power of relational databases is to join informationfrom related tables and answer such questions


33/60

Joins

Join tables by matching values from one table's rows tovalues in another table's rows


34/60

Join Example


35/60

Keys

The matching values between tables are known as keys Link tables by linking on keys

More theory tomorrow but for now...

Primary key value in first table

e.g. 'id' from customer table

Foreign keys matching values in second table

e.g. 'accountid' from rental table


36/60

Joins

Join creates a new relation from a combination of tables Basis of joins is the Cartesian product all possible

combinations of rows between joined tables


37/60

Joins


38/60

Joins

You need to tell database which values match thusmaking a more useful join than the cartesian product

l i i i SQ


39/60

Implementing Joins in SQL

Inner join

J i t


40/60

Join types

Two types joins are available

Inner join

Outer join


41/60

O t J i i SQL


42/60

Outer Joins in SQL

'LEFT OUTER JOIN' specifies which of the tables you wantto see all rows from

O t J i i SQL


43/60

Outer Joins in SQL

SQL m ltiple tables


44/60

SQL- multiple tables

Show customer names and the titles of the videos theyhave rented

SQL multiple tables


45/60


Join tables in succession 'customer' to 'rental' with id and accountid

SQL multiple tables


46/60


SQL multiple tables


47/60



SQL multiple tables


48/60


SQL multiple tables


49/60



SQL multiple tables


50/60


SQL multiple tables


51/60




52/60


Note that table name is required for ambiguous columns(e.g. video.id)

Can use table aliases instead

SELECT c.firstname, r.videoid

FROM customer AS c INNER JOIN rental AS r ON (c.id=r.accountid)

Useful for queries across multiple tables

Subqueries


53/60

Subqueries

'IN' and 'EXISTS' SELECT *

FROM table1

WHERE x IN (SELECT y FROM table2)

or:

SELECT *FROM table1

WHERE EXISTS (SELECT y FROM table2 WHERE y = x)

Subqueries


54/60

Subqueries

'IN'

Subqueries


55/60

Subqueries

'EXISTS'

Unions and Intersects


56/60

Unions and Intersects

UNION

INTERSECT

Query 1

Query 1

Query 2

Query 2

Unions


57/60

Unions

UNION allows appending of one query another

Number of columns and data types in each query mustmatch

query1 UNION query2

Duplicates are removed unless you use UNION ALL

Unions


58/60

Unions

Intersects


59/60

Intersects

INTERSECT shows all rows that are in both queries Number of columns and data types in each query must

match

query1 INTERSECT query2

Duplicates are removed unless you use INTERSECT ALL

Intersects


60/60

Intersects

02 advanced querying

Documents