DIT 216

Database Concepts & Technologies II

Lecture 2

K. O. Peasah
[email protected] // 024-426-3434
Jan 2014
2
3
4
5
6
 QUERY 1: Retrieve the birth date and address of the employee(s) whose
name is ‘John B. Smith’.

USE DATABASE Company

SELECT Bdate, Address
FROM EMPLOYEE
WHEREFname = ‘John’ AND Minit = ‘B’ AND Lname = ‘Smith’;
QUERY 2: Retrieve the name and address of all employees who work for the 'Research' department.

• SELECT FNAME, MINIT, LNAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT
WHEREDNAME='Research' AND DNUMBER=DNO
 • 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 birthdate.

9
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 birthdate.

SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS

FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND
PLOCATION='Stafford'
QUERY 4: The following statement retrieves the CustomerID, CompanyName, and ContactName data for companies who have a CustomerID value equal to alfki or anatr. The
result is ordered according to the ContactName value:

USE Northwind
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE (CustomerID = ‘alfki’ OR CustomerID
=‘anatr’)
ORDER BY ContactName
 Ambiguous Attribute Names, Aliasing, and Tuple
Variables
• In SQL the same name can be used for two (or more) attributes as long as
the attributes are in different relations.
• If this is the case, and a query refers to two or more attributes with the same
name, we must qualify the attribute name with the relation name to prevent
ambiguity.
• This is done by prefixing the relation name to the attribute name and
separating the two by a period.
Example: Ambiguous Attribute Names
SELECT Fname, EMPLOYEE.Name, Address
FROM EMPLOYEE, DEPARTMENT
WHEREDEPARTMENT.NAME='Research' AND
DEPARTMENT.Dnumber= EMPLOYEE. .Dnumber ;
 Ambiguous Attribute Names, Aliasing, and Tuple Variables
• Ambiguity also arises in the case of queries that refer to the same
relation twice, as in the following example.

• QUERY 5: For each employee, retrieve the employee's name, and

the name of his or her immediate supervisor.

Q5:SELECT E.FNAME, E.LNAME, S.FNAME,

S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN
Ambiguous Attribute Names, Aliasing, and Tuple Variables

• In this case, we are allowed to declare alternative

relation names E and S, called aliases or tuple
variables, for the EMPLOYEE relation. An alias can
follow the keyword AS, as shown in Q5,
 Unspecified WHERE Clause and Use of the Asterisk

• A missing WHERE clause indicates no condition on tuple selection;

hence all tuples of the relation specified in the FROM clause qualify
and are selected for the query result.

• If more than one relation is specified in the FROM clause and there is
no WHERE clause, then CROSS PRODUCT – all possible tuple
combinations – of these relations is selected.
• For example, Query 6 selects all EMPLOYEE Ssns and Query 7
selects all combinations of an EMPLOYEE Ssn and a
DEPARTMENT Dname.

• Q6: SELECT Ssn

FROM EMPLOYEE

• Q7: SELECT Ssn, Dname

FROM EMPLOYEE, DEPARTMENT
 Use of Asterisk
• 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
WHEREDn = 5
Use of Asterisk

SELECT *
FROM EMPLOYEE, DEPARTMENT
WHEREDname = ‘Research’ AND Dn = Dnumber

SELECT *
FROM EMPLOYEE, DEPARTMENT
 Aggregate Functions in SQL
• Grouping and aggregation are required in many database
applications, SQL has features that incorporate these concepts.

• A number of built-in functions exist: COUNT, SUM, MAX, MIN,

and AVG.

• The COUNT function returns the number of tuples or values as

specified in a query.
 Aggregate Functions in SQL

• The functions SUM, MAX, MIN, and AVG are applied to a set of
multiset of numeric values and return, respectively, the sum,
maximum value, minimum value, and average (mean) of those
values.

• These functions can be used in the SELECT clause or in a

HAVING clause (which we introduce later).
Aggregate Functions in SQL
• The functions MAX and MIN can also be used with attributes that
have nonnumeric domains if the domain values have a total ordering
among one another.

• We illustrate the use of these functions with example queries.

Find the sum of the salaries of all employees, the
maximum salary, the minimum salary, and the
average salary.

• SELECT SUM(Salary), MAX(Salary),

MIN(Salary),AVG(Salary)
FROM EMPLOYEE
Query 9: Find the sum of the salaries of all employees of the ‘Research’ department, as well
as the maximum salary, the minimum salary, and the average salary in this department.

• Q9: SELECT SUM(Salary), MAX(Salary),

MIN(Salary),AVG(Salary)
FROM (EMPLOYEE JOIN
DEPARTMENT ON Dno=Dnumber)
WHERE Dno=Dnumber AND
Dname=‘Research’
Queries 10 and 11. Retrieve the total number of employees in the
company (Q10) and the number of employees in the ‘Research’
department (Q11).

• Q10: SELECT COUNT(*)

FROM EMPLOYEE

• Q11: SELECT COUNT(*)

FROM EMPLOYEE, DEPARTMENT
WHEREDno=Dnumber AND
Dname=‘Research’
 Grouping: The GROUP BY and HAVING Clauses

• In many cases we want to apply the aggregate functions to

subgroups of tuples in a relation, where the subgroups are
based on some attribute values.

• For example, we may want to find the average salary of

employees in each department or the number of employees
who work on each project.
 Grouping: The GROUP BY and HAVING Clauses

▪ In these cases we need to partition the relation

into nonoverlapping subsets (or groups) of tuples.

▪ Each group (partition) will consist of the tuples

that have the same value of some attribute(s),
called the grouping attribute(s).
 Grouping: The GROUP BY and HAVING Clauses
• We can then apply the function to each such group independently. SQL
has a GROUP BY clause for this purpose.

• The GROUP BY clause specifies the grouping attributes, which should

also appear in the SELECT clause, so that the value resulting from
applying each aggregate function to a group of tuples appears along with
the value of the grouping attribute(s).
For each department, retrieve the department number, the number of
employees in the department, and their average salary.

SELECTDno,COUNT(*), AVG(Salary)
FROM EMPLOYEE
GROUP BY Dno
GROUP BY EXAMPLE
SELECT productid, count(*), sum(quantity)
FROM [order details]
GROUP BY productid

The above will group by productid, count the orders per

product and return the total quantity of each product that has
been ordered from the Order Details table
 GROUP BY EXAMPLE
For each project, retrieve the project number, the 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 Clauses
• When we want to retrieve the values of functions for only groups that
satisfy certain conditions, then we use the HAVING Clause.

• For example, suppose that we want to modify previous so that only

projects with more than two employees appear in the result.

• SQL provides a HAVING clause, which can appear in conjunction with

a GROUP BY clause, for this purpose.
HAVING Clause
• HAVING provides a condition on the group of tuples associated with
each value of the grouping attributes. Only that satisfy the condition
are retrieved in the result of the query.
 HAVING Clause Example
Query: For each project on which more than two employees work,
retrieve the project number, the project name, and the number of
employees who work on the project.

SELECT Pnumber, Pname, COUNT(*)

FROM PROJECT, WORKS_ON
WHEREPnumber =Pno
GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2
The ORDER BY Clause

• The ORDER BY clause sorts a query result by one or more columns

(up to 8060 bytes).

• A sort can be ascending (ASC) or descending (DESC). If neither is

specified, ASC is assumed. If more than one column is named in the
ORDER BY clause, sorts are nested.
The ORDER BY Clause - Example
USE Pubs
SELECT Pub_id, Type, Title_id, Price
FROM Titles
ORDER BY Pub_id DESC, Type, Price
INSERT, DELETE and UPDATE Statements in SQL
• In its simplest form, INSERT is used to add a single
tuple to a relation.

• We must specify the relation name and a list of values

for the tuple.

• The values should be listed in the same order in which

the corresponding attributes were specified in the
CREATE TABLE command.
INSERT -Example
• For example, to add a new tuple to the EMPLOYEE
relation ; we can use

INSERT INTO EMPLOYEE

VALUES ('Richard','K','Marini', '653298653', '30-
DEC-52','98 Oak Forest,Katy,TX', 'M',
37000,'987654321', 4 )
INSERT – Example 2

USE Northwind
INSERT INTO Territories
VALUES (98101, ‘Seatle’, 2)
 INSERT –Type 2

• A second form of INSERT statement allows the user

to specify explicit attribute names that correspond to
the values provided in the INSERT command.
• This is useful if a relation has many attributes but
only a few of those attributes are assigned values in
the new tuple.
INSERT
• The values must include all attributes with
NOT NULL specification and no default
value.

• Attributes with NULL allowed or DEFAULT

values are the ones that can be left out.
INSERT –Type 2 Example
• For example, to enter a tuple for a new EMPLOYEE for whom we
know only the Fname, Lname, Dno, and Ssn attributes, we can use the
statement below:

INSERT INTO EMPLOYEE (FNAME, LNAME, Dno, SSN)

VALUES ('Richard', 'Marini',4, '653298653')
 UPDATE
• The UPDATE command is used to modify attribute
values of one or more selected tuples.

• As in the DELETE command, a WHERE clause in the

UPDATE command selects the tuples to be modified
from a single relation.
UPDATE
• However, updating a primary key value may propagate to the foreign
key values of tuples in other relations if such a referential triggered
action is specified in the referential integrity constraints of the DDL.

• An additional SET clause in the UPDATE command specifies the

attributes to be modified and their new values.
UPDATE - Example
• For example, to change the location and controlling department
number of project number 10 to ‘Bellaire’
and 5 respectively, we use the statement below:

UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5
WHERE PNUMBER=10
DELETE STATEMENT
The DELETE statement removes rows from a table

DELETE
FROM [table]
WHERE {condition}
DELETE STATEMENT - Example
This example deletes all rows from the authors table.

USE pubs
DELETE authors
DELETE STATEMENT - Example
• This example deletes all rows in which au_lname is McBadden.

USE pubs
DELETE FROM authors
WHERE au_lname = 'McBadden'
 End

49
 Thank You

For any concerns, please contact

[email protected]
[email protected]
0322 191132
Jan 2014
VIEWS
• A view in SQL/MySQL terminology is a single
table that is derived from other tables.
• These other tables can be base tables or
previously defined views.
• A view does not necessarily exist in physical
form; it is considered a virtual table, in
contrast to base tables, whose tuples are
actually stored in the database.
 VIEWS
• In SQL, a view is a virtual table based on the result-set of
an SQL statement.
• A view contains rows and columns, just like a real table.
• The fields in a view are fields from one or more real tables
in the database.
• View doesn't actually store information itself, but just
pulls it out of one or more existing tables.
• Although impermanent, a view may be accessed
repeatedly by storing its criteria in a query.
VIEWS
• We may frequently issue queries that
retrieve the employee name and the
project names that the employee works
on.

• Rather than having to specify the join of

the EMPLOYEE, WORKS_ON, and
PROJECT tables every time we issue that
query, we can define a view that is a result
of these joins.
VIEWS
• Then we can issue queries on the view, which are
specified as single table retrievals rather than as
retrievals involving two joins on three tables.

• We call the EMPLOYEE, WORKS_ON, and

PROJECT tables the defining tables of the view.
 SYNTAX
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

• If the view does not exist, CREATE OR REPLACE VIEW is the same as

CREATE VIEW
•  If the view does exist, CREATE OR REPLACE VIEW is the same as 
ALTER VIEW.
• The view definition is “frozen” at creation time, so changes to the
underlying tables afterward do not affect the view definition.
• For example,
if a view is defined as SELECT * on a table, new columns added to
the table later do not become part of the view
VIEW – Example
CREATE VIEW WORKS_ON1
AS SELECTFname, Lname, Pname, Hours
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno =Pnumber

CREATE VIEW DEPT_INFO(Dept_name, No_of_emps, Total_sal)

AS SELECT Dname, COUNT (*), SUM(Salary)
FROM DEPARTMENT,EMPLOYEE
WHERE Dnumber=Dno
GROUP BY Dnumber
VIEW – Example
Querying A View
• we can utilise the WORKS_ON1 view and specify the query as below:

SELECT Fname, Lname

FROM WORKS_ON1
WHEREPname = ‘ProjectX’
 Using Advanced Query Techniques To Access
Data
• One of these techniques is to combine the contents of
two or more tables to produce a result set that
incorporates rows and columns from each table.

• Another technique is to use subqueries, which are

SELECT statements nested inside other SELECT,
INSERT, UPDATE, or DELETE statements.

• Subqueries can also be nested in other subqueries.

 Using Joins To Retrieve Data

• By using joins, you can retrieve data from two or more tables
based on logical relationships between the tables. Joins can
be specified in either the FROM or WHERE clauses.

• The join conditions combine with the WHERE and HAVING

search conditions to control the rows that are selected from
the base tables referenced in the FROM clause.

• Specifying the join conditions in the FROM clause, however,

helps separate them from any other search conditions that
might be specified in a WHERE clause and is the
recommended method for specifying joins.
JOINS• When multiple tables are referenced in a
single query, all column references must
be unambiguous.

• The table name must be used to qualify

any column name that is duplicated in two
or more tables referenced in a single
query.
JOINS• The select list for a join can reference all
of the columns in the joined tables or any
subset of the columns.

• The select list is not required to contain

columns from every table in the join.
JOINS
• For example, in a three-table join, only one
table can be used as a bridge from one of the
other tables to the third table, and none of the
columns from the middle table have to be
referenced in the select list.
• Although join conditions usually use the equals
sign ( = ) comparison operator, other
comparison or relational operators can be
specified ( as can other predicates). Most joins
can be categorized as inner joins or other joins.
JOINS
• Inner joins return rows only when there is at
least one row from both tables that matches the
join condition, eliminating the rows that do not
match with a row from the other table.

• Outer joins, however, return all rows from at

least one of the tables or views mentioned in
the FROM clause as long as these rows meet
any WHERE or HAVING search conditions.
INNER JOIN
An inner join is a join in which the values in the
columns being joined are compared through the use of
a comparison operator.

• The following SELECT statement uses an inner join to retrieve

data from the Publishers table and the Titles table in the Pubs
database:

SELECT t.Title, p.Pub_name

FROM Publishers AS p INNER JOIN Titles AS t
ON p.Pub_id = t.Pub_id
ORDER BY Title ASC
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Outer Joins
• There are three types of outer joins: left, right,
and full.

• All rows retrieved from the left table are

referenced with a left outer join, and all rows
from the right table are referenced in a right
outer join.

• All rows from both tables are returned in a full

outer join.
Using Left Outer Joins

• A result set generated by a SELECT

statement that includes a left outer join
includes all rows from the table referenced
to the left of LEFT OUTER JOIN.

• The only rows that are retrieved from the

table to the right are those that meet join
condition.
Using Left Outer Joins

• In the following SELECT statement, a left

outer join is used to retrieve the authors’ first
names, and (when applicable) the names of any
publishers that are located in the same cities as
the authors:
 Using Left Outer Joins

USE Pubs
SELECT a.Au_fname, a.Au_lname, p.Pub_name
FROM Authors a LEFT OUTER JOINPublishers p
ON a.City = p.City
ORDER BY p.Pub_name ASC, a.Au_lname ASC,
a.Au_fname ASC
Using Left Outer Joins

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Using Right Outer Joins

• A result set generated by a SELECT statement that

includes a right outer join includes all rows from the
table referenced to the right of RIGHT OUTER JOIN.

• The only rows that are retrieved from the table to the
left are those that meet the join condition.

• In the following SELECT statement, a right outer join

is used to retrieve the list of publishers and authors’
first names, and last names, if those authors are
located in the same cities as the publishers:
Using Right Outer Joins

USE Pubs
SELECT a.Au_fname, a.Au_lname, p.Pub_name
FROM Authors a RIGHT OUTER JOIN Publishers
p
ON a.City = p.City
ORDER BY p.Pub_name ASC, a.Au_lname ASC,
a.Au_fname ASC
Using ▪Full Outer
A result Joins by a SELECT statement that
set generated
includes a full outer join includes all rows from both
tables, regardless of whether the tables have a
matching value (as defined in the join condition).

• In the following SELECT statement, a full outer join

is used to retrieve the list of publishers and authors’
first and last names:
 Using Full Outer Joins

USE Pubs
SELECT a.Au_fname, a.Au_lname, p.Pub_name
FROM Authors a FULL OUTER JOIN Publishers p
ON a.City = p.City
ORDER BY p.Pub_name ASC, a.Au_lname ASC,
a.Au_fname ASC
Using Full Outer Joins
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Defining Subqueries inside SELECT Statement
▪ A subquery is a SELECT statement that returns a
single value and is nested inside a SELECT, INSERT,
UPDATE or DELETE statement or inside another
subquery.

▪ A subquery can be used anywhere an expression is

allowed. A subquery is also called an inner query or
inner select, while the statement containing a
subquery is called an outer select.

▪ In the following example, a subquery is nested in the

WHERE clause of the outer SELECT statement:
Subqueries

• Subqueries are an alternate way of returning data from

multiple tables.
• Subqueries can be used with the following SQL
statements along with the comparision operators like
=, <, >, >=, <= etc.
Subquery - Example
USE Northwind
SELECT ProductName
FROM Products
WHERE UnitPrice =
{
SELECT UnitPrice
FROM Products
WHERE ProductName = ‘Sir Rodney’ ‘s
Scones’
}
 Subqueries
• If a table only appears in a subquery and not in the outer
query, then columns from that table cannot be included in
the output (the select list of the outer query).

Types of Subqueries
Subqueries can be specified in many places within a
SELECT statement. Statements that include a subquery
usually take one of the following formats:

WHERE <expression> [NOT] IN (<subquery>)

WHERE <expression> <comparison_operator> [ANY |
ALL] (<subquery>)
WHERE [NOT] EXISTS (<subquery>)
Subqueries that Are used with IN or NOT IN
• The result of a subquery introduced with IN (or with NOT
IN) is a list of zero or more values.
ie. Usually, a subquery should return only one record, but
sometimes it can also return multiple records when used
with operators LIKE, IN, NOT IN in the where clause. The
query syntax would be like,

• After the subquery returns the result, the outer query makes
use of it.

• In the following example, a subquery is nested inside the

WHERE clause, and the IN keyword is used:
SQL LIKE Operator

• The LIKE operator is used to list all rows in a table

whose column values match a specified pattern.
• It is useful when you want to search rows to match a
specific pattern, or when you do not know the entire
value.
• For this purpose we use a wildcard character '%'.
For example:

• To select all the students whose name begins with 'S‘

SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE 'S%';
The output would be similar to:
first_name last_name
------------- -------------
Stephen Fleming
Shekar Gowda
SQL LIKE Operator
• The above select statement searches for all the rows where the first
letter of the column first_name is 'S' and rest of the letters in the name
can be any character.

• There is another wildcard character you can use with LIKE operator. It
is the underscore character, ' _ ' . In a search string, the underscore
signifies a single character.
Example
• To display all the names with 'a' second character
SELECT first_name, last_name 
FROM student_details 
WHERE first_name LIKE '_a%'; 
 SQL BETWEEN ... AND Operator
• The operator BETWEEN and AND, are used to compare
data for a range of values.
• For Example: to find the names of the students between age
10 to 15 years, the query would be like,
SELECT first_name, last_name, age 
FROM student_details 
WHERE age BETWEEN 10 AND 15;
SQL IN Operator:
• The IN operator is used when you want to compare a
column with more than one value. It is similar to an OR
condition.
• For example: If you want to find the names of students
who are studying either Maths or Science, the query
would be like,

SELECT first_name, last_name, subject

FROM student_details
WHERE subject IN ('Maths', 'Science');
• You can include more subjects in the list like
('maths','science','history')
• NOTE:The data used to compare is case sensitive.
SQL IS NULL Operator
• A column value is NULL if it does not exist. The IS NULL
operator is used to display all the rows for columns that do
not have a value.
• For Example: If you want to find the names of students
who do not participate in any games, the query would be
as given below
SELECT first_name, last_name
FROM student_details
WHERE games IS NULL
Subquery - Example
USE Pubs
SELECT Pub_name
FROM Publishers
WHERE Pub_id IN
(
SELECT Pub_id
FROM Titles
WHERE Type = ‘business’
)
Subqueries that Are Used with Comparison
Operators
• Comparison operators that introduce a
subquery can be modified with the keyword
ALL or ANY.
• Subqueries introduced with a modified
comparison operator return a list of zero or
more values and can include a GROUP BY or
HAVING clause.
• These subqueries can be restated with EXISTS.
Subqueries that Are Used with Comparison
Operators
• The ALL and ANY keywords each compare a
scalar value with a single-column set of values.
• The ALL keyword applies to every value, and the
ANY keyword applies to at least one value.
• In the following example, the greater than (>)
comparison operator is used with the ANY
keyword:
Subquery - Example
USE Pubs
SELECT Title
FROM Titles
WHERE Advance > ANY
(
SELECT Advance
FROM Publishers INNER JOIN Titles
ON Titles.Pub_id = Publishers.Pub_id
AND Pub_name = ‘Algodata Infosystems’
)
Subqueries that Are Used with EXISTS and NOT EXISTS

• When a subquery is introduced with the keyword EXISTS,

it functions as an existence test.

• The WHERE clause of the outer query tests for the

existence of rows returned by the subquery.

• The subquery does not actually produce any data; instead,

it returns a value of TRUE or FALSE.

• In the following example, the WHERE clause in the outer

SELECT statement contains the subquery and uses the
EXISTS keyword.
Subquery - Example
USE Pubs
SELECT Pub_name
FROM Publishers
WHERE EXISTS
(
SELECT * FROM Titles
WHERE Titles.Pub_id = Publishers.Pub_id
AND Type = ‘Business’
)
End
 Thank You

For any concerns, please contact

[email protected]
[email protected]
0322 191132
Jan 2014