INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (1, 1, '100 East Ridgewood Ave.', '', 'Paramus', 'NJ', '07652', '201-653-4472', 0), (2, 1, '21 Rosewood Rd.', '', 'Woodcliff Lake', 'NJ', '07677', '201-653-4472', 0), (3, 2, '16285 Wendell St.', '', 'Omaha', 'NE', '68135', '402-896-2576', 0), (4, 3, '19270 NW Cornell Rd.', 'Beaverton', 'OR', '97006', '503-654-1291', 0), (5, 4, '186 Vermont St.', 'Apt. 2', 'San Francisco', 'CA', '94110', '415-292-6651', 0), (6, 4, '1374 46th Ave.', '', 'San Francisco', 'CA', '94129', '415-292-6651', 0), (7, 5, '6982 Palm Ave.', '', 'Fresno', 'CA', '93711', '559-431-2398', 0), (8, 6, 23 Mountain View St.', 'Denver', 'CO', '80208', '303-912-3852', 0), (9, 7, '7361 N. 41st St.', 'Apt. B', 'New York', 'NY', '10012', '212-335-2093', 0), (10, 7, 13829 Broadway Ave.', 'Suite 2', 'New York', 'NY', '10012', '212-239-1208', 0), (11, 8, 2381 Buena Vista St.', '', 'Los Angeles', 'CA', '90023', '213-772-5033', 0), (12, 8, '291 W. Hollywood Blvd.', '', 'Los Angeles', 'CA', '90024', '213-391-2938', 0); INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id) VALUES (1, 1, '2018-03-28 09:40:28', '5.00', '32.32', '2018-03-30 15:32:51', 1, 'Visa', '4111111111111111', '04/2020', 2), (2, 2, '2018-03-28 11:23:20', '5.00', '0.00', '2018-03-29 12:52:14', 3, 'Visa', '4012888888881881', '08/2019', 3), (3, 1, '2018-03-29 09:44:58', '10.00', '89.92', '2018-03-31 9:11:41', 1, 'Visa', '4111111111111111', '04/2017', 2), (4, 3, '2018-03-30 15:22:31', '5.00', '0.00', '2018-04-03 16:32:21', 4, 'American Express', '378282246310005', '04/2016', 4), (5, 4, '2018-03-31 05:43:11', '5.00', '0.00', '2018-04-02 14:21:12', 5, 'Visa', '4111111111111111', '04/2019', 6), (6, 5, '2018-03-31 18:37:22', '5.00', '0.00', NULL, 7, 'Discover', '6011111111111117', '04/2019', 7), (7, 6, '2018-04-01 23:11:12', '15.00', '0.00', '2018-04-03 10:21:35', 8, 'MasterCard', '5555555555554444', '04/2019', 8), (8, 7, '2018-04-02 11:26:38', '5.00', '0.00', NULL, 9, 'Visa', '4012888888881881', '04/2019', 10), (9, 4, '2018-04-03 12:22:31', '5.00', '0.00', NULL, 5, 'Visa', '4111111111111111', '04/2019', 6); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (1, 1, 2, '1199.00', '359.70', 1), (2, 2, 4, 489.99', '186.20', 1), (3, 3, 3, 2517.00', '1308.84', 1), (4, 3, 6, 415.00', '161.85', 1), (5, 4, 2, '1199.00', '359.70', 2), (6, 5, 5, '299.00', '0.00', 1), (7, 6, 5, '299.00', '0.00', 1), (8, 7, 1, '699.00', '209.70', 1), (9, 7, 7, '799.99', '240.00', 1), (10, 7, 9, '699.99', '210.00', 1), (11, 8, 10, '799.99', '120.00', 1), (12, 9, 1, '699.00', '209.70', 1);
INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES (1, 1, '100 East Ridgewood Ave.', '', 'Paramus', 'NJ', '07652', '201-653-4472', 0), (2, 1, '21 Rosewood Rd.', '', 'Woodcliff Lake', 'NJ', '07677', '201-653-4472', 0), (3, 2, '16285 Wendell St.', '', 'Omaha', 'NE', '68135', '402-896-2576', 0), (4, 3, '19270 NW Cornell Rd.', 'Beaverton', 'OR', '97006', '503-654-1291', 0), (5, 4, '186 Vermont St.', 'Apt. 2', 'San Francisco', 'CA', '94110', '415-292-6651', 0), (6, 4, '1374 46th Ave.', '', 'San Francisco', 'CA', '94129', '415-292-6651', 0), (7, 5, '6982 Palm Ave.', '', 'Fresno', 'CA', '93711', '559-431-2398', 0), (8, 6, 23 Mountain View St.', 'Denver', 'CO', '80208', '303-912-3852', 0), (9, 7, '7361 N. 41st St.', 'Apt. B', 'New York', 'NY', '10012', '212-335-2093', 0), (10, 7, 13829 Broadway Ave.', 'Suite 2', 'New York', 'NY', '10012', '212-239-1208', 0), (11, 8, 2381 Buena Vista St.', '', 'Los Angeles', 'CA', '90023', '213-772-5033', 0), (12, 8, '291 W. Hollywood Blvd.', '', 'Los Angeles', 'CA', '90024', '213-391-2938', 0); INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id) VALUES (1, 1, '2018-03-28 09:40:28', '5.00', '32.32', '2018-03-30 15:32:51', 1, 'Visa', '4111111111111111', '04/2020', 2), (2, 2, '2018-03-28 11:23:20', '5.00', '0.00', '2018-03-29 12:52:14', 3, 'Visa', '4012888888881881', '08/2019', 3), (3, 1, '2018-03-29 09:44:58', '10.00', '89.92', '2018-03-31 9:11:41', 1, 'Visa', '4111111111111111', '04/2017', 2), (4, 3, '2018-03-30 15:22:31', '5.00', '0.00', '2018-04-03 16:32:21', 4, 'American Express', '378282246310005', '04/2016', 4), (5, 4, '2018-03-31 05:43:11', '5.00', '0.00', '2018-04-02 14:21:12', 5, 'Visa', '4111111111111111', '04/2019', 6), (6, 5, '2018-03-31 18:37:22', '5.00', '0.00', NULL, 7, 'Discover', '6011111111111117', '04/2019', 7), (7, 6, '2018-04-01 23:11:12', '15.00', '0.00', '2018-04-03 10:21:35', 8, 'MasterCard', '5555555555554444', '04/2019', 8), (8, 7, '2018-04-02 11:26:38', '5.00', '0.00', NULL, 9, 'Visa', '4012888888881881', '04/2019', 10), (9, 4, '2018-04-03 12:22:31', '5.00', '0.00', NULL, 5, 'Visa', '4111111111111111', '04/2019', 6); INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES (1, 1, 2, '1199.00', '359.70', 1), (2, 2, 4, 489.99', '186.20', 1), (3, 3, 3, 2517.00', '1308.84', 1), (4, 3, 6, 415.00', '161.85', 1), (5, 4, 2, '1199.00', '359.70', 2), (6, 5, 5, '299.00', '0.00', 1), (7, 6, 5, '299.00', '0.00', 1), (8, 7, 1, '699.00', '209.70', 1), (9, 7, 7, '799.99', '240.00', 1), (10, 7, 9, '699.99', '210.00', 1), (11, 8, 10, '799.99', '120.00', 1), (12, 9, 1, '699.00', '209.70', 1);
Chapter1: Getting Started With Excel
Section: Chapter Questions
Problem 4.4CP
Related questions
Question
Based on the data base provided, construct a few different SELECT statements that use the group by and having clauses? Furthermore, can the querys that you created by rewritten without those clauses?
Please and thank you
Transcribed Image Text:INSERT INTO addresses (address_id, customer_id, linel, line2, city, state, zip_code, phone, disabled) VALUES
(1, 1, '100 East Ridgewood Ave.', '', 'Paramus', 'NJ', '07652', '201-653-4472', 0),
(2, 1, '21 Rosewood Rd.', '', 'Woodcliff Lake', 'NJ', '07677', '201-653-4472', 0),
(3, 2, '16285 Wendell St.', '', 'Omaha', 'NE', '68135', '402-896-2576', 0),
(4, 3, '19270 NW Cornell Rd.',
'Beaverton', 'OR', '97006', '503-654-1291', 0),
(5, 4, '186 Vermont St.', 'Apt. 2', 'San Francisco', 'CA', '94110', '415-292-6651', 0),
(6, 4, '1374 46th Ave.', '', 'San Francisco', 'CA', '94129', '415-292-6651', 0),
(7, 5, '6982 Palm Ave.', '', 'Fresno', 'CA', '93711', '559-431-2398', 0),
(8, 6, 23 Mountain View St.',
'Denver', 'CO', '80208', '303-912-3852', 0),
(9, 7, '7361 N. 41st St.', 'Apt. B', 'New York', 'NY', '10012', '212-335-2093', 0),
(10, 7, 13829 Broadway Ave.', 'Suite 2', 'New York', 'NY', '10012', '212-239-1208', 0),
(11, 8, 2381 Buena Vista St.', '', 'Los Angeles', 'CA', '90023', '213-772-5033', 0),
(12, 8, '291 W. Hollywood Blvd.', '', 'Los Angeles', 'CA', '90024', '213-391-2938', 0);
INSERT INTO orders (order_id, customer_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id) VALUES
(1, 1, '2018-03-28 09:40:28', '5.00', '32.32', '2018-03-30 15:32:51', 1, 'Visa', '4111111111111111', '04/2020', 2),
(2, 2, '2018-03-28 11:23:20', '5.00', '0.00', '2018-03-29 12:52:14', 3, 'Visa', '4012888888881881', '08/2019', 3),
(3, 1, '2018-03-29 09:44:58', '10.00', '89.92', '2018-03-31 9:11:41', 1, 'Visa', '4111111111111111', '04/2017', 2),
(4, 3, '2018-03-30 15:22:31', '5.00', '0.00', '2018-04-03 16:32:21', 4, 'American Express', '378282246310005', '04/2016', 4),
(5, 4, '2018-03-31 05:43:11', '5.00', '0.00', '2018-04-02 14:21:12', 5, 'Visa', '4111111111111111', '04/2019', 6),
(6, 5, '2018-03-31 18:37:22', '5.00', '0.00', NULL, 7, 'Discover', '6011111111111117', '04/2019', 7),
(7, 6, '2018-04-01 23:11:12', '15.00', '0.00', '2018-04-03 10:21:35', 8, 'MasterCard', '5555555555554444', '04/2019', 8),
(8, 7, '2018-04-02 11:26:38', '5.00', '0.00', NULL, 9, 'Visa', '4012888888881881', '04/2019', 10),
(9, 4, '2018-04-03 12:22:31', '5.00', '0.00', NULL, 5, 'Visa', '4111111111111111', '04/2019', 6);
INSERT INTO order_items (item_id, order_id, product_id, item_price, discount_amount, quantity) VALUES
(1, 1, 2, '1199.00', '359.70', 1),
(2, 2, 4, 489.99', '186.20', 1),
(3, 3, 3, 2517.00', '1308.84', 1),
(4, 3, 6, 415.00', '161.85', 1),
(5, 4, 2, '1199.00', '359.70', 2),
(6, 5, 5, '299.00', '0.00', 1),
(7, 6, 5, '299.00', '0.00', 1),
(8, 7, 1, '699.00', '209.70', 1),
(9, 7, 7, '799.99', '240.00', 1),
(10, 7, 9, '699.99', '210.00', 1),
(11, 8, 10, '799.99', '120.00', 1),
(12, 9, 1, '699.00', '209.70', 1);
AI-Generated Solution
AI-generated content may present inaccurate or offensive content that does not represent bartleby’s views.
Unlock instant AI solutions
Tap the button
to generate a solution
Recommended textbooks for you
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:
9781337508841
Author:
Carey
Publisher:
Cengage
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:
9780357392676
Author:
FREUND, Steven
Publisher:
CENGAGE L