Week 3 Notes

SQL Language

Review

SELECT Name Country, 
IF(INSTR(Region, 'Central Americal'), Region, Continent) AS Area,
TRUNCATE( SurfaceArea*(0.62137*0.62137), 2) 
AS 'Area in Miles Squared'
FROM Country 
WHERE Continent LIKE '%America%';

INSTR(str, substr) - Returns the position of the first occurrence of substring substr in string str. Like Locate() except arguments are reversed.

Unions

• Example: Logical Union - US or Canada:

select Name Country, IF(INSTR(Region, 'Central America'), Region, Continent) as Area,
truncate( SurfaceArea*(0.62*0.62), 2) as 'Area in Miles Squared' from Country 
where name in ('United States', 'Canada');

or

select Name Country, IF(INSTR(Region, 'Central America'), Region, Continent) as Area,
truncate( SurfaceArea*(0.62*0.62), 2) as 'Area in Miles Squared' from Country 
where (name = 'United States', name = 'Canada');

or

select Name Country, IF(INSTR(Region, 'Central America'), Region, Continent) as Area,
truncate( SurfaceArea*(0.62*0.62), 2) as 'Area in Miles Squared' from Country 
where (name = 'Canada');

UNION

select Name Country, IF(INSTR(Region, 'Central America'), Region, Continent) as Area,
truncate( SurfaceArea*(0.62*0.62), 2) as 'Area in Miles Squared' from Country 
where (name = 'United States');

Intersect

Only returns rows that matches:

SELECT p.name, p.age
FROM People p
INTERSECT
SELECT s.name, s.age
FROM Staff s

Note: the above statement is not supported by MySQL, because INTERSECT is not allowed

SELECT p.name, p.age
	FROM People p
	INNER JOIN Staff s
	ON s.name=p.name

Embedded query

SELECT name, age FROM People 
	WHERE name IN (SELECT name from Staff)

Aggregate operators

In addition to retrieving data, we often want to perform some computation and summarization:

• COUNT([DISTINCT] A); Number of [unique] values in column A
• SUM([DISTINCT] A); Sum of [unique] values in column A
• AVG([DISTINCT] A); Average of [unique] values in column A
• MAX(A); Max of values in column A
• MIN(A); Min of values in column A

SELECT 
	MAX(LifeExpectancy) max, 
	MIN(LifeExpectancy) min, 
	AVG(LifeExpectancy) avg
	FROM Country

Count how many cities have population greater than 1M:

SELECT COUNT(*) FROM City WHERE Population>1000000;

GROUP BY and HAVING

SELECT [DISTINCT] select-list
FROM from-list
WHERE qualification
GROUP BY group-list
HAVING group-qualification;

SELECT cl.Language, AVG(LifeExpectancy) AS 'Avg Life Expectancy'
FROM country c, countrylanguage cl
WHERE c.code=cl.CountryCode
AND cl.isOfficial='T'
GROUP BY cl.language; 

Using HAVING

SELECT cl.Language, AVG(LifeExpectancy) AS 'Avg Life Expectancy', count(*) n
FROM country c, countrylanguage cl
WHERE c.code=cl.CountryCode
AND cl.isOfficial='T'
GROUP BY cl.language
HAVING n>2;

SQL Programming

Data Types

• Primary Data Types
  • Numeric Data Types
  • Date and Time Data Type
  • String Data Types
• Other
  • JSON data types

Primary Data Types

• Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

• Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC

  • In SQL, NUMERIC specifies both precision and scale, DECIMAL specifies scale- precision is at least what the coder specifies
  • In MySQL, DECIMAL is implemented as NUMERIC DECIMAL(p, s), NUMERIC(p, s)
  • Decimal curr(10, 2) = 5.05

• Floating-Point Types (Approximate Value) - FLOAT, DOUBLE

  • FLOAT - 4 bytes of storage
  • DOUBLE - 8 bytes of storage
  • Standard SQL syntax allows FLOAT(p) to specify precision(but not exponent range.
  • Older MySQL extension allows FLOAT(m, d) or DOUBLE(m, d) to approximate how many places after the decimal are to be stored

• Bit-Value Type - BIT

  • The BIT data type is used to store bit values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.
  • To specify bit values, b'value' notation can be used. value is a binary value written using zeros and ones. For example, b'111' represents 7.

• Date and Time Data Types

  • The DATE, DATETIME, and TIMESTAMP types
  • The TIME Type
    • in 'hhmmss' format, can represent daytime and time range
  • The YEAR Type
    • 1 byte, values 1901-2155

• String Data Types

GROUP BY and HAVING recap

HAVING applies to the selection after the grouping, whereas WHERE applies before the grouping.

Nested Queries

• A nested query is a query that has another query embedded in it - called a sub-query.
• The embedded query can itself contain an embedded query....
• A sub-query typically occurs within the WHERE clause
• Can also embed query within SELECT

Uncorrelated Nest Queries (inside out)

Find surface area for countries where language is English

• Can be used for intersect functionality in MySQL

select c.name, c.SurfaceArea from country c
where c.code IN (
	select cl.CountryCode from CountryLanguage cl where Language='English'
);

• NOT IN can be used anywhere IN is used

Uncorrelated Nest Queries (outside in)

select c.name, c.SurfaceArea from country c
where EXISTS (
	select * from CountryLanguage cl where c.code=c.CountryCode and Language='English'
);

select c.region, sum(c.SurfaceArea) as sa 
from country c
where EXISTS (
select * from CountryLanguage cl
where c.code=clCountryCode
and Language='English'
)
group by c.region
order by sa desc;

• order by [element] [desc/(default ascending)]

• Can use NOT EXISTS

select c.region, sum(c.SurfaceArea) as sa 
from country c
where NOT EXISTS (
select * from CountryLanguage cl
where c.code=clCountryCode
and Language='English'
)
group by c.region
order by sa desc;

SQL Examples

(Lecture was a discussion with Q&A)