Index
aggregate functions 3and 26arithmetic operators 2avg 10between 4ceiling 5column 35column aliases 2column specifications 4comparison operators 4concat 4count 64current_date 3current_time 3date 44delete 7distinct 15floor 5group by 10having 8in 165index 3indexes 2insert 7is null 4join 18left 11like 8limit 4logical operators 4ltrim 3min 5now 5null 9or not 1order by 15outer join 2regexp 6right 7round 7rtrim 3select 70sql indexes 1sql view 1sql views 1subquery 3sum 2trim 7truncate 5union 3update 8utc_date 3utc_time 3view 6views 3where 40
aggregate functions
Aggregate Functions
Using the HAVING Clause
and
Introduction
- … databases that you can download and install in your local MySQL instance.…
- … will include SQL design basics and guidance on how to install MySQL and MySQL…
How to Retrieve Data From a Single Table
- LIKE and REGEXP Operators
- and, OR, NOT Logical Operators
- and
- Separates two string patterns and matches either one
- … countryWHERE region = 'caribbean'and population > 100000ORDER BY population…
- … countryWHERE name BETWEEN "Aruba" and "Bahamas";
- Table 4. Operators and precendence order
- ( a and b ) –If both a and b are present, item is included
LIKE and REGEXP Operators
Arithmetic Operators
IS NULL, BETWEEN, IN Operators
AND, OR, NOT Logical Operators
- and, OR, NOT Logical Operators
- and
- … countryWHERE region = 'caribbean'and population > 100000ORDER BY population…
- ( a and b ) –If both a and b are present, item is included
The JOIN Clause
Date Functions
String Functions
The DELETE Clause
- … WHERE name = 'san felipe' and countrycode = 'chl';
- WHERE name = 'san felipe' and countrycode = 'chl';
Grouping Data
Using the HAVING and WHERE Clauses Together
COUNT(column_name) and COUNT(*)
arithmetic operators
How to Retrieve Data From a Single Table
Arithmetic Operators
avg
Aggregate Functions
Simple GROUP BY Query
- USE bike;SELECT category_id, avg(list_price)FROM productGROUP BY category_id
- SELECT category_id, avg(list_price):
Improving the GROUP BY Query
- … category_name, CONCAT('$', ROUND(avg(list_price),2)) AS 'Average List…
- CONCAT('$', ROUND(avg(list_price),2)) AS 'Average List Price'
Using the HAVING Clause
- HAVING avg(list_price) > 1000
- USE bike;SELECT category_id, avg(list_price)FROM productGROUP BY category_idHAVING…
Using the HAVING and WHERE Clauses Together
- USE bike;SELECT category_id, avg(list_price)FROM productWHERE model_year =…
- HAVING avg(list_price) > 1000
between
How to Retrieve Data From a Single Table
IS NULL, BETWEEN, IN Operators
ceiling
Numeric Functions
- FLOOR, ceiling, TRUNCATE
- … list_price, FLOOR(list_price), ceiling(list_price), TRUNCATE(list_price,…
- ceiling(number)
- ceiling(6.2)
- Table 6. FLOOR, ceiling, TRUNCATE functions
column
How to Retrieve Data From a Single Table
- column Aliases
- Show all columns
- Comma separated list of column names
- … previous example, we created a new column that was a calculated value. The problem…
- column Name
- Table 1. column Specifications
- … then in quotes we put the new column alias of “People per square mile.”…
The Five Clauses of the SELECT Statement
Column Specifications
- column Specifications
- Show all columns
- column Name
- Comma separated list of column names
- column Specifications
Column Aliases
- column Aliases
- … previous example, we created a new column that was a calculated value. The problem…
- … then in quotes we put the new column alias of “People per square mile.”…
The JOIN Clause
The INSERT Clause With a Column List
The INSERT Clause Without a Column List
Aggregate Functions
- SUM([DISTINCT] column_values)
- MIN([DISTINCT] column_values)
- The average of the non-null columns in the expression
- MAX([DISTINCT] column_values)
- COUNT([DISTINCT] column_values)
- … highest value of the non-null columns in the expression
- AVG([DISTINCT] column_values)
- The total of the non-null columns in the expression
- … lowest value off the non-null columns in the expression
- The number of the non-null columns in the expression
Grouping Data
COUNT(column_name) and COUNT(*)
column aliases
How to Retrieve Data From a Single Table
Column Aliases
column specifications
How to Retrieve Data From a Single Table
The Five Clauses of the SELECT Statement
Column Specifications
comparison operators
How to Retrieve Data From a Single Table
Comparison Operators
concat
String Functions
Improving the GROUP BY Query
- … bike;SELECT category_name, concat('$', ROUND(AVG(list_price),2)) AS 'Average…
- concat('$', ROUND(AVG(list_price),2)) AS 'Average List Price'
count
How to Retrieve Data From a Single Table
- USE world;SELECT nameFROM countryWHERE name IN ('Aruba', 'Barbados', 'Cuba',…
- … “People per square mile”FROM country;
- SELECT name, IndepYearFROM countryWHERE IndepYear IS NULL;
- USE world;SELECT nameFROM countryWHERE name REGEXP 'g[o,u]';
- … world;SELECT name, populationFROM countryWHERE population > 1000000;
- … world;SELECT name, populationFROM countryWHERE region = 'caribbean'AND population…
- … "People per square mile"FROM country;
- … world;SELECT name, IndepYearFROM countryWHERE name BETWEEN "Aruba" and "Bahamas";
- … DISTINCT continent, nameFROM countryORDER BY continent;
- USE world;SELECT nameFROM countryWHERE name LIKE ‘A%’
The Five Clauses of the SELECT Statement
LIKE and REGEXP Operators
- USE world;SELECT nameFROM countryWHERE name REGEXP 'g[o,u]';
- USE world;SELECT nameFROM countryWHERE name LIKE ‘A%’
Arithmetic Operators
Column Aliases
Comparison Operators
IS NULL, BETWEEN, IN Operators
- USE world;SELECT nameFROM countryWHERE name IN ('Aruba', 'Barbados', 'Cuba',…
- SELECT name, IndepYearFROM countryWHERE IndepYear IS NULL;
- … world;SELECT name, IndepYearFROM countryWHERE name BETWEEN "Aruba" and "Bahamas";
AND, OR, NOT Logical Operators
DISTINCT Clause
The JOIN Clause
- … “City Name”, co.name AS “country Name”
- JOIN country co
- ON ci.countryCode = co.Code;
- … AS "City Name", 3 country.name AS "country Name" 4 FROM country 6…
- … aliases. The table aliases of co for country and ci for city are defined in…
- … Name", 3 co.name AS "country Name" 4 FROM city ci 5 …
Joining More Than Two Tables
- ON cl.countryCode = ci.countryCode;
- JOIN countrylanguage cl.
- … Name",3 co.name AS "country Name", 4 cl.language AS…
The OUTER JOIN Clause
- ON c.code = cl.countryCode
- FROM country c LEFT JOIN countrylanguage cl
- … c.continent, cl.language3 FROM country c LEFT JOIN countrylanguage cl4 ON c.code…
How to Code a UNION
- SELECT name, populationFROM countryWHERE continent = 'Oceania'
- … name, populationFROM cityWHERE countryCode = 'AUS'
- … name, population3 FROM city WHERE countryCode = 'AUS'4 UNION5 SELECT name,…
Date Functions
Numeric Functions
String Functions
The INSERT Clause With a Column List
- (name, countryCode, district, population)
- … INTO city 3 (name, countryCode, district, population) 4 VALUES 5…
The DELETE Clause
- … WHERE name = 'san felipe' AND countrycode = 'chl';
- WHERE name = 'san felipe' AND countrycode = 'chl';
Aggregate Functions
- count(*)
- … MIN(list_price), MAX(list_price), count(list_price), count(*)FROM product;
- count([DISTINCT] column_values)
COUNT(column_name) and COUNT(*)
Using the DISTINCT Statement
The Subquery In a SELECT Statement
The Subquery in an UPDATE statement
- (SELECT countryCode FROM countrylanguage WHERE population = 0)
- 1 UPDATE country 2 SET GNPOld = 0.00 3 WHERE Code IN 4 …
- UPDATE country
The Subquery In a Delete Statement
- (SELECT code FROM country
- … world;DELETE FROM city_bakWHERE countryCode IN (SELECT code FROM country …
- WHERE countryCode IN
Benefits of Using Views
- USE WORLD;CREATE VIEW city_country ASSELECT ci.name AS city_name, co.name AS…
- CREATE VIEW city_country AS
- … ci.name AS city_name, co.name AS country_name
- JOIN country co
- ON ci.countryCode = co.Code;
- Results by selecting from the city_country view:
current_date
Date Functions
- current_date()
- current_date
- … DATE('2020-01-01') AS 'DATE(), date only', current_date AS 'current_date', CURRENT_TIME…
current_time
Date Functions
- current_time
- … CURRENT_DATE AS 'CURRENT_DATE', current_time AS 'current_time', UTC_DATE…
- current_time()
date
Date Functions
- date_FORMAT
- date_ADD
- dateDIFF
- Current date/Time Functions
- date, dateTIME
- Table 1. Current date Functions
- CURRENT_date()
- date
- date(date)
- CURRENT_date
- Table 3. date_FORMAT Function
- * Returns current local date
- date_FORMAT
- date/time
- Table 2. date_ADD Function
- date
- SELECT NOW() AS 'NOW()', date('2020-01-01') AS 'date(), date only', …
- • dates must be enclosed in quotes • You can pass a date or dateTIME datatype…
- SELECT dateDIFF('2018-01-01', '2019-01-01') AS 'date Difference';
- * extracts the date from input. If time is included, the time is dropped.
- date_FORMAT(‘2020-09-03’, ‘%m/%d/%y’)
- … world;SELECT name, continent, date_FORMAT('2020-01-28', '%m/%d/%y')FROM country;
- * Returns current local date and time.
- date/time
- USE bike;SELECT order_date, date_ADD(order_date, INTERVAL 1 DAY) AS 'ORDER…
- * Returns current UTC date.
- date
- date('2020-01-01 11:31:31')
- date_ADD(date, interval expression unit)
- * Returns current UTC date.
- UTC_date()
- UTC_date
- date_ADD(‘2020-01-01’, INTERVAL 1 DAY)
- • Returns a date with a date or dateTIME value equal to the original value…
The UPDATE Clause With a Column List
Aggregate Functions
The Subquery in an UPDATE statement
- The Subquery in an UPdate statement
- 1 UPdate country 2 SET GNPOld = 0.00 3 WHERE Code IN 4 …
- UPdate country
The Subquery In a Delete Statement
Views That Allow UPDATE Statements
delete
The DELETE Clause
The Subquery In a Delete Statement
- The Subquery in a delete statement
- delete FROM city_bak
- USE world;delete FROM city_bakWHERE CountryCode IN (SELECT code FROM country …
- NOTE: Before you can run a delete or UPDATE statement without a WHERE clause,…
distinct
How to Retrieve Data From a Single Table
- distinct Keyword
- distinct
- Table 7. distinct Keyword
- SELECT distinct continent, nameFROM countryORDER BY continent;
DISTINCT Clause
- distinct Keyword
- distinct
- Table 7. distinct Keyword
- SELECT distinct continent, nameFROM countryORDER BY continent;
Aggregate Functions
- SUM([distinct] column_values)
- MIN([distinct] column_values)
- MAX([distinct] column_values)
- COUNT([distinct] column_values)
- AVG([distinct] column_values)
Using the DISTINCT Statement
- Removing Duplicate Values With distinct
- … bike;SELECT COUNT(list_price), COUNT(distinct list_price) FROM product;
floor
Numeric Functions
- floor, CEILING, TRUNCATE
- floor(7.7)
- USE bike;SELECT list_price, floor(list_price), CEILING(list_price), TRUNCATE(list_price,…
- floor(number)
- Table 6. floor, CEILING, TRUNCATE functions
group by
Grouping Data
Simple GROUP BY Query
Improving the GROUP BY Query
- Improving the group by Query
- … p.category_id = c.category_idgroup by category_nameORDER BY category_name;
- group by category_name
Using the HAVING Clause
Using the HAVING and WHERE Clauses Together
having
Grouping Data
Using the HAVING Clause
- … Aggregate Functions With The having Clause
- having AVG(list_price) > 1000
- … AVG(list_price)FROM productGROUP BY category_idhaving AVG(list_price) > 1000
- … so we will focus solely on the having clause.
Using the HAVING and WHERE Clauses Together
- … model_year = 2016GROUP BY category_idhaving AVG(list_price) > 1000
- having AVG(list_price) > 1000
- … statement that includes both the having and WHERE clause in the same SQL statement.
in
Introduction
- Before You Begin
- … databases that you can download and install in your local MySQL instance.…
- in a future edition, this book will include SQL design basics and guidance…
How to Retrieve Data From a Single Table
- The in Keyword
- DISTinCT Keyword
- The clauses MUST appear in the order shown above.
- Matches any single character within the given range.
- … world;SELECT nameFROM countryWHERE name in ('Aruba', 'Barbados', 'Cuba',…
- DISTinCT
- Matches any single character listed within the brackets.
- Match the pattern to the beginning of the value being tested.
- Let us break the statement line by line:
- … but b must NOT be present to be included
- Eliminates duplicate rows
- Match any string of characters to the left of the symbol
- SELECT name, indepYearFROM countryWHERE indepYear IS NULL;
- Separates two string patterns and matches either one
- Matches any single character.
- USE world;SELECT name, indepYearFROM countryWHERE name BETWEEN "Aruba" and…
- … pattern to the end of the value being tested.
- integer Division
- in the previous example, we created a new column that was a calculated value.…
- Table 7. DISTinCT Keyword
- … a and b are present, item is included
- SELECT DISTinCT continent, nameFROM countryORDER BY continent;
- Match a single character
- … either a OR b is present item is included
- Modulo (remainder)
- We used the AS keyword then in quotes we put the new column alias of “People…
The Five Clauses of the SELECT Statement
LIKE and REGEXP Operators
- Matches any single character within the given range.
- Matches any single character listed within the brackets.
- Match the pattern to the beginning of the value being tested.
- Match any string of characters to the left of the symbol
- Separates two string patterns and matches either one
- Matches any single character.
- … pattern to the end of the value being tested.
- Match a single character
Arithmetic Operators
Column Aliases
- in the previous example, we created a new column that was a calculated value.…
- We used the AS keyword then in quotes we put the new column alias of “People…
IS NULL, BETWEEN, IN Operators
- The in Keyword
- … world;SELECT nameFROM countryWHERE name in ('Aruba', 'Barbados', 'Cuba',…
- SELECT name, indepYearFROM countryWHERE indepYear IS NULL;
- USE world;SELECT name, indepYearFROM countryWHERE name BETWEEN "Aruba" and…
AND, OR, NOT Logical Operators
- … but b must NOT be present to be included
- … a and b are present, item is included
- … either a OR b is present item is included
DISTINCT Clause
- DISTinCT Keyword
- DISTinCT
- Eliminates duplicate rows
- Table 7. DISTinCT Keyword
- SELECT DISTinCT continent, nameFROM countryORDER BY continent;
The JOIN Clause
- The Join Clause
- … write SQL statements more succinctly with an inner join clause using table…
- JOin country co
- Let us break the statement line by line:
- … example of a SQL statement with an inner join clause using explicit syntax.
- … FROM country 6 JOin city 5 ON city.CountryCode…
- The results of the join query would yield the same results as shown below…
- … FROM city ci 5 JOin country co 6 ON ci.CountryCode…
Joining More Than Two Tables
- How to Join More than Two Tables
- JOin countrylanguage cl.
- … FROM city ci6 JOin country co 7 ON ci.CountryCode…
The OUTER JOIN Clause
- The Outer Join Clause
- … SQL statement with an outer join clause.
- SELECT c.name, c.continent, cl.language
- FROM country c LEFT JOin countrylanguage cl
- … world;2 SELECT c.name, c.continent, cl.language3 FROM country c LEFT JOin…
How to Code a UNION
- … populationFROM countryWHERE continent = 'Oceania'
- … population6 FROM country7 WHERE continent = 'Oceania'8 ORDER BY name;
Date Functions
- • Dates must be enclosed in quotes • You can pass a DATE or DATETIME…
- * extracts the date from input. If time is included, the time is dropped.
- USE world;SELECT name, continent, DATE_FORMAT('2020-01-28', '%m/%d/%y')FROM…
- Minutes, numeric (00..59)
- … order_date, DATE_ADD(order_date, inTERVAL 1 DAY) AS 'ORDER DATE PLUS…
- DATE_ADD(date, interval expression unit)
- DATE_ADD(‘2020-01-01’, inTERVAL 1 DAY)
- … DATETIME value equal to the original value plus the specified interval.
Numeric Functions
- FLOOR, CEILinG, TRUNCATE
- … list_price, FLOOR(list_price), CEILinG(list_price), TRUNCATE(list_price,…
- CEILinG(number)
- CEILinG(6.2)
- Table 6. FLOOR, CEILinG, TRUNCATE functions
String Functions
- LOCATE, LENGTH, SUBSTRinG
- string
- SUBSTRinG(str,start[,length])
- string
- string
- LEFT(string, num. characters)
- TRIM(string)
- string
- … world;SELECT CONCAT(name, ', ', continent)FROM country;
- string
- LTRIM(string)
- string
- string
- RIGHT(string, num. characters)
- … LENGTH('salmon'), SUBSTRinG('salmon',3,999);
- Table 9. LOCATE. LENGTH, SUBSTRinG functions
- LOCATE(find,search[,start])
- string
- LOWER(string)
- SUBSTRinG('salmon',3,999)
- string
- string
- LOCATE(), and LENGTH() accept a string but return an integer. • SUBSTRinG()…
- RTRIM(string)
- string
- UPPER(string)
The INSERT Clause With a Column List
- The inSERT Clause With a Column List
- Below is a basic example of an inSERT statement with a column list:
- inSERT inTO city
- Results of the insert:
- 1 USE world;2 inSERT inTO city 3 (name, countryCode,…
The INSERT Clause Without a Column List
Aggregate Functions
- SUM([DISTinCT] column_values)
- Min([DISTinCT] column_values)
- … average of the non-null columns in the expression
- … AVG(list_price), SUM(list_price), Min(list_price), MAX(list_price),…
- MAX([DISTinCT] column_values)
- COUNT([DISTinCT] column_values)
- … value of the non-null columns in the expression
- numeric, date, string
- AVG([DISTinCT] column_values)
- … total of the non-null columns in the expression
- numeric, date, string
- … value off the non-null columns in the expression
- … number of the non-null columns in the expression
Grouping Data
Improving the GROUP BY Query
- Improving the GROUP BY Query
- … List Price'FROM product p JOin category c ON p.category_id = c.category_idGROUP…
- JOin category c
Using the HAVING Clause
- Filtering Aggregate Functions With The HAVinG Clause
- HAVinG AVG(list_price) > 1000
- … productGROUP BY category_idHAVinG AVG(list_price) > 1000
- … previously discussed the preceding lines of code for this query so we will…
Using the HAVING and WHERE Clauses Together
- … = 2016GROUP BY category_idHAVinG AVG(list_price) > 1000
- HAVinG AVG(list_price) > 1000
- … an example of a statement that includes both the HAVinG and WHERE clause…
Using the DISTINCT Statement
- Removing Duplicate Values With DISTinCT
- … COUNT(list_price), COUNT(DISTinCT list_price) FROM product;
The Subquery In a SELECT Statement
- The Subquery in a SELECT Statement
- WHERE CountryCode in
- … city 4 WHERE CountryCode in 5 (SELECT code 6 …
The Subquery in an UPDATE statement
- The Subquery in an UPDATE statement
- … GNPOld = 0.00 3 WHERE Code in 4 (SELECT CountryCode FROM countrylanguage…
- WHERE Code in
Create a Duplicate Table From An Existing Table
The Subquery In a Delete Statement
- The Subquery in a DELETE statement
- … FROM city_bakWHERE CountryCode in (SELECT code FROM country …
- WHERE CountryCode in
- … uncheck “Safe Updates” checkbox in MySQL Preference. Please see below.
Benefits of Using Views
- Benefits of Using Views
- … country_nameFROM city ci JOin country co ON ci.CountryCode = co.Code;
- JOin country co
- Results by selecting from the city_country view:
Views That Allow UPDATE Statements
SQL Indexes Explained
Clustered vs. Non-clustered Indexes
index
SQL Indexes Explained
Clustered vs. Non-clustered Indexes
indexes
SQL Indexes Explained
Clustered vs. Non-clustered Indexes
insert
The INSERT Clause With a Column List
- The insert Clause With a Column List
- Below is a basic example of an insert statement with a column list:
- insert INTO city
- Results of the insert:
- 1 USE world;2 insert INTO city 3 (name, countryCode, district,…
The INSERT Clause Without a Column List
is null
How to Retrieve Data From a Single Table
IS NULL, BETWEEN, IN Operators
join
The JOIN Clause
- The join Clause
- … more succinctly with an inner join clause using table aliases. Instead of…
- join country co
- … a SQL statement with an inner join clause using explicit syntax.
- … FROM country 6 join city 5 ON city.CountryCode…
- The results of the join query would yield the same results as shown below whether…
- … FROM city ci 5 join country co 6 ON ci.CountryCode…
Joining More Than Two Tables
- How to join More than Two Tables
- join countrylanguage cl.
- … FROM city ci6 join country co 7 ON ci.CountryCode…
The OUTER JOIN Clause
- The Outer join Clause
- … a SQL statement with an outer join clause.
- FROM country c LEFT join countrylanguage cl
- … cl.language3 FROM country c LEFT join countrylanguage cl4 ON c.code = cl.CountryCode5…
Improving the GROUP BY Query
Benefits of Using Views
left
How to Retrieve Data From a Single Table
LIKE and REGEXP Operators
The OUTER JOIN Clause
- FROM country c left JOIN countrylanguage cl
- … c.continent, cl.language3 FROM country c left JOIN countrylanguage cl4 ON…
String Functions
- RIGHT, left
- left(string, num. characters)
- left(‘Salmon ‘)
- SELECT LTRIM(' Salmon ') AS "left Trim", RTRIM(' Salmon ') AS…
- Table 7. RIGHT, left functions
- … bike;SELECT category_name, left(category_name, 8) AS 'First 8 Characters', …
- left(‘Salmon’, 3)
like
How to Retrieve Data From a Single Table
- like and REGEXP Operators
- like Symbol
- Table 2. like Keyword
- … world;SELECT nameFROM countryWHERE name like ‘A%’
LIKE and REGEXP Operators
- like and REGEXP Operators
- like Symbol
- Table 2. like Keyword
- … world;SELECT nameFROM countryWHERE name like ‘A%’
limit
How to Retrieve Data From a Single Table
The Five Clauses of the SELECT Statement
The Subquery In a SELECT Statement
logical operators
How to Retrieve Data From a Single Table
AND, OR, NOT Logical Operators
ltrim
String Functions
- TRIM, ltrim, RTRIM
- ltrim(string)
- SELECT ltrim(' Salmon ') AS "Left Trim", RTRIM(' Salmon ') AS "Right…
min
How to Retrieve Data From a Single Table
DISTINCT Clause
Date Functions
Aggregate Functions
now
How to Retrieve Data From a Single Table
Column Aliases
Date Functions
null
How to Retrieve Data From a Single Table
IS NULL, BETWEEN, IN Operators
Aggregate Functions
- The average of the non-null columns in the expression
- The highest value of the non-null columns in the expression
- The total of the non-null columns in the expression
- The lowest value off the non-null columns in the expression
- The number of the non-null columns in the expression
or not
The JOIN Clause
order by
How to Retrieve Data From a Single Table
- … 'Barbados', 'Cuba', 'Bahamas')order by population ASC;
- order by name
- … 'caribbean'AND population > 100000order by population ASC;
- … DISTINCT continent, nameFROM countryorder by continent;
The Five Clauses of the SELECT Statement
IS NULL, BETWEEN, IN Operators
AND, OR, NOT Logical Operators
DISTINCT Clause
The OUTER JOIN Clause
How to Code a UNION
Improving the GROUP BY Query
The Subquery In a SELECT Statement
outer join
The OUTER JOIN Clause
regexp
How to Retrieve Data From a Single Table
- LIKE and regexp Operators
- … world;SELECT nameFROM countryWHERE name regexp 'g[o,u]';
- regexp Characters
LIKE and REGEXP Operators
- LIKE and regexp Operators
- … world;SELECT nameFROM countryWHERE name regexp 'g[o,u]';
- regexp Characters
right
String Functions
- right, LEFT
- right(string, num. characters)
- right(‘Salmon’, 3)
- right(‘ Salmon‘)
- … RTRIM(' Salmon ') AS "right Trim", TRIM(' Salmon ') AS "Trim";
- Table 7. right, LEFT functions
- … AS 'First 8 Characters', right(category_name, 8) AS 'Last 8 Characters'FROM…
round
Numeric Functions
- Table 5. round function
- round(13.37, 1)
- … world;SELECT name, LifeExpectancy, round(LifeExpectancy) FROM world.country;
- round(number[, length])
- round
Improving the GROUP BY Query
- … category_name, CONCAT('$', round(AVG(list_price),2)) AS 'Average List…
- CONCAT('$', round(AVG(list_price),2)) AS 'Average List Price'
rtrim
String Functions
select
How to Retrieve Data From a Single Table
- The Five Clauses of the select statement
- USE world;select nameFROM countryWHERE name IN ('Aruba', 'Barbados', 'Cuba',…
- select name, population / SurfaceArea AS “People per square mile”FROM…
- select name, IndepYearFROM countryWHERE IndepYear IS NULL;
- USE world;select nameFROM countryWHERE name REGEXP 'g[o,u]';
- USE world;select name, populationFROM countryWHERE population > 1000000;
- USE world;select name, populationFROM countryWHERE region = 'caribbean'AND population…
- USE world;select name, population / SurfaceAreaAS "People per square mile"FROM…
- USE world;select name, IndepYearFROM countryWHERE name BETWEEN "Aruba" and "Bahamas";
- select name
- select DISTINCT continent, nameFROM countryORDER BY continent;
- USE world;select nameFROM countryWHERE name LIKE ‘A%’
The Five Clauses of the SELECT Statement
- The Five Clauses of the select statement
- … Example:1 USE world;2 select name3 FROM city4 WHERE CountryCode…
- select name
LIKE and REGEXP Operators
- USE world;select nameFROM countryWHERE name REGEXP 'g[o,u]';
- USE world;select nameFROM countryWHERE name LIKE ‘A%’
Arithmetic Operators
Column Aliases
Comparison Operators
IS NULL, BETWEEN, IN Operators
- USE world;select nameFROM countryWHERE name IN ('Aruba', 'Barbados', 'Cuba',…
- select name, IndepYearFROM countryWHERE IndepYear IS NULL;
- USE world;select name, IndepYearFROM countryWHERE name BETWEEN "Aruba" and "Bahamas";
AND, OR, NOT Logical Operators
DISTINCT Clause
The JOIN Clause
- select ci.name AS “City Name”, co.name AS “Country Name”
- 1 USE world;2 select city.name AS "City Name", 3 country.name…
- … clause and referenced in the select and ON clause:
- 1 USE world;2 select ci.name AS "City Name", 3 co.name…
Joining More Than Two Tables
The OUTER JOIN Clause
- select c.name, c.continent, cl.language
- 1 USE world;2 select c.name, c.continent, cl.language3 FROM country c LEFT JOIN…
How to Code a UNION
- select name, populationFROM countryWHERE continent = 'Oceania'
- select name, populationFROM cityWHERE CountryCode = 'AUS'
- 1 USE world;2 select name, population3 FROM city WHERE CountryCode = 'AUS'4 UNION5…
Date Functions
- select NOW() AS 'NOW()', DATE('2020-01-01') AS 'DATE(), date only', CURRENT_DATE…
- select DATEDIFF('2018-01-01', '2019-01-01') AS 'Date Difference';
- USE world;select name, continent, DATE_FORMAT('2020-01-28', '%m/%d/%y')FROM country;
- USE bike;select order_date, DATE_ADD(order_date, INTERVAL 1 DAY) AS 'ORDER…
Numeric Functions
- USE bike;select list_price, FLOOR(list_price), CEILING(list_price), TRUNCATE(list_price,…
- USE world;select name, LifeExpectancy, ROUND(LifeExpectancy) FROM world.country;
String Functions
- select UPPER('Salmon'), LOWER('Salmon');
- USE world;select CONCAT(name, ', ', continent)FROM country;
- select FORMAT(list_price,2) FROM bike.product;
- select LOCATE('al','salmon',1), LENGTH('salmon'), SUBSTRING('salmon',3,999);
- select LTRIM(' Salmon ') AS "Left Trim", RTRIM(' Salmon ') AS "Right…
- USE bike;select category_name, LEFT(category_name, 8) AS 'First 8 Characters', …
Aggregate Functions
Simple GROUP BY Query
- USE bike;select category_id, AVG(list_price)FROM productGROUP BY category_id
- select category_id, AVG(list_price):
Improving the GROUP BY Query
Using the HAVING Clause
Using the HAVING and WHERE Clauses Together
COUNT(column_name) and COUNT(*)
Using the DISTINCT Statement
The Subquery In a SELECT Statement
- The Subquery in a select Statement
- 1 USE world;2 select name, population 3 FROM city 4 WHERE…
- select name, population
- (select code
The Subquery in an UPDATE statement
- (select CountryCode FROM countrylanguage WHERE population = 0)
- … 0.00 3 WHERE Code IN 4 (select CountryCode FROM countrylanguage…
Create a Duplicate Table From An Existing Table
- … from an Existing Table with a select Statement
- CREATE TABLE city_bak AS select * FROM city;
- … CREATE TABLE city_bak AS select * FROM city;
The Subquery In a Delete Statement
Benefits of Using Views
- … WORLD;CREATE VIEW city_country ASselect ci.name AS city_name, co.name AS country_nameFROM…
- select ci.name AS city_name, co.name AS country_name
- Results by selecting from the city_country view:
sql indexes
SQL Indexes Explained
sql view
SQL View Explained
sql views
SQL View Explained
subquery
The Subquery In a SELECT Statement
The Subquery in an UPDATE statement
The Subquery In a Delete Statement
sum
Aggregate Functions
- sum([DISTINCT] column_values)
- … bike;SELECT AVG(list_price), sum(list_price), MIN(list_price), MAX(list_price),…
trim
String Functions
- trim, Ltrim, Rtrim
- trim(string)
- trim(‘ Salmon ‘)
- Ltrim(string)
- Table 8. trim functions
- SELECT Ltrim(' Salmon ') AS "Left trim", Rtrim(' Salmon ') AS…
- Rtrim(string)
truncate
Numeric Functions
- FLOOR, CEILING, truncate
- truncate(7.9)
- … FLOOR(list_price), CEILING(list_price), truncate(list_price, 0)FROM product;
- Table 6. FLOOR, CEILING, truncate functions
- truncate(NUMBER, length)
union
How to Code a UNION
- How to Code a union
- union
- … city WHERE CountryCode = 'AUS'4 union5 SELECT name, population6 FROM country7…
update
The UPDATE Clause With a Column List
The Subquery in an UPDATE statement
- The Subquery in an update statement
- 1 update country 2 SET GNPOld = 0.00 3 WHERE Code IN 4 (SELECT…
- update country
The Subquery In a Delete Statement
Views That Allow UPDATE Statements
utc_date
Date Functions
utc_time
Date Functions
view
SQL View Explained
Benefits of Using Views
- Benefits of Using views
- USE WORLD;CREATE view city_country ASSELECT ci.name AS city_name, co.name AS…
- CREATE view city_country AS
- … selecting from the city_country view:
Views That Allow UPDATE Statements
views
SQL View Explained
Benefits of Using Views
Views That Allow UPDATE Statements
where
How to Retrieve Data From a Single Table
- … world;SELECT nameFROM countrywhere name IN ('Aruba', 'Barbados', 'Cuba', 'Bahamas')ORDER…
- SELECT name, IndepYearFROM countrywhere IndepYear IS NULL;
- … world;SELECT nameFROM countrywhere name REGEXP 'g[o,u]';
- … world;SELECT name, populationFROM countrywhere population > 1000000;
- … world;SELECT name, populationFROM countrywhere region = 'caribbean'AND population…
- … world;SELECT name, IndepYearFROM countrywhere name BETWEEN "Aruba" and "Bahamas";
- … world;SELECT nameFROM countrywhere name LIKE ‘A%’
The Five Clauses of the SELECT Statement
LIKE and REGEXP Operators
- … world;SELECT nameFROM countrywhere name REGEXP 'g[o,u]';
- … world;SELECT nameFROM countrywhere name LIKE ‘A%’
Comparison Operators
IS NULL, BETWEEN, IN Operators
- … world;SELECT nameFROM countrywhere name IN ('Aruba', 'Barbados', 'Cuba', 'Bahamas')ORDER…
- SELECT name, IndepYearFROM countrywhere IndepYear IS NULL;
- … world;SELECT name, IndepYearFROM countrywhere name BETWEEN "Aruba" and "Bahamas";
AND, OR, NOT Logical Operators
How to Code a UNION
- SELECT name, populationFROM countrywhere continent = 'Oceania'
- SELECT name, populationFROM citywhere CountryCode = 'AUS'
- … SELECT name, population3 FROM city where CountryCode = 'AUS'4 UNION5 SELECT…
Date Functions
- Year for the week where Sunday is the first day of the week, numeric, four digits;…
- Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used…
- Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
- Week (00..53), where Monday is the first day of the week; WEEK() mode 1
- Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used…
- Year for the week, where Monday is the first day of the week, numeric, four…
The DELETE Clause
- … DELETE 3 FROM city 4 where name = 'san felipe' AND countrycode…
- where name = 'san felipe' AND countrycode = 'chl';
Grouping Data
Using the HAVING and WHERE Clauses Together
- where model_year = 2016
- … category_id, AVG(list_price)FROM productwhere model_year = 2016GROUP BY category_idHAVING…
- … includes both the HAVING and where clause in the same SQL statement.
The Subquery In a SELECT Statement
- where CountryCode IN
- … population 3 FROM city 4 where CountryCode IN 5 (SELECT…
- where region = 'Caribbean')
The Subquery in an UPDATE statement
- … CountryCode FROM countrylanguage where population = 0)
- … SET GNPOld = 0.00 3 where Code IN 4 (SELECT CountryCode FROM…
- where Code IN
The Subquery In a Delete Statement
- USE world;DELETE FROM city_bakwhere CountryCode IN (SELECT code FROM country …
- where region = 'Central Africa');
- where CountryCode IN
- … or UPDATE statement without a where clause, you must uncheck “Safe Updates”…

CC BY-NC-ND International 4.0: This work is released under a CC BY-NC-ND International 4.0 license, which means that you are free to do with it as you please as long as you (1) properly attribute it, (2) do not use it for commercial gain, and (3) do not create derivative works.