Question Details

[solution] » Submit one SQL Script that includes SQL statements to answer Part

Brief item decscription

Step-by-step solution file


Item details:

Submit one SQL Script that includes SQL statements to answer Part
More:

Submit one SQL Script that includes SQL statements to answer Part 1, Part 2, and Part 3. Make sure that your SQL script runs without any errors.   

 

Part 1 (3 Points): Creating the database:

 

Create the following tables. The underlined column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements. 

 

customer: (cus_code:integer, cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char, cus_areacode:integer,cus_phone:integer).

 

invoice: (inv_number:integer, cus_code:integer, inv_date:date,              foreign key cus_code references customer(cus_code))

 

line: (inv_number:integer,  prod_code:integer  ,line_units:integer,  foreign key (inv_number) references Invoice(inv_number), foreign key (prod_code) references Product (prod_code) )

 

product:(prod_code:integer, prod_desc:varchar(50), prod_price:integer, prod_quant:integer,vend_code:integer,

foreign key (vend_code) referenecs Vendor(vend_code))

 

vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30 ),vend_areacode:integer,vend_phone:integer)

 

 

 

 

 

Part 2 (3 Points): Inserting data:

 

Insert the following data in the tables using insert into statements:

 

customer:

10010, Ramas, Alfred, A, 615, 8442573

10011, Dunne, Leona, K, 713, 8941238

10012, Smith, Kathy, W, 615, 8942285

10013, Olowski, Paul,F, 615, 2221672 10014, Orlando, Myron, NULL, 615, 2971228

invoice:

1001, 10011, 2008-08-03

1002, 10014, 2008-08-04

1003, 10012, 2008-03-20

1004, 10011, 2008-09-23

line:

1001,  12321, 1

1001,  65781, 3

1002,  34256, 6

1003,  12321, 5

1002,  12333, 6

product:

12321, hammer, 189 ,20, 232

65781, chain, 12, 45, 235

34256, tape, 35, 60, 235 12333, hanger, 200 ,10, 232

vendor:

232, Bryson, Smith, 615, 2233234

235, SuperLoo, Anderson, 615, 2158995

 

Part 3 (19 Points): SQL Queries

 

For each of the following, write SQL statement to produce the required output:

 

(1 Point) List the invoice number and invoice date for all invoices of customer number 10011

select inv_number, inv_date

from invoice

where cus_code = 10011;

 

(1 Point) List the product number and product quantity for products in invoice number 1001

 

(1 Point) List all product description and product price supplied by vendor whose vendor contact  name is Smith

 

(1 Point) Produce a list of product description, vendor name, and vendor phone for all products with price less than 50.

 

(1 Point) For each product bought by a customer, list product description and customer?s first name and last name.

 

(2 Points) How many vendors supplied products that are cheaper than 50?

 

(2 Points) For each vendor, find the cheapest product.

 

(2 Points) For each invoice, find the total price.

 

(2 Points) Find how many products are there in each invoice.

 

(2 Points) Find how many invoices are made by each customer.

 

(2 Points) Find how many products are bought by each customer.

 

(2 Points) Find vendor code, vendor contact and the number of product supplied by each vendor

 

 

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~00066467427.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now