Commands

Question 1

SELECT * FROM tbl

Answer 1

Select all rows and columns from table tbl

Question 2

SELECT c1,c2 FROM tbl

Answer 2

Select column c1,c2 and all rows from table tbl

Question 3

SELECT c1,c2 FROM tbl WHERE conditions ORDER BY c1 ASC, c2 DESC

Answer 3

Select columns c1, c2 with where conditions and from able tbl. order results by column c1 in ascending order and c2 in descending order

Question 4

SELECT DISTINCT c1,,c2 FROM tbl

Answer 4

Select unique rows by columns c1 and c2 from the table

Question 5

SELECT c1, aggregate(expr) FROM tbl GROUP BY c1

Answer 5

Select column c1 and use aggregate function on expression expr, group columns by column c1

Question 6

SELECT c1, aggregate(expr) AS c2 FROM tbl GROUP BY c1 HAVING c2 > v

Answer 6

Select column c1 and c2 as column alias of the result of aggregate function on expr. Filter group of records with c2 greater than value v

Question 7

INSERT into tbl(c1,c2,…) VALUES(v1,v2…)

Answer 7

Insert data into table tbl

Question 8

INSERT INTO tbl(c1,c2,…) SELECT c1,c2.. FROM tbl2 WHERE conditions

Answer 8

Insert data from tbl2 into tbl1

Question 9

UPDAT tbl SET c1 = v1 c2 = v2 WHERE conditions

Answer 9

Update data in table tbl

Question 10

DELETE FROM tbl WHERE conditions

Answer 10

Delete records from table tbl based on conditions

Question 11

TRUNCATE TABLE tbl

Answer 11

Drop table tbl and recerate it, losing all data

Question 12

CREATE TABLE tbl( c1 datatype(length) c2 datatype(length)… PRIMARY KEY(c1))

Answer 12

create a table with the primary key as c1

Question 13

DROP TABLE tbl

Answer 13

Remove table tbl from database

Question 14

ALTER TABLE tbl ADD COLUMN c1 datatype(length)

Answer 14

Add a column to the table

Question 16

SELECT column_name FROM table_name

Answer 16

Gets you a column from your table

Question 17

SELECT c1,c2 FROM table_1

Answer 17

Gets you the specific columns

Question 18

SELECT DISTINCT column FROM table

Answer 18

Get the unique values from a column

Question 19

SELECT COUNT(column) FROM table

Answer 19

Count all the rows in a column

Question 20

SELECT COUNT( DISTINCT column) FROM TABLE

Answer 20

Gets you a number of distinct elements in a table

Question 21

SELECT column FROM table WHERE condition

Answer 21

Lets you select a specific set of data

Question 22

Example

Answer 22

Question 23

SELECT column_1, column_2 FROM table ORDER BY column_1 ASC/DESC

Answer 23

Order the returned data by a specific column

Question 24

SELECT company,name,sales FROM table ORDER BY company,sales

Answer 24

Orders the columns first by all unique companies, then by sales results

Question 25

SELECT data FROM table ORDER BY column LIMIT 5

Answer 25

Limits the ordered data to 5 rows

Question 26

SELECT column FROM table BETWEEN low AND high

Answer 26

Gets you data in a range of

Question 27

SELECT value FROM table NOT BETWEEN low AND high

Answer 27

Finds you data within a range of

Question 28

SELECT color FROM table WHERE color IN (‘red’, ‘blue’)

Answer 28

Find rows where the column targetted (color) is in a list of possibilities

Question 29

SELECT name FROM celebrity WHERE name LIKE ‘A%’

Answer 29

Gets all celebrities whose names start with A from a celebrity table

Question 30

SELECT name FROM song WHERE song ILIKE ‘%and’

Answer 30

Gets a list of all songs from a song table that end with and or And

Question 31

WHERE value LIKE ‘value__’

Answer 31

Each underscore is one blank space

Question 32

SELECT ROUND(AVG(num_column),3) FROM TABLE

Answer 32

Returns the average value as a float. Can use ROUND() around the average to get a lower number of decimal places

Question 33

SELECT MAX(num_column) FROM TABLE

Answer 33

Return the maximum

Question 34

SELECT MIN(num_column) FROM table

Answer 34

Return the minimum number of

Question 35

SELECT SUM(num_column) FROM TABLE

Answer 35

Return the sum number

Question 36

SELECT category_col, AGG(data_col) FROM table WHERE condition GROUP BY category_col

Answer 36

It organizes the data into groups. You need to include the columns that you want to group by

Question 37

SELECT company, SUM(sales) FROM finance_table WHERE company != ‘Google’ GROUP BY company HAVING SUM(sales) > 1000

Answer 37

Here having is used to sort companies that have more than 1000 sales. Importantly, having is used when where is filtered by the same thing it’s grouped by

Question 38

AS

Answer 38

Creates an alias for something (table or column) SELECT SUM(column) AS new_name FROM table AS can’t be used in where because it happens at the end of the operation

Question 39

SELECT * FROM TableA INNER JOIN TableB ON TableA.col_match = TableB.col_match

Answer 39

Gets you a table with all the rows where the values of the table A and table B columns match

Question 40

SELECT column, Table2.column, column FROM Table1 INNER JOIN Table2 ON Table1.column = Table2.column

Answer 40

Gets you the columns on either end of the joined column. Also, only gets you one column when joining, not a duplicate

Question 41

JOIN is equal to inner join

Answer 41

There you go

Question 42

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.col_match = TableB.col_match

Answer 42

Gets everything from both tables. You will get null where there is no match

Question 43

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableA.id IS null OR TableB IS null

Answer 43

Gives you all unique rows from both tables

Question 44

Left outer Join SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.col_match = TableB.col_match

Answer 44

Gets you all of table A and the overlapping columns of table B

Question 45

SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.col_match = TableB.col_match WHERE TableB.id IS null

Answer 45

Gets you only the things unique to table A

Question 46

SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.col_match = TableB.col_match

Answer 46

Gives you all rows where there is overlap in data or only data in Table B

Question 47

SELECT column_name FROM table1 UNION SELECT column_name FROM table2

Answer 47

Combines the columns together to display a new data table

Question 48

TIME

Answer 48

Column with only time

Question 49

DATE

Answer 49

Column with only date

Question 50

TIMESTAMP

Answer 50

Contains sate and time

Question 51

TIMESTAMPTZ

Answer 51

Contains date, time, and timezone

Question 52

TIMEZONE

Question 53

NOW

Question 54

TIMEOFDAY

Question 55

CURRENT_TIME

Question 56

CURRENT_DATE

Question 57

SHOW TIMEZONE

Answer 57

Does what it sounds like

Question 58

SELECT NOW()

Answer 58

Gives an up to the second timestamp

Question 59

SELECT CURRENT_DATE

Answer 59

Gets you the current date

Question 60

EXTRACT(YEAR FROM date_col)

Answer 60

Gets you just the year from a timestamp

Question 61

AGE(date_col)

Answer 61

Gets you an age based on the date in the column

Question 62

TO_CHAR(date_col, ‘mm-dd-yyyy’)

Answer 62

Gets you the year formatted