Practical Database Concepts
Lecture 5: SQL
Santha Sumanasekara
March 2020
Overview of the lecture
– What is SQL?
– A demo of working environments
– SQLite Studio
– Oracle SQL Developer (optional)
– Sqlplus – command-line interface (optional)
– Familiarise with SELECT statement
– Selecting columns
– Selecting rows
– Counting rows
– Inserting data
– Updating data
– Deleting Data
What is SQL?
– Very useful LinkedIn Learning Tutorial:
https://www.linkedin.com/learning/sql-essential- training-3/understanding-sql
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.
SQLiteStudio Demo
World Database Demo
SQL in 10 Minutes!
– Display all the countries in the world!
Represents all attributes.
SELECT * FROM country;
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;
Changing Column Headings
– By default column headings on display are as same as attribute names.
– You can change this default behaviour.
New column Heading
SELECT name AS “Country”, continent FROM country;
Changing Display Order
– By default, rows are displayed in no particular order.
– You can change this default behaviour.
This clause specifies that results to be displayed on Alphabetical order of Country Names.
SELECT name AS “Country”, continent FROM country
ORDER BY name;
SQL in 10 Minutes! – 5 Minutes to go!
Selecting Rows
– Display the countries the Oceania Continent.
This clause specifies that results to be filtered on the continent attribute.
SELECT name AS “Country”, continent FROM country
WHERE continent = ‘Oceania’
ORDER BY name;
Selecting Rows
This binary 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;
Counting Rows
– How many countries in the world?
Count every row in the table.
SELECT COUNT(*) FROM country;
Counting Rows
– How many countries in the world?
With a better heading!
SELECT COUNT(*) AS “Number of Countries” FROM country;
Counting Rows
– How many countries in Europe?
Where clause will filter rows first and then count matching rows.
SELECT COUNT(*) AS “Number of Countries” FROM country
WHERE continent = ‘Europe’;
Counting Rows
Multiple conditions possible.
– How many countries in Europe with population of 10 Million or more?
SELECT count (*) AS “Number of Countries” FROM country
WHERE continent = ‘Europe’
AND population >= 10000000;
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!
SQL in 3 weeks!
– Let’s learn the rest, in a slow pace!
More on WHERE clause
Continents other than Europe. Note: <> and != are equivalent
– In our first example, we use two boolean operators “=“ and “>=“, say countries in Europe and population of 10 Million or more.
– Other operators: >, <, <=, <>, != are all possible.
SELECT count (*) AS ’”Number off Counttriies’” FROFMRcOoMunctroyuntry
WHEWRHEEcRonEticnoentin
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).
NULL values in WHERE clause
– 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.
You must use IS operator when checking for NULL values.
SELECT name, continent, population FROFMRcOoMunctroyuntry WHEWRHEEpRoEpuplaotpiounlaItSionNU=LNLU; LL;
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;
Partial Matching in WHERE 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.
% is a wildcard that stands for 0
or more characters.
SELECT name, continent, population FROM country
WHERE name LIKE ‘%Island%’;
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’;
Using Sets in WHERE clause
– 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.
What if we have to compare against 100 values?
SELECT name, continent FROM country
WHERE continent = ‘Asia’ OR continent = ‘Oceania’ OR continent = ‘Antarctica’ ;
Using Sets in WHERE clause
Attribute IN
– 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’) ;
Eliminating Duplicates – Sometimes result sets contain duplicates.
This shows Oceania 28 times, Asia 51 times, etc.
– 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
– Display names of continents
Correctly shows 7 continents.
SELECT continent FROM country;
SELECT DISTINCT continent FROM country;
Counting distinct values – How many continents in the world?
Counts all continent the values.
– 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;
ORDER BY clause
– 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.
Alphabetical order
SELECT name, continent FROM country
ORDER BY name;
ORDER BY clause
Reverse Alphabetical order
– Default order is ascending (ASC)
– 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;
ORDER BY clause
– You can have multiple attributes to sort on: say first sort on ascending order of continent, then sort on descending order of population.
SELECT name, continent, population FROM country
ORDER BY continent, population DESC;
Equivalent to:
SELECT name, continent, population
FROM country
ORDER BY continent ASC, population DESC;
Insert clause
– You can insert records.
INSERT INTO countrylanguage
(countrycode, language, isofficial, percentage) VALUES (‘AUS’, ‘Strayan’, 0, 5.1);
Equivalent to:
INSERT INTO countrylanguage VALUES (‘AUS’, ‘Strayan’, 0, 5.1);
Update clause
– You can update existing records.
Update rows that match condition only
Updated value
UPDATE countrylanguage SET percentage = 6.1 WHERE countrycode = ‘AUS’ AND language = ‘Strayan’;
Update clause
– You can update existing records.
This can be an SQL expression (SELECT percentage
FROM countrylanguage WHERE countrycode = ‘AUS’
AND language = ‘English’)
UPDATE countrylanguage SET percentage = (……) WHERE countrycode = ‘AUS’ AND language = ‘Strayan’;
Delete clause
– You can delete existing records.
Delete all rows that match condition
DELETE FROM countrylanguage WHERE countrycode = ‘AUS’ AND language = ‘Strayan’;
Next week….
– We further explore SQL
– Querying data from more than one table – Sub-queries
– Views
– SQL as a DDL