The Language of SQL- P42:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | Creating Tables 191 The third column in the table is named ColumnThree and defined as a VARCHAR datatype with a length of 25. This column will allow NULL values. The fourth column in the table is named ColumnFour and defined as a FLOAT datatype and will allow NULL values. It will be given a default value of 10. Here is the CREATE TABLE statement in Microsoft SQL Server that accomplishes this CREATE TABLE MyTable ColumnOne INT IDENTITY 1 1 PRIMARY KEY NOT NULL ColumnTwo INT NOT NULL REFERENCES RelatedTable FirstColumn ColumnThree VARCHAR 25 NULL ColumnFour FLOAT NULL DEFAULT 10 DATABASE DIFFERENCES MySQL and Oracle The same create table statement in MySQL looks like CREATE TABLE MyTable ColumnOne INT AUTO_INCREMENT PRIMARY KEY NOT NULL ColumnTwo INT NOT NULL ColumnThree VARCHAR 25 NULL ColumnFour FLOAT NULL DEFAULT 10 CONSTRAINT FOREIGN KEY ColumnTwo REFERENCES RelatedTable FirstColumn The same statement in Oracle is CREATE TABLE MyTable ColumnOne INT PRIMARY KEY NOT NULL ColumnTwo INT NOT NULL ColumnThree VARCHAR2 25 NULL ColumnFour FLOAT DEFAULT 10 NULL CONSTRAINT ForeignKey FOREIGN KEY ColumnTwo REFERENCES RelatedTable FirstColumn As previously mentioned Oracle doesn t allow for auto-increment columns. After a table is created an ALTER TABLE statement can be used to modify specific attributes of the table. Due to its complexity and to the vast differences 192 Chapter 18 Maintaining Tables between databases the syntax for the ALTER TABLE won t be covered in this book. As one example if you wanted to modify MyTable to eliminate the Column-Three column from the table you would need to issue this ALTER TABLE statement ALTER TABLE MyTable DROP COLUMN ColumnThree The syntax for deleting a table is simple. To delete MyTable issue this statement DROP TABLE MyTable Creating Indexes SQL provides a CREATE INDEX statement to create indexes after the table is created. You can also use the ALTER TABLE statement to add or modify indexes. If you would like to add a new index on
