Table Creation and Management

Identify the table name and structure

Commands used to create or modify database tables are called data definition language(DDL commands

A Database Object is a defined self contained structure also called database tables

Creating Tables	Description
CREATE TABLE	Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the SQL*PLUS command DESCRIBE.
Modifying Tables
ALTER TABLE . . . ADD	Adds a column to a table.
ALTER TABLE . . . MODIFY	Changes a column size, datatype, or default value.
ALTER TABLE . . . DROP COLUMN	Deletes one column from a table
ALTER TABLE . . . SET UNUSED or SET UNUSED COLUMN	Marks a column for deletion at a latertime.

Creating a table based on another table (subquery)

CREATE TABLE cust_mkt AS (SELECT customers, city, state, zip FROM customers);

SELECT firstname || lastname FROM customers;
this will display the info but last and first names will be together eg. firstlast
to add a space between the names :
SELECT firstname || ' , ' | || lastname as "Customer Name" FROM customers;
this will add the , between the names and give it an alias heading "Customer name"

Adding Primary Key Constraints

This makes customer# the primary key  the constraint name is used so you can easily 
identify an error if you try to enter a duplicate customer
If you don’t assign constraint names, the error message displays the
system-generated constraint name, which isn’t as helpful
ALTER TABLE customers ADD CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#);

Foreign Key Constraint

This command instructs Oracle 11g to add a FOREIGN KEY constraint on the Customer# column of the ORDERS table. The name chosen for the constraint is orders_customer#_fk. This constraint makes sure an entry for the Customer# column of the ORDERS table matches a value stored in the Customer# column of the CUSTOMERS table. When the command executes, a message indicates the table was altered successfully

ALTER TABLE orders ADD CONSTRAINT orders_customer#_fk FOREIGN KEY(customer#) REFERENCE customers (customer#)

Unique Constraints

ALTER TABLE tablename ADD CONSTRAINT constraintname UNIQUE(columnname);

Check Constraint

ALTER TABLE orders ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);

Not Null Constraint

ALTER TABLE orders MODIFY (customer# CONSTRAINT orders_customer#_nn NOT NULL);

Default Values

CREATE TABLE acctmgr(
id CHAR(4),
am_date DATE DEFAULT SYSDATE,
am_count NUMBER(7,2) DEFAULT 0,

);

CHECK Constraint

ALTER TABLE orders(
ADD CONSTRAINT orders_shipdate_ck CHECK (orderdate <= shipdate);