Last active
April 16, 2019 14:43
-
-
Save yh2n/a0fdcca1d335d04eccab6635e3bb8e66 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--Relational Database | |
--1. You are the proud owner of the MySQL database schema below. Write a query that will return a result set of (plan name, | |
-- price, and interval) ordered by price from lowest to highest. | |
SELECT Plan.name, Pricing.price, Pricing.interval | |
FROM Plan, Pricing | |
WHERE Plan.id=Pricing.plan_id | |
ORDER BY price; | |
+--------+-------+----------+ | |
| name | price | interval | | |
+--------+-------+----------+ | |
| Sports | 3.00 | month | | |
| Music | 3.00 | month | | |
| Film | 5.00 | month | | |
| Sports | 30.00 | year | | |
| Music | 39.00 | year | | |
+--------+-------+----------+ | |
-- The requirements have changed! Instead of pricing options being applied directly to a plan, each plan will | |
-- now have multiple tiers to allow for more advanced pricing configurations. Each tier will have a monthly | |
-- and/or annual price option. | |
-- 2. Write a DDL statement to create a new table called Tier. | |
CREATE TABLE Tier ( | |
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, | |
PRIMARY KEY(`id`) | |
); | |
-- 3. Write a series of SQL statements to migrate the existing records in Plan to Tier so that each plan has a | |
-- single tier with the name "Default Tier.” The Pricing records should now reference Tier instead of Plan. | |
ALTER TABLE Tier ADD COLUMN `name` VARCHAR(12) NOT NULL DEFAULT 'Default Tier'; | |
INSERT INTO `Tier` (`id`); | |
ALTER TABLE `Pricing` | |
DROP FOREIGN KEY `pricing_ibfk_1`, | |
CHANGE COLUMN `plan_id` `tier_id` INT(11) UNSIGNED NOT NULL, | |
ADD CONSTRAINT pricing_ibfk_1 FOREIGN KEY (`tier_id`) REFERENCES Tier (`id`); | |
-- 4. Write a query to return (plan name, tier name, price, and interval). | |
SELECT Plan.name AS plan, Tier.name AS tier, Pricing.price, Pricing.interval | |
FROM Plan, Pricing, Tier | |
WHERE Pricing.tier_id= Plan.id | |
GROUP BY Pricing.id; | |
+--------+--------------+-------+----------+ | |
| plan | tier | price | interval | | |
+--------+--------------+-------+----------+ | |
| Sports | Default Tier | 3.00 | month | | |
| Sports | Default Tier | 30.00 | year | | |
| Music | Default Tier | 3.00 | month | | |
| Music | Default Tier | 39.00 | year | | |
| Film | Default Tier | 5.00 | month | | |
+--------+--------------+-------+----------+ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment