Question: Customer Table: Orders Table: Orderline Table: Parts Table: Salesrep Table: Answer all the questions with query sql code: 1)Find the item classes and the number

Customer Table:

Customer Table: Orders Table: Orderline Table: Orders Table:

Customer Table: Orders Table: Orderline Table: Orderline Table:

Customer Table: Orders Table: Orderline Table: Parts Table:

Customer Table: Orders Table: Orderline Table: Salesrep Table:

Customer Table: Orders Table: Orderline Table:

Answer all the questions with query sql code:

1)Find the item classes and the number of parts under each class. Only show those classes with at least 3 types of parts

2)List the customer number, last name, first name, and complete address of every customer who lives on Pine; that is, whose address contains the letters "Pine".

3)List the customer number, last name, and first name of every customer who has the same credit limit as the largest credit limit awarded to any customer of sales rep 6.

4)List the warehouse number and total dollar amount of all items sold from that warehouse. Rename the calculated column TOTAL_VALUE.

Hint: TOTAL_VALUE=SUM(NUMBER_ORDERED*QUOTED_PRICE).

5)List the order number and order date for every order that contains a part stored in warehouse number 3.

C_NUMBER CLAST CFIRST STREET CITY STATE ZIP CODE BALANCE CREDIT_LIMIT SLSREP_NUMBER 124 Adams Sally 418 Oak Lansing MI 49224 818.75 1000 3 256 Samuels Ann 215 Pete Grant MI 49219 21.5 1500 6 311 Charles Don 48 College Tra MI 49034 825.75 1000 12 315 Daniels Tom 914 Cherry Kent MI 48391 770.75 750 6 405 Williams AL 519 Watson Grant MI 49219 402.75 1500 12 412 Adams Sally 16 Elm Lansing MI 49224 1817.75 2000 3 522 Nelson Mary 108 Pine Ada MI 49441 98.75 1500 - 567 Dinh Tran 808 Ridge Harper MI 48421 402.4 750 6 587 Galvez Mara 512 Pine Ada MI 49441 114.6 1000 6 622 Martin Dan 419 Chip Grant MI 49219 1045.75 1000 3 EDIT ORDER NUMBER ORDER_DATE C_NUMBER 12489 02-Aug-2013 124 12491 02-Aug-2013 311 12494 04-Aug-2013 315 12495 04-Aug-2013 256 12498 05-Aug-2013 522 12500 05-Aug-2013 124 12504 05-Aug-2013 522 ORDER NUMBER PART NUMBER NUMBER ORDERED QUOTED PRICE 12489 AX12 11 21.95 12491 BT04 1 149.99 12491 BZ66 1 399.99 12494 CB03 4 279.99 12495 CX11 2 22.95 12498 AZ52 2 12.95 12498 BA74 4 24.95 12500 BT04 3 149.99 12504 CZ81 2 325.99 PART NUMBER PART_DESCRIPTION UNITS_ON_HAND ITEM_CLASS WAREHOUSE NUMBER UNIT_PRICE AX12 Iron 104 HW 3 24.95 AZ52 Dartboard 20 SG 2 12.95 BA74 Basketball 40 SG 1 29.95 BH22 Cornpopper 95 HW 3 24.95 BT04 Gas Grill 11 AP 2 149.99 BZ66 Washer 52 AP 3 399.99 CA14 Griddle 78 HW 3 39.99 CB03 Bike 44 SG 1 299.99 CX11 Blender 112 HW 3 22.95 CZ81 Treadmill 68 SG 2 349.95 SLSREP_NUMBER SRLAST SRFIRST STREET CITY STATE ZIP CODE TOTAL COMMISSION COMMISSION_RATE 3 Jones Mary 123 Main Grant MI 42919 2150 .05 4 Morton Tom 300 College Flint MI 49227 2075 .06 6 Smith William 102 Raymond Ada MI 49441 4912.5 07 12 Diaz Miguel 419 Harper Lansing MI 49224 2150 05 C_NUMBER CLAST CFIRST STREET CITY STATE ZIP CODE BALANCE CREDIT_LIMIT SLSREP_NUMBER 124 Adams Sally 418 Oak Lansing MI 49224 818.75 1000 3 256 Samuels Ann 215 Pete Grant MI 49219 21.5 1500 6 311 Charles Don 48 College Tra MI 49034 825.75 1000 12 315 Daniels Tom 914 Cherry Kent MI 48391 770.75 750 6 405 Williams AL 519 Watson Grant MI 49219 402.75 1500 12 412 Adams Sally 16 Elm Lansing MI 49224 1817.75 2000 3 522 Nelson Mary 108 Pine Ada MI 49441 98.75 1500 - 567 Dinh Tran 808 Ridge Harper MI 48421 402.4 750 6 587 Galvez Mara 512 Pine Ada MI 49441 114.6 1000 6 622 Martin Dan 419 Chip Grant MI 49219 1045.75 1000 3 EDIT ORDER NUMBER ORDER_DATE C_NUMBER 12489 02-Aug-2013 124 12491 02-Aug-2013 311 12494 04-Aug-2013 315 12495 04-Aug-2013 256 12498 05-Aug-2013 522 12500 05-Aug-2013 124 12504 05-Aug-2013 522 ORDER NUMBER PART NUMBER NUMBER ORDERED QUOTED PRICE 12489 AX12 11 21.95 12491 BT04 1 149.99 12491 BZ66 1 399.99 12494 CB03 4 279.99 12495 CX11 2 22.95 12498 AZ52 2 12.95 12498 BA74 4 24.95 12500 BT04 3 149.99 12504 CZ81 2 325.99 PART NUMBER PART_DESCRIPTION UNITS_ON_HAND ITEM_CLASS WAREHOUSE NUMBER UNIT_PRICE AX12 Iron 104 HW 3 24.95 AZ52 Dartboard 20 SG 2 12.95 BA74 Basketball 40 SG 1 29.95 BH22 Cornpopper 95 HW 3 24.95 BT04 Gas Grill 11 AP 2 149.99 BZ66 Washer 52 AP 3 399.99 CA14 Griddle 78 HW 3 39.99 CB03 Bike 44 SG 1 299.99 CX11 Blender 112 HW 3 22.95 CZ81 Treadmill 68 SG 2 349.95 SLSREP_NUMBER SRLAST SRFIRST STREET CITY STATE ZIP CODE TOTAL COMMISSION COMMISSION_RATE 3 Jones Mary 123 Main Grant MI 42919 2150 .05 4 Morton Tom 300 College Flint MI 49227 2075 .06 6 Smith William 102 Raymond Ada MI 49441 4912.5 07 12 Diaz Miguel 419 Harper Lansing MI 49224 2150 05

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

Customer Table: Orders Table: Orderline Table: Parts Table: Salesrep Table: Answer all the questions with query sql code: 1)Find the customer number, last name, first name, current balance, and sales...

DATABASE MANAGEMENT Part II: SQL Queries (50 marks) Consider the following tables which model certain features of an online shop, allowing users to purchase Page 1 of 3 Database Management 2021...

Database management Part II: SQL Queries (50 marks) Consider the following tables which model certain features of an online shop, allowing users to purchase Page 1 of 3 Database Management 2021...

database management Part II: SQL Queries (50 marks) Consider the following tables which model certain features of an online shop, allowing users to purchase Page 1 of 3 Database Management 2021...

Database Management Systems: Part 1: create table Branch ( branch_id int NOT NULL PRIMARY KEY, branch_name VARCHAR2(50), branch_city VARCHAR2(50), assets NUMBER(11,2)); CREATE TABLE Loan (...

This SQL project in three Phases and the instruction as below: Instructions Follow the steps and let me know if you have any questions. The intent of the project is to create a project you can use in...

-- DDL for Table CUSTOMERT -------------------------------------------------------- CREATE TABLE "CUSTOMERT" ("CUSTOMER_ID" NUMBER(5,0), "CUST_NAME" VARCHAR2(30), "CITY" VARCHAR2(15), "GRADE"...

For how to: https://www.freecodecamp.org/news/how-to-install-mysql-workbench-on-windows/ Take a screen shots of your installation and paste here Watch this tutorial Section on how to use MYSQL work...

I would highly appreciate your kind answers to the questions includedin the attached case study which was provided by my financial accouting professor. thank you. ISSUES IN ACCOUNTING EDUCATION Vol....

You need to first run this SQL script then use it to answer the following questions. /* ADVSQLDBINIT.SQL */ /* Advanced SQL */ /* Script file for MS SQL Server DBMS */ /* This script file creates the...

On April 17, 2013, the Loadstone Mining Company purchased the rights to a coal mine. The purchase price plus additional costs necessary to prepare the mine for extraction of the coal totaled...

Three-month sterling interest-rate futures are quoted as follows on 30 August: 500,000 points of 100% Settlement price September ......................... 91.50 December ............................

If interest rates are expected to _ _ _ _ _ _ _ _ , mortgage real investment trusts ( REITs ) _ _ _ _ _ _ _ _ _ _ _ . Group of answer choices decline; become less attractive rise; become less...

When older adults have difficulty with transportation and communication, or may be reluctant to request needed assistance to which they are entitled, they may be in need of: Group of answer choices...