CPS 430/542 Lecture notes: Defining and populating a relation schema in SQL

---

Overview

---

• various datatypes
• setting primary key
• adding and dropping attributes
• default values (not null)
• adding, dropping, and modifying tuples

---

Online guides

---

• using Oracle 8 on the CPS Suns
• notes on Oracle from the Stanford University DB group
• help on Oracle 8 from the University of Maryland Baltimore County

---

Basic tasks

---

• creating a relation:

  CREATE TABLE Movies (title CHAR (22),
         year INTEGER, length INTEGER,
         stdio CHAR (32));
  

• inserting a tuple into a relation: INSERT INTO Movies;
• dropping a relation: DROP TABLE Movies;
  • need not be empty to drop
  • if not empty, all tuples lost
  • use with caution
  • maintain backups of your database

---

References

[FCDB]

J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.