SQL_syntax.sql

-- SQL Basic Syntax -- 

--initialize SQLite database w/ command line:
sqlite3 database_name.db

--helpful commands
.help -- list of commands
.tables -- see all tables
.mode column / .header on -- helpful for viewing


--Creating a Database:

CREATE DATABASE database_name;


--Removing a Database:

DROP DATABASE database_name;
-- ***SQLite*** - use the terminal to delete the file


--Creating a Table (w/ multipe columns):

CREATE TABLE new_table_name (new_column_name1 VARCHAR(50), new_column_name2 INTEGER);


--Rename Table;

RENAME TABLE table_name TO new_table_name;
--or
ALTER TABLE movie RENAME TO movies; -- ***SQLite***


--Delete Table

DROP TABLE table_name;


--Add Column (can take multiple columns)

ALTER TABLE table_name ADD (new_column_name VARCHAR(100), new_column_name2 DATE);
--***SQLite*** only one column can be added with sqlite3 at a time; also, don't use parantheses!!!


--Rename Column (cannot take multiple columns):

ALTER TABLE table_name CHANGE old_column_name new_column_name TEXT;
--or
SELECT left_most_column_name, column_to_be_changed AS new_name FROM table_name; -- ***SQLite*** (only renames the column for one display)


--Delete Column

ALTER TABLE table_name DROP column_name;
-- ***SQLite*** - no easy way to do this


--Insert Row:

INSERT INTO table_name VALUES (first_column_data, second_column_data, etc.); 


--Insert Multiple Rows:
INSERT INTO table_name VALUES (first_column_data, second_column_data, etc.), (second_row_first_column_data, second_row_second_column_data, etc.); 


--Update Row:

UPDATE table_name SET column_name = new_data WHERE column_name = identifying_data;


--Delete Row:

DELETE FROM table_name WHERE column_name = data_name; --data from more columns can be specified for greater precision; separated by 'AND' 


--Retrieve Data from all Columns:

SELECT * FROM table_name;


--Retrieve Data from specific Column(s):

SELECT column_name FROM table_name; --more columns can be added, by separating the name of each column with a comma-- 


--Retrieving Queried Data:

SELECT * FROM table_name WHERE column_name1 != 1999 AND column_name2 = "Casablanca";

SELECT * FROM table_name WHERE column_name1 = 1999 OR year = 2000;

SELECT * FROM table_name WHERE column_name BETWEEN 1998 and 2000;

SELECT * FROM table_name WHERE column_name LIKE "exact match"; --not case sensitive -- add % before and/or after the search criteria to exclude characters that come before and/or after the search criteria


--Ordering the Result Set:

SELECT * FROM table_name ORDER BY column_name; --multiple columns can be added

SELECT * FROM table_name ORDER BY column_name DESC, column_name ASC;

SELECT * FROM table_name WHERE column_name IS NULL; -- or NOT NULL / these lists can also be ordered using syntax similar to the prior two lines above



--Limiting the Result Set:

SELECT * FROM table_name LIMIT 10 OFFSET 20; -- limit = rows returned / offset = where system starts counting

SELECT * FROM table_name LIMIT 20, 10; -- first number is offset, second is limit

SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1,2;


--INNER JOIN

SELECT * FROM table1 JOIN table2 ON table1.column1_match = table2.column2_match; --This will only display each row from table1 that has a value that matches one of the values being used from table2

--LEFT OUTER JOIN
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column1_match = table2.column2_match; --like an INNER JOIN this will match up the corresponding rows from each table, however, this will also display the rows that do not match up from the left table (table1 is the left table because it's left most in the code)

--RIGHT OUTER JOIN
SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column1_match = table2.column2_match; --like an INNER JOIN this will match up the corresponding rows from each table, however, this will also display the rows that do not match up from the right table (table2 is the right table because it's right most in the code)
-- ***SQLite*** - no easy way to do this


--Aliases

example:
SELECT table1.column1 AS temp_name1, table2.column2 AS temp_name2 FROM table1 JOIN table2 ON table1.column1_match = table2.column2_match;