Embedded Files
1) Overview:
Relational Databases.
Querying
Filtering Records.
Aggregate Functions.
Sorting and Grouping.
Inner Joins.
Outer Joins, Cross Joins, and Self Joins.
Set Theory for SQL Joins
Sub queries.
2) Details:
Relational Databases
Databases.
Databases: Store data.
Relational databases: Define relationships between tables of data inside the database.
Adv: Store more data and encryption.
SQL:
Short for Structured Query Language.
The most widely used programming language for databases.
Tables.
Organize into rows and columns.
Table name rules: be lowercase, no space, refer to a collective group, or be plural.
Data.
A string is a sequence of characters such as letters and punctuation. (VARCHAR).
Intergers store whole numbers (INT).
Floats store numbers that include a fractional part (NUMERIC).
DATA SCHEMAS: Show each datatype that each field holds.
Server stores database.
Querying
Querries.
Keywords: Reserved words for operations: SELECT, FROM.
SELECT card_num, name
FROM patrons;
SQL flavors.
Vast majority of keywords are the same
PostgreSQL: Free and open-source relational database system.
SQL Server: by Microsoft.
Filtering Records
Filtering numbers:
WHERE filtering clause.
WHERE color = 'red';.
<>: not equal to.
=: equal to.
SELECT COUNT(*) AS films_over_100K_votes
Multiple Criteria:
AND
OR
BETWEEN: WHERE buttons BETWEEN 1 AND 5;
Filtering text:
LIKE: match.
NOT LIKE: not match.
IN: specify several values to match.
% match zero, one, or many characters.
_ match a single character.
WHERE name LIKE 'Ev_';
WHERE name IN (PHU, Kha, Phuc)
NULL values:
No value (missing).
None.
IS NULL
IS NOT NULL
Aggregate Functions
Summaring data.
MIN(), MAX(), AVG(), SUM(), COUNT()
SELECT MIN()
Summaryizing subsets.
Using WHERE with aggregate functions
ROUND()
Sorting and Grouping
Sorting.
ORDER BY
ASC: ascending.
DESC: descending.
ORDER BY budget ASC;
Grouping.
GROUP BY
GROUP BY certification;
Filtering grouped data.
HAVING
GROUP BY release_year HAVING COUNT(title) >10;
Inner Joins, Outer Joins, Cross Joins, and Self Joins
INNER JOIN
Returns only the rows that have matching values in both tables.
It filters out rows with no match in the joined table.
Retrieve records that have related data in both tables.
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
LEFT JOIN (LEFT OUTER JOIN)
Returns rows from the left table (first mentioned table) and the matched rows from the right table.
If there is no match in the right table, NULL values are returned from the columns of the right table.
It ensures that all records from the left table are included, regardless of whether they match the right table.
RIGHT JOIN (RIGHT OUTER JOIN)
Opposite to LEFT JOIN.
FULL OUT JOIN
Returns all rows when there is a match in either the left or the right table.
If there is no match in either table, NULL values are returned for the columns of the table that does not have a match.
Combine both LEFT JOIN and RIGHT JOIN.
Ins and Outs of Inner Join
Inner join of presidents and prime_ministers, joining on country
FROM prime_misters
INNER JOIN presidents
ON prime_ministers.country = presidents.country
Inner join of presidents and prime_ministers, joining on country
FROM prime_ministers AS p1
INNER JOIN presidents AS p2
ON p1.country = p2.country
Defining relationships.
One-to-many relationships
One-to-one relationships
Many-to-many relationships
Left and Right Joins
LEFT JOIN
RIGHT JOIN
USING: The same as ON when two columns have the same name
USING (code)
Full Joins
FULL JOIN combines a LEFT JOIN and a RIGHT JOIN
If missing values ==> NULL
FULL JOIN
USING
Keyword: FULL OUTER JOIN
Self Joins
SELF JOINs are tables joined with themselves.
They can be used to compare parts of the same table
Set Theory for SQL Joins
Venn diagram and Set Theory
UNION: Reject duplicates.
UNION ALL: Union all values including duplicates.
Intersect
INTERSECT: keep values appearing in both tables.
Subqueries
Subqueries with Semi Joins and Anti Joins
Subqueries inside WHERE
All semi-joins and anti-joins we have seen included a subquery in WHERE.
WHERE some_field IN (SELECT ... FROM ... WHERE ...)
Subqueries inside SELECT
SELECT DISTINCT ..., (SELECT ... FROM ... WHERE ...)
Subqueries inside FROM
Page updated
Google Sites
Report abuse