Database Tables


  • All data in a relational database is structured in tables:
    • A table has a name, a fixed sequence of columns, and a varying set of rows.
    • A column has a name and a data type.
    • A row is an unnamed sequence of values. Each value corresponds to a column and belongs to the column’s data type.
    • A cell is a single column of a single row.
  • table must have at least one column but any number of rows
  • A table without rows is called an empty table
  • a table is also called a relation

Rules Governing Tables

  1. Exactly one value per cell
    • A cell may not contain multiple values
    • Unknown data is represented with a special NULL value
  2. No duplicate column names
    • Duplicate column names are allowed in different tables, but not in the same table
  3. No duplicate rows
    • No two rows may have identical values in all columns
  4. No row order
    • Rows are not ordered
    • organization of rows on a storage device never affects query results
    • called data independence
      • allows database administrators to improve query performance without affecting results

Database Table Statements

Creating Tables

The CREATE TABLE statement creates a new table by specifying the table name, column names, and column data types.

  • fails if the named table already exists
    • to avoid failure, use the optional clause IF NOT EXISTS

Example

CREATE TABLE Employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
mi char(1),
lname varchar(30)
);
  • creates a table called Employees with an ID field called “ID-NUM” containing a value that counts up and provides a unique number for each record entered
  • creates a first name, last name, and middle initial fields with varying lengths
  • When creating a table using a script, need to include:
    • at least one field
    • the field data type

Deleting Tables

The DROP TABLE statement deletes a table, along with all the table’s rows, from a database.

  • fails if the named table does not exist
    • to avoid failure, use the optional clause IF EXISTS
  • cannot be undone

Syntax: CREATE and DROP

CREATE TABLE [IF NOT EXISTS] TableName (

Column1 DATA_TYPE,
Column2 DATA_TYPE,
…
ColumnN DATA_TYPE
);

DROP TABLE [IF EXISTS] TableName;

TRANSPOSE

Transpose is an operation on a table in which rows become columns, and columns become rows.

  • important operation for mathematical matrices, but is not commonly applied to tables

TRUNCATE Command

TRUNCATE is command used to delete all records in a table.

  • is a “delete all records” command
    • no filters in place
  • often used for testing and clearing a table of all data
  • cannot be undone
TRUNCATE TABLE ProductDetails;

Altering Tables

The ALTER TABLE statement adds, deletes, or modifies columns on an existing table.

  • specifies the table name followed by a clause that indicates what should be altered
  • alters the objects within the database, and the properties of those different objects
ALTER TABLE clauseDescriptionSyntax
ADDAdds a columnALTER TABLE TableName
ADD ColumnName DataType;
CHANGEModifies a columnALTER TABLE TableName
CHANGE CurrentColumnName NewColumnName NewDataType;
DROPDeletes a columnALTER TABLE TableName
DROP ColumnName;
ALTER TABLE Employees
Add Jtitle varchar(75)

Renaming Objects

Use the RENAME clause alongside the ALTER statement.

  • exact syntax you use might vary depending on the system you are working with
ALTER TABLE Employees
RENAME COLUMN Jtitle TO JobTitle;