Choose the letter of the correct answer based on the table EMPLOYEES as shown be
ID: 3902327 • Letter: C
Question
Choose the letter of the correct answer based on the table EMPLOYEES as shown below.
Table 1.0 EMPLOYEES
Which of the following query is correct which will display the same output as shown below?
1.
Select one:
a. SELECT MAX(FIRSTNAME), MANAGER_ID, COUNT(SALARY), AVG(SALARY)
FROM EMPLOYEES
WHERE SALARY BETWEEN 6000 AND 10000
GROUP BY MANAGER_ID
HAVING (SALARY) >= 5000;
b. SELECT MAX(FIRSTNAME), MANAGER_ID, COUNT(SALARY), AVG(SALARY)
FROM EMPLOYEES
GROUP BY MANAGER_ID
WHERE SALARY BETWEEN 6000 AND 10000
HAVING MIN(SALARY) >= 5000;
c. SELECT MAX(FIRSTNAME), MANAGER_ID, COUNT(SALARY), AVG(SALARY)
FROM EMPLOYEES
WHERE SALARY BETWEEN 10000 AND 6000
GROUP BY MANAGER_ID
HAVING MIN(SALARY) >= 5000;
d. SELECT MAX(FIRSTNAME), MANAGER_ID, COUNT(SALARY), AVG(SALARY)
FROM EMPLOYEES
WHERE SALARY BETWEEN 6000 AND 10000
HAVING MIN(SALARY) >= 5000
GROUP BY MANAGER_ID;
2.
Select one:
a. SELECT MIN(initcap(FIRSTNAME||' surnname is '||LASTNAME)) AS COMPLETE NAME, MIN(MANAGER_ID) AS "LOWEST MANAGER_ID" FROM EMPLOYEES;
b. SELECT MIN(LOWER(FIRSTNAME||' surnname is '||LASTNAME)) AS "COMPLETE NAME", MIN(MANAGER_ID) AS "LOWEST MANAGER_ID" FROM EMPLOYEES;
c. SELECT MIN(LOWER(FIRSTNAME||surnname is ||LASTNAME)) AS "COMPLETE NAME", MIN(MANAGER_ID) AS "LOWEST MANAGER_ID" FROM EMPLOYEES;
d. SELECT MIN(initcap(FIRSTNAME||' surnname is '||LASTNAME)) AS "COMPLETE NAME", MIN(MANAGER_ID) AS "LOWEST MANAGER_ID" FROM EMPLOYEES;
3.
Select one:
a. SELECT MIN(LASTNAME), MIN(JOB_ID), MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
GROUP BY JOB_ID;
b. SELECT MIN(LASTNAME), JOB_ID, MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%';
c. SELECT MIN(LASTNAME), JOB_ID, MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE 'REP%'
GROUP BY JOB_ID;
d. SELECT MIN(LASTNAME), JOB_ID, MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
GROUP BY JOB_ID;
4.
Select one:
a. SELECT MIN(LASTNAME),MAX(FIRSTNAME),SUM(SALARY),AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE = ‘ST’;
b. SELECT MIN(LASTNAME),MAX(FIRSTNAME),SUM(SALARY),AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%ST';
c. SELECT MIN(LASTNAME),MAX(FIRSTNAME),SUM(SALARY),AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%ST%';
d. SELECT MIN(LASTNAME),MAX(FIRSTNAME),SUM(SALARY),AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE 'ST%';
Select one:
a. SELECT MIN(LASTNAME||FIRSTNAME) AS NAME, DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
WHERE DEPARTMENT_ID IN(90,50)
HAVING MIN(DEPARTMENT_ID)<=90;
b. SELECT MIN(LASTNAME||FIRSTNAME) AS NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID NOT IN(90,50)
GROUP BY DEPARTMENT_ID
HAVING(DEPARTMENT_ID)<=90;
c. SELECT MIN(LASTNAME||FIRSTNAME) AS NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(90,50)
HAVING MIN(DEPARTMENT_ID)<=90;
d. SELECT MIN(LASTNAME||FIRSTNAME) AS NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(90,50)
GROUP BY DEPARTMENT_ID
HAVING MIN(DEPARTMENT_ID)<=90;
EMPLOYEE ID FIRSTNAME LASTNAME JOB ID SALARY MANAGER ID DEPARTMENT ID 24000 17000 17000 90 90 90 100 STEVEN 101 NENA 102 LEX 03 ALEXANDER HUNOLD 104 BRUCE 107 DIANA 124 KEVIN 141 TRINA 142 CURTIS 43 RANDALLMATOS 144 PETER 149 ELENI 174 ELLEN 176 JONATHAN TAYLOR 78 KIMBERLEY GRANT 200 JENNIFER WHALEN AD ASST 201 MICHAELHARTSTEIN MK MAN 202 PAT 205 SHELLEYHIGGINS AC MGR 206 WILLIAM GIETZ KING KOCHAR E HAAN AD VP AD PRES AD VP 100 100 101 102 103 100 124 124 124 124 100 149 149 149 101 100 201 101 205 IT PROG IT PROG 6000 4200 5800 3500 3100 2600 2500 10500 11000 8600 7000 4400 13000 6000 12000 8300 ERNST LORENT2 IT PROG MOURGOS ST MAN RAJS DAVIES 50 ST CLERK ST CLERK ST CLERR ST CLERK 50 50 50 80 50 80 VARGAS LOTKEY SA MAN ABEL SA REP SA REP SA REP 10 20 20 110 110 FAY MK REP AC ACCOUNTExplanation / Answer
Note: 4 sub parts at a time please -- Policy of Chegg
1.
Sequence of execution is WHERE GROUP_BY HAVING
Between must be from lower to higher
a. SELECT MAX(FIRSTNAME), MANAGER_ID, COUNT(SALARY), AVG(SALARY)
FROM EMPLOYEES
WHERE SALARY BETWEEN 6000 AND 10000
GROUP BY MANAGER_ID
HAVING (SALARY) >= 5000;
2.
initicap makes the every starting letter of the word capitalized.
After AS, the alias name has to be enclosed in quotation marks
d. SELECT MIN(initcap(FIRSTNAME||' surnname is '||LASTNAME)) AS "COMPLETE NAME", MIN(MANAGER_ID) AS "LOWEST MANAGER_ID" FROM EMPLOYEES;
3.
%REP% means there can be anything before and after REP in the job_id
Min is only to the lastname and salary columns
d. SELECT MIN(LASTNAME), JOB_ID, MAX(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
GROUP BY JOB_ID;
4.
After ST we need more characters which is ST%
d. SELECT MIN(LASTNAME),MAX(FIRSTNAME),SUM(SALARY),AVG(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE 'ST%';