Sample SQL Questions
You're doing research about academic achievement, and you're studying one particular group of
students at a local university. Given a table of the students' grades, your task is to find the average
grade of the five most successful students in this group.
The information about the students' grades is stored in a table called students, which has the
● student_id: the unique identifier of the student;
● student_name: the name of the student;
● grade: the grade that the student achieved.
Your task is to write a select statement which outputs one column average_grade and one row that
contains the average grade of the five students with the highest grades. The answer should be
formatted to 2 digits after the decimal point. It is guaranteed that the five most successful students
can be uniquely identified.
For the following table students
student_id student_name grade
1 Oliver Smith 3.2
2 Jacob Bell 2.9
3 William Thompson 3.1
4 Sophie Clark 3.5
5 Daniel Palmer 3.6
6 Emily Morris 4.0
7 Zachary Mills 2.5
the output should be
The top 5 grades for this group are 4.0, 3.6, 3.5, 3.2, and 3.1, which sums to 17.4, so the average
is 17.4 / 5 = 3.48.
You own a small company, and you keep track of its income in the accounting table, which has the
● date: a unique date on which your company was open;
● profit: the amount of money your company earned that day;
● loss: the amount of money your company lost that day.
You've decided to sell the company, and in order to make the offer more appealing to potential
buyers you need to create a financial report.
Given the accounting table, write a select statement which returns three columns: year, quarter
and net_profit. The first column should contain the year, the second one should contain the quarter
of that year, and the third one should contain the net income (profit - loss difference) of your
company during that period. The output should be sorted by the year in ascending order. If there are
several rows with the same year, sort them by the quarter in ascending order.
Don't include year/quarter in the answer if there is no entry for it in the accounting table.
For the following table accounting
date profit loss
2006-01-01 100 15
2006-07-15 40 100
2006-08-01 50 50
2006-11-11 100 50
2006-12-01 50 80
2007-05-03 42 16
the output should be
year quarter net_profit
2006 1 85
2006 3 -60
2006 4 20
2007 2 26
Your company has fallen on hard times, and you have to let some of your employees go. You figure
it will be easier to fire an entire department all at once, so now you want to determine which
department it's going to be.
Information about your employees and departments is stored in two tables, employees and
departments, respectively. Here are their structures:
○ id: unique department id
○ name: department name
○ id: unique employee id
○ full_name: employee's full name
○ department: foreign key referencing departments.id
○ salary: employee's salary
To choose the unfortunate department, you set a number of criteria: you are willing to get rid of any
department that has no more than 5 employees. Among these smaller departments, you will
consider those where the total salary of all its employees is maximal. Lastly, to make a tough
situation more fair, you decide to make the final choice from the remaining departments at random.
Thus, you'd like to write a select statement that lists departments:
● select all departments with less than 6 employees;
● sort these departments by the total salary of its workers in descending order (in the case of a
tie, the department with the greatest number of employees should go first; if it's still not
enough to break a tie, the department with the smallest id should go first);
● cross out the departments at the even rows and leave only those in the odd positions, to
consider them more thoroughly afterwards.
Given tables employees and departments, your task is to write a select statement described
above. The output should have columns dep_name (the name of the department), emp_number (the
number of employees in this department), and total_salary (the sum of all employees' salaries in
this department) and be sorted according to the specifications above.
For the following tables departments
id full_name salary department
1 James Smith 20 1
2 John Johnson 13 1
3 Robert Jones 15 1
4 Michael Williams 15 1
5 Mary Troppins 17 1
8 Penny Old 14 2
9 Richard Young 17 2
10 Drew Rich 50 3
the output should be
dep_name emp_number total_salary
IT 5 80
HR 2 31
All three departments have 5 or fewer employees, so they are all candidates to be fired. When
sorted in descending order by total_salary, the Sales department becomes the second (i.e. is
located at an even row), so it's not present in the resulting table.Computer science
DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma
Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t
Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th
1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of
1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of