Write Sql Statements: to Update the Publisher Name From Read With Us to Read for Us

Main Body

Chapter fifteen SQL Structured Query Linguistic communication

Adrienne Watt & Nelson Eng

Structured Query Language (SQL) is a database language designed for managing information held in a relational database management system. SQL was initially developed past IBM in the early 1970s (Engagement 1986). The initial version, called SEQUEL (Structured English language Query Language), was designed to manipulate and retrieve information stored in IBM's quasi-relational database management organization, System R. And then in the late 1970s, Relational Software Inc., which is at present Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 for VAX computers.

Many of the currently available relational DBMSs, such every bit Oracle Database, Microsoft SQL Server (shown in Effigy 15.one), MySQL, IBM DB2, IBM Informix and Microsoft Access, use SQL.

In a DBMS, the SQL database language is used to:

Create the database and table structures
Perform basic data management chores (add, delete and modify)
Perform complex queries to transform raw data into useful information

In this affiliate, we will focus on using SQL to create the database and tabular array structures, mainly using SQL as a information definition linguistic communication (DDL). In Chapter 16, we volition apply SQL as a information manipulation linguistic communication (DML) to insert, delete, select and update data inside the database tables.

Create Database

The major SQL DDL statements are CREATE DATABASE and CREATE/DROP/ALTER Tabular array. The SQL argument CREATE is used to create the database and table structures.

Example: CREATE DATABASE SW

A new database named SW is created by the SQL statement CREATE DATABASE SW. Once the database is created, the next pace is to create the database tables.

The full general format for the CREATE TABLE control is:

CREATE Table <tablename>
(
ColumnName, Datatype, Optional Column Constraint,
ColumnName, Datatype, Optional Column Constraint,
Optional tabular array Constraints
);

Tablename is the proper name of the database table such as Employee. Each field in the CREATE Table has three parts (come across to a higher place):

ColumnName
Data type
Optional Cavalcade Constraint

ColumnName

The ColumnName must be unique within the table. Some examples of ColumnNames are FirstName and LastName.

Data Type

The information type, every bit described below, must exist a system information type or a user-divers information type. Many of the data types take a size such as CHAR(35) or Numeric(8,2).

Chip –Integer data with either a 1 or 0 value

Int –Integer (whole number) information from -2^31 (-2,147,483,648) through two^31 – i (2,147,483,647)

Smallint –Integer information from two^15 (-32,768) through 2^15 – 1 (32,767)

Tinyint –Integer information from 0 through 255

Decimal –Fixed precision and calibration numeric information from -ten^38 -i through ten^38

Numeric –A synonym for decimal

Timestamp –A database-wide unique number

Uniqueidentifier –A globally unique identifier (GUID)

Money – Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 – 1 (+922,337,203,685,477.5807), with accurateness to one-ten-thousandth of a budgetary unit

Smallmoney –Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to ane-ten-thousandth of a monetary unit

Float –Floating precision number data from -one.79E + 308 through 1.79E + 308

Existent –Floating precision number data from -three.40E + 38 through iii.40E + 38

Datetime –Engagement and time data from Jan 1, 1753, to December 31, 9999, with an accuracy of ane-three-hundredths of a 2nd, or three.33 milliseconds

Smalldatetime –Date and time information from January one, 1900, through June 6, 2079, with an accuracy of ane minute

Char –Fixed-length non-Unicode character information with a maximum length of 8,000 characters

Varchar –Variable-length non-Unicode data with a maximum of 8,000 characters

Text –Variable-length non-Unicode data with a maximum length of two^31 – 1 (two,147,483,647) characters

Binary –Stock-still-length binary data with a maximum length of eight,000 bytes

Varbinary –Variable-length binary information with a maximum length of 8,000 bytes

Prototype – Variable-length binary information with a maximum length of 2^31 – ane (2,147,483,647) bytes

Optional Cavalcade Constraints

The Optional ColumnConstraints are Naught, Not NULL, UNIQUE, Primary Primal and DEFAULT, used to initialize a value for a new record. The column constraint NULL indicates that nil values are allowed, which means that a row can be created without a value for this column. The column constraint Not Nix indicates that a value must be supplied when a new row is created.

To illustrate, we will utilize the SQL statement CREATE Table EMPLOYEES to create the employees table with 16 attributes or fields.

USE SW
CREATE Tabular array EMPLOYEES
(
EmployeeNo                      CHAR(10)             Not NULL           UNIQUE,
DepartmentName            CHAR(xxx)             Non NULL           DEFAULT "Man Resources",
FirstName                           CHAR(25)             NOT NULL,
LastName                            CHAR(25)             Not NULL,
Category                              CHAR(20)             Non NULL,
HourlyRate                         CURRENCY          Non NULL,
TimeCard                             LOGICAL              NOT NULL,
HourlySalaried                   CHAR(i)               Non Cipher,
EmpType                             CHAR(i)               Non NULL,
Terminated                       LOGICAL              Not Zippo,
ExemptCode                       CHAR(two)               NOT Goose egg,
Supervisor                           LOGICAL              NOT Cipher,
SupervisorName                CHAR(l)             Non Zippo,
BirthDate                             DATE                     Non NULL,
CollegeDegree                     CHAR(5)               NOT Zippo,
CONSTRAINT                     Employee_PK    Master Central(EmployeeNo
);

The start field is EmployeeNo with a field type of CHAR. For this field, the field length is x characters, and the user cannot leave this field empty (Not NULL).

Similarly, the second field is DepartmentName with a field blazon CHAR of length 30. Afterwards all the table columns are defined, a tabular array constraint, identified by the word CONSTRAINT, is used to create the principal key:

CONSTRAINT EmployeePK Primary Fundamental(EmployeeNo)

We will discuss the constraint property further later in this chapter.

Also, nosotros can create a Department table, a Projection table and an Assignment table using the CREATE Table SQL DDL control as shown in the below case.

USE SW
CREATE TABLE DEPARTMENT
(
DepartmentName Char(35) NOT NULL,
BudgetCode     Char(30) Non NULL,
OfficeNumber   Char(fifteen) NOT NULL,
Phone          Char(fifteen) NOT NULL,
CONSTRAINT DEPARTMENT_PK Main KEY(DepartmentName)
);

In this example, a project table is created with 7 fields: ProjectID, ProjectName, Section, MaxHours, StartDate, and EndDate.

Utilise SW
CREATE Table Project
(
ProjectID       Int Not NULL IDENTITY (k,100),
ProjectName     Char(fifty) NOT NULL,
Section      Char(35) Not NULL,
MaxHours        Numeric(8,2) NOT NULL DEFAULT 100,
StartDate       DateTime Zippo,
EndDate         DateTime NULL,
CONSTRAINT      ASSIGNMENT_PK Principal KEY(ProjectID)
);

In this concluding example, an assignment tabular array is created with three fields: ProjectID, EmployeeNumber, and HoursWorked. The assignment tabular array is used to tape who (EmployeeNumber) and how much time(HoursWorked) an employee worked on the particular project(ProjectID).

USE SW
CREATE TABLE ASSIGNMENT
(
ProjectID Int Non Aught,
EmployeeNumber Int NOT NULL,
HoursWorked Numeric(6,2) Nothing,
);

Table Constraints

Table constraints are identified by the CONSTRAINT keyword and tin be used to implement various constraints described below.

IDENTITY constraint

We can use the optional column constraint IDENTITY to provide a unique, incremental value for that column. Identity columns are frequently used with the Primary KEY constraints to serve as the unique row identifier for the tabular array. The IDENTITY holding can exist assigned to a column with a tinyint, smallint, int, decimal or numeric data type. This constraint:

Generates sequential numbers
Does not enforce entity integrity
But one cavalcade can have the IDENTITY holding
Must be divers every bit an integer, numeric or decimal data type
Cannot update a column with the IDENTITY belongings
Cannot contain NULL values
Cannot bind defaults and default constraints to the column

For IDENTITY[(seed, increment)]

Seed – the initial value of the identity column
Increment – the value to add together to the terminal increment cavalcade

Nosotros will apply another database case to further illustrate the SQL DDL statements by creating the table tblHotel in this HOTEL database.

CREATE TABLE tblHotel
(
HotelNo                 Int                    IDENTITY (ane,ane),
Name                    Char(l)          NOT Null,
Address                 Char(l)          Nada,
City                       Char(25)          NULL,
)

UNIQUE constraint

The UNIQUE constraint prevents duplicate values from being entered into a cavalcade.

Both PK and UNIQUE constraints are used to enforce entity integrity.
Multiple UNIQUE constraints can be defined for a tabular array.
When a UNIQUE constraint is added to an existing table, the existing data is always validated.
A UNIQUE constraint tin be placed on columns that accept nulls. O nly one row can be NULL.
A UNIQUE constraint automatically creates a unique index on the selected column.

This is the full general syntax for the UNIQUE constraint:

[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED]
(col_name [, col_name2 […, col_name16]])
[ON segment_name]

This is an examle using the UNIQUE constraint.

CREATE TABLE EMPLOYEES
(
EmployeeNo CHAR(10) Non Null UNIQUE,
)

Foreign KEY constraint

The Foreign KEY (FK) constraint defines a column, or combination of columns, whose values lucifer the Master KEY (PK) of another tabular array.

Values in an FK are automatically updated when the PK values in the associated table are updated/changed.
FK constraints must reference PK or the UNIQUE constraint of another table.
The number of columns for FK must be same every bit PK or UNIQUE constraint.
If the WITH NOCHECK pick is used, the FK constraint will not validate existing data in a table.
No index is created on the columns that participate in an FK constraint.

This is the general syntax for the Strange KEY constraint:

[CONSTRAINT constraint_name]
[FOREIGN KEY (col_name [, col_name2 […, col_name16]])]
REFERENCES [owner.]ref_table [(ref_col [, ref_col2 […, ref_col16]])]

In this case, the field HotelNo in the tblRoom table is a FK to the field HotelNo in the tblHotel table shown previously.

USE HOTEL
Get
CREATE Table tblRoom
(
HotelNo           Int                    NOT Zippo ,
RoomNo Int                    Not NULL,
Blazon                    Char(50)          Nix,
Price                   Money             NULL,
Chief KEY (HotelNo, RoomNo),
FOREIGN Key (HotelNo) REFERENCES tblHotel
)

Bank check constraint

The Check constraint restricts values that can be entered into a tabular array.

It can contain search conditions similar to a WHERE clause.
Information technology can reference columns in the same table.
The information validation rule for a Check constraint must evaluate to a boolean expression.
It tin can exist defined for a column that has a rule spring to it.

This is the general syntax for the Cheque constraint:

[CONSTRAINT constraint_name]
Check [Not FOR REPLICATION] (expression)

In this instance, the Type field is restricted to take but the types 'Single', 'Double', 'Suite' or 'Executive'.

Utilize HOTEL
GO
CREATE Tabular array tblRoom
(
HotelNo           Int                    NOT Aught,
RoomNo Int                    NOT Zippo,
Type                    Char(50)          Cypher,
Toll       Coin                         Aught,
Primary KEY (HotelNo, RoomNo),
FOREIGN Central (HotelNo) REFERENCES tblHotel
CONSTRAINT Valid_Type
Cheque (Type IN ('Single', 'Double', 'Suite', 'Executive'))
)

In this 2nd example, the employee rent engagement should exist before Jan one, 2004, or accept a bacon limit of $300,000.

GO
CREATE TABLE SALESREPS
(
Empl_num     Int Not Nil
CHECK (Empl_num BETWEEN 101 and 199),
Name              Char (fifteen),
Age      Int        CHECK (Historic period >= 21),
Quota                         Coin                         CHECK (Quota >= 0.0),
HireDate       DateTime,
CONSTRAINT QuotaCap Bank check ((HireDate < "01-01-2004") OR (Quota <=300000))
)

DEFAULT constraint

The DEFAULT constraint is used to supply a value that is automatically added for a column if the user does not supply ane.

A column tin have just ane DEFAULT.
The DEFAULT constraint cannot be used on columns with a timestamp data type or identity property.
DEFAULT constraints are automatically jump to a cavalcade when they are created.

The general syntax for the DEFAULT constraint is:

[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL}
[FOR col_name]

This instance sets the default for the urban center field to 'Vancouver'.

Utilise HOTEL
Alter TABLE tblHotel
Add CONSTRAINT df_city DEFAULT 'Vancouver' FOR City

User Defined Types

User divers types are always based on system-supplied data blazon. They tin can enforce data integrity and they allow nulls.

To create a user-defined data type in SQL Server, choose types nether "Programmability" in your database. Next, right click and choose 'New' –>'User-divers data type' or execute the sp_addtype organization stored procedure. After this, blazon:

sp_addtype ssn, 'varchar(11)', 'Not Aught'

This will add together a new user-divers data blazon chosen SIN with nine characters.

In this example, the field EmployeeSIN uses the user-defined information blazon SIN.

CREATE TABLE SINTable
(
EmployeeID INT Primary Primal,
EmployeeSIN SIN,
CONSTRAINT CheckSIN
CHECK (EmployeeSIN LIKE
' [0-9][0-nine][0-9] – [0-9][0-9] [0-9] – [0-nine][0-9][0-9] ')
)

Alter Tabular array

You can use Change Tabular array statements to add and drop constraints.

Modify Table allows columns to be removed.
When a constraint is added, all existing data are verified for violations.

In this example, we apply the Modify TABLE argument to the IDENTITY property to a ColumnName field.

USE HOTEL
Get
Modify Table tblHotel
Add together CONSTRAINT unqName UNIQUE (Proper noun)

Employ the ALTER TABLE statement to add together a cavalcade with the IDENTITY property such as Change Table TableName.

ADD
ColumnName int IDENTITY(seed, increment)

Drop TABLE

The DROP TABLE will remove a table from the database. Make sure you have the correct database selected.

Driblet TABLE tblHotel

Executing the higher up SQL Drib TABLE statement will remove the table tblHotel from the database.

DDL: abridgement for information definition language

DML: abbreviation for data manipulation language

SEQUEL: acronym for Structured English Query Language;designed to manipulate and retrieve information stored in IBM's quasi-relational database direction system, System R

Structured Query Language (SQL): a database language designed for managing data held in a relational database management system

Using the data for the Chapter 9 practise, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.

Create the table shown here in SQL Server and show the statements you lot used.
Table: Employee

Attribute (FIELD) NAME	Information Declaration
EMP_NUM	CHAR(three)
EMP_LNAME	VARCHAR(15)
EMP_FNAME	VARCHAR(xv)
EMP_INITIAL	CHAR(1)
EMP_HIREDATE	DATE
JOB_CODE	CHAR(3)

Having created the tabular array construction in question ii, write the SQL code to enter the rows for the table shown in Figure 15.1.

Figure 15.two. Employee table with information for questions 4-10, by A. Watt.

Utilize Effigy xv.2 to answer questions four to 10.
Write the SQL code to change the job lawmaking to 501 for the person whose personnel number is 107. After y'all have completed the chore, examine the results, and then reset the job code to its original value.
Assuming that the data shown in the Employee table have been entered, write the SQL code that lists all attributes for a job lawmaking of 502.
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. (Hint: Use logical operators to include all the data given in this problem.)
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.
Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = eighteen for all employees whose chore nomenclature (JOB_CODE) is 500.
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.
Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before Jan 1, 1994, and whose job code is at least 501. (You lot may presume that the table will be restored to its original condition preceding this question.)

As well run into Appendix C: SQL Lab with Solution

References

Engagement, C.J. Relational Database Selected Writings. Reading: Mass: Addison-Wesley Publishing Company Inc., 1986, p. 269-311.

pritchardobace1952.blogspot.com

Source: https://opentextbc.ca/dbdesign01/chapter/sql-structured-query-language/