5. Now, take your SQL statement from #3 above (the one that is not rounded) and

Question

5. Now, take your SQL statement from #3 above (the one that is not rounded) and instead of using the string LIKE ‘%REP%’   use the string LIKE '%SA_REP%' in order to attempt to limit the output to all sales representatives.

Is the output different from #2?

If it is different, why do you think it is?

6. Create an SQL statement that displays a report containing departments, the job ids, and sums of the salaries from the EMPLOYEES table. We only want to see those departments with a department id greater than 40. The output should be grouped by the department id and then the job id and the output order should be by department id.  

See output below:

st)SQL | All DEPARTMENT-ID Rows Fetched: 13 in 0 seconds JOB-ID SUM SALARY) 50 SH CLERK 50 ST CLERK 50 ST MAN 60 IT PROG 70 PR REP 80 SA MAN 80 SA REP 90 AD PRES 90 AD VP 64300 55700 36400 28800 10000 61000 243500 24000 34000 39600 12008 8300 12008 3 4 7 8 10 100 FI ACCOUNT 100 FI MGR 110 AC ACCOUNT 110 AC MGR 12 13

Explanation / Answer

Since you haven't provided #3 It's not possible to solve #5, since they are directly correlated. But I'm assuming that you only meant to ask the #6, since you have provided the table output as well and that #5 was attached by mistake. If not, then post the #3 SQL statement and answer as a comment, I will be happy to help you.

Explaination:

First write the colums you want to display with (SELECT <column name>). Then write the table you need the columns from (FROM Table_name). Next add the condition clause i.e. (WHERE <CONDITION>). In this case the condition is DEPARTMENT_ID>40. Next write the GROUP BY part to group the results according to the column names. Then finally use ORDER BY, to order the results according to the given field (in this case DEPARTMENT_ID). The order is ascending by default.

Hence the SQL Statement:

SELECT DEPARTMENT_ID, JOB_ID, SUM(SALARY)

FROM EMPLOYEES WHERE DEPARTMENT_ID > 40

GROUP BY DEPARTMENT_ID, JOB_ID

ORDER BY DEPARTMENT_ID;

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.