Week 7 - SQL

Question 1

What is inner join?

Answer 1

It matches the PK with the FK if a matching tuple exists. Ie. FK is not NULL.

Question 2

What is equijoin?

Answer 2

It is a type of inner join that matches common values using the equality operator ‘=’
EQUIJOIN R1.PK = R2.PK

Question 3

Show the employees who are working in the department ‘Research’.

Answer 3

SELECT Fname, Lname, Address
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=’Research’;

Question 4

What is the OUTER JOIN operator?

Answer 4

returns all rows from both the participating tables regardless of whether there the FK matches the PK, ie. returns tuples where the FK is NULL.

Question 5

What is the LEFT OUTER JOIN operator?

Answer 5

Every tuple (row) in the relation (table1) for the LHS must appear in the result & if no matching tuple exists in the RHS (table 2), add NULL values.

Question 6

What is the RIGHT OUTER JOIN operator?

Answer 6

Every tuple (row) in the relation (table2) for the RHS must appear in the result & if no matching tuple exists in the LHS (table2), add NULL values.

Question 7

Query 1: Show the last name of an employee and the last name of their supervisor, if there exists!

Answer 7

SELECT E.Lname, S.Lname

FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON E.SUPER_SSN = S.SSN)

Question 8

What are we expecting from the below query? What knowledge do we extract?

SELECT E.Fname, E.Minit, E.Lname, D.Dname
FROM EMPLOYEE AS E LEFT OUTER JOIN DEPARTMENT AS D ON E.SSN = D.MGR_SSN

Answer 8

Dname of employees that are managers & NULL for employees who aren’t managers.

Question 9

What aggregate function would you use to calculate how many employees are working in Dept 5?

Answer 9

COUNT( * )

Question 10

Which aggregate function would you use to sum up all salaries of employees in Dept 5.

Answer 10

SUM ( X )

Question 11

What aggregate function would you use to find out the youngest employee in department 5?

Answer 11

MAX( X ) / MIN ( X )

Question 12

What aggregate function would you use to find the average salary of employees in Dept. 5.

Answer 12

AVG( X )

Question 13

What aggregation function would you use to find the correlation between Age and Salary of employees in Dept. 5

Answer 13

CORR(X, Y)

Question 14

Show the maximum, minimum and average salary of those

employees who work in Dept. 5

Answer 14

SELECT MAX (Salary) AS Highest_Sal,
MIN (Salary) AS Lowest_Sal,
AVG (Salary) AS Average_Sal
FROM EMPLOYEE
WHERE DNO = 5;

Question 15

Which aggregate function does NOT discared NULL values?

Answer 15

COUNT(*)

Question 16

Can we define our own function?

Answer 16

Yes eg, calculate the GPA as a weighted some of grades.

Question 17

How do we use the GROUP BY function to Show the number of employees per department & average salary per department?

Answer 17

SELECT DNO, COUNT (*), AVG (Salary)
FROM EMPLOYEE
GROUP BY DNO;

Step 1: Partition EMPLOYEE into separate groups w.r.t. department.
Step 2: For each group, calculate its cardinality (num of tuples per group) and taking the average salary per group.

Actual Analytics Query is: Which is the most populated department?

Question 18

What is the lecturer trying to say about GROUP BY?

Answer 18

Grouping tuples together having the same value, reminds you of the frequency of value being evaluated.