SQL Data Definition II

1. SQL Data Definition II Stanislava Armstrong http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html SQL Data Definition II

2. Creating Tables • From last lecture… • CREATE TABLE • Columns • Data types • [NOT] NULL, DEFAULT values • Constraints • Primary keys • Unique columns • Foreign keys CREATE TABLE <name> ( <col-def-1>, <col-def-2>, : <col-def-n>, <constraint-1>, : <constraint-k> )[Engine = <engine_choice>]; SQL Data Definition II

3. Deleting Tables To delete a table use: DROP TABLE [IF EXISTS] <name> e.g.: DROP TABLE Fruit; • BE CAREFUL with any SQL statement with DROP in it • You will delete any information in the table as well • You won’t normally be asked to confirm • There is no easy way to undo the changes SQL Data Definition II

4. Changing Tables Sometimes you want to change the structure of an existing table • One way is to DROP it then rebuild it • This is dangerous, so there is the ALTER TABLE command instead ALTER TABLE can • Add a new column • Remove an existing column • Add a new constraint • Remove an existing constraint • Change a column’s name and definition SQL Data Definition II

5. ALTERing Columns I To add columns use: ALTER TABLE <table> ADD COLUMN <col> <col_definition>; To remove columns use: ALTER TABLE <table> DROP COLUMN <name>; E.g.: #add a Quality column to the Fruit table ALTER TABLE Fruit ADD COLUMN Quality int; #remove the Quality column from the Fruit table ALTER TABLE Fruit DROP COLUMN Quality; SQL Data Definition II

6. ALTERing Columns II To change columns use: ALTER TABLE <table> CHANGE COLUMN <old_col_name> <new_col_name> <col_definition>; To modify a column’s definition use: ALTER TABLE <table> MODIFY COLUMN <col_name> <col_definition>; The new column definitions have to be compatible with all values already entered into the column. SQL Data Definition II

7. ALTERing Constraints To add constraints use: ALTER TABLE <table> ADD CONSTRAINT <definition>; To remove UNIQUE constraints use: ALTER TABLE <table> DROP INDEX <name>; To remove any other constraints use: ALTER TABLE <table> DROP CONSTRAINT <name>; Examples: #make the fruitName column in the Fruit table UNIQUE ALTER TABLE Fruit ADD CONSTRAINT unFruitNameUNIQUE (fruitName); #remove the UNIQUE constraint on fruitNamecolumn ALTER TABLE Fruit DROP INDEX unFruitName; SQL Data Definition II

8. INSERT, UPDATE, DELETE • INSERT - add a row to a table • UPDATE - change row(s) in a table • DELETE - remove row(s) from a table • UPDATE and DELETE use ‘WHERE clauses’ to specify which rows to change or remove • BE CAREFUL with these - an incorrect WHERE clause can destroy lots of data SQL Data Definition II

9. INSERT INSERT INTO <table> [(col1, col2, …)] VALUES (val1, val2, …); e.g.: INSERT INTO Fruit (FruitID, FruitName, FruitPrice) VALUES (NULL, 'kiwi', 20); • The number of columns and values must be the same • If you are adding a value to every column, you don’t have to list them, but you will need to put them in the correct order SQL Data Definition II

10. INSERT Fruit Fruit Fruit Fruit ID 1 2 ID 1 2 ID 1 ID 1 2 Name kiwi apple Name kiwi Name kiwi apple Name kiwi apple Price 20 Price 20 Price 20 34 Price 20 34 INSERT INTO Fruit (ID, Name, Price) VALUES (2, ‘apple’, 34) INSERT INTO Fruit (Name, ID) VALUES (‘apple’, 2) INSERT INTO Fruit VALUES (2, ‘apple’, 34) SQL Data Definition II

11. UPDATE UPDATE <table> SET col1 = val1 [,col2 = val2…] [WHERE <condition>]; e.g.: UPDATE Fruit SET FruitID = 5 WHERE FruitName = 'kiwi‘; • All rows where the condition is true have the columns set to the given values • If no condition is given all rows are changed so BE CAREFUL • Values are constants or can be computed from columns • You can update multiple tables at the same time SQL Data Definition II

12. UPDATE Fruit Fruit Fruit ID 1 2 3 4 ID 1 2 3 4 ID 1 2 3 4 Name kiwi apple grape lemon Name kiwi apple grape melon Name kiwi apple grape lemon Price 20 34 70 2 Price 20 34 70 100 Price 25 39 75 7 UPDATE Fruit SET Price = 100, Name = ‘melon’ WHERE ID = 4; UPDATE Fruit SET Price = Price +5; SQL Data Definition II

13. DELETE Removes all rows which satisfy the condition DELETE FROM <table> [WHERE <condition>]; • If no condition is given then ALL rows are deleted - BE CAREFUL • Some versions of SQL also have TRUNCATE TABLE <T> which is like DELETE FROM <T> but it is quicker as it doesn’t record its actions e.g.: DELETE FROM Fruit WHERE Price > 30; SQL Data Definition II

14. DELETE Fruit Fruit ID 1 2 3 4 ID 2 3 Name kiwi apple grape lemon Name apple grape Price 20 34 70 20 Price 34 70 DELETE FROM Fruit WHERE Price = 20; Fruit DELETE FROM Fruit; or TRUNCATE TABLE Fruit; ID Name Price SQL Data Definition II

15. Being Careful • When using DELETE and UPDATE • You need to be careful to have the right WHERE clause • You can check it by running a SELECT statement with the same WHERE clause first (more on this later :) Before running DELETE FROM Student WHERE Year = 3; run SELECT * FROM Student WHERE Year = 3; SQL Data Definition II

16. Exercise SQL Data Definition II

17. Exercise -Sam brought a holiday to Varna and is leaving on 23rd August 2012. He told us that lives in Chilwell, but he hasn’t given us a phone number. Update the database to reflect the new booking. -Tania hasn’t sent us the payment for the holiday, her address is too vague and we haven’t been able to get hold of her over the phone. Remove her booking. -Add £10.50 to the price of all holidays that cost more than £200.00. -Add a new column, Attractions, to the Destination table. This column should hold a string of up to 500 characters, and if no Attractions is provided then it should default to the empty string. SQL Data Definition II

18. Reading Material • The Manga Guide to Databases – chapters 3 • Database Systems – A Practical Approach to Design, Implementation and Management by Connolly and Begg – chapters 5 and 6 • Any other book – chapter on SQL data definition • http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html- MySQL documentation for data definition SQL Data Definition