sql description

SQL

== SQL Snippets ==

-- Lesson1

-- show all
SELECT * FROM {{table_name}};
-- select more then 1 column
SELECT name, imdb_rating FROM movies;

-- create table
CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);

-- insert into table
INSERT INTO celebs (id, name, age) VALUES (1, 'test', 21);

-- show value from table
SELECT name FROM celebs;

-- update VALUES
UPDATE celebs SET age = 22 WHERE id = 1; 

-- adding new columns to the table, changes an existing table.
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT; 

-- Delete all of the rows that have a NULL value in the twitter column. Above SELECT type
DELETE FROM celebs WHERE twitter_handle IS NULL;

-- sofar
CREATE TABLE creates a new table.
INSERT INTO adds a new row to a table.
SELECT queries data from a table.
UPDATE edits a row in a table.
ALTER TABLE changes an existing table.
DELETE FROM deletes rows from a table.

-- Lesson2

-- SELECT DISTINCT is used to return unique values in the result set. It filters out all duplicate values. Here, the result set lists each genre in the movies table exactly once.
SELECT DISTINCT genre FROM movies;

-- select by filter column
SELECT * FROM movies WHERE imdb_rating > 8;
filters: = equals, != not equals, > greater than, < less than, >= greater than or equal to, <= less than or equal to

-- Like
SELECT * FROM movies WHERE name LIKE 'Se_en'; -- SeVen, Se7en
SELECT * FROM movies WHERE name LIKE '%man%'; -- spider-man, iron man, man of steel
SELECT * FROM movies WHERE name LIKE 'a%'; -- everything starts with an 'a'
LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.
A% matches all movies with names that begin with "A"
%a matches all movies that end with "a"

-- another examples with BETWEEN
SELECT * FROM movies WHERE name BETWEEN 'A' AND 'J'; -- name starts betwen a to j
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000; -- year between 1990 and 2000
-- filter between years and genre
SELECT * FROM movies WHERE year BETWEEN 1990 AND 2000 AND genre = 'comedy';

-- AND
AND is an operator that combines two conditions. Both conditions must be true for the row to be included in the result set.
Here, we use the AND operator to only return movies made between 1990 and 2000 that are also comedies.
-- OR
The OR operator evaluates each condition separately and if any of the conditions are true then the row is added to the result set. example:
SELECT * FROM movies WHERE genre = 'comedy' OR year < 1980;

-- DESC /ASC order by
ORDER BY is a clause that indicates you want to sort the result set by a particular column either alphabetically or numerically. example:
SELECT * FROM movies ORDER BY imdb_rating DESC;
SELECT * FROM movies ORDER BY imdb_rating ASC;

-- LIMIT
LIMIT is a clause that lets you specify the maximum number of rows the result set will have. Here, we specify that the result set can not have more than three rows.

-- sofar
SELECT is the clause you use every time you want to query information from a database.
WHERE is a popular command that lets you filter the results of the query based on conditions that you specify.
LIKE and BETWEEN are special operators that can be used in a WHERE clause
AND and OR are special operators that you can use with WHERE to filter the query on two or more conditions.
ORDER BY lets you sort the results of the query in either ascending or descending order.
LIMIT lets you specify the maximum number of rows that the query will return. This is especially important in large tables that have thousands or even millions of rows.

-- Lesson3
SELECT COUNT(*) FROM fake_apps;
COUNT() is a function that takes the name of a column as an argument and counts the number of rows where the column is not NULL. Here, we want to count every row so we pass * as an argument.

-- show the group prices and their counts
SELECT price, COUNT(*) FROM fake_apps GROUP BY price;
GROUP BY is a clause in SQL that is only used with aggregate functions. It is used in collaboration with the SELECT statement to arrange identical data into groups.

-- SUM
SUM() is a function that takes the name of a column as an argument and returns the sum of all the values in that column. Here, it adds all the values in the downloads column.

SELECT category, SUM(downloads) FROM fake_apps GROUP BY category; --group by
SELECT MAX(downloads) FROM fake_apps; --get max value

-- MIN/MAX
Return the names of the most downloaded apps in each category.
SELECT name, category, MAX(downloads) FROM fake_apps GROUP BY category;

-- AVG
SELECT AVG(downloads) FROM fake_apps;
The AVG() function works by taking a column name as an argument and returns the average value for that column.

-- ROUND
ROUND() is a function that takes a column name and an integer as an argument. It rounds the values in the column to the number of decimal places specified by the integer.
Here, we pass the column AVG(downloads) and 2 as arguments. SQL first calculates the average for each price and then rounds the result to two decimal places in the result set. example:
SELECT price, ROUND(AVG(downloads), 2) FROM fake_apps GROUP BY price;

-- TLDR
COUNT takes the name of a column(s) as an argument and counts the number of rows where the value(s) is not NULL.
GROUP BY is a clause used with aggregate functions to combine data from one or more columns.
SUM() takes the column name as an argument and returns the sum of all the values in that column.
MAX() takes the column name as an argument and returns the largest value in that column.
MIN() takes the column name as an argument and returns the smallest value in that column.
AVG() takes a column name as an argument and returns the average value for that column.
ROUND() takes two arguments, a column name and the number of decimal places to round the values in that column.

-- Lesson4

-- select values from diff tables
SELECT albums.name, albums.year, artists.name FROM albums, artists;

-- JOIN
-- An inner join will combine rows from different tables if the join condition is true
SELECT * FROM albums JOIN artists ON albums.artist_id = artists.id;

-- AS
AS is a keyword in SQL that allows you to rename a column or table using an alias
SELECT albums.name AS 'Album', albums.year, artists.name AS 'Artist' FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year > 1980;

-- TLDR
Primary Key is a column that serves a unique identifier for row in the table. Values in this column must be unique and cannot be NULL.
Foreign Key is a column that contains the primary key to another table in the database. It is used to identify a particular row in the referenced table.
Joins are used in SQL to combine data from multiple tables.
INNER JOIN will combine rows from different tables if the join condition is true.
LEFT OUTER JOIN will return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.
AS is a keyword in SQL that allows you to rename a column or table in the result set using an alias.