In this video we learn about writing queries involving a single table.
To follow along, you will need to download and install 2 sql files:
SELECT <columns> FROM <table> WHERE <condition>;
SELECT * FROM books WHERE author = 'Ann Mulkern';
SELECT author, title FROM books WHERE publisher = 'Wisdom';
SELECT movie FROM movies WHERE tomatorating > 90;
SELECT <columns> FROM <table> WHERE <condition>;
<condition> = <columnName> = <value>
<condition> = <condition> AND <condition>
<condition> = <condition> OR <condition>
SELECT movie FROM tomato WHERE firstname = 'Anne' AND lastname = 'Hathaway';
SELECT DISTINCT <columns> FROM <table> WHERE <condition>;
SELECT DISTINCT firstname, lastname from tomato;
<condition> = <columnName> LIKE <value>
<condition> = <columnName> ILIKE <value> *case insensitive*
SELECT movie FROM tomato WHERE movie LIKE 'S%';
SELECT movie FROM tomato WHERE movie ILIKE '%avenger%';
Patterns
| Pattern | Description |
|---|---|
A% |
matches any string that start with an ‘A’ |
%ed |
matches any string that ends with ‘ed’ |
A%ed |
matches any string that starts with ‘A’ and ends with ‘ed’ |
%z% |
matc hes any string that contains a ‘z’ (can start with, end with, or contain a z) |
<condition> = <columnName> IS NULL
<condition> = <columnName> IS NOT NULL
SELECT movie FROM tomato where release-date IS NULL;
SELECT <columns> FROM <table> WHERE <condition> ORDER BY <columnNames>;
SELECT movie FROM tomato ORDER BY movie;
SELECT firstname, lastname FROM tomato WHERE movie = 'Avengers' ORDER BY lastname, firstname;