程序代写代做代考 database SQL INFO20003 Database Systems

INFO20003 Database Systems

INFO20003 Database Systems 1© University of Melbourne 2018

INFO20003 Database Systems

Lecture 08

SQL

Semester 2 2018, Week 4

Dr Renata Borovica-Gajic

INFO20003 Database Systems 3© University of Melbourne 2018

Relational algebra: Practice at home

1. Find (the name of) all sailors whose rating is above 9

2. Find all sailors who reserved a boat prior to

November 1, 1996

3. Find (the names of) all boats that have been reserved

at least once

4. Find all pairs of sailors with the same rating

INFO20003 Database Systems 4© University of Melbourne 2018

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

http://www.jtc1sc32.org/doc/N2151-2200/32N2153T-text_for_ballot-FDIS_9075-1.pdf
http://en.wikipedia.org/wiki/Category:SQL_keywords

INFO20003 Database Systems 5© University of Melbourne 2018

SQL Language

– –

• Provides the following capabilities:

– Data Definition Language (DDL)

• To define and set up the database

• CREATE, ALTER, DROP

– Data Manipulation Language (DML)

• To maintain and use the database

• SELECT, INSERT, DELETE, UPDATE

– Data Control Language (DCL)

• To control access to the database

• GRANT, REVOKE

– Other Commands

• Administer the database

• Transaction Control

INFO20003 Database Systems 6© University of Melbourne 2018

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 7© University of Melbourne 2018

SQL Context in Development Process

– –

INFO20003 Database Systems 8© University of Melbourne 2018

Create Table: Review

– –

INFO20003 Database Systems 9© University of Melbourne 2018

Foreign keys: Review

– –

• We looked at Customer

– A customer can have a number of Accounts

– The tables get linked through a foreign key

CustID Customer

FirstName

CustMiddle

Name

CustLast

Name

Business

Name

CustType

1 Peter Smith Personal

2 James Jones JJ

Enterprises

Company

AccountID AccountName Outstanding

Balance

CustID

01 Peter Smith 245.25 1

05 JJ Ent. 552.39 2

06 JJ Ent. Mgr 10.25 2

INFO20003 Database Systems 10© University of Melbourne 2018

SQL CREATE Statement (With FK)

– –

;

INFO20003 Database Systems 11© University of Melbourne 2018

Insert Data

– –

CustID CustomerFirst

Name

CustMiddle

Name

CustLastName BusinessName CustType

1 Peter NULL Smith NULL Personal

2 James NULL Jones JJ Enterprises Company

3 NULL Smythe Company

Customer

No column specification means

ALL columns need to be entered

Specifies which columns

will be entered1)

INFO20003 Database Systems 12© University of Melbourne 2018- –

What does NULL mean?

Null Island: The Busiest Place That Doesn’t Exist:

by the channel MinuteEarth

INFO20003 Database Systems 13© University of Melbourne 2018

Query Table with SELECT statement

– –

All columns

• Select statement allows us to query table(s)

* (star): Allows us to obtain all columns from a table

INFO20003 Database Systems 14© University of Melbourne 2018

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], …]
– Indicate categorisation of results

• [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 15© University of Melbourne 2018

Select Examples

– –

SQL

RESULT

SELECT * FROM Customer;

= Give me all information you have about customers

INFO20003 Database Systems 16© University of Melbourne 2018

Select Examples : Projection

– –

SQL

In Relational Algebra:

)(Customer
meCustLastNa



In SQL:

SELECT CustLastName

FROM Customer;

Result

NOTE: MySQL doesn’t discard duplicates.

To remove them use DISTINCT in front of

the projection list.

INFO20003 Database Systems 17© University of Melbourne 2018

Select Examples: Selection

– –

SQL

Result

In Relational Algebra:

In SQL:

SELECT CustLastName

FROM Customer

WHERE CustLastName = “Smith”;

))(
“”

( Customer
SmithmeCustLastNameCustLastNa 



In SQL:

WHERE cond1 AND cond2

OR cond3

In Relational Algebra:
)(Re

321
l

condcondcond 


INFO20003 Database Systems 18© University of Melbourne 2018

Select Examples: LIKE clause

– –

LIKE “REG_EXP”
% Represents zero, one, or multiple characters

_ Represents a single character

Result

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”

Examples: SQL:

• In addition to arithmetic expressions, string conditions are specified

with the LIKE clause

INFO20003 Database Systems 19© University of Melbourne 2018

Aggregate Functions

– –

• AVG()

– Average value

• MIN()

– Minimum value

• MAX()

– Maximum value

• COUNT()

– Number of values

• SUM()

– Sum of values

Aggregate functions operate on the (sub)set of values in a column

of a relation (table) and return a single value

• Plus others

– 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 rows not the

values and thus even if the value is NULL it is still counted.

http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html

Aggregate Examples: Count/AVG

COUNT() – returns the number of records

AVG() – average of the values

Examples:

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

Group by / Having

• 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;

• The only way to put a selection condition over a group by statement

is by using having clause

• Example:
What is the exact average balance per customer for customers whose

average balance is over 10000

SELECT AVG(OutstandingBalance)

FROM Account

GROUP BY CustomerID

HAVING AVG(OutstandingBalance) > 10000

Column renaming

– –

We can rename the column

name of the output by using

the AS clause

Order by

– –

SQL

RESULT

ORDER BY XXX ASC/DESC (ASC is default)

• Orders records by particular column(s)

Limit and Offset

– –

• LIMIT number – limits the output size

• OFFSET number – skips first ‘number’ records

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

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.

CONDITION

Joins: Different Types

– –

• Outer join:

– Joins the tables over keys

– Can be left or right (see difference below)

– Includes records that don’t match the join from the other table

What’s examinable

• You need to know how to write SQL

–DDL

–DML

Next Lecture

– –

• SQL Summary

‒ Overview of concepts, more examples

Related Posts