MONASH
INFORMATION TECHNOLOGY
Structured Query Language (SQL) – Part 1
Anatomy of an SQL SELECT Statement
SELECT stud_nbr, stu_fname, stu_lname FROM student
WHERE stu_fname = ‘Maria’;
clauses
statement
Predicate / search condition
2
2
What table(s) the data come from?
SQL SELECT Statement – Usage
What column/s to display
SELECT stud_nbr, stu_fname, stu_lname FROM student
WHERE stu_fname = ‘Maria’;
What row/s to retrieve – the RESTRICTION on the select
3
3
SQL Predicates or Search Conditions
▪ The search conditions are applied on each row, and the row is returned if the search conditions are evaluated to be TRUE for that row.
▪ Comparison
– Compare the value of one expression to the value of another expression. – Operators: =, !=,< >, <, >, <=, >=
– Example: salary > 5000
▪ Range
– Test whether the value of an expression falls within a specified range of
values.
– Operator: BETWEEN
– Example: salary BETWEEN 1000 AND 3000 (both are inclusive)
4
4
SQL Predicates or Search Conditions
▪ Set Membership
– To test whether the value of expression equals one of a set of values. – Operator: IN
– Example : city IN (‘Melbourne’, ‘Sydney’)
▪ Pattern Match
– To test whether a string (text) matches a specified pattern. – Operator: LIKE
– Patterns:
• % character represents any sequence of zero or more character.
• _ character represents any single character. – Example:
• WHERE city LIKE ‘M%’
• WHERE unit_code LIKE ‘FIT20__’
5
5
SQL Predicates or Search Conditions
▪ NULL
– To test whether a column has a NULL (unknown) value. – Example: WHERE grade IS NULL.
▪ Use in subquery (to be discussed in the future) – ANY, ALL
– EXISTS
6
6
What row will be retrieved?
▪ Predicate evaluation is done using three-valued logic. – TRUE, FALSE and UNKNOWN
▪ DBMS will evaluate the predicate against each row. ▪ Row that is evaluated to be TRUE will be retrieved. ▪ NULL is considered to be UNKNOWN.
7
7
8
8
Q1. Consider the predicate “enrol_mark >= 50”, what row(s) will be selected for this predicate by the DBMS?
a. b. c. d.
1, 4 and 6 All rows
1 and 6
All rows except row 4
Combining Predicates
▪ Logical operators – AND, OR, NOT
▪ Rules:
– An expression is evaluated LEFT to RIGHT
– Sub-expression in brackets are evaluated first – NOTs are evaluated before AND and OR
– ANDs are evaluated before OR
– Use of BRACKETS better alternative
9
9
Truth Table
• AND is evaluated to be TRUE if and only if both conditions are TRUE
• OR is evaluated to be TRUE if and only if at least one of the conditions
is TRUE AND
A
B
T
U
F
T
T
U
F
U
U
U
F
F
F
F
F
T = TRUE
F = FALSE
U = Unknown
OR
A
B
T
U
F
T
T
T
T
U
T
U
U
F
T
U
F
Unknown = NULL in relational database
10 10
11 11
Q2. What row will be retrieved when the WHERE clause predicate is written as
V_CODE = 21344 AND V_CODE = 24288 ?
V_CODE
1 21344
2 20001
3 24288
4 20001
5 24288
a. b. c. d.
1,3,5 1 3,5
No rows will be retrieved
12 12
Q3. What row will be retrieved when the WHERE clause predicate is written as
V_CODE <> 21344 OR V_CODE <> 24288 ?
V_CODE
1 21344
2 20001
3 24288
4 20001
5 24288
a. 1,3,5 b. 2,4 c. 3,5
d. 1,2,3,4,5
13 13
14 14
Q4. What is the correct SQL predicate to retrieve those students who have passed and also those students who have not been awarded any mark?
a. enrol_mark >= 50 AND enrol_mark IS NULL
b. enrol_mark >= 50 OR enrol_mark IS NULL
c. enrol_mark >= 50 AND enrol_mark IS NOT NULL
d. enrol_ mark >= 50 OR enrol_mark IS NOT NULL
e. None of the above
Arithmetic Operations
▪ Can be performed in SQL. ▪ For example:
SELECT stu_nbr, enrol_mark/10 FROM enrolment;
15 15
Oracle NVL function
▪ It is used to replace a NULL with a value.
SELECT stu_nbr, NVL(enrol_mark,0), NVL(enrol_grade,’WH’)
FROM enrolment;
16 16
Renaming Column
▪ Note column headings on slide 16 ▪ Use the word “AS”
– New column name in ” ” to maintain case or spacing ▪ Example
SELECT stu_nbr, enrol_mark/10 AS new_mark FROM enrolment;
SELECT stu_nbr, enrol_mark/10 AS “New Mark” FROM enrolment;
17 17
Sorting Query Result
▪ “ORDER BY” clause – tuples have no order
– Must be used if more than one row may be returned
▪ Order can be ASCending or DESCending. The default is ASCending. – NULL values can be explicitly placed first/last using “NULLS
LAST” or “NULLS FIRST” command
▪ Sorting can be done for multiple columns.
– order of the sorting is specified for each column. ▪ Example:
SELECT stu_nbr, enrol_mark FROM enrolment
ORDER BY enrol_mark DESC
18 18
19 19
Q5. What will be the output of the following SQL statement?
SELECT stu_nbr
FROM enrolment
WHERE enrol_mark IS NULL;
11111111
11111111
11111112
11111113
11111114
11111111
11111112
11111113
11111111
11111112
11111113
11111114
a.
b. c.
Removing Duplicate Rows in the Query Result
▪ Use “DISTINCT” as part of SELECT clause – use with care
SELECT DISTINCT stu_nbr FROM enrolment
WHERE enrol_mark IS NULL;
20 20
SQL NATURAL JOIN
STUDENT
sno name
1 alex
2 maria
3 bob
SELECT *
FROM student JOIN qualification
QUALIFICATION
sno degree year
1 bachelor 1990
1 master 2000
2 PhD 2001
ON student.sno = qualification.sno ORDER BY student.sno
1 1 2
sno
name degree year
alex bachelor 1990 alex master 2000 maria PhD 2001
21 21
SQL JOIN
▪ For database students are required to use ANSI JOINS
– placing the join in the where clause is not acceptable and will be marked as incorrect
for all assessment purposes
• such a join is sometimes known as “implicit join notation” – effectively a cross product and then restricted by the where clause
▪ ANSI JOINS – ON
• the general form which always works, hence the syntax we tend to use
• FROM student JOIN qualification ON student.sno = qualification.sno – USING
• requires matching attribute names for the PK and FK
• FROM student JOIN qualification USING (sno) – NATURAL
• requires matching attribute names for the PK and FK
• FROM student NATURAL JOIN qualification
22 22
JOIN-ing Multiple Tables
Pair the PK and FK in the JOIN condition Note table aliasing e.g. unit u in FROM clause
SELECT s.stu_nbr, s.stu_lname, u.unit_name
FROM ((unit u JOIN enrolment e ON u.unit_code=e.unit_code)
JOIN student s ON e.stu_nbr=s.stu_nbr) ORDER BY s.stu_nbr, u.unit_name;
23 23
How many conditions will be used to join the two tables?
SELECT *
FROM table1 t1 JOIN table2 t2 ON
(t1.T1_attribute1 = t2.T1_attribute1 AND
t1.T1_attribute2 = t2.T1_attribute2)
ORDER BY t1.T1_attribute1, t1.T1_attribute2;
24 24
Summary
▪ SQL statement, clause, predicate. ▪ Writing SQL predicates.
– Comparison, range, set membership, pattern matching, is NULL
– Combining predicates using logic operators (AND, OR, NOT) ▪ Arithmetic operation.
– NVL function
▪ Column alias.
▪ Ordering (Sorting) result. ▪ Removing duplicate rows. ▪ JOIN-ing tables
25 25
Oracle Date Data Type
26 26
Oracle Data Datatype
▪ Dates are stored differently from the SQL standard – standard uses two different types: date and time – Oracle uses one type: DATE
• Stored in internal format contains date and time – Julian date as number (can use arithmetic)
• Output is controlled by formatting
– select to_char(sysdate,’dd-Mon-yyyy’) from dual;
» 04-May-2020 – select
to_char(sysdate,’dd-Mon-yyyy hh:mi:ss PM’) from dual;
» 04-May-2020 02:51:24 PM
27 27
• DATE data type should be formatted with TO_CHAR when selecting for display.
• Text representing date must be formatted with TO_DATE when comparing or inserting/updating.
• Example:
select studid,
studfname || ‘ ‘ || studlname as StudentName,
to_char(studdob,’dd-Mon-yyyy’) as StudentDOB
from uni.student
where studdob > to_date(’01-Apr-1991′,’dd-Mon-yyyy’)
order by studdob;
28 28
Current Date
• Current date can be queried from the DUAL table using the SYSDATE attribute.
– SELECT sysdate FROM dual; • Oracle internal attributes include:
– sysdate: current date/time
– systimestamp: current date/time as a timestamp – user: current logged in user
29 29
Uni Data Model
30 30
Putting this to Work
Q1. Show the ids, names of students as a single column called NAME and their DOBs. Order the output in date of birth order
Q2. Show the ids, names of students as a single column called NAME, unit code, and year and semester of enrolment where the mark is NULL. Order the output by student id, within unit code order
31 31
Sample Solutions
Q1:
SELECT
studfname
|| ‘ ‘
|| studlname AS name, to_char(studdob, ‘DD-MON-YYYY’)
AS dob FROM
uni.student ORDER BY
studdob;
Q2:
SELECT studid,
studfname
|| ‘ ‘
|| studlname AS name, unitcode, to_char(ofyear,’YYYY’) AS year, semester
FROM
uni.student NATURAL JOIN uni.enrolment
WHERE
mark IS NULL
ORDER BY unitcode, studid;
32 32