Lecture 9 : SQL-Based Database Table Creation

Lecture 9: SQL-Based Database Table Creation November 14th

Reminder: Querying Tables using SQL • Retrieving data • Ordering query results • Limiting query results • Grouping query results

Write the SQL Code for this Query

Write the SQL Code for this Query SELECT id, name, owner_id, birth_date, gender FROM horse WHERE gender = ‘M’ ORDER BY birth_date DESC;

Write the SQL Code for this query SELECT id, name, owner_id FROM horse WHERE owner_id IN (‘00011’, ‘0850’, ‘02001’, ‘02003’);

Naming Tables • When naming tables: • Use a descriptive name • Limit table names to 30 characters or less • Use only letters, numbers and the underscore • Avoid duplicating the names of other database objects

Data Types • When creating a table, you specify each column that will be included in the table • Each column has a name and defined data type • The database uses the data type to determine how to store the data for each column

Column Data Type Categories • Different databases provide different column data types. However, all databases contain data types that fit into the following common categories: • Character (TEXT in Access) • Whole number (INTEGER in Access) • Decimal (REAL in Access) • Date and time (DATETIME)

Column Data Type Categories + - / * INTEGER BYTE TEXT + - / *

Constraints • Constraints are clauses that you include in the creation of a table to enforce column-level rules

Constraints • NOT NULL • Requires a value in a column • UNIQUE • Ensures that column values are unique • CHECK • Imposes a condition on a column • PRIMARY KEY • Determines a unique value for the identification of individual rows • FOREIGN KEY • Ensures data integrity between 2 tables

Examples of Constraints • NOT NULL • In an orders table, every order must have an order_date • UNIQUE • Every order_no should be unique • CHECK • Check order_total column to ensure that no order less than 10 euro is placed in the orders table • PRIMARY KEY • Order_no could be used as a primary key

How do I create a table in Access?

Table Statements • CREATE TABLE • ALTER TABLE: ADD • ALTER TABLE: MODIFY • ALTER TABLE: DROP • DROP TABLE

CREATE TABLE • This is a DDL statement that results in immediate change to the database CREATE TABLE table_name (column1_name datatype, column2_name datatype);

CREATE TABLE Create a table named status with 3 columns, with one specified as a primary key .. • Create the table: CREATE TABLE status • 1st column is order_id, with datatype INTEGER and NOT NULL constraint: CREATE TABLE status (order_id integer NOT NULL …. • 2nd column is order_status, with datatype TEXT and NOT NULL constraint: CREATE TABLE status (order_id integer NOT NULL, order_status TEXT ..

CREATE TABLE • 3rd column is ship_date, with datatype DATETIME : CREATE TABLE status (order_id integer NOT NULL, order_status TEXT, ship_date DATETIME • Order_id is the PRIMARY KEY : CREATE TABLE status (order_idinteger NOT NULL, order_status TEXT, ship_date DATETIME, PRIMARY KEY(order_id));

CREATE THIS TABLE Primary Key CREATE TABLE personal (name TEXT, age INTEGER, PRIMARY KEY(name));

How do I add a column to an existing table in Access? In Design View, add a new column attribute

In SQL, ALTER TABLE: ADD • After using a database table you may find that it would be more useful if a column was either added to a table or deleted from the table • Database tables can be modified using ALTER TABLE • A column can be added using the ALTER STATEMENT with the ADD clause ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE: ADD Add a column to the status table • Alter the table: ALTER TABLE status • Add a column order_date and assign a datatype DATETIME: ALTER TABLE status ADD order_date DATETIME;

How is the new column added to this table? ALTER TABLE personal ADD address TEXT;

How do I modify a column in an existing table in Access? In Design View, alter a column characteristic

In SQL, ALTER TABLE: MODIFY • You can modify many characteristics of the columns in a table ALTER TABLE table_name MODIFY column_name datatype;

ALTER TABLE: MODIFY Modify ship_date column so that it does not allow null values • Alter the table: ALTER TABLE status • Modify a column ship_date and assign a constraint NOT NULL: ALTER TABLE status MODIFY ship_date NOT NULL;

How is the AGE modified from INTEGER to TEXT? ALTER TABLE personal MODIFY age TEXT;

ALTER TABLE: DELETE • Deletion of structural elements from a table is done using the ALTER TABLE statement with the DROP clause ALTER TABLE table_name DROP CHECK; ALTER TABLE table_name DROP UNIQUE(column_name);

ALTER TABLE: DELETE Delete the PRIMARY KEY constraint from the status table • Alter the table: ALTER TABLE status • Delete the PRIMARY KEY constraint: ALTER TABLE status DROP PRIMARY KEY;

How do I delete a table in Access?

DROP TABLE • You can drop a table and all of its data by using the DROP TABLE statement DROP TABLE table_name;

DROP TABLE Drop the status table from the database: • Delete the table: DROP TABLE status;

Table Statements in Today’s Lecture • CREATE TABLE • ALTER TABLE: ADD • ALTER TABLE: MODIFY • ALTER TABLE: DROP • DROP TABLE

Lecture 9 : SQL-Based Database Table Creation