Home

Topic: Hackerrank

Problem Set 1: SELECT * and Hello World

Problem Set 2: WHERE filters and basic SQL functions

Problem Set 3: Aggregation Functions e.g. SUM(), COUNT(*), etc

Problem Set #4: ORDER BY

Problem Set #5: COUNT(DISTINCT ...)

Problem Set #6: GROUP BY

Problem Set #7: JOINs

Problem Set #8: Common Table Expressions (CTEs)

  1. Write a query that returns the first name, last name, and hire date of every female manager. For this problem, an employee is defined as a manager if the employee existing in the dept_manager table. Write this query using common table expressions (CTEs). (Hint: Use dept_manager and employees tables.)

  2. Repeat question 1 and get the same output, but write the query using no subqueries, just JOINs.

  3. Write a query that returns the first name, last name, and salary of every level of engineer (e.g. Engineer, Senior Engineer, etc) who was hired after 1970. If an employee has multiple salary records, return the highest salary. Write this query using common table expressions (CTEs). (Hint: Use titles, salaries, employees tables.)

  4. Repeat question 3 and get the same output, but write the query using no subqueries, just JOINs.

  5. Write a query that returns the first name, last name, and hire date of every employee who has worked in the Marketing department who makes more than $30k in salary. Write this query using common table expressions (CTEs). (Hint: Use dept_emp and salaries tables.)

  6. Repeat question 5, but write the query using no subqueries, just JOINs.

Sample Solutions for Problem Set #8

Problem Set #9: Real Life Interview Questions