INFO20003 Database Systems
Dr Renata Borovica-Gajic
Lecture 08 SQL
INFO20003 Database Systems © University of Melbourne 1
Copyright By PowCoder代写加微信 powcoder
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 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
INFO20003 Database Systems © Univers-it-y of Melbourne 15
Select Examples : Projection
In Relational Algebra:
CustLastName(Customer)
SELECT CustLastName FROM Customer;
NOTE: MySQL doesn’t 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 don’t 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
What’s 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代考加微信: powcoder QQ: 1823890830 Email: [email protected]
Reviews
There are no reviews yet.