SQL.txt

SQL
==============================================================================


ops - exe, retrieve, insert, update, delete, create (db, tables, ...)
web - rdbms, server-side scripting lang (php/asp)


rdbms - basis of - MS SQL Server, IBM DB2, Oracle, MySQL, Microsoft Access
table - cols (v) & rows (h)
sql keywords are not case-sensitive


`*`  - all
`--`, `//` - comments
`;`  - some db req at end
`NULL` vals - is a field with no value


-------------------------------------------------------------------------------
STATEMENTS:
-------------------------------------------------------------------------------


SELECT          - extracts data from a database
UPDATE          - updates data in a database
DELETE          - deletes data from a database
INSERT INTO     - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE  - modifies a database
CREATE TABLE    - creates a new table
ALTER TABLE     - modifies a table
DROP TABLE      - deletes a table
CREATE INDEX    - creates an index (search key)
DROP INDEX      - deletes an index


-------------------------------------------------------------------------------


COUNT & DISTINCT
	def    : By using the DISTINCT keyword in a function called COUNT, we can 
		 return the number of different countries
	syntax : COUNT(DISTINCT column_name) 
	example: SELECT COUNT(DISTINCT column_name) FROM table_name;
	note   : this is not supported by ms access
	workard: SELECT Count(*) AS DistinctCountries
        	 FROM (SELECT DISTINCT Country FROM Customers);


ORDER BY
	def    : The ORDER BY keyword is used to sort the result-set in ascending 
		 or descending order
	syntax : SELECT * FROM table_name ORDER BY column_name;
	note   : we can add `ASC` / `DESC` at end (before ;)
		 `ASC` is by-default
	more eg: SELECT * FROM table_name ORDER BY column_name DESC;
		 SELECT * FROM table_name ORDER BY column1, column2;
		 SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;


AND & OR
	def    : They are used to filter records based on one or more condition
	syntax : SELECT * FROM table_name WHERE column_name = 'x' AND column_name > y
	note   : if you combine `AND` & `OR`, use parenthesis
	eg     : SELECT * FROM table_name WHERE column_name = 'x' AND (column_name >= y OR column_name LIKE 'S%')


NOT
	def    : The NOT operator is used in combination with other operators 
		 to give the opposite result, also called the negative result
	syntax : SELECT * FORM table_name WHERE NOT column_name = 'x'	
	note   : it can be used before `BETWEEN`,`LIKE`, `IN` & other 
		 operators - works as negation


INSERT INTO
	def    : The INSERT INTO statement is used to insert new records in a table
	types  : 
		1: Specify both the column names and the values to be inserted 
			INSERT INTO table_name (column1, column2, column3, ...)
			VALUES (value1, value2, value3, ...);
		2: Adding values for all the columns of the table, no need to specify
		   the column names. Make sure the order of the values is in the same
		   order as the columns in the table
			INSERT INTO table_name VALUES (value1, value2, value3, ...);
	note   : To insert multiple columns, add values separated by comma
			INSERT INTO table_name (column1, column2, column3, ...)
			VALUES 
				(value1, value2, value3, ...),
				(value1, value2, value3, ...),
				(value1, value2, value3, ...);	


UPDATE
	def    : The UPDATE statement is used to modify the existing records in a table
	syntax : UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
	eg     : UPDATE table_name SET column2 = value1, column4 = value2 WHERE columnx = y
	note   : If you omit the WHERE clause, ALL records will be updated!
			UPDATE table_name SET column_name='x';



DELETE
	def    : The DELETE statement is used to delete existing records in a table
	syntax : DELETE FROM table_name WHERE condition;
	note   : If you omit the WHERE clause, ALL records will be deleted!


TOP, LIMIT, FETCH FIRST OR ROWNUM
	SELECT TOP
		def     : The SELECT TOP clause is used to specify the number of records to return
		eg      : SELECT TOP 3 * FROM table_name;

	Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause
	to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM

	SQL Server / MS Access Syntax:
		SELECT TOP number|percent column_name(s)
		FROM table_name
		WHERE condition;

	MySQL Syntax:
		SELECT column_name(s)
		FROM table_name
		WHERE condition
		LIMIT number;

	Oracle 12 Syntax:
		SELECT column_name(s)
		FROM table_name		
		ORDER BY column_name(s)
		FETCH FIRST number ROWS ONLY;

	Older Oracle Syntax:
		SELECT column_name(s)
		FROM table_name
		WHERE ROWNUM <= number;

	Older Oracle Syntax (with ORDER BY):
		SELECT *
		FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
		WHERE ROWNUM <= number;



Aggregate Functions
	An aggregate function is a function that performs a calculation on a set of values, 
	and returns a single value. Aggregate functions are often used with the GROUP BY 
	clause of the SELECT statement.	The GROUP BY clause splits the result-set into groups 
	of values and the aggregate function can be used to return a single value for each group.

		MIN()   - returns the smallest value within the selected column
			  SELECT MIN(column_name) FROM table_name WHERE condition;
		MAX()   - returns the largest value within the selected column
			  SELECT MAX(column_name) FROM table_name WHERE condition;
		COUNT() - returns the number of rows in a set
			  SELECT COUNT(column_name) FROM table_name WHERE condition;
		SUM()   - returns the total sum of a numerical column
			  SELECT SUM(column_name) FROM table_name WHERE condition;
		AVG()   - returns the average value of a numerical column




-------------------------------------------------------------------------------
QUERIES:
-------------------------------------------------------------------------------


-- 'select from' clause
SELECT * FROM table_name;
SELECT column1, column2 FROM table_name;


-- 'where' clause
SELECT * FROM table_name WHERE column_name = 'filter-text';
-- it can consist of following operators:
-- =, <, >, <=, >=, <> / !=, BETWEEN, LIKE, IN


-- testing NULL vals
SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;


-- `update` clause
UPDATE table_name SET column_name = 'x', column_name = 'y' WHERE column_name = 1;
-- multiple records
UPDATE table_name SET column_name='x' WHERE column_name='y';
-- all records
UPDATE table_name SET column_name='x';


-- `delete` clause
DELETE FROM table_name WHERE column_name='x';
-- delete all records
DELETE FROM table_name;
-- delete a table
DROP TABLE table_name


-- 'SQL server' & 'MS Access'aqw
-- `LIMIT` 
SELECT * FROM table_name LIMIT 3;
-- `FETCH FIRST`
SELECT * FROM table_name FETCH FIRST 3 ROWS ONLY;
-- `TOP PERCENT`
SELECT TOP 50 PERCENT * FROM table_name;
SELECT * FROM table_name FETCH FIRST 50 PERCENT ROWS ONLY;
-- `SELECT TOP` + `WHERE`
[MS Access] SELECT TOP 3 * FROM table_name WHERE column_name='x';
[MySQL]     SELECT * FROM table_name WHERE column_name='x' LIMIT 3;
[Oracle]    SELECT * FROM table_name WHERE column_name='x' FETCH FIRST 3 ROWS ONLY;
-- `SELECT TOP` + `ORDER BY`
[MS Access] SELECT TOP 3 * FROM table_name ORDER BY column_name DESC;
[MySQL]     SELECT * FROM table_name ORDER BY column_name DESC LIMIT 3;
[Oracle]    SELECT * FROM table_name WHERE column_name='x' FETCH FIRST 3 ROWS ONLY;


-- MIN / MAX
-- set column name (alias)
SELECT MIN(column_name) AS var_name FROM table_name;
-- with GROUP BY
SELECT MIN(column_name) AS alias1, alias2 FROM table_name GROUP BY column_name;

-- COUNT
-- ignore duplicates
SELECT COUNT(DISTINCT column_name) FROM table_name;
-- use alias
SELECT COUNT(*) AS [column name with spaces], column_name_without_space FROM table_name;
-- with GROUP BY
SELECT COUNT(*) AS [column name with spaces], column_name_without_space FROM table_name GROUP BY column_name;

-- SUM
-- WHERE
SELECT SUM(column_name) FROM table_name WHERE coulumn_name = 11;
-- alias
SELECT SUM(column_name) AS var_name FROM table_name;
-- GROUP BY
SELECT OrderID, SUM(column_name) AS [var name] FROM table_name GROUP BY column_name;