SQL Basics

Question 1

What is a relational database?

Answer 1

A collection of tables where each table is related to one another in some way

Question 2

What is a table comprised of?

Answer 2

Columns and rows/records

Question 3

T/F: The primary key column can contain duplicate data

Answer 3

False

Question 4

T/F: The primary key column can contain null values

Answer 4

False

Question 5

Insert ‘Baby’ and ‘Domestic’ into a table called ‘departments’ which has two columns

Answer 5

insert into departments values ('Baby', 'Domestic');

Question 6

Create a table called ‘departments’ with two columns, ‘department’ and ‘division’, which have type ‘varchar(100)’. Make the primary key ‘department’.

Answer 6

create table departments (
    department varchar(100),
    division varchar(100),
    primary key (department)
);

Question 7

T/F: You can have more than one primary key constraint in a table

Answer 7

False

Question 8

T/F: You can have multiple columns acting as one primary key

Answer 8

True

Question 9

What does ; do in a query?

Answer 9

It separates statements in one script

Question 10

Write a WHERE clause that returns all ‘department’ values that start with ‘F’ and end with ‘e’

Answer 10

WHERE department like ‘F%e’;

Question 11

Write a WHERE clause that returns all ‘salary’ values greater than 10

Answer 11

WHERE salary > 10;

Question 12

Write a WHERE clause that returns values where ‘salary’ < 40000 and ‘department’ is either ‘Clothing’ or ‘Pharmacy’

Answer 12

WHERE 'salary' < 40000
AND (department = 'Pharmacy'
OR department = 'Clothing');

Question 13

Write a WHERE clause where ‘department’ does not equal ‘Sports’

Answer 13

WHERE NOT department = ‘Sports’;

Question 14

What’s another way of writing negative assertion other than NOT?

Answer 14

!= or <>

Question 15

Write a WHERE clause that returns all emails that are NULL

Answer 15

WHERE email IS NULL;

Question 16

Write a WHERE clause that returns all emails that are not NULL

Answer 16

WHERE email IS NOT NULL;

Question 17

Write a WHERE clause that returns department values that equal ‘Sports’, ‘First Aid’, ‘Toys’, and ‘Garden’

Answer 17

WHERE department IN (‘Sports’, ‘First Aid’, ‘Toys’, ‘Garden’);

Question 18

T/F: BETWEEN 80 AND 100 is inclusive

Answer 18

True

Question 19

Write a WHERE clause that returns ‘salary’ between 80 and 100

Answer 19

WHERE salary BETWEEN 80 AND 100;

Question 20

How do you write comments in an SQL query?

Answer 20

Use – to comment out a line

Question 21

Write a clause that orders the result by employee_id in descending order

Answer 21

ORDER BY employee_id DESC

Question 22

Write a query that returns all the distinct ‘department’ values from the ‘employees’ table

Answer 22

SELECT DISTINCT department FROM employees

Question 23

What does ORDER BY 1 mean?

Answer 23

It sorts in ascending order based on the first column in the table

Question 24

Limit the returned results by 10 using two different methods

Answer 24

LIMIT 10
FETCH FIRST 10 ROWS ONLY

Question 25

Give an alias 'hello there' to 'department' in a query

Answer 25

SELECT department as 'hello there'

Question 26

T/F: AS is required to alias columns

Answer 26

False, you can omit it entirely

Question 27

What's wrong with this query? ``` SELECT MAX(salary), MIN(salary) WHERE last_name != 'Wilson' FROM professors ```

Answer 27

The WHERE clause needs to be AFTER the FROM clause