程序代写代做代考 html go database INFO20003 Database Systems

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

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
1.
2.
3.
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
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 © Univers-it-y of Melbourne 9

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

Insert Data
1)
Specifies which columns will be entered
2)
Customer
No column specification means ALL columns need to be entered
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
INFO20003 Database Systems © Univers-it-y of Melbourne 11

What does NULL mean?
Null Island: The Busiest Place That Doesn’t 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
SQL
RESULT
INFO20003 Database Systems © Univers-it-y of Melbourne 15

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

Select Examples: Selection
In Relational Algebra:
In Relational Algebra:
CustLastName(CustLastName=”Smith”(Customer))
In SQL:
SELECT CustLastName
FROM Customer
WHERE CustLastName = “Smith”;
SQL
cond1cond2cond3(Rel)
In SQL:
WHERE cond1 AND cond2 OR cond3
Result
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”
Result
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
• AVG()
– Average value
• MIN()
– Minimum value
• MAX()
– Maximum value
• Plus others
• COUNT()
– Number of values
• SUM()
– 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
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
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)
SQL
RESULT
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
CONDITION
• 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 don’t match the join from the other table
INFO20003 Database Systems © Univers-it-y of Melbourne 28

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

JOINS depicted as Venn Diagrams
• 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 Venn Diagrams
• 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 Venn Diagrams
• T1 FULL OUTER JOIN T2 ON T1.ID = T2.ID
T1.ID T2.ID
✅✅✅
INFO20003 Database Systems © Univers-it-y of Melbourne 33

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

Related Posts