Structured Query Language or SQL is the standard query language for relational databases.

•  It first became an official standard in 1986 as defined by the American National Standards Institute (ANSI).
•  All major database vendors conform to the SQL standard with minor variations in syntax (different dialects).
•  SQL consists of both a Data Definition Language (DDL) and a Data Manipulation Language (DML).
•  SQL is a declarative language (non-procedural). A SQL query specifies what to retrieve but not how to retrieve it.
•  SQL is not a complete programming language. It does not have control or iteration commands

SQL Basic Rules

Some basic rules for SQL statements: .

1) There is a set of reserved words that cannot be used as names for database objects.

        SELECT, FROM, WHERE, etc

2) SQL is generally case-insensitive.

Only exception is string constants. 'FRED' not the same as 'fred'.

3) SQL is free-format and white-space is ignored.

4) The semi-colon is often used as a statement terminator, although that is not part of the standard.

5) Date and time constants have defined format:

 DATE 'YYYY-MM-DD' e.g. DATE '1975-05-17'

 TIME 'hh:mm:ss[.f]' e.g. TIME '15:00:00', TIME '08:30:30.5'

6) Two single quotes '' are used to represent a single quote character in a character constant. e.g. 'Master''s'.

SQL Queries

The query portion of SQL is performed using a SELECT statement. The general form of the statement is:

SELECT A1 , A2 , … , An    <--------     attributes in result

 FROM R1 , R2 , … , Rm     <----------    tables in query

WHERE (condition) Notes:

1) The "*" is used to select all attributes.

2) Combines the relational algebra operators of selection, projection, and join into a single statement.

3) Comparison operators: =, <>, >, <, >=, <=.

Example Relations

emp (eno, ename, bdate, title, salary, supereno, dno)

proj (pno, pname, budget, dno)

dept (dno, dname, mgreno)

workson (eno, pno, resp, hours)

Foreign keys:

emp: emp.supereno to emp.eno, emp.dno to dept.dno

proj: proj.dno to dept.dno

dept: dept.mgreno to emp.eno

workson: workson.eno to emp.eno, workson.pno to proj.pno

Return the employee name and salary of all employees whose title is 'EE':

SELECT ename, salary

FROM emp

WHERE title = 'EE';

One Relation Query  more Examples  

Return the birth date and salary of employee 'J. Doe'

SELECT bdate, salary

 FROM emp

WHERE ename = 'J. Doe';

Return all information on all employees:

  SELECT * FROM emp;   <------returns all attributes

Return the employee number, project number, and number of hours worked where the hours worked is > 50:

  SELECT eno, pno, hours

    FROM workson

    WHERE hours > 50

Duplicates in SQL  

One major difference between SQL and relational algebra is that relations in SQL are bags instead of sets. It is possible to have two or more identical rows in a relation.

Consider the query:

Return all titles of employees.

SELECT title

FROM emp

Join Query Example

Multiple tables can be queried in a single SQL statement by listing them in the FROM clause. Note that if you do not specify any join condition to relate them in the WHERE clause, you get a cross product of the tables.

Example: Return the employees who are assigned to the 'Management' department.

SELECT ename

 FROM emp, dept

WHERE dname = 'Management'

                 and emp.dno = dept.dno

Join Query Examples  

Return the department names and the projects in each department:  

SELECT dname, pname

FROM dept, proj

WHERE dept.dno = proj.dno;  

Return the employees and the names of their department:

  SELECT ename, dname

  FROM emp, dept

  WHERE emp.dno=dept.dno;  

Return all projects who have an employee working on them whose title is 'EE':    

SELECT pname

FROM emp, proj, workson

WHERE emp.title = 'EE'

and workson.eno=emp.eno

and workson.pno = proj.pno;  

Practice Questions

  Relational database schema:

emp (eno, ename, bdate, title, salary, supereno, dno)

proj (pno, pname, budget, dno)

dept (dno, dname, mgreno)

 workson (eno, pno, resp, hours)  

1) Return the project names that have a budget > 250000.

2) Return the employees born after July 1, 1970 that have a salary > 35000 and have a title of 'SA' or 'PR'.

3) Return a list of all department names, the names of the projects of that department, and the name of the manager of each department.

4) Return a list of all distinct combinations of title and salary in the database.

Calculated Fields  

Mathematical expressions are allowed in the SELECT clause to perform simple calculations.

When an expression is used to define an attribute, the DBMS gives the attribute a unique name such as col1, col2, etc.

Example:

Return how much employee 'A. Lee' will get paid for his work on each project.

SELECT ename, pname, salary/52/5/8*hours

FROM emp, workson, proj

WHERE emp.eno = workson.eno and ename=‘A. Lee’

Renaming and Aliasing  

Often it is useful to be able to rename an attribute in the final result (especially when using calculated fields). Renaming is ename pname pay A. Lee Budget 192.31 A. Lee Maintenance 923.08 accomplished using the keyword AS:

SELECT ename, pname, salary/52/5/8*hours AS pay

FROM emp, workson, proj

WHERE emp.eno = workson.eno

 and ename=‘A. Lee’

and proj.pno = workson.pno

 AS keyword is optional.

Renaming is also used when two or more copies of the same table are in a query. Using aliases allows you to uniquely identify what table you are talking about.

Example: Return the employees and their managers where the managers make less than the employee. S

ELECT E.ename, M.ename

 FROM emp as E, emp as M

 WHERE E.mgreno = M.eno

 and E.salary > M.salary;

Advanced Conditions – BETWEEN  

 Sometimes the condition in the WHERE clause will request tuples where one attribute value must be in a range of values. Example: Return the employees who make at least $20,000 and less than or equal to $45,000.  

SELECT ename

FROM emp

WHERE salary >= 20000 and salary <= 45000

We can use the keyword BETWEEN instead:

SELECT ename FROM emp WHERE salary between 20000 and 45000

Advanced Conditions - LIKE  

For string valued attributes, the LIKE operator is used to search for partial matches. Partial string matches are specified by using either "%" that replaces an arbitrary number of characters or underscore "_" that replaces a single character.

Example: Return all employee names that start with 'A'. Example:

 Return all employee names who have a first name that starts with 'J' and whose last name is 3 characters long.

SELECT ename

 FROM emp

WHERE ename LIKE 'A%';

SELECT ename

 FROM emp

WHERE ename LIKE 'J. _ _ _';  

Performance Concerns of LIKE  

Warning: Do not use the LIKE operator if you do not have to. It is one of the most inefficient operations. The reason is that the DBMS is not able to optimize lookup using LIKE as it can for equal (=) comparisons. The result is the DBMS often has to examine ALL TUPLES in the relation. In almost all cases, adding indexes will not increase the performance of LIKE queries because the indexes cannot be used.

 Most indexes are implemented using B-trees that allow for fast equality searching and efficient range searches.  

Advanced Conditions - IN  

To specify that an attribute value should be in a given set of values, the IN keyword is used.

Example: Return all employees who are in any one of the departments {'D1', 'D2', 'D3'}.

SELECT ename

 FROM emp

WHERE dno IN ('D1','D2','D3');  

 Note that this is equivalent to using OR:  

 SELECT ename

 FROM emp

WHERE dno = 'D1'

OR dno = 'D2'

OR dno = 'D3';

However, we will see more practical uses of IN and NOT IN when we study nested subqueries.

Advanced Conditions - NULL  
Remember NULL is used to indicate that a given attribute does not have a value.

 To determine if an attribute is NULL, we use the clause IS NULL. Note that you cannot test NULL values using = and <>. Example: Return all employees who are not in a department.

SELECT ename

FROM emp

 WHERE dno IS NULL

Example: Return all departments that have a manager.

  SELECT dname

 FROM dept

WHERE mgreno IS NOT NULL 

Set Operations

  The basic set operations of union, intersection, and difference can be performed in SQL. They are generally used to combine the results of two separate SQL queries. UNION defined in SQL1, INTERSECT, EXCEPT in SQL2

Example: Return the employees who are either directly supervised by 'R. Davis' or directly supervised by 'M. Smith'.

(SELECT E.ename

FROM emp as E, emp as M

 WHERE E.supereno = M.eno and M.ename='R. Davis')

UNION

 (SELECT E.ename

 FROM emp as E, emp as M

WHERE E.supereno = M.eno

and M.ename='M. Smith');

SELECT INTO  

The result of a select statement can be stored in a temporary table using the INTO keyword:

SELECT E.ename INTO davisMgr

FROM emp as E, emp as M

WHERE E.supereno = M.eno and M.ename='R. Davis'

This can be used for set operations instead of using parentheses  

 SELECT E.ename INTO smithMgr

 FROM emp as E, emp as M

WHERE E.supereno = M.eno

 and M.ename=’M. Smith'

davisMgr UNION smithMgr;

Ordering Result Data  

The query result returned is not ordered on any attribute by default. We can order the data using the ORDER BY clause:

SELECT ename, salary, bdate

FROM emp

WHERE salary > 30000

ORDER BY salary DESC, ename ASC;

• 'ASC' sorts the data in ascending order, and 'DESC' sorts it in descending order. The default is 'ASC'. ·
• The order of sorted attributes is significant. The first attribute specified is sorted on first, then the second attribute is used to break any ties, etc.
• NULL is normally treated as less than all non-null values

Practice Questions

  Relational database schema:

emp (eno, ename, bdate, title, salary, supereno, dno)

proj (pno, pname, budget, dno)

dept (dno, dname, mgreno)

workson (eno, pno, resp, hours)    

 1) Calculate the monthly salary for each employee.

2) Return the list of employees who make less than their managers and how much less they make.

3) Return a list of distinct projects where an employee has worked more hours on the project than a manager on the project. 4) List all employees who do not have a supervisor.