pico_tools_db.sql

--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    |
+--------+--------------+-------+----------+