Write an SQL statement using a subquery to list LastName, FirstName, and full mailing address of customers who have purchased an item that has an ItemPrice of $500 or more.
Write an SQL statement using a subquery to list LastName, FirstName, and full mailing address of customers who have purchased an item that has an ItemPrice of $500 or more.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
CUSTOMER
Columns = 7, Rows = 10
| CustomerID | LastName | FirstName | Address | ZIP | Phone | |
|---|---|---|---|---|---|---|
| 1 | Shire | Robert | 6225 Evanston Ave N | 98103 | 206-524-2433 | Robert.Shire@somewhere.com |
| 2 | Goodyear | Katherine | 7335 11th Ave NE | 98105 | 206-524-3544 | Katherine.Goodyear@somewhere.com |
| 3 | Bancroft | Chris | 12605 NE 6th Street | 98005 | 425-635-9788 | Chris.Bancroft@somewhere.com |
| 4 | Griffith | John | 335 Aloha Street | 98109 | 206-524-4655 | John.Griffith@somewhere.com |
| 5 | Tierney | Doris | 14510 NE 4th Street | 98005 | 425-635-8677 | Doris.Tierney@somewhere.com |
| 6 | Anderson | Donna | 1410 Hillcrest Parkway | 98273 | 360-538-7566 | Donna.Anderson@elsewhere.com |
| 7 | Svane | Jack | 3211 42nd Street | 98115 | 206-524-5766 | Jack.Svane@somewhere.com |
| 8 | Walsh | Denesha | 6712 24th Avenue NE | 98053 | 425-635-7566 | Denesha.Walsh@somewhere.com |
| 9 | Enquist | Craig | 534 15th Street | 98225 | 360-538-6455 | Craig.Enquist@elsewhere.com |
| 10 | Anderson | Rose | 6823 17th Ave NE | 98105 | 206-524-6877 | Rose.Anderson@elsewhere.com |
EMPLOYEE
Columns = 5, Rows = 5
| EmployeeID | LastName | FirstName | Phone | |
|---|---|---|---|---|
| 1 | Stuart | Anne | 206-527-0010 | Anne.Stuart@QACS.com |
| 2 | Stuart | George | 206-527-0011 | George.Stuart@QACS.com |
| 3 | Stuart | Mary | 206-527-0012 | Mary.Stuart@QACS.com |
| 4 | Orange | William | 206-527-0013 | William.Orange@QACS.com |
| 5 | Griffith | John | 206-527-0014 | John.Griffith@QACS.com |
ITEM
Columns = 6, Rows = 25
| ItemID | ItemDescription | PurchaseDate | ItemCost | ItemPrice | VendorID |
|---|---|---|---|---|---|
| 1 | Antique Desk | 2013-11-07 00:00:00 | 1800.00 | 3000.00 | 2 |
| 2 | Antique Desk Chair | 2013-11-10 00:00:00 | 300.00 | 500.00 | 4 |
| 3 | Dining Table Linens | 2013-11-14 00:00:00 | 600.00 | 1000.00 | 1 |
| 4 | Candles | 2013-11-14 00:00:00 | 30.00 | 50.00 | 1 |
| 5 | Candles | 2013-11-14 00:00:00 | 27.00 | 45.00 | 1 |
| 6 | Desk Lamp | 2013-11-14 00:00:00 | 150.00 | 250.00 | 3 |
| 7 | Dining Table Linens | 2013-11-14 00:00:00 | 450.00 | 750.00 | 1 |
| 8 | Book Shelf | 2013-11-21 00:00:00 | 150.00 | 250.00 | 5 |
| 9 | Antique Chair | 2013-11-21 00:00:00 | 750.00 | 1250.00 | 6 |
| 10 | Antique Chair | 2013-11-21 00:00:00 | 1050.00 | 1750.00 | 6 |
| 11 | Antique Candle Holders | 2013-11-28 00:00:00 | 210.00 | 350.00 | 2 |
| 12 | Antique Desk | 2014-01-05 00:00:00 | 1920.00 | 3200.00 | 2 |
| 13 | Antique Desk | 2014-01-05 00:00:00 | 2100.00 | 3500.00 | 2 |
| 14 | Antique Desk Chair | 2014-01-06 00:00:00 | 285.00 | 475.00 | 9 |
| 15 | Antique Desk Chair | 2014-01-06 00:00:00 | 339.00 | 565.00 | 9 |
| 16 | Desk Lamp | 2014-01-06 00:00:00 | 150.00 | 250.00 | 10 |
| 17 | Desk Lamp | 2014-01-06 00:00:00 | 150.00 | 250.00 | 10 |
| 18 | Desk Lamp | 2014-01-06 00:00:00 | 144.00 | 240.00 | 3 |
| 19 | Antique Dining Table | 2014-01-10 00:00:00 | 3000.00 | 5000.00 | 7 |
| 20 | Antique Sideboard | 2014-01-11 00:00:00 | 2700.00 | 4500.00 | 8 |
| 21 | Dining Table Chairs | 2014-01-11 00:00:00 | 5100.00 | 8500.00 | 9 |
| 22 | Dining Table Linens | 2014-01-12 00:00:00 | 450.00 | 750.00 | 1 |
| 23 | Dining Table Linens | 2014-01-12 00:00:00 | 480.00 | 800.00 | 1 |
| 24 | Candles | 2014-01-17 00:00:00 | 30.00 | 50.00 | 1 |
| 25 | Candles | 2014-01-17 00:00:00 | 36.00 | 60.00 | 1 |
SALE
Columns = 7, Rows = 15
| SaleID | CustomerID | EmployeeID | SaleDate | SubTotal | Tax | Total |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 2013-12-14 00:00:00 | 3500.00 | 290.50 | 3790.50 |
| 2 | 2 | 1 | 2013-12-15 00:00:00 | 1000.00 | 83.00 | 1083.00 |
| 3 | 3 | 1 | 2013-12-15 00:00:00 | 50.00 | 4.15 | 54.15 |
| 4 | 4 | 3 | 2013-12-23 00:00:00 | 45.00 | 3.74 | 48.74 |
| 5 | 1 | 5 | 2014-01-05 00:00:00 | 250.00 | 20.75 | 270.75 |
| 6 | 5 | 5 | 2014-01-10 00:00:00 | 750.00 | 62.25 | 812.25 |
| 7 | 6 | 4 | 2014-01-12 00:00:00 | 250.00 | 20.75 | 270.75 |
| 8 | 2 | 1 | 2014-01-15 00:00:00 | 3000.00 | 249.00 | 3249.00 |
| 9 | 5 | 5 | 2014-01-25 00:00:00 | 350.00 | 29.05 | 379.05 |
| 10 | 7 | 1 | 2014-02-04 00:00:00 | 14250.00 | 1182.75 | 15432.75 |
| 11 | 8 | 5 | 2014-02-04 00:00:00 | 250.00 | 20.75 | 270.75 |
| 12 | 5 | 4 | 2014-02-07 00:00:00 | 50.00 | 4.15 | 54.15 |
| 13 | 9 | 2 | 2014-02-07 00:00:00 | 4500.00 | 373.50 | 4873.50 |
| 14 | 10 | 3 | 2014-02-11 00:00:00 | 3675.00 | 305.03 | 3980.03 |
| 15 | 2 | 2 | 2014-02-11 00:00:00 | 800.00 | 66.40 | 866.40 |
SALE_ITEM
Columns = 4, Rows = 20
| SaleID | SaleItemID | ItemID | ItemPrice |
|---|---|---|---|
| 1 | 1 | 1 | 3000.00 |
| 1 | 2 | 2 | 500.00 |
| 2 | 1 | 3 | 1000.00 |
| 3 | 1 | 4 | 50.00 |
| 4 | 1 | 5 | 45.00 |
| 5 | 1 | 6 | 250.00 |
| 6 | 1 | 7 | 750.00 |
| 7 | 1 | 8 | 250.00 |
| 8 | 1 | 9 | 1250.00 |
| 8 | 2 | 10 | 1750.00 |
| 9 | 1 | 11 | 350.00 |
| 10 | 1 | 19 | 5000.00 |
| 10 | 2 | 21 | 8500.00 |
| 10 | 3 | 22 | 750.00 |
| 11 | 1 | 17 | 250.00 |
| 12 | 1 | 24 | 50.00 |
| 13 | 1 | 20 | 4500.00 |
| 14 | 1 | 12 | 3200.00 |
| 14 | 2 | 14 | 475.00 |
| 15 | 1 | 23 | 800.00 |
VENDOR
Columns = 9, Rows = 10
| VendorID | CompanyName | ContactLastName | ContactFirstName | Address | ZIP | Phone | Fax | |
|---|---|---|---|---|---|---|---|---|
| 1 | Linens and Things | Huntington | Anne | 1515 NW Market Street | 98107 | 206-325-6755 | 206-329-9675 | LAT@business.com |
| 2 | European Specialties | Tadema | Ken | 6123 15th Avenue NW | 98107 | 206-325-7866 | 206-329-9786 | ES@business.com |
| 3 | Lamps and Lighting | Swanson | Sally | 506 Prospect Street | 98109 | 206-325-8977 | 206-329-9897 | LAL@business.com |
| 4 | Lee | Andrew | 1102 3rd Street | 98033 | 425-746-5433 | Andrew.Lee@somewhere.com | ||
| 5 | Harrison | Denise | 533 10th Avenue | 98033 | 425-746-4322 | Denise.Harrison@somewhere.com | ||
| 6 | New York Brokerage | Smith | Mark | 621 Roy Street | 98109 | 206-325-9088 | 206-329-9908 | NYB@business.com |
| 7 | Walsh | Denesha | 6712 24th Avenue NE | 98053 | 425-635-7566 | Denesha.Walsh@somewhere.com | ||
| 8 | Bancroft | Chris | 12605 NE 6th Street | 98005 | 425-635-9788 | 425-639-9978 | Chris.Bancroft@somewhere.com | |
| 9 | Specialty Antiques | Nelson | Fred | 2512 Lucky Street | 94110 | 415-422-2121 | 415-429-9212 | SA@business.com |
| 10 | General Antiques | Garner | Patty | 2515 Lucky Street | 94110 | 415-422-3232 | 415-429-9323 | GA@business.com |
ZIPCODE
Columns = 3, Rows = 11
| ZIP | City | State |
|---|---|---|
| 94110 | San Francisco | CA |
| 98005 | Bellevue | WA |
| 98033 | Kirkland | WA |
| 98053 | Redmond | WA |
| 98103 | Seattle | WA |
| 98105 | Seattle | WA |
| 98107 | Seattle | WA |
| 98109 | Seattle | WA |
| 98115 | Seattle | WA |
| 98225 | Bellingham | WA |
| 98273 | Mt. Vernon | WA |
| 5. | Write an SQL statement using a subquery to list LastName, FirstName, and full mailing address of customers who have purchased an item that has an ItemPrice of $500 or more. |
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education