CST1600 Relational Databases
Instructional Guide

Homework Set 1 Results

#1 List all countries that customers are from. Eliminate all duplicates so that a particular country is listed only once.

country
Australia
Austria
Belgium
Canada
Denmark
Finland
France
Germany
Hong Kong
Ireland
Israel
Italy
Japan
Netherlands
New Zealand
Norway
Philippines
Poland
Portugal
Russia
Singapore
South Africa
Spain
Sweden
Switzerland
UK
USA

#2 List the customer names of the top 10 customers that have ordered the most in dollar volume.

customerName Total
Euro+ Shopping Channel 820,689.54
Mini Gifts Distributors Ltd. 591,827.34
Australian Collectors, Co. 180,585.07
Muscle Machine Inc 177,913.95
La Rochelle Gifts 158,573.12
Dragon Souveniers, Ltd. 156,251.03
Down Under Souveniers, Inc 154,622.08
Land of Toys Inc. 149,085.15
AV Stores, Co. 148,410.09
The Sharp Gifts Warehouse 143,536.27

#3 List orders where their total amount were greater than $60,000. List the order number, customer number, status, and shipped date.

orderNumber customerNumber status shippedDate Order Total
10310 [->] 259 Shipped 2004-10-18 61,234.67
10287 [->] 298 Shipped 2004-09-01 61,402.00
10165 [->] 148 Shipped 2003-12-26 67,392.85

#4 List all products that have the word “Cars” in the product line. List the product code, product name, and product line.

productCode productName productLine
S10_1949 1952 Alpine Renault 1300 Classic Cars [->]
S10_4757 1972 Alfa Romeo GTA Classic Cars [->]
S10_4962 1962 LanciaA Delta 16V Classic Cars [->]
S12_1099 1968 Ford Mustang Classic Cars [->]
S12_1108 2001 Ferrari Enzo Classic Cars [->]
S12_3148 1969 Corvair Monza Classic Cars [->]
S12_3380 1968 Dodge Charger Classic Cars [->]
S12_3891 1969 Ford Falcon Classic Cars [->]
S12_3990 1970 Plymouth Hemi Cuda Classic Cars [->]
S12_4675 1969 Dodge Charger Classic Cars [->]
S18_1129 1993 Mazda RX-7 Classic Cars [->]
S18_1342 1937 Lincoln Berline Vintage Cars [->]
S18_1367 1936 Mercedes-Benz 500K Special Roadster Vintage Cars [->]
S18_1589 1965 Aston Martin DB5 Classic Cars [->]
S18_1749 1917 Grand Touring Sedan Vintage Cars [->]
S18_1889 1948 Porsche 356-A Roadster Classic Cars [->]
S18_1984 1995 Honda Civic Classic Cars [->]
S18_2238 1998 Chrysler Plymouth Prowler Classic Cars [->]
S18_2248 1911 Ford Town Car Vintage Cars [->]
S18_2325 1932 Model A Ford J-Coupe Vintage Cars [->]
S18_2795 1928 Mercedes-Benz SSK Vintage Cars [->]
S18_2870 1999 Indy 500 Monte Carlo SS Classic Cars [->]
S18_2949 1913 Ford Model T Speedster Vintage Cars [->]
S18_2957 1934 Ford V8 Coupe Vintage Cars [->]
S18_3136 18th Century Vintage Horse Carriage Vintage Cars [->]
S18_3140 1903 Ford Model A Vintage Cars [->]
S18_3232 1992 Ferrari 360 Spider red Classic Cars [->]
S18_3233 1985 Toyota Supra Classic Cars [->]
S18_3278 1969 Dodge Super Bee Classic Cars [->]
S18_3320 1917 Maxwell Touring Car Vintage Cars [->]
S18_3482 1976 Ford Gran Torino Classic Cars [->]
S18_3685 1948 Porsche Type 356 Roadster Classic Cars [->]
S18_3856 1941 Chevrolet Special Deluxe Cabriolet Vintage Cars [->]
S18_4027 1970 Triumph Spitfire Classic Cars [->]
S18_4409 1932 Alfa Romeo 8C2300 Spider Sport Vintage Cars [->]
S18_4522 1904 Buick Runabout Vintage Cars [->]
S18_4668 1939 Cadillac Limousine Vintage Cars [->]
S18_4721 1957 Corvette Convertible Classic Cars [->]
S18_4933 1957 Ford Thunderbird Classic Cars [->]
S24_1046 1970 Chevy Chevelle SS 454 Classic Cars [->]
S24_1444 1970 Dodge Coronet Classic Cars [->]
S24_1628 1966 Shelby Cobra 427 S/C Classic Cars [->]
S24_1937 1939 Chevrolet Deluxe Coupe Vintage Cars [->]
S24_2022 1938 Cadillac V-16 Presidential Limousine Vintage Cars [->]
S24_2766 1949 Jaguar XK 120 Classic Cars [->]
S24_2840 1958 Chevy Corvette Limited Edition Classic Cars [->]
S24_2887 1952 Citroen-15CV Classic Cars [->]
S24_2972 1982 Lamborghini Diablo Classic Cars [->]
S24_3151 1912 Ford Model T Delivery Wagon Vintage Cars [->]
S24_3191 1969 Chevrolet Camaro Z28 Classic Cars [->]
S24_3371 1971 Alpine Renault 1600s Classic Cars [->]
S24_3420 1937 Horch 930V Limousine Vintage Cars [->]
S24_3432 2002 Chevy Corvette Classic Cars [->]
S24_3816 1940 Ford Delivery Sedan Vintage Cars [->]
S24_3856 1956 Porsche 356A Coupe Classic Cars [->]
S24_3969 1936 Mercedes Benz 500k Roadster Vintage Cars [->]
S24_4048 1992 Porsche Cayenne Turbo Silver Classic Cars [->]
S24_4258 1936 Chrysler Airflow Vintage Cars [->]
S24_4620 1961 Chevrolet Impala Classic Cars [->]
S32_4289 1928 Ford Phaeton Deluxe Vintage Cars [->]
S50_1341 1930 Buick Marquette Phaeton Vintage Cars [->]
S700_2824 1982 Camaro Z28 Classic Cars [->]

#5 List the customer names that have purchased model trains. Customers should be listed only once.

customerName
Amica Models & Co.
Australian Gift Network, Co
AV Stores, Co.
Baane Mini Imports
Blauer See Auto, Co.
CAF Imports
Classic Legends Inc.
Clover Collections, Co.
Danish Wholesale Imports
Diecast Classics Inc.
Diecast Collectables
Dragon Souveniers, Ltd.
Euro+ Shopping Channel
Gift Depot Inc.
Gifts4AllAges.com
giftsbymail.co.uk
GiftsForHim.com
Heintze Collectables
Iberia Gift Imports, Corp.
Kelly's Gift Shop
L'ordine Souveniers
La Corne D'abondance, Co.
Land of Toys Inc.
Lyon Souveniers
Mini Classics
Mini Creations Ltd.
Mini Gifts Distributors Ltd.
Muscle Machine Inc
Petit Auto
Reims Collectables
Royale Belge
Saveley & Henriot, Co.
Suominen Souveniers
Super Scale Inc.
Tokyo Collectables, Ltd
Volvo Model Replicas, Co