SQL – Data Definition Language
SQL Overview
SQL (Structured Query Language) is the standard query language for relational databases, which was initially established by the American National Standards Institute in 1986. (ANSI). With minor syntax differences, all major database manufacturers adhere to the SQL standard (different dialects).
SQL is made up of two languages: a Data Definition Language (DDL) and a Data Manipulation Language (DML) (DML). SQL is a declarative programming language (non-procedural). A SQL query tells you what to get, but not how to get it. Because it lacks control and iteration instructions, Basic SQL is not a complete programming language. T-SQL, PL/SQL (Oracle), and PL/SQL (Oracle) are examples of procedural extensions (SQL Server).
SQL History
- 1970: Codd invents the relational model and algebra;
- 1974: D. Chamberlin (also at IBM) defines Structured English Query Language (SEQUEL);
- 1976: SEQUEL/2 defines and renames SQL for legal reasons.
The official pronunciation is ‘S-Q-L,’ however the origin of the pronunciation is ‘See-Quel.’
System R, Oracle, and INGRES implement SQL-like query languages in the late 1970s. - 1982 – SQL standardization project begins;
- 1986 – SQL becomes an official ANSI standard;
- 1987 – SQL becomes an ISO standard;
- 1992 – SQL2 (SQL92) version
- SQL3 (supports recursion, object-relational) was released in 1999.
- Updates include SQL:2003, SQL:2006, SQL:2008, SQL:2011, and SQL:2016.
SQL Basic Rules
The following are some fundamental SQL rules:
1) A list of reserved terms that cannot be used as database object names exists. (for example, SELECT, FROM, WHERE)
2) SQL does not care about case. String constants are the only exception. The words ‘FRED’ and ‘fred’ are not interchangeable.
3) SQL is a free-format language that ignores whitespace.
4) Although not necessarily needed, the semi-colon is frequently employed as a statement terminator.
5) The format of date and time constants is defined:
- Dates: ‘YYYY-MM-DD’, for example, ‘1975-05-17’
- ‘hh:mm:ss[.f]’, for example, ’15:00:00′
- ‘YYYY-MM-DD hh:mm:ss[.f]’, for example, ‘1975-05-17 15:00:00’
6) A single quotation character in a character constant is represented by two single quotes “.
‘Master’s’, for example.
SQL DDL Overview
SQL has a data definition language (DDL) that lets users to do things like:
add, edit, and remove tables; construct views; define and enforce integrity constraints; and impose security limitations.
SQL Identifiers
Tables, views, and columns are all identified in the database using identifiers.
The name of the database object is the identifier.
The following requirements must be followed when creating a SQL identifier (name): only contain upper or lower case letters, numbers, and the underscore (“_”) character; and be no more than 128 characters.
Database vendors may apply tougher restrictions.
Contains no spaces and must begin with a letter (or underscore).
Note that identifiers that are quoted or delimited and contained in double quotes can contain spaces and other characters.
For example, “select”
SQL Data Types
Each attribute has an associated domain of values in the relational mode l.
Each column (attribute) in SQL has a data type that restricts the values it may hold. The standard SQL data types are similar to their equivalents in programming languages.
When necessary, the database will perform implicit data type conversion.
Using functions like CAST and CONVERT, you can convert explicit data types.
SQL Data Types
| BOOLEAN | TRUE or FALSE |
| CHAR | Fixed length string (padded with blanks) e.g. CHAR(10) |
| VARCHAR | Variable length string e.g. VARCHAR(50) |
| BIT | Bit string e.g. BIT(4) can store ‘0101’ |
| NUMERIC or DECIMAL | Exact numeric data type e.g. NUMERIC(7,2) has a precision (max. digits) of 7 and scale of 2 (# of decimals) e.g. 12345.67 |
| INTEGER | Integer data only |
| SMALLINT | Smaller space than INTEGER |
| FLOAT or REAL | Approximate numeric data types. Precision dependent on implementation. |
| DOUBLE PRECISION | |
| DATE | Stores YEAR, MONTH, DAY |
| TIME | Stores HOUR, MINUTE, SECOND |
| TIMESTAMP | Stores date and time data. |
| INTERVAL | Time interval. |
| CHARACTER LARGE OBJECT | Stores a character array (e.g. for a document) |
| BINARY LARGE OBJECT | Stores a binary array (e.g. for a picture, movie) |
SQL User Defined Data Types
The CREATE DOMAIN command allows you to define your own types that are subsets of built-in types:
CREATE DOMAIN domainName AS dataType [DEFAULT defaultValue]
[CHECK (condition)]
Example: Create user-defined domain for Emp.title:
CREATE DOMAIN titleType AS CHAR(2)
DEFAULT ‘EE’
CHECK (VALUE IN (NULL,’EE’,’SA’,’PR’,’ME’));
The CHECK clause can use a nested select statement to retrieve values from the database:
CREATE DOMAIN mgrType AS CHAR(5)
DEFAULT NULL
CHECK (VALUE IN (SELECT eno FROM emp
WHERE title = ‘ME’ OR title =
‘SA’));
Domains can be removed from the system using DROP:
DROP DOMAIN domainName [RESTRICT | CASCADE]
- RESTRICT – if domain is currently used, drop fails.
- CASCADE – if domain is current used, domain dropped and fields using domain defaulted to base type.
- Example:
DROP DOMAIN mgrType;
SQL CREATE TABLE
The CREATE TABLE command is used to create a table in the database. A table consists of a table name, a set of fields with their names and data types, and specified constraints.
The general form is:
CREATE TABLE tableName (
attr1Name attr1Type [attr1_constraints]
attr2Name attr2Type [attr2_constraints]….
attrMName attrMType [attrM_constraints]
attr2Name attr2Type [attr2_constraints]….
[primary and foreign key constraints]);
The CREATE TABLE command for the Emp relation:
CREATE TABLE Emp
eno ( CHAR 5)
ename VARCHAR(30), NOT NULL,
bdate DATE,
title CHAR(2),
salary DECIMAL(9,2),
supereno CHAR(5),
dno CHAR(5),
PRIMARY KEY (eno)
FOREIGNKEY (dno) REFERENCES Dept(dno)
ON DELETE SET NULL ON UPDATE CASCADE );
SQL Constraints
In the CREATE and ALTER statements, constraints are provided.
Types of Constraints
- Required data-Declare NOT NULL after the column declaration to specify that a column must always contain a data value (cannot be NULL).
- Domain constraints – Used to check that the value of a column is in a particular domain using CHECK. e.g. eno CHAR(5) NOT NULL
e.g. title CHAR(2) CHECK (title IN (NULL,’EE’,’SA’,’PR’,’ME’));
It’s also possible to utilise user-defined kinds (domains). - Tuple Constraints-instead of a single attribute, CHECK can be applied to an entire tuple:
CREATE TABLE student(
num CHAR(10) NOT NULL,
honors CHAR(1),
gpa DECIMAL(3,2),
CHECK ( (honors = ‘Y’ AND gpa > 3.50)
OR honors = ‘N’)
);
It’s worth noting that the CHECK clause can contain subqueries, but the CHECK is only run when the relation is changed.
When a relation in a subquery is changed, it is not checked.
SQL Constraints – Entity Integrity
Entity Integrity Constraint: Each row of a table’s primary key must have a unique, non-null value. The PRIMARY KEY clause is used to specify the primary key.
PRIMARY KEY (eno) (for Emp relation)
PRIMARY KEY (eno,pno) (for WorksOn relation)
You may also utilize PRIMARY KEY after you’ve defined the attribute in the CREATE TABLE statement. Only one primary key may be given per relation; further candidate keys can be specified using UNIQUE:
UNIQUE (ename), for example
SQL Constraints – Referential Integrity
Referential integrity constraint – Defines a foreign key that refers to another table’s primary key. If a foreign key has a non-NULL value, that value must be included in a tuple in the relation that includes the referenced primary key.
WorksOn, for example, has two foreign keys:
WorksOn.eno is a website that refers to other websites. WorksOn.pno references Emp.eno Proj.pno
FOREIGN KEY syntax is used to specify foreign keys:
REFERENCES Emp FOREIGN KEY (eno) FOREIGN KEY (eno) FOREIGN KEY (eno) FOR (eno)
CREATE TABLE
WorksOn ( CHAR(5),
eno CHAR(5),
pno CHAR(5),
resp VARCHAR(20),
hours SMALLINT,
PRIMARY KEY (eno,pno), FOREIGN KEY (eno) REFERENCES Emp(eno),
FOREIGN KEY (pno) REFERENCES Proj(pno) );
SQL Referential Integrity and Updates
If you try to INSERT or UPDATE a row in a relation with a foreign key (for example, WorksOn), the action will be denied because it violates referential integrity.
You may choose what happens to the data in the foreign key relation (WorksOn) when you UPDATE or DELETE a row in the main key relation (e.g. Emp or Proj).
1) CASCADE – When the main key relation has rows removed, delete (update) data in the foreign key relation (updated).
2) SET NULL – When the matching primary key relation row is removed, set foreign key fields to NULL.
3) SET DEFAULT – Restore the default value for foreign keys (if defined).
4) NO ACTION – Reject the request on the parent table.
Full Syntax of Create Table
CREATE TABLE tableName (
{ attrName attrType [NOT NULL] [UNIQUE] [PRIMARY KEY]
[DEFAULT value] [CHECK (condition)] }
[PRIMARY KEY (colList)]
{[FOREIGN KEY (colList) REFERENCES tbl [(colList)],
[ON UPDATE action]
[ON DELETE action] }
{[CHECK (condition)] }
);
Creating the Example Database
CREATE DOMAIN T_eno AS CHAR(5);
CREATE DOMAIN T_pno AS CHAR(5);
CREATE DOMAIN T_dno AS CHAR(5);
CREATE TABLE Emp(
ename VARCHAR(30) NOT NULL,
bdate DATE,
title CHAR(2),
salary DECIMAL(9,2),
supereno T_eno,
dno T_dno
PRIMARY KEY (eno),
FOREIGN KEY (dno) REFERENCES Dept(dno)
CASCADE
);
Defining a Database
You can usually build, edit, and destroy a hierarchy of database items. How a database is created is not standardized by SQL. A database will often have one or more catalogues, each with its own set of schemas.
Many DBMSs don’t implement everything and rename things, which makes things more more complex.
For instance, with MySQL, a database IS a schema (there is no CREATE SCHEMA command).
Creating Schemas
A schema is a set of database objects (tables, views, domains, and so on) that are generally linked to a single user.
Creating a schema entails the following steps:
CREATE SCHEMA employeeSchema AUTHORIZATION Joe;
Dropping a schema:
DROP SCHEMA employeeSchema;
ALTER TABLE
You may use the ALTER TABLE command to make changes to an existing table. When the table already has data in it and you want to add or delete a column or constraint, this is a good option.
DB vendors may only support sections of ALTER TABLE or may allow extra modifications such as altering a column’s data type.
ALTER TABLE tableName
[ADD [COLUMN] colName dataType [NOT NULL] [UNIQUE]
[DEFAULT value] [CHECK (condition)] ]
[DROP [COLUMN] colName [RESTRICT | CASCADE]
[ADD [CONSTRAINT [constraintName]] constraintDef]
[DROP CONSTRAINT constraintName [RESTRICT | CASCADE]]
[ALTER [COLUMN] SET DEFAULT defValue]
[ALTER [COLUMN] DROP DEFAULT]
Add column location to Dept relation:
ALTER TABLE dept
ADD location VARCHAR(50);
Add field SSN to Emp relation:
ALTER TABLE Emp
ADD SSN CHAR(10);
Indicate that SSN is UNIQUE in Emp:
ALTER TABLE Emp
ADD CONSTRAINT ssnConst UNIQUE(SSN);
Drop Table
The command DROP TABLE is used to delete the table definition and all data from the database:
DROP TABLE tableName [RESTRICT | CASCADE];
Example:
DROP TABLE Emp;
Indexes
Indexes are used to speed up access to table rows depending on the values of particular characteristics.
An index can enhance query efficiency substantially, but they come at a cost because they must be updated every time the table is updated.
The following is the typical syntax for adding and removing indexes:
CREATE [UNIQUE] INDEX indexName
ON
tableName (colName [ASC|DESC] [,…])
DROP INDEX indexName;
UNIQUE denotes that each value in the index is one-of-a-kind.
The sorted order of index is specified by ASC/DESC.
Indexes Example
In WorksOn, creating indexes on eno and pno is beneficial since it speeds up joins with the Emp and Proj databases, respectively.
Because eno (pno) might appear several times in WorksOn, index is not UNIQUE.
CREATE INDEX idxEno ON WorksOn (eno);
CREATE INDEX idxPno ON WorksOn (pno);
Most DBMSs will create an index on the primary key, but if they don’t, here’s what WorksOn would like:
CREATE UNIQUE INDEX idxPK ON WorksOn (eno,pno);
Database Updates
Inserting rows, removing rows, and updating rows in a database are all done using their own statements.
The INSERT command is used to insert data:
INSERT INTO tableName [(column list)] VALUES (data value list)
Examples: INSERT INTO emp VALUES (‘E9′,’S. Smith’,DATE ‘1975-03-05’, ‘SA’,60000,’E8′,’D1′);
INSERT INTO proj (pno, pname) VALUES (‘P6′,’Programming’);
Values must be given in the order they were generated in the table if the column list is omitted. If a column is missing from the list, it is set to NULL.
Insert Multiple Rows
INSERT statement extended by many databases to take multiple rows:
INSERT INTO tableName [(column list)]
VALUES (data value list) [, (values) ]+
Example:
INSERT INTO Emp (eno, ename) VALUES
(‘E10’, ‘Fred’), (‘E11’, ‘Jane’), (‘E12’, ‘Joe’)
INSERT rows from SELECT
Insert multiple rows that are the result of a SELECT statement:
INSERT INTO tableName [(column list)]
SELECT …
Example: Add rows to a temporary table that contains only employees with title =’EE’.
INSERT INTO tmpTable SELECT eno, ename FROM emp
WHERE title = ‘EE’
UPDATE Statement
Updating existing rows is performed using UPDATE statement:
UPDATE tableName
SET col1 = val1 [,col2=val2…] [WHERE condition]
Examples:
- 1) Increase all employee salaries by 10%.
UPDATE emp SET salary = salary*1.10;
- 2) Increase salaries of employees in department ‘D1’ by 8%.
UPDATE emp SET salary = salary*1.08
WHERE dno = ‘D1’;
DELETE Statement
Rows are deleted using the DELETE statement:
DELETE FROM tableName [WHERE condition]
Examples:
- 1) Fire everyone in the company.
DELETE FROM works on;
DELETE FROM emp;
- 2) Fire everyone making over $35,000.
DELETE FROM emp
WHERE salary > 35000;
Conclusion
SQL is a data definition language that lets you CREATE, ALTER, and DELETE database objects like tables, triggers, indexes, schemas, and views.
To keep the database’s integrity, constraints are employed: CHECK may be used to validate attribute values.
Entity Integrity constraint: Each row of a table’s primary key must have a unique, non-null value.
Referential integrity constraint – Defines a foreign key that points to a table’s unique key.
The INSERT, DELETE, and UPDATE commands change the data in a database.
