1. Write the SQL code required to list the employee number, last name, first nam

Question

1. Write the SQL code required to list the employee number, last name, first name, and middle initial of all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Sort the results by employee number. Assume case sensitivity.

2. Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted by project number.

3. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGNMENT table. Your query should retrieve the assignment number, employee number, project number, the stored assignment charge (ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS). Sort the results by the assignment number.

4. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked, sorted by employee number.

Explanation / Answer

Solution;

1)

SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL FROM EMPLOYEE WHERE EMP_LNAME LIKE '%Smith%' ORDER BY EMP_NUM

2)

SELECT DISTINCT PROJ_NUM FROM ASSIGNMENT

3)

SELECT ASSIGN_NUM, EMP_NUM, PROJ_NUM, ASSIGN_CHARGE FROM ASSIGNMENT WHERE ASSIGN_CHARGE= (ASSIGN_CHG_HR * ASSIGN_HOURS) ORDER BY ASSIGN_NUM

4)

SELECT SUM(ASSIGN_CHG_HR ) AS TOTALHOUR, SUM((ASSIGN_CHG_HR * TOTALHOUR) AS FINALAMOUNT FROM ASSIGNMENT GROUP BY EMP_NUM ORDER BY EMP_NUM

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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