C170 Data Management - Applications SQL Syntax

CREATE TABLE syntax DDL CREATE TABLE tableName ( colName dataType constraint INDEX indexName (colName) PRIMARY KEY (colName, colName) or colName dataType PRIMARY KEY FOREIGN KEY (colName, colName) REFERENCES tableName (colName) ); CREATE INDEX syntax DDL CREATE [UNIQUE] INDEX indexName ON tableName (colName); UNIQUE should only be used on a column that should not have duplicate values. DROP INDEX syntax DDL DROP INDEX indexName; DROP INDEX 'PRIMARY' ON tableName; drops PK ALTER INDEX DDL ALTER INDEX indexName ON tableName (colName, colName); INSERT command syntax DML INSERT INTO tableName VALUES (val1, val2, val3...); For inserting new rows into existing table. UPDATE syntax DML UPDATE tableName SET colName = expression {ex. PRICE = 4.00} WHERE condition; {ex. S_NUMBER = A123} For modifying data in a table DELETE syntax DML DELETE FROM tableName WHERE condition; {ex. PRICE 3.00} For deleting rows from a table ALTER TABLE syntax DDL ALTER TABLE tableName ADD (colName, dataType); or ALTER TABLE tableName ADD INDEX indexName (colName); or ALTER TABLE tableName ADD PRIMARY KEY (colName); or ALTER TABLE tableName ADD FOREIGN KEY (colName) REFERENCES tableName; ALTER TABLE tableName DROP COLUMN colName; or DROP CONSTRAINT constraintName; or DROP PRIMARY KEY; or DROP INDEX indexName; ALTER TABLE tableName MODIFY (colName dataType); For changing a columns data characteristics. To copy a table and populate it from existing table DDL CREATE TABLE newTableName (create table parameters); then INSERT INTO newTableName (newColList) SELECT existingColList FROM existingTable Name; or CREATE TABLE newTableName AS SELECT oldCol1 AS newCol1, oldCol2 AS newCol2 FROM existingTableName; or CREATE TABLE....LIKE followed by INSERT INTO...SELECT Creates DB if it does not already exist DDL CREATE DATABASE IF NOT EXISTS dbName CREATE VIEW DDL CREATE VIEW viewName AS SELECT query FROM tableName; Changing a VIEW DDL ALTER VIEW Subquery DML SELECT colName FROM tableName WHERE colName = (SELECT colName FROM tableName WHERE condition);