TECHNICAL TEST

Duration: minutes

SQL Test (1 point for each question)

Chose the correct statement
1) Assume that table tab1 has 3000 records, tab2 has 4000 records. How many rows
the following query returns
select * from tab1, tab2
a) 3000
b) 4000
c) 7000
d) 12000000
e) Depend on how many common fields in tab1 and tab2

2) What will be the result of the query below?

select case when null = null then 'Correct' else 'False' end as Result;
a) False
b) True
c) Null
d) null
e) Correct

3) Assume that table tab1 with id is primary key has 3000 records, tab2 with id is
primary key has 4000 records. How many rows the following query returns
select id from tab1
UNION
select id from tab2;
a) Aways 3000
b) At least 3000
c) Aways 4000
d) At least 4000
e) Aways 7000

4) Assume that table tab1 with id is primary key has 3000 records, tab2 with id is
primary key has 4000 records. How many rows the following query returns
select id from tab1 where exists (select id from tab2 where tab1.id = tab2.id)
UNION ALL
select id from tab2 ;
a) Aways 3000
b) At least 3000
c) Aways 4000
d) At least 4000

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 1 of 8
 e) Aways 7000
5) Which is correct query to find a set of duplicate records with one field (say
field_name) in a given table (say tab)
a) select field_name from tab group by field_name having count(*) > 1;
b) select field_name from tab a where (select count(*) from tab b where a.field_name
= b.field_name) > 1;
c) select field_name from tab a where a.field_name not in (select field_name from tab
b where (select count(*) from tab c where b.field_name = c.field_name) = 1);
d) select field_name from tab a where (select max(field_name) from tab b) > 1;
e) select distinct field_name from (select field_name from tab a where (select
max(field_name) from tab b) > 1);

There are two tables in “test” schema for questions 6,7 as below:

Department Table
DEPT_ID Dept_Name Manager_ID
1 HR 1
2 IT 7
3 Admin 12

Employee Table

EMP_ID NAME GENDER SALARY DEPT_ID

1 Hang Female 1500 1

2 Dung Male 1300 1
3 Cuong Male 1100 1
4 Minh Male 800 1
5 Hoa Female 700 1
6 Linh Female 1100 1
7 Thong Male 2000 2
8 Thinh Male 1700 2
9 Trung Male 1300 2
10 Hoang Female 800 2
11 Hue Female 1400 2
12 Anh Male 1200 3
13 Toan Male 1100 3
14 Tai Male 1000 3
15 Tam Female 1000 3
16 Tue Female 900 3
17 Hai Male 600 3
18 Duc Male 500 3
19 Ly Male 500 3

6) Which is correct query to find the department names which their managers has
highest salary compared with managers of other departments.

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 2 of 8
 a) SELECT d.Dept_name FROM department d, employee e WHERE d.manager_id =
e.emp_id group by salary order by salary desc;
b) SELECT d.Dept_name FROM department d, employee e WHERE d.manager_id =
e.emp_id group by salary order by salary desc limit 1;
c) SELECT d.Dept_name FROM department d, employee e WHERE d.manager_id =
e.emp_id and not exists (select 1 from department d1 where d1.manager_id in
(select emp_id from employee where salary > e.salary))
d) SELECT d.Dept_name FROM department d, employee e WHERE d.manager_id =
e.emp_id and exists (select 2 from department d1 where d1.manager_id not in
(select emp_id from employee where salary > e.salary))
e) SELECT d.Dept_name, max(salary) FROM department d, employee e WHERE
d.manager_id = e.emp_id;
7) Which is correct query to find decending list of the salaries sum of each department
which is greater than 4000 calculating from all employees getting more than 1000.
a) SELECT
d.dept_name, SUM(salary) AS s_salary
FROM
department d,
employee e
WHERE
d.dept_id = e.dept_id
AND e.salary > 1000
GROUP BY e.dept_id
HAVING s_salary > 4000;
b) SELECT
r.dept_name, SUM(salary) AS s_salary
FROM
(SELECT
*
FROM
department d, employee e
WHERE
d.dept_id = e.dept_id
AND e.salary > 1000) AS r
GROUP BY r.dept_id
HAVING SUM(salary) > 4000
ORDER BY s_salary DESC;
c) SELECT
r.dept_name, SUM(salary) AS s_salary
FROM
(SELECT
d.dept_name, e.*
FROM
department d, employee e
WHERE
d.dept_id = e.dept_id
AND e.salary > 1000) AS r
GROUP BY r.dept_id
HAVING SUM(salary) > 4000
ORDER BY s_salary DESC;

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 3 of 8
 d) SELECT
r.dept_name, SUM(salary) AS s_salary
FROM
(SELECT
*
FROM
department d, employee e
WHERE
d.dept_id = e.dept_id
AND e.salary > 1000
GROUP BY r.dept_id
HAVING SUM(salary) > 4000
ORDER BY s_salary DESC) AS r;
e) SELECT
r.dept_name, SUM(salary) AS s_salary
FROM
(SELECT
d.dept_name, e.*
FROM
department d, employee e
WHERE
d.dept_id = e.dept_id
AND e.salary > 1000
GROUP BY r.dept_id
HAVING SUM(salary) > 4000
ORDER BY s_salary DESC) AS r;

For the following relation schema:

employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)

8) Which is correct query to find the names of all employees who earn more than any
employee of “Upskills”. Assume that all people work for at most one company.
a) SELECT
`employee-name`
FROM
works
WHERE
salary > (SELECT
MAX(salary)
FROM
works
WHERE
`company-name` = 'Upskills');
b) SELECT
employee-name
FROM
works

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 4 of 8
 WHERE
salary > (SELECT
MAX(salary)
FROM
works
WHERE
company-name = 'Upskills');
c) SELECT
employee-name
FROM
works
WHERE
salary > (SELECT
MIN(salary)
FROM
works
WHERE
company-name = 'Upskills');
d) SELECT
`employee-name`
FROM
works
WHERE
salary > (SELECT
MIN(salary)
FROM
works
WHERE
`company-name` = 'Upskills');
e) SELECT
`employee-name`
FROM
works w
WHERE
NOT EXISTS( SELECT
1
FROM
works
WHERE
`company-name` = 'Upskills'
AND salary > w.salary);

9) Which is correct query to find a set of all companies located in every city in which
“Upskills” is located (For example, if Upskills is located in A,B,C cities, the satisfied
companies must be located at least in A,B,C).
a) SELECT
s.`company-name`
FROM
company s
WHERE
s.`company-name` <> 'Upskills'

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 5 of 8
 AND NOT EXISTS( SELECT
1
FROM
company
WHERE
`company-name` = 'Upskills'
AND city NOT IN (SELECT
city
FROM
company c
WHERE
c.`company-name` = s.`company-name`));
b) SELECT
s.company-name
FROM
company s
WHERE
s.company-name <> 'Upskills'
AND NOT EXISTS( SELECT
1
FROM
company
WHERE
company-name = 'Upskills'
AND city NOT IN (SELECT
city
FROM
company c
WHERE
c.company-name = s.company-name));
c) SELECT
s.company-name
FROM
company s,
company u
WHERE
s.company-name <> u.company-name
AND u.company-name = 'Upskills'
AND s.city = u.city
GROUP BY s.company-name
HAVING COUNT(*) = (SELECT
COUNT(*)
FROM
company t
WHERE
t.company-name = 'Upskills');
d) SELECT
s.`company-name`
FROM
company s,
company u
WHERE

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 6 of 8
 s.`company-name` <> u.`company-name`
AND u.`company-name` = 'Upskills'
AND s.city = u.city
GROUP BY s.`company-name`
HAVING COUNT(*) = (SELECT
COUNT(*)
FROM
company t
WHERE
t.`company-name` = 'Upskills');
e) SELECT DISTINCT
s.company-name
FROM
company s
WHERE
s.company-name <> 'Upskills'
AND s.city IN (SELECT
city
FROM
company
WHERE
company-name = 'Upskills');
10)Which is correct query to find the name of a company that has the smallest payroll (the
amount of money paid for the employees)
a) SELECT
`company-name`
FROM
works
GROUP BY `company-name`
order by SUM(salary) limit 1;
b) SELECT
`company-name`
FROM
works
GROUP BY `company-name`
order by SUM(salary) desc limit 1;
c) SELECT
company-name
FROM
works
GROUP BY company-name
order by SUM(salary) desc limit 1;
d) SELECT
company-name
FROM
works
GROUP BY company-name
order by SUM(salary) limit 1;
e) SELECT
company-name
FROM
works

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 7 of 8
 GROUP BY company-name
order by SUM(salary);

Upskills Viet Nam Co. Ltd | 6th Floor, CNC Building, 8 – 10 Nguyen Ba Tuyen Street, Ward 12, Tan Binh District,
Ho Chi Minh City, Vietnam | www.upskills.com
Page 8 of 8