Step-by-step solution file
Submit one SQL Script that includes SQL statements to answer Part
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))
Part 2 (3 Points): Inserting data:
Insert the following data in the tables using insert into statements:
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
1001, 10011, 2008-08-03
1002, 10014, 2008-08-04
1003, 10012, 2008-03-20
1004, 10011, 2008-09-23
1001, 12321, 1
1001, 65781, 3
1002, 34256, 6
1003, 12321, 5
1002, 12333, 6
12321, hammer, 189 ,20, 232
65781, chain, 12, 45, 235
34256, tape, 35, 60, 235 12333, hanger, 200 ,10, 232
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
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
This question was answered on: Feb 21, 2020
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)
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.