SQL basics

• SQL is a programming language that talks with the database
• Every sql statement myst end with a semi-colon ;
• Uppercase for sql keywords like SELECT, FROM is a convention, recommended but not required.
• We search columns first and tables next
• * means all
• WHERE let’s you specify conditions to filter data. You use conditional operators to build your conditions
• AND and OR let you specify multiple conditions. AND means all conditions must be met, OR means any condition can be met
• ORDER BY is for sorting data in ASC (default) or DESC order
• When adding and deleting data, make sure to provide a WHERE clause, or it’ll mess with the entire table or columns.

Getting and filtering existing data

• Keywords: SELECT, FROM, WHERE, AND, OR, ORDER BY
• Operators: =, >, <, <=, >=, <>

Retrieving data

We retrieve data using SELECT

 1# SELECT ___ FROM ___;
 2# SELECT columnName FROM tableName;
 3
 4# select the `title` column from the `movies` table
 5SELECT title FROM movies;
 6
 7# select data from multiple columns
 8SELECT id, title, genre, duration FROM movies;
 9
10# select all columns
11SELECT * FROM movies;

Filtering data

• To filter data we use the WHERE clause (within a SELECT statement)
• The sql command isn’t done when it finds the particular item, it continues till it’s done with all entries (e.g. If you’re looking for a movie title with an id of 2, it’ll find 2, and continue through the rest 3,4,5 and so on until it has gone through all entries, to make sure there was only one cell with id of 2)

1# SELECT ___ FROM ___ WHERE ___;
2# SELECT columnName FROM tableName WHERE condition;
3
4SELECT title FROM movies WHERE id = 2; # numbers
5SELECT * FROM movies WHERE title = 'The Kid'; # strings (match exact sequence)

Sorting data

Sort by Order

• ORDER BY clause is used to sort data in ascending ASC or descending DESC order
• ASC is default

1# SELECT ___ FROM ___ ORDER BY ____  ASC|DESC;
2
3SELECT title
4FROM movies
5ORDER by duration; # get movie titles in (default) ascending order of duration (shortest first)
6
7SELECT title
8FROM movies
9ORDER by duration DESC; # get movie titles in descending order of duration (longest first)

Sort by Range

• We can specify a range using comparison operators >, <, =, <=, >=, <>
• <> is same as !=, it means not equal to

1# SELECT ___ FROM ___ WHERE ____ > ____;
2
3SELECT * FROM movies WHERE duration > 100;
4SELECT * FROM movies WHERE duration < 100;
5SELECT * FROM movies WHERE duration >= 94;

Sort by multiple conditions

• AND and OR operators let’s you add multiple conditions
• When you use AND to combine multiple conditions, the all conditions must be met to get any results
• OR gives you results if any of the multiple conditions is met

 1SELECT title
 2FROM movies
 3WHERE id = 1
 4AND genre = 'Comedy'; # with AND, both conditions must be met
 5
 6
 7SELECT title
 8FROM movies
 9WHERE id = 1
10OR genre = 'Comedy'; # will get results if either condition is met

Managing data

Adding data

• INSERT is the keyword for adding data.
• The values must be in the respective order of column names you mention. The first column you mention will get the first value you specify. If you’re not specifying any column names, the order of values must match the order of columns in the table
• If you are inserting data into all the columns of the table, mentioning every column name is not necessary
• The order of the columns/values is very important whether you mention the column names or not.
• NULL is the data type for when there’s no value in the cell (empty cell, missing data). NULL is a placeholder for unknown data

 1# INSERT INTO ___ (___, ___) VALUES (___, ___);
 2# INSERT INTO tableName (columnName, columnName) VALUES (value, value);
 3
 4INSERT INTO movies (id, title, genre, duration)
 5VALUES (5, 'The Circus', 'Comedy', 71);
 6
 7INSERT INTO movies
 8VALUES (5, 'The Circus', 'Comedy', 71); # same as above since movies has only 4 columns
 9
10INSERT INTO movies (title, duration)
11VALUES ('The Fly', 80)

Primary Keys

• Primary keys are unique for a table. They’re never blank or empty. Once value in the column can not be the same as another value in the column.
• SQL can auto-increment the primary key for a table for new rows. Each time a row is added to the table, the key gets automatically incremented and added to the row.
• Primary Keys are usually used as reference keys, you use these to identify and get the rows. Some examples are customer_id, product_id, post_id etc.
• Primary keys are unique identifiers

Changing existing data

• UPDATE is the keyword for updating existing data
• Withut a WHERE clause, the entire column will update for all rows

 1# UPDATE ___ SET ___ = ___ (WHERE ___) 
 2# UPDATE tableName SET columnName = columnValue WHERE clause
 3
 4UPDATE movies
 5SET genre = 'Romance'
 6WHERE id = 5;
 7
 8UPDATE movies
 9SET genre = 'Comedy', duration = 70 # update multiple values
10WHERE id = 5;
11
12UPDATE movies
13SET genre = 'Romance'
14WHERE id = 5 OR id = 7; # update multiple rows since id is unique

Deleting data

• DELETE is the keyword
• WHERE can be used for further refining the statement
• If you don’t mention a WHERE clause, it’ll delete all data from the table

1# DELETE FROM ___ (WHERE ___)
2# DELETE FROM tableName (WHERE clause)
3
4DELETE FROM movies
5WHERE id = 5;
6
7DELETE FROM movies
8WHERE duration < 100;

Managing databases

• CREATE DATABASE and CREATE TABLE are the self-explanatory keyword for creating databases and tables
• DROP DATABASE is when you want to remove a database (i.e. drop it)
• Dropping databases is irreversible

 1# CREATE DATABASE ___;
 2CREATE DATABASE Chaplin Theaters;
 3
 4# DROP DATABASE ___;
 5DROP DATABASE Chaplin Theaters;
 6
 7# CREATE TABLE ___ ( columnName dataType, columnNamedataType)
 8CREATE TABLE movies
 9( 
10id int, 
11title varchar(320),
12genre varchar(160),
13duration int
14);
15
16# DROP TABLE ___;
17DROP TABLE movies;

Manipulating tables

• When you want to add/remove columns to/from a table, you use the ALTER TABLE keyword in conjunction with ADD COLUMN and DROP COLUMN

1# ALTER TABLE ___ ADD COLUMN ___ ___;
2ALTER TABLE movies
3ADD COLUMN ratings int;
4
5# ALTER TABLE ___ DROP COLUMN ___;
6ALTER TABLE movies
7DROP COLUMN ratings;

Links

• CodeSchool: Try SQL free video course with code challenges (less than 3 hrs approx for the entire course, Took me 1 hour per Level while i was also taking notes)