SQL Language (Condensed)

SELECT [DISTINCT | MIN | MAX] column1 [AS alias]
FROM table_name
WHERE condition [IS NULL|IS NOT NULL] <== WHERE is a command to declare that a filtering condition shall follow
GROUP BY column_name(s)
HAVING [NOT] condition1 AND|OR condition2 <== performs aggregate functions
ORDER BY column1,column2 [BETWEEN value1 AND value2] [IN (value1,value2)] [LIKE {pattern}] [ASC|DESC];

SELECT [MIN|MAX|COUNT|AVG|SUM](column_name) FROM tablename;

SELF JOIN: table joins with itself
INNER JOIN: both tables
LEFT JOIN: matched values of LEFT table
RIGHT JOIN: matched values of RIGHT table
FULL JOIN: returns all records matching both tables (large resulting data sets)

SELECT Cars.Toyota, Owners.Name
FROM ParkingLot;
INNER JOIN Owners ON Cars.OwnerID=Owners.OwnerID;

UNION is used to selects only distinct records between two SELECTs. To also return non-distinct, use UNION ALL

SELECT column1 FROM table1
UNION [ALL]
SELECT column1 FROM table2;

EXISTS: returns a boolean value of TRUE or FALSE of any matches
SELECT * FROM Cars WHERE EXISTS (SELECT Model from Cars WHERE Model='Camry');

SELECT INTO: copies data from one table into a new table
SELECT *
INTO newHouse [IN city2]
FROM oldHouse
WHERE movable=TRUE;

SQL INSERT INTO SELECT Statement is similar to INSERT INTO with the addition of requiring data source and target tables match
INSERT INTO newHouse.LivingRoom
SELECT * FROM oldHouse.LivingRoom
WHERE movable=TRUE;

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE FROM table_name WHERE condition;

CASE Syntax:
CASE
WHEN condition THEN result
ELSE otherResult
END;

Comparison operators:
=,!=,<=,>=

Supplement operators:
ANY :returns TRUE or FALSE if subquery meets the condition
ISNULL() :returns true or false from the evaluative condition
IFNULL() :will execute when null condition is met
COALESCE() :is same is IFNULL()

Wilcards:
% :Represents any character
_ :underscore represents 1 character
[] :character inside brackets
^ :NOT
- :range (e.g. [a-z]}

Other Special Keywords:
ROWNUM :matches the row number

Stored Procedures:

1. Create:
CREATE PROCEDURE backup_db
AS
BACKUP DATABASE oldHouse
TO DISK = 'C:\oldHouse_db.BAK'
GO;

2. Execute
EXEC backup_db;

Leave a Reply

Your email address will not be published. Required fields are marked *