[SOLVED] CS INFO20003 Database Systems

INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 08 SQL
INFO20003 Database Systems University of Melbourne 1

Copyright By Assignmentchef assignmentchef

What is SQL
SQL or SEQUEL is a language used in relational databases
DBMS support CRUD
Create, Read, Update, Delete commands
SQL supports CRUD
Create, Select, Update, Delete commands
Other info
You can see the 2011 standard of SQL at
http://www.jtc1sc32.org/doc/N2151-2200/32N2153T-text_for_ballot- FDIS_9075-1.pdf
Wikipedia has several sections on SQL (good for generic syntax)
http://en.wikipedia.org/wiki/Category:SQL_keywords
INFO20003 Database Systems Univers-it-y of Melbourne 4

SQL Language
Provides the following capabilities:
Data Definition Language (DDL)
Todefineandsetupthedatabase
CREATE, ALTER, DROP
Data Manipulation Language (DML)
To maintain and use the database
SELECT, INSERT, DELETE, UPDATE
Data Control Language (DCL)
Tocontrolaccesstothedatabase
GRANT, REVOKE
Other Commands
Administerthedatabase TransactionControl
INFO20003 Database Systems Univers-it-y of Melbourne 5

How We Use SQL
In Implementation of the database
Take the tables we design in physical design
Implement these tables in the database using create commands
In Use of the database
Use Select commands to read the data from the tables, link the
tables together etc
Use alter, drop commands to update the database
Use insert, update, delete commands to change data in the database
INFO20003 Database Systems Univers-it-y of Melbourne 6

SQL Context in Development Process
INFO20003 Database Systems Univers-it-y of Melbourne 7

Create Table: Review
INFO20003 Database Systems Univers-it-y of Melbourne 8

Foreign keys: Review
We looked at Customer
A customer can have a number of Accounts The tables get linked through a foreign key
Customer FirstName
CustMiddle Name
CustLast Name
Business Name

JJ Enterprises
AccountName
Outstanding Balance

JJ Ent. Mgr
INFO20003 Database Systems Univers-it-y of Melbourne 9

SQL CREATE Statement (With FK)
INFO20003 Database Systems Univers-it-y of Melbourne 10

Insert Data
Specifies which columns will be entered
No column specification means ALL columns need to be entered
CustomerFirst Name
CustMiddle Name
CustLastName
BusinessName
JJ Enterprises
INFO20003 Database Systems Univers-it-y of Melbourne 11

What does NULL mean?
Null Island: The Busiest Place That Doesnt Exist:

by the channel MinuteEarth
INFO20003 Database Systems Univers-it-y of Melbourne 12

Query Table with SELECT statement
Select statement allows us to query table(s)
* (star): Allows us to obtain all columns from a table
All columns
INFO20003 Database Systems Univers-it-y of Melbourne 13

The SELECT Statement: Detail
A cut down version of the SELECT statement MySQL
SELECT [ALL | DISTINCT] select_expr [, select_expr ]
List the columns (and expressions) that are returned from the query
[FROM table_references ]
Indicate the table(s) or view(s) from where the data is obtained
[WHERE where_condition]
Indicate the conditions on whether a particular row will be in the result
[GROUP BY {col_name | expr } [ASC | DESC], ] Indicatecategorisationofresults
[HAVING where_condition]
Indicate the conditions under which a particular category (group) is included in
the result
[ORDER BY {col_name | expr | position} [ASC | DESC], ] Sort the result based on the criteria
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
Limit which rows are returned by their return order (ie 5 rows, 5 rows from row 2)
Order is important! E.g. Limit cannot go before Group By or Having
INFO20003 Database Systems Univers-it-y of Melbourne 14

Select Examples
SELECT * FROM Customer;
= Give me all information you have about customers
INFO20003 Database Systems Univers-it-y of Melbourne 15

Select Examples : Projection
In Relational Algebra:
CustLastName(Customer)
SELECT CustLastName FROM Customer;
NOTE: MySQL doesnt discard duplicates. To remove them use DISTINCT in front of the projection list.
INFO20003 Database Systems Univers-it-y of Melbourne 16

Select Examples: Selection
In Relational Algebra:
In Relational Algebra:
CustLastName(CustLastName=Smith(Customer))
SELECT CustLastName
FROM Customer
WHERE CustLastName = Smith;
cond1cond2cond3(Rel)
WHERE cond1 AND cond2 OR cond3
INFO20003 Database Systems Univers-it-y of Melbourne 17

Select Examples: LIKE clause
In addition to arithmetic expressions, string conditions are specified with the LIKE clause
LIKE REG_EXP
% Represents zero, one, or multiple characters _ Represents a single character
Examples: SQL:
WHERE CustomerName LIKE a%
Finds any values that start with a
WHERE CustomerName LIKE %a
Finds any values that end with a
WHERE CustomerName LIKE %or%
Finds any values that have or in any position
WHERE CustomerName LIKE _r%
Finds any values that have r in the second position
WHERE CustomerName LIKE a_%_%
Finds any values that start with a and are at least 3 characters in length
WHERE ContactName LIKE a%o
Finds any values that start with a and end with o
INFO20003 Database Systems Univers-it-y of Melbourne 18

Column renaming
We can rename the column name of the output by using the AS clause
INFO20003 Database Systems Univers-it-y of Melbourne 19

Aggregate Functions
Aggregate functions operate on the (sub)set of values in a column of a relation (table) and return a single value
Average value
Minimum value
Maximum value
Plus others
Number of values
Sum of values
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html
All of these except for COUNT(*) ignore null values and return null if all values are null. COUNT(*) counts the number of records.
INFO20003 Database Systems Univers-it-y of Melbourne 20

Aggregate Examples: Count/AVG
COUNT() returns the number of records AVG() average of the values
SELECT COUNT(CustomerID) FROM Customer;
SELECT AVG(OutstandingBalance) FROM Account;
SELECT AVG(OutstandingBalance) FROM Account
WHERE CustomerID= 1;
SELECT AVG(OutstandingBalance) FROM Account
GROUP BY CustomerID;
= How many customers do we have (cardinality)
= What is the average balance of ALL ACCOUNTS
= What is the average balance of Accounts of Customer 1
= What is the average balance
PER CUSTOMER
INFO20003 Database Systems University of Melbourne 21

GROUP BY clause
Group by groups all records together over a set of attributes Frequently used with aggregate functions
Example:
What is the average balance PER CUSTOMER
SELECT AVG(OutstandingBalance) FROM Account
GROUP BY CustomerID;
Returns one record per each customer
INFO20003 Database Systems University of Melbourne 22

HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions
SELECT column_name(s) FROM table_name
WHERE condition
GROUP BY column_name(s) HAVING condition
ORDER BY column_name(s);
Example:
List the number of customers of each country, but ONLY include countries with more than 5 customers
SELECT COUNT(CustomerID), CountryName
FROM Customers
GROUP BY CountryName
HAVING COUNT(CustomerID) > 5;
Condition over the aggregate
INFO20003 Database Systems University of Melbourne 23

ORDER BY Clause
Orders records by particular column(s)
ORDER BY XXX ASC/DESC (ASC is default)
INFO20003 Database Systems Univers-it-y of Melbourne 24

Limit and Offset
LIMIT number limits the output size
OFFSET number skips first number records
INFO20003 Database Systems Univers-it-y of Melbourne 25

Joining tables together
SELECT * FROM Rel1, Rel2; this is a cross product
Not quite useful
Typically we would like to find:
For every record in the Customer table list every record in the Account table
INFO20003 Database Systems Univers-it-y of Melbourne 26

Joins: Different Types
Inner/Equi join:
Joins the tables over keys
Natural Join:
Joins the tables over keys. The condition does not have to be specified (natural join does it automatically), but key attributes have to have the same name.
INFO20003 Database Systems Univers-it-y of Melbourne 27

Joins: Different Types
Outer join:
Joins the tables over keys
Can be left or right (see difference below)
Includes records that dont match the join from the other table
INFO20003 Database Systems Univers-it-y of Melbourne 28

JOINS depicted as
T1.ID T2.ID
INFO20003 Database Systems Univers-it-y of Melbourne 29

JOINS depicted as
T1 INNER JOIN T2 ON T1.ID = T2.ID
T1 NATURAL JOIN T2
INFO20003 Database Systems Univers-it-y of Melbourne 30

JOINS depicted as
T1 LEFT OUTER JOIN T2 ON T1.ID = T2.ID
T1.ID T2.ID
INFO20003 Database Systems Univers-it-y of Melbourne 31

JOINS depicted as
T1 RIGHT OUTER JOIN T2 ON T1.ID = T2.ID
T1.ID T2.ID
INFO20003 Database Systems Univers-it-y of Melbourne 32

JOINS depicted as
T1 FULL OUTER JOIN T2 ON T1.ID = T2.ID
T1.ID T2.ID
INFO20003 Database Systems Univers-it-y of Melbourne 33

Whats examinable
You need to know how to write SQL
INFO20003 Database Systems University of Melbourne 34

Next Lecture
SQL Summary
Overview of concepts, more examples
INFO20003 Database Systems Univers-it-y of Melbourne 35

CS: assignmentchef QQ: 1823890830 Email: [email protected]