Please use sql to answer the following question an erd also provide as the picture shown COMPI22 W21 ERS 100ASGM1 ORDER ITEMSPF. ORDER IDP TEM IDF PRODUCT IDQUANTITY* UNIT PRICENUMBER (12)NUMBER (12)NUMBER (12)NUMBER (8,2)NUMBER (82)COMPI22 W21 ERS 100ASGM1 ORDERSP. ORDER IDF CUSTOMER_IDSTATUSCOMPI22 W21 ERS 100 ASGM1 EMPLOYEESP. EMPLOYEE ID* FIRST NAMELAST NAMEEMAIL• PHONE* HIRE DATEMANAGER IDJOB TITLENUMBERNUMBERVARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (50 BYTE)DATENUMBER (12)VARCHAR2 (255 BYTE)NUMBER (6)VARCHAR2 (20 BYTE)NUMBER (6)DATESALESMAN ID* ORDER DATEPK ASGM1 ORDER_ITEMS (ORDER_ID, ITEM_ID)O FK ORDER_ITEMS_ORDERS (ORDER_ID)FK ORDER_ITEMS PRODUCTS (PRODUCT_ID)O PK ASGM1 ORDER_ITEMS (ORDER_ID, ITEM_ID)ASGM1 ORDERS PK (ORDER ID)O FK ORDERS_CUSTOMERS (CUSTOMER_ID)FK ORDERS EMPLOYEES (SALESMAN_D)ASGM1 EMPLOYEES PK (EMPLOYEE ID)Og FK EMPLOYEES MANAGER (MANAGER_ID)COMP122 W21 ERS 100.ASGM1_INVENTORIESPF PRODUCT_IDPF WAREHOUSE ID* QUANTITYNUMBER (12)NUMBER (12)NUMBER (8)PK ASGM1_INVENTORIES (PRODUCT_ID, WAREHOUSE ID)FK INVENTORIES PRODUCTS (PRODUCT_ID)FK_INVENTORIES_WAREHOUSES (WAREHOUSE ID)O PK ASGM1_INVENTORIES (PRODUCT_ID, WAREHOUSE ID)COMP122_W21_ERS_100 ASGM 1_CUSTOMERSP CUSTOMER_ID NUMBERNAMEVARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)NUMBER (8,2)COMP122 W21_ERS_100 ASGM1_PRODUCTSP PRODUCT_D* PRODUCT NAMEDESCRIPTIONADDRESSWEBSITECREDIT LIMITNUMBERVARCHAR2 (255 BYTE)VARCHAR2 (2000 BYTE)NUMBER (92)NUMBER (92)NUMBERASGM1 CUSTOMERS PK (CUSTOMER_ID)STANDARD COoSTLIST PRICE• CATEGORY IDASGM1_PRODUCTS PK (PRODUCT_ID)FK ASGM1 PRODUCTS CATEGORIES (CATEGORY ID)COMP122 W21 ERS 100 ASGM1 WAREHOUSESCOMP122 W21 ERS 100.ASGM1 CONTACTSP. WAREHOUSE IDWAREHOUSE NAMELOCATION_IDP CONTACT ID* FIRST_NAMELAST NAMEEMAILPHONECUSTOMER IDNUMBERVARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (255 BYTE)VARCHAR2 (20 BYTE)NUMBERNUMBERVARCHAR2 (255 BYTE)NUMBER (12)IFa ASGM1_WAREHOUSES PK (WAREHOUSE ID)COMP122 W21_ERS 100 ASGM1_PRODUCT CATEGORIESP. CATEGORY ID• CATEGORY NAMEFK _WAREHOUSES_LOCATIONS (LOCATION_ID)NUMBERVARCHAR2 (255 BYTE)> ASGM1 CONTACTS_PK (CONTACT_ID)FK CONTACTS CUSTOMERS (CUSTOMER_ID)ASGM1_PRODUCT CATEGORIES_PK (CATEGORY_ID)COMP122 W21 ERS 100 ASGM1 LOCATIONSP. LOCATION IDADDRESSPOSTAL CODENUMBERVARCHAR2 (255 BYTE)VARCHAR2 (20 BYTE)VARCHAR2 (50 BYTE)VARCHAR2 (50 BYTE)CHAR (2 BYTE)COMP122 W21 ERS 100.ASGM1 COUNTRIESCHAR (2 BYTE)VARCHAR2 (40 BYTE)NUMBERCOMP122 W21 ERS 100 ASGM1 REGIONSP. REGION IDREGION NAMECOUNTRY IDCOUNTRY NAMECITYNUMBERSTATEVARCHAR2 (50 BYTE)REGION IDCOUNTRY_ID- ASGM1_COUNTRIES_PK (COUNTRY_ID)a ASGM1 REGIONS PK (REGION_ID)ASGM1 LOCATIONS PKLOCATION IDA 1. Display the employee number, full employee name, job title, and hire date of allemployees hired in September with the most recently hired employees displayedfirst.Enlevee NteName12 James, Elliott Accountant11 Ramirez, Tyler Accountant27 Long, Kai2Rivera, Jude101 Dunn, Annabelle Administration Assistant[September 30oth of 2016][September 28th of 2016][september 2eth of 2016)Stock clerkAdministration Vice President (September 2lst of 2016](September 17th of 2016]Milestone 2 - Week 3 (Question 2, 3, 4, 5, and 6)2. The company wants to see the total sale amount per sales person (salesman) for allorders. Assume that online orders do not have any sales representative. For onlineorders (orders with no salesman ID), consider the salesman ID as 0. Display thesalesman ID and the total sale amount for each employee.Sort the result according to employee number.Enlyee te tat SO $18,245, 463.50$1,884,295.40$3, 525, 462.19$2, 754,951.05$3, 522, 704.53$3, 900, 172.99ו3. 737 ,233 ,$35455565759603. Display customer Id, customer name and total number of orders for customers thatthe value of their customer ID is in values from 35 to 45. Include the customers withno orders in your report if their customer ID falls in the range 35 and 45.Sort the result by the value of total orders.Cuntone Nane38 Kraft Meinz37 Hunt sman35 Kimberly-Ciark40Fluor39 Lear36 Hartford Finaneial services Group41 ABCOM43 Facebook42 Becton Dickinson45 Centurylink44 Jabil Cireuit

1) SELECT Employee Number, Full Name as ‘Employee Name’, Job Title, Start Date as ‘Hire Date’ from…

1. Display the employee number, full employee name, job title, and hire date of all employees hired in September with the most recently hired employees displayed first. Eneve Ntea Ne 12 James, Elliott Accountant 11 Ramirez, Tyler Accountant Stoek clerk Sat e [September 30th of 2016] [September 20th of 2016] (september 2eth of 2016) 27 Long, Kai 2Rivera, Jude Administration Vice President [September 21st of 2016] 101 Dunn, Annabelle Administration Assistant [September 17th of 2016]

1. Display the employee number, full employee name, job title, and hire date of all employees hired in September with the most recently hired employees displayed first. Eneve Ntea Ne 12 James, Elliott Accountant 11 Ramirez, Tyler Accountant Stoek clerk Sat e [September 30th of 2016] [September 20th of 2016] (september 2eth of 2016) 27 Long, Kai 2Rivera, Jude Administration Vice President [September 21st of 2016] 101 Dunn, Annabelle Administration Assistant [September 17th of 2016]

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Q: Display the list of students who are involved in the IT club Display the details of all the…

A: First get the studid from Club table and then get those Student details This can be done using inner…

Q: How does the CONCAT_WS function provide advantages over the traditional CONCAT function in SQL?

A: CONCAT_WS function is an SQL function that is used to add two or more strings together with the…

Q: Write an SQL statement to list LastName and FirstName of customers who have purchase an item that…

A: we need to write Write an SQL statement to list LastName and FirstName of customers who have…

Q: Please help with the following: Using oracle sql live or sql developer what sql statements could be…

A: part 1: Here we need to find the number of patients who don't have a phone number, so for this, I…

Q: Oracle SQL Packages Create a stored procedure to display top three of cheapest course in terms of…

A: To return the smallest value of the selected column we use the MIN( ) function. And to return the…

Q: The following figure is part of a university database. LNAME and FNAME stands for last name and…

A: Project operation (denoted by pi (∏) symbol) is used to select desired columns (or attributes) from…

Q: plz help with the following use oracle sql or sql live The procedure should satisfy the following…

A: Answer is given below .

Q: Given two database table i_users(userid,username,email address,isActive,gender) stores the users…

A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…

Q: The first requirement: Using the SQL language, transform the following entity model into tables in…

Q: 1- Give an example of the following below in PL/SQL, and briefly describe the purpose of each. a)…

A: ( A ). Procedure :- A Procedure in PL/SQL is a subprogram unit that comprises of a gathering of…

Q: The following SQL clause(s) are not permitted to be used in a subquery in the WHERE or HAVING…

A: Multicolumn subqueries cannot use the <, >, <=, >= comparison operators.

Q: Create a stored procedure named prc_new_rental to insert new rows in the RENTAL table. The procedure…

A: A stored procedure called prc_new_rental has been developed in response to the request to help with…

Q: Please help with the following: Using oracle sql live or sql developer what sql statements could be…

A: 1.List all the rows in which the prescription dates occur on or after November 14. SELECT * FROM…

Q: PL/SQL please use oracle sql developer. I know it would be hard to slove without the data but please…

A: Query for passenger info between dates.

Q: examples of aggregate functions in SQL

A: Examples of aggregate functions in SQL

Q: Database Q1: Select the correct SQL statement to provide the user Samer with read only permission…

A: Database: A database is an organized collection of structured data, generally stored and…

Q: help with sql class 1. Report the names of the professors who work on 'project01'. 2. Report the…

A: 1. select prof_name from professors where prof_id in(select prof_id from supervise where…

Q: What is the need for transactions in SQL?

A: GIVEN: What is the need for transactions in SQL?

Concept explainers

Multiple Table

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.

Question

Please use sql to answer the following question an erd also provide as the picture shown

COMPI22 W21 ERS 100ASGM1 ORDER ITEMS
PF. ORDER ID
P TEM ID
F PRODUCT ID
QUANTITY
* UNIT PRICE
NUMBER (12)
NUMBER (12)
NUMBER (12)
NUMBER (8,2)
NUMBER (82)
COMPI22 W21 ERS 100ASGM1 ORDERS
P. ORDER ID
F CUSTOMER_ID
STATUS
COMPI22 W21 ERS 100 ASGM1 EMPLOYEES
P. EMPLOYEE ID
* FIRST NAME
LAST NAME
EMAIL
• PHONE
* HIRE DATE
MANAGER ID
JOB TITLE
NUMBER
NUMBER
VARCHAR2 (255 BYTE)
VARCHAR2 (255 BYTE)
VARCHAR2 (255 BYTE)
VARCHAR2 (50 BYTE)
DATE
NUMBER (12)
VARCHAR2 (255 BYTE)
NUMBER (6)
VARCHAR2 (20 BYTE)
NUMBER (6)
DATE
SALESMAN ID
* ORDER DATE
PK ASGM1 ORDER_ITEMS (ORDER_ID, ITEM_ID)
O FK ORDER_ITEMS_ORDERS (ORDER_ID)
FK ORDER_ITEMS PRODUCTS (PRODUCT_ID)
O PK ASGM1 ORDER_ITEMS (ORDER_ID, ITEM_ID)
ASGM1 ORDERS PK (ORDER ID)
O FK ORDERS_CUSTOMERS (CUSTOMER_ID)
FK ORDERS EMPLOYEES (SALESMAN_D)
ASGM1 EMPLOYEES PK (EMPLOYEE ID)
Og FK EMPLOYEES MANAGER (MANAGER_ID)
COMP122 W21 ERS 100.ASGM1_INVENTORIES
PF PRODUCT_ID
PF WAREHOUSE ID
* QUANTITY
NUMBER (12)
NUMBER (12)
NUMBER (8)
PK ASGM1_INVENTORIES (PRODUCT_ID, WAREHOUSE ID)
FK INVENTORIES PRODUCTS (PRODUCT_ID)
FK_INVENTORIES_WAREHOUSES (WAREHOUSE ID)
O PK ASGM1_INVENTORIES (PRODUCT_ID, WAREHOUSE ID)
COMP122_W21_ERS_100 ASGM 1_CUSTOMERS
P CUSTOMER_ID NUMBER
NAME
VARCHAR2 (255 BYTE)
VARCHAR2 (255 BYTE)
VARCHAR2 (255 BYTE)
NUMBER (8,2)
COMP122 W21_ERS_100 ASGM1_PRODUCTS
P PRODUCT_D
* PRODUCT NAME
DESCRIPTION
ADDRESS
WEBSITE
CREDIT LIMIT
NUMBER
VARCHAR2 (255 BYTE)
VARCHAR2 (2000 BYTE)
NUMBER (92)
NUMBER (92)
NUMBER
ASGM1 CUSTOMERS PK (CUSTOMER_ID)
STANDARD COoST
LIST PRICE
• CATEGORY ID
ASGM1_PRODUCTS PK (PRODUCT_ID)
FK ASGM1 PRODUCTS CATEGORIES (CATEGORY ID)
COMP122 W21 ERS 100 ASGM1 WAREHOUSES
COMP122 W21 ERS 100.ASGM1 CONTACTS
P. WAREHOUSE ID
WAREHOUSE NAME
LOCATION_ID
P CONTACT ID
* FIRST_NAME
LAST NAME
EMAIL
PHONE
CUSTOMER ID
NUMBER
VARCHAR2 (255 BYTE)
VARCHAR2 (255 BYTE)
VARCHAR2 (255 BYTE)
VARCHAR2 (20 BYTE)
NUMBER
NUMBER
VARCHAR2 (255 BYTE)
NUMBER (12)
IF
a ASGM1_WAREHOUSES PK (WAREHOUSE ID)
COMP122 W21_ERS 100 ASGM1_PRODUCT CATEGORIES
P. CATEGORY ID
• CATEGORY NAME
FK _WAREHOUSES_LOCATIONS (LOCATION_ID)
NUMBER
VARCHAR2 (255 BYTE)
> ASGM1 CONTACTS_PK (CONTACT_ID)
FK CONTACTS CUSTOMERS (CUSTOMER_ID)
ASGM1_PRODUCT CATEGORIES_PK (CATEGORY_ID)
COMP122 W21 ERS 100 ASGM1 LOCATIONS
P. LOCATION ID
ADDRESS
POSTAL CODE
NUMBER
VARCHAR2 (255 BYTE)
VARCHAR2 (20 BYTE)
VARCHAR2 (50 BYTE)
VARCHAR2 (50 BYTE)
CHAR (2 BYTE)
COMP122 W21 ERS 100.ASGM1 COUNTRIES
CHAR (2 BYTE)
VARCHAR2 (40 BYTE)
NUMBER
COMP122 W21 ERS 100 ASGM1 REGIONS
P. REGION ID
REGION NAME
COUNTRY ID
COUNTRY NAME
CITY
NUMBER
STATE
VARCHAR2 (50 BYTE)
REGION ID
COUNTRY_ID
- ASGM1_COUNTRIES_PK (COUNTRY_ID)
a ASGM1 REGIONS PK (REGION_ID)
ASGM1 LOCATIONS PKLOCATION IDA

1. Display the employee number, full employee name, job title, and hire date of all
employees hired in September with the most recently hired employees displayed
first.
Enlevee NteName
12 James, Elliott Accountant
11 Ramirez, Tyler Accountant
27 Long, Kai
2Rivera, Jude
101 Dunn, Annabelle Administration Assistant
[September 30oth of 2016]
[September 28th of 2016]
[september 2eth of 2016)
Stock clerk
Administration Vice President (September 2lst of 2016]
(September 17th of 2016]
Milestone 2 - Week 3 (Question 2, 3, 4, 5, and 6)
2. The company wants to see the total sale amount per sales person (salesman) for all
orders. Assume that online orders do not have any sales representative. For online
orders (orders with no salesman ID), consider the salesman ID as 0. Display the
salesman ID and the total sale amount for each employee.
Sort the result according to employee number.
Enlyee te tat S
O $18,245, 463.50
$1,884,295.40
$3, 525, 462.19
$2, 754,951.05
$3, 522, 704.53
$3, 900, 172.99
ו3. 737 ,233 ,$3
54
55
56
57
59
60
3. Display customer Id, customer name and total number of orders for customers that
the value of their customer ID is in values from 35 to 45. Include the customers with
no orders in your report if their customer ID falls in the range 35 and 45.
Sort the result by the value of total orders.
Cuntone Nane
38 Kraft Meinz
37 Hunt sman
35 Kimberly-Ciark
40Fluor
39 Lear
36 Hartford Finaneial services Group
41 ABCOM
43 Facebook
42 Becton Dickinson
45 Centurylink
44 Jabil Cireuit

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions

Write queries in SQL to answer each of the following questions: 1. Find all students in Comp. Sci. dept 2. Find all students with total credits > 100 3. Find all students who took course in Spring 2010 (Remove duplicates please) 4. Find all courses taken by the student whose ID is 76543 and also find his name create table student (ID varchar(5), name varchar(20) not null, dept_name varchar(20), tot_cred numeric(3,0), primary key (ID) ); create table takes (ID varchar(5), course_id varchar(8), sec_id varchar(8), semester varchar(6), year numeric(4,0), grade varchar(2), primary key (ID, course_id, sec_id, semester, year) ); insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102'); insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32'); insert into student values ('19991', 'Brandt', 'History', '80'); insert into student values ('23121', 'Chavez', 'Finance', '110'); insert into student values ('44553', 'Peltier', 'Physics', '56'); insert into student values…
PLZ hlp with the following: In SQL If you add the PRIMARY KEY constraint to a column, you must also add a UNIQUE constraint to it. Select one: True False
Schema: Frequents(kid, store) Sells(store, candy) Likes(kid, candy) d) Use SQL to list the names of all the candies that are liked by at least one kid, along with the names of the stores that sell them. Sort the result in alphabetical order by candy name.
Describe the use of the CAST and CONVERT functions in SQL.
Write the SQL syntax to create a table in the database called Employee with the employee first and last name, employee hire date, employee number, employee initial, and employee years.