SQL Lecture 1

1
SQL Lecture 1

• HIIB62
• Databases and Data Modelling
• Contributions by Arthur Adamopoulos, Vince Bruno,
  Hossein S. Zadeh, Ian Searle and Ian Storey

2
Structured Query Language

• A complete interface language for a database
  system.
• Data Definition
• Creating Defining files, fields etc.
• Data Manipulation
• Inserting, Updating Deleting records
• Data Extraction
• Getting data from the database (queries).

3
Structure of SQL Interface
User
Application
SQL Interface
Database
Data management engine
4
Relational Databases SQL
Table / Relation
Columns / fields
Num Surname Given Sex Mem
123 Smith Fred M Y
rows / tuples /
145 Smith Mary F Y
records
236 Nguyen Trung M N
378 Papadopo Harry M N
Values
Primary Key
5
Relational / SQL Database Rules

• No ordering or table joining information is
  stored(although relationships/constraints imply
  joins).
• All access to the database is through SQL. Both
  for users and programs!
• Multiple table operations involve joining tables
  on fly.

6
DB Tables
STUDENT
PROGRAMME
STUDENT_COURSE(enrol)
COURSE
7
SQL Tables - STUDENT PROGRAMME

• STUDENT
• Name Null? Type
• --------------------------- -------- ----
• STUDENT_NO NOT NULL CHAR(8)
• SURNAME VARCHAR2(30)
• GIVEN VARCHAR2(30)
• DOB DATE
• SEX CHAR(1)
• ADDR1 VARCHAR2(40)
• ADDR2 VARCHAR2(40)
• ADDR3 VARCHAR2(40)
• POSTCODE NUMERIC(4)
• PHONE VARCHAR2(20)
• PROGRAMME_CODE VARCHAR2(6)
• PROGRAMME_LOAD VARCHAR2(1)
• PROGRAMME
• Name Null? Type
• --------------------------- -------- ----

8
SQL Table - COURSE STUD_COURSE
COURSE Name Null? Type -------------------------
--- -------- ---- COURSE_CODE NOT NULL CHAR(5)
COURSE_NAME VARCHAR2(50) DEPT_NO NUMERIC
FEE NUMERIC(10,2) STUDENT_COURSE
Name Null? Type ---------------------------- ----
---- ---- STUDENT_NO NOT NULL CHAR(8)
COURSE_CODE NOT NULL CHAR(5) COURSE_PROGRAMME VA
RCHAR2(6) SEM_START NUMERIC(1)
SEM_DURATION NUMERIC(1) RESULT VARCHAR2(4)
9
Some Simple Queries

• SELECT SURNAME, GIVEN
• FROM STUDENT
• SELECT SURNAME, GIVEN, PROGRAMME_CODE
• FROM STUDENT

SURNAME GIVEN -----------------
-------------------- UNCHERNRAT ROSEMARY
ANN SCRIVENER ROBERT CRAWFORD R BULSOOK
JORGE
SURNAME GIVEN
PROGRAMME_CODE --------------- ------------------
-------------- UNCHERNRAT ROSEMARY ANN
167001 SCRIVENER ROBERT CRAWFORD
162010 BULSOOK JORGE
165701 FEKONJA TRUC TRUNG 165000
10
Some Simple Queries

• SELECT SEX
• FROM STUDENT

• SELECT DISTINCT SEX
• FROM STUDENT

SEX - M M F M F F
SEX - F M
11
Concatenation Queries

• SELECT SURNAME , GIVEN
• FROM STUDENT
• SELECT Student SURNAME, Programme
  PROGRAMME_CODE
• FROM STUDENT
• Note Concatenation can be with fields and/or
  string literals.

SURNAME', 'GIVEN -----------------------------
------ RAJOO, THINH MINH PHONGWATCHARARUK, JERZY
MIROSLAW CHOW, SOW LIM
STUDENT'SURNAME 'PROGRAMME'PRO --
--------------------------- --------------- Studen
t RAJOO Programme
167402 Student PHONGWATCHARARUK Programme
161005 Student CHOW Programme
165000
12
Simple Sorting Queries

• Ascending order
• SELECT SURNAME, GIVEN
• FROM STUDENT
• ORDER BY SURNAME

SURNAME GIVEN --------------
-------------------- ABELA VENUS ABERTON
SARAH JANE ALBERT ROSLYN
ISABEL ALLEN BARTHOLOMEUS DIAZ ALLURI
ZENO
13
Simple Sorting Queries

• Descending order
• SELECT SURNAME, GIVEN, PROGRAMME_CODE
• FROM STUDENT
• ORDER BY PROGRAMME_CODE DESC

SURNAME GIVEN
PROGRA -------------------- ------------------
------ MARTIN CLARE ALLISON
167730 RAJOO THINH MINH
167402 LAOHASAREEKUL GIN HOCK
167401 ALBERT ROSLYN ISABEL
167400 THOMAS CLAIRE JOY
167331
14
Multiple Sort Fields

• SELECT SURNAME, GIVEN
• FROM STUDENT
• ORDER BY SURNAME, GIVEN
• Rows are initially sorted by the first sort
  field.
• Those rows with the same value in the first sort
  field are then sorted between themselves using
  the second sort field.

SURNAME GIVEN -------------
---------------- ABELA VENUS ABERTON
SARAH JANE ABERTON ZENO ALLEN
BARTHOLOMEUS ALLEN PETER ALLEN
STEPHEN GRAHAM
15
Change Column Headings

• SELECT SURNAME AS Last Name, DOB AS Date
  Birth
• FROM STUDENT
• SELECT SURNAME , GIVEN AS Full Name
• FROM STUDENT
• ORDER BY Full Name
• SELECT SURNAME , GIVEN AS Full Name,
  SEX AS Gender, DOB
• FROM STUDENT
• ORDER BY 2

Last Name Date
Birth ------------------------------
---------- RAJOO
01-JUL-73 PHONGWATCHARARUK
01-JAN-73 CHOW 02-DEC-74
16
SELECT WHERE

• In order to select specific records from the
  resulting output of a SELECT clause you can do
  this with WHERE.
• SELECT SURNAME, GIVEN, SEX, PROGRAMME_CODE, DOB
• FROM STUDENT
• WHERE PROGRAMME_CODE 165000

SURNAME GIVEN S PROGRA
DOB ------------- ----------------- - ------
--------- CHOW SOW LIM F
165000 02-DEC-74 ALLURI ZENO
M 165000 24-SEP-74 DWYER ANDREW
ANTHONY M 165000 08-OCT-74
17
Selection Criteria

• All the usual comparison operators.
• ie. lt gt lt gt ltgt
• Usual compound operators.
• ie. AND OR and NOT
• Brackets can be used
• NOT(((cond1) AND (cond2)) OR cond3)

18
SELECT WHERE

• SELECT SURNAME, GIVEN, SEX, PROGRAMME_CODE, DOB
• FROM STUDENT
• WHERE SEX M AND POSTCODE 3000
• SELECT SURNAME, GIVEN, SEX, PROGRAMME_CODE, DOB
• FROM STUDENT
• WHERE NOT(SEX M)

SURNAME GIVEN S PROGRA
DOB ---------- ----------- - ------
--------- TSUNG KOK WAH M 166701
06-JUN-72
SURNAME GIVEN S PROGRA
DOB ----------------- ----------------- - ------
--------- CHOW SOW LIM F
165000 02-DEC-74 SAMARAWICKRAMA JANE
F 162010 06-FEB-70 GILTRAP
ELIZABETH JANE F 164002 15-SEP-72
19
Wildcards
SURNAME ------------- SMITH SANDERS SOMERS

• SELECT SURNAME
• FROM STUDENT
• WHERE SURNAME LIKE S'
• SELECT SURNAME
• FROM STUDENT
• WHERE SURNAME LIKE TH'
• SELECT SURNAME
• FROM STUDENT
• WHERE SURNAME LIKE 'ON'

SURNAME ------------- COATH SMITH SMITH
SURNAME ----------------- PHONGWATCHARARUK WHEATON
ONG
20
Selection Criteria Shortcuts

• SELECT SURNAME, POSTCODE
• FROM STUDENT
• WHERE POSTCODE BETWEEN 3000 AND 4000
• SELECT SURNAME
• FROM STUDENT
• WHERE SURNAME IN ('JONES', 'SMITH', 'ADAMS')
• SELECT SURNAME, PROGRAMME_CODE
• FROM STUDENT
• WHERE PROGRAMME_CODE IN (165000, 161000, 164000)

SURNAME PROGRA ------------- ------ CHOW
165000 ALLURI 165000 DWYER
164000
21
NULL operator

• A field is NULL if it is empty.
• NULL is different to an empty string!
• NOT version also possible
• SELECT SURNAME, GIVEN
• FROM STUDENT
• WHERE PROGRAMME_CODE IS NULL

22
Query Calculations

• Queries can use arithmetic calculations and built
  in functions.
• SELECT Fee AUD FEE, Fee US FEE 2.2
• FROM COURSE
• SELECT INVOICEID, PRODNAME, QTY UNITPRICE
• FROM SALE
• SELECT SURNAME, ((SYSDATE-DOB)/365.25)
• FROM STUDENT

SURNAME ((SYSDATE-DOB)/365.25) --------
---------- ---------------------- RAJOO
28.426404 PHONGWATCHARARUK
28.921955 CHOW
27.0054594
23
System Variables

• System variables can be used in place of field
  names.
• SYSDATE - Current System Date
• SELECT SURNAME, FLOOR((SYSDATE-DOB)/365)
• FROM STUDENT

SURNAME FLOOR((SYSDATE-DOB)/365.25) ---
--------------- --------------------------- RAJOO

28 PHONGWATCHARARUK
28 CHOW 27
24
Scalar Functions

• Functions that return a single value
• Usable where you would normally use a value.
• Normally used within the column definitions or
  within where clauses etc.

25
Scalar Functions

• DECODE(TARGET,VAL1,RES1,VAL2,RES2...)
• Eg DECODE(SEX, M,Male,F,Female)
• FLOOR
• Remove the fraction part of a number
• INITCAP
• Make first letter upper and rest lower
• LOWER
• Lower case all characters.
• LTRIM
• Left Trim. Remove leading spaces.

26
Scalar Functions

• NVL(TARGET,REPLACEVAL)
• Null value. Replace any nulls with a message.
• RTRIM
• Right Trim. Remove trailing spaces.
• SOUNDEX
• Creates a code for the the way the value sounds.
• UPPER
• Upper Case all characters.

27
Formatting Dates

• TO_CHAR Function used to display dates.
  TO_CHAR(FIELD,FORMAT)
• The format string can be made up of many
  formatting codes that you can look up on the
  on-line documentation (Format Models in TO_CHAR)
• Examples
• SELECT TO_CHAR(DOB,DD/MM/YY)
• SELECT TO_CHAR(DOB,DD Month, YYYY)
• SELECT TO_CHAR(DOB,DAY)
• SELECT TO_CHAR(DOB,CC) Century

28
Statistical Functions

• There are five basic statistical functions.
• They are also known as grouping functions.
• All statistical functions return one value only,
  no matter how many rows they operate on.
• When they are used, values of individual rows
  cannot be displayed.
• Can be used with usual where clauses.

29
Statistical Functions - COUNT

• COUNT
• Counts number of rows
• SELECT COUNT()
• FROM COURSE
• SELECT COUNT()
• FROM STUDENT
• WHERE SURNAME 'SMITH'

COUNT() --------- 4469
COUNT() --------- 2
30
Statistical Functions - SUM

• SUM
• Add up values in a specified column for all
  selected rows.
• SELECT SUM(FEE)
• FROM COURSE
• WHERE DEPTNO 100
• Result would be a single value of all the fees in
  the selected rows added up.

SUM(FEE) ----------- 100,234.00
31
Statistical FunctionsAVG, MAX, MIN

• AVG
• Average of all values in a specific column.
• MAX
• Highest value found for a specific column in all
  selected rows.
• MIN
• Lowest value found for a specific column in all
  selected rows.

32
Grouping Data - GROUP BY

• Also known as break reports.
• A grouping field is selected to group the rows
• The rows are sorted by the grouping field.
• Rows with the same value for the grouping field
  are treated as a group.
• Usually a statistical function is also used and
  applied to each group (eg SUM).

33
Grouping Example

• The Query
• SELECT PROGRAMME_CODE, COUNT()
• FROM STUDENT
• GROUP BY PROGRAMME_CODE

34
Grouping Example Raw Data
35
Grouping Example Sorted
36
Grouping Example Break Points
37
Grouping Example Result
38
GROUP BY Extra fields

• When GROUP BY is used, the only fields that can
  be displayed are the grouping field and
  statistical functions.
• Values for individual rows cannot be displayed.
• If you want to display other fields, a trick is
  to include the extra field as a secondary
  grouping field in the GROUP BY clause.

39
Grouping Data HAVING

• The HAVING clause operates like a WHERE clause,
  but is applied to the grouping value.
• WHERE is applied to each row before the grouping
  operation is done.
• HAVING is applied after the grouping is performed
  and operates on the calculated grouping value
  (before it is displayed).

40
Grouping Data HAVING

• SELECT STUDENT_NO, COUNT()
• FROM STUD_COURSE
• GROUP BY STUDENT_NO
• HAVING COUNT() gt 2

These areselectedand displayed
41
COUNT simple

• The COUNT() function returns the number of
  selected rows in a selection.
• With "Persons" Table (on right)
• SELECT COUNT() FROM Persons
• Result 5
• SELECT COUNT() FROM Persons WHERE Agegt20
• Result 2

42
COUNT Column and NULL

• The COUNT(column) function returns the number of
  rows without a NULL value in the specified
  column.
• This example finds the number of persons with a
  value in the "Age" field in the "Persons" table
• SELECT COUNT(Age) FROM Persons
• Result 3
• The COUNT(column) function is handy for finding
  columns without a value. Note that the result is
  one less than the number of rows in the original
  table because one of the persons does not have an
  age value stored.

43
COUNT and DISTINCT

• COUNT DISTINCT The keyword COUNT and DISTINCT can
  be used together to count the number of distinct
  results.
• Example Count Distinct Companies For this
  "Orders" Table (on right)
• SELECT COUNT(Company) FROM Orders
• Result 6
• SELECT COUNT(DISTINCT Company) FROM Orders
• Result 3