1 / 12

DBS201: More on SQL

DBS201: More on SQL. Lecture 3. Agenda. How to use SQL to update table definitions How to update data in a table How to join tables together. Updating a Table Definition. ALTER TABLES Used to update a database definition Syntax ALTER TABLE tablename Can do any of the following

erna
Download Presentation

DBS201: More on SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DBS201: More on SQL Lecture 3

  2. Agenda • How to use SQL to update table definitions • How to update data in a table • How to join tables together

  3. Updating 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

  4. Updating a Table Definition • To add a field: • ALTER TABLE tablename ADD COLUMN field-name datatype • i.e. ALTER TABLE MARINA ADD COLUMN SLIP_DESCRIPTION CHAR (20)

  5. Updating a Table Definition • To alter a field: • ALTER TABLE tablename ALTER COLUMN fieldname SET DATA TYPE data type • i.e. ALTER TABLE MARINA ALTER COLUMN SLIP_DESCRIPTION SET DATA TYPE CHAR(12) NOT NULL WITH DEFAULT 'abc'

  6. Updating a Table Definition • To remove a field: • ALTER TABLE tablename DROP COLUMN fieldname • i.e. ALTER TABLE MARINA DROP COLUMN SLIP_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

  7. Updating a Table Definition • To add a constraint: • ALTER TABLE tablename ADD constraint • Constraint is primary key or foreign key • i.e. ALTER TABLE MARINA ADD constraint Slip_pk PRIMARY KEY (SLIP_DESCRIPTION) • ALTER TABLE tablename ADD constraint • Constraint is value restricted • i.e. ALTER TABLE MARINA ADD constraint Slip_ck CHECK (SLIP_DESCRIPTION BETWEEN 'AAA' AND 'DDD‘)

  8. Updating a Table Definition • To drop a constraint • ALTER TABLE tablename DROP CONSTRAINT constraint name • i.e. • ALTER TABLE MARINA DROP CONSTRAINT slip_ck

  9. Updating Data in a Table • To update data in a table, use the UPDATE statement • UPDATE tablename SET fieldname = new value WHERE condition • i.e. UPDATE MARINA SET SLIP_DESCRIPTION = ‘AAA' UPDATE MARINA SET SLIP_DESCRIPTION = ‘AAB‘ WHERE MARINA_ID = ‘AZ24’

  10. How to Join Tables • Can join two or more tables together in a Select statement • Result is output that has associated the correct rows from each table • Result exists only for the duration of the execution of the query

  11. How to Join Tables • To create a join, specify the tables that you want to be included • For each field you want, specify it’s full name: table-name.field-name • Must use the WHERE clause of the SELECT statement. The WHERE clause controls what is being joined

  12. How to Join Tables • Two tables: Marina and Customer • Create a report that shows the boat name and customer name • SELECT statement might look like: SELECT CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME, MARINA.BOAT_NAME FROM CUSTOMER, MARINA WHERE CUSTOMER.OWNER_NUM = MARINA.OWNER_NUM)

More Related