Skip to main content

Question 7: Group By

Examine the description of the EMPLOYEES table:

EMP_ID NUMBER(4) NOT NULL
LAST_NAME VARCHAR2(30) NOT NULL
FIRST_NAME VARCHAR2(30)
DEPT_ID NUMBER(2)
JOB_CAT VARCHARD2(30)
SALARY NUMBER(8,2)

Which statement shows the maximum salary paid in each job category of each department?


A.SELECT dept_id, job_cat, MAX(salary) FROM employees
WHERE salary > MAX(salary);

B.SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id, job_cat;

C. SELECT dept_id, job_cat, MAX(salary) FROM employees;

D. SELECT dept_id, job_cat, MAX(salary) FROM employees
GROUP BY dept_id;


Answer: B


Explanation:
This answer provides correct syntax and semantics to show the maximum salary paid in each job category of each department.



Incorrect Answers

A: This query will not return any row because condition SALARY > MAX(SALARY) is FALSE.

C: This query will return error because you cannot show maximum salary with DEPT_ID and JOB_CAT without grouping by these columns.

D: The GROUP BY clause is missing JOB_ID column.

E: You don't need to group results of query by SALARY in the GROUP BY column.
Labels:

Comments

Post a Comment

Popular posts from this blog

Question 8: Aggregate Functions

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two) A. SUM(start_date) B. AVG(start_date) C. COUNT(start_date) D. AVG(start_date, end_date) E. MIN(start_date) F. MAXIMUM(start_date) Answer: C & E Explanation: It is possible to apply COUNT() and MIN() functions on the column with DATE data type. Incorrect Answers A: Function SUM() cannot be used with DATE data type column. B: Function AVG() cannot be used with DATE data type column. D: Function AVG() cannot be used with DATE data type column, and function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement. F: There is no MAXIMUM() function in Oracle, only MAX() function exists.
Post a Comment
Read more

18. ADDING CHECK CONSTRAINT WHILE INSERTING.

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) SAL NUMBER MGR_ID NUMBER DEPARTMENT_ID NUMBER You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below: EMPLOYEE_ID: Next value from the sequence EMP_ID_SEQEMP_NAME and JOB_ID: As specified by the user during run time, through substitution variables SAL: 2000 MGR_ID: No value DEPARTMENT_ID: Supplied by the user during run time throughsubstitutionvariable. The INSERT statement should fail if the user supplies a value other than20 or 50. Which INSERT statement meets the above requirements? A. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL,&did); B. INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '...
Post a Comment
Read more

Question 6: ORDER BY with NULL

You are sorting data in a table in you SELECT statement in descending order. The column you are sorting on contains NULL records, where will the NULL record appears? A.At the beginning of the list. B.At the end of the list. C.In the middle of the list. D.At the same location they are listed in the unordered table. Answer: A Explanation: When sorting a column with null values in ascending order then the oracle places the Null values at the end of the list if the sorting is in descending order the oracle places the null values at the start of the list.
Post a Comment
Read more