Simple Insert
INSERT INTO table_name VALUES (‘col1_value‘,’col2_value‘);
Challenge 7
Insert a new artist, “Taylor Swift” in to the Artist table.
You will have to find out:
- How many columns are in the table.
- What the highest ArtistID is.
Insert using column names
INSERT INTO table_name (col1_name, col2_name)VALUES (‘col1_value‘, ‘col2_value‘);
This technique is used where you want to insert data into specific columns in a table. You would use this when:
- Some columns can be left empty/blank and you don’t want to add data.
- Some columns are Auto Incrementing and you want the database to pick the next ID/number.
Challenge 8
Add a new customer to the Customer table, with the following details:
- First Name : Bob
- Second Name : Jones
- Email : bob@jones.com
UPDATE table_name SET column_name = ‘new_value‘ WHERE column_name = ‘old_value‘;
Update can be used to update one or many rows in a table
Challenge 9
The international consortium of music genres has decided to rename the genre ‘Jazz’ to ‘Jazz Hands’ with immediate effect. Apply this change to the Genre table.
Warning
The update function is very powerful, but also very dangerous! If you forget to include the WHERE, it will rename every entry in the table!!!
DELETE FROM table_name WHERE column_name = ‘value‘;
Challenge 10
U2 have decided to give away their entire back catalogue for free, remove themselves from public life and spend of the rest of their days as nuns. Remove them from the Artist table.
Challenge 11
The company have just found out the Nancy Edwards has been stealing albums from the company and flogging them on Ebay. She has been fired, so remove her from the Employee table with immediate effect.
Challenge 12
The company has decided that in today’s market they are going to have to specialize in a niche in order to survive. They have therefore decided to only sell songs over 275 seconds long. Remove all non-conforming tracks from the Track table. Long live the long songs!
Load up the learning log and add your notes on each of the techniques used today in to the learning log.
Finish off challenges 6 to 12.