Data and Schema Modifications in SQL

7.1 Inserting Rows

insert into table $\left[\right.$(attributes)$\left.\right]$
$<$values (values) $\left|\right.$ SQL-query $>$

The previous example inserts a single record, the following incorporates information from an alternative table.

Incomplete insertions are similar to

7.2 Deleting Rows

delete from table
$\left[\right.$where condition$\left.\right]$

7.3 Updating Attributes

update table
set attribute = $<$expr $\left|\right.$ SQL-query $\left|\right.$ null $\left|\right.$ default $>$,...
$\left[\right.$where condition$\left.\right]$

7.4 Updating Table Definitions

alter table name
$<$
add column def $\left|\right.$
drop column name $\left|\right.$
alter column name $<$ set default value $\left|\right.$ drop default $>$
$>$

Names can be assigned to constraints by a prefix of the form constraint name.

7.5 Modifications through Views

• A view is declared to be updated-able by appending its definition with the clause ‘with check option’
  create view Males (Nm,Num) 
  select Name,Number 
  from Students 
  where  Sex = ’M’ 
  with check option 
  

• A view may be updated only if there is a unique way to achieve it on the base relations.
  An ‘insert into Males(Name,Number) values (Ron,4123)’ adds the record ‘Ron,4123)’ to ‘Students’.

  Students Name Number Sex Ben 3412 M Dan 1234 M Nel 2341 F

  Name Number Sex Ben 3412 M Dan 1234 M Nel 2341 F Ron 4123 NULL

7.6 Removing Components

drop $<$ table $\left|\right.$ view $>$ name $\left[\right.$restrict $\left|\right.$ cascade $\left.\right]$

• restrict asks the action to take place only if the component is empty
• cascade removes the component and its dependents

7.7 Updating Domain Definitions

alter domain name
$<$
set value value $\left|\right.$
drop default $\left|\right.$
add constraint def $\left|\right.$
drop constraint name
$>$

The revised specifications must agree with the data already stored in the table

7.8 Assignment #3

Due: Mo, Feb 9, midnight

Provide a SQL file which performs the following operations.

1. Declares the tables of the library schema of Problem 6.18 in the textbook (pages 186–187 4th ed; 215–216 5th ed).
2. Populates the tables with entries which produce non-empty answers for the queries below
3. Issues queries (a)–(g) of Problem 6.18

Notes

• Submit your file electronically using the departmental submit utility: submit XXX lab3 lab3.sql. XXX represents ‘c670aa’ for students of the 3:30 section, and ‘c670ab’ for students of the 5:30 section.
  • Files are restricted to 1,048,576 bytes
  • The submit program issues error messages to the student’s CSE email account

• Your file should be well formatted and commented.

• isql can be invoked in batch mode with commands of the form
  isql -X -i file.sql

  or of the form

  isql -P password < file.sql

• Complex files may be compilable only in batch mode, and not be receptive to import through the ‘:r’ command in interactive mode.

• The go directives are required also in files processed in batch mode. For instance,
  /* comment */ 
  create ... 
  go 
  insert to ... 
  go 
  select ... 
  from   ... 
  where       ... 
  go 
  delete from   ... 
  go 
  ... 
  

• Sybase accounts are established in a a similar way as for Unix accounts. For SSH programs, use the host name ‘stdsun.cse.ohio-state.edu’.

  If you don’t have a, or don’t remember your CSE Unix account, please go with an id to the SOC lab (8th floor of DL) and get an account.

  If you don’t have a, or don’t remember your, sybase account, please contact help@cse.ohio-state.edu. Put the word PRIVATE in the subject header when sending a request that contains sensitive information, such as social security numbers. (If you fail to do this, any information included in the request will be viewable to anyone, anywhere on the web.)

References:

• A tutorial on Sybase (Kerr et. al.) for CSE 670. [corrections]
• Reference manual and other manuals (accessible only through a cse account)
• Q&A

Reference: Ch. 8.6 in textbook.