SQL REFERENCE
ALTER TABLE
 CREATE INDEX
CREATE TABLE
CREATE VIEW
 Data Types
DELETE Rows
DROP INDEX
DROP TABLE

ALTER TABLE
Use the ALTER TABLE command to change a table's structure. Type the ALTER TABLE command, followed by the table name, and then the alteration to perform.

ClauseDescriptionRequired
ALTER TABLE table name Indicates the name of the table to be altered. Yes
alteration Indicates the type of alteration to be performed. Yes

The following command alters the Customer table by adding a new column named CustType:

  ALTER TABLE Customer
  ADD CustType CHAR(1)
  ;

The following command alters the Customer table by changing the length of the CustomerName column:

  ALTER TABLE Customer
  CHANGE COLUMN CustomerName TO CHAR(50)
  ;

The following command alters the Part table by deleting the Warehouse column:

  ALTER TABLE Part
  DELETE Warehouse
  ;

Back to Top

CREATE INDEX
Use the CREATE INDEX command to create an index for a table.

ClauseDescriptionRequired
CREATE INDEX index name Indicates the name of the index. Yes
ON table name Indicates the table for which the index is to be created. Yes
column list Indicates the columns or columns on which the index is to be based. Yes

The following CREATE INDEX command creates an index named RepBal for the Customer table on the combination of the RepNum and Balance columns:

  CREATE INDEX RepBal
  ON Customer (RepNum, Balance)
  ;

Back to Top

CREATE TABLE
Use the CREATE TABLE command to define the structure of a new table.

ClauseDescriptionRequired
CREATE TABLE table name Indicates the name of the table to be created. Yes
(Column and data type list) Indicates the columns the comprise the table along with their corresponding data types (see Data Types section). Yes

The following CREATE TABLE examples create the "Rep" table and the "EMPLOYEE" table and its associated columns and data types.

CREATE TABLE Rep
      (RepNum     CHAR(2),
       LastName   CHAR(15),
       FirstName  CHAR(15),
       Street     CHAR(15),
       City       CHAR(15),
       State      CHAR(2),
       Zip        CHAR(5),
       Commission DECIMAL(7,2),
       Rate       DECIMAL(3,2) )
      ;

CREATE TABLE EMPLOYEE
      (EMPNO       CHAR(6)         NOT NULL,
       FIRSTNME    VARCHAR(12)     NOT NULL,
       MIDINIT     CHAR(1)         NOT NULL,
       LASTNAME    VARCHAR(15)     NOT NULL,
       WORKDEPT    CHAR(3)                 ,
       PHONENO     CHAR(4)                 ,
       HIREDATE    DATE                    ,
       JOB         CHAR(8)                 ,
       EDLEVEL     SMALLINT        NOT NULL,
       SEX         CHAR(1)                 ,
       BIRTHDATE   DATE                    ,
       SALARY      DECIMAL(9,2)            ,
       BONUS       DECIMAL(9,2)            ,
       COMM        DECIMAL(9,2)         
       PRIMARY KEY (EMPNO))
      ;

Note: Access doesn't have a DECIMAL data type. To create numbers with decimals you must use either the CURRENCY or NUMBER data type.

Back to Top

CREATE VIEW
Use the CREATE VIEW command to create a view.

ClauseDescriptionRequired
CREATE VIEW view name AS Indicates the name of the view to be created. Yes
query Indicates the defining query for the view. Yes

The following CREATE VIEW command creates a view named Housewares, which consists of the part number, description, on hand, and price for all rows in the Part table on which the class is HW:

  CREATE VIEW Housewares AS
  SELECT PartNum, Description, OnHand, Price
  FROM Part
  WHERE Class = HW
  ;

Back to Top

Data Types
These are the data types that you can use in a CREATE TABLE command.

Data TypeDescription
CHAR (n) Stores a cahracter string n characters long. You use the CHAR type for fields that contain letters and other special characters, and for fields that contain numbers that will not be used for arithmetic.
DATE Stores data in the form DD-MON-YYYY or MM/DD/YYYY. Note: The specific format in which the dates are stored varies from one SQL implementation to another.
DECIMAL (p,q) Stores a decimal number p digits long with q of these digits being decimal places. For example, DECIMAL(5,2) represents a number with three places to the left and two places to the right of the decimal. You can use the contents of DECIMAL fields for arithmetic. Note: In some SQL implementations, the decimal point counts as one of the places, and in others it does not. Likewise, in some implementations a minus sign counts as one of the places, but in others it does not.
INTEGER Stores integers, which are numbers without a decimal part. The valid data range is -2147483648 to 2147483647. You can use the contents of the INTEGER field for arithmetic.
SMALLINT Stores integers, but uses less space than the INTEGER data type. The valid data range is -32768 to 32768. SMALLINT is a better choice than INTEGER when you are certain that the field will store numbers in the indicated range. You can use the contents of the SMALLINT field for arithmetic.

Back to Top

DELETE Rows
Use the DELETE command to delete one or more rows from a table.

ClauseDescriptionRequired
DELETE FROM table name Indicates the table from which the row or rows are to be deleted. Yes
WHERE condition Indicates a condition. Those rows for which the condition is true will be retrieved and deleted. NO
If you omit the WHERE clasue, all rows will be deleted.

The following DELETE command deletes any row from the OrderLine table on which the part number is BV06.

  DELETE
  FROM OrderLine
  WHERE PartNum='BV06'
  ;

Back to Top

DROP INDEX
Use the DROP INDEX command to delete an index.

ClauseDescriptionRequired
DROP INDEX index name Indicates the name of the index to be dropped. Yes

The following DROP INDEX command deletes the index named RepBal.

  DROP INDEX RepBal
  ;

Back to Top

DROP TABLE
Use the DROP TABLE command to delete a table.

ClauseDescriptionRequired
DROP TABLE table name Indicates the name of the table to be dropped. Yes

The following DROP TABLE command deletes the table named SmallCust.

  DROP TABLE SmallCust
  ;

Back to Top