SQL

Structured Query Language

sql
standard db language for relational databases

SQL consists of two core functions

Data Definition Language (DDL)
Data Manipulation Language (DML)

Data Manipulation Language (DML)

for manipulating and querying the data contained in the database, i.e., populating, reading, and updating the data in the tables.

Data Definition Language (DDL)

for defining and creating the database objects.

A Selection of Data Types

Never Use Float or Double for Money!

They are approximate (inexact) types.

May result in discrepancies! Rounding errors.

use numeric type or the DBMS’s monetary type

Logical Model to Physical Model - ddata types example

Naming Conventions

be consistent
popular recommendation is using snake_case in SQL
some dbms convert data objects names to lowercase automatically. use a snake_case is better for readability.

Creating Tables with SQL - Defining Columns (Attributes) in a Table

You define an attribute (column) for a table by specifying:

column_name: the name of the attribute in the table

data_type: the type of data the attribute can store

size: maximum length of the data it can store (optional)

Is the order in which you create the tables important?

Yes, parent tables before child tables (tables with FKs).

Creating Tables - But We Also Had Constraints!

Constraints

Rules formulated by the database designer may contain column or table constraints to prevent:

Accidental damage to the database

Changes that may cause data inconsistency

Many constraints can be specified using clauses in the CREATE TABLE statement.

Some may have to be specified using database procedures or rules

Types of Constraints

Mandatory data

Entity integrity

Referential integrity

Column value constraints

Enterprise constraints

mandatory data - constraints

NOT NULL

entity integrity constraints

PRIMARY KEY

Referential integrity - constraints

FOREIGN KEY

Column value constraints

CHECK, UNIQUE, DEFAULT

Referential Integrity Rule (1)

Foreign keys establish relationships between tables.

Referential integrity rule:

Value of a foreign key must either match an existing value of the primary key it refers to or be null.

Adding Foreign Keys: Example

referential integrity (2)

It is important to enforce referential integrity in a relational database to ensure data consistency -No dangling reference in a ‘child’ table

Deletion of a record in the ‘parent’ table is not allowed if there exists any referenced records in the ‘child’ table.

referential integrity (3)

The effects of amendments to the ‘parent’ table depend on actions specified in the REFERENCES clause of the ‘child’ table when creating the foreign key.

By default: Any DELETION of a record in the parent table is not allowed if there exists any referenced child records in the child table.

You can change the default behaviour.

Cascading Referential Integrity - options

cascade
set null
set default

referential integrity option - cascade

Deleting a record in the ‘parent’ table automatically deletes all referenced records in ‘child’ tables.

referential integrity option - set null

Deleting a record in the ‘parent’ table automatically sets the foreign key value of all referenced ‘child’ records to NULL. Only works if the FK column allows nulls.

referential integrity option - set default

Deleting a record in the ‘parent’ table automatically sets the foreign key value of all referenced ‘child’ records to a default value.

SQL provides four statements for data manipulation.

Data manipulation involves:

insert - adding data to tables
update - modifying data in tables
delete - deleting data from tables
select - retrieving data from tables

INSERT Statement

INSERT INTO TableName [(column1, column2, column3, …)]

VALUES (dataValue1, dataValue2, dataValue3, ...);

UPDATE Statement

UPDATE TableName
SET columnName1 = newDataValue1 [, columnName2 = newDataValue2…]
[WHERE searchCondition]

DELETE Statement

DELETE FROM TableName

[WHERE searchCondition]

aggregate functions

Aggregate functions combine data and derive summary information
function is applied to selected col
Aggregate_Function(Column_Name)

type of aggregate functions

count,sum,avg,min,max
these functions use column expression as an argument
COUNT may also take a row – COUNT(*)
ignore null vals
return single val

Aggregate Functions: Examples

Distinct

used to get a unique count
distinct doesn’t work with COUNT(*)

Aggregate Functions: Group By

specifies attributes to group the records by
Summarising data by groups.

The grouping attribute(s) are usually included as part of the results, but they can be omitted.

Group By Clause: Notes

each item in select list must be single val per group
means the result of a group by can only contain:
The grouping attributes

Constants (values that do not change)

Aggregate function results (from COUNT, MIN, MAX, SUM, AVG)

Group By and Having Clause

The HAVING clause is used with the GROUP BY clause to reduce the groups that appear in the query result.

similar to WHERE but WHERE filters induvidual rows and HAVING filters groups

Step 1: FROM … WHERE

Step 2: GROUP BY

Step 3: SELECT … COUNT

Step 4: HAVING

Step 5: ORDER BY

refer to slides wk5 14-21

Nested Queries (or Subqueries)

results is a table
queries can be nested in another query
results of a subq can be assigned an alias name

A subquery can be used as a normal table in a query, e.g.:

A single column in SELECT

A table in FROM

A table in WHERE

Subquery in SELECT Clause

Subqueries: EXISTS and NOT EXISTS

used for subq
produce simple true or false results

subq exists

True, if and only if there exists at least one row in the result returned by the subquery.

False, if subquery returns an empty result – zero rows

subq not exist

False, if there exists at least one row in the result returned by the subquery.

True, if subquery returns an empty result – zero rows.

Subqueries in WHERE Clause Using EXISTS

PostgreSQL Date Operations

Date Operations: Example Query

Avoiding Complex Queries WITH Clause

assign an alias name to a temp query results
The named subquery can then be used anywhere in the main query as a temporary table.

WITH alias_name

AS (subquery statement)

main query statement;

WITH Clause: Example

WINDOW Function

further simplify the with clause by using a window func OVER clause

SELECT Module_Code, Student_ID, Coursework_Mark,

AVG(Coursework_Mark) OVER (PARTITION BY Module_Code) AS Avg_Mark

FROM Registration;

this func performs the calc for each row in the window
similar calc to the aggregated but no row grouping into a single summary row

has effect of grouping while retaining each row individually in the results.

type of set operators

union
intersect
except

these ops automatically elimiates duplicates
to apply these ops tables must union compatible

dbms provides

Transaction support

Concurrency control services

Recovery services

it ensures that the db is reliable and remain in a consisten state

union compatible relation: defenition

two table are union compatible if:
ther have the same degree(n-no of cols) and domain(A_i) = domain(B_i)

so compatible means same no of cols and cols have same data type

set op example

Queries on Multiple Tables: Aliases

SQL permits columns to have the same name provided they are in different tables

Two ways to remove any ambiguity in a multi-table query result:
-Prefix the attribute (column) with the required table name: staff.staffID
-Use aliases to simplify or shorten the length of the query. Common practice is to use the first letter of the table name: s.staffID

Queries on Multiple Tables: Steps

example wk5 l2 slide 13-16 ,

types of JOINs

JOIN clause is used to retrieve rows from two tables based on the linkage condition

JOIN( inner) - by default returns all rows that have a match in both tables
LEFT JOIN
RIGHT JOIN
FULL JOIN

LEFT JOIN

returns all rows from the left (first) table and the matched ones from the right (second) table. NULLs are returned for columns in the right side when there is no match

RIGHT JOIN

returns all rows from the right table and the matched ones from the left table. NULLs are returned for columns in the left side when there is no match.

FULL JOIN

returns all rows from both tables whether there is a match or not.

INNER JOIN

Queries on Multiple Tables: Example

19-25

Queries on Multiple Tables: Tip

To join 3 tables, you need two linkage conditions, i.e., 2 joins

To join 2 tables, you need one linkage condition, i.e., 1 join

one table:
-it depends
- if a self join is required: table has a relationship with itself and this need to be shown in query reults use a self join
- otherwise no join needed

self join query

table join to its self
useful for unary relationships - relationship with itself
or useful for checking if a row shares an attributes value with another row
may be viewed as a join of two copies of same table
atleast n copy must have alias name

Self-JOIN Queries: Example 1

check wk5 l2 slide 28 , 30-31

what are views

base tables - Tables created with CREATE TABLE are stored in the database

a view is a virtual table:
-A logical window on data from one or more tables.
-No data is retrieved when a view is created – simply stores the query definition.
-The ‘virtual table’ defined by a view is recreated each time the view is referenced.

views are dynamic: changes made to the base tables that affect the view are immediately reflected in the view.

Views: Example 1

Why Views

provide data independence from concrete data structure: if table structure is changed and not needed by view then view doesn’t change
allows users to customise their views of the base tabe appropriate for their needs
icrese security by restricting access to the db: proves powerful and flexible security mechanise by hiding part of the db from certan user
simplifies complex queries on the base table

Views uses

a view maybe used to filter or combine

filter - view use

restrict access to selected row or col of one or more table or views
horizontal view: restrict the users access to selected rows
vertical view: restricts a user’s access to selected cols
horizontal and vertical can be used in combo

combine -view use

simply a complex query
de-normalise - join tables split up during normalisation

Views: Horizontal Views

Restricts access to selected rows from one or more tables, or views.
use where clause

Views: Vertical Views

Restrict access to selected columns from one or more tables, or views.

view example

refer to wk5 l2 slides 40-41