SQL Basics

• There are two main ways to create tables
  1. Use a tool supplied with the DBMS
  2. Run SQL statements

• Table-creation syntax can differ between DBMSs

-- MySQL
CREATE TABLE ProductsBak
(
     prod_id          CHAR(10)        NOT NULL,
     vend_id          CHAR(10)        NOT NULL,
     prod_name        CHAR(254)       NOT NULL,
     prod_price       DECIMAL(8,2)    NOT NULL,
     prod_desc        VARCHAR(1000)   NULL
);

• A new table's name must be unique within its naming scope
• Each column either allows NULL or is defined as NOT NULL
• For NOT NULL columns without defaults, values must be supplied in INSERT
• In some DBMSs, NULL can be omitted from a column definition because allowing NULL is the default
• A primary key cannot be NULL

• Default values can be defined when creating a table
• Use the DEFAULT keyword to specify a default value

-- MySQL
CREATE TABLE OrderItemsBak
(
     order_num        INTEGER        NOT NULL,
     order_item       INTEGER        NOT NULL,
     prod_id          CHAR(10)       NOT NULL,
     quantity         INTEGER        NOT NULL    DEFAULT 1,
     item_price       DECIMAL(8,2)   NOT NULL
);

• The current date can also be used as a default value
• The syntax varies between DBMSs; see the table below

DBMS	Function / Variable
Oracle	SYSDATE
MySQL	CURRENT_DATE() / NOW()
SQL SERVER	GETDATE()
PostgreSQL	CURRENT_DATE
DB2	CURRENT_DATE
SQLite	date('now')

-- MySQL
CREATE TABLE OrdersBak
(
     order_num        INTEGER        NOT NULL,
     order_date       DATETIME       NOT NULL      DEFAULT NOW(),
     cust_id          INTEGER        NOT NULL
);

• Use ALTER TABLE to change a table's definition
  • Avoid changing populated tables without careful preparation
  • All the listed DBMSs allow columns to be added
  • Support for modifying or dropping columns varies between DBMSs
  • Many DBMSs allow column names to be changed
• Changing a table is not always straightforward or consistent across systems
• Use ALTER TABLE carefully and make a backup first

-- MySQL
ALTER TABLE ProductsBak
ADD prod_weight CHAR(20);	

• DBMSs use different methods to rename tables

DBMS	Approach
Oracle / MySQL / MariaDB / DB2 / PostgreSQL	RENAME statement
SQL Server	sp_rename
SQLite	ALTER TABLE

-- MySQL
RENAME TABLE ProductsBak TO ProductsBackup;	
-- Oracle: RENAME ProductsBak TO ProductsBackup;

-- Oracle / MySQL / SQL Server
DROP TABLE ProductsBackup;
DROP TABLE OrderItemsBak;
DROP TABLE OrdersBak;

SQL does not provide a general-purpose Undo command

69. Add a "website" column to custCopy and enter a URL in one row

This exercise assumes custCopy still contains the data from Chapter 15, exercise 66. The TRUNCATE example in Chapter 16 is theoretical and is not run automatically. If you ran it yourself, drop the empty table and recreate custCopy using Chapter 15, exercise 66.

70. Drop custCopy