sql-commands.md

SQL Commands

From SQL by Repetition

Schema

Comment

/* open comment

*/ close comment

/* this is what a SQL comment looks like */

Select All Columns

SELECT * FROM table_name;

Example - Return all info from artists table

SELECT * FROM artists;

Select One Column

SELECT column_name FROM table_name;

Example - Return only the playlists name from the playlists table

SELECT name FROM playlists;

Select Multiple Columns

SELECT first_column_name, second_column_name FROM table_name;

Example - Return first name, last name, title, and email address from the employees table

SELECT first_name, last_name, title, email FROM employees;

Filter Data Matching Exact Condition

SELECT * FROM table_name WHERE column = condition;

Example 1 - In anticipation of a sale, you need to identify all tracks that are $1.99

SELECT * from tracks WHERE unit_price = 1.99;

Example 2 - There's a promotion for your customers who live in France. Return the first name, last name, and phone number of the customers you'll add to the telemarketing list.

SELECT first_name, last_name, phone FROM customers WHERE country = 'France';

Calculation Functions

1. COUNT()

SELECT COUNT(*) FROM table_name;

Example - CEO wants to know how many customers there are only in Arizona. Return the number of customers in the state of AZ.

SELECT COUNT(*) FROM customers WHERE state = 'AZ';

2. AVG()

SELECT AVG(column_name) FROM table_name;

Example - Return the average length in milliseconds of all tracks.

SELECT AVG(milliseconds) FROM tracks;

3. MIN()

Example - Return the shortest track in the collection

SELECT MIN(milliseconds) FROM tracks;

4. MAX()

Example - Return the longest track in the collection

SELECT MAX(milliseconds) FROM tracks;

Filter Data With Greater/Less Than Condition

SELECT column_name FROM table_name WHERE column_name < condition;

Example 1 - Find all employees born before Feburary 18th 1962. ('1962-02-18')

SELECT * FROM employees WHERE birth_date < '1962-02-18';

LENGTH()

Example - Find all Artists who have a name shorter than 5 characters

SELECT * FROM artists WHERE LENGTH(name) < 5;

Filter Data With Partial Text Match

% - WILDCARD

SELECT column_name FROM table_name WHERE column_name LIKE '%condition%';

Example - Return all artists that have a name that starts with a 'S'

SELECT * FROM artists WHERE name LIKE 'S%