Creating Databases and Tables in SQL

Creating the database

Are you ready to begin creating databases and tables with the Structured Query Language? In this article, we explore the process of creating tables manually with the CREATE DATABASE and CREATE TABLE commands. If you're new to SQL, you may wish to review some SQL basics first.

Business Requirements

Before we sit down at the keyboard, we need to ensure that we have a solid understanding of the customer's requirements. What's the best way to obtain this insight? Talking to the customer, of course! After sitting down with XYZ's Human Resources Director, we've learned that they are a widget sales company and are primarily interested in tracking information on their sales personnel.

XYZ Corporation divides its sales force into eastern and western regions, each of which is divided into many territories covered by individual sales reps. The HR department would like to track the territory covered by each employee as well as each employee's salary information and supervisory structure. To meet these requirements, we've designed a database consisting of three tables, shown in the Entity-Relationship diagram on this page.

Choosing a Database Platform

We've decided to use a database management system (or DBMS) that is built upon the Structured Query Language (SQL). Therefore, all of our database and table creation commands should be written with standard ANSI SQL in mind.

As an added benefit, using ANSI-compliant SQL will ensure that these commands will work on any DBMS that supports the SQL standard, including Oracle and Microsoft SQL Server. If you haven't selected a platform for your database yet, Database Software Options walks you through the selection process.

Creating the Database

Our first step is to create the database itself. Many database management systems offer a series of options to customize database parameters at this step, but our database only permits the simple creation of a database. As with all of our commands, you may wish to consult the documentation for your DBMS to determine if any advanced parameters supported by your specific system meet your needs. Let's use the CREATE DATABASE command to set up our database:

CREATE DATABASE personnel

Take special note of the capitalization used in the example above. It's common practice among SQL programmers to use all capital letters for SQL keywords such as "CREATE" and "DATABASE" while using all lowercase letters for user-defined names like the "personnel" database name. These conventions provide for easy readability.

Now that we've designed and created our database, we're ready to begin creating the three tables used to store XYZ Corporation's personnel data.

Creating Our First Table

Our first table consists of personal data for each employee of our company. We need to include each employee's name, salary, ID, and manager. It's good design practice to separate the last and first names into separate fields to simplify data searching and sorting in the future. Also, we'll keep track of each employee's manager by inserting a reference to the manager's employee ID in each employee record. Let's first take a look at the desired employee table.

The ReportsTo attribute stores the manager ID for each employee. From the sample records shown, we can determine that Sue Scampi is the manager of both Tom Kendall and John Smith. However, there is no information in the database on Sue's manager, as indicated by the NULL entry in her row.

Now we can use SQL to create the table in our personnel database. Before we do so, let's ensure that we are in the correct database by issuing a USE command:

USE personnel;

Alternatively, the "DATABASE personnel;" command would perform the same function. Now we can take a look at the SQL command used to create our employees' table:

CREATE TABLE employees
(employeeid INTEGER NOT NULL,
lastname VARCHAR(25) NOT NULL,
firstname VARCHAR(25) NOT NULL,
reportsto INTEGER NULL);

As with the above example, note that programming convention dictates that we use all capital letters for SQL keywords and lowercase letters for user-named columns and tables. The command above may seem confusing at first, but there's actually a simple structure behind it. Here's a generalized view that might clear things up a bit:

CREATE TABLE table_name
(attribute_name datatype options,
...,
attribute_name datatype options);

Attributes and Data Types

In the previous example, the table name is employees and we include four attributes: employeeid, lastname, firstname, and reportsto. The datatype indicates the type of information we wish to store in each field. The employee ID is a simple integer number, so we'll use the INTEGER datatype for both the employeeid field and the reportsto field. The employee names will be character strings of variable length and we don't expect any employee to have a first or last name longer than 25 characters. Therefore, we'll use the VARCHAR(25) type for these fields.

NULL Values

We can also specify either NULL or NOT NULL in the options field of the CREATE statement. This simply tells the database whether NULL (or empty) values are allowed for that attribute when adding rows to the database. In our example, the HR department requires that an employee ID and complete name be stored for each employee. However, not every employee has a manager (the CEO reports to nobody!) so we allow NULL entries in that field. Note that NULL is the default value and omitting this option will implicitly allow NULL values for an attribute.

Building the Remaining Tables

Now let's take a look at the territories table. From a quick look at this data, it appears that we need to store an integer and two variable-length strings. As with our previous example, we don't expect the Region ID to consume more than 25 characters. However, some of our territories have longer names, so we'll expand the allowable length of that attribute to 40 characters.

Let's look at the corresponding SQL:

CREATE TABLE territories
(territoryid INTEGER NOT NULL,
territory Description VARCHAR(40) NOT NULL,
regionid VARCHAR(25) NOT NULL);

Finally, we'll use the EmployeeTerritories table to store the relationships between employees and territories. Detailed information on each employee and territory is stored in our previous two tables. Therefore, we only need to store the two integer identification numbers in this table. If we need to expand this information we can use a JOIN in our data selection commands to obtain information from multiple tables.

This method of storing data reduces redundancy in our database and ensures optimal use of space on our storage drives. We'll cover the JOIN command in-depth in a future tutorial. Here's the SQL code to implement our final table:

CREATE TABLE employeeterritories
(employeeid INTEGER NOT NULL,
territoryid INTEGER NOT NULL);

The Mechanism SQL Provides to Alter the Structure of a Database After Creation

If you're particularly astute today, you might have noticed that we "accidentally" omitted one of the design requirements when implementing our database tables. XYZ Corporation's HR Director requested that the database track employee salary information and we neglected to provide for this in the database tables we created.

However, all is not lost. We can use the ALTER TABLE command to add this attribute to our existing database. We want to store the salary as an integer value. The syntax is quite similar to that of the CREATE TABLE command, here it is:

ALTER TABLE employees
ADD salary INTEGER NULL;

Notice that we specified that NULL values are permitted for this attribute. In most cases, there is no option when adding a column to an existing table. This is due to the fact that the table already contains rows with no entry for this attribute. Therefore, the DBMS automatically inserts a NULL value to fill the void.

Format
mla apa chicago
Your Citation
Chapple, Mike. "Creating Databases and Tables in SQL." ThoughtCo, Nov. 18, 2021, thoughtco.com/creating-databases-and-tables-in-sql-1019781. Chapple, Mike. (2021, November 18). Creating Databases and Tables in SQL. Retrieved from https://www.thoughtco.com/creating-databases-and-tables-in-sql-1019781 Chapple, Mike. "Creating Databases and Tables in SQL." ThoughtCo. https://www.thoughtco.com/creating-databases-and-tables-in-sql-1019781 (accessed March 28, 2024).