SQL Basics

Question 1

SELECT

Answer 1

Column you want to look at

use to specify the columns you want to retrieve data from

Question 2

FROM

Answer 2

Table the data lives in

used to specify the table or tables you want to retrieve data from

Question 3

WHERE

Answer 3

Certain conditions on the data

used to filter data based on specific conditions

Question 4

GROUP BY

Answer 4

Column you want to aggregate by

used to group data based on one or more columns

Question 5

ORDER BY

Answer 5

Column you want to order results by in ASC or DESC order

Used to sort data in ascending or descending order based on one or more columns. The ordering is automatically ASC and will order in sequence of your first statement.

Question 6

SELECT DISTINCT

Answer 6

Returns unique values of specified columns. This can be useful to eliminate duplicate rows from your query results.

Question 7

INNER JOIN

Answer 7

When you only want to see information from two tables that match.

Question 8

HAVING

Answer 8

Certain condition on aggregation

Question 9

LIMIT

Answer 9

Maximum order of rows you want your results to contain.

Question 10

Syntax for INNER JOIN

Answer 10

SELECT Columns
FROM Table 1 INNER JOIN table 2
ON Table1 Col 1 = Table 2 col 2

Question 11

Joins - USING instead of ON

Answer 11

USING has to be exact match, ON Specifys columns

Question 12

Aliases and syntax

Answer 12

Aliases shorten names when you are refering to them in SQL

SELECT c.col1, c.col2, e.col1, e.col2

FROM Customer c INNER JOIN e
ON c.ID = e.ID

Question 13

If you have to count the number of times a track appears in the table and group by track name. What would the statement be?

Table = Track
Track Name = Name

Answer 13

– Select the track name and count
SELECT t.Name, COUNT(*)

FROM Track t

– Filter on the USA
WHERE i.BillingCountry = ‘USA’

– Group by track name
GROUP BY t.name

Question 14

What are the three types of outer joins?

Answer 14

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Question 15

Left Outer Join

Answer 15

Returns matched rows and left table rows

Nulls will be on the right side of the table

Question 16

Right Outer Join

Answer 16

Returns matched rows and right table rows

Nulls will be on the left side of the table

Question 17

Full Outer Join

Answer 17

Returns all matched and unmatched rows from both tables

Nulls will be on both side of the table

Question 18

Cartesian Product (Cross Product)

Answer 18

A cartesian product is when you have two sets of elements and you multiply them to generate all the possible pairs between the elements of the two sets.

Question 19

SELF JOIN

Answer 19

Self joins allow you to join a table to itself.

Question 20

How do you concatenate in a select statement?

Answer 20

SELECT e.firstname || ‘ ‘ || e.lastname AS “Employee”

output = new colume with full name

Question 21

set operators

Answer 21

Take the output of two or more SELECT queries and combine them into one result.

Row oriented as they combine queries

Question 22

set operators v joins

Answer 22

both combine data.

Join clauses combine tables and are column oriented.

Set operators combine queries and are row oriented.

Question 23

4 types of set - operators

Answer 23

Union - The first is union and it returns all rows with no duplicates.

Union All - UNION ALL which returns all rows including duplicated

Intersect - returns rows outputted by both queries

Minus- returns distinct rows in the first query that are not in the second query.

Question 24

Union

Answer 24

returns all distinct rows selected by either query, effectively combing the results and duplicates

Question 25

Union All

Answer 25

Return all rows selected by either query, including duplicates, without any filtering

Question 26

Intersect

Answer 26

returns all distinct rows selected by both queries, effectively finding common rows

Question 27

MINUS

Answer 27

Returns all distinct rows selected by the first query but not by the second query, effectively finding the difference between the two sets.

Question 28

INTERSECT ALL

Answer 28

Returns all rows selected byboth queries, including duplicates, without any filtering.

Question 29

MINUS ALL

Answer 29

Returns all rows selected by the first query but not the second query, including duplicates without filtering.

Question 30

If an aliase is used in the select clasue, where can't it be used?

Answer 30

WHERE, GROUP BY, and HAVING Can be used in the order by

Question 31

In a basic sql query with a SELECT FROM WHERE

Question 32

Customising Output

Answer 32

uppercasing characters or selecting first characters of each cell in a column. You need functions to do this.

Question 33

Data Type

Answer 33

Define what type of data a column can contain. There are three main - numeric, chacater and date.

Question 34

Character functions Inputs and Outputs

Answer 34

Input: character values Outputs: character, numeric, date values Date functions/general functions/conversion functions

Question 35

Number functions

Answer 35

Inputs: numeric values Outputs: numeric values

Question 36

SUBSTRING

Answer 36

Character type SUBSTR(column, m, n) returns portion of a string from positions m, n how long i.e. the number of character you want to return from position m

Question 37

LENGTH

Answer 37

Character function SELECT LENGTH('cat') solution would be 3 - returns length of a string.

Question 38

REPLACE

Answer 38

character function SELECT REPLACE('kayak', 'k', 'y') Will replace all the the second condition of the function with the third therefore the result would be yayay

Question 39

ROUNDING Total - 11.94 Round totals to 1 and 0

Answer 39

numeric function ROUND(column, m): round column to m decimal ROUND(Total, 1) = 11.9 ROUND(total, 0) = 12

Question 40

TRUNC Total = 15.86 trunc to 1 and 0

Answer 40

Numeric function TRUNC(column, m): truncates column to m decimals TRUNC(Total, 1) = 15.8 TRUNC(Total, 0) = 15 Cuts of the decimal without rounding

Question 41

Null Values deinition

Answer 41

No Value Not the same as 0 Arithmetic expression with NULL evaluate to NULL NULL + 10 = NULL Aggregate functions often ignore nulls such as count

Question 42

What do you do to test for nulls?

Answer 42

IS NULL to check is null IS NOT NULL to check it is not null

Question 43

NVL

Answer 43

Used to convert null value to non-null values. NVL(x, y): convert x, which may contain a null value, to y, a non-null value

Question 44

NULLIF

Answer 44

NULLIF(x, y): Compares x and y, returns NULL if x = y x if they are not equal