Unit 2:
SQL AND PL/SQL
Data Definition Language
•The SQL data-definition language (DDL) allows the specification
of information about relations, including:
The schema for each relation.
The domain of values associated with each attribute.
Integrity constraints
And as we will see later, also other information such as
The set of indices to be maintained for each relations.
Security and authorization information for each relation.
The physical storage structure of each relation on disk.
Domain Types in SQL
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.
More are covered in Chapter 4.
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 instructor (
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 instructor (
ID char(5),
Name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
Integrity Constraints in Create Table
not null
primary key (A1, ..., An )
foreign key (Am, ..., An ) references r
•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);
•primary key declaration on an attribute automatically ensures not null
Updates to tables
Insert
insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);
Delete
Remove all tuples from the student relation
delete from student
Drop Table
drop table r
Alter
alter table r add A D
where A is the name of the attribute to be added to relation
• r and D is the domain of A.
All exiting tuples in the relation are assigned null as the value for
the new attribute.
alter table r drop A
where A is the name of an attribute of relation r
Dropping of attributes not supported by many databases.
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 (Cont.)
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
The keyword all specifies that duplicates should not be
removed.
• select all dept_name
• from instructor
The select Clause (Cont.)
An asterisk in the select clause denotes “all attributes”
• select *
• from instructor
An attribute can be a literal with no from clause
• select ‘437’
Results is a table with one column and a single row with value “437”
Can give the column a name using:
• select ‘437’ as FOO
An attribute can be a literal with from clause
• select ‘A’
• from instructor
Result is a table with one column and N rows (number of tuples in the
• instructors table), each row with value “A”
The select Clause (Cont.)
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
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.'
Comparison results can be combined using the logical connectives
• and, or, and not
To find all instructors in Comp. Sci. dept with salary > 80000
• select name
• from instructor
• where dept_name = ‘Comp. Sci.' and salary > 80000
Comparisons can be applied to results of arithmetic expressions.
The Rename Operation
The SQL allows renaming relations and attributes using the as
clause:
• old-name as new-name
Find the names of all instructors who have a higher salary
than some instructor in ‘Comp. Sci’.
select distinct T.name
• from instructor as T, instructor as S
• where T.salary > S.salary and S.dept_name = ‘Comp. Sci.’
Keyword as is optional and may be omitted
• instructor as T ≡ instructor T
String Operations
SQL includes a string-matching operator for comparisons on
character strings. The operator like uses patterns that are
described using two special characters:
percent ( % ). The % character matches any substring.
underscore ( _ ). The _ character matches any character.
Find the names of all instructors whose name includes the
substring “dar”.
• select name
• from instructor
• where name like '%dar%'
Match the string “100%”
• like ‘100 \%'escape '\'
• in that above we use backslash (\) as the escape character.
String Operations (Cont.)
Patterns are case sensitive.
Pattern matching examples:
‘Intro%’ matches any string beginning with “Intro”.
‘%Comp%’ matches any string containing “Comp” as a
substring.
‘_ _ _’ matches any string of exactly three characters.
‘_ _ _ %’ matches any string of at least three characters.
SQL supports a variety of string operations such as
concatenation (using “||”)
converting from upper to lower case (and vice versa)
finding string length, extracting substrings, etc.
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
Tuple comparison
select name, course_id
• from instructor, teaches
• where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);
Set Operations
Find courses that ran in Fall 2009 or in Spring 2010
• (select course_id from section where sem = ‘Fall’ and year = 2009)
• union
• (select course_id from section where sem = ‘Spring’ and year = 2010)
Find courses that ran in Fall 2009 and in Spring 2010
• (select course_id from section where sem = ‘Fall’ and year = 2009)
• intersect
• (select course_id from section where sem = ‘Spring’ and year = 2010)
Find courses that ran in Fall 2009 but not in Spring 2010
• (select course_id from section where sem = ‘Fall’ and year = 2009)
• except
• (select course_id from section where sem = ‘Spring’ and year = 2010)
Set Operations (Cont.)
Find the salaries of all instructors that are less than the largest salary.
select distinct T.salary
• from instructor as T, instructor as S
• where T.salary < S.salary
Find all the salaries of all instructors
select distinct salary
• from instructor
Find the largest salary of all instructors.
(select “second query” )
• except
• (select “first query”)
Set Operations (Cont.)
Set operations union, intersect, and except
Each of the above operations automatically eliminates
duplicates
To retain all duplicates use the corresponding multiset versions
union all, intersect all and except all.
Suppose a tuple occurs m times in r and n times in s, then, it
occurs:
m + n times in r union all s
min(m,n) times in r intersect all s
max(0, m – n) times in r except all s
Aggregate Functions
These functions operate on the multiset of values of a column of a relation,
and return a value
• avg: average value
• min:
minimum value
• max:
maximum value
• sum: sum
of values
• count:
number of values
Aggregate Functions (Cont.)
Find the average salary of instructors in the Computer Science
• department
select avg (salary)
• from instructor
• where dept_name= ’Comp. Sci.’;
Find the total number of instructors who teach a course in the
Spring 2010 semester
select count (distinct ID)
• from teaches
• where semester = ’Spring’ and year = 2010;
Find the number of tuples in the course relation
select count (*)
• from course;
Aggregate Functions – Group By
Find the average salary of instructors in each department
select dept_name, avg (salary) as avg_salary
• from instructor
• group by dept_name;
Aggregation (Cont.)
Attributes in select clause outside of aggregate functions must
appear in group by list
/* erroneous query */
• select dept_name, ID, avg (salary)
• from instructor
• group by dept_name;
Aggregate Functions – Having Clause
Find the names and average salaries of all departments
whose average salary is greater than 42000
• select dept_name, avg (salary)
• from instructor
• group by dept_name
• having avg (salary) > 42000;
• Note: predicates in the having clause are applied
after the formation of groups whereas predicates
in the where clause are applied before forming
groups
