Introduction to SQL
IBM developed the original version of SQL, originally called Sequel, as part of the System R project in the
early 1970s. The Sequel language has evolved since then, and its name has changed to SQL (Structured
Query Language). Many products now support the SQL language. SQL has clearly established itself as
the standard relational database language.
In 1986, the American National Standards Institute (ANSI) and the International Organization for
Standardization (ISO) published an SQL standard, called SQL-86. ANSI published an extended standard
for SQL, SQL-89, in 1989. The next version of the standard was SQL-92 standard, followed by
SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011,SQL:2016 and most recently SQL:2023.
A Brief History of SQL
The forerunner of SQL, which was called QUEL, first emerged in the specifications for System/R,
IBM’s experimental relational database, in the late 1970s. However, two other products, with
various names for their query languages, beat IBM to the marketplace with the first commercial
relational database products: Relational Software’s Oracle and Relational Technology’s Ingres. IBM
released SQL/DS in 1982, with the query language named Structured English Query Language
(SEQUEL). However, when IBM learned that SEQUEL was a trademark owned by Hawker Siddeley
Aircraft Company of the UK, the name was changed to SQL. As a result of the name change, you will
hear the name pronounced both as a word (sequel) and as a string of letters (S-Q-L), and while the
later is generally preferred, both are considered correct.
• There is a free
version of MySQL.
Introduction to SQL
• SQL is a command-based language.
• SQL statements are formed in clauses using keywords and
parameters.
• The keywords used are usually reserved words for the DBMS,
meaning they cannot be used for the names of database objects.
• The clauses usually have to appear in a prescribed sequence.
• SQL statements should be terminated with a semicolon (;).
• The program you use to connect to the database and interact with it
is called an SQL client.
SQL statements can be divided into the following categories:
• Data Query Language (DQL) Statements that query the database but do not alter any data or database
objects. This category contains the SELECT statement. Not all vendors make a distinction here; many lump
DQL into DML, as defined next.
• Data Manipulation Language (DML) Statements that modify data stored in database objects (that is, tables).
This category contains the INSERT, UPDATE, and DELETE statements.
DML -- provides the ability to query information from the database and to insert tuples into, delete tuples
from, and modify tuples in the database.
• Data Definition Language (DDL) Statements that create and modify database objects. Whereas DML and
DQL work with the data in the database objects, DDL works with the database objects themselves. In other
words, DDL manages the data containers whereas DML manages the data inside the containers. This
category includes the CREATE, ALTER, and DROP statements. The DDL includes commands for specifying
integrity constraints. The DDL includes commands for defining views.
• Data Control Language (DCL) Statements that manage privileges that database users have regarding the
database and objects stored in it. This category includes the GRANT and REVOKE statements.
Data Definition Language
The SQL data-definition language (DDL) allows the specification of
information about relations, including:
The schema for each relation.
The type of values associated with each attribute.
The Integrity constraints
The set of indices to be maintained for each relation.
Security and authorization information for each relation.
The physical storage structure of each relation on disk.
Domain Types in SQL
The SQL standard supports a variety of built-in types. Some are
char(n). Fixed length character string, with user-specified length n.
varchar(n). Variable length character strings, with user-specified maximum length n.
int. Integer (a finite subset of the integers that is machine-dependent).
smallint. Small integer (a machine-dependent subset of the integer domain type).
numeric(p,d). Fixed point number, with user-specified precision of p digits, with d digits to the right of
decimal point. (ex., numeric(3,1), allows 44.5 to be stores exactly, but not 444.5 or 0.32)
real, double precision. Floating point and double-precision floating point numbers, with machine-
dependent precision.
float(n). Floating point number, with user-specified precision of at least n digits.
Create Table Construct
An SQL relation is defined using the create table command:
create table r
(A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),
...,
(integrity-constraintk));
• r is the name of the relation
• each Ai is an attribute name in the schema of relation r
• Di is the data type of values in the domain of attribute Ai
Example:
create table department
(dept_name varchar (20),
building varchar (15),
budget numeric (12,2),
primary key (dept_name));
Integrity Constraints in Create Table
SQL supports a number of different integrity constraints.
Types of integrity constraints
• primary key (A1, ..., An )
• foreign key (Am, ..., An ) references r (Am, ..., An )
• not null
SQL prevents any update to the database that violates an integrity constraint.
Example:
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department(dept_name));
Integrity Constraints in Create Table
SQL supports a number of different integrity constraints.
Types of integrity constraints
• primary key (A1, ..., An )
- The primary-key specification says that attributes (A1, ..., An ) form the primary key for the relation.
The primary-key attributes are required to be nonnull and unique; that is, no tuple can have a null value for
a primary-key attribute, and no two tuples in the relation can be equal on all the primary-key attributes.
Although the primary-key specification is optional, it is generally a good idea to specify a primary key for
each relation.
Integrity Constraints in Create Table
SQL supports a number of different integrity constraints.
Types of integrity constraints
• foreign key (Am, ..., An ) references r
- The foreign key specification says that the values of attributes (Am, ..., An ) for any tuple in the
relation must correspond to values of the primary key attributes of some tuple in relation s.
- Following slide presents a partial SQL DDL definition of the university database we use. The
definition of the course table has a declaration “foreign key (dept name) references
department(dept_name)”.
- This foreign-key declaration specifies that for each course tuple, the department name
specified in the tuple must exist in the primary key attribute (dept_name) of the department
relation. Without this constraint, it is possible for a course to specify a nonexistent department
name.
Integrity Constraints in Create Table
SQL supports a number of different integrity constraints.
Types of integrity constraint
• not null
The not null constraint on an attribute specifies that the null value is not
allowed for that attribute; in other words, the constraint excludes the null value
from the domain of that attribute. For example, the not null constraint
on the name attribute of the instructor relation ensures that the name of an
instructor cannot be null.
Integrity Constraints in Create Table
SQL supports a number of different integrity constraints.
Types of integrity constraints
• primary key (A1, ..., An )
• foreign key (Am, ..., An ) references r (Am, ..., An )
• not null
SQL prevents any update to the database that violates an integrity constraint.
Example:
create table instructor (
ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department(dept_name));
Check
When applied to a relation declaration, the clause check(P) specifies a predicate P that must be satisfied by
every tuple in a relation. A common use of the check clause is to ensure that attribute values satisfy specified
conditions.
create table section
(course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
building varchar (15),
room number varchar (7),
time_slot_id varchar (4),
primary key (course_id, sec_id, semester, year),
check (semester in ('Fall', 'Winter', 'Spring', 'Summer')));
Unique Constraint
The unique specification says that attributes Aj1, Aj2 ,…, Ajm form a superkey; that is, no two tuples in the
relation can be equal on all the listed attributes. However, attributes declared as unique are permitted to be
null unless they have explicitly been declared to be not null. Recall that a null value does not equal any other
value.
CREATE TABLE Persons (
ID int ,
LastName varchar(50) ,
FirstName varchar(50),
Age int,
UNIQUE (ID)
);
Show columns of table
• Show columns from table name;
Practice
Create the database called university and add the
following tables using MySQL.
create table department
create table section
(dept_name varchar (20), (course_id varchar (8),
building varchar (15), sec id varchar (8),
semester varchar (6),
budget numeric (12,2),
year numeric (4,0),
primary key (dept_name));
building varchar (15),
room number varchar (7),
create table course time slot_id varchar (4),
primary key (course_id, sec_id, semester, year),
(course id varchar (7),
foreign key (course id) references course(course_id));
title varchar (50),
dept_name varchar (20), create table teaches
(ID varchar (5),
credits numeric (2,0),
course_id varchar (8),
primary key (course id),
sec-id varchar (8),
foreign key (dept_name) references department(dept_name); semester varchar (6),
year numeric (4,0),
primary key (ID, course_id, sec_id, semester, year),
create table instructor
foreign key (course_id, sec_id, semester, year) references section (course_id, sec_id, semester, year),
(ID varchar (5), foreign key (ID) references instructor(ID));
name varchar (20) not null,
dept_name varchar (20),
salary numeric (8,2),
primary key (ID),
foreign key (dept_name) references department(dept_name));
Practice
INSERT INTO
To insert data into a relation, we either specify a tuple to be inserted or write a query whose result
is a set of tuples to be inserted. The attribute values for inserted tuples must be members of the
corresponding attribute’s domain. Similarly, tuples inserted must have the correct number of
attributes.
The simplest insert statement is a request to insert one tuple. Suppose that we wish to insert the
fact that there is a course CS-437 in the Computer Science department with title “Database
Systems” and four credit hours. We write:
insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
INSERT INTO
SQL allows the attributes to be specified as part of the insert statement. For example, the following
SQL insert statements are identical in function to the preceding one:
insert into course (course id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);
insert into course (title, course_id, credits, dept_name)
values ('Database Systems', 'CS-437', 4, 'Comp. Sci.');
Practice
Create classroom table and insert the following data.
Basic Query Structure
A typical SQL query has the form:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
• Ai represents an attribute
• ri represents a relation
• P is a predicate.
The result of an SQL query is a relation.
The select Clause
The select clause lists the attributes desired in the result of a query
• corresponds to the projection operation of the relational algebra
Example: find the names of all instructors:
select name
from instructor
NOTE: SQL names are case insensitive (i.e., you may use upper- or
lower-case letters.)
• E.g., Name ≡ NAME ≡ name
• Some people use upper case wherever we use bold font.
The select Clause
SQL allows duplicates in relations as well as in query results.
To force the elimination of duplicates, insert the keyword distinct after
select.
Find the department names of all instructors, and remove duplicates
select distinct dept_name
from instructor
Practice
Practice
Instructor
The select Clause
The select clause can contain arithmetic expressions involving the
operation, +, –, , and /, and operating on constants or attributes of
tuples.
• The query:
select ID, name, salary/12
from instructor;
would return a relation that is the same as the instructor relation,
except that the value of the attribute salary is divided by 12.
• Can rename “salary/12” using the as clause:
select ID, name, salary/12 as monthly_salary from instructor;
The where Clause
The where clause specifies conditions that the result must satisfy
• Corresponds to the selection predicate of the relational algebra.
To find all instructors in Comp. Sci. dept
select name
from instructor
where dept_name = “Comp. Sci.”;
SQL allows the use of the logical connectives and, or, and not
The operands of the logical connectives can be expressions involving the
comparison operators <, <=, >, >=, =, and <>.
Comparisons can be applied to results of arithmetic expressions
To find all instructors in Comp. Sci. dept with salary > 70000
select name
from instructor
where dept_name = “Comp. Sci.” and salary > 70000;
Practice
The from Clause
The from clause lists the relations involved in the query
• Corresponds to the Cartesian product operation of the relational
algebra.
Find the Cartesian product instructor X teaches
select
from instructor, teaches;
• generates every possible instructor – teaches pair, with all attributes
from both relations.
• For common attributes (e.g., ID), the attributes in the resulting table
are renamed using the relation name (e.g., instructor.ID)
Cartesian product not very useful directly, but useful combined with
where-clause condition (selection operation in relational algebra).
Examples
Find the names of all instructors who have
taught some course and the course_id
• select name, course_id
from instructor , teaches
where instructor.ID = teaches.ID;
Examples
Find the names of all instructors in the Art
department who have taught some course and the
course_id
• select name, course_id
from instructor , teaches
where instructor.ID = teaches.ID
and instructor. dept_name = ‘History‘;
=
Where Clause Predicates
• SQL includes a between comparison operator
• Example: Find the names of all instructors with
salary between $90,000 and $100,000 (that is,
$90,000 and $100,000)
• select name
from instructor
where salary between 90000 and 100000;
Where Clause Predicates
• Tuple comparison
• select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID,
'Biology');
