SQL Questions

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Sample SQL Questions

 

top5AverageGrade

 

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

following attributes:

 

● 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.

 

Example

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

average_grade

3.48

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.

netIncome

You own a small company, and you keep track of its income in the accounting table, which has the

following structure:

 

● 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.

 

Example

 

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

unluckyEmployees

 

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:

● departments:

○ id: unique department id

○ name: department name

● employees:

○ 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.

 

Example

For the following tables departments

id name

1 IT

2 HR

3 Sales

and employees

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 

Attachments:

Expert's Answer

2410 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

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. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

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

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

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

. 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 Sea Ports. Here are the classes and their instance variables we wish to define:

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