STRUCTURED QUERY LANGUAGE
(POSTGRESQL)
WHAT IS A DATABASE?
A database stores data. A relational database is a database that stores tables data
that are related to each other. The columns are called fields, and the rows are
called records(entries). The information on a database is stored on the hard disk of
a database server. Servers are centralized computers that perform services via
request over a network.
Structured Query Language(SQL) is used to query databases and access useful
information. Queries are SQL codes used to access and manipulate data in a
database. Note that querying a database does not change the data in the database.
A view is a virtual table that is the result of an SQL SELECT keyword. That is,
the data is not stored in the database but rather, the query code is stored for future
use. The benefit of this is that whenever the view is accessed, it automatically
updates in response to updates in the underlying database.
SYNTAX FOR CREATING A VIEW:
>>> CREATE VIEW view name As
>>> SELECT column names
>>> FROM table name;
DATABASE SCHEMA
A database schema is the blueprint of a database. It shows the tables in a database
along with their relationships and the type of data each field in each table can
hold.
DATABASE BEST PRACTICES
1. Table names should be lowercase and have no spaces (use underscores
instead).
2. Field names should be singular and lowercase with no spaces
3. Field names should not share the same name with the table they are in.
4. Field names should be different.
5. Unique identifiers primary keys (very often in integer) should be used to
identify records in a table to distinguish records
SQL BEST PRACTICES
1. End each query with a semicolon (;)
2. Put non-standard field names in double quotes (e.g “release year” if field
name is release year instead of release year)
3. Keywords should be capitalized
4. Use newlines and indentation when necessary.
CREATE, READ, UPDATE AND DELETE (CRUD) OPERATION IN
POSTGRESQL
To perform CRUD operation on your local PC, you can either use the graphical
user interface of PGAdmin or write queries.
Syntax:
>>> CREATE TABLE table name
(first column datatype condition,
second column datatype condition,
third column datatype
);
Example:
>>> CREATE TABLE undergrads
(std_id INT PRIMARY KEY,
std_name VARCHAR(240) NOT NULL,
std_course VARCHAR(240) NOT NULL
);
To insert records into the table, we use the syntax below:
>>> INSERT INTO table name VALUES
(first row column records separated by commas),
(second row column records separated by commas);
Example:
>>> INSERT INTO undergrads VALUES
(1, “Ram Singh”, “BA in Hindi”)
(2, “Raj Singh”, “BCA”);
To read the values in the table, we use the SELECT keyword
>>> SELECT
column name 1,
column name 2
>>> FROM table name;
OR
>>> SELECT *
>>> FROM table name;
Note: the asterisk tells SQL to select all columns
To update any record, use the following syntax:
>>> UPDATE table name
>>> SET column name = new value
>>> WHERE condition;
Example:
UPDATE undergrads
SET std_course = “BA in English”, std_name = “Michael Greene”
WHERE std_id = 1;
To delete records use the syntax:
DELETE FROM table name
WHERE condition;
Example:
DELETE FROM undergrads
WHERE std_id = 2
SORTING AND FILTERING DATA
Sorting data set in a specific order in SQL is done by using the ORDER By
Keyword Filtering data is done by using the WHERE keyword as seen above.
Syntax:
>>> SELECT column names separated by commas
>>> FROM table name
>>> ORDER BY column name;
By default SQL sorts the output in ascending order. We could specify the order we
want by using ASC or DESC keywords at the end of the column name after
ORDER BY. We could also sort on two or more field names.
Examples:
>>> SELECT std_name
>>> FROM undergrads
>>> ORDER BY std_name DESC
>>> SELECT std_name
>>> FROM undergrads
>>> ORDER BY std_name ASC, std_course;
Important keywords that can be used with WHERE Keyword are:
1. AND: This keyword is used when we want two or more conditions to be true
for filtering
2. OR: this keyword is used when you want either conditions to be true for
filtering
3. BETWEEN : This keyword is used for filtering values within a specified
range
4. IN: This keyword is used with WHERE Keyword to avoid multiple OR
Keywords
Examples:
>>> SELECT title
>>> FROM movies
>>> WHERE ( release_year = 1994 OR release_year = 1990)
>>> AND ( certification = “PG” OR certification = “R”)
>>> SELECT title
>>> FROM
>>> WHERE release_year BETWEEN 1994 AND 2000;
Note: The beginning and end of the range are inclusive
>>> SELECT title
>>> FROM movies
>>> WHERE release_year IN (1993, 1994, 1995, 2000)
LIKE & NOT LIKE
LIKE keyword is used to search for patterns in records. This is done by using
wildcards as placeholders. The wildcards in PostgreSQL are:
1. The % wildcards which matches zero, one or more characters
2. The _ wildcard which matches a single character
Examples:
>>> SELECT name
>>> FROM companies
>>> WHERE name LIKE “Data %”;
>>> /* Will return names like Data, DataC, DataCamp, DataMind etc.*/
>>> SELECT name
>>> FROM companies
>>> WHERE name NOT LIKE “DataC_mp”;
>>> /* Will return everything except DataCamp*/
IS NULL & IS NOT NULL
NULL represents missing values. You can check NULL values using the NULL
keywords.
Example
>>> SELECT *
>>> FROM people
>>> WHERE birthday IS NULL
We also use operators in filtering. SQL operators includes:
= Equal to
<> Not equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
AGGREGATING DATA
The Aggregate function in SQL helps to calculate the data aggregates of fields in
the database table.
AVG (): calculate the average of a field
Example:
>>> SELECT AVG (budget)
>>> FROM movies
SUM (): calculate the sum
MIN (): finds the minimum value
MAX (): finds the maximum value
ROUND (): rounds a values to the specified placeholder
Example:
>>> SELECT ROUND (AVG(BUDGET), 2)
>>> FROM movies;
>>> /* this will round up the average to two decimals*/
>>> SELECT ROUND (AVG(budget), -2)
>>> FROM movies;
>>> /* this will round up the average to the nearest ten*/
ALIASING AND LIMIT IN POSTGRESQL
Aliasing is often used with aggregates functions. Aliasing is done using the AS
keyword. Also, we could limit the number of rows our query returns by using the
LIMIT keyword.
Example:
>>> SELECT MAX(budget) As max_budget MAX(duration) As max_duration
>>> FROM movies
>>> LIMIT 10;
Note: Without Aliases, the query will return two columns with same name of
“max”
GROUPING DATA
Sometimes, we would want to group our output based on categories. This is done
using the GROUP BY keyword. It is important to note that when grouping data, an
aggregate must be among the field names in the SELECT clause.
Examples
>>> SELECT sex, COUNT(*)
>>> FROM employees
>>> GROUP BY sex;
FILTERING WITH AGGREGATES
In SQL, aggregate functions cannot be used in WHERE clauses, instead we use
them with the HAVING keywords.
Example
>>> SELECT realease_year, COUNT(title)
>>> FROM movies
>>> GROUP BY release_year
>>> HAVING COUNT(title) > 10;
ORDER OF QUERY EXECUTION
Unlike some programming languages, SQL is not processed by line number. The
order of execution of an SQL query is as follows:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT
JOINING DATA IN SQL
Introduction to [INNER] Join
INNER Join only includes records in which the key is in both tables. We
look for matches in the right table corresponding to all entries in the key field.
Left table Right table
id val id val
1 L1 →→→→→→→→ 1 R1
2 L2 4 R2
3 L3 5 R3
4 L4 6 R4
↓
INNER JOIN
L_id L_val R_val
1 L1 R1
4 L4 R2
Basic Syntax of INNER JOIN:
SELECT*
FROM left_table
INNER JOIN right_table
ON left_table: id= right_table:id;
NB: TO SELECT a field(column) in your query that appears in multiple table,
you’ll need to identify which table/ table alias you’re referring to, by using dot(.)
e.g [Link]
INNER JOIN via USING
When the key field you’d like to join on is the same name in both tables, you
can use a USING clause instead of ON clause. Note that parentheses are required
around the key field when using USING
Self joins, just in CASE
Self joins are used to compare values from part of a table to other values
from within the same table. Self join is simply joining a table with itself.
CASE is however, a simplified way of doing WHEN_THEN_ELSE_
statements in SQL.
SELECT country_code, size,
CASE WHEN size > 50000000
THEN “large”
WHEN size > 100000
THEN “medium:
ELSE “small” END
As pop_size_group
From populations;
LEFT and RIGHT JOINs
Outer joins reach out to another table while keeping all the records of the
original table. Whereas, inner joins keep records in both tables.
There are three(3) types of outer joins
1. LEFT JOIN
2. RIGHT JOIN
3. FULL JOIN
LEFT JOIN motes these columns on the left table, that do not have a match
on the key field in the right table.
id val
id val
1 R1
1 L1
4 R2
2 L2
5 R3
3 L3
6 R4
4 L4
RIGHT JOIN is similar to the left join, except that it is the reverse i.e matching
entries in the id of the right table to the id of the left table.
FULL JOINS
A FULL JOIN combines a LEFT JOIN and a RIGHT JOIN
Crossing the Rubicon
CROSS JOIN creates all possible combinations of two tables.
NB: In cross joins, we do not need to match on any key.
State of the UNION
Set Theory Venn Diagram
UNIONS do not do lookups like JOINS do. They simply stack records together.
NB: In using UNION fields must be of the same data type.
Syntax:
SELECT prime_minister As leader, country
From Prime_ministers
UNION
SELECT monarch, country
FROM monarch
ORDER BY country;
Semi-joins and Anti-joins
The six joins(inner, self, left, right, full, cross) previously taught are all
additive joins, that is: they add columns to the original left table.
Semi-joins and Anti-joins use a right table to determine which records we
would keep in the left table.
Semi-join chooses the record in the first table, where condition is met in the
second table.
Anti-join chooses the record in the first table, where condition is not met in
the second table.
Semi-and Anti-joins are examples of subqueries.
Semi-Join
SELECT president, country, continent
FROM presidents
WHERE country IN
(SELECT name
FROM states
WHERE indep-year <1800);
Anti-Join
SELECT president, country, continent
FROM presidents
WHERE continent LIKE “%Amneza”
AND country NOT IN
(SELECT name
FROM states
WHERE indep-year <1800);
Subqueries inside WHERE and SELECT clauses
A subquery is a nested query. As seen previously a subquery can be used
inside a WHERE clause.
Subqueries can be used inside a SELECT clause as shown below:
SELECT DISTINCT continent,
(SELECT COUNT(*)
FROM states
WHERE prime_minsters, continent=[Link]>
As countries_num
FROM prime_ministers
Subqueries could also be used inside FROM clauses
Best Practices for subqueries
1. Properly format your queries
2. Annotate your queries with comments
3. Properly indent all information withins a query
4. Properly filter each sub query.
DATA MANIPULATION IN SQL
-Correlated subqueries are special kinds of subquery that use values from the outer
query. To generate a result. The subquery is re-run for every row generated in the
final data set.
Correlated subqueries are used for advanced joining, filtering and evaluating
data.
Example;
Which match stages tend to have a higher than average number of goals scored?
SELECT
[Link],
ROUND(s.avg_goals,2) AS avg_goal,
SELECT AVG (home_goal+away_goal)
FROM match
WHERE season = ‘2012/2013”) AS overall_avg
FROM
(SELECT
stage,
AVG(home_goal+away_goal) AS avg_goals
FROM match
WHERE season = ‘2012/2013’
GROUP BY stage) AS s
WHERE s.avg_goals>
(SELECT AVG (home_goal+away_goal)
FROM match as m } Correlated subquery
WHERE [Link] > [Link]);
Simple subquery Correlated subquery
1. Can be run independently from Dependent on main query to execute
the main query
2. Evaluate once in the whole query Evaluated in loops, therefore
significantly slows down query runtime
-Common Table Expressions
When adding subqueries, query complexity increases quickly and
information can become difficult to track. The solution to this common table
expressions,
Common Table Expressions (CTEs) are a special type of subquery that are
declared ahead of your main query. They are named using the WITH statement,
and referenced in the FROM statement.
Example;
SELECT
[Link] AS country,
COUNT([Link]) AS matches
FROM COUNTRY AS c
INNER JOIN(
SELECT country_id, id
FROM match
WHERE (home_goal+away_goal) >=10) AS s
ON [Link] = s.country_id
GROUP BY country;
The above sequence can be written with CTE as:
WITH s AS(
SELECT country_id, id
FROM match }CTE
WHERE (home_goal+away_goal) >= 10
)
SELECT
[Link] AS country,
COUNT ([Link]) AS matches
FROM country AS c
INNER JOIN s
ON [Link] = s.country_id
GROUP BY COUNTRY;
When using multiple CTEs, properly names them and separate with commas
Why use CTEs?
1. Executed once this:
- Stored in memory
- Improves query performance
2. Improving organization of queries
3. Referencing other CTEs
4. Referencing itself (SELF JOIN)
- Deciding on Techniques to use
As seen we could use joins to perform subquery, and CTE operations,
but these techniques are not identical.
Joins Correlated subquery Multiple/Nested subqueries
Combines two or Matches subqueries and Used when there is a need for
more tables, and are tables(another subquery. Multi-step transformations.
mostly limited to This avoids limits of This helps to improve
simple operations. joins(namely: you can’t accuracy and reproductivity.
join two separate columns
in one table to a single
column in another at a
time), However, remember
that correlated subqueries
require high processing
time.
CTEs
● Allows for specifically organized subqueries and every reference to
other CTEs
So which do I use?
● Depends on your database or question.
● The technique that best allows you to
-use and reuse your queries.
-generate clear and accurate results.
Different Use Cases
- Joins : 2+tables (What us the total sale per employee)
- Correlated subqueries : Who does the employee report to in a company?
- Multiple/Nested subqueries : What is the average deal size closed by each
sales representative in the quarter?
- CTEs : How did the marketing, sales, growth and engineering teams perform
on key metrics?