SQL Basics.sql

-- all
SELECT * FROM results;

-- specific columns
SELECT first_name, last_name, email FROM results;

-- alias
SELECT name AS "Product Name", description AS "Product Description" FROM products;

-- filter by condition
SELECT * FROM contacts WHERE first_name = "Andrew";
SELECT * FROM contacts WHERE first_name != "Andrew";

-- filter by comparing values
SELECT first_name, last_name FROM users WHERE date_of_birth < '1998-12-01';
SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015;
SELECT name, description FROM products WHERE price > 9.99;
SELECT title FROM movies WHERE release_year >= 2000;


-- filter by one or more condition
SELECT username FROM users WHERE last_name = "Chalkley" AND first_name = "Andrew";
SELECT * FROM products WHERE category = "Games Consoles" AND price < 400;
SELECT * FROM movies WHERE title = "The Matrix" OR title = "The Matrix Reloaded" OR title = "The Matrix Revolutions";
SELECT country FROM countries WHERE population < 1000000 OR population > 100000000;

-- filter by dates
SELECT first_name, last_name FROM users WHERE date_of_birth < '1998-12-01';
SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015;
SELECT name, description FROM products WHERE price > 9.99;
SELECT title FROM movies WHERE release_year >= 2000;

-- searching within a set of values
SELECT answer FROM answers WHERE id IN (7, 42);
SELECT * FROM products WHERE category NOT IN ("Electronics");
SELECT title FROM courses WHERE topic NOT IN ("SQL", "NoSQL");

-- searching within a range of values
SELECT * FROM movies WHERE release_year BETWEEN 2000 AND 2010;
SELECT name, description FROM products WHERE price BETWEEN 9.99 AND 19.99;
SELECT name, appointment_date FROM appointments WHERE appointment_date BETWEEN "2015-01-01" AND "2015-01-07";

-- finding data that matches a pattern
SELECT title FROM books WHERE title LIKE "Harry Potter%Fire";
SELECT title FROM movies WHERE title LIKE "Alien%";
SELECT * FROM contacts WHERE first_name LIKE "%drew";
SELECT * FROM books WHERE title LIKE "%Brief History%";

-- filtering out or finding missing information
SELECT * FROM loans WHERE return_by > "2015-12-18" AND returned_on IS NULL;
SELECT * FROM loans WHERE return_by > "2015-12-18" AND returned_on IS NOT NULL;

-- ordering by a single column criteria
SELECT * FROM books ORDER BY title ASC;
SELECT * FROM products WHERE name = "Sonic T-Shirt" ORDER BY stock_count DESC;
SELECT * FROM users ORDER BY signed_up_on DESC;
SELECT * FROM countries ORDER BY population DESC;

-- ordering by multiple column criteria
SELECT * FROM books ORDER BY genre ASC, title ASC;
SELECT * FROM books ORDER BY genre ASC, year_published DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;

-- limit numbers of records
SELECT * FROM books LIMIT 5;

-- offset records
SELECT * FROM books LIMIT 5 OFFSET 4;

-- offset records
SELECT * FROM books LIMIT 5 OFFSET 4;

-- upper case records
SELECT UPPER(first_name), email FROM users;

-- lower case records
SELECT LOWER(first_name), email FROM users;

-- concat records
SELECT first_name || " " || last_name AS "Full Name", email FROM users;
SELECT CONCAT(first_name, " ", last_name) AS "Full Name", email FROM users;

-- lenght of a record
SELECT LENGTH(first_name) FROM books;

-- substr a record
SELECT SUBSTR(bio, 1, 30) FROM authors;

-- replace a text
SELECT REPLACE(country, "México", "MEX") FROM users;

-- count
-- -- distinct
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT category) FROM users;

-- group by
SELECT * FROM books GROUP BY genre;
SELECT COUNT(*) FROM books GROUP BY genre;

-- sum
-- -- having
SELECT SUM(total) FROM orders;
SELECT SUM(total) AS total_spend FROM orders GROUP BY user_id HAVING total_spend > 250;

-- average
SELECT AVG(cost) FROM products;
SELECT AVG(cost) FROM products GROUP BY user_id;

-- maximum value
SELECT MAX(cost) FROM products;
SELECT MAX(cost) FROM products GROUP BY user_id;

-- minimum value
SELECT MIN(cost) FROM products;
SELECT MIN(cost) FROM products GROUP BY user_id;

-- + - * /
SELECT ROUND(cost * 1.16,2) AS IVA FROM products;