[SOLVED] CS SQL database MONASH

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