100 likes | 285 Views
DBS201: More on SQL - DDL. Lecture 11 - Part 2 - ALTER. Agenda. Data Definition Language How to use SQL to update table definitions Data Manipulation Language How to update data in a table How to delete rows of data in a table. Changing a Table Definition. ALTER TABLES
E N D
DBS201: More on SQL - DDL Lecture 11 - Part 2 - ALTER
Agenda • Data Definition Language • How to use SQL to update table definitions • Data Manipulation Language • How to update data in a table • How to delete rows of data in a table
Changing a Table Definition • ALTER TABLES • Used to update a database definition • Syntax • ALTER TABLE tablename • Can do any of the following • Add a field • Alter a field • Drop a field • Add a constraint • Drop a constraint
Changing a Table Definition • To add a field: • ALTER TABLE tablename ADD COLUMN field-name datatype Example: • ALTER TABLE MARINA ADD COLUMN Boat_Description CHAR (20)
Changing a Table Definition • To change a field definition: • ALTER TABLE tablename ALTER COLUMN fieldname SET DATA TYPE data type Example: • ALTER TABLE MARINA ALTER COLUMN Boat_Description SET DATA TYPE CHAR(12) NOT NULL WITH DEFAULT 'abc'
Changing a Table Definition • To remove a field: • ALTER TABLE tablename DROP COLUMN column_name Example: ALTER TABLE MARINA DROP COLUMN Boat_Description *Note – be careful not to drop a column that you may in fact need as the data will be lost. Rule of thumb: do not alter a table after it contains data
Changing a Table Definition • To add a constraint: Example defining the PRIMARY KEY: ALTER TABLE MARINA ADD CONSTRAINT Boat_No_PK PRIMARY KEY (Boat_No) Example of adding a check constraint: ALTER TABLE MARINA ADD CONSTRAINT Boat_Code_CK CHECK (Boat_Code BETWEEN 'AAA' AND 'DDD‘)
Updating a Table Definition - DDL • To drop a constraint • ALTER TABLE tablename DROP CONSTRAINT constraint_name • ALTER TABLE MARINA DROP CONSTRAINT Boat_Name_UN Name must be the name assigned to the constraint (may have been assigned by user or by system)
Updating Data in a Table • To update data in a table, use UPDATE. • UPDATE tablename SET column name = new value WHERE condition examples UPDATE MARINA SET Dock_Number = ‘AAA' UPDATE MARINA SET Dock_Number = ‘AAB‘ WHERE Marina_Num = ‘M-2407’
How to Delete Rows in Tables-DDL • To delete a row in a table, Use the Delete statement DELETE FROM tablename WHERE condition example DELETE FROM MARINA WHERE Dock_Num = ‘CCC’