SQL Subqueries/Joins

Question 1

How are rows in tables normally stored, and how is Database Index a better alternative?
Also the types of Database indexes?

Answer 1

Rows are stored in random order, allowing for no processing when being WRITTEN, but when you are READING from the table, every row must be checked.

Database Index allows for rows to be located faster.
Types of Indexes:
Non-clustered - index structure is separate from the table
Clustered - The table is organized as an index

Question 2

What does B-Tree Index stand for?
What constraints automatically generate a B-Tree index?

Answer 2

B-Tree stands for balanced tree, note Azure Data Studio uses B-Tree indexes.

The UNIQUE and PRIMARY KEY constraints automatically generate a B-Tree index

Question 3

Answer the following questions:

a. What is a subquery?

b. What are the four places we can use a subquery?

c. Subqueries can be either/or what 2 things?

Answer 3

a. A subquery is an inner query that exists in an outer query.

b. Condition in the WHERE clause,
as a table after the FROM clause,
Returning a column after the SELECT clause,
within the HAVING clause

c. Subqueries can be correlated or non-correlated

Question 4

Given the following tables:
Employee(fName, salary, jobID)
Job(jobID, jobTitle)

Using Subqueries:
return the name and salary of all employees that are ‘Programmer’

Answer 4

SELECT fName, salary from Employee,
WHERE jobID = (SELECT jobID from Job where jobTitle = ‘Programmer’);

Question 5

Given the following tables:
Employee(fName, salary, jobID)
Job(jobID, jobTitle)

Using Join:
return the name and salary of all employees that are ‘Programmer’

Answer 5

SELECT fName, salary from Employee e
JOIN Job j on e.jobID = j.jobID
WHERE j.jobTitle = ‘Programmer’;

Question 6

What is the difference between a JOIN and a Subquery?

Answer 6

Join creates a cross product, essentially combining rows from the initial table with the joining table that share the same Primary Key(done using ON)

Subquery just operates and returns a result that can be used in the outer query.

Note: Both can be used to solve the same query, but they solve the same query in very distinct ways

Question 7

What is a Cartesian product?

Answer 7

When using a CROSS JOIN without a WHERE condition, the two tables will become combined and the end result will be the # of rows returned will be equal to #of table1 rows times table2 rows.

Example:
SELECT * FROM Employees - returns 107 rows
SELECT * FROM Job - returns 27 rows

SELECT * FROM Employees. Job - returns 107*27 rows

Question 8

Given the following table:
Employee(name, salary, emp_ID)

Using a Correlated Subquery:
return all info on the highest paid employee

Answer 8

SELECT * FROM Employee e1
WHERE e1.salary > ALL
(SELECT salary from Employee e2
WHERE e2.emp_ID != e1.emp_ID)

or

SELECT * FROM Employee,
WHERE salary = (SELECT max(salary) from Employee)

Question 9

Given the tables:
Employee(name, emp_ID, department_ID)
Department(department_ID, department_name)

Using EXISTS:
return all employees that work in the ‘IT’ department

Answer 9

SELECT * FROM Employee E
WHERE EXISTS (Select * FROM DEPARTMENT d
WHERE d.department_ID = e.department_ID AND department_name = ‘IT’);

Question 10

Given the table:
Employee(name, salary)

Using a derived table:
return the name, salary, and average salary and only show employees that have more than the average

Answer 10

SELECT name, salary, Avg_Salary FROM Employee e1,
(SELECT AVG(salary) as Avg_Salary from Employee e2)
WHERE salary > Avg_Salary

Question 11

What does a UNION do?

Answer 11

Combine the output of multiple queries into one result set

Question 12

Given the table:
Employee(name, salary)

Using CASE:
return all data from the employee table and add a column named salary_level where if the employee makes more than 10000, it displays ‘High salary’ otherwise it displays ‘Low salary’

Answer 12

SELECT name, salary, CASE WHEN salary > 10000 THEN ‘High salary’ ELSE ‘Low Salary’ END AS salary_level

Question 13

Given the following Tables:

Countries(country_id, country_name, region_id)

Regions(region_id, region_name)

Using either subqueries or join:
List all countries in the region Europe

Answer 13

Using Subqueries:
SELECT * from Countries c
WHERE region_id = (SELECT region_id from Regions WHERE region_name = ‘Europe’)

Using JOINs:
SELECT country_id, country_name from Countries c
JOIN Regions r on c.region_id = r.region_id
WHERE region_name = ‘Europe’

Question 14

Given the table:
Employee(name, salary)

A. using Noncorrelated subquery and the IN operator:
return all employees that make more than 13000

B. using Noncorrelated subquery and the ANY operator:
return all employees that make more than 13000

Answer 14

SELECT * from employees
WHERE salary IN (SELECT salary from employees WHERE salary > 13000)

SELECT * from employees
WHERE salary > ANY (SELECT salary from employees WHERE salary = 13000)

Note: salary > ANY, means that salary has to be greater than the lowest value returned by the subquery.

Question 15

Name the 4 types of joins

Answer 15

(INNER) JOIN : returns records that intersect between tables.
note: INNER JOIN is the same as JOIN

LEFT (OUTER) JOIN
and
RIGHT (OUTER) JOIN: Returns all records from the LEFT or RIGHT table

FULL (OUTER JOIN): Returns all records from both tables

Question 16

Given the tables:

Employees(Name, Salary, Emp_ID, Department_ID)
Departments(Department_ID, Department_name, Department_head)

Using Join:
Show the name of every employee, and the name of their department.

Answer 16

SELECT name, department_name from employees e
JOIN departments d on e.department_id = d.department_id

Question 17

Given Two tables:
Campus(has 10 rows)
Room(has 3 rows)

What is the result of this query:
select * from campus
cross join room

Answer 17

10 x 3 = 30
Also known as a Cartesian Product

Question 18

Given the following SELECTS:
SELECT * FROM ROOM1
<*>
SELECT * FROM ROOM2.

What would happen if instead of <*> either UNION, INTERSECT, or EXCEPT were there instead?

Answer 18

Putting UNION between the two selects would return all the results of ROOM1 combined with all the results of ROOM2.

Using INTERSECT would only return the rows that are the exact same.

Using EXCEPT would only return rows in ROOM1 minus any shared rows from ROOM2

Question 19

How can you use OVER() with aggregate functions?

Answer 19

Since aggregate functions only return one value, using a select statement with the OVER() clause applied to an aggregate function allows you to use the aggre function with other columns.

Example: SELECT name, AVG(salary) OVER() FROM Employees