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)

Which statements would execute successfully?

 

Select one or more:

 a. SELECT student_name,subject1

FROM marks

WHERE subject1 > AVG(subject1);

 b. SELECT student_name,SUM(subject1)

FROM marks

WHERE student_name LIKE 'R%';

c. SELECT SUM (subject1+subject2+subject3)

FROM marks

WHERE student_name IS NULL

d. SELECT SUM (DISTINCT NVL(subject1,0)),MAX(subject1)

FROM marks

WHERE subject1 > subject2;

ANSWER:

  • Loginhackers Blogger
  • Contact us via Twitter Loginhackers
  • Contact us via Instagram
  • loginhackers facebook contact id

Free Premium | Spotify premium for free | Grammarly free premium | Netflix free accounts | Netflix mod | Spotify mod | Hotstar mod | Spotify premium mod apk | free amazon prime account