Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE gc200380935;
- DROP TABLE IF EXISTS donations;
- CREATE TABLE donations (
- first_name VARCHAR(20) NOT NULL,
- last_name VARCHAR(20) NOT NULL,
- recipient VARCHAR(255) NOT NULL );
- INSERT INTO donations (first_name, last_name, recipient)
- VALUES ('Eric', 'Smith', 'Barrie, Liberal Party');
- INSERT INTO donations (first_name, last_name, recipient)
- VALUES ('Alicia', 'Jones', 'Simcoe North, Conservative Party');
- INSERT INTO donations (first_name, last_name, recipient)
- VALUES ('Sue', 'Wilson', 'London South, NDP');
- /*View the donations table*/
- select * FROM donations;
- /*Is this in 1NF (first normal form)?
- No, it needs a unqiue identifier (key) for each row
- Let add a column called donorID*/
- ALTER TABLE donations
- ADD COLUMN donorID INT NOT NULL auto_increment primary KEY;
- /*Let's make the donorID the first column*/
- ALTER TABLE donations
- MODIFY COLUMN donorID INT FIRST;
- /*Let's move the recipient column to be the 2nd column*/
- ALTER TABLE donations
- MODIFY COLUMN recipient VARCHAR(255) AFTER donorID;
- select * FROM donations;
- /*The riding and political parties are currently in the same column, which is not atomic. As such, we need to
- add 2 columns*/
- alter table donations
- add column riding VARCHAR(25) AFTER recipient;
- alter table donations
- add column party VARCHAR(25) AFTER riding;
- select * FROM donations;
- /*We need to populate these new fields, let's write some queries to do that*/
- select recipient , INSTR(recipient, ',') from donations;
- select LEFT(recipient, instr(recipient,',')-1) from donations;
- /*OR try substring index*/
- select substr(recipient,1,instr(recipient,',')-1) from donations;
- /*Now let's update the riding column with this information*/
- UPDATE donations
- SET riding = left(recipient, instr(recipient,',')-1);
- select * from donations;
- /*Create a query to give us the political party*/
- select substr(recipient, instr(recipient,',')+2) from donations;
- /*We've tested our query, let's update the table*/
- update donations
- set party = substr(recipient, instr(recipient,',')+2);
- /*We now need to DROP the recipient column*/
- alter table donations
- drop column recipient;
- /*We are now in first normal form! Also known as 1NF*/
- select* from donations;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement