3. Write the SQL to list all attributes from the ASSIGNMENT table that were assi

Question

3. Write the SQL to list all attributes from the ASSIGNMENT table that were assigned on March 5, 2004. There is no specific order of rows for this problem.

4. Write the SQL to list the attributes: ASSIGN_NUM, ASSIGN_HOURS, and ASSIGN_CHG_HOUR from the ASSIGNMENT table. Add a derived attribute that multiplies the ASSIGN_HOURS and ASSIGN_CHG_HOUR and assign the alias ASSIGN_TOTAL_CHARGE to the derived attribute. Sort the rows by the derived attribute in descending order - that is, highest total charges first.

5. Take the SQL from the previous problem "which is question number 4"(Write the SQL to list the attributes: ASSIGN_NUM, ASSIGN_HOURS, and ASSIGN_CHG_HOUR from the ASSIGNMENT table. Add a derived attribute that multiplies the ASSIGN_HOURS and ASSIGN_CHG_HOUR and assign the alias ASSIGN_TOTAL_CHARGE to the derived attribute. Sort the rows by the derived attribute in descending order - that is, highest total charges first.) and limit the rows returned that have a total charge (the derived attribute) of 378 or greater.

6. Write the SQL to list the attributes ASSIGN_NUM, ASSIGN_HOURS from the ASSIGNMENT table. There is no specific order of rows for this problem.

7. Write the SQL to list the attribute ASSIGN_NUM from the ASSIGNMENT table. Use the aggregate function SUM to total the ASSIGN_HOURS for each ASSIGN_NUM in the table. There is no specific order of rows for this problem. (2 points)
8. Are the results the same or different in problems #6 and problem #7? Why or why not?

9. Write the SQL to list the attribute ASSIGN_DATE from the ASSIGNMENT table. Use the aggregate function SUM to total the ASSIGN_HOURS for each ASSIGN_DATE in the table. Sort the rows by date with most recent date first.

10. Write the SQL to list the attribute ASSIGN_DATE from the ASSIGNMENT table. Use the aggregate function SUM to total the ASSIGN_HOURS for each ASSIGN_DATE in the table. Limit the rows returned to those with the total ASSIGN_HOURS greater than 10. Sort the rows by date by the SUM of the ASSIGN_HOURS in descending order.

Explanation / Answer

3. SELECT * FROM ASSIGNMENT; - You might not need the semi-colon 4. SELECT ASSIGN_NUM, ASSIGN_HOURS * ASSIGN_CHG_HOUR AS ASSIGN_TOTAL_CHARGE FROM ASSIGNMENT ORDER BY ASSIGN_TOTAL_CHARGE DESC; 5. SELECT ASSIGN_NUM, ASSIGN_HOURS * ASSIGN_CHG_HOUR AS ASSIGN_TOTAL_CHARGE FROM ASSIGNMENT WHERE ASSIGN_TOTAL_CHARGE > 377 ORDER BY ASSIGN_TOTAL_CHARGE DESC; 6. SELECT ASSIGN_NUM, ASSIGN_HOURS FROM ASSIGNMENT; 7. SELECT ASSIGN_NUM, SUM(ASSIGN_HOURS) AS ASSIGN_TOTAL_HOURS FROM ASSIGNMENT; 8. This should display the same as the previous one for the fact that your are not doing any math operation against the hours....just using a different way to display the results. 9. SELECT ASSIGN_DATE, SUM(ASSIGN_HOURS) AS TOTAL_ASSIGN_HOURS FROM ASSIGNMENT; 10. SELECT ASSIGN_DATE, SUM(ASSIGN_HOURS) AS TOTAL_ASSIGN_HOURS FROM ASSIGNMENT; WHERE TOTAL_ASSIGN_HOURS < 10 ORDER BY ASSIGN_DATE DESC; Let me know how that works. Happy studies!!

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

---