SQL Commands

Question 1

What does the “AS” command do?

Answer 1

allows you to rename a column or table

SELECT column_name AS ‘alias’ from table_name;

Question 2

What does AVG do?

Answer 2

Returns the average of the numeric value
SELECT AVG(column_name_ from table_name

Question 3

What does the BETWEEN command do?

Answer 3

Filters the result between certain range

SELECT column_name from table_name WHERE column_name BETWEEN value_1 and value_2

Question 4

What does the CASE command do?

Answer 4

If-then logic to create new variables

SELECT column_name,
CASE
WHEN condition THEN 'Result_1'
WHEN condition THEN 'Result_2'
ELSE 'Result_3'
END
FROM table_name

Question 5

COUNT

Answer 5

Count non-null rows in a column

SELECT COUNT(column_name_ from table_name;

Question 6

GROUP BY

Answer 6

Aggregate function
Organizes the output into different groups

SELECT column_name
FROM table_name
GROUP BY column_name

Question 7

HAVING

Answer 7

Is “WHERE” with aggregate functions

SELECT column_name, COUNT()
FROM table_name
GROUP BY column_name
HAVING COUNT() > value;

Question 8

Website with these commands

Answer 8

https://www.codecademy.com/articles/sql-commands

Question 9

Inner Join

Answer 9

Combine different rows from different tables if the JOIN condition is true

SELECT column_name
FROM table_name1
JOIN table_2
ON table_1.column_name = table_2.column_name;

Question 10

IS NULL / IS NOT NULL

Answer 10

Used with WHERE clause to test for empty values

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL

Question 11

LIKE

Answer 11

Special operator used with the WHERE caluse to search for a specific pattern in a column

SELECT column_name
FROM table_name
WHERE column_name LIKE %pulse%

Question 12

LIMIT

Answer 12

Clause that lets you specific the maximum number of rows the result set will have

SELECT column_name
FROM table_name
LIMIT number;

Question 13

MAX

Answer 13

function that returns the largest value in a column

SELECT MAX(column_name)
FROM table_name;

Question 14

MIN

Answer 14

Function that returns the smallest value in a column

SELECT MIN(column_name)
FROM table_name

Question 15

OR

Answer 15

Operator that filters the results set to only include where where either condition is true

SELECT column_name
FROM table_name
WHERE column_name = value_1
OR column_name2=value_2

Question 16

ORDER BY

Answer 16

Clause that allows you to order the results by a particular column in ascending or descending order

SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC

Question 17

LEFT JOIN

Answer 17

Combine rows from different tables even if the join condition is not met.
With left join, all of the rows on the left table is returned.

SELECT column_name
FROM table_name
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name

Question 18

RIGHT JOIN

Answer 18

Combines rows from different tables even if the join condition is not met.
With right join, all of the rows on the right table are returned. NULL for left table

SELECT column_name
FROM table_1
RIGHT JOIN table_2
ON table_1.column_name = table_2.column_name

Question 19

FULL JOIN

Answer 19

Returns all records when there is a match in the left or right table records.

SELECT column_name
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Question 20

ROUND

Answer 20

Function that rounds the values in a column to the integer specified in the function argument.

SELECT ROUND(column_name, integer)
FROM table_name

Question 21

SELECT DISTINCT

Answer 21

specifies that the statement is going to be a query that returns unique values in the specified columns

SELECT DISTINCT column_name
FROM table_name;

Question 22

SUM

Answer 22

Function that adds a column

SELECT SUM(column_name) 
FROM table_name

Question 23

Comments in SQL

Answer 23

/*

*/

Question 24

How to create subqueries (nested queries, inner queries)

Answer 24

SELECT column_name,
(Select AVG(salary) from table_name as NAME from table_name)
FROM table_name

Question 25

Where can you use subqueries?

Answer 25

SELECT, FROM, WHERE

Basically anywhere