More SQL Data Definitions

1
More SQL Data Definitions

• Database Systems Lecture 6

2
Covered Last Lecture

• SQL
• The SQL language
• CREATE TABLE
• Columns
• Primary Keys
• Foreign Keys
• For more information Connolly and Begg chapter 6

3
In todays Bonanza

• Starting SQL
• DROP TABLE
• ALTER TABLE
• INSERT, UPDATE, and DELETE
• Sequences
• For more information
• Connolly and Begg chapters 5 and 6

4
Creating Relations

• From last lecture.. CREATE TABLE
• Column definitions are made up of a
• Name, type (domain), and any column constraints
  you want to add.

• CREATE TABLE ltnamegt
• (
• ltcolumn definitionsgt,
• ltconstraintsgt
• )

5
Column Constraints

• NOT NULL
• PRIMARY KEY AUTO_INCREMENT
• FOREIGN KEY
• UNIQUE
• DEFAULT value
• CHECK (expression)
• COLLATE collation-name

6
Last Lectures Question
Write the SQL that would Create a new table
called Reviews which should have the following
columns rID, an integer that will be the primary
key cID, an integer that will be a foreign key to
the CD table Title, a string of up to 100
characters Text, a string of up to 5000
characters (5 marks)
7
Solution
CREATE TABLE Student ( rID INT, cID
INT, Title VARCHAR(100), Text
VARCHAR(5000), CONSTRAINT PRIMARY KEY
(rID), CONSTRAINT FOREIGN KEY (cID) )
CREATE TABLE Reviews ( rID INT NOT
NULL, cID INT NOT NULL, Title
VARCHAR(100) NOT NULL, Text VARCHAR(5000) NOT
NULL, CONSTRAINT PRIMARY KEY (rID),
CONSTRAINT FOREIGN KEY (cID) )
8
Deleting Tables

• To delete a table use
• DROP TABLE
• IF EXISTS
• ltnamegt
• Example
• DROP TABLE Module

• BE CAREFUL with any SQL statement with DROP in it
• You will delete any information in the table as
  well
• You wont normally be asked to confirm
• There is no easy way to undo the changes

9
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

10
ALTERing Columns

• To add or remove columns use
• ALTER TABLE lttablegt
• ADD COLUMN ltcolgt
• ALTER TABLE lttablegt
• DROP COLUMN ltnamegt

• Examples
• ALTER TABLE Student
• ADD COLUMN
• Degree VARCHAR(50)
• ALTER TABLE Student
• DROP COLUMN Degree

11
ALTERing Constraints

• To add or remove constraints use
• ALTER TABLE lttablegt
• ADD CONSTRAINT ltdefinitiongt
• ALTER TABLE lttablegt
• DROP CONSTRAINT ltnamegt

12
ALTERing Constraints

• Examples
• ALTER TABLE Module
• ADD CONSTRAINT ck UNIQUE (title)
• ALTER TABLE Module
• DROP CONSTRAINT ck

13
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

14
INSERT

• INSERT INTO
• lttablegt
• (col1, col2, )
• VALUES
• (val1, val2, )

• The number of columns and values must be the same
• If you are adding a value to every column, you
  dont have to list them
• SQL doesnt require that all rows are different
  (unless a constraint says so)

15
INSERT example
INSERT into Muppets VALUES (Sven-Goran
Erikkson, White, Human)

• Note that you have to remember the correct order
  for the attributes if you dont specify them, to
  pair up with the values you are putting in. This
  can get very confusing.

16
INSERT illustration
17
Duplicates?

• Remember that a relation (table) is just a set.
• Every table must have at least one key, so each
  row can be identified.
• However SQL, not being a perfect implementation,
  allows duplicates and tables with no primary keys.

18
Duplicates. Just say no.

• Remember that a relation (table) is just a set.
• A set such as 1, 2, 3, 3 is illegal an
  element appears twice. This makes no sense in set
  theory.
• Just the same for databases. No row may appear
  twice you would simply be recording the same
  fact again, wasting space.
• Never, ever create duplicate rows.

19
UPDATE

• UPDATE lttablegt
• SET col1 val1
• ,col2 val2
• WHERE
• ltconditiongt

• 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

20
UPDATE illustration
21
UPDATE dangers

• An update can change one row, or many rows
  depending on how you specify the statement.
• The following will change every tuple (row)
• UPDATE Muppets SET colour Brown
• So make sure you specify your WHERE statement
• UPDATE Muppets SET colour Brown
• WHERE nameFozzy

22
DELETE

• Removes all rows which satisfy the condition
• DELETE FROM
• lttablegt
• WHERE
• ltconditiongt

• If no condition is given then ALL rows are
  deleted - BE CAREFUL
• Some versions of SQL also have TRUNCATE TABLE ltTgt
  which is like DELETE FROM ltTgt but it is quicker
  as it doesnt record its actions

23
DELETE illustration
24
Being Careful Again

• Before running
• DELETE FROM Student
• WHERE Year 3
• run
• SELECT FROM Student
• WHERE Year 3

• 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

25
A table or a table variable?

• When you create a table you are actually creating
  a variable, with a name.
• This is what Codd called a time-dependent
  relation.
• Updates, Inserts and Deletes replace the value in
  that variable.

26
Back to Set Theory

• What are INSERT, UPDATE and DELETE actually
  doing?
• They replace the whole of the previous table with
  a whole new table.
• Or really they replace one relation value with
  another.
• A table is a relation variable (a relvar) and any
  change to it replaces the whole thing.

27
Replacing Relation Values
Muppets1 (Kermit, Green, Frog) (Miss
Piggy, Pink, Pig) (Fozzy, Orange, Bear)
INSERT into Muppets VALUES (Robin, Green, Frog)
28
Update Confusion

• It is easy to see with inserts and deletes that
  they are replacing the old relation (table) with
  a larger or smaller one.
• However it is vital to realise with updates the
  system is in theory
• - Memorizing some row in the old table
• - Altering that memorized row
• - Deleting the original row from the old table
• - Creating a new table with the new altered row
  added.

29
Sequences

• Often we want each tuple to have a unique number
• These are useful as primary keys
• Using integers to reference rows is more
  efficient
• We would like the DBMS to do this

• In most versions of SQL we can use
  autoincrementing fields to do this
• Details differ between versions
• Usually the first entry is assigned 1, the next
  2, and so on.

30
AUTO_INCREMENT

• Auto_increment keeps track of an id number,
  adding one for each new statement you add to your
  database.
• CREATE TABLE Students (
• student_ID INT NOT NULL AUTO_INCREMENT,
• name VARCHAR(32) NOT NULL,
• age INT NOT NULL )
• Then to add a statement
• INSERT INTO Students VALUES (NULL, Gonzo, 25 )
  

31
Sequences

• In Oracle we can also use a Sequence
• Autoincrements just go up in integers.
• A sequence is a user-defined source of numbers.
• We can declare several sequences, giving each a
  name, a start point, and a step size.
• We can then generate unique numbers by asking for
  the next element from a sequence.

32
Sequences in Oracle

• To declare a sequence
• CREATE SEQUENCE ltnamegt
• START WITH ltvaluegt
• INCREMENT BY ltvaluegt
• If no START WITH or INCREMENT BY values are given
  they default to 1
• To get the next value from a sequence
• ltsequence namegt.nextVal

33
Sequence Example

• Creating a sequence
• CREATE SEQUENCE mySeq START WITH 1
• Using a sequence
• SELECT mySeq.nextVal FROM DUAL
• INSERT INTO Student
• (stuID, stuName, stuAddress)
• VALUES
• (mySeq.nextVal, 'Steve Mills',
• '13 Elm Street')

34
This Lecture in Exams
35
This Lecture in Exams
Add 2.50 to the price of all CDs that cost more
than 10.00. (2 marks) Add a new column, Genre,
to the CD table. This column should hold a string
of up to 100 characters, and if no genre is
provided then it should default to the value
Unknown. (3 marks) Add a track titled Runnin
by the artist Fat Freddys Drop which is 12
minutes and 27 second long to the CD titled
Compilation. For this part only, you may assume
that the tables contain exactly the information
shown above. (3 marks)
36
Next Lecture

• SQL SELECT
• WHERE clauses
• SELECT from multiple tables
• The genius of JOINS
• For more information
• Connolly and Begg Chapter 5