{"id":235,"date":"2014-03-14T23:35:45","date_gmt":"2014-03-14T23:35:45","guid":{"rendered":"http:\/\/opentextbc.ca\/dbdesign01\/?post_type=chapter&#038;p=235"},"modified":"2019-06-11T16:48:09","modified_gmt":"2019-06-11T16:48:09","slug":"sql-structured-query-language","status":"publish","type":"chapter","link":"https:\/\/opentextbc.ca\/dbdesign01\/chapter\/sql-structured-query-language\/","title":{"raw":"Chapter 15 SQL Structured Query Language","rendered":"Chapter 15 SQL Structured Query Language"},"content":{"raw":"<em>Structured Query Language<\/em>\u00a0(SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called <em>SEQUEL<\/em> (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM\u2019s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.\r\n\r\nMany of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server (shown in Figure 15.1), MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL.\r\n\r\n[caption id=\"attachment_349\" align=\"aligncenter\" width=\"400\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer.jpg\"><img class=\"wp-image-349\" alt=\"SQLServer\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer.jpg\" height=\"303\" width=\"400\" \/><\/a> Figure 15.1. Example of Microsoft SQL Server, by A. Watt.[\/caption]\r\n\r\nIn a DBMS, the SQL database language is used to:\r\n<ul>\r\n \t<li>Create the database and table structures<\/li>\r\n \t<li>Perform basic data management chores (add, delete and modify)<\/li>\r\n \t<li>Perform complex queries to transform raw data into useful information<\/li>\r\n<\/ul>\r\nIn this chapter, we will focus on using SQL to create the database and table structures, mainly using SQL as a data definition language (<em>DDL<\/em>). In Chapter 16, we will use SQL as a data manipulation language (<em>DML<\/em>) to insert, delete, select and update data within the database tables.\r\n<h2>Create Database<\/h2>\r\nThe major SQL DDL statements are CREATE DATABASE and CREATE\/DROP\/ALTER TABLE. The SQL statement CREATE is used to create the database and table structures.\r\n\r\n<strong>Example: CREATE DATABASE SW<\/strong>\r\n\r\nA new database named <strong>SW<\/strong> is created by the SQL statement CREATE\u00a0 DATABASE SW. Once the database is created, the next step is to create the database tables.\r\n\r\nThe general format for the CREATE TABLE command is:\r\n<div class=\"textbox shaded\">CREATE TABLE &lt;tablename&gt;\r\n(\r\nColumnName, Datatype, Optional Column Constraint,\r\nColumnName, Datatype, Optional Column Constraint,\r\nOptional table Constraints\r\n);<\/div>\r\nTablename is the name of the database table such as <strong>Employee<\/strong>. Each field in the CREATE TABLE has three parts (see above):\r\n<ol>\r\n \t<li>ColumnName<\/li>\r\n \t<li>Data type<\/li>\r\n \t<li>Optional Column Constraint<\/li>\r\n<\/ol>\r\n<h3>ColumnName<\/h3>\r\nThe ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName.\r\n<h2>Data Type<\/h2>\r\nThe data type, as described below, must be a system data type or a user-defined data type.\u00a0Many of the data types have a size such as CHAR(35) or Numeric(8,2).\r\n\r\n<b>Bit<\/b> <b>\u2013<\/b>Integer data with either a 1 or 0 value\r\n\r\n<b>Int \u2013<\/b>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1\u00a0 (2,147,483,647)\r\n\r\n<b>Smallint \u2013<\/b>Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767)\r\n\r\n<b>Tinyint<\/b> <b>\u2013<\/b>Integer data from 0 through 255\r\n\r\n<b>Decimal \u2013<\/b>Fixed precision and scale numeric data from -10^38 -1 through 10^38\r\n\r\n<b>Numeric \u2013<\/b>A synonym for <b>decimal<\/b>\r\n\r\n<b>Timestamp \u2013<\/b>A database-wide unique number\r\n\r\n<b>Uniqueidentifier \u2013<\/b>A globally unique identifier (GUID)\r\n\r\n<b>Money <\/b>- Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to one-ten-thousandth of a monetary unit\r\n\r\n<b>Smallmoney \u2013<\/b>Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to one-ten-thousandth of a monetary unit\r\n\r\n<b>Float \u2013<\/b>Floating precision number data from -1.79E + 308 through 1.79E + 308\r\n\r\n<b>Real \u2013<\/b>Floating precision number data from -3.40E + 38 through 3.40E + 38\r\n\r\n<b>Datetime \u2013<\/b>Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one-three-hundredths of a second, or 3.33 milliseconds\r\n\r\n<b>Smalldatetime \u2013<\/b>Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute\r\n\r\n<b>Char \u2013<\/b>Fixed-length non-Unicode character data with a maximum length of 8,000 characters\r\n\r\n<b>Varchar \u2013<\/b>Variable-length non-Unicode data with a maximum of 8,000 characters\r\n\r\n<b>Text \u2013<\/b>Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters\r\n\r\n<b>Binary \u2013<\/b>Fixed-length binary data with a maximum length of 8,000 bytes\r\n\r\n<b>Varbinary \u2013<\/b>Variable-length binary data with a maximum length of 8,000 bytes\r\n\r\n<b>Image <\/b><b>\u2013<\/b><b> <\/b>Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes\r\n<h2>Optional Column Constraints<\/h2>\r\nThe Optional ColumnConstraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that null values are allowed, which means that a row can be created without a value for this column. The column constraint NOT NULL indicates that a value must be supplied when a new row is created.\r\n\r\nTo illustrate, we will use the SQL statement CREATE TABLE EMPLOYEES to create the employees table with 16 attributes or fields.\r\n<div class=\"textbox shaded\">USE SW\r\nCREATE TABLE EMPLOYEES\r\n(\r\nEmployeeNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(10)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNIQUE,\r\nDepartmentName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(30)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DEFAULT \u201cHuman Resources\u201d,\r\nFirstName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(25)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nLastName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(25)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nCategory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(20)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nHourlyRate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CURRENCY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nTimeCard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOGICAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nHourlySalaried\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(1)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nEmpType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(1)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nTerminated\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 LOGICAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nExemptCode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(2)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nSupervisor\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOGICAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nSupervisorName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nBirthDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DATE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nCollegeDegree\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(5)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nCONSTRAINT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Employee_PK\u00a0\u00a0\u00a0 PRIMARY KEY(EmployeeNo\r\n);<\/div>\r\nThe first field is EmployeeNo with a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL).\r\n\r\nSimilarly, the second field is DepartmentName with a field type CHAR of length 30. After all the table columns are defined, a table constraint, identified by the word CONSTRAINT, is used to create the primary key:\r\n<div class=\"textbox\">CONSTRAINT\u00a0\u00a0\u00a0\u00a0 EmployeePK\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY(EmployeeNo)<\/div>\r\nWe will discuss the constraint property further later in this chapter.\r\n\r\nLikewise, we can create a Department\u00a0 table,\u00a0 a Project table and an Assignment table using the CREATE TABLE SQL DDL command as shown in the below example.\r\n<div class=\"textbox shaded\">USE SW\r\nCREATE TABLE DEPARTMENT\r\n(\r\nDepartmentName Char(35)\u00a0 NOT NULL,\r\nBudgetCode\u00a0\u00a0\u00a0\u00a0 Char(30)\u00a0 NOT NULL,\r\nOfficeNumber\u00a0\u00a0 Char(15)\u00a0 NOT NULL,\r\nPhone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(15)\u00a0 NOT NULL,\r\nCONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)\r\n);<\/div>\r\nIn this example, a project table is created with seven fields: ProjectID, ProjectName, Department, MaxHours, StartDate, and EndDate.\r\n<div class=\"textbox shaded\">USE SW\r\nCREATE TABLE PROJECT\r\n(\r\nProjectID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0 NOT NULL IDENTITY (1000,100),\r\nProjectName\u00a0\u00a0\u00a0\u00a0 Char(50) NOT NULL,\r\nDepartment\u00a0\u00a0\u00a0\u00a0\u00a0 Char(35) NOT NULL,\r\nMaxHours\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Numeric(8,2)\u00a0 NOT NULL DEFAULT 100,\r\nStartDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,\r\nEndDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,\r\nCONSTRAINT\u00a0\u00a0\u00a0\u00a0\u00a0 ASSIGNMENT_PK\u00a0 PRIMARY KEY(ProjectID)\r\n);<\/div>\r\nIn this last example,<span> an assignment table is created with three fields: ProjectID, EmployeeNumber, and HoursWorked. The assignment table is used to record who (EmployeeNumber) and how much time(HoursWorked) an employee worked on the particular project(ProjectID).\r\n<\/span>\r\n<div class=\"textbox shaded\">USE SW\r\nCREATE TABLE ASSIGNMENT\r\n(\r\nProjectID\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0Int\u00a0 NOT NULL,\r\nEmployeeNumber\u00a0 Int\u00a0 NOT NULL,\r\nHoursWorked\u00a0\u00a0\u00a0\u00a0 Numeric(6,2)\u00a0 NULL,\r\n);<\/div>\r\n<h2>Table Constraints<\/h2>\r\nTable constraints are identified by the CONSTRAINT keyword and can be used to implement various constraints described below.\r\n<h3>IDENTITY constraint<\/h3>\r\nWe can use the optional column constraint IDENTITY to provide a unique, incremental value for that column. Identity columns are often used with the PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a column with a tinyint, smallint, int, decimal or numeric data type. This constraint:\r\n<ul>\r\n \t<li>Generates sequential numbers<\/li>\r\n \t<li>Does not enforce entity integrity<\/li>\r\n \t<li>Only one column can have the IDENTITY property<\/li>\r\n \t<li>Must be defined as an integer, numeric or decimal data type<\/li>\r\n \t<li>Cannot update a column with the IDENTITY property<\/li>\r\n \t<li>Cannot contain NULL\u00a0values<\/li>\r\n \t<li>Cannot bind defaults and default constraints to the column<\/li>\r\n<\/ul>\r\nFor IDENTITY[(seed, increment)]\r\n<ul>\r\n \t<li>Seed \u2013 the initial value of the identity column<\/li>\r\n \t<li>Increment \u2013 the value to add to the last increment column<\/li>\r\n<\/ul>\r\nWe will use another database example to further illustrate the SQL DDL statements by creating the table\u00a0tblHotel in this HOTEL database.\r\n<div class=\"textbox shaded\">CREATE TABLE\u00a0 tblHotel\r\n(\r\nHotelNo \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IDENTITY (1,1),\r\nName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nAddress\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,\r\nCity\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(25) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,\r\n)<\/div>\r\nUNIQUE constraint\r\n\r\nThe UNIQUE constraint prevents duplicate values from being entered into a column.\r\n<ul>\r\n \t<li>Both PK and UNIQUE constraints are used to enforce entity integrity.<\/li>\r\n \t<li>Multiple UNIQUE constraints can be defined for a table.<\/li>\r\n \t<li>When a UNIQUE constraint is added to an existing table, the existing data is always validated.<\/li>\r\n \t<li>A UNIQUE constraint can be placed on columns that accept nulls. <em>O<\/em><i>nly one row can be NULL.<\/i><\/li>\r\n \t<li>A UNIQUE constraint automatically creates a unique index on the selected column.<\/li>\r\n<\/ul>\r\nThis is the general syntax for the UNIQUE constraint:\r\n<div class=\"textbox\">[CONSTRAINT constraint_name]\r\nUNIQUE [CLUSTERED | NONCLUSTERED]\r\n(col_name [, col_name2 [\u2026, col_name16]])\r\n[ON segment_name]<\/div>\r\n<span>This is an examle using the UNIQUE constraint.\r\n<\/span>\r\n<div class=\"textbox shaded\">CREATE TABLE EMPLOYEES\r\n(\r\nEmployeeNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(10)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNIQUE,\r\n)<\/div>\r\n<h3>FOREIGN KEY constraint<\/h3>\r\nThe FOREIGN KEY (FK) constraint defines a column, or combination of columns, whose values match the PRIMARY KEY \u00a0(PK) of another table.\r\n<ul>\r\n \t<li>Values in an FK are automatically updated when the PK values in the associated table are updated\/changed.<\/li>\r\n \t<li>FK constraints must reference PK or the UNIQUE constraint of another table.<\/li>\r\n \t<li>The number of columns for FK must be same as PK or UNIQUE constraint.<\/li>\r\n \t<li>If the WITH NOCHECK option is used, the FK constraint will not validate existing data in a table.<\/li>\r\n \t<li>No index is created on the columns that participate in an FK constraint.<\/li>\r\n<\/ul>\r\nThis is the general syntax for the FOREIGN KEY constraint:\r\n<div class=\"textbox\">[CONSTRAINT constraint_name]\r\n[FOREIGN KEY (col_name [, col_name2 [\u2026, col_name16]])]\r\nREFERENCES [owner.]ref_table [(ref_col [, ref_col2 [\u2026, ref_col16]])]<\/div>\r\nIn this example, the field HotelNo in the tblRoom table is a FK to the field HotelNo in the tblHotel table shown previously.\r\n<div class=\"textbox shaded\">USE HOTEL\r\nGO\r\nCREATE TABLE\u00a0 tblRoom\r\n(\r\nHotelNo \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL ,\r\nRoomNo\u00a0 Int \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,\r\nPrice\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Money \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,\r\nPRIMARY KEY (HotelNo, RoomNo),\r\nFOREIGN KEY (HotelNo) REFERENCES tblHotel\r\n)<\/div>\r\n<h3>CHECK constraint<\/h3>\r\nThe CHECK constraint\u00a0restricts values that can be entered into a table.\r\n<ul>\r\n \t<li>It can contain search conditions similar to a WHERE clause.<\/li>\r\n \t<li>It can reference columns in the same table.<\/li>\r\n \t<li>The data validation rule for a CHECK constraint must evaluate to a boolean expression.<\/li>\r\n \t<li>It can be defined for a column that has a rule bound to it.<\/li>\r\n<\/ul>\r\nThis is the general syntax for the CHECK constraint:\r\n<div class=\"textbox\">[CONSTRAINT constraint_name]\r\nCHECK [NOT FOR REPLICATION] (expression)<\/div>\r\nIn this example, the Type field is restricted to have only the types 'Single', 'Double', 'Suite' or 'Executive'.\r\n<div class=\"textbox shaded\">USE HOTEL\r\nGO\r\nCREATE TABLE\u00a0 tblRoom\r\n(\r\nHotelNo \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nRoomNo\u00a0 Int \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,\r\nType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,\r\nPrice\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Money \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,\r\nPRIMARY KEY (HotelNo, RoomNo),\r\nFOREIGN KEY (HotelNo) REFERENCES tblHotel\r\nCONSTRAINT Valid_Type\r\nCHECK (Type IN (\u2018Single\u2019, \u2018Double\u2019, \u2018Suite\u2019, \u2018Executive\u2019))\r\n)<\/div>\r\nIn this second example, the employee hire date should be before January 1, 2004, or have a salary limit of $300,000.\r\n<div class=\"textbox shaded\">GO\r\nCREATE TABLE SALESREPS\r\n(\r\nEmpl_num \u00a0\u00a0\u00a0 Int Not Null\r\nCHECK (Empl_num BETWEEN 101 and 199),\r\nName \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char (15),\r\nAge \u00a0\u00a0\u00a0\u00a0 Int \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHECK (Age &gt;= 21),\r\nQuota \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Money \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHECK (Quota &gt;= 0.0),\r\nHireDate \u00a0\u00a0\u00a0\u00a0\u00a0 DateTime,\r\nCONSTRAINT\u00a0 QuotaCap CHECK ((HireDate &lt; \u201c01-01-2004\u201d) OR (Quota &lt;=300000))\r\n)<\/div>\r\n<h3>DEFAULT constraint<\/h3>\r\nThe DEFAULT constraint\u00a0is used to supply a value that is automatically added for a column if the user does not supply one.\r\n<ul>\r\n \t<li>A column can have only one DEFAULT.<\/li>\r\n \t<li>The DEFAULT constraint cannot be used on columns with a timestamp data type or identity property.<\/li>\r\n \t<li>DEFAULT constraints are automatically bound to a column when they are created.<\/li>\r\n<\/ul>\r\nThe general syntax for the DEFAULT constraint is:\r\n<div class=\"textbox\">[CONSTRAINT constraint_name]\r\nDEFAULT {constant_expression | niladic-function | NULL}\r\n[FOR col_name]<\/div>\r\nThis example sets the default for the city field to 'Vancouver'.\r\n<div class=\"textbox shaded\">USE HOTEL\r\nALTER TABLE tblHotel\r\nAdd CONSTRAINT df_city DEFAULT \u2018Vancouver\u2019 FOR City<\/div>\r\n<h2>User Defined Types<\/h2>\r\nUser defined types are always based on system-supplied data type. They can enforce data integrity and they allow nulls.\r\n\r\nTo create a user-defined data type in SQL Server, choose types under \"Programmability\" in your database. Next, right click and choose \u2018New\u2019 --&gt;\u2018User-defined data type\u2019 or execute the sp_addtype system stored procedure. After this, type:\r\n<div class=\"textbox\">sp_addtype ssn, 'varchar(11)', 'NOT NULL'<\/div>\r\nThis will add a new user-defined data type called SIN with nine characters.\r\n\r\nIn this example, the field EmployeeSIN uses the user-defined data type SIN.\r\n<div class=\"textbox shaded\">CREATE TABLE SINTable\r\n(\r\nEmployeeID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT Primary Key,\r\nEmployeeSIN\u00a0\u00a0\u00a0 SIN,\r\nCONSTRAINT CheckSIN\r\nCHECK (EmployeeSIN LIKE\r\n\u2018 [0-9][0-9][0-9] - [0-9][0-9] [0-9] - [0-9][0-9][0-9] \u2018)\r\n)<\/div>\r\n<h3>ALTER TABLE<b>\r\n<\/b><\/h3>\r\nYou can use ALTER TABLE statements to add and drop constraints.\r\n<ul>\r\n \t<li>ALTER TABLE\u00a0allows columns to be removed.<\/li>\r\n \t<li>When a constraint is added, all existing data are verified for violations.<\/li>\r\n<\/ul>\r\nIn this example, we use the ALTER TABLE statement to the IDENTITY property to a ColumnName field.\r\n<div class=\"textbox shaded\">USE HOTEL\r\nGO\r\nALTER TABLE\u00a0 tblHotel\r\nADD CONSTRAINT unqName UNIQUE (Name)<\/div>\r\nUse the ALTER TABLE statement to add a column with the IDENTITY property such as ALTER TABLE TableName.\r\n<div class=\"textbox shaded\">ADD\r\nColumnName \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int\u00a0\u00a0\u00a0 IDENTITY(seed, increment)<\/div>\r\n<h3>DROP TABLE<\/h3>\r\nThe DROP TABLE\u00a0will remove a table from the database. Make sure you have the correct database selected.\r\n<div class=\"textbox\">DROP TABLE tblHotel<\/div>\r\nExecuting the above SQL DROP TABLE\u00a0statement will remove the table tblHotel from the database.\r\n<div class=\"textbox textbox--key-takeaways\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Key Terms<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\">\r\n<strong>DDL<\/strong>: abbreviation for <em>data definition language<\/em>\r\n\r\n<strong>DML<\/strong>: abbreviation for <em>data manipulation language<\/em>\r\n\r\n<strong>SEQUEL<\/strong>: acronym for <em>Structured English Query Language;\u00a0<\/em>designed to manipulate and retrieve data stored in IBM\u2019s quasi-relational database management system, System R\r\n\r\n<strong>Structured Query Language\u00a0(SQL)<\/strong>:\u00a0a database language designed for managing data held in a relational database management system\r\n<\/div>\r\n<\/div>\r\n<div class=\"textbox textbox--exercises\"><header class=\"textbox__header\">\r\n<p class=\"textbox__title\">Exercises<\/p>\r\n\r\n<\/header>\r\n<div class=\"textbox__content\">\r\n\r\n<ol>\r\n \t<li>Using the information for the\u00a0Chapter 9 exercise, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.<\/li>\r\n \t<li>Create the table shown here in SQL Server and show the statements you used.\r\nTable: Employee\r\n<table style=\"color: #1f1f1d;\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td valign=\"top\" width=\"285\"><strong>ATTRIBUTE (FIELD) NAME<\/strong><\/td>\r\n<td valign=\"top\" width=\"240\"><strong>DATA DECLARATION<\/strong><\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\" width=\"285\">EMP_NUM<\/td>\r\n<td valign=\"top\" width=\"240\">CHAR(3)<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\" width=\"285\">EMP_LNAME<\/td>\r\n<td valign=\"top\" width=\"240\">VARCHAR(15)<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\" width=\"285\">EMP_FNAME<\/td>\r\n<td valign=\"top\" width=\"240\">VARCHAR(15)<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\" width=\"285\">EMP_INITIAL<\/td>\r\n<td valign=\"top\" width=\"240\">CHAR(1)<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\" width=\"285\">EMP_HIREDATE<\/td>\r\n<td valign=\"top\" width=\"240\">DATE<\/td>\r\n<\/tr>\r\n<tr>\r\n<td valign=\"top\" width=\"285\">JOB_CODE<\/td>\r\n<td valign=\"top\" width=\"240\">CHAR(3)<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/li>\r\n \t<li>Having created the table structure in question 2, write the SQL code to enter the rows for the table shown in Figure 15.1.\r\n\r\n[caption id=\"attachment_324\" align=\"alignnone\" width=\"500\"]<a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1.jpg\"><img class=\"wp-image-324\" alt=\"Ch15 Exercise Fig15.1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1.jpg\" height=\"174\" width=\"500\" \/><\/a> Figure 15.2.\u00a0Employee table with data for questions 4-10, by A. Watt.[\/caption]\r\n\r\nUse Figure 15.2 to answer questions 4 to 10.<\/li>\r\n \t<li>Write the SQL code to change the job code to 501 for the person whose personnel number is 107. After you have completed the task, examine the results, and then reset the job code to its original value.<\/li>\r\n \t<li>Assuming that the data shown in the Employee table have been entered, write the SQL code that lists all attributes for a job code of 502.<\/li>\r\n \t<li>Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (<i>Hint<\/i>: Use logical operators to include all the information given in this problem.)<\/li>\r\n \t<li>Add the attributes EMP_PCT and PROJ_NUM to the Employee table. The EMP_PCT is the bonus percentage to be paid to each employee.<\/li>\r\n \t<li>Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500.<\/li>\r\n \t<li>Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher.<\/li>\r\n \t<li>Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its original condition preceding this question.)<\/li>\r\n<\/ol>\r\n<strong>Also see<\/strong>\u00a0 <em>Appendix C: SQL Lab with Solution<\/em>\r\n\r\n<\/div><\/div>\r\n<h2>References<\/h2>\r\nDate, C.J. <em>Relational Database Selected Writings.<\/em>\u00a0Reading: Mass: Addison-Wesley Publishing Company Inc., \u00a01986, p. 269-311.","rendered":"<p><em>Structured Query Language<\/em>\u00a0(SQL) is a database language designed for managing data held in a relational database management system. SQL was initially developed by IBM in the early 1970s (Date 1986). The initial version, called <em>SEQUEL<\/em> (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM\u2019s quasi-relational database management system, System R. Then in the late 1970s, Relational Software Inc., which is now Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.<\/p>\n<p>Many of the currently available relational DBMSs, such as Oracle Database, Microsoft SQL Server (shown in Figure 15.1), MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL.<\/p>\n<figure id=\"attachment_349\" aria-describedby=\"caption-attachment-349\" style=\"width: 400px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-349\" alt=\"SQLServer\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer.jpg\" height=\"303\" width=\"400\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer.jpg 588w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer-300x227.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer-65x49.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer-225x170.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/06\/SQLServer-350x264.jpg 350w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><\/a><figcaption id=\"caption-attachment-349\" class=\"wp-caption-text\">Figure 15.1. Example of Microsoft SQL Server, by A. Watt.<\/figcaption><\/figure>\n<p>In a DBMS, the SQL database language is used to:<\/p>\n<ul>\n<li>Create the database and table structures<\/li>\n<li>Perform basic data management chores (add, delete and modify)<\/li>\n<li>Perform complex queries to transform raw data into useful information<\/li>\n<\/ul>\n<p>In this chapter, we will focus on using SQL to create the database and table structures, mainly using SQL as a data definition language (<em>DDL<\/em>). In Chapter 16, we will use SQL as a data manipulation language (<em>DML<\/em>) to insert, delete, select and update data within the database tables.<\/p>\n<h2>Create Database<\/h2>\n<p>The major SQL DDL statements are CREATE DATABASE and CREATE\/DROP\/ALTER TABLE. The SQL statement CREATE is used to create the database and table structures.<\/p>\n<p><strong>Example: CREATE DATABASE SW<\/strong><\/p>\n<p>A new database named <strong>SW<\/strong> is created by the SQL statement CREATE\u00a0 DATABASE SW. Once the database is created, the next step is to create the database tables.<\/p>\n<p>The general format for the CREATE TABLE command is:<\/p>\n<div class=\"textbox shaded\">CREATE TABLE &lt;tablename&gt;<br \/>\n(<br \/>\nColumnName, Datatype, Optional Column Constraint,<br \/>\nColumnName, Datatype, Optional Column Constraint,<br \/>\nOptional table Constraints<br \/>\n);<\/div>\n<p>Tablename is the name of the database table such as <strong>Employee<\/strong>. Each field in the CREATE TABLE has three parts (see above):<\/p>\n<ol>\n<li>ColumnName<\/li>\n<li>Data type<\/li>\n<li>Optional Column Constraint<\/li>\n<\/ol>\n<h3>ColumnName<\/h3>\n<p>The ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName.<\/p>\n<h2>Data Type<\/h2>\n<p>The data type, as described below, must be a system data type or a user-defined data type.\u00a0Many of the data types have a size such as CHAR(35) or Numeric(8,2).<\/p>\n<p><b>Bit<\/b> <b>\u2013<\/b>Integer data with either a 1 or 0 value<\/p>\n<p><b>Int \u2013<\/b>Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 &#8211; 1\u00a0 (2,147,483,647)<\/p>\n<p><b>Smallint \u2013<\/b>Integer data from 2^15 (-32,768) through 2^15 &#8211; 1 (32,767)<\/p>\n<p><b>Tinyint<\/b> <b>\u2013<\/b>Integer data from 0 through 255<\/p>\n<p><b>Decimal \u2013<\/b>Fixed precision and scale numeric data from -10^38 -1 through 10^38<\/p>\n<p><b>Numeric \u2013<\/b>A synonym for <b>decimal<\/b><\/p>\n<p><b>Timestamp \u2013<\/b>A database-wide unique number<\/p>\n<p><b>Uniqueidentifier \u2013<\/b>A globally unique identifier (GUID)<\/p>\n<p><b>Money <\/b>&#8211; Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 &#8211; 1 (+922,337,203,685,477.5807), with accuracy to one-ten-thousandth of a monetary unit<\/p>\n<p><b>Smallmoney \u2013<\/b>Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to one-ten-thousandth of a monetary unit<\/p>\n<p><b>Float \u2013<\/b>Floating precision number data from -1.79E + 308 through 1.79E + 308<\/p>\n<p><b>Real \u2013<\/b>Floating precision number data from -3.40E + 38 through 3.40E + 38<\/p>\n<p><b>Datetime \u2013<\/b>Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one-three-hundredths of a second, or 3.33 milliseconds<\/p>\n<p><b>Smalldatetime \u2013<\/b>Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute<\/p>\n<p><b>Char \u2013<\/b>Fixed-length non-Unicode character data with a maximum length of 8,000 characters<\/p>\n<p><b>Varchar \u2013<\/b>Variable-length non-Unicode data with a maximum of 8,000 characters<\/p>\n<p><b>Text \u2013<\/b>Variable-length non-Unicode data with a maximum length of 2^31 &#8211; 1 (2,147,483,647) characters<\/p>\n<p><b>Binary \u2013<\/b>Fixed-length binary data with a maximum length of 8,000 bytes<\/p>\n<p><b>Varbinary \u2013<\/b>Variable-length binary data with a maximum length of 8,000 bytes<\/p>\n<p><b>Image <\/b><b>\u2013<\/b><b> <\/b>Variable-length binary data with a maximum length of 2^31 &#8211; 1 (2,147,483,647) bytes<\/p>\n<h2>Optional Column Constraints<\/h2>\n<p>The Optional ColumnConstraints are NULL, NOT NULL, UNIQUE, PRIMARY KEY and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that null values are allowed, which means that a row can be created without a value for this column. The column constraint NOT NULL indicates that a value must be supplied when a new row is created.<\/p>\n<p>To illustrate, we will use the SQL statement CREATE TABLE EMPLOYEES to create the employees table with 16 attributes or fields.<\/p>\n<div class=\"textbox shaded\">USE SW<br \/>\nCREATE TABLE EMPLOYEES<br \/>\n(<br \/>\nEmployeeNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(10)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNIQUE,<br \/>\nDepartmentName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(30)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DEFAULT \u201cHuman Resources\u201d,<br \/>\nFirstName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(25)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nLastName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(25)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nCategory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(20)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nHourlyRate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CURRENCY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nTimeCard\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOGICAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nHourlySalaried\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(1)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nEmpType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(1)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nTerminated\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 LOGICAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nExemptCode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(2)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nSupervisor\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LOGICAL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nSupervisorName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nBirthDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DATE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nCollegeDegree\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(5)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nCONSTRAINT \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Employee_PK\u00a0\u00a0\u00a0 PRIMARY KEY(EmployeeNo<br \/>\n);<\/div>\n<p>The first field is EmployeeNo with a field type of CHAR. For this field, the field length is 10 characters, and the user cannot leave this field empty (NOT NULL).<\/p>\n<p>Similarly, the second field is DepartmentName with a field type CHAR of length 30. After all the table columns are defined, a table constraint, identified by the word CONSTRAINT, is used to create the primary key:<\/p>\n<div class=\"textbox\">CONSTRAINT\u00a0\u00a0\u00a0\u00a0 EmployeePK\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY(EmployeeNo)<\/div>\n<p>We will discuss the constraint property further later in this chapter.<\/p>\n<p>Likewise, we can create a Department\u00a0 table,\u00a0 a Project table and an Assignment table using the CREATE TABLE SQL DDL command as shown in the below example.<\/p>\n<div class=\"textbox shaded\">USE SW<br \/>\nCREATE TABLE DEPARTMENT<br \/>\n(<br \/>\nDepartmentName Char(35)\u00a0 NOT NULL,<br \/>\nBudgetCode\u00a0\u00a0\u00a0\u00a0 Char(30)\u00a0 NOT NULL,<br \/>\nOfficeNumber\u00a0\u00a0 Char(15)\u00a0 NOT NULL,<br \/>\nPhone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(15)\u00a0 NOT NULL,<br \/>\nCONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)<br \/>\n);<\/div>\n<p>In this example, a project table is created with seven fields: ProjectID, ProjectName, Department, MaxHours, StartDate, and EndDate.<\/p>\n<div class=\"textbox shaded\">USE SW<br \/>\nCREATE TABLE PROJECT<br \/>\n(<br \/>\nProjectID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0 NOT NULL IDENTITY (1000,100),<br \/>\nProjectName\u00a0\u00a0\u00a0\u00a0 Char(50) NOT NULL,<br \/>\nDepartment\u00a0\u00a0\u00a0\u00a0\u00a0 Char(35) NOT NULL,<br \/>\nMaxHours\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Numeric(8,2)\u00a0 NOT NULL DEFAULT 100,<br \/>\nStartDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,<br \/>\nEndDate\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DateTime NULL,<br \/>\nCONSTRAINT\u00a0\u00a0\u00a0\u00a0\u00a0 ASSIGNMENT_PK\u00a0 PRIMARY KEY(ProjectID)<br \/>\n);<\/div>\n<p>In this last example,<span> an assignment table is created with three fields: ProjectID, EmployeeNumber, and HoursWorked. The assignment table is used to record who (EmployeeNumber) and how much time(HoursWorked) an employee worked on the particular project(ProjectID).<br \/>\n<\/span><\/p>\n<div class=\"textbox shaded\">USE SW<br \/>\nCREATE TABLE ASSIGNMENT<br \/>\n(<br \/>\nProjectID\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0Int\u00a0 NOT NULL,<br \/>\nEmployeeNumber\u00a0 Int\u00a0 NOT NULL,<br \/>\nHoursWorked\u00a0\u00a0\u00a0\u00a0 Numeric(6,2)\u00a0 NULL,<br \/>\n);<\/div>\n<h2>Table Constraints<\/h2>\n<p>Table constraints are identified by the CONSTRAINT keyword and can be used to implement various constraints described below.<\/p>\n<h3>IDENTITY constraint<\/h3>\n<p>We can use the optional column constraint IDENTITY to provide a unique, incremental value for that column. Identity columns are often used with the PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to a column with a tinyint, smallint, int, decimal or numeric data type. This constraint:<\/p>\n<ul>\n<li>Generates sequential numbers<\/li>\n<li>Does not enforce entity integrity<\/li>\n<li>Only one column can have the IDENTITY property<\/li>\n<li>Must be defined as an integer, numeric or decimal data type<\/li>\n<li>Cannot update a column with the IDENTITY property<\/li>\n<li>Cannot contain NULL\u00a0values<\/li>\n<li>Cannot bind defaults and default constraints to the column<\/li>\n<\/ul>\n<p>For IDENTITY[(seed, increment)]<\/p>\n<ul>\n<li>Seed \u2013 the initial value of the identity column<\/li>\n<li>Increment \u2013 the value to add to the last increment column<\/li>\n<\/ul>\n<p>We will use another database example to further illustrate the SQL DDL statements by creating the table\u00a0tblHotel in this HOTEL database.<\/p>\n<div class=\"textbox shaded\">CREATE TABLE\u00a0 tblHotel<br \/>\n(<br \/>\nHotelNo \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IDENTITY (1,1),<br \/>\nName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nAddress\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,<br \/>\nCity\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(25) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,<br \/>\n)<\/div>\n<p>UNIQUE constraint<\/p>\n<p>The UNIQUE constraint prevents duplicate values from being entered into a column.<\/p>\n<ul>\n<li>Both PK and UNIQUE constraints are used to enforce entity integrity.<\/li>\n<li>Multiple UNIQUE constraints can be defined for a table.<\/li>\n<li>When a UNIQUE constraint is added to an existing table, the existing data is always validated.<\/li>\n<li>A UNIQUE constraint can be placed on columns that accept nulls. <em>O<\/em><i>nly one row can be NULL.<\/i><\/li>\n<li>A UNIQUE constraint automatically creates a unique index on the selected column.<\/li>\n<\/ul>\n<p>This is the general syntax for the UNIQUE constraint:<\/p>\n<div class=\"textbox\">[CONSTRAINT constraint_name]<br \/>\nUNIQUE [CLUSTERED | NONCLUSTERED]<br \/>\n(col_name [, col_name2 [\u2026, col_name16]])<br \/>\n[ON segment_name]<\/div>\n<p><span>This is an examle using the UNIQUE constraint.<br \/>\n<\/span><\/p>\n<div class=\"textbox shaded\">CREATE TABLE EMPLOYEES<br \/>\n(<br \/>\nEmployeeNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(10)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNIQUE,<br \/>\n)<\/div>\n<h3>FOREIGN KEY constraint<\/h3>\n<p>The FOREIGN KEY (FK) constraint defines a column, or combination of columns, whose values match the PRIMARY KEY \u00a0(PK) of another table.<\/p>\n<ul>\n<li>Values in an FK are automatically updated when the PK values in the associated table are updated\/changed.<\/li>\n<li>FK constraints must reference PK or the UNIQUE constraint of another table.<\/li>\n<li>The number of columns for FK must be same as PK or UNIQUE constraint.<\/li>\n<li>If the WITH NOCHECK option is used, the FK constraint will not validate existing data in a table.<\/li>\n<li>No index is created on the columns that participate in an FK constraint.<\/li>\n<\/ul>\n<p>This is the general syntax for the FOREIGN KEY constraint:<\/p>\n<div class=\"textbox\">[CONSTRAINT constraint_name]<br \/>\n[FOREIGN KEY (col_name [, col_name2 [\u2026, col_name16]])]<br \/>\nREFERENCES [owner.]ref_table [(ref_col [, ref_col2 [\u2026, ref_col16]])]<\/div>\n<p>In this example, the field HotelNo in the tblRoom table is a FK to the field HotelNo in the tblHotel table shown previously.<\/p>\n<div class=\"textbox shaded\">USE HOTEL<br \/>\nGO<br \/>\nCREATE TABLE\u00a0 tblRoom<br \/>\n(<br \/>\nHotelNo \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL ,<br \/>\nRoomNo\u00a0 Int \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,<br \/>\nPrice\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Money \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,<br \/>\nPRIMARY KEY (HotelNo, RoomNo),<br \/>\nFOREIGN KEY (HotelNo) REFERENCES tblHotel<br \/>\n)<\/div>\n<h3>CHECK constraint<\/h3>\n<p>The CHECK constraint\u00a0restricts values that can be entered into a table.<\/p>\n<ul>\n<li>It can contain search conditions similar to a WHERE clause.<\/li>\n<li>It can reference columns in the same table.<\/li>\n<li>The data validation rule for a CHECK constraint must evaluate to a boolean expression.<\/li>\n<li>It can be defined for a column that has a rule bound to it.<\/li>\n<\/ul>\n<p>This is the general syntax for the CHECK constraint:<\/p>\n<div class=\"textbox\">[CONSTRAINT constraint_name]<br \/>\nCHECK [NOT FOR REPLICATION] (expression)<\/div>\n<p>In this example, the Type field is restricted to have only the types &#8216;Single&#8217;, &#8216;Double&#8217;, &#8216;Suite&#8217; or &#8216;Executive&#8217;.<\/p>\n<div class=\"textbox shaded\">USE HOTEL<br \/>\nGO<br \/>\nCREATE TABLE\u00a0 tblRoom<br \/>\n(<br \/>\nHotelNo \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nRoomNo\u00a0 Int \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NOT NULL,<br \/>\nType\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char(50)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,<br \/>\nPrice\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Money \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NULL,<br \/>\nPRIMARY KEY (HotelNo, RoomNo),<br \/>\nFOREIGN KEY (HotelNo) REFERENCES tblHotel<br \/>\nCONSTRAINT Valid_Type<br \/>\nCHECK (Type IN (\u2018Single\u2019, \u2018Double\u2019, \u2018Suite\u2019, \u2018Executive\u2019))<br \/>\n)<\/div>\n<p>In this second example, the employee hire date should be before January 1, 2004, or have a salary limit of $300,000.<\/p>\n<div class=\"textbox shaded\">GO<br \/>\nCREATE TABLE SALESREPS<br \/>\n(<br \/>\nEmpl_num \u00a0\u00a0\u00a0 Int Not Null<br \/>\nCHECK (Empl_num BETWEEN 101 and 199),<br \/>\nName \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Char (15),<br \/>\nAge \u00a0\u00a0\u00a0\u00a0 Int \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHECK (Age &gt;= 21),<br \/>\nQuota \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Money \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHECK (Quota &gt;= 0.0),<br \/>\nHireDate \u00a0\u00a0\u00a0\u00a0\u00a0 DateTime,<br \/>\nCONSTRAINT\u00a0 QuotaCap CHECK ((HireDate &lt; \u201c01-01-2004\u201d) OR (Quota &lt;=300000))<br \/>\n)<\/div>\n<h3>DEFAULT constraint<\/h3>\n<p>The DEFAULT constraint\u00a0is used to supply a value that is automatically added for a column if the user does not supply one.<\/p>\n<ul>\n<li>A column can have only one DEFAULT.<\/li>\n<li>The DEFAULT constraint cannot be used on columns with a timestamp data type or identity property.<\/li>\n<li>DEFAULT constraints are automatically bound to a column when they are created.<\/li>\n<\/ul>\n<p>The general syntax for the DEFAULT constraint is:<\/p>\n<div class=\"textbox\">[CONSTRAINT constraint_name]<br \/>\nDEFAULT {constant_expression | niladic-function | NULL}<br \/>\n[FOR col_name]<\/div>\n<p>This example sets the default for the city field to &#8216;Vancouver&#8217;.<\/p>\n<div class=\"textbox shaded\">USE HOTEL<br \/>\nALTER TABLE tblHotel<br \/>\nAdd CONSTRAINT df_city DEFAULT \u2018Vancouver\u2019 FOR City<\/div>\n<h2>User Defined Types<\/h2>\n<p>User defined types are always based on system-supplied data type. They can enforce data integrity and they allow nulls.<\/p>\n<p>To create a user-defined data type in SQL Server, choose types under &#8220;Programmability&#8221; in your database. Next, right click and choose \u2018New\u2019 &#8211;&gt;\u2018User-defined data type\u2019 or execute the sp_addtype system stored procedure. After this, type:<\/p>\n<div class=\"textbox\">sp_addtype ssn, &#8216;varchar(11)&#8217;, &#8216;NOT NULL&#8217;<\/div>\n<p>This will add a new user-defined data type called SIN with nine characters.<\/p>\n<p>In this example, the field EmployeeSIN uses the user-defined data type SIN.<\/p>\n<div class=\"textbox shaded\">CREATE TABLE SINTable<br \/>\n(<br \/>\nEmployeeID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT Primary Key,<br \/>\nEmployeeSIN\u00a0\u00a0\u00a0 SIN,<br \/>\nCONSTRAINT CheckSIN<br \/>\nCHECK (EmployeeSIN LIKE<br \/>\n\u2018 [0-9][0-9][0-9] &#8211; [0-9][0-9] [0-9] &#8211; [0-9][0-9][0-9] \u2018)<br \/>\n)<\/div>\n<h3>ALTER TABLE<b><br \/>\n<\/b><\/h3>\n<p>You can use ALTER TABLE statements to add and drop constraints.<\/p>\n<ul>\n<li>ALTER TABLE\u00a0allows columns to be removed.<\/li>\n<li>When a constraint is added, all existing data are verified for violations.<\/li>\n<\/ul>\n<p>In this example, we use the ALTER TABLE statement to the IDENTITY property to a ColumnName field.<\/p>\n<div class=\"textbox shaded\">USE HOTEL<br \/>\nGO<br \/>\nALTER TABLE\u00a0 tblHotel<br \/>\nADD CONSTRAINT unqName UNIQUE (Name)<\/div>\n<p>Use the ALTER TABLE statement to add a column with the IDENTITY property such as ALTER TABLE TableName.<\/p>\n<div class=\"textbox shaded\">ADD<br \/>\nColumnName \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int\u00a0\u00a0\u00a0 IDENTITY(seed, increment)<\/div>\n<h3>DROP TABLE<\/h3>\n<p>The DROP TABLE\u00a0will remove a table from the database. Make sure you have the correct database selected.<\/p>\n<div class=\"textbox\">DROP TABLE tblHotel<\/div>\n<p>Executing the above SQL DROP TABLE\u00a0statement will remove the table tblHotel from the database.<\/p>\n<div class=\"textbox textbox--key-takeaways\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Key Terms<\/p>\n<\/header>\n<div class=\"textbox__content\">\n<strong>DDL<\/strong>: abbreviation for <em>data definition language<\/em><\/p>\n<p><strong>DML<\/strong>: abbreviation for <em>data manipulation language<\/em><\/p>\n<p><strong>SEQUEL<\/strong>: acronym for <em>Structured English Query Language;\u00a0<\/em>designed to manipulate and retrieve data stored in IBM\u2019s quasi-relational database management system, System R<\/p>\n<p><strong>Structured Query Language\u00a0(SQL)<\/strong>:\u00a0a database language designed for managing data held in a relational database management system\n<\/div>\n<\/div>\n<div class=\"textbox textbox--exercises\">\n<header class=\"textbox__header\">\n<p class=\"textbox__title\">Exercises<\/p>\n<\/header>\n<div class=\"textbox__content\">\n<ol>\n<li>Using the information for the\u00a0Chapter 9 exercise, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.<\/li>\n<li>Create the table shown here in SQL Server and show the statements you used.<br \/>\nTable: Employee<\/p>\n<table style=\"color: #1f1f1d; border-spacing: 0px;\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\"><strong>ATTRIBUTE (FIELD) NAME<\/strong><\/td>\n<td valign=\"top\" style=\"width: 240px;\"><strong>DATA DECLARATION<\/strong><\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\">EMP_NUM<\/td>\n<td valign=\"top\" style=\"width: 240px;\">CHAR(3)<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\">EMP_LNAME<\/td>\n<td valign=\"top\" style=\"width: 240px;\">VARCHAR(15)<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\">EMP_FNAME<\/td>\n<td valign=\"top\" style=\"width: 240px;\">VARCHAR(15)<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\">EMP_INITIAL<\/td>\n<td valign=\"top\" style=\"width: 240px;\">CHAR(1)<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\">EMP_HIREDATE<\/td>\n<td valign=\"top\" style=\"width: 240px;\">DATE<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\" style=\"width: 285px;\">JOB_CODE<\/td>\n<td valign=\"top\" style=\"width: 240px;\">CHAR(3)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/li>\n<li>Having created the table structure in question 2, write the SQL code to enter the rows for the table shown in Figure 15.1.<br \/>\n<figure id=\"attachment_324\" aria-describedby=\"caption-attachment-324\" style=\"width: 500px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-324\" alt=\"Ch15 Exercise Fig15.1\" src=\"http:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1.jpg\" height=\"174\" width=\"500\" srcset=\"https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1.jpg 465w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1-300x104.jpg 300w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1-65x22.jpg 65w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1-225x78.jpg 225w, https:\/\/opentextbc.ca\/dbdesign01\/wp-content\/uploads\/sites\/11\/2014\/03\/Ch15-Exercise-Fig15.1-350x121.jpg 350w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><\/a><figcaption id=\"caption-attachment-324\" class=\"wp-caption-text\">Figure 15.2.\u00a0Employee table with data for questions 4-10, by A. Watt.<\/figcaption><\/figure>\n<p>Use Figure 15.2 to answer questions 4 to 10.<\/li>\n<li>Write the SQL code to change the job code to 501 for the person whose personnel number is 107. After you have completed the task, examine the results, and then reset the job code to its original value.<\/li>\n<li>Assuming that the data shown in the Employee table have been entered, write the SQL code that lists all attributes for a job code of 502.<\/li>\n<li>Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (<i>Hint<\/i>: Use logical operators to include all the information given in this problem.)<\/li>\n<li>Add the attributes EMP_PCT and PROJ_NUM to the Employee table. The EMP_PCT is the bonus percentage to be paid to each employee.<\/li>\n<li>Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500.<\/li>\n<li>Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 25 for all employees whose job classification (JOB_CODE) is 502 or higher.<\/li>\n<li>Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its original condition preceding this question.)<\/li>\n<\/ol>\n<p><strong>Also see<\/strong>\u00a0 <em>Appendix C: SQL Lab with Solution<\/em><\/p>\n<\/div>\n<\/div>\n<h2>References<\/h2>\n<p>Date, C.J. <em>Relational Database Selected Writings.<\/em>\u00a0Reading: Mass: Addison-Wesley Publishing Company Inc., \u00a01986, p. 269-311.<\/p>\n","protected":false},"author":5,"menu_order":15,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["adrienne-watt-nelson-eng"],"pb_section_license":""},"chapter-type":[],"contributor":[48],"license":[],"class_list":["post-235","chapter","type-chapter","status-publish","hentry","contributor-adrienne-watt-nelson-eng"],"part":3,"_links":{"self":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/users\/5"}],"version-history":[{"count":27,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/235\/revisions"}],"predecessor-version":[{"id":1154,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/235\/revisions\/1154"}],"part":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapters\/235\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/media?parent=235"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/pressbooks\/v2\/chapter-type?post=235"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/contributor?post=235"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/opentextbc.ca\/dbdesign01\/wp-json\/wp\/v2\/license?post=235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}