Employees (EmployeelD, LastName, FirstName, BirthDate, Notes) Orders (OrderID, CustomerID, EmployeeÏD, OrderDate, ProductID, UnitPrice, Quantity, ShipperID) Note that: The data type of "LastName", "FirstName", and "Notes" is string. The data type of "OrderID", "ProductID", "UnitPrice", "Quantity", "CustomerID", "EmployeeID", and "ShipperID" is number. The data type of "BirthDate" and "OrderDate" is date. "EmployeeID" in relation "Orders" represents the single employee who handles this order, and it is a foreign key that references "EmployeeID" in relation "Employees". Each order contains only a single type of product with a positive quantity. Question: For each of all employees, use the "GROUP BY" statement to compute the total sales of products in orders he/she handled regardless of the product ID. Return employee ID, employee's last name and the corresponding total sales, arranged in ascending order of employee's last name.

Employees (EmployeelD, LastName, FirstName, BirthDate, Notes) Orders (OrderID, CustomerID, EmployeeÏD, OrderDate, ProductID, UnitPrice, Quantity, ShipperID) Note that: The data type of "LastName", "FirstName", and "Notes" is string. The data type of "OrderID", "ProductID", "UnitPrice", "Quantity", "CustomerID", "EmployeeID", and "ShipperID" is number. The data type of "BirthDate" and "OrderDate" is date. "EmployeeID" in relation "Orders" represents the single employee who handles this order, and it is a foreign key that references "EmployeeID" in relation "Employees". Each order contains only a single type of product with a positive quantity. Question: For each of all employees, use the "GROUP BY" statement to compute the total sales of products in orders he/she handled regardless of the product ID. Return employee ID, employee's last name and the corresponding total sales, arranged in ascending order of employee's last name.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter11: Group Functions

Section: Chapter Questions

Problem 18MC

See similar textbooks

Similar questions

WEEK SQL ASSIGNMENT Complete the following exercise: Alter your student table adding a new column called enroll_date using the datetime data type. Populate the new column (enroll_date) with the current date and time. Using your student table return the student first and last names concatenated with a space and then the enroll_date minus one month. Here is an example of the output: Name Date Jane Smith 2019-05-03 13:14:12 Tom Jones 2020-08-07 05:23:56 Using your student table return the month name for the enroll_date column. For example, if the enroll_date was 11-6-2019 then the month name would be June.
INPUT TABLE You're given a orders table and the columns in the orders table are shown below: Orders Order_Id INT Type VARCHAR(10) Red_Shipping_ Days INT Scheduled_Shipping_Days INT Customer_Id INT Order _city VARCHAR(20) Order_Date DATE Order_Region VARCHAR(15) Order_State VARCHAR(20) Order _Status VARCHAR(20) Shipping_Mode VARCHAR(20) QUERY Calculate count of all the orders. Where Order_ State is Maharashtra Note - Use the alias of oc for count of orders Group the results by Type Order them by oc in ascending order. OUTPUT COLUMNS oc, Type
Consider the tables provided below and answer the following SQL questions: Table 1: Service SereicelD SereiceName Accounting. Security Maintenance Table 2: ServiceSetUp SereicelD SetupFee 2 3 4 100 300 350 Recurring 25 50 125 Tax 10% 11 9% Questions: Write insert statements to enter first two records into Table 1 as shown in above figure. Display a list that presents the data contained in Table 1 sorted by column 2 in descending order. Display the name of the service that has setup fee greater than $300. d. Display average of tax rates for all services.
Please write these statements for (i), (ii) and (iii) using the data and tables I've presented. Similar to how you answered (iv), (v) and (vi)
A Column spanner in a table should describe which of the following?
sql In the orders table, group by ship_via, employee_id, ship_country fields, where the ship_via is less than the employee_id and (condition 1) the ship_country does not end in the letter 'y'(CI) or 'L' (CI) or (condition 2) the employee_id is 4. For each grouping, return the count of the number of items as total and the average freight as avg_fr. Return groupings with avg_fr greater than 30, a total greater than 7, and sort the results by total descending.
Q1: Suppose you want to get all student names that start with letter ‘M’ and end on ‘D’. Write a regular expression to get this type of data from database table. SQl
4.6 LAB - Select horses with logical operators The Horse table has the following columns: ID - integer, primary key RegisteredName - variable-length string Breed - variable-length string Height - decimal number BirthDate - date Write a SELECT statement to select the registered name, height, and birth date for only horses that have a height between 15.0 and 16.0 (inclusive) or have a birth date on or after January 1, 2020
The COUNT(*) function in the SELECT clause is used to return:a. the number of records in the specified tablesb. the number of orders placed by each customerc. the number of NULL values in the specified tablesd. the number of customers who have placed an order
Query 3: Write a parameter query to display the names of all prospects each member tried to recruit based on the member’s first name and the member’s last name you input. List the member’s First Name, member’s Last Name, prospect’s First Name, and prospect’s Last Name (in this order in the query grid). Display the member’s First Name Heading as Member First Name, member’s Last Name Heading as Member Last Name, prospect’s first name heading as Prospect First Name, and prospect’s last name heading as Prospect Last Name. Sort the list by Member Last Name, Member First Name, Prospect Last Name, and Prospect First Name, all ascending order. (WE ARE USING ACCESS SO I AM JUST TRYING TO UNDERSTAND WHAT TO PUT AND PLUG IN ETC. USE MY PICTURES AS REFERRENCE!)
The Driver Relationship team realized that maintaining driver IDs is difficult and requested an automatic way of incrementing the value when a new driver is added. You need to make the changes on the table to automatically increment the Driver ID. After the change, you need to insert the following driver: First Name: Nursin Last Name: Yilmaz Driving License ID: 4141447 Start Date: 2019-12-28 Driving License Checked: True Rating: 4.0 THIS IS IN SQL FORMAT FOR CENGAGE MINDTAP, PLEASE HELP I AM STUCK
Write the pl/sql block of code to call the above created package by passing the employee number in the above created function, run it and take the screenshot of the result and paste it in the output section give below. (SQL)