SQL Exercises

After a long exhausting day of work spending 10 hours learning Rails to understand the constructs of RSpec, what better way to finish it off than another 3 hours of learning SQL to query the database that I am still currently learning about ;-). Here are some basic notes about SQL Queries:

Basis Construct: SELECT * FROM table_name

SELECT: selects columns from the table that was selected. * means all columns, otherwise you can do column_1, column_2, column_3, etc.

FROM: selects what table the data will be pulled from. FROM table_name

ORDER BY: This allows you to order tables by specific columns. It default on ascending order which works for both numbers and letters either from 1 or a. If you type in DESC at the end it will go in descending order.

orders table by first_name in descending order
example: SELECT * FROM employees ORDER BY first_name DESC

WHERE: Where allows you multiple options including comparison operators as well as using LIKE which allows you to choose letters and words that match.

LIKE: 'a%' finds all billing_city cells that START with 'a'. '%b' finds all billing_city cells that END with 'b'. '%ville%' finds all billing_city that contain 'ville'.


selects all invoices where billing_city contains the word Redmond
SELECT * FROM invoices WHERE billing_city LIKE '%Redmond%'


IS NULL: This can be used after a WHERE statement to find null values in columns

SELECT * FROM tracks WHERE composer IS NULL

Multiple LIKE: SELECT * FROM invoices WHERE (billing_city LIKE 'Cupertino' OR billing_city LIKE 'Mountain View')

Count Columns: SELECT COUNT(billing_city) FROM invoices

Select Multiple Songs: The convention is NO spaces between columns when selecting
SELECT first_name,last_name,birthday FROM students