Dbms ii mca-ch7-sql-2013

Aruna Devi C (DSCASC) 1
SQL
Chapter 7
Unit IV
Data Base Management System
[DBMS]

SQL
• The name SQL is presently expanded as Structured Query Language.
• Originally, SQL was called SEQUEL (Structured English QUEry
Language) and was designed and implemented at IBM Research as
the interface for an experimental relational database system called
SYSTEM R.
• SQL is now the standard language for commercial relational DBMSs.

History - SQL
IBM Sequel language developed as part of System R project at the
IBM San Jose Research Laboratory.
Renamed as Structured Query Language (SQL)
ANSI and ISO standard SQL:
* SQL-86
* SQL-89
* SQL-92
* SQL:1999 (language name became Y2K compliant!)
* SQL:2003
* SQL:2006 ( XML features)
* SQL:2008 (Object Database features)
SQL is based on set and relational operations with certain modifications and
enhancements.
SQL is not case sensitive.

SQL Data Definition and Data Types
• SQL uses the terms table, row, and column for the formal relational model
terms relation, tuple, and attribute, respectively.
• SQL command for data definition is the CREATE statement, which can be used
to create schemas, tables (relations), and domains (as well as other constructs
such as views, assertions, and triggers).
Schema and Catalog Concepts in SQL:
• An SQL schema is identified by a schema name, and includes an
authorization identifier to indicate the user or account who owns the schema,
as well as descriptors for each element in the schema.
• Schema elements include tables, constraints, views, domains, and other
constructs (such as authorization grants) that describe the schema.

SQL Data Definition and Data Types (cont.,)
• A schema is created via the CREATE SCHEMA statement, which can include
all the schema elements’ definitions.
• The following statement creates a schema called COMPANY, owned by the
user with authorization identifier ‘Jsmith’.
• Note that each statement in SQL ends with a semicolon.
CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;

• SQL uses the concept of a catalog—a named collection of schemas in an SQL
environment.
• An SQL environment is basically an installation of an SQL-compliant RDBMS
on a computer system.
• A catalog always contains a special schema called INFORMATION_SCHEMA,
which provides information on all the schemas in the catalog and all the
element descriptors in these schemas.

SQL
SQL can be divided into two parts:
* Data Definition Language (DDL)
* Data Manipulation Language (DML)
DDL:
The DDL part of SQL permits database tables to be created or
deleted. It also define indexes (keys), specify links between tables, and impose constraints
between tables. The most important DDL statements in SQL are:
» CREATE TABLE - creates a new table
» ALTER TABLE - modifies a table
» DROP TABLE - deletes a table
DML:
The query and update commands form the DML part of SQL:
» SELECT - extracts data from a database
» UPDATE - updates data in a database
» DELETE - deletes data from a database
» INSERT INTO - inserts new data into a database

The CREATE TABLE Command in SQL:
The CREATE TABLE command is used to specify a new relation by giving it a name and
specifying its attributes and initial constraints.
• An SQL relation is defined using the create table command:
create table r (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1), ...)
– 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 branch
(branch_name char(15) not null,
branch_city char(30),
assets integer)

The CREATE TABLE Command in SQL: (Cont.,)

Company Database

Attribute Data Types and Domains in SQL:
The basic data types available for attributes include numeric, character
string, bit string, Boolean, date, and time.
Numeric data types:
• Integer numbers of various sizes (INTEGER or INT, and SMALLINT) and floating-point (real)
numbers of various precision (FLOAT or REAL, and DOUBLE PRECISION).
• Formatted numbers can be declared by using DECIMAL(i,j)—or DEC(i,j) or NUMERIC(i,j)—
where i, the precision, is the total number of decimal digits.
Character-string data types:
• Fixed length — CHAR(n) or CHARACTER(n), where n is the number of characters.
• Varying length— VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n), where
n is the maximum number of characters.
Note: A literal string value is placed between single quotation marks and it is case sensitive

Attribute Data Types and Domains in SQL (cont.,)
CLOB data type:
• CLOB is to specify columns that have large text values, such as documents.
• The CLOB maximum length can be specified in kilobytes (K), megabytes (M), or gigabytes
(G).
• For example, CLOB(20M) specifies a maximum length of 20 megabytes.
Bit-string data types:
• Bit string are either of fixed length n—BIT(n)—or varying length—BIT VARYING(n), where n
is the maximum number of bits.
BLOB data type:
• The default for n, the length of a character string or bit string, is 1.
• BLOB is also available to specify columns that have large binary values, such as images.
• The BLOB maximum length can be specified in kilobits (K), megabits (M), or gigabits (G).
• For example, BLOB(30G) specifies a maximum length of 30 gigabits.

Attribute Data Types and Domains in SQL (cont.,)
Boolean data type:
It has the traditional values of TRUE or FALSE.
DATE data type:
It has ten positions, and its components are YEAR, MONTH, and DAY in the
form YYYY-MM-DD.
TIME data type:
It has at least eight positions, with the components HOUR, MINUTE, and
SECOND in the form HH:MM:SS.
Timestamp data type:
TIMESTAMP includes the DATE and TIME fields, plus a minimum of six
positions for decimal fractions of seconds and an optional WITH TIME ZONE
qualifier.
------------------

Specifying Constraints in SQL
• SQL allows NULLs as attribute values, a constraint NOT NULL may be specified
if NULL is not permitted for a particular attribute.
• It is also possible to define a default value for an attribute by appending the
clause DEFAULT <value> to an attribute definition.
• Constraint can restrict attribute or domain values using the CHECK clause
following an attribute or domain definition.
Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

Specifying Key and Referential Integrity Constraints:
• The PRIMARY KEY clause specifies one or more attributes that make up the
primary key of a relation.
• If a primary key has a single attribute, the clause can follow the attribute directly.
• The UNIQUE clause specifies alternate (secondary) keys.
Dname VARCHAR(15) UNIQUE
• Referential integrity is specified via the FOREIGN KEY clause.
Specifying Constraints in SQL (Cont.,)

Specifying Key and Referential Integrity Constraints (cont.,):
• A referential integrity constraint can be violated when tuples are inserted or
deleted, or when a foreign key or primary key attribute value is modified.
• The default action that SQL takes for an integrity violation is to reject the update
operation that will cause a violation, which is known as the RESTRICT option.
• However, the schema designer can specify an alternative action to be taken by
attaching a referential triggered action clause to any foreign key constraint.
• The options include SET NULL, CASCADE, and SET DEFAULT.
• An option must be qualified with either ON DELETE or ON UPDATE.

Specifying Key and Referential Integrity Constraints (cont.,)
Example:
• The database designer chooses ON DELETE SET NULL and ON UPDATE
CASCADE for the foreign key Super_ssn of EMPLOYEE.
• This means that if the tuple for a supervising employee is deleted, the value of
Super_ssn is automatically set to NULL for all employee tuples that were
referencing the deleted employee tuple.
• On the other hand, if the Ssn value for a supervising employee is updated (say,
because it was entered incorrectly), the new value is cascaded to Super_ssn for all
employee tuples referencing the updated employee tuple.
• The action for CASCADE ON DELETE is to delete all the referencing tuples.
• The action for CASCADE ON UPDATE is to change the value of the referencing
foreign key attribute(s) to the updated (new) primary key value for all the
referencing tuples.

• We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on
referential integrity constraints (foreign keys)
CREATE TABLE DEPT
( DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE CASCADE );

CREATE TABLE EMP
( ENAME VARCHAR(30) NOT NULL,
ESSN CHAR(9),
BDATE DATE,
DNO INTEGER DEFAULT 1,
SUPERSSN CHAR(9),
PRIMARY KEY (ESSN),
FOREIGN KEY (DNO) REFERENCES DEPT
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (SUPERSSN) REFERENCES EMP
ON DELETE SET NULL ON UPDATE CASCADE );

Giving Names to Constraints:
• A constraint name is used to identify a particular constraint in case the
constraint must be dropped later and replaced with another constraint.
Specifying Constraints on Tuples Using CHECK:
• CHECK clauses at the end of a CREATE TABLE statement.
• These can be called tuple-based constraints because they apply to each tuple
individually and are checked whenever a tuple is inserted or modified.
CHECK (Dept_create_date <= Mgr_start_date);

Schema Change Statement in SQL
Schema Change Statement in SQL, which can be
used to alter a schema by adding or dropping tables, attributes,
constraints, and other schema elements.
1) DROP Command.
2) The ALTER Command.

Schema Change Statement in SQL (Cont.,)
DROP Command:
The DROP command can be used to drop named schema elements,
such as tables, domains, or constraints.
There are two drop behavior options: CASCADE and RESTRICT.
For example, to remove the COMPANY database schema and all its tables,
domains, and other elements, the CASCADE option is used as follows:
DROP SCHEMA COMPANY CASCADE;

• If the RESTRICT option is chosen in place of CASCADE, the schema is dropped
only if it has no elements in it; otherwise, the DROP command will not be executed.
• If a base relation within a schema is no longer needed, the relation and its definition can be
deleted by using the DROP TABLE command.
• If the RESTRICT option is chosen instead of CASCADE, a table is dropped only if it is not
referenced in any constraints.
• With the CASCADE option, all such constraints, views, and other elements that reference
the table being dropped are also dropped automatically from the schema, along with the
table itself.
• The DROP TABLE command not only deletes all the records in the table
successful, but also removes the table definition from the catalog.
DROP TABLE DEPENDENT CASCADE;

The ALTER Command:
The definition of a base table or of other named schema elements can be
changed by using the ALTER command.
• The possible alter table actions:
1) Add
2)Drop a column (attribute)
3) Alter (or changing) a column definition
• To Add a column
Syntax:
Alter Table <tablename> Add Column A D
ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);
• If no default clause is specified, the new attribute will have NULL in all the tuples of the
relation immediately after the command is executed; hence, the NOT NULL constraint is not
allowed in this case.

• To drop a column, we must choose either CASCADE or RESTRICT for drop
behavior.
Syntax:
• Alter Table <tablename> Drop Column A cascade;
ALTER TABLE COMPANY.EMPLOYEE DROP COLUMN Address CASCADE;
• It is also possible to alter a column definition by dropping an existing default
clause or by defining a new default clause.
Syntax:
Alter Table <tablename> Alter Column A D
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn Varchar2;

Basic Retrieval Queries in SQL
Basic statement for retrieving information from a database is the
SELECT statement.
The SELECT-FROM-WHERE Structure of Basic SQL Queries:
where
• <attribute list> is a list of attribute names whose values are to be retrieved by
• the query.
• <table list> is a list of the relation names required to process the query.
• <condition> is a conditional (Boolean) expression that identifies the tuples
to be retrieved by the query.
SELECT <attribute list>
FROM <table>
WHERE <condition>;

Basic Retrieval Queries in SQL (Cont.,)
• The basic logical comparison operators for comparing attribute values with one
another and with literal constants are =, <, <=, >, >=, and <>.
Query 1: Retrieve the birth date and address of the employee(s) whose name is
John B. Smith’.
SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname=‘John’ AND Minit=‘B’ AND Lname=‘Smith’;
1
Result:
Company Database

Query 2: Retrieve the name and address of all employees who work for the
‘Research’ department.
SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname=‘Research’ AND Dnumber=Dno;
Result:
2
Company Database

Query 3: For every project located in ‘Stafford’, list the project number, the
controlling department number, and the department manager’s last name, address,
and birth date.
SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND
Plocation=‘Stafford’;
Result:
3
Company Database

Ambiguous Attribute Names, Aliasing, Renaming, and
Tuple Variables:
• In SQL, we can use the same name for two (or more) attributes as long as the
attributes are in different relations.
• A query that refers to two or more attributes with the same name must qualify
the attribute name with the relation name by prefixing the relation name to the
attribute name.
SELECT Fname, EMPLOYEE.Name, Address
WHERE DEPARTMENT.Name=‘Research’ AND
DEPARTMENT.Dnumber=EMPLOYEE.Dnumber;

For each employee, retrieve the employee’s first and last name and
the first and last name of his or her immediate supervisor.
SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;
Result:
• E and S, called aliases or tuple variables, for the EMPLOYEE relation. An alias can
follows the keyword AS (or) it can directly follow the relation name.
• EMPLOYEE AS E(Fn, Mi, Ln, Ssn, Bd, Addr, Sex, Sal, Sssn, Dno) in the FROM clause,
Fn becomes an alias for Fname, Mi for Minit, Ln for Lname, and so on.
Aliasing
Company Database

• To retrieve all the attribute values of the selected tuples, we do not have to list the
attribute names explicitly in SQL; we just specify an asterisk (*), which stands for
all the attributes.
SELECT *
FROM EMPLOYEE
WHERE Dno=5;
Company Database
USE OF *

• If we do want to eliminate duplicate tuples from the result of an SQL query, we
use the keyword DISTINCT in the SELECT clause, meaning that only distinct
tuples should remain in the result.
Query: a)Retrieve the salary of every employee,
b) Retrieve all distinct salary values.
SELECT ALL Salary
FROM EMPLOYEE;
SELECT DISTINCT Salary
FROM EMPLOYEE;
Company Database
DISTINCT

• SQL has directly incorporated some set operations.
• There is a union operation (UNION), and in some versions of SQL there are
set difference (MINUS) and intersection (INTERSECT) operations.
• The resulting relations of these set operations are sets of tuples; duplicate
tuples are eliminated from the result.
• The set operations apply only to union compatible relations ; the two relations
must have the same attributes and the attributes must appear in the same
order
SET OPERATIONS:

• Make a list of all project numbers for projects that involve an employee whose last
name is ‘Smith’, either as a worker or as a manager of the department that controls
the project.
SET OPERATIONS (Cont.,)
The first SELECT query retrieves the projects that involve a ‘Smith’ as manager of
the department that controls the project, and the second retrieves the projects
that involve a ‘Smith’ as a worker on the project.

Aruna Devi.C (DSCASC) 38
Set Operations
• Find all customers who have a loan, an account, or both:
(select customer-name from depositor)
except
(select customer-name from borrower)
intersect
Find all customers who have an account but no loan.
union
Find all customers who have both a loan and an account.
TS∪S T
S union T
TS∩S T
S intersect T
TSS - T
S minus T

• SQL includes a string-matching operator for comparisons on character strings.
• Patterns are described using two special characters:
– percent (%) The % character matches any substring.
– underscore (_) The _ character matches any character.
percent (%)
select cname from customer where street like ‘%Main%’
Underscore(_) ”---” matches any string of exactly three character.
“---%” matches any string of atleast three character.
Escape Keyword: It is use when the special pattern like % and _ is in the
data, It is treated like normal character.
like ‘ab%cd%’
escape matches all string beginning with ‘ab%cd’
Eg:select cname from customer where cstreet like ‘%Nagar%’
select cname from customer where cstreet like ‘Jaya%’
select cname from customer where ccity like ‘%lore%’
select cname from customer where cname like ‘_ _ _ _’
Substring Pattern Matching and Arithmetic Operators

• The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction,
multiplication, and division, respectively) can be applied to numeric values in
an SQL query result.
Query: Show the resulting salaries if every employee working on the
‘ProductX’ project is given a 10 percent raise.
SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND
P.Pname=‘ProductX’;
Arithmetic Operators

• Comparison operator, which can be used for convenience, is BETWEEN.
Query: Retrieve all employees in department 5 whose salary is between $30,000
and $40,000.
comparison operator
SELECT *
FROM EMPLOYEE
WHERE (Salary BETWEEN 30000 AND
40000) AND Dno = 5;
The condition (Salary BETWEEN 30000 AND 40000) in Query is equivalent
to the condition ((Salary >= 30000) AND (Salary <= 40000)).

• The ORDER BY clause is used to sort the tuples in a query result based
on the values of some attribute(s).
Retrieve a list of employees and the projects they are working on, ordered by
department and, within each department, ordered alphabetically by last name,
then first name.
SELECT D.Dname, E.Lname, E.Fname, P.Pname
FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W,
PROJECT P
WHERE D.Dnumber= E.Dno AND E.Ssn= W.Essn AND
W.Pno= P.Pnumber
ORDER BY D.Dname, E.Lname, E.Fname;
ORDER BY
We can specify the keyword DESC if we want to see the result in a descending order of
values. The keyword ASC can be used to specify ascending order explicitly

INSERT, DELETE, and UPDATE Statements in SQL
• In its simplest form, it is used to add one or more tuples to a relation.
• Attribute values should be listed in the same order as the attributes were
specified in the CREATE TABLE command.
INSERT INTO EMPLOYEE
VALUES ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’, ‘98
Oak Forest, Katy, TX’, ‘M’, 37000, ‘653298653’, 4 );
INSERT
A second form of the INSERT statement allows the user to specify explicit
attribute names that correspond to the values provided in the INSERT command.
INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)
VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);
Attributes not specified in query are set to their DEFAULT or to NULL.

INSERT, DELETE, and UPDATE Statements in SQL (Cont.,)
• To remove tuples from a relation.
• Includes a WHERE-clause to select the tuples to be deleted.
• Tuples are deleted from only one table at a time (unless CASCADE is specified
on a referential integrity constraint).
• A missing WHERE-clause specifies that all tuples in the relation are to be
deleted; the table then becomes an empty table.
• The number of tuples deleted depends on the number of tuples in the relation
that satisfy the WHERE-clause.
• Referential integrity should be enforced
DELETE

DELETE FROM EMPLOYEE
WHERE Lname=‘Brown’;
DELETE
DELETE FROM EMPLOYEE
WHERE Ssn=‘123456789’;
DELETE FROM EMPLOYEE;

• Update is used to modify attribute values of one or more selected tuples.
• A WHERE-clause selects the tuples to be modified.
• An additional SET-clause specifies the attributes to be modified and their new
values.
• Each command modifies tuples in the same relation.
• Referential integrity should be enforced.
UPDATE

Change the location and controlling department number of project
number 10 to 'Bellaire' and 5, respectively.
UPDATE PROJECT
SET Plocation = ‘Bellaire’, Dnum = 5
WHERE Pnumber=10;
UPDATE
----------------------------------------
Company Database

More Complex SQL Queries
• SQL has various rules for dealing with NULL values.
• NULL is used to represent a missing value.
• That it usually has one of three different interpretations:
1. Value unknown (exists but is not known).
2. Value not available (exists but is purposely withheld).
3. Value not applicable (the attribute is undefined for this tuple).
Example:
• Unknown value: A person’s date of birth is not known, so it is represented by NULL in the
database.
• Unavailable or withheld value: A person has a home phone but does not want it to
be listed, so it is withheld and represented as NULL in the database.
• Not applicable attribute: An attribute LastCollegeDegree would be NULL for a
person who has no college degrees because it does not apply to that person.
Comparisons Involving NULL and Three-Valued Logic

More Complex SQL Queries (Cont.,)
Nested queries:
• A complete SELECT query, called a nested query , can be specified within the
WHERE-clause of another query, called the outer query.
• Many of the previous queries can be specified in an alternative form using
nesting.
Query: Retrieve the name and address of all employees who work for the 'Research'
department.
Nested Queries, Tuples, and Set/Multiset Comparisons
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research' )

Nested queries:
• The nested query selects the number of the 'Research' department.
• The outer query select an EMPLOYEE tuple if its DNO value is in the result of
either nested query.
• The comparison operator IN compares a value v with a set (or multi-set) of
values V, and evaluates to TRUE if v is one of the elements in V.
• In general, we can have several levels of nested queries.
• A reference to an unqualified attribute refers to the relation declared in the
innermost nested query.
• In this example, the nested query is not correlated with the outer query.

Comparison operators:
• In addition to the IN operator, a number of other comparison operators can be
used to compare a single value v.
• The = ANY (or = SOME) operator returns TRUE if the value v is equal to some
value in the set V and is hence equivalent to IN.
• The two keywords ANY and SOME have the same effect. Other operators that
can be combined with ANY (or SOME) include >, >=, <, <=, and <>.
• The keyword ALL can also be combined with each of these operators.
• For example, the comparison condition (v > ALL V) returns TRUE if the value v is
greater than all the values in the set (or multiset) V.

Query: Return the names of employees whose salary is greater than the salary of
all the employees in department 5.
SELECT Lname, Fname
FROM EMPLOYEE
WHERE Salary > ALL ( SELECT Salary
FROM EMPLOYEE
WHERE Dno=5 );
Company Database

Query : Retrieve the name of each employee who has a dependent with the
same first name and is the same sex as the employee.
In the nested query, we must qualify E.Sex because it refers to the Sex attribute of
EMPLOYEE from the outer query, and DEPENDENT also has an attribute called Sex. If there
were any unqualified references to Sex in the nested query, they would refer to the Sex
attribute of DEPENDENT. However, we would not have to qualify the attributes Fname and
Ssn of EMPLOYEE if they appeared in the nested query because the DEPENDENT relation
does not have attributes called Fname and Ssn, so there is no ambiguity.

• If a condition in the WHERE-clause of a nested query references an attribute of a
relation declared in the outer query , the two queries are said to be correlated.
• The result of a correlated nested query is different for each tuple (or combination
of tuples) of the relation(s) the outer query.
Query: Retrieve the name of each employee who has a dependent with the same
first name as the employee.
Correlated Nested Queries
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN (SELECT ESSN
FROM DEPENDENT
WHERE ESSN=E.SSN AND
E.FNAME=DEPENDENT_NAME)

• EXISTS is used to check whether the result of a correlated nested query is empty
(contains no tuples) or not
Query 12: Retrieve the name of each employee who has a dependent with the same
first name as the employee.
EXISTS Functions in SQL
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN AND
FNAME=DEPENDENT_NAME)

Query: Retrieve the names of employees who have no dependents.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN)
The correlated nested query retrieves all DEPENDENT tuples related
to an EMPLOYEE tuple.
If none exist , the EMPLOYEE tuple is selected

Joints:
Join operation take two relations and return another relation.
Can specify a "joined relation" in the FROM-clause
Different SQL JOINs:
• JOIN: Return rows when there is at least one match in both tables.
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the
right table.
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in
the left table.
• FULL JOIN: Return rows when there is a match in one of the tables.
Joined Tables in SQL and Outer Joins

Join operation take two relations and return another relation.
Product (pname, price, category, manufacturer)
Company (cname, stockPrice, country)
Find all products under $200 manufactured in Japan;
return their names and prices.
SELECT PName, Price
FROM Product, Company
WHERE Manufacturer=CName AND Country=‘Japan’
AND Price <= 200
SELECT PName, Price
AND Price <= 200
Join
between Product
and Company
Joins

Joins
• Connect two or more tables:
PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi
Product
Company
CName StockPrice Country
GizmoWorks 25 USA
Canon 65 Japan
Hitachi 15 Japan
What is
the Connection
between
them ?

Joins
PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi
Product Company
Cname StockPrice Country
GizmoWorks 25 USA
Canon 65 Japan
Hitachi 15 Japan
PName Price
SingleTouch $149.99
SELECT PName, Price
AND Price <= 200
SELECT PName, Price
AND Price <= 200

• Join operations take two relations and return as a result another relation.
• These additional operations are typically used as subquery expressions in the
from clause
• Join condition – defines which tuples in the two relations match, and what
attributes are present in the result of the join.
• Join type – defines how tuples in each relation that do not match any tuple in
the other relation (based on the join condition) are treated.
Joins

Query Q1, which retrieves the name and address of every employee who works for
the ‘Research’ department.
Simple Query 1:
WHERE Dname=‘Research’ AND Dnumber=Dno;
• Query1 Can be written with join: [It may be easier to specify the join of the
EMPLOYEE and DEPARTMENT relations first, and then to select the desired
tuples and attributes.]
FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)
WHERE Dname=‘Research’;
Joins
The attributes of such a table are all the attributes of the first table, EMPLOYEE, followed by
all the attributes of the second table, DEPARTMENT

• If the names of the join attributes are not the same in the base relations, it is
possible to rename the attributes so that they match, and then to apply
NATURAL JOIN.
• Query where the DEPARTMENT relation is renamed as DEPT and its attributes are renamed as Dname,
Dno (to match the name of the desired join attribute Dno in the EMPLOYEE table), Mssn, and Msdate.
The implied join condition for this NATURAL JOIN is EMPLOYEE.Dno=DEPT.Dno, because this is the
only pair of attributes with the same name after renaming:
• Query 1 can be also written as
Natural Join
FROM (EMPLOYEE NATURAL JOIN
(DEPARTMENT AS DEPT (Dname, Dno, Mssn, Msdate)))
WHERE Dname=‘Research’;

• The default type of join in a joined table is called an inner join, where a tuple is
included in the result only if a matching tuple exists in the other relation.
• For example, in query, only employees who have a supervisor are included in the
result; an EMPLOYEE tuple whose value for Super_ssn is NULL is excluded. If
the user requires that all employees be included, an OUTER JOIN must be used
explicitly.
SELECT E.Lname AS Employee_name,
S.Lname AS Supervisor_name
FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S
ON E.Super_ssn=S.Ssn);
2

In SQL, the options available for specifying joined tables include:
• INNER JOIN (only pairs of tuples that match the join condition are retrieved,
same as JOIN).
• LEFT OUTER JOIN (every tuple in the left table must appear in the result; if it
does not have a matching tuple, it is padded with NULL values for the attributes
of the right table).
• RIGHT OUTER JOIN (every tuple in the right table must appear in the result; if
it does not have a matching tuple, it is padded with NULL values for the
attributes of the left table), and FULL OUTER JOIN.

Joined Relations – Datasets for Examples
Relation loan
Relation borrower
Note: borrower information not available for L-260 and loan
information not available for L-155

Joined Relations – Examples
• loan inner join borrower on
loan.loan_number = borrower.loan_number
• loan left outer join borrower on
loan.loan_number = borrower.loan_number

• loan natural inner join borrower
• loan natural right outer join borrower

• loan full outer join borrower using (loan_number)
Find all customers who have either an account or a loan (but not both)
at the bank.
select customer_name
from (depositor natural full outer join borrower )
where account_number is null or loan_number is null

• Include COUNT, SUM, MAX, MIN, and AVG
Query: Find the maximum salary, the minimum salary, and the average salary
among all employees.
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE
– Some SQL implementations may not allow more than one function in
the SELECT-clause
Aggregate Functions in SQL

Query: Find the maximum salary, the minimum salary, and the average salary
among employees who work for the 'Research' department.
SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)
WHERE DNO=DNUMBER AND DNAME='Research'
Query: Retrieve the total number of employees in the company
Aggregate Functions in SQL
SELECT COUNT (*) FROM EMPLOYEE
SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME='Research
Query: Retrieve the number of employees in the 'Research' department.

• In many cases, we want to apply the aggregate functions to subgroups of
tuples in a relation Each subgroup of tuples consists of the set of tuples that
have the same value for the grouping attribute(s)
• The function is applied to each subgroup independently
• SQL has a GROUP BY-clause for specifying the grouping attributes, which
must also appear in the SELECT-clause
Grouping: The GROUP BY and HAVING Clauses

Query: For each department, retrieve the department number, the number of
employees in the department, and their average salary.
SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO
– In Query, the EMPLOYEE tuples are divided into groups--each group
having the same value for the grouping attribute DNO
– The COUNT and AVG functions are applied to each such group of tuples
separately
– The SELECT-clause includes only the grouping attribute and the functions
to be applied on each group of tuples
– A join condition can be used in conjunction with grouping
GROUP BY

• Query: For each project, retrieve the project number, project name, and the
number of employees who work on that project.
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
– In this case, the grouping and functions are applied after the joining of
the two relations
GROUP BY

• Sometimes we want to retrieve the values of these functions for only those
groups that satisfy certain conditions
• The HAVING-clause is used for specifying a selection condition on groups
(rather than on individual tuples).
Query: For each project on which more than two employees work , retrieve the
project number, project name, and the number of employees who work on that
project.
SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2
Having Clause
Note:
predicates in the having clause are applied after the formation of groups
whereas predicates in the where clause are applied before forming groups.

Summary of SQL Queries
A query in SQL can consist of up to six clauses, but only the first two, SELECT and
FROM, are mandatory. The clauses are specified in the following order:
SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[GROUP BY <grouping attribute(s)>]
[HAVING <group condition>]
[ORDER BY <attribute list>]

Summary of SQL Queries (cont.,)
• The SELECT-clause lists the attributes or functions to be retrieved.
• The FROM-clause specifies all relations (or aliases) needed in the query but not
those needed in nested queries.
• The WHERE-clause specifies the conditions for selection and join of tuples from
the relations specified in the FROM-clause.
• GROUP BY specifies grouping attributes.
• HAVING specifies a condition for selection of groups.
• ORDER BY specifies an order (Ascending or Descending) for displaying the
result of a query.
• A query is evaluated by first applying the WHERE-clause, then GROUP BY and
HAVING, and finally the SELECT- clause.

Specifying Constraints as Assertions and Triggers
Specifying General Constraints as Assertions in SQL:
• General constraints: constraints that do not fit in the basic SQL categories.
• Mechanism: CREATE ASSERTION
– components include: a constraint name, followed by CHECK, followed by a
condition
• For example, to specify the constraint that the salary of an employee must not be
greater than the salary of the manager of the department that the employee works
for in SQL, we can write the following assertion:

Specifying Constraints as Assertions and Triggers (cont.,)
• The basic technique for writing such assertions is to specify a query that
selects any tuples that violate the desired condition.
• Example, the query selects all employees whose salaries are greater than the
salary of the manager of their department. If the result of the query is not
empty, the assertion is violated.
• On the other hand, the schema designer should use CREATE ASSERTION
only in cases where it is not possible to use CHECK on attributes, domains, or
tuples, so that simple checks are implemented more efficiently by the DBMS.

• In many cases it is convenient to specify the type of action to be taken when
certain events occur and when certain conditions are satisfied.
• For example, it may be useful to specify a condition that, if violated, causes
some user to be informed of the violation. A manager may want to be informed
if an employee’s travel expenses exceed a certain limit by receiving a message
whenever this occurs.
• The CREATE TRIGGER statement is used to implement such actions in SQL.
Triggers in SQL

• A trigger to compare an employee’s salary to his/her supervisor during insert or
update operations:
• Several events can trigger this rule: inserting a new employee record, changing an
employee’s salary, or changing an employee’s supervisor.
• Suppose that the action to take would be to call an external stored procedure
SALARY_VIOLATION, which will notify the supervisor.
CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN
ON EMPLOYEE
FOR EACH ROW
WHEN ( NEW.SALARY > ( SELECT SALARY FROM EMPLOYEE
WHERE SSN = NEW.SUPERVISOR_SSN ) )
INFORM_SUPERVISOR(NEW.Supervisor_ssn,
NEW.Ssn );
Triggers

• The trigger is given the name SALARY_VIOLATION.
• The event(s): These are usually database update operations that are explicitly
applied to the database. In this example the events are: inserting a new employee
record, changing an employee’s salary, or changing an employee’s supervisor.
• The condition that determines whether the rule action should be executed:
Once the triggering event has occurred, an optional condition may be evaluated.
• The action to be taken: The action is usually a sequence of SQL statements, but
it could also be a database transaction or an external program that will be
automatically executed. In this example, the action is to execute the stored
procedure INFORM_SUPERVISOR.
Triggers

Views (Virtual Tables) in SQL
• A view is a “virtual” table that is derived from other tables.
• Allows for limited update operations (since the table may not physically be
stored).
• Allows full query operations.
• A convenience for expressing certain operations.

Views (Virtual Tables) in SQL (Cont.,)
Specification of Views:
SQL command: CREATE VIEW
– a table (view) name
– a possible list of attribute names (for example, when arithmetic operations
are specified or when we want the names to be different from the attributes
in the base relations)
– a query to specify the table contents

Specify a different WORKS_ON table
SQL Views: An Example
CREATE VIEW WORKS_ON1
AS SELECT Fname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber;
Company Database

Using a Virtual Table:
• We can specify SQL queries on a newly create table (view):
SELECT FNAME, LNAME FROM WORKS_ON1
WHERE PNAME=‘ProductX’;
• When no longer needed, a view can be dropped:
DROP WORKS_ON1;

View Implementation:
• View materialization: involves physically creating and keeping a temporary table
– assumption: other queries on the view will follow
– concerns: maintaining correspondence between the base table and the view
when the base table is updated
– strategy: incremental update
View Update:
• Update on a single view without aggregate operations: update may map to an
update on the underlying base table
• Views involving joins: an update may map to an update on the underlying base
relations
– not always possible

Additional Features of SQL
• SQL has various techniques for writing programs in various programming
languages that include SQL statements to access one or more databases. These
include embedded (and dynamic) SQL, SQL/CLI (Call Level Interface) and its
predecessor ODBC (Open Data Base Connectivity), and SQL/PSM (Persistent
Stored Modules).
• Each commercial RDBMS will have, in addition to the SQL commands, a set of
commands for specifying physical database design parameters, file structures for
relations, and access paths such as indexes. We called these commands a storage
definition language (SDL).
• SQL has transaction control commands. These are used to specify units of
database processing for concurrency control and recovery purposes.

Additional Features of SQL (Cont.,)
• SQL has language constructs for specifying the granting and revoking of
privileges to users. Privileges typically correspond to the right to use certain
SQL commands to access certain relations.
• SQL has language constructs for creating triggers.
• SQL has incorporated many features from object-oriented models to have
more powerful capabilities, leading to enhanced relational systems known as
object-relational.
• SQL and relational databases can interact with new technologies such as
• XML and OLAP
----------------------------------

Database Programming: Techniques and Issues
• The majority of database interactions are executed through programs that have been
carefully designed and tested.
• These programs are generally known as application programs or database applications.
Approaches to Database Programming:
• Embedding database commands in a general-purpose programming language:
Database commands are embedded in a general-purpose programming language
• Using a library of database functions:
Available to the host language for database calls; known as an API (Application
Programming Interface)
• Designing a brand-new language:
A database programming language is designed from scratch to be compatible with
the database model and query language.

Database Programming: Techniques and Issues
When a programmer or software engineer writes a program that
requires access to a database, it is quite common for the program to be running on
one computer system while the database is installed on another.
When writing such a program, a common sequence of interaction is the
following:
1. When the client program requires access to a particular database, the program
must first establish or open a connection to the database server.
2. Once the connection is established, the program can interact with the database by
submitting queries, updates, and other database commands.
3. When the program no longer needs access to a particular database, it should
terminate or close the connection to the database.
Typical Sequence of Interaction in Database Programming

Embedded SQL, Dynamic SQL
• Most SQL statements can be embedded in a general-purpose host programming
language such as COBOL, C, Java.
Retrieving Single Tuples with Embedded SQL:
• An embedded SQL statement is distinguished from the host language
statements by EXEC SQL and a matching END-EXEC (or semicolon).
– shared variables (used in both languages) usually prefixed with a colon (:) in
SQL
• Suppose that we want to write C programs to process the COMPANY database.
• We need to declare program variables to match the types of the database
attributes that the program will process.

Embedded SQL, Dynamic SQL (Cont.,)
• Variables inside DECLARE are shared and can appear (while prefixed by a colon)
in SQL statements.
• SQLCODE is used to communicate errors/exceptions between the database and
the program
C program variables used in the
embedded SQL examples E1 and E2.
The C program variables
declared in lines 2
through 5 correspond
to the attributes of the
EMPLOYEE and
DEPARTMENT tables
from the
COMPANY database
The variables declared SQLCODE and
SQLSTATE—are used to communicate
errors and exception conditions between the
database system and the executing
program.

Connecting to the Database:
• The SQL command for establishing a connection to a database has the following
form:
CONNECT TO <server name>AS <connection name>
AUTHORIZATION <user account name and password> ;
• To change from the currently active connection to a different one by using the
following command:
SET CONNECTION <connection name> ;
• Once a connection is no longer needed, it can be terminated by the following
command:
DISCONNECT <connection name> ;
1
2
3

Example of Embedded SQL Programming:
• To illustrate embedded SQL programming is a repeating program segment
(loop) that takes as input a Social Security number of an employee and prints
some information from the corresponding EMPLOYEE record in the
database.

Example explanation –
• The program reads (inputs) an Ssn value and then retrieves the EMPLOYEE
tuple with that Ssn from the database via the embedded SQL command.
• The INTO clause (line 5) specifies the program variables into which attribute
values from the database record are retrieved.

Retrieving Multiple Tuples with Embedded SQL Using Cursors:
• We can think of a cursor as a pointer that points to a single tuple (row) from the
result of a query that retrieves multiple tuples.
• An OPEN CURSOR command fetches the query result from the database and
sets the cursor to a position before the first row in the result of the query.
• FETCH commands move the cursor to the next tuple.
• CLOSE CURSOR indicates that the processing of query results has been
completed

Specifying Queries at Runtime Using Dynamic SQL:
• Objective: executing new (not previously compiled) SQL statements at run-time
– a program accepts SQL statements from the keyboard at run-time
– a point-and-click operation translates to certain SQL query
• Dynamic update is relatively simple; dynamic query can be complex
– because the type and number of retrieved attributes are unknown at compile
time

Database Stored Procedures and SQL/PSM
Database Stored Procedures and Functions:
• Procedures / functions (modules) are stored locally and executed by the database
server (as opposed to execution by clients)
• Advantages:
– If the procedure is needed by many applications, it can be invoked by any of
them (thus reduce duplications)
– Execution by the server reduces communication costs
– Enhance the modeling power of views

Database Stored Procedures and SQL/PSM (Cont.,)
• Many commercial DBMS’s allow stored procedures and functions to be written in a
general-purpose programming language.
• Alternatively, a stored procedure can be made of simple SQL commands.
The general form of declaring stored procedures is as follows:
• A stored procedure:
CREATE PROCEDURE procedure-name (params)
local-declarations
procedure-body;
• A stored function:
CREATE FUNCTION fun-name (params) RETURNS return-type
local-declarations
function-body;
• Calling a procedure or function:
CALL procedure-name/fun-name (arguments);

Company Database
Q1
Q2

Dbms ii mca-ch7-sql-2013

Recommended

More Related Content

What's hot (20)

Viewers also liked (18)

Similar to Dbms ii mca-ch7-sql-2013 (20)

Recently uploaded (20)

Dbms ii mca-ch7-sql-2013

Editor's Notes