Choose the letter of the correct answer based on the table EMPLOYEES as shown be
ID: 3902291 • Letter: C
Question
Choose the letter of the correct answer based on the table EMPLOYEES as shown below.
Table 1.0 EMPLOYEES
1.
Which of the following is the correct query that display the Minimum firstname concatenated to lastname? Look for the maximum salary that is less than 10000 per department_id.
Select one:
a. SELECT MIN(FIRSTNAME||LASTNAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY)<10000;
b. SELECT MIN(FIRSTNAME||LASTNAME)
FROM EMPLOYEES
HAVING MAX(SALARY)<10000
GROUP BY DEPARTMENT_ID;
c. SELECT MIN(FIRSTNAME||LASTNAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY);
d. SELECT MIN(FIRSTNAME,LASTNAME)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY)<10000;
2.
Which of the following is the correct query that counts the number of ST_CLERK job_id? Rename the column as “Total no. of ST_CLERK”.
Select one:
a. SELECT COUNT(JOB_ID), AS “TOTAL NO. OF ST_CLERK”
FROM EMPLOYEES
WHERE JOB_ID != ‘ST_CLERK’;
b. SELECT COUNT(JOB_ID), AS TOTAL NO. OF ST_CLERK
FROM EMPLOYEES
WHERE JOB_ID = ‘ST_CLERK’;
c. SELECT COUNT * (JOB_ID), AS “TOTAL NO. OF ST_CLERK”
FROM EMPLOYEES
WHERE JOB_ID = ‘ST_CLERK’;
d. SELECT COUNT(JOB_ID), AS “TOTAL NO. OF ST_CLERK”
FROM EMPLOYEES
WHERE JOB_ID = ‘ST_CLERK’;
3.
Which of the following is the correct query that display the Job_id and add the ff: function:
Count the total number of job_id per distinct values; Compute for the summary of salary per job_id; and Compute for the average salary per job_id
Select one:
a. SELECT COUNT (DISTINCT(JOB_ID) AS “NO. OF JOB_ID”, SUM(SALARY) AS “TOTAL SALARY”, AVG(SALARY) AS “AVERAGE SALARY” FROM EMPLOYEES GROUP BY JOB_ID;
b. SELECT DISTINCT(JOB_ID), COUNT * (JOB_ID) AS “NO. OF JOB_ID”, SUM(SALARY) AS “TOTAL SALARY”, AVG(SALARY) AS “AVERAGE SALARY” FROM EMPLOYEES GROUP BY JOB_ID;
c. SELECT DISTINCT(JOB_ID), COUNT(JOB_ID) AS “NO. OF JOB_ID”, SUM(SALARY) AS “TOTAL SALARY”, AVG(SALARY) AS “AVERAGE SALARY” FROM EMPLOYEES GROUP BY JOB_ID;
d. SELECT DISTINCT(JOB_ID), COUNT(JOB_ID) AS “NO. OF JOB_ID”, SUM(SALARY) AS “TOTAL SALARY”, AVG(SALARY) AS “AVERAGE SALARY” FROM EMPLOYEES;
4.
Which of the following is the correct that display distinct job_id and the the total number per distinct (unique) job_id.
Select one:
a. SELECT DISTICT(JOB_ID), COUNT(JOB_ID) AS “TOTAL NUMBER OF JOB_ID”
FROM EMPLOYEES;
b. SELECT DISTICT((JOB_ID), COUNT(JOB_ID)) AS “TOTAL NUMBER OF JOB_ID”
FROM EMPLOYEES
GROUP BY JOB_ID;
c. SELECT DISTICT(JOB_ID), COUNT*(JOB_ID) AS “TOTAL NUMBER OF JOB_ID”
FROM EMPLOYEES
GROUP BY JOB_ID;
d. SELECT DISTICT(JOB_ID), COUNT(JOB_ID) AS “TOTAL NUMBER OF JOB_ID”
FROM EMPLOYEES
GROUP BY JOB_ID;
5.
Which of the following is the correct query that displays the MINIMUM salary of employees per job_id? Note job_id should be in lowercase.
Select one:
a. SELECT MIN(SALARY) AS LOWEST SALARY, LOWER(JOB_ID)
FROM EMPLOYEES
GROUP BY JOB_ID;
b. SELECT MIN(SALARY) AS “LOWEST SALARY”, LOWER(JOB_ID)
FROM EMPLOYEES
GROUP BY JOB_ID;
c. SELECT MIN(SALARY) AS “LOWEST SALARY”, LOWER(JOB_ID)
FROM EMPLOYEES;
d. SELECT MIN(SALARY) AS LOWEST SALARY, LOWER(JOB_ID)
FROM EMPLOYEES;
D-00000000000000 12211 1 0012304 444, 0 9 9 910115 00000022220444, 00000 Y-000 -477 211 02851655060?0003 64, 5 3 3 2 2 0 1874 3628 PPPs PRC CLARE RE AS A RE MG AC PPPM JO | ADAT IT IT ST ST ST ST SA SA SA SA MK MK AC AC 11- ?s ES 9 HERE E AM IN R N E E LA MB NCTEL TEI I-T F-S BD PEEJK MPSW D-01 2 3 4 7 4 1 2 3 4 946801256 1-00000024. 4 44 7 7 7 OOOOOExplanation / Answer
1. a) is correct because we use || symbol to concatenate two columns and having comes after group by
2. d) is correct because if alias is having space than we need to enclose it wit quotes. and count(job_id) is the right way to do it.
Please post others as saperate question...Let me know in comments if you have any issues.