Database Reference
In-Depth Information
The
SQL CREATE TABLE statement
is used to construct tables, define columns and column
constraints, and create relationships. Most DBMS products provide graphical tools for perform-
ing these tasks, and you may be wondering why you need to learn SQL to perform the same
work. There are four reasons. First, creating tables and relationships with SQL is quicker than
with graphical tools. Once you know how to use the SQL CREATE TABLE statement, you will be
able to construct tables faster and more easily than by fussing around with buttons and graphi-
cal gimmickry. Second, some applications, particularly those for reporting, querying, and data
mining, require you to create the same table repeatedly. You can do this efficiently if you create
an SQL script text file with the necessary SQL CREATE TABLE statements. You then just execute
the SQL script when you need to re-create a table. Third, some applications require you to create
temporary tables during application work. The discussion of RFM reports in Appendix J shows
one such application. The only way to create tables from program code is to use SQL. Finally, SQL
DDL is standardized and DBMS independent. With the exception of some data types, the same
CREATE TABLE statement will work with SQL Server, Oracle Database, DB2, or MySQL.
Creating the View Ridge Gallery Database
Of course, before you can create any tables, you have to create the database. The SQL-92 and sub-
sequent standards include an SQL statement for creating databases, but it is seldom used. Instead,
most developers use special commands or graphical tools for creating a database. These tech-
niques are DBMS specific, and we describe them in context in Chapters 10A (SQL Server 2012),
10B (Oracle Database 11
g
Release 2), 10C (MySQL 5.6), and Appendix A (Microsoft Access 2010).
At this point, we highly recommend that you read the section on creating a new database
in the DBMS product you are using and use the appropriate steps to create a new database for
the View Ridge Gallery named
VRG
. For illustrative purposes, we will use SQL Server 2012 in this
chapter, and our SQL code will be the correct code for SQL Server 2012. The correct SQL state-
ments for other DBMS products will be similar, but they will vary slightly. The correct SQL state-
ments for Oracle Database 11
g
Release 2 and MySQL 5.6 can be found in Chapters 10B and 10C,
respectively. Figure 7-3 shows the VRG database is the SQL Server 2012 Management Studio.
Using SQL Scripts
Each DBMS product has a GUI utility program that is used to create, edit, and store SQL script
files. An
SQL script file
or
SQL script
is a separately stored plain text file, and it usually uses a
Figure 7-3
the VrG Database in SQL
Server 2012 Management
Studio
The
Object Explorer
The
VRG database
object
The VRG database
object folders—when
database object such
as tables are created,
they will be visible
in these folders
Search WWH ::
Custom Search