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