SUBQUERY
MULTIPLE CHOICE QUESTION
ANSWER:
1)
SELECT cust_city. COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
Which statement is true regarding the outcome of the above query?
Select one:
a. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
b. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
c. It executes successfully.
d. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
e. Date functions
2)
The COMMISSION column shows the monthly commission earned by the employee.
Emp_Id Dept_Id Commission
1 10 500
2 20 1000
3 10
4 10 600
5 30 800
6 30 200
7 10
8 20 300
Which tasks would require subqueries or joins in order to be performed in a single
step?
Select one or more:
a. Listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
b. Finding the number of employees who earn a commission that is higher than the average commission of the company
c. Listing the employees whose annual commission is more than 6000
d. Finding the total commission earned by the employees in department 10
e. Listing the departments whose average commission is more that 600
f. Listing the employees who earn the same amount of commission as employee 3.
3)
Which SQL statement produces an error?
Select one:
a. SELECT department_id, SUM(salary)
FROM emp_dept_vu
GROUP BY department_id;
b. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vu
GROUP BY department_id, job_id;
c. SELECT *
FROM emp_dept_vu;
d. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
e. None of the statements produce an error; all are valid.
4)
To create a report displaying employee last names, department names, and locations. Which query should you use to create an equijoin?
Select one:
a. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments d
WHERE manager_id =manager_id;
b. SELECT employees.last_name, departments.department_name,
departments.location_id FROM employees e, departments d
WHERE e.department_id =d.department_id;
c. SELECT last_name, department_name, location_id
FROM employees , departments ;
d. SELECT e.last_name, d.department_name, d.location_id
FROM employees e, departments d
WHERE e.department_id =d.department_id;
5)