Runhe Tian Coding Practice

Imagine a simple database storing information for students’ grades. Design what this database might look like, and provide a SQL query to return a list of the honor roll students (top 10%), sorted by their grade point average.

My initial thoughts:

The query is given by:

SELECT TOP 10 PERCENT Student.Name, AVG(Grade) AS GPA
FROM Student JOIN StudentTakesCourse
GROUP BY Student.Name
ORDER BY GPA

Solution:

In a simplistic database, we’ll have at least these three objects: Students, Courses, and courseEnrollment. Students will have at least the student name and ID, and will likely have other personal information. Courses will contain the course name and ID, and will likely contain the course description, professor, etc. CourseEnrollment will pair Students and Courses, and will also contain a field for CourseGrade. We will assume that CourseGrade is an integer.

Our SQL query to get the list of honor roll students might look like this:
SELECT StudentName, GPA
FROM (
  SELECT top 10 percent Avg(CourseEnrollment.Grade) AS GPA,
    CourseEnrollment.StudentID
  FROM CourseEnrollment
  GROUP BY CourseEnrollment.StudentID
  ORDER BY Avg(CourseEnrollment.Grade)) Honors
INNER JOIN Students ON Honors.StudentID = Students.StudentID

This database could get arbitrarily more complicated if we wanted to add in professor information, billing, etc.
	

What are the different types of joins? Please explain how they differ and why certain types are better in certain situations.

Solution:
JOIN is used to combine the results of two tables. To perform a join, each of the tables must have at least one field which will be used to find matching records from the other table. The join type defines which records will go into the result set.
Let’s take for example two tables: one table lists “regular” beverages, and another lists the calorie-free beverages. Each table has two fields: the beverage name and its product code. The “code” field will be used to perform the record matching.

Regular Beverages:

  

    
Name
    
Code
  
  

    
Budweiser
    
BUDWEISER
  

    
Coca-Cola
    
COCACOLA
  

    
Pepsi
    
PEPSI
  

Calorie-Free Beverage:

  

    
Code
    
Name
  
  

    
COCACOLA
    
Diet Coca-Cola
  

    
FRESCA
    
Fresca
  

    
PEPSI
    
Diet Pepsi
  

    
PEPSI
    
Pepsi Light
  

    
PEPSI
    
Purified Water
  

Let’s join this table by the code field. Whereas the order of the joined tables makes sense in some cases, we will consider the following statement:

[Regular Beverage] JOIN [Calorie-Free Beverage]

i.e. [Regular Beverage] is from the left of the join operator, and [Calorie-Free Beverage] is from the right.

INNER JOIN: Result set will contain only those data where the criteria match. In our example we will get 3 records: 1 with COCACOLA and 2 with PEPSI codes.

  

    
Regular_Beverages.Name
    
Code
Calorie-Free_Beverages.Name
  
  

    
Coca-Cola
    
COCACOLA
Diet Coca-Cola
  

    
Pepsi
    
PEPSI
Diet Pepsi
  

    
Pepsi
    
PEPSI
Pepsi Light
  



OUTER JOIN: OUTER JOIN will always contain the results of INNER JOIN, however it can contain some records that have no matching record in other table. OUTER JOINs are divided to following subtypes:

LEFT OUTER JOIN, or simply LEFT JOIN: The result will contain all records from the left table. If no matching records were found in the right table, then its fields will contain the NULL values. In our example, we would get 4 records. In addition to INNER JOIN results, BUDWEISER will be listed, because it was in the left table.

  

    
Regular_Beverages.Name
    
Code
Calorie-Free_Beverages.Name
  

    
Budweiser
    
BUDWEISER
NULL
  
  

    
Coca-Cola
    
COCACOLA
Diet Coca-Cola
  

    
Pepsi
    
PEPSI
Diet Pepsi
  

    
Pepsi
    
PEPSI
Pepsi Light
  



RIGHT OUTER JOIN, or simply RIGHT JOIN: This type of join is the opposite of LEFT JOIN; it will contain all records from the right table, and missing fields from the left table will contain NULL. If we have two tables A and B, then we can say that statement A LEFT JOIN B is equivalent to statement B RIGHT JOIN A. In our example, we will get 5 records. In addition to INNER JOIN results, FRESCA and WATER records will be listed.

  

    
Regular_Beverages.Name
    
Code
Calorie-Free_Beverages.Name
  
  

    
Coca-Cola
    
COCACOLA
Diet Coca-Cola
  

    
NULL
    
FRESCA
Fresca
  

    
Pepsi
    
PEPSI
Diet Pepsi
  

    
Pepsi
    
PEPSI
Pepsi Light
  

    
NULL
    
Water
Purified Water
  



FULL OUTER JOIN. This type of join combines the results of LEFT and RIGHT joins. All records from both tables will be part of the result set, whether the matching record exists in the other table or not. If no matching record was found then the corresponding result fields will have a NULL value. In our example, we will get 6 records.

  

    
Regular_Beverages.Name
    
Code
Calorie-Free_Beverages.Name
  

    
Budweiser
    
BUDWEISER
NULL

  

    
Coca-Cola
    
COCACOLA
Diet Coca-Cola
  

    
NULL
    
FRESCA
Fresca
  

    
Pepsi
    
PEPSI
Diet Pepsi
  

    
Pepsi
    
PEPSI
Pepsi Light
  

    
NULL
    
Water
Purified Water
  





	

Write a method to find the number of employees in each department.

My initial thoughts(code):

SELECT Department.DepartmentName, COUNT(employee.lastName)
FROM employee, department 
WHERE employee.DepartmentID = department.DepartmentID
GROUP BY Department.DepartmentName

Solution:
SELECT Dept_Name, Departments.Dept_ID, COUNT(*) AS 'num_employees'
FROM Departments
LEFT JOIN Employees
ON Employees.Dept_ID = Departments.Dept_ID
GROUP BY Departments.Dept_ID, Dept_Name