Blog

SQL Prep – Daily Challenge #102

Calculate the number of products each supplier is responsible for. Output should include supplier ID, supplier company name and product count. Sort by product count (descending) and company name (alphabetic order).

Come back once you have submitted a query.

Please submit a query to see results here.

supplierIDcompanyNameproductcount
7Pavlova, Ltd.5
12Plutzer Lebensmittelgroßmärkte AG5
2New Orleans Cajun Delights4
8Specialty Biscuits, Ltd.4
16Bigfoot Breweries3
1Exotic Liquids3
14Formaggi Fortini s.r.l.3
24G'day, Mate3
3Grandma Kelly's Homestead3
11Heli Süßwaren GmbH & Co. KG3
23Karkki Oy3
20Leka Trading3
6Mayumi's3
15Norske Meierier3
17Svensk Sjöföda AB3
4Tokyo Traders3
18Aux joyeux ecclésiastiques2
5Cooperativa de Quesos 'Las Cabras'2
29Forêts d'érables2
28Gai pâturage2
21Lyngbysild2
25Ma Maison2
19New England Seafood Cannery2
26Pasta Buttini s.r.l.2
9PB Knäckebröd AB2
22Zaanse Snoepfabriek2
27Escargots Nouveaux1
13Nord-Ost-Fisch Handelsgesellschaft mbH1
10Refrescos Americanas LTDA1

SQL Prep – Daily Challenge #101

Find the customer name who has the highest credit limit. If there is more than one, sort customer names in alphabetical order.

Come back once you have submitted a query.

Please submit a query to see results here.

customernamecreditlimit
Euro+ Shopping Channel227600.00

SQL Prep – Daily Challenge #100

Display the product line that has the highest order revenue. Output should include and be labeled as "productline", "textdescription" and "revenue".

Come back once you have submitted a query.

Please submit a query to see results here.

productlinetextdescriptionrevenue
Classic CarsAttention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office.3853922.49

SQL Prep – Daily Challenge #99

Display product line, product line text description and revenue for each product line. Sort by revenue in descending order.

Come back once you have submitted a query.

Please submit a query to see results here.

productlinetextdescriptionrevenue
Classic CarsAttention car enthusiasts: Make your wildest car ownership dreams come true. Whether you are looking for classic muscle cars, dream sports cars or movie-inspired miniatures, you will find great choices in this category. These replicas feature superb attention to detail and craftsmanship and offer features such as working steering system, opening forward compartment, opening rear trunk with removable spare wheel, 4-wheel independent spring suspension, and so on. The models range in size from 1:10 to 1:24 scale and include numerous limited edition and several out-of-production vehicles. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office.3853922.49
Vintage CarsOur Vintage Car models realistically portray automobiles produced from the early 1900s through the 1940s. Materials used include Bakelite, diecast, plastic and wood. Most of the replicas are in the 1:18 and 1:24 scale sizes, which provide the optimum in detail and accuracy. Prices range from $30.00 up to $180.00 for some special limited edition replicas. All models include a certificate of authenticity from their manufacturers and come fully assembled and ready for display in the home or office.1797559.63
MotorcyclesOur motorcycles are state of the art replicas of classic as well as contemporary motorcycle legends such as Harley Davidson, Ducati and Vespa. Models contain stunning details such as official logos, rotating wheels, working kickstand, front suspension, gear-shift lever, footbrake lever, and drive chain. Materials used include diecast and plastic. The models range in size from 1:10 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. All models come fully assembled and ready for display in the home or office. Most include a certificate of authenticity.1121426.12
Trucks and BusesThe Truck and Bus models are realistic replicas of buses and specialized trucks produced from the early 1920s to present. The models range in size from 1:12 to 1:50 scale and include numerous limited edition and several out-of-production vehicles. Materials used include tin, diecast and plastic. All models include a certificate of authenticity from their manufacturers and are a perfect ornament for the home and office.1024113.57
PlanesUnique, diecast airplane and helicopter replicas suitable for collections, as well as home, office or classroom decorations. Models contain stunning details such as official logos and insignias, rotating jet engines and propellers, retractable wheels, and so on. Most come fully assembled and with a certificate of authenticity from their manufacturers.954637.54
ShipsThe perfect holiday or anniversary gift for executives, clients, friends, and family. These handcrafted model ships are unique, stunning works of art that will be treasured for generations! They come fully assembled and ready for display in the home or office. We guarantee the highest quality, and best value.663998.34
TrainsModel trains are a rewarding hobby for enthusiasts of all ages. Whether you're looking for collectible wooden trains, electric streetcars or locomotives, you'll find a number of great choices for any budget within this category. The interactive aspect of trains makes toy trains perfect for young children. The wooden train sets are ideal for children under the age of 5.188532.92

SQL Prep – Daily Challenge #98

Select the country codes where the country language is English. Sort country code in alphabetical order.

Come back once you have submitted a query.

Please submit a query to see results here.

countrycode
ABW
AIA
ANT
ASM
ATG
AUS
BHR
BLZ
BMU
BRB
BRN
CAN
CCK
COK
CXR
CYM
DNK
FLK
GBR
GIB
GUM
HKG
IRL
ISL
JPN
KNA
KWT
LCA
LSO
MAC
MCO
MDV
MHL
MLT
MNP
MSR
MYS
NFK
NIU
NOR
NRU
NZL
PLW
PRI
SHN
SYC
TCA
TKL
TON
TTO
TUV
UMI
USA
VCT
VGB
VIR
VUT
WSM
ZAF
ZWE

SQL Prep – Daily Challenge #97

select all the customer names and their total payments whose total payment are more than 100,000. Name total payments as "totalamount". Sort by total amount (descending order) and customer name.

Come back once you have submitted a query.

Please submit a query to see results here.

customernametotalamount
Euro+ Shopping Channel715738.98
Mini Gifts Distributors Ltd.584188.24
Australian Collectors, Co.180585.07
Muscle Machine Inc177913.95
Dragon Souveniers, Ltd.156251.03
Down Under Souveniers, Inc154622.08
AV Stores, Co.148410.09
Anna's Decorations, Ltd137034.22
Corporate Gift Ideas Co.132340.78
Saveley & Henriot, Co.130305.35
Rovelli Gifts127529.69
Reims Collectables126983.19
La Rochelle Gifts116949.68
Online Diecast Creations Co.116449.29
Kelly's Gift Shop114497.19
Corrida Auto Replicas, Ltd112440.09
Vida Sport, Ltd108777.92
Land of Toys Inc.107639.94
Danish Wholesale Imports107446.50
Tokyo Collectables, Ltd105548.73
Handji Gifts& Co105420.57
Technics Stores Inc.104545.22
Baane Mini Imports104224.79
Suominen Souveniers103896.74
Mini Creations Ltd.101872.52

SQL Prep – Daily Challenge #96

Find all the managers who were hired after their subordinates. Output should be named as "managerfirstname", "managerlastname", "managerhiredate", "employeefirstname", "employeelastname", "employeehiredate".

Come back once you have submitted a query.

Please submit a query to see results here.

managerfirstnamemanagerlastnamemanagerhiredateemployeefirstnameemployeelastnameemployeehiredate
AndrewFuller1992-08-14 00:00:00.000NancyDavolio1992-05-01 00:00:00.000
AndrewFuller1992-08-14 00:00:00.000JanetLeverling1992-04-01 00:00:00.000

SQL Prep – Daily Challenge #95

Select all the cities whose population is greater than 1 million. Sort the city names in alphabetical order.

Come back once you have submitted a query.

Please submit a query to see results here.

name
Abidjan
Accra
Adana
Addis Abeba
Ahmedabad
Aleppo
Alexandria
Alger
Almaty
Ankara
Anshan
Baghdad
Baku
Bandung
Bangalore
Bangkok
Barcelona
Barranquilla
Beirut
Belém
Belo Horizonte
Beograd
Berlin
Bhopal
Birmingham
Brasília
Brisbane
Bucuresti
Budapest
Buenos Aires
Bursa
Cairo
Calcutta [Kolkata]
Cali
Cape Town
Caracas
Casablanca
Changchun
Changsha
Chengdu
Chennai (Madras)
Chicago
Chittagong
Chongqing
Ciudad de México
Conakry
Córdoba
Curitiba
Dalian
Dallas
Damascus
Dar es Salaam
Davao
Delhi
Dhaka
Dnipropetrovsk
Donetsk
Douala
Ecatepec de Morelos
Esfahan
Faisalabad
Fortaleza
Fukuoka
Fushun
Fuzhou
Giza
Goiânia
Guadalajara
Guarulhos
Guayaquil
Guiyang
Gujranwala
Hamburg
Hangzhou
Hanoi
Harare
Harbin
Harkova [Harkiv]
Hefei
Hiroshima
Ho Chi Minh City
Houston
Hyderabad
Hyderabad
Ibadan
Inchon
Indore
Istanbul
Izmir
Jaipur
Jakarta
Jedda
Jekaterinburg
Jilin
Jinan
Jokohama [Yokohama]
Juárez
Kabul
Kalookan
Kalyan
Kanpur
Kanton [Guangzhou]
Kaohsiung
Karachi
Kawasaki
Kazan
Kinshasa
Kioto
Kitakyushu
Kobe
Kowloon and New Kowloon
Kuala Lumpur
Kunming
Kwangju
Kyiv
La Habana
La Matanza
Lagos
Lahore
Lanzhou
León
Lima
London
Los Angeles
Luanda
Lucknow
Ludhiana
Lusaka
Madrid
Manaus
Manila
Maputo
Maracaíbo
Mashhad
Medan
Medellín
Melbourne
Milano
Minsk
Monterrey
Montevideo
Montréal
Moscow
Multan
Mumbai (Bombay)
Munich [München]
Nagoya
Nagpur
Nairobi
Nanchang
Nanking [Nanjing]
Nanning
Napoli
New York
Nezahualcóyotl
Ningbo
Nizni Novgorod
Novosibirsk
Odesa
Omdurman
Omsk
Osaka
Palembang
Paris
Peking
Perm
Perth
Philadelphia
Phoenix
Porto Alegre
Praha
Puebla
Pune
Pusan
Pyongyang
Qingdao
Qiqihar
Quezon
Quito
Rangoon (Yangon)
Rawalpindi
Recife
Rio de Janeiro
Riyadh
Roma
Rostov-na-Donu
Salvador
Samara
San Antonio
San Diego
Santafé de Bogotá
Santiago de Chile
Santo Domingo de Guzmán
São Paulo
Sapporo
Semarang
Seoul
Shanghai
Shenyang
Shijiazhuang
Shiraz
Singapore
Sofija
St Petersburg
Surabaya
Surat
Sydney
TÅ¡eljabinsk
Tabriz
Taegu
Taejon
Taipei
Taiyuan
Tangerang
Tangshan
Tbilisi
Teheran
Tianjin
Tijuana
Tokyo
Toskent
Tripoli
Ufa
Ujung Pandang
Ulsan
Urumtši [Ürümqi]
Vadodara (Baroda)
Victoria
Warszawa
Wien
Wuhan
Xi´an
Yaoundé
Yerevan
Zapopan
Zhengzhou
Zibo

SQL Prep – Daily Challenge #94

Calculate the number of employees in the largest office (office that has the most employees). Name the output column as "maxemployeecount".

Come back once you have submitted a query.

Please submit a query to see results here.

maxemployeecount
6

SQL Prep – Daily Challenge #93

Find the number of employees per office. Output should include office code, office city and number of employees.

Come back once you have submitted a query.

Please submit a query to see results here.

officecodecity
1San Francisco6
2Boston2
3NYC2
4Paris5
5Tokyo2
6Sydney4
7London2