Our version of the test of knowledge of SQL
We, like many other organizations, are testing applicants for admission to work. The basis of testing is an oral interview, but in some cases, practical tasks are also given. A few days ago, Management asked me to prepare a set of SQL knowledge tasks.
Of course, I tried to make the tasks not too complicated. The level of applicants is different and the tasks, in my opinion, should be structured so that the results of their decision can be judged on how well the subject knows the subject.
Also, it did not make sense to give assignments to the knowledge of any features of certain DBMS. We use Oracle in our work, but this should not create difficulties for applicants who know, for example, only MS SQL or PostgreSQL. Thus, the use of platform-dependent solutions is not forbidden, but is not expected when solving problems.
For testing, a schema containing the following tables was deployed in Oracle 11g:
')
It was required to compile SQL queries to solve the following five tasks:
There was no need to look for an optimal solution in any sense. The only requirement: the request must return the correct answer on any input data. Tasks were allowed to solve in any order, without time limit. With the correct solution of all tasks, the following task of increased complexity was proposed:
Of course, the answers posted here are not the only correct ones. If the applicant's request contains no obvious errors, the results of its execution (for different sets of input data) are compared with the results of the execution of the corresponding reference query.
Of course, I tried to make the tasks not too complicated. The level of applicants is different and the tasks, in my opinion, should be structured so that the results of their decision can be judged on how well the subject knows the subject.
Also, it did not make sense to give assignments to the knowledge of any features of certain DBMS. We use Oracle in our work, but this should not create difficulties for applicants who know, for example, only MS SQL or PostgreSQL. Thus, the use of platform-dependent solutions is not forbidden, but is not expected when solving problems.
For testing, a schema containing the following tables was deployed in Oracle 11g:
')
It was required to compile SQL queries to solve the following five tasks:
Exercise 1
Display a list of employees who receive more wages than the direct manager
Possible answer
select a.* from employee a, employee b where b.id = a.chief_id and a.salary > b.salary Task 2
Display a list of employees receiving the maximum salary in their department
Possible answer
select a.* from employee a where a.salary = ( select max(salary) from employee b where b.department_id = a.department_id ) Task 3
Display a list of department IDs, the number of employees in which does not exceed 3 people
Possible answer
select department_id from employee group by department_id having count(*) <= 3 Task 4
Display a list of employees who do not have a designated manager working in the same department.
Possible answer
select a.* from employee a left join employee b on (b.id = a.chief_id and b.department_id = a.department_id) where b.id is null Task 5
Find a list of department IDs with the maximum total salary of employees
Possible answer
with sum_salary as ( select department_id, sum(salary) salary from employee group by department_id ) select department_id from sum_salary a where a.salary = ( select max(salary) from sum_salary ) There was no need to look for an optimal solution in any sense. The only requirement: the request must return the correct answer on any input data. Tasks were allowed to solve in any order, without time limit. With the correct solution of all tasks, the following task of increased complexity was proposed:
Additional task
Create a SQL query that calculates the product of real values contained in some column of the table
Possible answer
select exp(sum(ln(decode(sign(salary),0,1,-1,-salary,salary)))) *decode(mod(sum(decode(sign(salary),-1,1,0)),2),1,-1,1) *sign(min(abs(salary))) from employee Of course, the answers posted here are not the only correct ones. If the applicant's request contains no obvious errors, the results of its execution (for different sets of input data) are compared with the results of the execution of the corresponding reference query.
Source: https://habr.com/ru/post/181033/
All Articles