Introduction to SQL

The SQL language is a standard for relational databases:

• since database systems share a standard language, migrating a database application to another relational database is a quick and cheap work
• database application programs can access data stored in multiple relational DBMSs using the same database language

The practical SQL vs the relational algebra

• the SQL language is a high level declarative language for the relational model
• with a query in the SQL language, the user must only indicate how the result is to be and the DBMS decides how to execute the query
• the relational algebra is a formal language for the relational model
• with a query in the relational algebra, the user must specify a sequence of low-level operations. Relational algebra operations are not suitable for most DBMS users

The SQL name stands for Structured Query Language

• the SQL language is a standard by the American National Standards Institute (ANSI) and the International Standards Organization (ISO)
• on the wikipedia page about SQL, you can find out the history of the versions of the SQL standard

SQL = DDL + DML + DCL + TCL

• SQL is Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL) and Transaction Control Language (TCL)
• SQL language has data manipulation statements for query and update
• SQL language has data definition statements for creating and altering tables
• SQL language has data control statements for granting and revoking privileges on tables
• SQL language has transaction control statements for committing and rolling back transactions

1 SQL Data Definition and Data Types

The relational model uses the terms relation, tuple and attribute. In the SQL the previous terms are replaced by the terms table, row and column.

• the CREATE statement is the main SQL command for data definition, it is used for creating schemas, tables, types, domains, as well as other constructs such as views, assertions and triggers

1.1 Schema and Catalog Concepts in SQL

The concept of database schema in the SQL language

• a database schema groups together tables and other objects that belong to the same database application
• a schema includes tables, types, constraints, views, domains and authorization grants
• in some systems, database schemas are called databases
• a database schema is identified by a schema name, it includes an authorization identifier of the user or account who owns the schema and the descriptors of the elements in the schema
• to create a database schema use the CREATE SCHEMA statement
• for Example, create the schema COMPANY owned by the user with identifier ‘Williams’

   CREATE SCHEMA COMPANY AUTHORIZATION ‘Williams’; 

The concept of catalog in the SQL language

• a database installation always contains a catalog, which is a collection of schemas
• a database catalog contains a special schema, named INFORMATION_SCHEMA, which holds information about all the schema in the catalog

Default environment and default schema

• a database installation has a default environment and default schema
• when a user establishes a connection to the database, he can refer to the tables within the default schema, without having to specify a schema name

1.2 The CREATE TABLE Command in SQL

The CREATE TABLE command generates a new table

• The CREATE TABLE statement actually creates the table and its rows and stores them as files on the database
• within the command, you specify the table's name, the table's attributes and table's constraints
• you assign a name, a data type and possible constraints to attributes
• the SQL schema of the table is either implicitly specified in the environment where the command is executed or explicitly specified by prepending the schema name to the table name
• relations declared by the CREATE TABLE statement are also called base tables to signify that are stored in the database as physical files; relations created by the CREATE VIEW statement are also called virtual tables to indicate that they may or may not correspond to a physical file

Example, the CREATE TABLE command in the COMPANY database schema:

CREATE TABLE EMPLOYEE ( 
  Ssn           CHAR(9)       NOT NULL,  
  Fname         VARCHAR(15)   NOT NULL,
  Minit         CHAR,
  Lname         VARCHAR(15)   NOT NULL,
  Bdate         DATE , 
  Address       VARCHAR(30),
  Sex           CHAR,
  Salary        DECIMAL(10,2),
  SuperSsn      CHAR(9),
  Dno           INT           NOT NULL        DEFAULT 1,
  PRIMARY KEY (Ssn));

CREATE TABLE DEPARTMENT ( 
  Dname         VARCHAR(15)   NOT NULL,
  Dnumber       INT           NOT NULL,
  MgrSsn        CHAR(9)       NOT NULL        DEFAULT ‘888786555’,
  MgrStartDate  DATE,
  PRIMARY KEY (Dnumber),
  UNIQUE (Dname),
  FOREIGN KEY (MgrSsn) REFERENCES EMPLOYEE(Ssn)
    ON DELETE SET DEFAULT 
    ON UPDATE CASCADE);

CREATE TABLE DEPT_LOCATIONS ( 
  Dnumber       INT           NOT NULL,
  Dlocation     VARCHAR(15)   NOT NULL,
  PRIMARY KEY (Dnumber, Dlocation),
  FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
    ON DELETE CASCADE 
    ON UPDATE CASCADE);

CREATE TABLE PROJECT ( 
  Pname         VARCHAR(15)   NOT NULL,
  Pnumber       INT           NOT NULL,
  Plocation     VARCHAR(15),
  Dnum          INT           NOT NULL,
  PRIMARY KEY (Pnumber),
  UNIQUE (Pname),
  FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber));

CREATE TABLE WORKS_ON ( 
  Essn          CHAR(9)       NOT NULL,
  Pno           INT           NOT NULL,
  Hours         DECIMAL(3,1)  NOT NULL,
  PRIMARY KEY (Essn, Pno),
  FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
  FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber));

CREATE TABLE DEPENDENT ( 
  Essn          CHAR(9)       NOT NULL,
  DependentName VARCHAR(15)   NOT NULL,
  Sex           CHAR,
  Bdate         DATE,
  Relationship  VARCHAR(8),
  PRIMARY KEY (Essn, DependentName),
  FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn));  

ALTER TABLE EMPLOYEE
ADD CONSTRAINT FOREIGN KEY (SuperSsn) REFERENCES EMPLOYEE (Ssn)
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

ALTER TABLE EMPLOYEE
ADD CONSTRAINT FOREIGN KEY (DepNo) REFERENCES DEPARTMENT (Dnumber)
  ON DELETE SET DEFAULT 
  ON UPDATE CASCADE;

1.3 Attribute Data Types and Domains in SQL

The basic data types for attributes include numeric, character string, bit string, boolean, date and time

Types of Data Types

• Exact Numeric Data Types: Integer Data Types and Fixed Point Data Types
• Approximate Numeric Data Types: Floating Point Data Types
• Strings Data Types: Character Strings Data Types and Bit Strings Data Types
• Bolean Data Type
• DateTime Data Types

Exact Numeric Data Types: Integer and Fixed Point

• an exact numeric data type stores numbers of a specified precision and scale
• precision is the number of digits in the number.
• scale is the number of digits to the right of the decimal point in a number.
• for example, the number 123.45 has a precision of 5 and a scale of 2.
• length is the number of bytes that are used to store the number.
• for example, the integer data type has a precision of 10, a length of 4 and a scale of 0.

Integer Types (Exact Value)
SMALLINT	stores a 2 byte length integer number, whose value ranges from 0 to 65535
INTEGER | INT	stores a 4 byte length integer number, whose value ranges from 0 to 4294967295
BIGINT	stores a 8 byte length integer number, whose value ranges from 0 to 264-1
Fixed-Point Types (Exact Value)
DECIMAL(p,s) | DEC(p,s) | NUMERIC(p,s)	declare a number with precision p and scale s; default precision is implementation defined, default scale is zero
These types store exact numeric data values and are used when it is important to keep exact precision, for example with monetary data. Example, the type DECIMAL(5,2) with 5 the precision and 2 the scale, stores any value with five digits and two decimals. The value range is from -999.99 to 999.99.
Floating-Point Types (Approximate Value)
REAL | FLOAT	stores a 4 bytes number in single-precision floating point format; max value is 3.402823466E+38
DOUBLE PRECISION	DOUBLE PRECISION stores a 8 bytes number in double-precision floating point format; max value is 1.7976931348623157E+308
FLOAT(p) is SQL optional declaration syntax, where p is the precision in bits FLOAT(p,s) and DOUBLE(p,s) are SQL non standard declaration syntax Declare FLOAT or DOUBLE data type without specifying precision to preserve code portability
Character string data types
CHAR(n) | CHARACTER(n)	fixed lenght charaters-string data types, where n is the number of character
VARCHAR(n) | CHARACTER VARYING(n)	varying length characters-string data types, with n the maximum number of characters
CLOB | CHARACTER LARGE OBJECT	very large string of characters
Specify a literal character string value, by placing the string between single quotation marks. For ex. ‘John’. Literal values are case sensitive If a CHAR is assigned a shorter string, the value is padded with blank characters to the right. MySQL designates the CLOB data type as LONGTEXT The TEXT data type is not part of SQL standard, but is implemented in many database systems
Bit string data types
BINARY(n)	fixed length bytes-string data type, where n is the number of bytes
VARBINARY(n) | BINARY VARYING(n)	varying length bytes-string data type, where n is the maximum number of bytes
BLOB | BINARY LARGE OBJECT	stores a long string of bytes, for ex. sound, images, etc
The SQL:2003 standard removes the BIT data type Literal bit strings are placed between single quotes and preceded by a B to distinguish them from character strings; for example, B‘00011’ Specify CLOB and BLOB maximum size in kilobytes (K), megabytes (M) or gigabytes (G); for example, BLOB(5M) indicates a maximum length of 5 megabytes.
Boolean Data Type
BOOLEAN	data type that has the values of TRUE or FALSE
In SQL, a third possible value for the Boolean type is unknown because of NULL values
Datetime Data Fypes
DATE	stores year, month and day, has the format 'YYYY-MM-DD' Literal values are enclosed in single-quoted strings and preceded by DATE, for ex. DATE'2018-09-27'
TIME	stores hour, minute and second, has the format 'hh:mm:ss' Literal values are enclosed in single-quoted strings and preceded by TIME, for ex. TIME'08:12:35'
TIMESTAMP	stores both date and time fields, with optional second fractions, has the format: 'YYYY-MM-DD hh:mm:ss[.fraction]' Literal values are are enclosed in single quotes and preceded by a TIMESTAMP. For Example, TIMESTAMP ‘2019-06-13 08:20:47.830256’
TIME WITH TIME ZONE	stores a time and a UTC time zone designator, the time zone uses additional six spaces The time zone designator has the format ±hh:mm and range of –12:59, +13:00. If the time zone is not included, the default is the local time zone for the SQL session.
TIMESTAMP WITH TIME ZONE	a timestamp and a UTC time zone designator.
Note that the format of DATE , TIME and TIMESTAMP can be considered as a special type of string. They can be used in string comparisons, by being cast into strings.
Temporal Interval Data Fypes
INTERVAL	interval data type specifies an interval of time, meant as difference between two absolute time values.

SQL Domains

You can specify the data type of each attribute or specify the attribute as belonging to a domain

• declare a domain and use the domain name in the attribute specification
• this makes it easier to change the data type for a domain used by multiple attributes in a schema
• for example, you can create the domain SSN_TYPE a use it to specify the attributes Ssn, SuperSsn, MgrSsn.

 CREATE DOMAIN SSN_TYPE AS CHAR(9); 

2 Specifying Constraints in SQL

The basic constraints that can be specified while creating a table are: primary key, referential integrity constraints, restrictions on attribute domains, NULLS, constraints on single relation tuples using the CHECK clause.

2.1 Specifying Attribute Constraints and Attribute Defaults

NOT NULL Constraint

• specify a NOT NULL constraint on an attribute, if NULL is not permitted as attribute's value
• primary key attributes implictly have a NOT NULL constraint
• for example, the COMPANY physical schema applies the NOT NULL constraint on the EMPLOYEE's FName and LName attributes

DEFAULT Value Constraint

• it is possible to specify a default value for an attribute by appending the clause DEFAULT <value> to the attribute definition
• when a new tuple in inserted into the database without providing an explict value, the default value is used
• for example, you can specify a default department for a new employ or a default manager for a new department

The CHECK Constraint

• The CHECK constraint is used to restrict attribute or domain values
• append the CHECK clause after an attribute or domain definition
• for example, suppose that the department numbers are restricted to numbers between 1 and 20.
  In the DEPARTMENT table, declare a Dnumber attribute as follow:

Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

2.2 Specifying Key and Referential Integrity Constraints

The Primary Key Constraint

• the PRIMARY KEY clause specifies one or more attributes that constitutes the primary key of a relation
• if the primary key has only one attribute, the PRIMARY KEY clause may follow the attribute directly
• if the primary key has multiple attributes, the PRIMARY KEY clause must be specified at the end of the table declaration

The Unique Constraint

• The UNIQUE clause specifies unique keys
• A relation schema may have multiple candidate keys: one is chosen as primary key and the others are designated as unique keys
• for example, the DEPARTMENT's Dname and PROJECT's Pname are specified as unique keys

The Referential Integrity Constraint

• The FOREIGN KEY clause specifies referential integrity constraints

 CONSTRAINT FOREIGN KEY (SuperSsn) REFERENCES EMPLOYEE(Ssn) 

• a SQL statement violates an integrity constraint when:
• tuples are inserted or deleted
• a primary key or a foreign key attribute value is updated
• DBMS by default rejects an update operation that causes an integrity constraint violation: this is called the RESTRICT option
• Database designers can modify the DBMS' default behaviour, attaching a referential triggered action clause to foreign key constraint:
• qualify the triggered action with either ON DELETE or ON UPDATE
• choose an option: SET NULL, CASCADE or SET DEFAULT
• the EMPLOYEE's foreign key SuperSsn is specified as follow:

CONSTRAINT FOREIGN KEY (SuperSsn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE

• on delete set null means that, if a tuple of a supervisor employee is deleted, the value of SuperSsn, of all the tuples that reference the deleted tuple is set to NULL
• on update cascade means that, if a tuple of a supervisor employee is updated, the new value of Ssn is passed (cascaded) to the SuperSsn attribute of all the tuples that reference the supervisor employee

How To Choose an Option for the Referential Triggered Action

• SET NULL or SET DEFAULT options
• the DBMS take the same actions for the SET NULL or SET DEFAULT options for both ON DELETE/ON UPDATE:
• the value of the referencing attribute is set to NULL (for SET NULL) or to the default value (for SET DEFAULT)
• CASCADE option
• the ON DELETE CASCADE action deletes all the referencing tuples
• the ON UPDATE CASCADE action changes the value of the referencing foreign key attribute to the updated new primary key value, for all the referencing tuples
• the CASCADE option is suitable for "relationship" relations (such us WORKS_ON), for relations that represent multivalued attributes (such as DEPT_LOCATIONS) and for relations that represent weak entity type

2.3 Giving Names to Constraints

You can give a name to a constraint, adding the constraint name following the keyword CONSTRAINT

• the constraint name must be unique in the schema
• the contraint's name is optional

2.4 Specifying Constraints on Tuples Using CHECK

• You can add constraints to table using the CHECK clause at the end of the CREATE TABLE statement
• these are called row-based constraints because:
• they apply to each row individually
• they are checked whenever a row is inserted or modified
• for example, suppose that the DEPARTMENT table has a the DepartCreationDate attribute
• you can use a check clause to make sure that a manager start date is later that the department creation date:

 CHECK (DepartCreationDate <= MgrStartDate); 

3 Basic Retrieval Queries in SQL

The SQL's SELECT statement retrieves information from a database. In the following sections, many sample queries against the Company schema are demonstrated.

SQL's table VS formal relation:

• there is a difference between a SQL table and a relation of the formal relational model
• a SQL table is not a set of tuples, because SQL allows a table to have two or more identical tuples
• some SQL tables are a set of tuples because a primary key constrain has been declared

3.1 The SELECT-FROM-WHERE Structure of Basic SQL Queries

The basic SELECT statement is made up of three clauses: SELECT, FROM and WHERE, and has the following form:

SELECT <attribute list>
FROM <table list>
WHERE <condition>;

• <attribute list> is a list of attribute names whose values are retrieved by the query
• <table list> is a list of relation names
• <condition> is a conditional expression that identifies the tuples to be retrieved, the conditional expression uses a SQL comparison operator
• the SQL logical comparison operators are: =, <, <=, >, >=, <>, they are used to compare attribute values with one another and with literal constants

SQL terms vs relational algebra terms

• The SELECT clause specifies the attributes whose value are retrieved by the query: the relational algebra uses the term projection attributes.
• The WHERE clause specifies the boolean condition that must be true for the tuple: the relational algebra uses the term select condition.

Example, a query that involves only the EMPLOYEE relation

• retrieves the birth date and address of the employees whose name is ‘Wile E. Coyote’

SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = ‘Wile’ AND Minit = ‘E’ AND Lname = ‘Coyote’;

Example, a join query that involves two relations: EMPLOYEE and DEPARTMENT

• retrieves the name and address of all employees who work for the ‘Research’ department

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dnumber = Dno AND Dname = ‘Research’;

• a select-project-join query is a select that involves projection attributes plus select and join conditions
• the WHERE clause contains a select condition and a join condition
• a single query can contain any number of selections and join conditions
• Dname = ‘Research’ is the selection condition
• Dnumber = Dno is the join condition. Tuples from two relations are combined, when the value of the number of the department is equal in the two tuples

Example, a join query that involves three relations: PROJECT, DEPARTMENT and EMPLOYEE

• retrieve the project number, the number of the controlling department, the name and address of department's manager of each project located in Birmingham.

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND MgrSsn = Ssn AND Plocation = ‘Birmingham’

• the are two join conditions:
• Dnum = Dnumber relates a PROJECT tuple with a controlling DEPARTMENT tuple
• MgrSsn = Ssn relates a controlling DEPARTMENT with the EMPLOYEE who manages the department

3.2 Qualifying Attribute Names, Aliasing Relation Names

Example, query that qualify attribute names with table names

• Suppose that the department number attributes in the relations EMPLOYEE and DEPARTMENT have the same name,
  the query that retrieves the name and address of all employees who work for the ‘Research’ department must be rephrased as follows:

SELECT Fname, LName, Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Dnumber = EMPLOYEE.Dnumber AND DEPARTMENT.Name = ‘Research’;

• In SQL, the same name can be used for two or more attributes in different tables
• a multitable query that refers to two attribute with same name, must qualify the attribute name with the relation name
• qualify the attribute name by prefixing the relation name to the attribute name and separating the two names with a period
• Use fully qualified attribute names for clarity even if the attribute name do not share the same name.
  The query that retrieves the name and address of all employees who work for the ‘Research’ department can be rewritten with fully qualified attribute names

SELECT EMPLOYEE.Fname, EMPLOYEE.LName, EMPLOYEE.Address
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.Dnumber = EMPLOYEE.Dno AND DEPARTMENT.DName = ‘Research’;

Example, query that declares relation's aliases

• the query retrieves first name and last name of each employee and his supervisor

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SuperSsn = S.Ssn;

• a query that refers to the same relation twice gives rise to ambiguity in attribute names
• the query declares the alternative names (or aliases) E and S for the EMPLOYEE relation
• the alias can follow the keyword AS or it can immediately follow the relation name
• when you give more alisases to a relation, you can use these names to reference the same relation multiple times in the same query
• E and S can be seen as two different copies of the EMPLOYEE relation:
• E represents employees in the role of supersees
• S represents employees in the role of supervisors
• the join condition E.SuperSsn = S.Ssn joins the the relation with itself
• this query is an example of recursive query
• it also possible to rename the relation's attributes in a SQL query
• for example, you can write: FROM EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Ssn, Dno)

Alias-naming mechanism for making queries more user friendly

• the alias-naming mechanism can be used for every table in the WHERE clause
• this practice is recommended because it produces queries that are easier to understand
• for example, you can write the query that retrieves the name and address of all employees who work for the ‘Research’ department as follow

SELECT E.Fname, E.LName, E.Address
FROM EMPLOYEE AS E, DEPARTMENT AS D
WHERE D.Dnumber = E.Dno AND D.DName = ‘Research’;

3.3 Querying without WHERE Clause and Using the Asterisk , Cross Product

Example, queries that lack a WHERE clause

• the query selects all employee Ssns

SELECT Ssn
FROM EMPLOYEE;

• the lack of a WHERE clause indicates no condition for tuple selection, the result is a very large relation
  all the tuples of the relations specified in the FROM clause are selected for the query result


• the query selects all combinations of an EMPLOYEE's Ssn and a DEPARTMENT's Dname

SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;

• the query selects all combinations of Ssn and Dname attributes, regardless of weather the employee works for a department or not
• the query is similar to a cartesian product
• in the WHERE clause you should specify selection and join conditions:
  if all the attributes of EMPLOYEE and DEPARTMENT are specified, the result is the cross product, or cartesian product, of the two relations

Example, queries that use an asterisk (*) to retrieve all the attribute values of the selected tuples

• it retrieves all the attributes values of any employee who works in the department number 5.

SELECT *
FROM EMPLOYEE
WHERE Dno = 5;

• it retrieves all the attributes of an employee and all the attributes of the department where the employee works, if the name of the department is 'Research'

SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = ‘Research’ AND Dno = Dnumber;

• it retrieves the cross product of EMPLOYEE and DEPARTMENT relations

SELECT *
FROM EMPLOYEE, DEPARTMENT;

• the asterisk can also be prefixed by the name of the relation: for example EMPLOYEE.* indicates all the attribute of the EMPLOYEE table

3.4 Tables with Duplicate Tuples and Tables with Distinct tuples in SQL

SQL treats a table not as a set, but as a multiset

• duplicate tuples are allowed in tables and in query results
• SQL does not eliminate duplicate tuple, because the user may want to see duplicate tuples and apply an aggregate function to duplicate tuples

There are two conditions under which a SQL table is a set

• A SQL table with a key is a set, because the key value must be distinct in each tuple
• A query that uses the DISTINCT keyword in the SELECT clause, eliminates duplicate tuples from the result of the query:
• while a query with DISTINCT eliminates duplicate tuples, a query with SELECT ALL does not
• a query with neither DISTINCT nor ALL keyword is equivalent to a select with the ALL keyword

Example, queries that use DISTINCT and ALL

• the query selects all the salary from the EMPLOYEE relation, duplicats included

SELECT ALL Salary
FROM EMPLOYEE;

• if different employee have the same salary, the value will appear multiple times


• the query selects only the different values of the salary from the EMPLOYEE relation

SELECT DISTINCT Salary
FROM EMPLOYEE;

SQL Set Operations

• SQL set operations are: set union (UNION), set difference (EXCEPT) and set intersection (INTERSECT) operations
• the relations result of set operations is a set: duplicate tuples are eliminated from the result
• the set operations can be applied only to relations that are type compatible: they have the same attributes and the attributes are in the same order
• SQL also has the corresponding multiset operations, followed by the keyword ALL: UNION ALL, EXCEPT ALL, INTERSECT ALL: the results are multisets, duplicates are not eliminated

Example, query with UNION operation

• select all the project numbers which involves the employee whose lastName is 'Williams', the projects where this employee works either as a manager or as worker.

( SELECT DISTINCT Pnumber
  FROM PROJECT, DEPARTMENT, EMPLOYEE
  WHERE Dnum = Dnumber AND MgrSsn = Ssn AND Lname = ‘Williams’ )
  UNION
( SELECT DISTINCT Pnumber
  FROM PROJECT, WORKS_ON, EMPLOYEE
  WHERE Pnumber = Pno AND Essn = Ssn AND Lname = ‘Williams’ );

• the first query retrieves the project numbers which involves 'Smith' as manager of the controlling department
• the second query retrieves the project numbers which involves 'Smith' as worker of the project

3.5 Substring Pattern Matching with LIKE Operator, Arithmetic Operators, BETWEEN...AND Operator

The LIKE comparison operator allows comparison on parts of a character string

• partial strings are specified using two wildcard characters:
• percent character (%) stands for zero or more characters
• underscore character (_) stands for a single character

Example, query with LIKE operator and percent

• the query retrieves all the employee whose address is in Dallas, Texas

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE ‘%Dallas,TX%’;

Example, query with LIKE and underscore operator

• the query retrieves all the employees who were born during the 1970s, according to the 'YYYY-MM-DD' date format, the character 7 must be the third character of the string:

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Bdate LIKE ‘_ _ 7 _ _ _ _ _ _ _’;

Note: searching for underscore or percent characters

• if you need to search for a string containing an underscore or percent character, precede the character with an escape character
• specify the escape character using the keyword ESCAPE,
• for example, ‘100\%-200\%’ ESCAPE ‘\’, represents the literal string ‘100%-200%’
• if an apostrophe (’) is to be included in the string represents it as two consecutive apostrophe (”)

Applying Arithmetic, Concatenation, BETWEEN...AND Operators to Attribute Values

• use the standard arithmetic operators (+, −, *, /) with numeric values or attributes with numeric domains
• use the string concatenation operator || to append two string values
• use the + and − operators to increment or decrement a date, time or timestamp by an interval
• the BETWEEN comparison operator allows to test whether an expression is within a range of values

Example, a query that apply the sum arithmetic operator

• the query shows the salary of the employee, working on the ‘MarsRocket’ project, increased by 10%.

SELECT  E.Fname, E.Lname, 1.1 * E.Salary AS Increased-Salary
FROM    EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE   E.Ssn = W.Essn AND W.Pno = P.Pnumber AND P.Pname = ‘MarsRocket’;

• the query also shows how to rename attribute in the query result

Example, a query that retrieves all employee, working for the department num 4, whoose salary is >= 20,000 and <= 30,000

SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 20000 AND 30000) AND Dno = 4;

3.6 Ordering of Query Results

Example, queries that uses the ORDER BY clause

• retrieve a list of employee and projects the employee are working on, ordered by department and in each departement by last name and first name

SELECT    D.Dname, E.Lname, E.Fname, P.Pname
FROM      DEPARTMENT AS D, EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE     D.Dnumber = E.Dno AND E.Ssn = W.Essn AND W.Pno = P.Pnumber
ORDER BY  D.Dname, E.Lname, E.Fname;

• The ORDER BY clause allows the ordering of tuples in the result of a query by the values of one or more attributes

the default ordering is ascending order of values

• use the ASC keyword to order explicitily values in ascending order
• use the DESC keyword to order values in descending order
• for Example, the descending alphabetical order on Dname and ascending order on Lname, Fname
• ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC

4 INSERT, DELETE, and UPDATE Statements in SQL

In SQL, three commands can be used to modify the database: INSERT, DELETE, and UPDATE

4.1 The INSERT Command

the INSERT statement adds a single tuple to a table

• you have to specify the relation name and a list of values for the tuple
• the values has to be listed in the same order in which the attributes were specified in the CREATE TABLE command
• for example, add a new tuple to the relation: EMPLOYEE (Ssn, Fname, Minit, Lname, Bdate, Address, Sex, Salary, SuperSsn, DepNo)

INSERT INTO EMPLOYEE
VALUES ( ‘653453653’, ‘John’, ‘K’, ‘Williams’, ‘1982-12-26’, ‘1320 Taft St, Houston, TX’, ‘M’, 35000, ‘653888653’, 4 );

A second form of the INSERT statement allows users to specify attribute names and corresponding values in the INSERT command

• this form is convenient if a few attribute values of the new tuple are known
• this form must specify all attributes with NOT NULL and no default value. You can only leave out attributes with NULL allowed or DEFAULT values.
• Example, the query inserts an employee for whom it is only known Fname, Lname, Dno and Ssn attributes. (Attributes not specified are set to NULL or DEFAULT value.)

INSERT INTO EMPLOYEE (Ssn, Fname, Lname, Dno )
VALUES (‘653298653’, ‘John’, ‘Williams’, 4 );

• it is also possible to add into a relation multiple tuples separated by commas, by using a single INSERT command

A DBMS must enforce all the integrity constraints specified in the DDL

• for example, the U2 command is rejected by the DBMS because there exists no DEPARTMENT tuple in the database with Dnumber = 2.

INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno)
VALUES (‘Robert’, ‘Hatcher’, ‘980760540’, 2);

• for example, the command below is rejected by the DBMS because no Ssn primary key value is provided, which cannot be NULL.

INSERT INTO EMPLOYEE (Fname, Lname, Dno)
VALUES (‘Robert’, ‘Hatcher’, 5);

A third form of the INSERT command, add multiple tuples into a relation by loading them as a result of a query

• for example, the command ONE creates the WORKS_ON_INFO temporary table with attributes: employee last name, project name and hours per week and command TWO loads the temporary table with information retrieved from EMPLOYEE, PROJECT and WORKS_ON relations.

ONE: CREATE TABLE WORKS_ON_INFO
    ( EmployeeName VARCHAR(15),
      ProjectName VARCHAR(15),
      HoursPerWeek DECIMAL(3,1) );

TWO:  INSERT INTO WORKS_ON_INFO ( EmployeeName, ProjectName, HoursPerWeek )
      SELECT E.Lname, P.Pname, W.Hours
      FROM PROJECT P, WORKS_ON W, EMPLOYEE E
      WHERE P.Pnumber = W.Pno AND W.Essn = E.Ssn;

you can now query the WORKS_ON_INFO table as a normal database table.

• note that the WORKS_ON_INFO table may be not up to date
• if the PROJECT,WORKS_ON, or EMPLOYEE relations are modified, the information in WORKS_ON_INFO table will become outdated

The CREATE TABLE...LIKE...WITH DATA syntax

• creates a table with structure similar to another table and load it with data from the second table

CREATE TABLE NEWEMP LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE AS E
WHERE E.Dno = 5) WITH DATA;

4.2 The DELETE Command

The DELETE command removes one or more tuples from a relation

• use the DELETE command with a WHERE clause to select which tuple are to be removed from the relation
• a single DELETE command may remove one or more tuple, depending on the number of tuples selected by the WHERE clause
• the DELETE command remove all the tuple, if the WHERE clause is missing
• the deletion may propagate to other tables if referential triggered actions are specified in the DDL integrity constraints

Example of DELETE commands, that remove zero, one or more tuple from the EMPLOYEE relation

DELETE FROM EMPLOYEE
WHERE Lname = ‘Green’;

DELETE FROM EMPLOYEE
WHERE Ssn = ‘123456789’;

DELETE FROM EMPLOYEE
WHERE Dno = 5;

DELETE FROM EMPLOYEE;   

4.3 The UPDATE Command

The UPDATE command modifies attribute values of one or more tuples from a relation

• use a WHERE clause to select which tuple are to be modified in the relation
• use a SET clause to specify the new values of the attributes that are to be modified
• note: updating a primary key value may propagate to the foreign key values or other relation's tuples, as specified in the DDL referential integrity constraint

Example, changing attribute value of one tuple

• the query changes location and controlling department attribute's values of the project with primary key project number 10

UPDATE PROJECT
SET Plocation = ‘Pasadena’, Dnum = 5
WHERE Pnumber = 10;

Example, changing attribute value of several tuples.

• the query gives all the employee of the department number = 5 a salary raise of 10%

UPDATE EMPLOYEE
SET Salary = Salary * 1.1
WHERE Dno = 5;

• note: the SET clause uses a reference to the Salary attribute with on the right and on the left of the assignment expression with two different meanings
• you can also specify NULL or DEFAULT as the new attribute value