RMIT Classification: Trusted

Programming Studio 1

SQL
Santha Sumanasekara
 RMIT Classification: Trusted

An SQL query
can be as simple
as this.

SELECT * FROM country;

SELECT b.acad_career))
B.CRSE_ID AS "Course_ID", RMIT Classification:
AND [Link] =Trusted
(SELECT MAX(B_ED.EFFDT)
B.AMS_CRSE_CD AS "Course_Code", FROM ps_ams_crse_parta B_ED
B.COURSE_TITLE_LONG AS "Course_Title", WHERE B_ED.ACAD_CAREER =
[Link] AS "Teaching_Period", B.ACAD_CAREER
NVL([Link], 'New') AS "Status", AND B_ED.CRSE_ID = B.CRSE_ID
B.ACAD_CAREER AS "Career", AND B_ED.EFFDT <= (SELECT term_begin_dt
B.ENRL_TOT AS "Enrolments", FROM ps_term_tbl e_trm
[Link] AS "Campus", WHERE e_trm.strm = [Link]
B.DESCR254 AS "Leaning Mode", AND e_trm.acad_career =
PB.Primary_Learning_Mode AS "Primary_Learning_Mode", b.acad_career))
PB.CRSE_CONTACT_HRS AS "TeacherGuidedHours", AND B.ACAD_PROG = PA.ACAD_PROG --this is the one
PB.Learner_Directed_Hours AS "LearnerDirectedHours", you added at the bottom
PB.Learning_Activities AS "Learning Activities", AND B.ACAD_ORG = ORG.ACAD_ORG
PB.Weekly_Program AS "TeachingSchedule", AND [Link] = (SELECT MAX(E_ORG.EFFDT)
PB.Other_Resources AS "Other_Resources", FROM PS_ACAD_ORG_TBL E_ORG
PB.Assessment_Tasks AS "Assessment_Tasks", WHERE ORG.ACAD_ORG =
PB.Other_Info AS "Other_Info" E_ORG.ACAD_ORG
FROM AND E_ORG.EFFDT <= (select
PS_AMS_CRSE_PB_VW B, TERM_BEGIN_DT
PS_AMS_CRSE_PARTA A, from PS_TERM_TBL e_trm
PS_ACAD_ORG_TBL ORG, where e_trm.strm = [Link]
(SELECT * and e_trm.ACAD_CAREER =
FROM CGS.CGS_PARTB BB B.ACAD_CAREER))
WHERE [Link] = (SELECT MAX(VERSION) AND B.crse_id = PB.crse_id
FROM CGS.CGS_PARTB E_PB AND B.ams_crse_cd = PB.AMS_CRSE_CD
WHERE E_PB.CRSE_ID = BB.CRSE_ID AND B.acad_prog = PB.acad_prog
AND E_PB.AMS_CRSE_CD = AND [Link] = [Link]
BB.AMS_CRSE_CD AND B.CRSE_ID = PA.crse_id
AND E_PB.STRM = [Link] ) AND B.ACAD_ORG ='365H'
) PB, AND B.ACAD_GROUP ='DSC'
(SELECT * AND B.ACAD_PROG =' '
FROM CGS.CGS_PARTA AA AND B.ACAD_CAREER ='UGRD'
WHERE [Link] = (SELECT MAX(VERSION) AND [Link] ='1410'
FROM CGS.CGS_PARTA E_PA AND ([Link] ='Published' OR [Link]
WHERE E_PA.CRSE_ID=AA.CRSE_ID)) PA ='Republished')
ORDER BY B.CRSE_ID
....or, can be as
WHERE
B.crse_id = A.CRSE_ID
AND B.ACAD_PROG = A.ACAD_PROG
complex as this!
AND A.ACAD_CAREER = B.ACAD_CAREER
AND [Link] = (SELECT MAX(A_ED.EFFDT)
FROM ps_ams_crse_parta A_ED
WHERE A_ED.ACAD_CAREER =
A.ACAD_CAREER
AND A_ED.CRSE_ID = A.CRSE_ID
AND A_ED.EFFDT <= (SELECT term_begin_dt
FROM ps_term_tbl e_trm
WHERE e_trm.strm = [Link]
AND e_trm.acad_career =
 RMIT Classification: Trusted

What is SQL?
- SQL stands for Structured Query Language.

- It's used for creating, querying, updating and manipulating

modern relational databases.

- SQL is used on virtually all major platforms, and by virtually all

major relational database systems.

- It is a Declarative Language – you specify what you want, not

How the computer (or DBMS) should get them.

- It is like ordering a meal versus develop (and follow) a recipe to

cook the meal.
 RMIT Classification: Trusted
Complete Week
3 SC1 to get
yourself
familiarised with
SQLite Studio.

SQLiteStudio
Demo
 RMIT Classification: Trusted
Available in SQLite
format,
downloadable from
Sample
Databases and
Tools module.

World Database
Demo
RMIT Classification: Trusted
RMIT Classification: Trusted
RMIT Classification: Trusted
RMIT Classification: Trusted
 RMIT Classification: Trusted

SQL in 10 Minutes!

Represents all
- Display all the countries in the world! attributes.

SELECT *
FROM country;
 RMIT Classification: Trusted

Selecting Columns
Choose which
attributes to be
displayed
- Display names and their continents of all the countries in the world!

SELECT name, continent

FROM country;
 RMIT Classification: Trusted

Changing Column Headings

New column
Heading

- By default column headings on display are as same as attribute

names.

- You can change this default behaviour.

SELECT name AS ‘Country’,

continent
FROM country;
 RMIT Classification: Trusted

This clause
specifies that
Changing Display Order results to be
displayed on
Alphabetical order
of Country Names.

- By default, rows are displayed in no particular order.

- You can change this default behaviour.

SELECT name AS ‘Country’,

continent
FROM country
ORDER BY name;
 RMIT Classification: Trusted

SQL in 10 Minutes!

- 5 Minutes to go!
 RMIT Classification: Trusted

This clause

Selecting Rows specifies that

results to be
filtered on the
continent attribute.

- Display the countries the Oceania Continent.

SELECT name AS ‘Country’,

continent
FROM country
WHERE continent =
‘Oceania’
ORDER BY name;
 RMIT Classification: Trusted

This binary
Selecting Rows operator connects
two conditions.

- Display the countries the Oceania Continent, where population

exceeds 100 Million!

SELECT name AS ‘Country’, continent

FROM country
WHERE continent = ‘Oceania’
AND
population >=
100000000
ORDER BY name;
 RMIT Classification: Trusted

Count every row in

Counting Rows the table.

- How many countries in the world?

SELECT count (*)

FROM country;
 RMIT Classification: Trusted

Counting Rows
With a better
heading!

- How many countries in the world?

SELECT count (*) AS “Number of

Countries”
FROM country;
 RMIT Classification: Trusted

Where clause

Counting Rows will filter rows

first and then
count matching
rows.

- How many countries in Europe?

SELECT count (*) AS “Number of

Countries”
FROM country
WHERE continent = ‘Europe’;
 RMIT Classification: Trusted

Counting Rows Multiple

conditions
possible.

- How many countries in Europe with population over 100 Million?

SELECT count (*) AS “Number of

Countries”
FROM country
WHERE continent = ‘Europe’ AND
population >= 100000000;
 RMIT Classification: Trusted

SQL in 10 Minutes!

- Time’s up!

- You learned 80% most-commonly used

SQL statements.

- It will take remaining 80% of the time to

learn the rest of the 20% of the
language!
 RMIT Classification: Trusted

SQL in 3 weeks!

- Let’s learn the rest, in a slow pace!

 RMIT Classification: Trusted

More on WHERE clause Continents other

than Europe.

- In our first example, we use two boolean operators “=“ and “>=“,
say countries in Europe and population greater than 10 Million.
- Other operators: <, <=, <>, are all possible.

SELECT count (*) AS ’Number of

Countries’
FROM country
WHERE continent <> = ‘Europe’
‘Europe’
AND
population >= 100000000;
 RMIT Classification: Trusted

NULL values in WHERE clause

- Sometimes we find that some attribute values in some rows are not
defined, unspecified, not known yet.
- For example, we do not know the population of some islands in
Antarctica.
- They are denoted by special value called NULL. (It’s not the character
string ‘NULL’, it’s a special value).
 RMIT Classification: Trusted

You must use IS

operator when
NULL values in WHERE clause checking for
NULL values.

- You can use it in the conditions in WHERE clause.

- List the countries that the population unknown.
- You cannot use “population = NULL”
- Use IS operator.

SELECT name, continent, population

FROM country
WHERE population IS= NULL;
 RMIT Classification: Trusted

NULL values in WHERE clause

- You can check the opposite, too.

- List the countries where the population is known.

SELECT name, continent, population

FROM country
WHERE population IS NOT NULL;
 RMIT Classification: Trusted

% is a wildcard
that stands for 0
Partial Matching in WHERE or more
characters.
clause
- List all “Island Nations” – more specifically where the word “Island” in
their name.
- This is partial matching.
- We use LIKE operator for partial matching.

SELECT name, continent, population

FROM country
WHERE name LIKE ‘%Island%’;
 RMIT Classification: Trusted

Partial Matching in WHERE

clause
- In the above example, we compared country names that contained
‘Island’ with zero or more characters at the front and zero or more
characters at the end.
- In other words, it shows up rows where ‘Island’ anywhere in the
name.
- You can be more specific, say countries where name ends with
‘Island’.

SELECT name, continent, population

FROM country
WHERE name LIKE ‘%Island’;
 RMIT Classification: Trusted

What if we have
to compare
Using Sets in WHERE clause against 100
values?

- Sometimes, you will be required to compare an attribute against a

set of values.
- List the countries in Asia, Oceania or Antarctica.
- One way of doing this is to use OR within WHERE clause.
- When list become long, that become cumbersome.

SELECT name, continent

FROM country
WHERE continent = ‘Asia’ OR
continent = ‘Oceania’
OR
continent = ‘Antarctica’ ;
 RMIT Classification: Trusted

Using Sets in WHERE clause

Attribute IN <set-of-values>

- An elegant solution is to use ‘IN’ operator.

- It checks if the attribute exists within a set of values.

SELECT name, continent

FROM country
WHERE continent IN ( ‘Asia’,
‘Oceania’,
‘Antarctica’)
;
 RMIT Classification: Trusted

Eliminating Duplicates
This shows Oceania 28 times,
Asia 51 times, etc.
- Sometimes result sets contain duplicates.
- At best they are just an annoyance, however, they can lead into
incorrect outcomes, specially if counting of results is used.
- Use SELECT DISTINCT, in place of SELECT Correctly shows 7
- Display names of continents continents.

SELECT continent
FROM country;

SELECT DISTINCT continent

FROM country;
 RMIT Classification: Trusted

Counting distinct values

Counts all continent the
values.
- How many continents in the world?
- If we simply count continent values, you will get 239 as the
answer, which is incorrect.
- What you are required to do is to count distinct values
Shows the correct
count.

SELECT count(continent)
FROM country;

SELECT COUNT( DISTINCT continent)

FROM country;
 RMIT Classification: Trusted

ORDER BY clause
Alphabetical order

- Normally, DBMS engine displays rows as they were stored in a table.

- No particular order, and cannot be predicted.
- If you wish to display in a particular order (say, alphabetical order of
names), you must use ORDER BY clause.

SELECT name, continent

FROM country
ORDER BY name;
 RMIT Classification: Trusted

ORDER BY clause
Reverse Alphabetical order

- You can add DESC modifier to reverse the display order.

- To display names in reverse alphabetical order, we use:

SELECT name, continent

FROM country
ORDER BY name DESC;
 RMIT Classification: Trusted

ORDER BY clause

- You can have multiple attributes to sort on: say irst sort on continent,
then sort on descending order of population.

SELECT name, continent, population

FROM country
ORDER BY continent, population
DESC;
 RMIT Classification: Trusted

Next Studio Class….

- We further explore SQL
- Querying data from more than one table
- Sub-queries
- Views
- SQL as a DDL