Scribd
Upload
202 views

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

This document provides an overview of SQL fundamentals and intermediate concepts including: - Performing calculations with SQL such as addition, multiplication, and renaming columns - Selecting data from tables including specific columns, rows with limits, and entire tables - Joining data from multiple tables using INNER, LEFT, RIGHT, and FULL OUTER joins - Sorting and filtering query results with ORDER BY and WHERE clauses - Comments in SQL queries - Other common operations like combining columns, string matching, conditional logic, views, set operations, and chaining queries - Important concepts like reserved words that have special meanings in SQL The document covers core SQL topics through examples and explains how to perform more advanced queries

Uploaded by

shradha luintel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
202 views

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

This document provides an overview of SQL fundamentals and intermediate concepts including: - Performing calculations with SQL such as addition, multiplication, and renaming columns - Selecting data from tables including specific columns, rows with limits, and entire tables - Joining data from multiple tables using INNER, LEFT, RIGHT, and FULL OUTER joins - Sorting and filtering query results with ORDER BY and WHERE clauses - Comments in SQL queries - Other common operations like combining columns, string matching, conditional logic, views, set operations, and chaining queries - Important concepts like reserved words that have special meanings in SQL The document covers core SQL topics through examples and explains how to perform more advanced queries

Uploaded by

shradha luintel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

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.

You might also like