Basics

Question 1

What are the different data types that can be stored in a database?

Answer 1

Integer
Decimal
Text
CHAR
VARCHAR
DATE
TIME
Datetime
Boolean

Question 2

What does SELECT * mean?

Answer 2

Select all columns

Question 3

How do you select columns from a table?

Answer 3

SELECT NAME, NAME2 FROM CUSTOMER;

Question 4

Is a semicolon optional or mandatory?

Answer 4

Option, but if you want to run multiple queries at once it is necessary?

Question 5

Is select a write command?

Answer 5

No

Question 6

When importing multiple columns do you add a comma after the last column name?

Answer 6

No

Question 7

How do you add a new column?

Answer 7

SELECT COL1 * 1.05 AS COL2 FROM TABLE;

Question 8

Can you use spaces in column names?

Answer 8

No - use an underscore

Question 9

How do you add comments?

Answer 9

– or */ for multiline comments

Question 10

How do you concatenate text?

Answer 10

||

Question 11

How do you filter for only particular rows?

Answer 11

WHERE

Question 12

What are operators?

Answer 12

=
!=
>=
<=

Question 13

= or == for where?

Answer 13

=

Question 14

How do you get a range of dates using a where statement?

Answer 14

between

Question 15

Is between inclusive or exclusive?

Answer 15

Inclusive

Question 16

What are the wilcards?

Answer 16

% and _

Question 17

What is the difference between % and _

Answer 17

% is any number of characters and _ is one

Question 18

How are booleans stored?

Answer 18

0 false

1 true

Question 19

Do you have to use = 1 for where clauses where you want true?

Answer 19

No but you do for false

Question 20

How do you select null values?

Answer 20

is null

is not null

Question 21

Are null values included in where clauses?

Answer 21

No so you must specific with an or statement

Question 22

How can you filter data more efficiently than writing multiple or statements?

Answer 22

where ZYD IN (2010,2011,2012);

Question 23

How do you filter on text?

Answer 23

WHERE code = ‘dfdsfsdf’

put the search term in single quotes

Question 24

Can you use where and in on text values?

Answer 24

Yes - but they have to be in single quotes

Question 25

How do you filter on the length of the values?

Answer 25

WHERE length(report_code) != 6

Question 26

How do you search using wildcards?

Answer 26

WHERE report_code LIKE 'A%'

Question 27

How do you filter on a boolean?

Answer 27

WHERE hail = 1 AND thunder = 1

Question 28

What are your options to include null values?

Answer 28

WHERE precipitation IS NULL OR precipitation <= 0 or coalesce(precipitation,0) <= 0

Question 29

What order should statements be made?

Answer 29

``` SELECT FROM WHERE GROUP BY ORDER BY HAVING ```

Question 30

How do you get the number of records?

Answer 30

``` SELECT COUNT(*) as record_count FROM station_data ```

Question 31

What do you have to have in group by?

Answer 31

In select you have to have the column that you are grouping on

Question 32

How do you sort?

Answer 32

ORDER BY year, month

Question 33

What is the default sort order?

Answer 33

ascending

Question 34

How do you get a descending sort?

Answer 34

ORDER BY year DESC, month

Question 35

What is the difference between count(*) and count(preciptitation?)

Answer 35

Count referring to a column will ignore null values

Question 36

Do you have to use as alias when using an aggregate function?

Answer 36

No but it makes life easier

Question 37

SELECT COUNT(*) as record_count or SELECT COUNT(*)

Answer 37

Select count as record_count

Question 38

What is the average code?

Answer 38

AVG(temperature)

Question 39

How do you get to 2 decimal places?

Answer 39

round(avg(temperature),2)

Question 40

What are the aggregate functions?

Answer 40

AVG MIN MAX SUM

Question 41

Can you use where on aggregate values?

Answer 41

No use having

Question 42

What does where filter on?

Answer 42

Records

Question 43

How do you assign another value based on certain conditions?

Answer 43

Case statement

Question 44

What is the case structure?

Answer 44

CASE WHEN THEN ELSE END as alias

Question 45

What is the indent structure of case?

Answer 45

WHEN THEN and ELSE are indented

Question 46

Can you group on a case expression?

Answer 46

Yes

Question 47

When selecting using a case expression do you need a comma at the end?

Answer 47

Yes

Question 48

How do you count the records using the case expression

Answer 48

Count(*) not count(cat)

Question 49

Do you have to have a comma at the end of the case expression?

Answer 49

Yes

Question 50

How do you get two columns of different values of T/F for a different variable without using a join?

Answer 50

Use a case statement; | SUM(CASE WHEN precipitation = 1 THEN precipitation ELSE NULL END) as tornado_precipt

Question 51

How do you do an inner join?

Answer 51

FROM CUSTOMER INNER JOIN CUSTOMER_ORDER | ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID

Question 52

Where do you do an inner join?

Answer 52

In the from statement

Question 53

What comes first when specifying what table a column should come from?

Answer 53

table.column

Question 54

What do you do for a left join?

Answer 54

FROM CUSTOMER LEFT JOIN CUSTOMER_ORDER | ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID

Question 55

Which table is the left table in a left join?

Answer 55

The first one

Question 56

How do you use a left join to find customers who have no orders?

Answer 56

SELECT CUSTOMER.CUSTOMER_ID, NAME AS CUSTOMER_NAME FROM CUSTOMER LEFT JOIN CUSTOMER_ORDER ON CUSTOMER.CUSTOMER_ID = CUSTOMER_ORDER.CUSTOMER_ID WHERE ORDER_ID IS NULL

Question 57

How do you join multiple tables?

Answer 57

Do multiple joins: FROM CUSTOMER INNER JOIN CUSTOMER_ORDER ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID INNER JOIN PRODUCT ON CUSTOMER_ORDER.PRODUCT_ID = PRODUCT.PRODUCT_ID

Question 58

How do you create a table?

Answer 58

``` CREATE TABLE NAME ( COMPANY_ID INTEGER PRIMARY KEY COLNAME TEXT NOT NULL DESCRIPTION VARCHAR(10) PRIMARY_ATT_ID ``` ``` FOREIGN KEY (PRIMARY_ATT_ID) REFERENCES ATTENDEE(ATTENDEE_ID) ); ```

Question 59

How do you assign a primary key?

Answer 59

COLNAME INTEGER PRIMARY KEY

Question 60

How do you assign a foreign key?

Answer 60

FOREIGN KEY (NAME) REFERENCES OTHER_TABLE(NAMEINOTHERTABLE)

Question 61

When creating a table how do you make a column mandatory?

Answer 61

NOT NULL

Question 62

Does each table require a primary key?

Answer 62

Yes

Question 63

How do you autogenerate a value?

Answer 63

AUTOINCREMENT

Question 64

Do you have to have a comma after the column names in create a table?

Answer 64

yes

Question 65

Can you specify a default value when creating a table?

Answer 65

DEFAULT(VALUE)

Question 66

Can you have more than 2 foreign keys?

Answer 66

Yes

Question 67

Where do the columns go between the foreign keys?

Answer 67

After the end of the last column name and no further ones

Question 68

Do you indent on create table?

Answer 68

Yes

Question 69

If you are creating different tables in the same query do you need semicolons?

Answer 69

Yes

Question 70

How do you create a view?

Answer 70

CREATE VIEW NAME AS .....

Question 71

How do you add a record?

Answer 71

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME) | VALUES ('THOMAS', 'NIELD')

Question 72

When inserting records into a database, is it values 'jon' or values('jon')

Answer 72

values('jon')

Question 73

Can you insert multiple records at a time?

Answer 73

Yes | values('j','k'),('l','m'),('k','j')

Question 74

Can you insert record that has a foreign key that does not exist as a primary key in the original table?

Answer 74

No

Question 75

How do you round down?

Answer 75

FLOOR

Question 76

How do you round up?

Answer 76

CEIL()

Question 77

How do you give a subquery an alias?

Answer 77

Indent, and then AS WHATEVER after the parenteses (SELECT NAME, MONTHS, SALARY, MONTHS * SALARY AS EARNINGS FROM EMPLOYEE ORDER BY EARNINGS DESC) AS EARNINGS

Question 78

How do you return only the first row?

Answer 78

LIMIT 1