ZuoTi.Pro
Question

DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP...

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS order_details;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS artists;
DROP TABLE IF EXISTS employees;

-- create tables

CREATE TABLE customers

(
  
customer_id INT ,
  
customer_first_name VARCHAR(20),
  
customer_last_name VARCHAR(20) NOT NULL,
  
customer_address VARCHAR(50) NOT NULL,
  
customer_city VARCHAR(20) NOT NULL,
  
customer_state CHAR(2) NOT NULL,
  
customer_zip CHAR(5) NOT NULL,
  
customer_phone CHAR(10) NOT NULL,
  
customer_fax CHAR(10),
  
CONSTRAINT customers_pk
PRIMARY KEY (customer_id)

);

CREATE TABLE artists

(
  
artist_id INT NOT NULL,
  
artist_name VARCHAR(30),
  
CONSTRAINT artist_pk
PRIMARY KEY (artist_id)

);


CREATE TABLE items

(
  
item_id INT NOT NULL,
  
title VARCHAR(50) NOT NULL,
  
artist_id INT NOT NULL,
  
unit_price DECIMAL(9,2) NOT NULL,
  
CONSTRAINT items_pk
PRIMARY KEY (item_id),

CONSTRAINT items_fk_artists
FOREIGN KEY (artist_id) REFERENCES artists (artist_id)
);

CREATE TABLE employees

(
  
employee_id INT NOT NULL,
  
last_name VARCHAR(20) NOT NULL,
  
first_name VARCHAR(20) NOT NULL,

manager_id INT
,
CONSTRAINT employees_pk
PRIMARY KEY (employee_id),
CONSTRAINT emp_fk_mgr FOREIGN KEY (manager_id) REFERENCES employees(employee_id) );

CREATE TABLE orders

(
  
order_id INT NOT NULL,
  
customer_id INT NOT NULL,
  
order_date DATE NOT NULL,
  
shipped_date DATE,

employee_id INT,
  
CONSTRAINT orders_pk
PRIMARY KEY (order_id),
  
CONSTRAINT orders_fk_customers
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
CONSTRAINT orders_fk_employees
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)

);

CREATE TABLE order_details

(
  
order_id INT NOT NULL,
  
item_id INT NOT NULL,
  
order_qty INT NOT NULL,
  
CONSTRAINT order_details_pk
PRIMARY KEY (order_id, item_id),
  
CONSTRAINT order_details_fk_orders
FOREIGN KEY (order_id)
REFERENCES orders (order_id),
  
CONSTRAINT order_details_fk_items
FOREIGN KEY (item_id)
REFERENCES items (item_id)

);


-- insert rows into tables

INSERT INTO customers VALUES

(1,'Korah','Blanca','1555 W Lane Ave','Columbus','OH','43221','6145554435','6145553928'),

(2,'Yash','Randall','11 E Rancho Madera Rd','Madison','WI','53707','2095551205','2095552262'),

(3,'Johnathon','Millerton','60 Madison Ave','New York','NY','10010','2125554800',NULL),

(4,'Mikayla','Davis','2021 K Street Nw','Washington','DC','20006','2025555561',NULL),

(5,'Kendall','Mayte','4775 E Miami River Rd','Cleves','OH','45002','5135553043',NULL),

(6,'Kaitlin','Hostlery','3250 Spring Grove Ave','Cincinnati','OH','45225','8005551957','8005552826'),

(7,'Derek','Chaddick','9022 E Merchant Wy','Fairfield','IA','52556','5155556130',NULL),

(8,'Deborah','Davis','415 E Olive Ave','Fresno','CA','93728','5595558060',NULL),

(9,'Karina','Lacy','882 W Easton Wy','Los Angeles','CA','90084','8005557000',NULL),

(10,'Kurt','Nickalus','28210 N Avenue Stanford','Valencia','CA','91355','8055550584','055556689'),

(11,'Kelsey','Eulalia','7833 N Ridge Rd','Sacramento','CA','95887','2095557500','2095551302'),

(12,'Anders','Rohansen','12345 E 67th Ave NW','Takoma Park','MD','24512','3385556772',NULL),

(13,'Thalia','Neftaly','2508 W Shaw Ave','Fresno','CA','93711','5595556245',NULL),

(14,'Gonzalo','Keeton','12 Daniel Road','Fairfield','NJ','07004','2015559742',NULL),

(15,'Ania','Irvin','1099 N Farcourt St','Orange','CA','92807','7145559000',NULL),

(16,'Dakota','Baylee','1033 N Sycamore Ave.','Los Angeles','CA','90038','2135554322',NULL),

(17,'Samuel','Jacobsen','3433 E Widget Ave','Palo Alto','CA','92711','4155553434',NULL),

(18,'Justin','Javen','828 S Broadway','Tarrytown','NY','10591','8005550037',NULL),

(19,'Kyle','Marissa','789 E Mercy Ave','Phoenix','AZ','85038','9475553900',NULL),

(20,'Erick','Kaleigh','Five Lakepointe Plaza, Ste 500','Charlotte','NC','28217','7045553500',NULL),

(21,'Marvin','Quintin','2677 Industrial Circle Dr','Columbus','OH','43260','6145558600','6145557580'),

(22,'Rashad','Holbrooke','3467 W Shaw Ave #103','Fresno','CA','93711','5595558625','5595558495'),

(23,'Trisha','Anum','627 Aviation Way','Manhatttan Beach','CA','90266','3105552732',NULL),

(24,'Julian','Carson','372 San Quentin','San Francisco','CA','94161','6175550700',NULL),

(25,'Kirsten','Story','2401 Wisconsin Ave NW','Washington','DC','20559','2065559115',NULL);


INSERT INTO artists(artist_id,artist_name) VALUES

(10, 'Umani'),
(11, 'The Ubernerds'),
(12, 'No Rest For The Weary'),
(13, 'Burt Ruggles'),
(14, 'Sewed the Vest Pocket'),
(15, 'Jess & Odie'),
(16, 'Onn & Onn');


INSERT INTO items (item_id,title,artist_id,unit_price) VALUES

(1,'Umami In Concert',10,17.95),

(2,'Race Car Sounds',11,13),

(3,'No Rest For The Weary',12,16.95),

(4,'More Songs About Structures and Comestibles',12,17.95),

(5,'On The Road With Burt Ruggles',13,17.5),

(6,'No Fixed Address',14,16.95),

(7,'Rude Noises',15,13),

(8,'Burt Ruggles: An Intimate Portrait',13,17.95),

(9,'Zone Out With Umami',10,16.95),

(10,'Etcetera',16,17);

INSERT INTO employees VALUES

(1,'Smith', 'Cindy', null),

(2,'Jones', 'Elmer', 1),

(3,'Simonian', 'Ralph', 2),
(9,'Locario', 'Paulo',1),
(8,'Leary', 'Rhea',9),
(4,'Hernandez','Olivia',9),

(5,'Aaronsen', 'Robert',4),

(6,'Watson', 'Denise',8),

(7,'Hardy', 'Thomas',2);

INSERT INTO orders VALUES

(19, 1, '2012-10-23', '2012-10-28', 6),

(29, 8, '2012-11-05', '2012-11-11', 6),

(32, 11, '2012-11-10', '2012-11-13', NULL),

(45, 2, '2012-11-25', '2012-11-30', NULL),

(70, 10, '2012-12-28', '2013-01-07', 5),

(89, 22, '2013-01-20', '2013-01-22', 7),

(97, 20, '2013-01-29', '2013-02-02', 5),

(118, 3, '2013-02-24', '2013-02-28', 7),

(144, 17, '2013-03-21', '2013-03-29', NULL),

(158, 9, '2013-04-04', '2013-04-20', NULL),

(165, 14, '2013-04-11', '2013-04-13', NULL),

(180, 24, '2013-04-25', '2013-05-30', NULL),

(231, 15, '2013-06-14', '2013-06-22', NULL),

(242, 23, '2013-06-24', '2013-07-06', 3),

(264, 9, '2013-07-15', '2013-07-18', 6),

(298, 18, '2013-08-18', '2013-09-22', 3),

(321, 2, '2013-09-09', '2013-10-05', 6),

(381, 7, '2013-11-08', '2013-11-16', 7),
(413, 17, '2013-12-05', '2014-01-11', 7),

(442, 5, '2013-12-28', '2014-01-03', 5),

(479, 1, '2014-01-30', '2014-03-03', 3),

(491, 16, '2014-02-08', '2014-02-14', 5),


(523, 3, '2014-03-07', '2014-03-15', 3),

(548, 2, '2014-03-22', '2014-04-18', NULL),

(550, 17, '2014-03-23', '2014-04-03', NULL),

(601, 16, '2014-04-21', '2014-04-27', NULL),

(607, 20, '2014-04-25', '2014-05-04', NULL),

(624, 2, '2014-05-04', '2014-05-09', NULL),

(627, 17, '2014-05-05', '2014-05-10', NULL),

(630, 20, '2014-05-08', '2014-05-18', 7),

(651, 12, '2014-05-19', '2014-06-02', 7),

(658, 12, '2014-05-23', '2014-06-02', 7),

(687, 17, '2014-06-05', '2014-06-08', NULL),

(693, 9, '2014-06-07', '2014-06-19', NULL),

(703, 19, '2014-06-12', '2014-06-19', 7),

(778, 13, '2014-07-12', '2014-07-21', 7),

(796, 17, '2014-07-19', '2014-07-26', 5),

(800, 19, '2014-07-21', '2014-07-28', NULL),

(802, 2, '2014-07-21', '2014-07-31', NULL),

(824, 1, '2014-08-01', NULL, NULL),

(827, 18, '2014-08-02', NULL, NULL),

(829, 9, '2014-08-02', NULL, NULL);


INSERT INTO order_details VALUES

(381,1,1),(601,9,1),
(442,1,1),
(523,9,1),

(630,5,1),
(778,1,1),
(693,10,1),
(118,1,1),

(264,7,1),
(607,10,1),
(624,7,1),
(658,1,1),

(800,5,1),
(158,3,1),
(321,10,1),
(687,6,1),

(827,6,1),
(144,3,1),

(479,1,2),

(630,6,2),(796,5,1),(97,4,1),
(601,5,1),

(800,1,1),
(29,10,1),
(70,1,1),


(165,4,1),
(180,4,1),
(231,10,1),


(413,10,1),
(491,6,1),

(607,3,1),
(651,3,1),
(703,4,1),


(802,3,1),
(824,7,2),

(829,1,1),
(550,4,1),
(796,7,1),
(693,6,1),
(29,3,1),
(32,7,1),
(242,1,1),

(298,1,1),
(479,4,1),
(548,9,1),
(627,9,1),

(778,3,1),

(19,5,1),
(89,4,1),

(242,6,1),
(264,4,1),
(550,1,1),


(693,7,3),
(824,3,1),
(829,5,1),
(829,9,1);

Queries that require joins and aggregate functions can be easier to construct when using a view as a “temporary” table. Consider a report to show total sales by artist.

a. First create a view called SalesData that displays the order_id, item_id, the calculated field ItemTotal (which is quantity times price), the title and artist_id.

b. Display the data in the SalesData view sorted by artist_id. Does this help you to “visualize” how to group the data to create the totals?

c. Create a query USING THIS VIEW and the appropriate aggregate function to display artist_id and the total sales for each artist.

d. Now join to the artist table in order to display the artist_name along with the total sales.

Now use this same method to display the total sales per customer.

a. Create a view called SalesData with the appropriate data. At a minimum you will need customer_id and the calculated item total. DO NOT use the customer table in this view, it will be joined later.

b. Display the data in your view sorted by customer_id. Does this help you to “visualize” how to group the data to create the totals?

c. Create a query USING THIS VIEW and the appropriate aggregate function to display customer_id and the total sales for each customer.

d. Now join to the customer table in order to display the customer_name as a single field named Customer along with the total sales. Sort the report by Total sales in descending order.

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Part a)

mysql> create view SalesData AS select order_details.order_id, order_details.item_id, items.unit_price*order_details.order_qty as ItemTotal, items.title, items.artist_id from order_details natural join items;
Query OK, 0 rows affected (0.05 sec)

Part b)
mysql> select * from SalesData order by artist_id;
+----------+---------+-----------+---------------------------------------------+
-----------+
| order_id | item_id | ItemTotal | title |
artist_id |
+----------+---------+-----------+---------------------------------------------+
-----------+
| 70 | 1 | 17.95 | Umami In Concert |
10 |
| 118 | 1 | 17.95 | Umami In Concert |
10 |
| 242 | 1 | 17.95 | Umami In Concert |
10 |
.
.
.
There were 57 records in total. Yes it does help to visualize how the data should be grouped to calculate the totals!

Part c)
mysql> select artist_id, sum(ItemTotal) as TotalSales from SalesData group by artist_id;
+-----------+------------+
| artist_id | TotalSales |
+-----------+------------+
| 10 | 318.10 |
| 12 | 279.20 |
| 13 | 105.00 |
| 14 | 118.65 |
| 15 | 117.00 |
| 16 | 102.00 |
+-----------+------------+
6 rows in set (0.02 sec)

Part d)
mysql> select artist_name, sum(ItemTotal) as TotalSales from SalesData NATURAL JOIN artists group by artist_id;
+-----------------------+------------+
| artist_name | TotalSales |
+-----------------------+------------+
| Umani | 318.10 |
| No Rest For The Weary | 279.20 |
| Burt Ruggles | 105.00 |
| Sewed the Vest Pocket | 118.65 |
| Jess & Odie | 117.00 |
| Onn & Onn | 102.00 |
+-----------------------+------------+
6 rows in set (0.00 sec)

0 0
Add a comment
Know the answer?
Add Answer to:
DROP TABLE IF EXISTS customers; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS order_details; DROP...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • 2. The Vice President of Marketing for your firm wants the firm’s sales representatives to be...

    2. The Vice President of Marketing for your firm wants the firm’s sales representatives to be able to directly view and edit customer details, but only for the state to which a particular sales representative is assigned. You have suggested that this need can be addressed with a view. For example, a view could be created for one particular state, and user account permissions for accessing that view granted only to sales representatives from that state. The VP has asked...

  • Need help converting into sql language. How many players from each town served on the committee...

    Need help converting into sql language. How many players from each town served on the committee in any capacity? Display the town as ‘Town’ and the number served as ‘Committee Service’.   Insert your screenshot here. /* ******************************************************************* CREATE and OPEN the TENNIS Base ******************************************************************* */ Create database tennis; USE tennis; #--Create table players and fill it-------------------------- Create table players ( playerno   int       not null    primary key, name       varchar(15)   not null, initials   varchar(3), birth_date   date, gender       char(1),...

  • The Primary Key of the table recording this information is SheepID+WeighingDate. The table has not been...

    The Primary Key of the table recording this information is SheepID+WeighingDate. The table has not been normalized beyond First Normal Form. That is, there are no ‘repeating groups’, but there may be Partial and Transitive Dependencies. SheepID Owner Birthdate WeighingDate Vet Weight VetPhoneNum K3922 McNab013 2013-05-12 2013-08-14 M330 22 7633088852 K3922 McNab013 2013-05-12 2014-06-02 S929 34 7609865463 K3922 McNab013 2013-05-12 2015-08-02 M330 43 7633088852 K3922 McNab013 2013-05-12 2016-07-30 P301 53 7682907965 K3922 McNab013 2013-05-12 2017-08-12 P301 52 7682907965 K3922 McNab013...

  • MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects...

    MICROSOFT SQL SERVER - Create the following views 1.List the employee assigned to the most projects 2.List the project with the most hours SCRIPT TO BUILD DATABASE create table department ( dept_ID int not null , dept_name char(50) NOT NULL, manager_ID int not null, manager_start_date date not null, constraint Dept_PK primary key (dept_ID), constraint D_Name_AK unique (dept_name) ); insert into department values(1,'abc',1,'2019-01-08'); insert into department values(2,'abc2',2,'2019-01-08'); insert into department values(3,'abc3',2,'2019-01-08'); insert into department values(4,'abc4',2,'2019-01-08'); /*project table done*/ create table project...

  • Need help writing a program that meets pseudocode and criteria . Txt File below input.txt file...

    Need help writing a program that meets pseudocode and criteria . Txt File below input.txt file data 05 11/30/16 03 12/07/16 05 12/07/16 05 12/08/16 01 12/10/16 07 12/11/16 07 12/14/16 06 12/15/16 02 12/21/16 05 12/21/16 06 12/22/16 07 12/22/16 08 12/23/16 07 12/23/16 07 12/23/16 07 12/23/16 08 12/24/16 08 12/24/16 07 12/24/16 03 12/26/16 05 12/26/16 07 12/28/16 04 12/29/16 07 01/01/17 06 01/03/17 07 01/03/17 08 01/05/17 05 01/10/17 04 01/17/17 08 01/17/17 07 01/18/17 07...

  • How do i find the image height and width in SOF marker Segment( the Xthumbnail and...

    How do i find the image height and width in SOF marker Segment( the Xthumbnail and Ythumbnail are all 0)? Which is the resolution of the image ff d8 ff e0 00 10 4a 46 49 46 00 01 01 01 00 c8 00 c8 00 00 ff db 00 43 00 02 02 02 02 02 01 02 02 02 02 02 02 02 03 03 06 04 03 03 03 03 07 05 05 04 06 08 07...

  • I want write a MySQL query to get the number of customers who have made at...

    I want write a MySQL query to get the number of customers who have made at least one purchase in each group (gender, education), I know how to do with two seperate query. but is that possible to get one query with the column like: gender | counts | education | counts The first 5 rows of each table looks like this: customers customer_id | first_name | last_name | state | birthdate | education | gender | date_account_opened 50 |...

  • Need help with C++ assignment Assignment 1 and .txt files are provided at the bottom. PART...

    Need help with C++ assignment Assignment 1 and .txt files are provided at the bottom. PART A PART B Assignment 1 #include <iostream> #include <string> #include <fstream> #include <iomanip> #include <stdio.h> #include <ctype.h> #include <string.h> #include <algorithm> using namespace std; /** This structure is to store the date and it has three integer fields **/ struct Date{    int day;    int month;    int year; }; /** This structure is to store the size of the box and it...

  • In a digital communication system, probability density function of the two level signal received in the...

    In a digital communication system, probability density function of the two level signal received in the receiver is: PR(v) = PS(v)*PN(v) = [0.4δ(v+1) + 0.6δ(v-4)]*η(v). And , η(v) is the noise that added to the message sign as the additive Gaussian noise with a value of zero and an effective value of 3. (* symbol means convolution process, in the solution of this problem you can use the below Q function table.) ,   η(v) = A) Plot the probability density...

  • Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and...

    Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and phones of all swimmers currently in level (of id) 3. +-------+---------+---------------------------+ | FName | LName   | EMail                     | +-------+---------+---------------------------+ | Bobby | Khan    | theBKhan1 | | Clara | Johnson | ClaraJohnson_11 | +-------+---------+---------------------------+ 2 rows in set (0.00 sec) (2) Provide the names of swimmers who have signed up to participate in the event '100M Butterfly' of Meet id 1. +-------+---------+ | FName...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT