SQL Cheat Sheet: Fundamentals SQL Intermediate:: Performing Calculations With SQL Joins & Complex Queries

SQL Cheat Sheet: Fundamentals SQL Intermediate:
Performing calculations with SQL Joins & Complex Queries

Many of these examples use table and column names from
Performing a single calculation: the real SQL databases that learners work with in our
SELECT 1320+17; interactive SQL courses. For more information, sign up for a
free account and try one out!
Performing multiple calculations:
SELECT 1320+17, 1340-3, 7*191, 8022/6;
Joining data in SQL:
Performing calculations with multiple numbers: Joining tables with INNER JOIN:
SELECT 1*2*3, 1+2+3; SELECT column_name_1, column_name_2 FROM table_name_1
INNER JOIN table_name_2 ON table_name_1.column_name_1
= table_name_2.column_name_1;
Renaming results:
SELECT 2*3 AS mult, 1+2+3 AS nice_sum;
Joining tables using a LEFT JOIN:
SELECT * FROM facts
Selecting tables, columns, and rows: LEFT JOIN cities ON cities.facts_id = facts.id;
Remember: The order of clauses matters in SQL. SQL Joining tables using a RIGHT JOIN:
uses the following order of precedence: FROM, SELECT,
SELECT f.name country, c.name city
LIMIT. FROM cities c
RIGHT JOIN facts f ON f.id = c.facts;
Display the whole table:
SELECT * Joining tables using a FULL OUTER JOIN:
FROM table_name;
SELECT f.name country, c.name city
FROM cities c
Select specific columns from a table:
FULL OUTER JOIN facts f ON f.id = c.facts_id;
SELECT column_name_1, column_name_2
FROM table_name;
Sorting a column without specifying a column name:
Display the first 10 rows on a table: SELECT name, migration_rate FROM FACTS
ORDER BY 2 desc; -- 2 refers to migration_rate column
SELECT *
FROM table_name;
LIMIT 10; Using a join within a subquery, with a limit:
SELECT c.name capital_city, f.name country
FROM facts f
Adding comments to your SQL queries INNER JOIN (
SELECT * FROM cities
WHERE capital = 1
) c ON c.facts_id = f.id
Adding single-line comments:
INNER 10
-- First comment
SELECT column_1, column_2, column_3 -- Second comment
FROM table_name; -- Third comment Joining data from more than two tables:
SELECT [column_names] FROM [table_name_one]
Adding block comments: [join_type] JOIN [table_name_two] ON [join_constraint]
[join_type] JOIN [table_name_three] ON [join_constraint]
/* ...
This comment ...
spans over ...
multiple lines [join_type] JOIN [table_name_three] ON [join_constraint]
*/
SELECT column_1, column_2, column_3
FROM table_name;
Other common SQL operations:
Combining columns into a single column: Dropping a view

SELECT
album_id, DROP VIEW chinook.customer_2;
artist_id,
"album id is " || album_id col_1,
"artist id is " || artist_id col2,
album_id || artist_id col3 Selecting rows that occur in one or more SELECT statements:
FROM album LIMIT 3;
[select_statement_one]
UNION
[select_statement_two];
Matching part of a string:
SELECT
first_name, Selecting rows that occur in both SELECT statements:
last_name,
phone SELECT * from customer_usa
FROM customer INTERSECT
WHERE first_name LIKE "%Jen%"; SELECT * from customer_gt_90_dollars;
Selecting rows that occur in the first SELECT statement but

Using if/then logic in SQL with CASE:
not the second SELECT statement:
CASE
WHEN [comparison_1] THEN [value_1] SELECT * from customer_usa
WHEN [comparison_2] THEN [value_2] EXCEPT
ELSE [value_3] SELECT * from customer_gt_90_dollars;
END
AS [new_column_name]
Chaining WITH statements:
WITH
Using the WITH clause: usa AS
(
WITH track_info AS SELECT * FROM customer
( WHERE country = "USA"
SELECT ),
t.name, last_name_g AS
ar.name artist, (
al.title album_name, SELECT * FROM usa
FROM track t WHERE last_name LIKE "G%"
INNER JOIN album al ON al.album_id = t.album_id ),
INNER JOIN artist ar ON ar.artist_id = al.artist_id state_ca AS
) (
SELECT * FROM track_info SELECT * FROM last_name_g
WHERE album_name = "Jagged Little Pill"; WHERE state = "CA"
)
SELECT
first_name,
Creating a view: last_name,
country,
CREATE VIEW chinook.customer_2 AS state
SELECT * FROM chinook.customer; FROM state_ca
Important Concepts and Resources:

Reserved words
Reserved words are words that cannot be used as identifiers (such as variable names or function names) in a programming
language, because they have a specific meaning in the language itself. Here is a list of reserved words in SQL.

SQL Cheat Sheet: Fundamentals SQL Intermediate:: Performing Calculations With SQL Joins & Complex Queries

Uploaded by

Copyright:

Available Formats

SQL Cheat Sheet: Fundamentals SQL Intermediate:: Performing Calculations With SQL Joins & Complex Queries

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Cheat Sheet: Fundamentals SQL Intermediate:: Performing Calculations With SQL Joins & Complex Queries

Uploaded by

Copyright:

Available Formats

SQL Cheat Sheet: Fundamentals SQL Intermediate:

Performing calculations with SQL Joins & Complex Queries

Combining columns into a single column: Dropping a view

Selecting rows that occur in the ﬁrst SELECT statement but

Important Concepts and Resources:

You might also like