Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1875
Members Online:
3
Guests Online:
74

Total Post History
Posts:
81446
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
konam534As

Other

FAQ
All times are UTC [ DST ]

Google Ads

Alien Pizza - SQL Puzzle

Mind Boggling Puzzles, to keep that grey matter in shape...
Forum rules
Always post answers in a "Hidecode" tag, so that others have a chance to answer the question too.
Please wait...

Alien Pizza - SQL Puzzle

Postby Emtucifor on Sat Mar 19, 2011 7:39 pm

Alien Pizza is high-tech pizza company that uses a SQL Server database back end.

They take orders electronically, and let customers build pizzas exactly how they want. It is perfectly legitimate to order a pizza with just cheese (and no crust) or to have five crusts of different types.

Alien Pizza uses an external billing system to handle actual charges, which get posted to customer accounts. When a pizza order is placed, billing charges are sent right away to the billing provider, even if the order is for a later date. This is the only way the customer can know how much the pizza will cost, and even pay in advance if he likes.

You work as a database analyst for Alien Pizza. Your boss says that a customer apparently had forgotten to check with his wife before ordering, and she demanded a bunch of changes, so he needs his order for tomorrow changed. The problem is that no billing change logic was built into the system!

First, the system has a quirk: it can't show the new Pizza as it should look after the changes, it only provides the current pizza order and a change list. Only AFTER the billing charges are sent on to the billing provider will the change list be merged with the pizza order internally.

Second, the change list does not have only the items that changed, but all the items in a category that had any changed items (for example, if one cheese is changed, then the change list has all current cheeses listed again). If all items in a category are removed, a single row for that category will be included with the value set to '' (empty string) indicating it is now empty.

Write a query that will create a list of the charges that need to be added and removed (one row per charge), based on the following tables, so that you can send a list of cancelled and new charges to the billing provider.

  1. SET NOCOUNT ON;
  2. DECLARE @Charges TABLE (
  3.     Category VARCHAR(32) NOT NULL,
  4.     Topping VARCHAR(32) NOT NULL,
  5.     Charge VARCHAR(32),
  6.     PRIMARY KEY CLUSTERED (Category, Topping)
  7. );
  8. INSERT @Charges SELECT 'Meat', 'Pepperoni', '$MEAT';
  9. INSERT @Charges SELECT 'Meat', 'Sausage', '$MEAT';
  10. INSERT @Charges SELECT 'Meat', 'Proscuitto', '$MEAT2';
  11. INSERT @Charges SELECT 'Meat', 'Anchovy', '$MEAT';
  12. INSERT @Charges SELECT 'Meat', 'Escargot', '$SPECIALTY';
  13. INSERT @Charges SELECT 'Cheese', 'Cheddar', '$CHEESE';
  14. INSERT @Charges SELECT 'Cheese', 'Swiss', '$CHEESE';
  15. INSERT @Charges SELECT 'Cheese', 'Provolone', '$CHEESE';
  16. INSERT @Charges SELECT 'Cheese', 'Emmenthaler', '$CHEESE2';
  17. INSERT @Charges SELECT 'Crust', 'Thin', '$CRUST';
  18. INSERT @Charges SELECT 'Crust', 'Normal', '$CRUST';
  19. INSERT @Charges SELECT 'Crust', 'Thick', '$CRUST2';
  20. INSERT @Charges SELECT 'Crust', 'Stuffed', '$CRUST3';
  21. INSERT @Charges SELECT 'Crust', 'Triple', '$SPECIALTY';
  22. INSERT @Charges SELECT 'Crust', 'Calzone', '$CRUST4';
  23. INSERT @Charges SELECT 'Sauce', 'Tomato', '$SAUCE';
  24. INSERT @Charges SELECT 'Sauce', 'Ranch', '$SAUCE';
  25. INSERT @Charges SELECT 'Sauce', 'Pesto', '$SAUCE';
  26. INSERT @Charges SELECT 'Vegetable', 'Mushroom', '$VEG';
  27. INSERT @Charges SELECT 'Vegetable', 'Onion', '$VEG';
  28. INSERT @Charges SELECT 'Vegetable', 'Tomato', '$VEG';
  29. INSERT @Charges SELECT 'Vegetable', 'Endive', '$VEG2';
  30. INSERT @Charges SELECT 'Misc1', 'Delivery', '$DELIVERY';
  31. INSERT @Charges SELECT 'Alien', 'Tentacles', '$SPECIALTY';
  32. INSERT @Charges SELECT 'Alien', 'Eyestalks', '$SPECIALTY';
  33. INSERT @Charges SELECT 'Alien', 'Blazzlerunkus', '$SPECIALTY';
  34.  
  35. DECLARE @CurrentPizza TABLE (
  36.     Category VARCHAR(32) NOT NULL,
  37.     Topping VARCHAR(32) NOT NULL,
  38.     PRIMARY KEY CLUSTERED (Category, Topping)
  39. );
  40. INSERT @CurrentPizza SELECT 'Meat', 'Pepperoni';
  41. INSERT @CurrentPizza SELECT 'Meat', 'Escargot';
  42. INSERT @CurrentPizza SELECT 'Meat', 'Anchovy';
  43. INSERT @CurrentPizza SELECT 'Cheese', 'Emmenthaler';
  44. INSERT @CurrentPizza SELECT 'Cheese', 'Cheddar';
  45. INSERT @CurrentPizza SELECT 'Crust', 'Normal';
  46. INSERT @CurrentPizza SELECT 'Sauce', 'Pesto';
  47. INSERT @CurrentPizza SELECT 'Vegetable', 'Mushroom';
  48. INSERT @CurrentPizza SELECT 'Vegetable', 'Onion';
  49.  
  50. DECLARE @Changes TABLE (
  51.     Category VARCHAR(32) NOT NULL,
  52.     Topping VARCHAR(32) NOT NULL,
  53.     PRIMARY KEY CLUSTERED (Category, Topping)
  54. );
  55. -- Rows in the Changes table represent complete replacements for that category.
  56. INSERT @Changes SELECT 'Meat', 'Pepperoni';
  57. INSERT @Changes SELECT 'Meat', 'Sausage'; -- Removing anchovy and escargot from the meats, adding sausage
  58. INSERT @Changes SELECT 'Crust', 'Stuffed'; -- The crust is being changed completely
  59. INSERT @Changes SELECT 'Vegetable', 'Mushroom'; -- We're removing onions
  60. INSERT @Changes SELECT 'Sauce', ''; -- We don't want any sauce at all
  61. INSERT @Changes SELECT 'Misc1', 'Delivery'; -- This item is completely new
  62. INSERT @Changes SELECT 'Alien', 'Tentacles'; -- we want some Alien in our pizza!
  63. INSERT @Changes SELECT 'Alien', 'Eyestalks';
  64. INSERT @Changes SELECT 'Alien', 'Blazzlerunkus';
  65. -- Note that the sauce and cheese choices remain unchanged and are not in the @Changes table

The result should look like this:
ChargeAction
$CRUSTRemove
$SAUCERemove
$VEGRemove
$CRUST3Add
$DELIVERYAdd
$SPECIALTYAdd
$SPECIALTYAdd

Please hide your answers with [hidecode] tags.

Edit: I removed a commented-out line from the changes list. Nothing is affected, but it could have confused things.
Last edited by Emtucifor on Tue Mar 22, 2011 6:32 pm, edited 1 time in total.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Alien Pizza - SQL Puzzle

Postby SQLDenis on Tue Mar 22, 2011 5:10 pm

I looked at this at home and started with a FULL OUTER JOIN and of course that overcomplicated things

with exists and not exists it would not be bad but I guess that is now what you want to see

Also are you sure you should see this row twice?

$SPECIALTY Add
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Alien Pizza - SQL Puzzle

Postby Emtucifor on Tue Mar 22, 2011 6:27 pm

Do it any way you like! Use EXISTS if you want.

And yes I'm sure about the double Add. :)
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Alien Pizza - SQL Puzzle

Postby fieldb1 on Thu Apr 26, 2012 7:49 pm

Code is hidden, SHOW
fieldb1
Apprentice
Apprentice
 
Posts: 6
Joined: Thu Apr 26, 2012 5:24 pm
Unrated

Re: Alien Pizza - SQL Puzzle

Postby fieldb1 on Thu Apr 26, 2012 7:50 pm

I posted the code above, but am new to the site and didn't display the color correctly.
Anyway, it works. I would love to see a simplified version.
fieldb1
Apprentice
Apprentice
 
Posts: 6
Joined: Thu Apr 26, 2012 5:24 pm
Unrated