SQL Concepts
Database Languages
Characteristics of SQL
SQL Server Object Names
Delimited Identifiers
SQL’s DDL Statements
Creating Tables
Creating User-Defined Data Types
Data Integrity
Constraints
The PRIMARY KEY Constraint
Syntax When Creating a Table
Column-Level Constraint
Table-Level Constraint
Syntax After Creating a Table
The FOREIGN KEY Constraint
Syntax When Creating a Table
Column-Level Constraint
Table-Level Constraint
Syntax After Creating a Table
The CHECK Constraint
The DEFAULT Constraint
The UNIQUE Constraint
SQL’s DML Statements
The SELECT Statement
Selecting All Columns and All Rows
Selecting Specific Columns and All Rows
Selecting Specific Rows
Comparison Operators
OperatorMeaning
Examples
Using AND
Using OR
Operator Precedence
Changing Precedence
Example
Eliminating Duplicate Rows
Controlling the Order of Displayed Rows
Examples
Inserting Data in Only Some Columns
SELECT for Joining Tables
Cartesian Product
Examples
Using Pseudonyms (Aliases)
Views
Database Languages
SQL, often known as a query language, is a combined DDL, DML and DCL used with relational databases.
- A Data Definition Language (DDL) is used to specify the data in a database.
- A Data Manipulation Language (DML) is used to access the data in a database.
- A Data Control Language (DCL) is used to control access to the data in a database.
Characteristics of SQL
- The DDL statements define database objects, eg databases, tables, views, indexes, users, constraints, user-defined data types:
CREATE, DROP, ALTER
- The DML statements manipulate data:
SELECT, INSERT, DELETE, UPDATE
- The DCL statements control access to data:
GRANT, DENY, REVOKE
Transact-SQL is the SQL Server implementation of SQL.
SQL Server Object Names
Standard Identifiers
- Can contain from one to 128 characters, including letters, symbols (_ @ or #) and numbers.
- No embedded spaces are allowed.
- The first character must be alphabetic.
- A name beginning with @ denotes a local variable or parameter.
- A name beginning with # denotes a temporary table or procedure.
- A name beginning with ## denotes a global temporary object.
NB:Names for temporary objects shouldn’t exceed 116 characters including # or ## as SQL Server gives them an internal numeric suffix.
Delimited Identifiers
Do not comply with the rules for standard identifiers and must, therefore, always be delimited.
You can use delimited identifiers when:
- Names contain embedded spaces.
- Reserved words are used for object names or portions of object names.
You must enclose delimited identifiers in square brackets or quotation marks when you use them in Transact-SQL statements, eg:
SELECT * FROM [Blanks In Table Name]
SELECT * FROM “Blanks In Table Name”
NB:You can always use bracketed delimiters but can only use quotation marks if the SET QUOTED_IDENTIFIER option is on.
SQL’s DDL Statements
Creating Tables
In SQL Server you can have up to:
- Two billion tables per database.
- 1,024 columns per table.
- 8060 bytes per row (this maximum length does not apply to image, text, and ntext data types).
The CREATE TABLE statement creates a new table. The simplest format of the statement is:
CREATE TABLE Table_name
(Column_name1 Data_type1,
Column_name2 Data_type2, ….);
Before creating a table you need to decide on the table name, column names, data types and widths.
Example
CREATE TABLE Student
(Studentno CHAR(9) NOT NULL,
Title VARCHAR(6) NULL,
Fname VARCHAR(15) NOT NULL,
Surname VARCHAR(15) NOT NULL,
DOB DATETIME NULL,
Telno VARCHAR(12) NULL);
The NULL/NOT NULL option specifies whether NULL values are allowed for a column.
Deleting a Table
DROP TABLE Table_name;
DROP TABLE Student;
Dropping a table removes the table definition and all data. A dropped table is unrecoverable.
Adding a Column to a Table
ALTER TABLE Table_name
ADD Column_name Data_type;
ALTER TABLE Student
ADD Email CHAR(20) NULL;
Deleting a Column from a Table
ALTER TABLE Table_name
DROP COLUMN Column_name;
ALTER TABLE Student
DROP COLUMN Email;
Altering a Column
ALTER TABLE Table_name
ALTER COLUMN Column_name Data_type;
ALTER TABLE Student
ALTER COLUMN Email VARCHAR(30) NULL;
Creating User-Defined Data Types
User-defined data types allow you to refine system-supplied data types. Data type names must follow the rules for identifier names.
User-defined data types are defined for a specific database, but those that you create in the model database are automatically included in all databases that are subsequently created.
You define each user-defined data type in terms of a system-supplied data type, specifying NULL or NOT NULL.
An existing user-defined data type can be used with the CREATE TABLE or ALTER TABLE statements to define one or more columns.
Creating a User-defined Data Type
Use the sp_addtype system stored procedure.
Example
EXEC sp_addtype City_dt, 'VARCHAR(15)', NULL;
EXEC sp_addtype Studentno_dt, 'CHAR(9)',
‘NOT NULL’;
Using a User-Defined Data Type
Having created a user-defined data type, it can be used to define a column’s data type and width, eg:
CREATE TABLE Student
(Studentno studentno_dt, …
Dropping a User-Defined Data Type
Use the sp_droptype system stored procedure.
A user-defined data type cannot be dropped if tables or other database objects reference it.
Example
EXEC sp_droptype city;
Data Integrity
Data integrity refers to the consistency and accuracy of data that is stored in a database.
Constraints are the preferred method of enforcing data integrity in a database.
Entity (Table) Integrity
Requires that all rows in a table have a unique identifier, known as the PRIMARY KEY.
Enforced through the use of the PRIMARY KEY constraint, which ensures uniqueness of the column(s) and ensures that no column that is part of the primary key can contain a NULL value.
Referential Integrity
Ensures that the relationship between a primary key and a foreign key is always maintained.
Enforced through the use of the FOREIGN KEY constraint, which ensures that a value in a foreign key must match an existing value in the referenced primary key or be NULL.
Domain (Column) Integrity
Specifies a set of data values that are valid for a column and determines whether NULLs are allowed.
Enforced through definition of the data type of a column, also through use of the CHECK and DEFAULT constraints, NULL/NOT NULL.
The CHECK constraint restricts the data that users can enter into a particular column to specific values.
A DEFAULT constraint enters a value in a column when one is not specified in an INSERT statement.
Constraints
You can create a constraint:
- When the table is created, as part of the CREATE TABLE statement.
- After the table has been created, by using the ALTER TABLE statement.
Always name a constraint or SQL Server will give it a complicated system-generated name.
- A column-level constraint is defined within a column definition. It must apply to a single column.
- A table-level constraint is defined at the end of the CREATE TABLE or ALTER TABLE statement, after the definition of all columns. If a constraint references multiple columns, it must be defined as a table-level constraint.
The PRIMARY KEY Constraint
Syntax When Creating a Table
Column-Level Constraint
Can only be used when the PRIMARY KEY consists of only one column.
CREATE TABLE Table_name
(Column_name1 Data_type1
CONSTRAINT Constraint_name PRIMARY KEY,
Column_name2 Data_type2, ….);
CREATE TABLE Student
(Studentno CHAR(9)
CONSTRAINT Student_PK PRIMARY KEY,
Title VARCHAR(6) NULL,
Fname VARCHAR(15) NOT NULL,
Surname VARCHAR(15) NOT NULL,
DOB DATETIME NULL,
Telno VARCHAR(12) NULL);
Table-Level Constraint
Must be used when the PRIMARY KEY consists of more than one column.
May be used when the PRIMARY KEY consists of only one column.
CREATE TABLE Table_name
(Column_name1 Data_type1,
Column_name2 Data_type2, ….,
CONSTRAINT Constraint_name
PRIMARY KEY
(Column_name1, Column_name2, …));
CREATE TABLE Student
(Studentno CHAR(9),
Title VARCHAR(6) NULL,
Fname VARCHAR(15) NOT NULL,
Surname VARCHAR(15) NOT NULL,
DOB DATETIME NULL,
Telno VARCHAR(12) NULL,
CONSTRAINT Student_PK PRIMARY KEY (Studentno));
CREATE TABLE CourseStudent
(Courseno TINYINT,
Studentno CHAR(9),
CONSTRAINT CourseStudent_PK
PRIMARY KEY (Courseno, Studentno));
- Only one PRIMARY KEY constraint can be defined per table.
- The PRIMARY KEY constraint creates a unique index on the specified column(s).
Syntax After Creating a Table
You must use the ALTER TABLE statement.
ALTER TABLE Table_name
ADD CONSTRAINT Constraint_name
PRIMARY KEY
(Column_name1, Column_name2, …);
Examples
ALTER TABLE Student
ADD CONSTRAINT Student_PK
PRIMARY KEY (Studentno);
ALTER TABLE CourseStudent
ADD CONSTRAINT CourseStudent_PK
PRIMARY KEY (Courseno, Studentno);
The FOREIGN KEY Constraint
Syntax When Creating a Table
Column-Level Constraint
Can only be used when the FOREIGN KEY consists of only one column.
CREATE TABLE Table_name
(Column_name1 Data_type1,
Column_name2 Data_type2 CONSTRAINT
Constraint_name FOREIGN KEY
REFERENCES Table_name (Column_name),
Column_name3 Data_type3, ….);
CREATE TABLE TutorStudent
(Tutorno INT
CONSTRAINT TutorStudent_Tutorno_FK
FOREIGN KEY
REFERENCES Tutor (Tutorno),
Studentno CHAR(9)
CONSTRAINT TutorStudent_Studentno_FK
FOREIGN KEY
REFERENCES Student (Studentno));
Table-Level Constraint
Must be used when the FOREIGN KEY consists of more than one column.
May be used when the FOREIGN KEY consists of only one column.
Example
CREATE TABLE TutorStudent
(Tutorno INT,
Studentno CHAR(9),
CONSTRAINT TutorStudent_Tutorno_FK
FOREIGN KEY (Tutorno)
REFERENCES Tutor (Tutorno),
CONSTRAINT TutorStudent_Studentno_FK
FOREIGN KEY (Studentno)
REFERENCES Student (Studentno));
Syntax After Creating a Table
You must use the ALTER TABLE statement.
Example
ALTER TABLE TutorStudent
ADD CONSTRAINT TutorStudent_Tutorno_FK
FOREIGN KEY (Tutorno)
REFERENCES Tutor (Tutorno);
- Data type(s) in the FK column(s) must match data type(s) in the referenced PK column(s).
- A row in a referenced table cannot be deleted, nor the PK changed, if a FK refers to the row, unless the CASCADE option is specified.
- FOREIGN KEY constraints don’t create indexes automatically, so if you’ll be joining these tables often you should create an index on FOREIGN KEY column(s) to improve join performance.
The CHECK Constraint
Column-Level Constraint When Creating a Table
CONSTRAINT Constraint_name
CHECK (Condition);
CREATE TABLE Tutor
(Tutorno INT, …..
Job VARCHAR(20),
Salary INT
CONSTRAINT Tutor_Salary_Check
CHECK (Salary BETWEEN 17000 AND 35000));
The CHECK Constraint:
- Verifies data every time that you execute an INSERT or UPDATE statement.
- Is like WHERE clauses in that you specify the conditions under which data will be accepted.
- Can reference other columns in the same table, eg Salary could reference a value in Job.
Table-Level ConstraintWhen Creating a Table
CONSTRAINT Constraint_name
CHECK (Condition);
CREATE TABLE Tutor
(Tutorno INT, …..
Job VARCHAR(20),
Salary INT,
CONSTRAINT Tutor_Salary_Check
CHECK (Salary BETWEEN 17000 AND 35000));
The comma makes all the difference!
CHECK Constraint After Creating a Table
You must use the ALTER TABLE command.
ALTER TABLE Tutor
ADD CONSTRAINT Tutor_Salary_Check
CHECK (Salary BETWEEN 17000 AND 35000);
The DEFAULT Constraint
A DEFAULT constraint can only be defined as a column-level constraint.
CREATE TABLE Student
(Studentno CHAR(9), …..
TermstreetVARCHAR(30),
TermtownVARCHAR(30)
CONSTRAINT Student_Termtime_DF
DEFAULT ‘Middlesbrough’, …..);
ALTER TABLE Student
ADD CONSTRAINT Student_Termtime_DF
DEFAULT ‘Middlesbrough’ FOR TermTown;
- Only one DEFAULT constraint can be defined per column.
- A DEFAULT constraint applies only to INSERT statements.
The UNIQUE Constraint
A UNIQUE constraint designates a column or columns as unique so that no 2 rows in a table can have the same value for this column or columns.
Column-Level Constraint When Creating a Table
CREATE TABLE Course
(Courseno TINYINT,
Coursename VARCHAR(25)
CONSTRAINT Unq_coursename UNIQUE,
CoursetypeCHAR(3));
Table-Level Constraint When Creating a Table
CREATE TABLE Course
(Courseno TINYINT,
Coursename VARCHAR(25),
CoursetypeCHAR(3),
CONSTRAINT Unq_coursename_type
UNIQUE (Coursename, Coursetype));
UNIQUE Constraint After Creating a Table
You must use the ALTER TABLE command.
ALTER TABLE Course
ADD CONSTRAINT Unq_coursename_type
UNIQUE (Coursename, Coursetype);
- The UNIQUE constraint can allow only one NULL value.
- A table can have multiple UNIQUE constraints.
- The UNIQUE constraint is enforced through the creation of a unique index on the specified column or columns.
SQL’s DML Statements
The SELECT Statement
A query, ie a request for information to be retrieved from the database.
The SELECT statement is made up of a number of clauses, with a minimum of two:
SELECT the columns to retrieve
FROM the table(s) which contain those columns
- The clauses may be entered on the same line or on separate lines.
- You can enter statements in UPPER CASE, lower case or a MiXtUrE of the two. However, reserved words are, by convention, entered in UPPER CASE.
Selecting All Columns and All Rows
To display all the information from a table called Student:
SELECT * FROM Student;
Selecting Specific Columns and All Rows
To select some of the columns and all the rows:
SELECT Studentno, Surname FROM Student;
The order of column names in a SELECT statement determines the order in which the columns are displayed.
SELECT Surname, Studentno FROM Student;
Selecting Specific Rows
Requires the WHERE clause following the FROM clause:
SELECT the columns to retrieve
FROM the table(s) which contain those columns
WHEREcertain conditions are met
SELECT * FROM Course
WHERE Courseno = 30;
SELECT Courseno, Coursename
FROM Course
WHERE Coursename = 'Computing';
Comparison Operators
OperatorMeaning
=equal to
!= or >not equal to
greater than
>=greater than or equal to
less than
<=less than or equal to
BETWEEN … AND …between two values
IN (list)any of a list of values
LIKEmatch a character pattern
IS NULLis a null value
To negate the last four operators, use the operator NOT, ie NOT BETWEEN, NOT IN, NOT LIKE and IS NOT NULL.
Examples
SELECT * FROM Course
WHERE Coursetype > 'BSc';
SELECT Studentno, Surname FROM Student
WHERE Dob < '01/26/77';
SELECT * FROM Student
WHERE Surname > 'M';
SELECT Tutorno, Job FROM Tutor
WHERE Salary >= 20000 AND Salary <=25000;
SELECT Tutorno, Job FROM Tutor
WHERE Salary BETWEEN 20000 AND 25000;
SELECT Tutorno, Job FROM Tutor
WHERE Salary NOT BETWEEN 20000 AND 25000;
SELECT Courseno, Coursename FROM Course
WHERE Courseno IN (30, 40, 50, 90);
SELECT Courseno, Coursename FROM Course
WHERE Coursename
NOT IN (‘Electronics’, ‘Computing’);
SELECT * FROM Course
WHERE Coursename LIKE 'Comp%';
SELECT Courseno, Studentno
FROM CourseStudent
WHERE Studymode LIKE '_T';
NB:Each _ represents one character. The % represents any number of characters.
SELECT Tutorno, Studentno
FROM TutorStudent
WHERE Studentno NOT LIKE '_ _D%';
Using AND
SELECT Tutorno, Job FROM Tutor
WHERE Salary >= 20000 AND Salary <=25000;
SELECT * FROM CourseStudent
WHERE Courseno = 50 AND Studymode > 'FT';
A SELECT statement with AND only retrieves a row or rows if both parts of the WHERE clause are true.
Using OR
A SELECT statement with OR retrieves a row or rows if either part of the WHERE clause is true.
SELECT * FROM CourseStudent
WHERE Courseno = 50 OR Studymode > 'FT';
SELECT Courseno, Coursename FROM Course
WHERE Courseno = 30 OR Courseno = 40;
Operator Precedence
When AND and OR appear in the same WHERE clause, AND has a higher precedence than OR, ie all the ANDs are performed first, then all the ORs.
SELECT * FROM Tutor
WHERE Job = ‘Lecturer' AND Salary > 26788
OR Job = 'Senior Lecturer' AND Salary < 30058
OR Job = 'Principal Lecturer' AND Salary < 30058;
All the comparison operators have equal precedence, then the order of precedence is:
NOT
AND
OR
Changing Precedence
To override the rules of precedence, place the part of the expression you want evaluated first in parentheses ().
Example
SELECT * FROM Tutor
WHERE Salary > 26788 AND Salary < 30058
AND (Job = 'Lecturer'
OR Job = 'Senior Lecturer'
OR Job = 'Principal Lecturer');
If in doubt, add parentheses to clarify your SELECT statements and ensure the correct results.
Eliminating Duplicate Rows
The default display of a result from a query is all rows, including duplicate rows.
To eliminate duplicate rows, use the DISTINCT keyword in the SELECT clause, eg:
SELECT DISTINCT Coursename
FROM Course;
SELECT DISTINCT Job
FROM Tutor;
SELECT DISTINCT Courseno, Coursename
FROM Course;
Controlling the Order of Displayed Rows
You can control the order in which rows are displayed by adding the ORDER BY clause as the last clause of a SELECT statement.
ORDER BY lets you order rows in ascending order, in descending order and by multiple columns.
SELECT * FROM Course
ORDER BY Coursename;
SELECT Tutorno, Job, Salary FROM Tutor
ORDER BY Salary DESC;
SELECT * FROM Student
ORDER BY Surname, Fname;
SELECT Tutorno, Job, Salary FROM Tutor
WHERE Salary BETWEEN 20000 AND 25000
ORDER BY Job, Salary DESC;
Inserting Data into a Table
Use INSERT to insert new rows into a table.
INSERT INTO Table_name
VALUES (Data_value1, Data_value2, ….);
- Put character data and dates in single quotes.
- Do not put numeric data in quotes.
- Data items must be separated by commas.
- Only one row may be inserted for each INSERT statement.
- The columns must be in the same order as defined when the table was created.
- A NULL may be placed instead of a value by typing NULL.
Examples
INSERT INTO Course
VALUES (20, ‘Computing’, ’HNC’);
INSERT INTO Student
VALUES
('HNC102345', 'Ms', 'Freda', 'Bloggs',
'05/16/80', '', NULL);
Inserting Data in Only Some Columns
To insert data into only some of the columns for a row you need to specify the column names after the table name. The columns not listed will have a NULL value.
INSERT INTO Student
(Studentno, Fname, Surname)
VALUES
(‘HNC102345’, ’Freda’, ’Bloggs’);
SELECT for Joining Tables
To retrieve data from more than one table, the tables must have a column containing common values. The tables can then be joined by naming them both in the FROM clause and by writing the join condition in the WHERE clause.
When the same column appears in more than one table you must prefix the column name with the table name.