Lecture 46

SQL- SELECT
 THE HOSPITAL & WORLD
DATABASES

2
 Overview of Hospital Database
The Hospital database will be used as an example during the lecture.

3
 Overview of Hospital Database
patients
patient_id patient_name address
760606-3140 John Doe Elm Street 6
540912-1818 Eve Smith Downing Street 32
831211-8923 Ron Lind Main Road 44

visits
visit_id patient_id date fee
10 760606-3140 2011-01-12 300
11 540912-1818 2011-05-18 300
12 831211-8923 2011-06-24 300
diagnosis
visit_id diagnosis medication
10 pneumonia Pneusic
11 flue Tr Exo
12 cold NULL

4
 The World database
During the course we will use the World database a lot.

• Examples during lectures

• Exercise
• Seminars (?)
• Exam (?)

Download the modified database from itslearning.

5
 Overview of World database

6
 Overview of World database
Sample data
City CountryLanguage

7
 Overview of World database
Country

8
 SQL & SELECT

9
 SQL
SQL = Structured Query Language

- SQL is a commonly used query language used for working with

databases.

- SQL is used as both DDL and DML, to define the contents of a

database and to manipulate it.

- Most of the common DBMS supports SQL

10
 DDL vs DML
DDL (Data Definition Language): Define the database structure or
schema
• CREATE
• ALTER
• DROP
DML (Data Manipulation Language) : Used for managing data within
schema
• SELECT
• INSERT
• UPDATE
• DELETE

11
 SELECT

Description of SELECT:

”This is probably the most important statment in SQL.

It is the statement we use to select rows from one or
more database tables.”

MySQL Tutorial (L Welling & L Thomson)

12
 Selecting Columns

Example SELECT query:

”SELECT * FROM patients;”

Meaning:
“Show me the data from all columns stored in the table named patients”

13
 Selecting Columns
Query: Result:
patient_id patient_name address
SELECT * 760606-3140 John Doe Elm Street 6

FROM patients; 540912-1818

831211-8923
Eve Smith
Ron Lind
Downing Street 32
Main Road 44

patient_name
SELECT patient_name John Doe
Eve Smith
FROM patients; Ron Lind

SELECT patient_name, address patient_name

John Doe
address
Elm Street 6
FROM patients; Eve Smith Downing Street 32
Ron Lind Main Road 44

14
 Selecting Rows

• Most of the time we need information from one specific row.

• To filter out some specific row(s) we use WHERE.

• Understanding of the WHERE clause is very important.

• WHERE is not only used for SELECT statements

15
 Selecting Rows
Query: Result:

SELECT * patient_id patient_name address

FROM patients 540912-1818 Eve Smith Downing Street 32

WHERE patient_id = 540912-1818;

SELECT *
FROM patients
patient_id patient_name address
WHERE address = ’Elm Street 6'; 760606-3140 John Doe Elm Street 6

16
 Search for Column AND Row?
If we are just interested in some specific column for a specific row we
can specify our search even further.

Query: Result:
SELECT patient_name
patient_name
FROM patients Eve Smith

WHERE patient_id = 540912-1818;

patients
patient_id patient_name address
760606-3140 John Doe Elm Street 6
540912-1818 Eve Smith Downing Street 32
831211-8923 Ron Lind Main Road 44

17
 Search for Column AND Row?
Here is another example:

Query: Result:

SELECT address
FROM patients address
Main Road 44
WHERE patient_name = 'Ron Lind';

18
 Sort the output?
Query: Result:
patient_id patient_name address
SELECT * 760606-3140
540912-1818
John Doe
Eve Smith
Elm Street 6
Downing Street 32
FROM patients; 831211-8923 Ron Lind Main Road 44

SELECT *
patient_id patient_name address
FROM patients 540912-1818 Eve Smith Downing Street 32
760606-3140 John Doe Elm Street 6
ORDER BY patient_name 831211-8923 Ron Lind Main Road 44

[ASC]
[DESC];

19
 All or DISTINCT result
When using SELECT all the matching rows will be retrieved (including
duplicates!)

If we do not want to receive duplicate values in the result we use the

keyword DISTINCT.

DISTINCT eliminates duplicates.

visits
SELECT DISTINCT fee visit_id patient_id date fee
FROM visits 10 760606-3140 2011-01-12 300
11 540912-1818 2011-05-18 500
12 831211-8923 2011-06-24 300

fee
Result: 300
500

20
 SQL – Text Matching
Question:
Can you search for a person even if you only know a
part of his/her name?

Answer:
Yes, we can search for a person even if we don’t know his/her
whole name. We use LIKE and the ’% ’ – sign.

21
 SQL Text Matching
We know everything We don’t know everything

SELECT * SELECT *
FROM visits FROM visits
WHERE date = '2011-06-24'; WHERE date LIKE ’2011-06%';
--SELECT *
FROM visits
WHERE date LIKE ’%06-24';

22
 SQL - Operators
AND, && select patient_name from patients where patient_id LIKE
'76%' && patient_name LIKE 'A%';

OR, || select patient_name from patients where patient_id LIKE

'76%' OR patient_id LIKE ' 77%';

!=, <> select patient_name from patients where patient_id !=

'770112-6666';

NOT LIKE select patient_name from patients where patient_id NOT

LIKE '76%';

<, > select * from visit where visit_id < 3;

23
 FUNCTIONS

24
 SQL Aggregate functions
Here are some aggregating functions that will be used often:

AVG() - Returns the average value

COUNT() - Returns the number of rows

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

25
 SQL Functions
• SQL provides several built-in functions:
country
Population
SUM(), sums numbers. 10500
34000
SELECT SUM(Population) 90000

FROM country; 134500

AVG(), returns the average of all numbers. Population

10000
20000
SELECT AVG(Population) 30000

FROM country; 20000

26
 SQL Functions
• With the SQL function count() it is possible to count rows or values:

SELECT count(*)
FROM city;
city
ID
➔ Gives the number of cities in the city table. 15
16

Which happens to be the same as: 17

SELECT count(ID)
FROM city;
(but here we count the ID values instead.)

27
 MAX
The function MAX returns the largest value in an attribute.

SELECT MAX(population)
FROM country

Question:
What does this do?

28
 MAX
Another example with MAX:

SELECT * FROM country

WHERE population = (SELECT MAX(population)
FROM country);

Question:
What does this do?

29
 MAX
Another example with MAX:

SELECT * FROM country

WHERE population = (SELECT MAX(population)
FROM country);

Answer:

This statement will return all information from the table country for
the country with the largest population.

30
 MIN

We can also do the opposite of MAX that is selecting the smallest

value in an attribute. This is done using the function MIN.

SELECT * FROM country

WHERE population= (SELECT MIN(population)
FROM country);

Question:
What does this do?

31
 MIN
We can also do the opposite of MAX that is selecting the smallest
value in an attribute. This is done using the function MIN.

SELECT * FROM country

WHERE population= (SELECT MIN(population)
FROM country);

Answer:
Returns information about the country with the smallest population

32
 SQL Functions
SQL provides several built-in functions.
start
SUBSTRING(), Returns parts of strings.

SELECT SUBSTRING(Name, 1, 3), Name

FROM country; Length
(Gives the first 3 characters of each country name and the complete country name.)

UPPER(), Convert strings to uppercase.

SELECT UPPER(SUBSTRING(Name, 1, 3))

FROM country;

LOWER(), Converts strings to lowercase.

SELECT LOWER(Name)
33 FROM country;
 CONCAT
Imagine that we have peoples’ first name and last name in different
columns and we want it as one string in the result. Then we can use
the function CONCAT.

SELECT CONCAT(lastName, ’ , ', firstName)

FROM employees;

Here we will construct a string that might

look something like this:
”Smith , John”
NOTE: Besides columns we can add other strings,
like the comma above

34
 LIMIT
Sometimes we are not interested in all the tuples that is returned to us
but maybe just the first ten tuples.

SELECT *
FROM country
WHERE population > 1000000 LIMIT 10;

Question:
What does this do?

35
 LIMIT
SELECT *
FROM country
WHERE population > 1000000 LIMIT 10;

Answer:
Selects everything from the table country, but only for countries
with a population greater than 1000000 and limits the result
such that it will only contain ten tuples.

36
 LIMIT
Often LIMIT is used together with ORDER BY. Here is an
example of that.

SELECT *
FROM country
ORDER BY population DESC LIMIT 10;

Question:
What does this do?

37
 LIMIT

SELECT *
FROM country
ORDER BY population DESC LIMIT 10;

Answer:

Will return the top ten countries based on population (i.e. the ten
countries with the largest population)

38
 LIMIT
The function LIMIT also allows us to start at any index in the
resulting list of tuples. This can for example be used if we want
to use paging.

SELECT *
FROM country
ORDER BY population LIMIT 10, 20;

➔ #10 = offset;
➔ #20 = page size
➔ Will return 20 countries based on population, starting at
position 11.

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

http://dev.mysql.com/doc/refman/5.7/en/select.html
39
 GROUPING / HAVING

40
 Grouping- A simple example
✓ Assume that a resturant saves information about orders in a
database.
✓ Assume also that an order contains one kind of food.
✓ After the first month, they want to figure out how much it has
sold of each kind of food.

order_Id date food sold

1 2016-06-12 Pizza 350
2 2016-06-12 Sausage 200
3 2016-06-13 Pizza 80
4 2016-06-22 Hamburger 400
5 2016-06-22 Sausage 120

41
 Grouping- A simple example
order_Id date food sold
1 2016-06-12 Pizza 350
2 2016-06-12 Sausage 200
3 2016-06-13 Pizza 80
4 2016-06-22 Hamburger 400
5 2016-06-22 Sausage 120

SELECT food, SUM(sold) as totalSold food totalSold

Pizza 430
FROM Order
Sausage 320
GROUP BY food;
Hamburger 400

42
 Grouping
• It can be useful to group data on a specified field, so that data with
the same value on that specific field acts together (as a group).

SELECT District, SUM(Population)

FROM city
GROUP BY District;

• Groups all the data on unique district and sums the population
for each district.

43
 Grouping
• Here is a statement to get the number people that live in all the
cities of the world database:

SELECT SUM(Population)
FROM city;

• But, if you want to know the number of people living in cities

for each country you have to group on country code and sum
the population:

SELECT SUM(Population), CountryCode

FROM city
GROUP BY CountryCode;

44
 Having
• HAVING is used together with GROUP BY to limit the groups
in the result set:

SELECT SUM(Population) AS ps, CountryCode

FROM city
GROUP BY CountryCode
HAVING ps > 2000000;

• Same statement as seen earlier but now only countries with more
than 2000000 citizens in their cities will appear.

45
 Having

• Let us look at another example with HAVING:

SELECT District, SUM(population) AS p

FROM city
GROUP BY District HAVING p > 1000000;

➔ Gives us all districts whose cities contain more than one million people.

46
 Having
• Other example of using HAVING:

SELECT CountryCode, AVG(population) AS a

FROM city
GROUP BY CountryCode
HAVING a > 1000000;

➔ Will give us all countries whose cities contain more than

one million people in average.

47
 SELECT-Syntax
SUM( column )
SELECT value_to_display AVG( column )

FROM table_name Count( column )

WHERE expression MAX( column )

GROUP BY how_to_group MIN( column )

UPPER( column )
HAVING expression
LOWER( column)
ORDER BY how_to_sort CONCAT( col1, col2 )
LIMIT row_count;

48