Head First SQL

Question 1

Does this work: WHERE second = “Orange Juice”;

Answer 1

No. SQL expects single quote

Question 2

if amount is DEC, does this work: WHERE amount = “1.5”

Answer 2

Yes. RDBMS is forgiving

Question 3

Types with quotes

Answer 3

CHAR, VARCHAR, DATE, DATETIME, TIME, TIMESTAMP, BLOB

Question 4

Types without quotes

Answer 4

DEC INT

Question 5

single quote is a special character

Answer 5

' or ‘’

Question 6

Not equal

Answer 6

<>

Question 7

Find all the cities ending with CA

Answer 7

WHERE location LIKE ‘%CA’. % stands for any number of unknown characters

Question 8

wild card in LIKE which stands for only one character

Answer 8

_

Question 9

between two numbers

Answer 9

calories BETWEEN 30 AND 60

Question 10

Write a query that will SELECT the names of drinks that begins with letters G through O

Answer 10

WHERE drink_name BETWEEN ‘G’ AND ‘O’

Question 11

rating is ‘innovative’ or ‘fabulous‘

Answer 11

WHERE rating in (‘innovative’, ‘fabulous’）

Question 12

rating is not ‘innovative’ and ‘fabulous’

Answer 12

WHERE rating not in (‘innovative’, ‘fabulous’)

Question 13

Difference:
WHERE NOT main IN ( );
WHERE main NOT IN ( );

Answer 13

The same

Question 14

Find IS not NULL

Answer 14

WHERE NOT main IS NULL; or

WHERE main IS NOT NULL

Question 15

DELETE records

Answer 15

DELECT FROM

Question 16

UPDATE a table

Answer 16

UPDATE … SET …

Question 17

Primary key requirement

Answer 17

1. Not NULL 2. can’t be changed

Question 18

Normal table

Answer 18

1. Atomic 2. Primary key

Question 19

Return the CREATE TABLE statement

Answer 19

SHOW CREATE TABLE my_contracts;

Question 20

To CREATE TABLE

Answer 20

CREATE TABLE my_contract (last_name VARCHAR(20) )

Question 21

AUTO_INCREMENT

Answer 21

Automatically fill the column with a value that starts on row 1 with increment of 1 for every new row

Question 22

Add a primary key to an existing table

Answer 22

ALTER TABLE XX
ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT
PRIMARY KEY (contact_id)

Question 23

Renaming the table

Answer 23

ALTER TABLE projeckts RENAME TO project_list

Question 24

Remove the primary key designation without changing the data?

Answer 24

ALTER TABLE XX DROP PRIMARY KEY;

Question 25

Difference:
ADD COLUMN phone VARCHAR(10) LAST;
ADD COLUMN phone VARCHAR(10);

Answer 25

Same

Question 26

update values based on different conditions

Answer 26

UPDATE … SET … = CASE WHEN … THEN WHEN… ELSE …END;

Question 27

Reverse order

Answer 27

ORDER BY … DESC

Question 28

ORDER

Answer 28

ORDER BY … ASC

Question 29

mean used with GROUP BY

Answer 29

AVG()

Question 30

select only unique value

Answer 30

SELECT DISTINCT

Question 31

Show only two rows

Answer 31

LIMIT 2;

Question 32

LIMIT 0, 4;

Answer 32

start from 0; show 4 results;

Question 33

parent key

Answer 33

The primary key used by the foreign key

Question 34

Foreign key

Answer 34

a column in a table that reference the primary key of another table

Question 35

Referential Integrity

Answer 35

You will only be able to insert values into your foreign key that exits in the parent table

Question 36

Ways to add foreign key

Answer 36

Creating table

Alter table

Question 37

CONSTRAINT

Answer 37

Make sure the foreign key contains a meaningful value

Question 38

What to add to create a foreign key contact_id when you creating a table? (parent table my_contacts, called contact_id in other table)

Answer 38

contact_id INT NOT NULL
CONSTRAINT my_contacts_contact_id_fk
FOREIGN KEY (contact_id)
REFERENCES my_contacts (contact_id)

Question 39

Composite key

Answer 39

a key made up of more than one column

Question 40

Partial functional dependency

Answer 40

A non-key column is dependent one some, but not all, of the columns in composite primary key

Question 41

Transitive functional dependency

Answer 41

An non-key column is related to another non-key column

Question 42

First normal form (1NF)

Answer 42

Columns contain only atomic values, and no repeating groups of data are permitted in a column

Question 43

Second normal form (2NF)

Answer 43

Your table must be in 1NF and contain no partial functional dependencies to be in 2NF

Question 44

Third normal form (3NF)

Answer 44

Your table must be in 2NF and have no transitive dependencies

Question 45

Difference:
SELECT profession my_prof
FROM my_contacts mc
and
SELECT profession AS my_prof
FROM my_contacts AS mc

Answer 45

They’re the same

Question 46

CROSS JOIN

Answer 46

Return every row from one table crossed with every row from the second

Question 47

Natural join

Answer 47

Only work if the column you’re joining has the same name

Question 48

How many ORDER BY with UNION?

Answer 48

Only one

Question 49

UNION ALL

Answer 49

The same as UNION, except it returns all the values from the columns rather than one instance