You are given the following data tables: Employees(Employeel D, LastName, FirstName, BirthDate, Notes) Orders(OrderID, CustomerID, EmployeelD, 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", "EmployeelD", and "ShipperID" is number. The data type of "BirthDate" and "OrderDate" is date. "EmployeelD" 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: Find all employees whose last name starts with letter "S". Return employee ID, first name and last name, arranged in ascending order of last name.

You are given the following data tables: Employees(Employeel D, LastName, FirstName, BirthDate, Notes) Orders(OrderID, CustomerID, EmployeelD, 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", "EmployeelD", and "ShipperID" is number. The data type of "BirthDate" and "OrderDate" is date. "EmployeelD" 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: Find all employees whose last name starts with letter "S". Return employee ID, first name and last name, arranged in ascending order of 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

SQL question help
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
SQL query Knowledge
Write a ASQ statement to return to see all of the first names in the People table. SELECT first name FROM people; WHERE FROM People first name; FROM People SELECT first_name; First name WHERE FROM People;
P5
calculate_new_balance Given a starting balance (a number), and a list of transaction tuples, calculate the final balance for an account. Transaction tuples are of the shape ("description", amount, "withdrawal") , or ("description", amount, "deposit"). The last entry in the tuple will be either "withdrawal" or "deposit". Every withdrawal decreases the balance of the account by the specified amount, and every deposit increases the balance. The return value is the new account balance, as a number. (which could be negative) Sample calls should look like: >>> calculate_new_balance(100, [("payday", 20, "deposit"), ("new shoes", 50, "withdrawal"), ("illicit winnings", 200, "deposit")])270>>> calculate_new_balance(100, [])100
Task 3: The Car Maintenance team wants to insert and store the following maintenance types to the MAINTENANCE_TYPES table: ID: 1; Description: Tire Change; Price: 50 ID: 2; Description; Oil Change; Price: 45 ID: 3; Description; Full Cleaning; Price: 100 ID: 4; Description; Gas Pump Change; Price: 145 Add the four maintenance types to the MAINTENANCE_TYPES table.
USE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.
Please explain how the GROUPING SETS clause works, emphasising its primary purpose.
SQL knowledge question
Employees(employee id, first name, last name, salary, hire date, job title, manager id, department id) Departments(department id, department name, location_ id) Which of the following queries will display the name of all employees who does not work for 'EXECUTIVE' department a.SELECT first name, last name FROM employees WHERE department id not in (SELECT department id FROM departments WHERE upper(department name) = 'EXECUTIVE'); b.SELECT first name, last name FROM employees WHERE department id != (SELECT department id FROM departments WHERE upper(department name) = 'EXECUTIVE'); c.SELECT first name, last name FROM employees WHERE department id = (SELECT department id FROM departments WHERE upper(department name) = 'EXECUTIVE'); %3D d.SELECT first name, last name FROM employees WHERE department id In (SELECT department id FROM departments WHERE upper(department name) = 'EXECUTIVE');
1) Write the SQL code that will create the table structure for a table named EMP. This table is a subset of the EMPLOYEE table. The basic EMP table structure is summarized in the table below. (Note that the EMP_NUM is the PK)? ATTRIBUTE(FIELD) NAME DATADECLARATION Constraints EMP_NUM INT Auto generated Identifier, start from 101 EMP_LNAME VARCHAR(15) Not null EMP_FNAME VARCHAR(15) Not Null EMP_INITIAL CHAR(1) EMP_HIREDATE DATE Default value is system date JOB_CODE CHAR(3) Make sure input is in the range500,501,502