SQL stands for Standard Query Language for relational databases. It is based on formal query languages such as relational algebra and tuple calculus.
SQL was originally developed in the 1970s as part of the IBM System R project. IBM originally called it “SEQUEL” (Structured English Query Language). The name changed in the 1980s to just “SQL”.
SQL is declarative, meaning that a query specifies what data to retrieve, not how to retrieve it. A query typically takes several tables as input and returns a single table as the result. For example:
select instructor.ID, department.dept_name
from instructor, department
where instructor.dept_name= department.dept_name and
department.budget > 95000;
Some details:
- SQL commands are not case-sensitive.
SELECT
,Select
, andselect
are treated the same.
- Use single quotes for string constants:
'working'
is valid, while"bad"
is not.
Unfortunately, despite the existence of a SQL standard, each DBMS has their own SQL dialect and each has different feature sets and syntactic quirks.
Subtopics
SQL and Relational Algebra
SQL is based on relational algebra, but there are some notable differences. One of the big differences is that relational algebra is based on sets (no duplicate), where SQL is based on bags (duplicates allowed).
Another difference is the choice of terminology. Notably, the select operator (where
clause in SQL, while the project operator (select
clause in SQL.
Keywords
select
: Specifies the columns to retrieve.- corresponds to the projection (
) operation in relational algebra.
- corresponds to the projection (
from
: Indicates the table(s) from which to retrieve data.where
: Specifies conditions for including rows in the result- corresponds to the selection (
) operation in relational algebra
- corresponds to the selection (
group by
: Categorizes rows into groups.having
: Specifies conditions for including groups in the result.order by
: Sorts the result based on specified criteria.
Remove Duplicates
SQL queries can return duplicate rows. To eliminate duplicates, use the distinct
keyword:
select distinct column_name from table_name;
Arithmetic Expressions
SQL supports arithmetic operations within queries:
select uos_code, title, points*2
from UnitOfStudy
where
Clause
- comparison operators in SQL:
=
,>
,>=
,<
,<=
,<>
(or!=
)
String Operations
SQL includes string-matching functionality through the LIKE
operator:
wildcards:
- percent (
%
): matches any substring - underscore (
_
): matches a single character
Rename Operation
You can rename columns using the as
keyword (i.e. old_name as new_name
)
e.g.
select a.uos_code as course_code, a.credit_points from UnitOfStudy as a;
Order By Clause
The order by
clause allows sorting of results:
- You can specify ascending (
ASC
) or descending (DESC
) order. - Example:
order by country DESC, name ASC
.
Set Operations
SQL supports set operations such as UNION
, INTERSECT
, and EXCEPT
, which also eliminate duplicates by default.