9 Chapter 9 Structured Query Language (SQL)

Original Chapter 15 Author: Adrienne Watt and Nelson Eng

Original Chapter 16 Author: Adrienne Watt and Nelson Eng

Rewrite: Fred Strickland

Learning Outcomes

Introduction to Chapter 9

In September 2024, DB-Engine listed 159 DBMSs that support the use of Structured Query Language (SQL). SQL is a fourth-generation programming language that is designed for working with databases and with the data in a database.

SQL could be introduced earlier in this book as extra material or SQL could be approached as a major topic. The second edition covered SQL in Chapter 15 and it covered SQL Data Manipulation Language in Chapter 16.

The approach for this chapter is to install the Microsoft’s AdventureWorks example database. The code examples will be based on the earlier chapters and on the Microsoft’s AdventureWorks example database. The order is different from the second edition.

Note: The example SQL commands in the second edition Chapter 16 did not match the example outputs nor the source database tables.

The Third Edition Style Guide

This is the style guide that this book will follow for this chapter and for the other chapters.

Figure 9.1 The database style guide. Adapted from https://vertabelo.com/blog/database-schema-naming-conventions/

The examples will conform to the style guide.

Installing the Microsoft’s AdventureWorks Example Database

The Microsoft’s AdventureWorks example database¹ is a fictional company that tracks many pieces and data.

This database could be uploaded to a SQL Server or to the Azure SQL cloud environment. The environment needs to have either the Microsoft SQL Server Management Studio or the Azure Data Studio. For this chapter, we will deal only with the SQL Server and the SQL Server Management Studio.

Microsoft makes available three versions of the AdventureWorks database:

• OLTP² data is for most typical online transaction processing workloads.
• Data Warehouse (DW) data is for data warehousing workloads.
• Lightweight (LT) data is a lightweight and pared down version of the OLTP sample.

The AdventureWorks database is customized for each SQL Server version. In September 2024, the current SQL Server version is 2022. The following table illustrates this point:

Figure 9.2 AdventureWorks files for supporting SQL Server 2008R2 through SQL Server 2022. Source of image: https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

At this writing, the most current SQL Server version is 2022. So we would download AdventureWorks2022.bak.

Restoring to the SQL Server

The steps used for installing the AdventureWorks database are the same steps for restoring a database backup.

Microsoft makes available two approaches:

• Using a graphic interface (GUI) from the SQL Server Management Studio.
• Using the RESTORE command.

Using the SQL Server Management Studio GUI

To restore your database in SSMS, follow these steps:

• Download the appropriate .bak file from one of links provided in the download backup files section.
• Move the .bak file to your SQL Server backup location. This location varies depending on your installation location, instance name, and version of SQL Server. For example, the default location for a default instance of SQL Server 2022 (16.x) is:

C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup

• Open your SQL Server Management Studio program and connect to your SQL Server instance.

Figure 9.3 Starting up Microsoft SQL Server Management Studio and connecting to the database engine. Source of iamge: https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver16

• Right-click Databases in Object Explorer > Restore Database… to launch the Restore Database wizard.

Figure 9.4 Right click on the Databases folder and choose the Restore Database option. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

• Select Device and then select the ellipses (…) to choose a device.

Figure 9.5 Inside the Source section, switch to the Device option. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

• Browse to the location of the .bak file. Select Add and then choose the .bak file you recently moved to the backup location.
  • If you moved your file to this location but you’re not able to see it in the wizard, SQL Server or the user signed into SQL Server doesn’t have permission to this file in this folder.

Figure 9.6 The Add screen. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

Figure 9.7 Finding the .bak file. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

Figure 9.8 Selecting the file Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

• Select OK to confirm your database backup selection and close the Select backup devices window.

Figure 9.9 Prior to hitting the OK button. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

• Check the Files tab to confirm the Restore as location and file names match your intended location and file names in the Restore Database wizard.
• Select OK to restore your database.

Figure 9.10 The success screen. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

The restored AdventureWorks database will have the following appearance:

Figure 9.11 Screenshot showing the tables and folders in the AdventureWorks database. Also shown is an example query. Source of image: https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

Using the RESTORE Statement

After logging onto the session (Figure 9.2), do a right mouse click and select “New Query.”

Figure 9.12 Selecting the “New Query” option. Source of image: https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver16

Either copy-and-paste or type the text into the query window. The database name and installation file path can vary depending on your environment.

USE [master];

GO

RESTORE DATABASE [AdventureWorks2022]

FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak’

WITH

FILE = 1,

NOUNLOAD,

STATS = 5;

GO

Execute the query by selecting the Execute button or by hitting F5 on your computer keyboard.

This website has an example of what to do on a Linux computer.

Background on SQL

SQL is a fourth-generation programming language, because it has the following traits:

• Very close to human language.
• It is a non-procedural programming language. The programmer defines what has to be done instead of how the task is to be done.
• The execution is very fast.

PHP, Python, SQL, and others are examples of a fourth-generation programming language. It is beyond the scope of this book to explore the advantages and the disadvantages. For more information, see the reference that has the words fourth-generation programming language in the title.

SQL dates back to the 1970s. IBM had named this as “Structured English Query Language” or SEQUEL. Oracle was the first company to use SQL in a commercial product. As companies issued DBMSs, a company-unique SQL was provided. By the mid-1980s, there were several SQL versions and these were not interchangeable.

Efforts were started to standardize. The American National Standards Institute (ANSI) saw the need and adopted SQL as a standard. A year later, the International Organization for Standardization (ISO) did the same thing. There are several SQL standards.

Figure 9.13 Learn SQL website graphic showing SQL standards from 1986 through to 2019. Source: https://learnsql.com/blog/history-of-sql-standards/

SQL-92 is the foundation that many relational DBMSs use. SQL-92 is taught in many schools and is covered in many textbooks. However, since SQL-92 has 579 pages, the coverage is not complete.

The later SQL standards added features that tend to be advanced or that are needed for a special application. Although understanding SQL-92 will help one on the job, there are commercial implementations that are not 100% compliant with the SQL-92 standard. For example, Microsoft has its own version known as “Transact-SQL” (T-SQL).

For a company to claim that it is compliant with the ANSI standard, the following commands must be support:

• Delete
• Insert
• Select
• Update
• Where

SQL commands are grouped into categories. One grouping scheme has three categories:

• Data Manipulation Language (DML): For working with the actual data in a table.
• Data Query Language (DQL): For querying and analyzing data in a database.
• Data Definition Language (DDL): For creating a database and working with the tables.

Geeks for Geeks SQL Commands article added the following categories:

• Data Control Language (DCL): For handling rights, permissions and other controls for a DBMS.
• Transaction Control Language (TCL): For grouping a series of SQL commands in a package. If any of the tasks fail, then the whole group fails. There are only two results: success or failure.

Different sources will group the SQL commands differently. For example, the same Geeks for Geeks article placed the SELECT command under DCL whereas most sources will place the SELECT command under DML. On the W3 schools website, all of the commands are grouped together.

SQL commands are not case-sensitive. By custom, many sources will express key words in blue font and in upper case letters. Key words are reserved words that cannot be used as variables. Example key words are SELECT, DELETE, UPDATE, and AS.

By custom, simple SQL commands are written on one line. More complex SQL commands will be written over several lines. A semi-colon is not needed for the execution of one SQL command. A semi-colon is needed for separating two or more SQL commands.

Data Manipulation Language (DML)

We will look at SELECT, DELETE, UPDATE, and INSERT. We will also look at AS, at DISTINCT, and at WHERE, because these will modify the outputs.

DML: SELECT

The Employees table in chapter 6 (Figure 6.4) had five rows. It had nine columns. We would use a DML command to display the data. We would use the SELECT command. For the Figure 6.4 example, we would use the following SQL SELECT command:

SELECT * FROM Employees;³

The asterisk is a wildcard character. A wildcard is a space holder for one or more characters. In this context, the asterisk represents all columns in the named table.

Here is the display:

Figure 9.14 Example of the Employees table from chapter 6. (The ID column corrected to follow the Third Edition Style guide.)

The W3 schools website has some example database tables that are interactive. Visit https://www.w3schools.com/sql/sql_syntax.asp and click on the “Try it Yourself” button.

Figure 9.15 W3 schools example of a SELECT command with a wildcard character . Source of image: https://www.w3schools.com/sql/sql_syntax.asp

The W3 schools example will return 91 rows. Figure 9.16 shows the first seven rows.

Figure 9.16 The result from the W3 schools example of a SELECT command with a wildcard character . Source of image: https://www.w3schools.com/sql/sql_syntax.asp

The AdventureWorks database example has a table for employees (Figure 9.17).

Figure 9.17 The AdvetureWorks Employee table . Source of image: https://learnsql.com/blog/sql-practice-for-beginners-adventureworks-exercises/

You could query the AdventureWorks Employee⁴ table with this SQL command:

SEECT * FROM Employee

The ordering of columns follows the Data Definition Language create table command. To override the default ordering of columns, we can list the desired columns in the desired order. The following command will list the ID, the last name, and the first name in that order:

SELECT ID, LastName, FirstName FROM Employees;

Here is the display:

Figure 9.18 Example of the corrected Employees table from chapter 6 with only three columns displayed.

TheW3 schools website supports making changes. On the same web page as before, change the command to read:

SELECT CustomerID, CustomerName FROM Customers;

The W3 schools example will return 91 rows. Figure 9.19 shows the SQL command and the first seven rows.

Figure 9.19 The result from the W3 schools example of a SELECT command with two columns listed . Source: https://www.w3schools.com/sql/sql_syntax.asp

The AdventureWorks.Employee table does not contain an employee’s name. Look at Figure 9.20 for the location of the name fields.

Figure 9.20 The Employee table uses the foreign key of ContactID in order to obtain the first and the last name. Source: https://i0.wp.com/improveandrepeat.com/wp-content/uploads/2018/12/AdvWorksOLTPSchemaVisio.png?ssl=1

As a side note, if you are collecting the same fields for a customer, for an employee, for a store contact, for a vendor contact, for a contact credit card, and for a sales order header, then it might make sense to have all of these tables pointing back to a person or contact table. It is in the obtaining the client’s database requirements that you begin to determine what database tables are needed. The AdventureWorks example uses six tables to fully document everything about a person (Contact, Address, StaetProvince, ContactType, AddressType, and CountryRegion). It is likely that you would document a person with fewer tables by combining the data into a more reasonable scheme.

Here is a possible SQL query:

SELECT ContactID, FirstName, LastName FROM Contact

The chapter 6 Employees table had the first column as “EmployeeID.” In the style guide, we are using “ID.” Recall that the best practice is to have the data sitting on one server, the DBMS sitting on a second server, and the client software sitting on a third server. Suppose that we have a requirement to prefix “Employee” to the “ID” in a report.

The following command will output the ID as “EmployeeID,” the last name, and the first name

SELECT ID AS “EmployeeID”, LastName, FirstName FROM Employees;

Here is the display in Figure 9.21:

Figure 9.21 Example of the corrected Employees table from chapter 6 with the ID column label reading as “EmployeeID.”

On the same W3 schools web page as before, change the command to read:

SELECT CustomerID AS ID, CustomerName AS “Customer Whole Name (first name and last name order)” FROM Customers;⁵

The W3 schools example will return 91 rows. Figure 9.22 shows the SQL command and the first seven rows.

Figure 9.22 The result from the W3 schools of a SELECT command using the AS key word and using a string . Source: https://www.w3schools.com/sql/sql_syntax.asp

Sometime similar could be done in the AdventureWorks database.

SELECT ContactID AS ID, FirstName AS “First Name”, LastName AS “Last Name” FROM Contact

The SELECT examples will return unique rows. What about queries that might return duplicate rows? The Employees table would return duplicate rows for the Salary column. The W3 schools table would return duplicate rows for the Country column. The AdventureWorks Contact table would return duplicate rows for the Title column.

The key word DISTINCT removes the duplicate rows. The following is the format:

SELECT DISTINCT Country FROM Customers;

DML: DELETE and UPDATE

The DDL commands of DELETE and UPDATE will make changes to a database. One must be careful to have the complete command. Otherwise major changes could be made to the database that are not reversible⁶.

To limit or to control these commands, the key word WHERE must be used.

DELETE Syntax: DELETE FROM TableName WHERE condition;

UPDATE Syntax: UPDATE TableName SET column1 = value1 WHERE condition

The WHERE condition could be for one record or for a subset of records. Each WHERE condition would be separated by a comma except for the last WHERE condition.

Suppose in our chapter 6 Employees table that DepartmentID #1 was sold to another company. Thus we would need to remove all individuals from this department⁷. We would use the following DML command:

DELETE FROM Employees WHERE DepartmentID = 1;

Suppose in our chapter 6 Employees table that DepartmentID #1 has been divided into two departments. Employees working in British Columbia would continue have a department ID code of 1, but employees working in New Brunswick would have a new department ID code of 3. We would use the following DML command:

UPDATE Employees SET DepartmentID = 3 WHERE Address = “Grand Falls”⁸;

As Ben noted in his article entitled “24 Database Design Mistakes and How to Avoid Them,” it may be better to do a “soft delete.” That is, a flag or an additional column is used that marks the record as inactive. His reason is that the data may be needed when something is re-activated..

DML: INSERT INTO

The DDL command of INSERT INTO will make changes to a database. One must be careful to have the complete command and to have the items in the correct order. Otherwise major changes could be made to the database that are hard to correct. The values are handled as pairs. The syntax is:

INSERT INTO Syntax: INSERT INTO TableName (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

Suppose in our chapter 6 Employees table that we wish to add a new person

INSERT INTO Employees (FirstName, LastName, SSN, Address, Phone, Birthdate, Salary, DepartmentID)

VALUES (‘Jennifer’, ‘Miller’, ‘7’, ‘Edmonton’, ‘789’, ‘01-01-2001’, ‘GS7-1’, ‘4’);

The INSERT INTO is very flexible. You could have the items in different order from the actual column order and the command will execute and place the values in the correct columns. If you know the exact order of the columns and you are careful to have the values in the correct order and if there are no missing values, then you could omit the column names.

Data Query Language (DQL)

DQL commands are used to query and to analyze data. Some sources consider SELECT and DISTINCT to be members of DQL. We have not. COUNT, SUM, AVG, MIN, and MAX are true members of DQL. These are functions that will process the rows and provide a result. We will also look at GROUP BY, because this will modify the outputs. (GROUP BY could have been covered in the DML section as another modifier. I choice to cover it here, because GROUP BY is used frequently with these DQL functions.)

DQL: AVG

The DQL command of AVG() does not make any changes to a database. The AVG() function returns the average value for a column that holds a numeric value. NULL values are ignored. And of course text value defined columns do not work. The syntax is:

AVG() Syntax: SELECT AVG(column_name)

FROM TableName

WHERE condition;

Chapter 7 did not have a normalized set of tables for the bank branch tables. You will be asked to do this in a question in the exercises.

The W3 schools website has an example of this SQL command for the Products table:

Figure 9.23 W3 schools example of an AVG() command . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_avg

Sometime similar could be done in the AdventureWorks database by using the SalesOrderHeader (Figure 9.24).

Figure 9.24 Example of an AVG() command by using the SalesOrderHeader table from AdventureWorks . Source: https://i0.wp.com/improveandrepeat.com/wp-content/uploads/2018/12/AdvWorksOLTPSchemaVisio.png?ssl=1

We could add a WHERE clause to filter the results. You could look for average prices of products from a certain category. Figure 9.25 illustrates this approach.

Figure 9.25 W3 schools example of an AVG() command with a WHERE clause . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_avg_where

Something similar could be done with the AdventureWorks Product table. You would need to have some insights into this table and into the ProductSubcategory table. From an inspection of the Product table it appears that the ProductNumber is for the actual product whereas the ProductSubcategoryID would be for a class of products with ProductCategory is for a specific or more narrow category. Think of Product as having the Name of “Santa Fe” with the ProductSubcategory being “Passenger” and with the ProductCategory being “Sedan.” Again, we need to read the developer’s notes to understand what is expected to be stored in each table. We could look at the sample data in each table and hope that the data entry followed the database designer’s intent. Example Figure 9.26 to understand this better.

Figure 9.26 Example of an AVG() command by using three Production tables from AdventureWorks . Source: https://i0.wp.com/improveandrepeat.com/wp-content/uploads/2018/12/AdvWorksOLTPSchemaVisio.png?ssl=1

The AVG() command will use a generic name for the column name in the output. By using the key word AS, we can change that. Here is an example from the W3 schools website:

Figure 9.27 W3 schools example of an AVG() command with the key word AS . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_avg_as

By using GROUP BY, we can produce an interesting result. The W3 schools website returned the average price for each category in the Products table:

Figure 9.28 W3 schools example of a AVG() command with a GROUP BY field. . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_avg_groupby

DQL: COUNT

The DQL command of COUNT() does not make any changes to a database. The COUNT() function returns the number of rows that matches a specified criterion. The syntax is:

COUNT() Syntax: SELECT COUNT(column_name)

FROM TableName

WHERE condition;

When a column name is used, NULL values are not counted.

For the chapter 6 Employees table, we can count the number of individuals with a first name by using the following SQL command:

SELECT COUNT(FirstName)

FROM Employees

The result will be just the figure.

The W3 schools website has an example of this SQL command for the Products table:

Figure 9.29 W3 schools example of a COUNT() command . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_count3

We could add a WHERE clause to filter the results. You could look for employees in a certain location or products within a certain price range.

The key word DISTINCT could be entered in the column name of the COUNT() command. Here is an example of how to find unique first names in the Employees table:

SELECT COUNT( DISTINCT FirstName)

FROM Employees

The COUNT() command will use a generic name for the column name in the output. By using the key word AS, we can change that. Here is an example of how to find unique first names in the Employees table with a useful column name in the output:

SELECT COUNT( DISTINCT FirstName) AS [Number of Unique First Names]

FROM Employees

Here is an example from the W3 schools website:

Figure 9.30 W3 schools example of a COUNT() command with the key word AS . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_count_as

By using GROUP BY, we can produce an interesting result. For example, we could count the number of individuals in a department:

SELECT COUNT(*) AS [Number of Records], DepartmentID

FROM Employees

GROUP BY DepartmentID

Here is a W3 schools example where we wish to know how many products exist in each category.

Figure 9.31 W3 schools example of a COUNT() command with a GROUP BY field. . Source: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_count_groupby

DQL: MAX

The DQL command of MAX() does not make any changes to a database. The MAX() function returns the highest value for a column that holds a numeric value. NULL values are ignored. And of course text value defined columns do not work. The syntax is:

MAX() Syntax: SELECT MAX(column_name)

FROM TableName

WHERE condition;

This function is similar to the AVG() function. You can use the key word AS to change the generic name of the output column. You can use the GROUP BY clause for returning the highest value for a value in another column.

DQL: MIN

The DQL command of MIN() does not make any changes to a database. The MIN() function returns the lowest value for a column that holds a numeric value. NULL values are ignored. And of course text value defined columns do not work. The syntax is:

MIN() Syntax: SELECT MIN(column_name)

FROM TableName

WHERE condition;

This function is similar to the AVG() function. You can use the key word AS to change the generic name of the output column. You can use the GROUP BY clause for returning the lowest value for a value in another column.

DQL: SUM

The DQL command of SUM() does not make any changes to a database. The SUM() function returns the SUM or total value for a column that holds a numeric value. NULL values are ignored. And of course text value defined columns do not work. The syntax is:

SUM() Syntax: SELECT SUM(column_name)

FROM TableName

WHERE condition;

This function is similar to the AVG() function. You can use the key word AS to change the generic name of the output column. You can use the GROUP BY clause for returning the SUM or total for a value in another column.

Data Definition Language (DDL)

We will look at CREATE, ALTER, TRUNCATE, RENAME, and DROP. We will also look at AS, at DISTINCT, and at WHERE, because these will modify the outputs.

Microsoft SQL Server Management Studio provides a graphic user interface (GUI) for creating tables. We will not use this means. We will look at SQL commands for creating a database and for adding tables. The benefit is that you have better control.

DDL: CREATE DATABASE

CREATE DATABASE Syntax: CREATE DATABASE database_name;

The SQL command is simple. If you wish to see a list of databases, you can use the following:

SHOW DATABASES;

DDL: DROP DATABASE

DROP DATABASE Syntax: DROP DATABASE database_name;

The SQL command is simple. BE CAREFUL! Deleting a database will result in total loss of data.

Database Backup

You might expect to use a SQL command to do things like backing up a database. Microsoft SQL Server has a command to do this, but other DBMS may not. Microsoft supports a full backup and a differential backup (changes made since the last backup).

Figure 9.32 Microsoft SQL Server backup commands

And of course, backups should be stored on a different drive just in case the disk crashes.

Backing up other DBMS may require using a third-party tool.

DDL: CREATE TABLE Background Information

The CREATE TABLE SQL command is used to create a new table in a database. The simple syntax does not reveal how powerful the command is:

CREATE TABLE Syntax: CREATE TABLE table_name(

column1 datatype,

colume2 datatype;

colume3 datatype;

….

);

This is where you need to have some knowledge about the selected DBMS. And you need to understand the nature of the table. If you have done a solid job of understanding what is needed for each table, then this should be easy.

The Microsoft’s VARCHAR(n) could be 8,000 bytes or 8,000 characters. Oracle’s VARCHAR2(s) could be 32,767 bytes or 32,767 characters. MySQL VARCHAR(n) could be 65,535 bytes or 65,535 characters. PostgreSQL’s VARCHAR(n) could be 10,485,760 characters. If the database deals with the ten-digit numbers as used in North American, then the field size should be ten characters¹⁰. The postal codes used in Canada and in the United States are two characters long. As Ben noted in his article entitled “24 Database Design Mistakes and How to Avoid Them,” any data warehouse and immediate systems would be required to handle the data columns with the same width.

Ben did not address the size for names. Frequently, you see examples with the figure of 255 being used. Is that a good figure? It depends. If you work for largest.org, then you would need to have the full name field to handle at least 963 characters. The next longest name has 899 characters. At number 7, Pablo Picasso’s full name has 103 characters. At number 10, Salvador Dail’s full name has 34 characters. So for individual name fields, you might be safe to limit the text field to about 40 characters.

Ben pointed out the need to use correct data types. Telephone numbers, United States Zip codes, plus any numeric appearing value that will not be manipulated through arithmetic should be stored as a text. Why? If a United States Zip codes has a leading zero and if these entries are stored as a numeric type, then the leading zeros would be removed.

Ben of Database Star did not address VARCHAR and NVARCHAR data types and the closely related CHAR and NCHAR.

• CHAR: This is a fixed length data type. The unused spaces are filled in with blanks or spaces.
• VARCHAR: This is a variable length data type.
• NCHAR: This is a fixed length data type. The unused spaces are filled in with blanks or spaces. You can store characters that do not exist in the English language.
• NVARCHAR: This is a variable length data type. You can store characters that do not exist in the English language.

The ID column needs to have unique values. Some DBMS provide an auto increment approach. A new value is created when a new record is added to a table. When values are added to a table, there is no need to address the ID column in the SQL insert command. Ben of Database Star recommends using a sequence table.

DDL: CREATE TABLE with Auto-Increment Field

The approach is not standardized.

The MySQL example is for a Persons table. The example came from the W3 schools website. It has been corrected to conform with our style guide:

Figure 9.33 Syntax for MySQL Create Table command with auto increment. Adapted from https://www.w3schools.com/SQl/sql_autoincrement.asp

The SQL Server example is for a Persons table. The ID field contains the auto-increment instruction. You can set the starting value and you can set the steps. Also we can state in this same line that this is the primary key. A second command is not needed. The example came from the W3 schools website. It has been corrected to conform with our style guide:

Figure 9.34 Syntax for SQL Server Create Table command with auto increment. Adapted from https://www.w3schools.com/SQl/sql_autoincrement.asp

Note: We do not wish to explore Microsoft Access. The approach is similar to the Microsoft SQL Server, except the key word AUTOINCREMENT is used instead of IDENTITY.

DDL: CREATE TABLE with Oracle Sequence Object

The Oracle approach is different. A table is created. Then a sequence object is created. Then a reference to the sequence object is used when adding a row.

Figure 9.35 Syntax for Oracle’s approach. Adapted from https://www.w3schools.com/SQl/sql_autoincrement.asp

Oracle does support auto increment. Ben has a detailed article explaining why the sequence object is better.

DDL: CREATE TABLE with Constraints

Constraints are used to enforce keys and the defined rules. These will limit the data that can be entered into a table.

CREATE TABLE Syntax: CREATE TABLE table_name(

column1 datatype constraint,

colume2 datatype constraint;

colume3 datatype constraint;

….

);

The following are the common constraints used:

• NOT NULL – Ensures that a column cannot have a NULL value.
• UNIQUE – Ensures that all values in a column are different.
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
• FOREIGN KEY – Prevents actions that would destroy links between tables.
• CHECK – Ensures that the values in a column satisfies a specific condition.
• DEFAULT – Sets a default value for a column if no value is specified.
• CREATE INDEX – Used to create and retrieve data from the database very quickly.

DDL: CREATE TABLE with the NOT NULL Constraint

We define what must be present before something can be added to a table. Using the W3 schools example, the following create table could be used:

Figure 9.36 NOT NULL Constraints in the CREATE TABLE command. Source https://www.w3schools.com/SQl/sql_notnull.asp

Once a table has been created, changing the constraints is easy to do. But the syntax is not standardized. See the reference for the other four approaches.

DDL: CREATE TABLE with the UNIQUE Constraint

We do not wish for duplicate values to be added to a table. Using the W3 schools example, the following create table command could be used:

Figure 9.37 UNIQUE Constraints in the CREATE TABLE command. Adapted from https://www.w3schools.com/SQl/sql_notnull.asp

If the Id field had been defined as a primary key, then by definition there would be no need to use the key word UNIQUE in that line. Again, this action is not standardized. See the refence for the MySQL lines.

It is possible to name a UNIQUE constraint and have it applied to two or more columns. W3 schools illustrated this action. The example is a bit misleading. With a unique Id number, you would not have an issue if a last name appeared twice in the database table.

You can add a UNIQUE constraint. Here is the syntax for doing this in MySQL, SLQ Server, and Oracle:

ALTER TABLE Persons

ADD UNIQUE (Id);

In these examples, SQL Server and other DBMSs would generate a name for the UNIQUE constrain. It may look like the following:

UQ__Persons__AB6E616417240E4E

If you needed to obtain the name of the UNIQUE constraint, then you would either do something to cause a UNIQUE constraint error message to appear or use a SQL query against a system table.

Figure 9.38 Two approaches for obtaining the name of a UNIQUE Constraint. Adapted from https://www.sqlservertutorial.net/sql-server-basics/sql-server-unique-constraint/ and https://database.guide/how-to-delete-a-unique-constraint-in-sql-server/

Instead of letting the system name the constraint, you can create your own name. Here is an example:

Figure 9.39 Syntax for SQL Server Create Table command with a named UNIQUE constraint. Adapted from https://www.sqlservertutorial.net/sql-server-basics/sql-server-unique-constraint/

The same idea would apply if a constraint is added after the table has been created:

Here is an example for doing this in MySQL, SLQ Server, and Oracle:

ALTER TABLE Persons

ADD UNIQUE UniqueEmail (Email);

To remove a constraint, you would use the following approach:

ALTER TABLE Persons

DROP UNIQUE UniqueEmail;

DDL: CREATE TABLE with the PRIMARY KEY Constraint

We use a PRIMARY KEY in order to uniquely identify each record in a table. We have covered the details of the requirements for a PRIMARY KEY. We will not repeat that information here. Using the W3 schools example, the following create table commands could be used:

Figure 9.40 PRIMARY KEY Constraints in the CREATE TABLE command. Source: https://www.w3schools.com/sql/sql_primarykey.asp

Since the PRIMARY KEY is a UNIQUE constraint, the syntax is the same for the other actions except the phrase PRIMARY KEY is used instead of UNIQUE.

The approaches of the other DBMSs are not standardized.

DDL: CREATE TABLE with the FOREIGN KEY Constraint

We use a FOREIGN KEY in order to protect the linkage between tables. We have covered the details of the requirements for a FOREIGN KEY. We will not repeat that information here. Using the W3 schools example, the following create table commands could be used:

Figure 9.41 The Key Constraints in the CREATE TABLE command. Adapted from: https://www.w3schools.com/sql/sql_foreignkey.asp

The syntax for the other actions is similar to the PRIMARY KEY syntax.

Again the approaches of the other DBMSs are not standardized.

DDL: CREATE TABLE with the CHECK Constraint

We use a CHECK constraint in order to protect the data by limiting the values. Using the modified W3 schools example, the following create table commands could be used:

Figure 9.42 An example of a CHECK Constraint in the CREATE TABLE command. Adapted from: https://www.w3schools.com/sql/sql_check.asp

A check could be named.

Figure 9.43 An example of a named CHECK Constraint in the CREATE TABLE command. Adapted from: https://www.w3schools.com/sql/sql_check.asp

A check could be compound.

Figure 9.44 An example of a named compound CHECK Constraint in the CREATE TABLE command. Adapted from: https://www.w3schools.com/sql/sql_check.asp

The syntax for the other actions is similar to the PRIMARY KEY syntax.

Again the approaches of the other DBMSs are not standardized.

DDL: CREATE TABLE with the DEFAULT Constraint

We use a DEFAULT constraint in order to avoid using a NULL value for an unknown field. This may be a concern when using a DQL function. For example, you want an average that is based on the rows and a reasonable value for each row. Using the modified W3 schools example, the following create table commands could be used:

Figure 9.45 An example of a DEFAULT Constraint in the CREATE TABLE command. Adapted from: https://www.w3schools.com/sql/sql_default.asp

The DEFAULT constraint supports obtaining the date from the system.

Figure 9.46 An example of a DEFAULT Constraint that uses a function in the CREATE TABLE command. Adapted from: https://www.w3schools.com/sql/sql_default.asp

The GETDATE() function is unique to SQL Server (2008 and onward), Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

The syntax for the other actions is similar to the PRIMARY KEY syntax.

Again the approaches of the other DBMSs are not standardized.

DDL: CREATE TABLE with the CREATE INDEX

Some authorities do not consider the CREATE INDEX to be a constraint. When the primary key is not known, we can craft a query to locate the desired row. By defining indexes, the query can execute faster. Updating a table with indexes will take longer. Indexes are invisible to users.

Microsoft SQL Server will automatically create indexes when a primary key and unique constraints have been defined in a table. These are stored in a B-tree¹⁶

The syntax differs from the constraints syntax. The key word ON is used. Again the approaches of the other DBMSs are not standardized.

DDL: CREATE TABLE with the Cascading Update and Cascading Delete

Some authorities do not include cascading in a discussion on constraints. This is another tool for maintaining data integrity in a database. This supports referential integrity inside a database. Cascading works with the foreign key constraints. An action done on one table will be applied to another table.

Imagine an employee with a family. If this person leaves the company, then cascading delete would purge the family information from the dependents table.

Figure 9.47 An example of a DELETE CASCADE in the CREATE TABLE command. Adapted from: https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php

In Figure 9.xx, the Dependents table has a foreign key that points back to the Persons table. The “ON DELETE CASCADE’ clause tells the SQL Server to delete the linked row if the Persons ID entry is removed.

The syntax for UPDATE CASCADE is similar.

There are some options:

• NO ACTION
• CASCADE
• SET NULL
• SET DEFAULT

The ALTER TABLE command can be used to add a cascade line.

Using a cascade line is better than using a trigger command.

The Full CREATE TABLE Syntax

The full syntax has many provisions and options. Some are listed below:

• Constraints on a table
• Table indexes
• Location on a partition
• Information about a file stream
• Details and constraints for a column
• Encryption key information
• Data types
• Primary keys and foreign keys
• Checks
• Column indexes
• Computed column definitions

Microsoft Learn has a very detailed article about the foregoing and more. See the references for more information.

Working with Two or More Tables

The power of a relational DBMS is when we are able to pull data from two or more tables. We can do this with the JOIN clauses or with the Union:

In the section on foreign key constraints on cascading, we talked about two tables. But we did not discuss how to work with the two tables.

Let’s use the following:

• Orders Table
  • ID
  • CustomerID
  • Date
• Customers Table
  • ID
  • Name
  • Contact
  • Country

CustomerID is the foreign key in the Order Table. If we wish to output the order ID, the customer’s name and the order date, then we would use the following command:

Figure 9.48 An example of a INNER JOIN in the SELECT command. Adapted from: https://www.w3schools.com/sql/sql_join.asp

If the column names are not found in the other table, then the dotted name approach is not needed. The dotted name approach is needed with the column name is the same in the involved tables.

The second table’s name follows the JOIN key word. The ON clause states where the linkage is. Typically this is the primary key in one table with the foreign key in the other table.

The INNER JOIN is very common. So much so that the key word “INNER” can be omitted.

There are three other JOINs.

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

The word OUTER could be omitted.

Vin diagrams are the best way to explain these:

Figure 9.49 Vin diagrams that illustrate the results of each join. Source of image: https://www.w3schools.com/sql/sql_join.asp

The UNION command ignores the requirement to have a linkage between two tables. The requirement is the number of columns must be the same from both tables.

For example, you might have a table of retirees in the Canadian system and a table of retirees in the United States system. You wish to invite these individuals to visit the Roosevelt Campobello International Park¹⁷. The two tables do not have the same columns, but you only want the names and the mailing addresses in the output. Here is how this could be done:

Figure 9.50 Contrived example of a UNION statement.

• And if you find that many individuals are in both tables, then you could filter out the duplicates by using “UNION ALL.”

Comment on SQL Commands

This coverage is limited. It was designed to get you started. We did not cover the following:

• Truncate
• Rename
• Order By
• AND
• Or
• Not
• Other wildcard characters
• Regular expressions
• Between
• In
• Having
• Exists
• Like
• Convert
• Date
• MERGE
• Performing mathematical operations on numeric data

As you work with databases, you may need to visit websites in order to obtain the syntax for a command. You may find it necessary to do some research in order to answer some of the questions at the end of this chapter.

The SQL commands in this chapter have tended to be SQL-92. Some of the unique Microsoft T-SQL may have slipped through. The actual T-SQL will be covered in other chapters.

This chapter did not provide a best practice list. Although the examples have been formed in accordance with some best practice guidelines. See Google’s list at https://cloud.google.com/spanner/docs/dml-best-practices and metabase.com at https://www.metabase.com/learn/grow-your-data-skills/learn-sql/working-with-sql/sql-best-practices.

I have provided some information on the differences between Oracle, Microsoft SQL Server, MySQL, and Postgres. The Database Star article on creating tables with examples does provide more information on the differences. See the reference list for the URL.

I did not address data types. Each DBMS has its own list of data types. See https://www.w3schools.com/sql/sql_datatypes.asp

I did not address DCL.

Key Terms

American National Standards Institute (ANSI): This is a private, non-profit organization in the United States that sets standards. It administers and coordinates the United States voluntary standards. It was founded in 1918.

Data Control Language (DCL): This is for handling rights, permissions and other controls for a DBMS.

Data Definition Language (DDL): This is for creating a database with tables. There are commands for changing tables. See the text for the actual words.

Data Manipulation Language (DML): This is for working with the actual data in a table. See the text for the actual words.

Data Query Language (DQL): This is for querying and analyzing data in a database. Some authorities will group these SQL commands under DML. See the text for the actual words.

Graphic interface (GUI): This is a means for interacting with a computer. This could involve using a mouse for clicking on menus. The opposite is a command prompt interface.

International Organization for Standardization (ISO): This is similar to the ANSI. It was founded in 1946.

key words: These are reserved words that cannot be used as variables in a SQL command.

Online Transaction Processing (LTP): This is as type of data processing that consists of executing a number of transactions that are occurring at the same time.

SQL-92: This is the foundation that many relational DBMSs use. SQL-92 is taught in many schools and is covered in many textbooks. However, since SQL-92 has 579 pages, the coverage is not complete.

SQL Server Management Studio (SSMS): Microsoft will frequently refer to their SQL Server Management Studio as “SSMS.” I prefer to use the long form in order to avoid abbreviation overload. “SSMS” will appear in the text when I am quoting information.

Structured Query Language (SQL): This is a fourth-generation programming language that is designed for working with databases and with the data in a database.

Transact Structured Query Language or Transact-SQL (T-SQL): This is Microsoft’s version of SQL with enhancements.

Transaction Control Language (TCL): For grouping a series of SQL commands in a package. If any of the tasks fail, then the whole group fails. There are only two results: success or failure.

wildcard: This is a space holder for one or more characters. The asterisk is a very common wildcard.

Exercises

1. Describe the functionality provided by languages such as SQL. [CE2016 CE-SWD-10 Database systems]
2. Using either W3 schools or the AdventureWorks database to execute a simple SELECT statement. In your answer, show the command and the output. [CS2013 IM/DS 7.]
3. In the explanation for the key word DISTINCT, an example output was not provided from the W3 schools website. How many countries would appear in the output? Provide the first five countries.
4. In “DML: DELETE and UPDATE section, the WHERE condition was used.
   1. What would happen if the WHERE clause was not used in the DELETE example?
   2. What would happen if the WHERE clause was not used in the UPDATE example
5. Write an UPDATE statement that would change the salary of those who live in Vancouver from GS10-1 to GS10-2.
6. In Chapter 7, we looked at a banking example and later at a student example. We covered how to find the issues that could come from doing an update, or an insertion, or a delete. We worked through the normalization steps, but we did not do it for the banking example. Write out the CREATE TABLE commands that would achieve the following:
   1. Create the normalized tables.
   2. Use primary and foreign keys.
   3. Use cascade commands.
   4. You may need to review what is entity integrity and referential integrity constraints.
   5. [CS2013 IM/QL 1 and CS2013 IM/QL 2; DS2021 DM/IR- T2; DS2021 PDA Programming; DMS 3]
7. For the previous question, use SQL to retrieve some data. In your answer, show the command and the output. [CS2013 IM/QL 2; DS2021 DM/IR- T2; DS2021 PDA Programming; IS2020 A3.2 Data / Information Competency Realm 1.; IT2017 ITE-IMA Domain Information Management Competencies C; IT2017 ITE IMA-04 Database query languages a ]
8. For the previous question, use SQL to retrieve some data. In your answer, show that you have used the following key words: [IT2017 ITE IMA-04 Database query languages a, b, c; IT2017 ITE-IMA-05 Database organizational architecture [L3]]
   1. WHERE
   2. ORDER BY (This was mentioned, but not explained. You will need to research this key word phrase.)
   3. BETWEEN (This was mentioned, but not explained. You will need to research this key word phrase.)
   4. LIKE (This was mentioned, but not explained. You will need to research this key word phrase.)
   5. GROUP BY
   6. HAVING (This was mentioned, but not explained. You will need to research this key word phrase.)
   7. UNION
9. For this running question, USE DQL commands. In your answer, show that you have used the following functions: [IT2017 ITE-IMA-04 Database query languages e.]
   1. AVG
   2. COUNT
   3. MAX
   4. MIN
   5. SUM
   6. A calculation. (I mentioned the ADDDATE() function, but I did not explain the actual date data type. You will need to research this. You could create an output that shows how many days have elapsed since the order was placed.)

A Running Project

A running project was introduced in Chapter 2. You were encouraged to collect insights about the needs. In Chapter 3, you were told to use a word processor to create tables with columns for the entity pieces. In Chapter 4, you were told to identify the data types. In Chapter 5, you were told to create ERD. In Chapter 6, you were told to revise your ERD to add PK and FK to your tables. You were told to add cardinality between the tables. In Chapter 7, you are to review your tables. Use the normalization steps to bring your database to 3NF. In Chapter 8, you were told to determine which DBMS would be the best one to use for the project.

In this chapter, you are to write out the SQL commands for creating the database. This action would satisfy the following hands-on learning outcomes:

• CS2013 IM/QL
  • 1. Create a relational database schema in SQL that incorporates key, entity integrity, and referential integrity constraints. [Usage]
  • 2. Use SQL to create tables and retrieve (SELECT) information from a database. [Usage]
• DS2021 DM-Information Retrieval – T2
  • Students need to have knowledge of
    • The techniques for creating and searching relational databases systems.
  • Skills
    • Create and use a relational database structure using SQL.
• DS2021 PDA-Programming
  • Skills
    • Manipulate data from selected sources (e.g., databases…) utilizing appropriate techniques (e.g., database queries…).
• IS2020 A3.2 Data / Information Competency Realm
  • 1. Query the relational model.
• IT2017 ITE-IMA Domain: Information Management Competencies
  • B. Design and implement a physical model based on appropriate organization rules for a given scenario including the impact of normalization and indexes. (Requirements and development)
  • C. Create working SQL statements for simple and intermediate queries to create and modify data and database objects to store, manipulate and analyze enterprise data. (Testing and performance)
• IT2017 ITE-IA-04 Database query languages
  • a. Create, modify, and query database objects using the Structured Query Language (SQL).
  • b. Perform filtering and sorting data using various clauses including where, order by, between, like, group by, and having.
  • c. Use joins to select data across multiple tables.
• IT2017 ITE-IMA-05 Database organizational architecture [L3]
  • a. Demonstrate select, …, union, …, and natural join relational operations using simple example relations provided.
• National Security Agency DMS
  • 3. Apply SQL to create and administer databases and to manipulate the data they contain.

Attribution

This chapter of Database Design is a derivative copy of Database System Concepts by Nguyen Kim Anh licensed under Creative Commons Attribution License 3.0 license

The text, the images, and the questions from the two chapters were not used. For chapter 16, the questions were based on Microsoft’s PUBS sample database. Microsoft is recommending that the AdventureWorks database be used.

This chapter drew from many sources. The information was completely revised by Fred Strickland for the third edition.

Image Attributions

No second edition images were used.

References

“AdventureWorks sample databases,” Microsoft Learn, May 9, 2024. https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

Ben. “24 Database Design Mistakes and How to Avoid Them,” Database Star, September 26, 2022. https://www.databasestar.com/database-design-mistakes/

Ben. “SQL Data Types: Oracle, SQL Server, MySQL, PostgreSQL,” Database Star, October 6, 2022. https://www.databasestar.com/sql-data-types/

Ben. “The Complete Guide to Oracle Sequencies,” Database Star, June 9, 2023. https://www.databasestar.com/oracle-sequence/

“CREATE TABLE (Transact-SQL),” Microsoft Learn, March 6, 2024. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16

“DB-Engines Ranking of Relational DBMS,” DB-Engines, September 2024. https://db-engines.com/en/ranking/relational+dbms

Sherlee Dizon. “SQL Server differences of char, nchar, varchar and nvarchar data types,” MS SQL Tips, 2024. https://www.mssqltips.com/sqlservertip/4322/sql-server-differences-of-char-nchar-varchar-and-nvarchar-data-types/

“How to Download and Install AdventureWorks Database in SQL?” Geeks for Geeks, August 30, 2022. https://www.geeksforgeeks.org/how-to-download-and-install-adventureworks-database-in-sql/

Ian. “How to Delete UNIQUE Constraint in SQL Server,” Database.Guide, August 8, 2024. https://database.guide/how-to-delete-a-unique-constraint-in-sql-server/

Agnieszka Kozubek-Krycun. “The History of SQL Standards,” LearnSQL,” December 8, 2020. https://learnsql.com/blog/history-of-sql-standards/

Nancy Levin. “10 People with the Longest Names Ever,” Largest.org, January 25, 2020. https://largest.org/people/names/

“Quickstart: Connect and query a SQL Server Instance using SQL Server Management Studio (SSMS),” Learn Microsoft, February 29, 2024. https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-sql-server?view=sql-server-ver16

“SQL AUTO INCREMENT Field,” W3 Schools, 2024. https://www.w3schools.com/SQl/sql_autoincrement.asp

“SQL Commands | DDL, DQL, DML, DCL and TCL Commands,” Geeks for Geeks, August 29, 2024. https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/

“SQL Constraints,” W3 schools, 2024. https://www.w3schools.com/SQl/sql_constraints.asp

“SQL CREATE TABLE Syntax and Examples – The Complete Guide,” Database Star, June 9, 2023. https://www.databasestar.com/sql-create-table/#CREATE_TABLE_Examples

“SQL CREATE INDEX Statement,” W3 schools, 2024. https://www.w3schools.com/sql/sql_create_index.asp

“SQL DEFAULT Constraint,” W3 schools, 2024. https://www.w3schools.com/sql/sql_default.asp

“SQL NOT NULL on CREATE TABLE,” W3 schools, 2024. https://www.w3schools.com/SQl/sql_notnull.asp

“SQL PRIMARY KEY Constraint,” W3 schools, 2024. https://www.w3schools.com/sql/sql_primarykey.asp

“SQL Server: Foreign Keys with cascade delete,” Tech on the Net, 2024. https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php

Ben Snaidero has a very detailed article. I did not use it, because the example SQL used the GO key word. I wish to defer this key word to another chapter.

Ben Snaidero. “Using DELETE CASCADE Option for Foreign Keys,” MS SQL Tips, n.d. https://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/

Ranga Babu has a nice article with images. I did not use it, because I do not wish to confuse the approach by having information about the SQL Server Management Studio GUI. Also the SQL commands did not have the cascade lines in the create table; these were added to the table with the ALTER TABLE lines.

Ranga Babu. “DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key,” SQLShack, July 3, 2019. https://www.sqlshack.com/delete-cascade-and-update-cascade-in-sql-server-foreign-key/

“SQL Server UNIQUE Constraint,” SQL Server Tutorial.net, 2024. https://www.sqlservertutorial.net/sql-server-basics/sql-server-unique-constraint/

“SQL UNIQUE Constraint,” W3 schools, 2024. https://www.w3schools.com/SQl/sql_unique.asp

“What is Fourth Generation Programming Language?” Geeks for Geeks, November 13, 2022. https://www.geeksforgeeks.org/what-is-fourth-generation-programming-language/