SQL Syntax Flashcards
SELECT and FROM
SELECT column 1, column 2
FROM database.table
or
SELECT *
FROM database.table
ORDER BY
SELECT *
FROM database.table
ORDER BY column 1
DISTINCT
SELECT DISTINCT column 1
FROM database.table
LIMIT
SELECT *
FROM database.table
ORDER BY column 1
LIMIT 10
COUNT
SELECT COUNT(*)
FROM database.table
OR
SELECT COUNT(DISTINCT column1)
FROM database.table
WHERE
SELECT *
FROM database.table
WHERE column_1 =10
AND/OR
SELECT *
FROM database.table
WHERE column_1 =10 AND column_2 =11 OR column_3 = 12
OR
SELECT *
FROM database.table
WHERE column_1 =10 AND (column_2 =11 OR column_3 = 12)
BETWEEN
SELECT *
FROM database.table
WHERE column_1 BETWEEN 10 AND 20
IN
SELECT *
FROM database.table
WHERE column_1 IN (1,5,3,7,12)
OR
SELECT *
FROM database.table
WHERE column_1 IN (‘up’,’down’,left’)
LIKE
SELECT *
FROM database.table
WHERE column_1 LIKE ‘%text_single character’
GROUP BY
SELECT Column_2, SUM(Column_1)
FROM database.table
GROUP BY Column_2
ORDER BY Column_2
Aggrigates are, Minimum, Maximum, Average, Sum
HAVING
SELECT Column_2, SUM(Column_1), AVERAGE(Column_1), MIN(Column_1), MAX(Column_1)
FROM database.table
WHERE Column_1 > 0
GROUP BY Column_2
HAVING COUNT(*) > 5
ORDER BY Column_2
UPPER/LOWER
SELECT UPPER(Column_1), LOWER(Column_1), LENGTH(Column_1)
FROM database.table
(returns uppercase, lowercase, length of text.)
LEFT/RIGHT
SELECT LEFT(Column_1, 3), RIGHT(Column_1, 3)
FROM database.table
(returns, the left three letters of column_1, returns the right 3 digits of column_1.)
POSITION
SELECT POSITION( ‘@’ from email)
FROM database.table
This will return the a number which is the numerical position of the ‘@’ symbol in the text which is an email in this case.
SUBTEXT
SELECT SUBTEXT(Column from beginning for lengthofsubtext)
The beginning is where you have to begin in the column, the lengthoftext is which will be returned. If length of text is not entered then the whole end of the text will be returned.
EXTRACT
SELECT EXTRACT(timeframewanted from column_1)
FROM database.table
TO_CHAR
SELECT TO_CHAR(Column_date, ‘Day”)
FROM database.table
The ‘Day’ will return a specific day of the column. It can be changed for other commands to return month year etc.
CASE
SELECT
CASE
WHEN Condition1 THEN result1
WHEN Condition2 THEN result2
WHEN ConditionN THEN resultN
ELSE resultRemaining
END
FROM database.table
CONCATINATE
SELECT Column_1 || Column_2|| Column_3 AS first_three_columns
this will return the first three columns with no spaces between them as the alias first_three_columns
CAST
SELECT CAST(ValueOrColumn AS DataType)
The data types may be varying. like DATE, VARCHAR, TEXT , etc. See PostgreSQL Docs
REPLACE
SELECT REPLACE (Column, ‘Old Text’, ‘New Text’)
FROM database.table
INNER JOIN
SELECT *
FROM Table_A
INNER JOIN Table_B
ON Table_A.Column_A = Table_B.Column_A
(The On command tells us which are the Primary keys and foreign keys for the table concatenation.)
OUTER JOIN
SELECT *
FROM table_A
FULL OUTER JOIN table_B
ON table_A.column_1=table_B.column_1
LEFT OUTER JOIN
SELECT *
FROM table_A
LEFT OUTER JOIN table_B
ON table_A.column_1=table_B.column_1
(FROM is the Primary key, or the main(left) table)
RIGHT OUTER JOIN
SELECT *
FROM table_A
LEFT OUTER JOIN table_B
ON table_A.column_1=table_B.column_1
(FROM is the Primary key, or the JOINED(RIGHT) table)
UNION
SELECT * FROM database.table
UNION
SELECT*FROM database.table
(Data types of columns must match, there must be the same number of columns in the correct order of data type.)
(If you do not want duplicates dropped then you will have to use
UNION ALL. other wise duplicates will be dropped.)
Correlated SUBQUERY
SELECT *
FROM table t1
WHERE column1 = (SELECT AVG(column1) FROM table t2 WHERE t1.column2=t2.column2)
