SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 282

file name extension of *.sql . An SQL script can be opened and run as an SQL command (or set
of commands). SQL scripts are used to create and populate databases, and to store a query or
set of queries. They are also used to store the SQL statements to create SQL elements that we
will discuss later in this chapter: SQL view, and SQL/PSM functions, triggers and stored pro-
cedures. We recommend that you use SQL scripts to edit and store any work you do in SQL in
this chapter (as well as any work SQL work in general)
The GUI utilities that we will use to create SQL scripts are:
● Microsoft SQL Server 2012 Management Studio for use with Microsoft SQL Server
2012 (See Chapter 10A for a discussion of Microsoft SQL Server 2012 Management
Studio).
● Oracle SQL Developer for use with Oracle Database 11 g Release 2 (See Chapter 10B
for a discussion of Oracle SQL Developer).
● Oracle MySQL Workbench for use with Oracle MySQL 5.6 (See Chapter 10C for a
discussion of Oracle MySQL Workbench).
When the Microsoft SQL Server 2012 Management Studio is installed, a new folder named
SQL Server Management Studio is created in your My Documents folder. We recommend that
you create a subfolder named Projects , and use the Projects folder as the default location
for SQL script files. Further, for each database, create a new folder in the Projects folder. For
example, we will create a folder named View-Ridge-Gallery-Database to store the script files
associated with the View Ridge Gallery database.
By default, Oracle SQL Developer stores *.sql files in an obscure location within its
own application files. We recommend that you create a subfolder in your My Documents
folder named Oracle Workspace and then create a subfolder for each database in the Oracle
Workspace folder. For example, we will create a folder named View-Ridge-Gallery-Database to
store the script files associated with the View Ridge Gallery database.
By default, MySQL Workbench stores files in the user's My Documents folder. We recom-
mend that you create a subfolder in your My Documents folder named MySQL Workspace and
then create subfolders labeled EER Models and Schemas . Within each of these subfolders, create
a sub-subfolder for each MySQL database. For example, we will create a folder named View-
Ridge-Gallery-Database to store the script files associated with the View Ridge Gallery database.
Using the SQL CREATE TABLE Statement
The basic format of the SQL CREATE TABLE statement is:
CREATE TABLE NewTableName (
three-part column definition,
three-part column definition,
. . .
optional table constraints
. . .
);
The parts of the three-part column definition are the column name, the column data type, and,
optionally, a constraint on column values. Thus, we can restate the CREATE TABLE format as:
CREATE TABLE NewTableName (
ColumnName DataType OptionalConstraint,
ColumnName DataType OptionalConstraint,
. . .
Optional table constraint
. . .
);