Please enable JavaScript.
Coggle requires JavaScript to display documents.
Wk3_L Extracting Data from Databases (Queries (extract data from tables),…
Wk3_L Extracting Data from Databases
SQL Data Manipulation Language
Reserved Words
DISTINCT
WHERE
SELECT
ORDER BY (etc)
User Defined Words
(case-sensitive)
Table Names
Attribute Names
Queries
(extract data from tables)
WHERE
select rows that meet a condition
Set Membership
SELECT * FROM LandReg WHERE LotNo IN (5,13);
numerical
SELECT * FROM LandReg WHERE DateOfSale IN ('22 Jun 1857', '11 Sep 1855');
non-numerical
the opposite operation: NOT IN
Pattern Matching
symbols
%: matches any sequence of zero or more characters
(wildcard)
_: matches any single character
can be at start, middle or end
SELECT * FROM LandReg WHERE DateOfSale LIKE '%J%';
Range
Range is
inclusive
can also express the opposite: NOT BETWEEN
SELECT * FROM LandReg WHERE LotNo BETWEEN 4 AND 8;
Null
check if data contains null value - is data missing?
Use IS NULL or IS NOT NULL
SELECT * FROM LandReg WHERE Lot IS NULL;
Comparison
for numerical values
Allowed comparison operators: =, <, >, >=, <=
SELECT * FROM LandReg WHERE LotNo = 5 OR LotNo = 6;
SELECT * FROM LandReg WHERE LotNo < 4;
expressions can be compounded (OR, AND, NOT)
SELECT
simple query
SELECT * FROM LandReg;
extracts whole table
SELECT LotNo FROM LandReg;
extracts specific columns
SELECT DISTINCT LotNo FROM LandReg;
remove duplicate data
Advanced Functionalities
Ordering
Can restrict columns, rows, then determine order of output
ASC (default) & DESC
ORDER BY needs to be last clause in a SELECT
SELECT * FROM LandReg ORDER BY LotNo DESC;
Aggregation
AVG
MIN
SUM
MAX
COUNT
SELECT COUNT(Lot) FROM LandReg;
SELECT COUNT(DISTINCT Lot) FROM LandReg;
SELECT COUNT(*) FROM LandReg;
Querying Multiple Tables
Joining tables:
Possible to extract data from multiple tables such that rows combine data across tables
SELECT NameOfPurchaser, Address, LotNo
FROM LandReg, AddressReg
WHERE LandReg.NameOfPurchaser = AddressReg.Name of Person;