Question

In: Computer Science

Problem 44 Write a query to display the employee number, last name, first name, and sum...

Problem 44 Write a query to display the employee number, last name, first name, and sum of invoice totals for all employees who completed an invoice. Sort the output by employee last name and then by first name (Partial results shown in Figure P7.44).

Solutions

Expert Solution

Solution

Problem 44

Explanation

we need to display employee number,last name, first name and sum of invoice totals
for that we can use SELECT statement

employee number E.EMP_NUM AS EMP_NUM
last name E.EMP_LNAME AS EMP_LNAME
first name E.EMP_FNAME AS EMP_FNAME
sum of invoice totals SUM(INV_TOTAL)

we can round it to 2 decimal placces using ROUND function

for grouping
We are using GROUP BY EMP_NUM;
for sorting the name
We are using ORDER BY

SQL Code

SELECT E.EMP_NUM AS EMP_NUM,
E.EMP_LNAME AS EMP_LNAME,
E.EMP_FNAME AS EMP_FNAME,
ROUND(SUM(INV_TOTAL),2) AS TOTALINVOICES
FROM LGEMPLOYEE E, LGINVOICE I
WHERE E.EMP_NUM=I.EMPLOYEE_ID
GROUP BY EMP_NUM,EMP_LNAME,EMP_FNAME
ORDER BY EMP_LNAME,EMP_FNAME;

SCREENSHOT

---

Please check the sql query and let me know if it is working or not

if you have any doubt, please mention it, love to help

all the best


Related Solutions

Write the pseudocode that prompts the user for their first and last name. Display the first...
Write the pseudocode that prompts the user for their first and last name. Display the first initial of their first name and their last name to the user. Ask the user to input a phone number. The program checks which part of Colorado a phone number is from using the values below. If the second digit of the phone number is one of the below digits, print the phone number and which part of Colorado it is from. If none...
Write an SQL query that will output the employee id, first name and hire date from...
Write an SQL query that will output the employee id, first name and hire date from the employee table. Pick only those employees whose employee ID is specified in the employee table (no nulls). If the employee id is 777, name is ABC and hire date is 01-JAN-2016, the output should be like - ' ABC (Employee ID - 777) was hired on 1, January of 2016'. Note - The date should not have preceding zeros.
1. Write the SQL code required to list the employee number, first and last name, middle...
1. Write the SQL code required to list the employee number, first and last name, middle initial, and the hire date. Sort your selection by the hire date, to display the newly hired employees first. 2. Modify the previous query and list the employee first, last name and middle initial as one column with the title EMP_NAME. Make sure the names are listed in the following format: First name, space, initial, space, last name (e.g. John T Doe). Hint: use...
Design and implement a C++ Application that: Interactively input: Employee First Name Employee Last Name Employee...
Design and implement a C++ Application that: Interactively input: Employee First Name Employee Last Name Employee id Employee hours worked per week Employee Pay Rate Menu with Option to: Print out Employee Report in the following report format Search for an Employee Display the Report in Sorted based on Last Name or ID Calculate the Pay Quit Criteria: Be sure to use Parallel Arrays to Store the Employee Information Output must be formatted and line up with the column header...
Create a query that shows the last name, employee ID, hours worked, and overtime amount owed...
Create a query that shows the last name, employee ID, hours worked, and overtime amount owed for hourly employees who earned overtime during week 2. Overtime is paid at 1.5 times the normal hourly rate for all hours worked over 40. Note that the amount shown in the query should be just the overtime portion of the wages paid. Also, this is not a totals query- amounts should be shown for individual workers. QBE grid template. This is the exact...
When all of the entries are valid, display an account number that shows first 3 digits of the telephone number and first two letters of the last name.
When all of the entries are valid, display an account number that shows first 3 digits of the telephone number and first two letters of the last name. using name John Smith phone number 1234567893 Using net beans take the first two letters from smith and first 3 numbers from the phone number and combine them to one line like this sm123.
1. How do I write a query that displays the name (concatenate the first name, middle...
1. How do I write a query that displays the name (concatenate the first name, middle initial, and last name), date of birth, and age for all students? Show the age with no decimal places, and only include those students who are 21 or older. Order by age, as shown below: (Hint: Use the TRUNC function. The ages may be different, depending on the date that the query is run.) SELECT S_FIRST || ' ' || S_MI || ' '...
Write a query to display the columns listed below. For each customer the query should show...
Write a query to display the columns listed below. For each customer the query should show the current system date, the current day (when you do the problem the date and day will be different), the number of characters in the member last name, the last date the customer rented a video and how many total videos the person rented. /* Database Systems, 9th Ed., Coronel/MOrris/Rob */ /* Type of SQL : MySQL */ CREATE SCHEMA IF NOT EXISTS TINY_VIDEO;...
C# (Thank you in advance) Create an Employee class with five fields: first name, last name,...
C# (Thank you in advance) Create an Employee class with five fields: first name, last name, workID, yearStartedWked, and initSalary. It includes constructor(s) and properties to initialize values for all fields. Create an interface, SalaryCalculate, class that includes two functions: first,CalcYearWorked() function, it takes one parameter (currentyear) and calculates the number of year the worker has been working. The second function, CalcCurSalary() function that calculates the current year salary. Create a Worker classes that is derived from Employee and SalaryCalculate...
Create a class Employee. Your Employee class should include the following attributes: First name (string) Last...
Create a class Employee. Your Employee class should include the following attributes: First name (string) Last name (string) Employee id (string) Employee home street address (string) Employee home city (string) Employee home state (string) Write a constructor to initialize the above Employee attributes. Create another class HourlyEmployee that inherits from the Employee class.   HourEmployee must use the inherited parent class variables and add in HourlyRate and HoursWorked. Your HourEmployee class should contain a constructor that calls the constructor from the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT