Sales Orders Database
List customers and the dates they placed an order, sorted in order date sequence. (Hint: The solution requires a JOIN of two tables.)
+------------------+------------+ | CustomerFullName | OrderDate | +------------------+------------+ | Sam Abolrous | 2012-09-01 | | David Smith | 2012-09-01 | | Liz Keyser | 2012-09-01 | | Mark Rosales | 2012-09-01 | | Andrew Cencini | 2012-09-01 | | William Thompson | 2012-09-01 | | Gary Hallmark | 2012-09-01 | | Suzanne Viescas | 2012-09-01 | | Mariya Sergienko | 2012-09-01 | | Suzanne Viescas | 2012-09-01 | | Suzanne Viescas | 2012-09-02 | | Robert Brown | 2012-09-02 | | Luke Patterson | 2012-09-02 | | Alaina Hallmark | 2012-09-02 | | Rachel Patterson | 2012-09-02 | | Mark Rosales | 2012-09-02 | | Sam Abolrous | 2012-09-02 | | Joyce Bonnicksen | 2012-09-02 | | Jim Wilson | 2012-09-02 | | Mark Rosales | 2012-09-02 | . . . (944 rows)List employees and the customers for whom they booked an order. (Hint: The solution requires a JOIN of more than two tables.)
+-------------------+------------------+ | EmpFullName | CustFullName | +-------------------+------------------+ | Ann Patterson | Liz Keyser | | Ann Patterson | Robert Brown | | Ann Patterson | Sam Abolrous | | Ann Patterson | Dean McCrae | | Ann Patterson | Suzanne Viescas | | Ann Patterson | John Viescas | | Ann Patterson | Mariya Sergienko | | Ann Patterson | Andrew Cencini | | Ann Patterson | Angel Kennedy | | Ann Patterson | Rachel Patterson | | Ann Patterson | Maria Patterson | | Ann Patterson | Luke Patterson | | Ann Patterson | Mark Rosales | | Ann Patterson | Darren Gehring | | Ann Patterson | David Smith | | Ann Patterson | Kirk DeGrasse | | Ann Patterson | Manuela Seidel | | Ann Patterson | Caleb Viescas | | Ann Patterson | Jim Wilson | | Ann Patterson | Neil Patterson | | Ann Patterson | William Thompson | | Ann Patterson | Alaina Hallmark | | Ann Patterson | Zachary Ehrlich | | Ann Patterson | Joyce Bonnicksen | | Ann Patterson | Gary Hallmark | | Ann Patterson | Estella Pundt | | Ann Patterson | Julia Schnebly | | Mary Thompson | Sam Abolrous | | Mary Thompson | Kirk DeGrasse | | Mary Thompson | Luke Patterson | | Mary Thompson | Manuela Seidel | . . . (211 rows)Display all orders, the products in each order, and the amount owed for each product, in order number sequence. (Hint: The solution requires a JOIN of more than two tables.)
+---------+------------+-----------+---------------------------------------+---------+------+------------+ | OrderNo | OrderDate | ProductNo | Product | Price | Qty | AmountOwed | +---------+------------+-----------+---------------------------------------+---------+------+------------+ | 1 | 2012-09-01 | 6 | Viscount Mountain Bike | 635.00 | 3 | 1905.00 | | 1 | 2012-09-01 | 16 | ProFormance ATB All-Terrain Pedal | 28.00 | 1 | 28.00 | | 1 | 2012-09-01 | 26 | Glide-O-Matic Cycling Helmet | 121.25 | 5 | 606.25 | | 1 | 2012-09-01 | 21 | Dog Ear Aero-Flow Floor Pump | 55.00 | 3 | 165.00 | | 1 | 2012-09-01 | 11 | GT RTS-2 Mountain Bike | 1650.00 | 4 | 6600.00 | | 1 | 2012-09-01 | 40 | Ultimate Export 2G Car Rack | 174.60 | 6 | 1047.60 | | 1 | 2012-09-01 | 1 | Trek 9000 Mountain Bike | 1200.00 | 2 | 2400.00 | | 2 | 2012-09-01 | 27 | X-Pro All Weather Tires | 24.00 | 4 | 96.00 | | 2 | 2012-09-01 | 40 | Ultimate Export 2G Car Rack | 180.00 | 4 | 720.00 | | 3 | 2012-09-01 | 36 | Cosmic Elite Road Warrior Wheels | 165.00 | 4 | 660.00 | | 3 | 2012-09-01 | 11 | GT RTS-2 Mountain Bike | 1650.00 | 1 | 1650.00 | | 3 | 2012-09-01 | 31 | True Grip Competition Gloves | 21.34 | 5 | 106.70 | | 3 | 2012-09-01 | 1 | Trek 9000 Mountain Bike | 1164.00 | 5 | 5820.00 | | 3 | 2012-09-01 | 6 | Viscount Mountain Bike | 615.95 | 5 | 3079.75 | | 3 | 2012-09-01 | 16 | ProFormance ATB All-Terrain Pedal | 28.00 | 2 | 56.00 | | 3 | 2012-09-01 | 26 | Glide-O-Matic Cycling Helmet | 125.00 | 3 | 375.00 | . . . (3973 rows)Show me the vendors and the products they supply to us for products that cost less than $100. (Hint: The solution requires a JOIN of more than two tables.)
+------------------------+---------------------------------------+----------------+ | VendName | ProductName | WholesalePrice | +------------------------+---------------------------------------+----------------+ | Shinoman, Incorporated | Shinoman 105 SC Brakes | 14.51 | | Shinoman, Incorporated | Shinoman Dura-Ace Headset | 41.68 | | Shinoman, Incorporated | Shinoman Deluxe TX-30 Pedal | 27.79 | | Viscount | Viscount C-500 Wireless Bike Computer | 31.12 | | Viscount | Viscount Microshell Helmet | 22.86 | | Viscount | Viscount Tru-Beat Heart Transmitter | 29.84 | | Nikoma of America | Nikoma Lok-Tight U-Lock | 21.53 | | Nikoma of America | Glide-O-Matic Cycling Helmet | 81.56 | | Nikoma of America | X-Pro All Weather Tires | 15.66 | | Nikoma of America | Turbo Twin Tires | 18.92 | | ProFormance | ProFormance Toe-Klips 2G | 3.34 | | ProFormance | ProFormance ATB All-Terrain Pedal | 18.76 | . . . (66 rows)Show me customers and employees who have the same last name. (Hint: The solution requires a JOIN on matching values.)
+------------------+-------------------+ | CustFullName | EmpFullName | +------------------+-------------------+ | Suzanne Viescas | Carol Viescas | | Suzanne Viescas | David Viescas | | William Thompson | Mary Thompson | | John Viescas | Carol Viescas | | John Viescas | David Viescas | | Neil Patterson | Ann Patterson | | Neil Patterson | Kathryn Patterson | | Rachel Patterson | Ann Patterson | | Rachel Patterson | Kathryn Patterson | | Caleb Viescas | Carol Viescas | | Caleb Viescas | David Viescas | | Maria Patterson | Ann Patterson | | Maria Patterson | Kathryn Patterson | | Kirk DeGrasse | Kirk DeGrasse | | Luke Patterson | Ann Patterson | | Luke Patterson | Kathryn Patterson | +------------------+-------------------+ (16 rows)
Bowling League Database
List the bowling teams and all the team members. (Hint: The solution requires a JOIN of two tables.)
+------------+---------------------+ | TeamName | BowlerFullName | +------------+---------------------+ | Marlins | Fournier, Barbara | | Marlins | Fournier, David | | Marlins | Kennedy, John | | Marlins | Sheskey, Sara | | Sharks | Patterson, Ann | | Sharks | Patterson, Neil | | Sharks | Viescas, David | | Sharks | Viescas, Stephanie | | Terrapins | Black, Alastair | | Terrapins | Cunningham, David | | Terrapins | Kennedy, Angel | | Terrapins | Viescas, Carol | | Barracudas | Hallmark, Elizabeth | | Barracudas | Hallmark, Gary | | Barracudas | Patterson, Kathryn | | Barracudas | Sheskey, Richard | | Dolphins | Hernandez, Kendra | | Dolphins | Hernandez, Michael | | Dolphins | Viescas, John | | Dolphins | Viescas, Suzanne | | Orcas | Ehrlich, Zachary | | Orcas | Hallmark, Alaina | | Orcas | Viescas, Caleb | | Orcas | Thompson, Sarah | | Manatees | Patterson, Megan | | Manatees | Thompson, Mary | | Manatees | Thompson, William | | Manatees | Viescas, Michael | | Swordfish | Hallmark, Bailey | | Swordfish | Patterson, Rachel | | Swordfish | Pundt, Steve | | Swordfish | Rosales, Joe | +------------+---------------------+ (32 rows)Display the bowlers, the matches they played in, and the bowler game scores. (Hint: The solution requires a JOIN of more than two tables.)
+---------+------------+--------------------+------------+----------+ | MatchID | TeamName | BowlerFullName | GameNumber | RawScore | +---------+------------+--------------------+------------+----------+ | 1 | Marlins | Barbara Fournier | 1 | 146 | | 1 | Marlins | Barbara Fournier | 2 | 146 | | 1 | Marlins | Barbara Fournier | 3 | 153 | | 5 | Marlins | Barbara Fournier | 1 | 145 | | 5 | Marlins | Barbara Fournier | 2 | 137 | | 5 | Marlins | Barbara Fournier | 3 | 152 | | 10 | Marlins | Barbara Fournier | 1 | 140 | | 10 | Marlins | Barbara Fournier | 2 | 137 | | 10 | Marlins | Barbara Fournier | 3 | 161 | | 13 | Marlins | Barbara Fournier | 1 | 159 | | 13 | Marlins | Barbara Fournier | 2 | 140 | | 13 | Marlins | Barbara Fournier | 3 | 158 | | 17 | Marlins | Barbara Fournier | 1 | 139 | | 17 | Marlins | Barbara Fournier | 2 | 164 | | 17 | Marlins | Barbara Fournier | 3 | 161 | | 21 | Marlins | Barbara Fournier | 1 | 141 | | 21 | Marlins | Barbara Fournier | 2 | 141 | | 21 | Marlins | Barbara Fournier | 3 | 139 | | 25 | Marlins | Barbara Fournier | 1 | 149 | | 25 | Marlins | Barbara Fournier | 2 | 159 | | 25 | Marlins | Barbara Fournier | 3 | 138 | | 29 | Marlins | Barbara Fournier | 1 | 161 | | 29 | Marlins | Barbara Fournier | 2 | 141 | | 29 | Marlins | Barbara Fournier | 3 | 156 | | 34 | Marlins | Barbara Fournier | 1 | 140 | | 34 | Marlins | Barbara Fournier | 2 | 140 | | 34 | Marlins | Barbara Fournier | 3 | 149 | | 37 | Marlins | Barbara Fournier | 1 | 143 | | 37 | Marlins | Barbara Fournier | 2 | 156 | | 37 | Marlins | Barbara Fournier | 3 | 151 | | 41 | Marlins | Barbara Fournier | 1 | 143 | | 41 | Marlins | Barbara Fournier | 2 | 156 | | 41 | Marlins | Barbara Fournier | 3 | 142 | | 45 | Marlins | Barbara Fournier | 1 | 164 | | 45 | Marlins | Barbara Fournier | 2 | 153 | | 45 | Marlins | Barbara Fournier | 3 | 163 | | 49 | Marlins | Barbara Fournier | 1 | 142 | | 49 | Marlins | Barbara Fournier | 2 | 145 | | 49 | Marlins | Barbara Fournier | 3 | 140 | | 53 | Marlins | Barbara Fournier | 1 | 139 | | 53 | Marlins | Barbara Fournier | 2 | 155 | | 53 | Marlins | Barbara Fournier | 3 | 158 | | 1 | Marlins | David Fournier | 1 | 166 | | 1 | Marlins | David Fournier | 2 | 135 | . . . (1344 rows)