Transcript Systems Analysis and design with Databases

Information Retrieval and Use
(IRU) CE00873-2
An Introduction To SQL
Part 1
1
Outline of Lecture/Tutorial approach






2
The first two weeks will concentrate on the
following:
Select Statements
The Where Clause
Manipulating data using the emp and dept tables
using Oracle SQL+ so that you get a feel for the
SQL language.
Creating tables, populating them with data and
manipulating the data.
The tutorials will allow you to work through a
series of questions to reinforce the lecture
material. You will find it useful to keep your
lecture material with you during the tutorials.
The Basics: SQL Command Set

Data Manipulation Language (DML)





Insert allows you to enter new rows
Update allows you to change existing rows
Delete remove unwanted rows
Select is used to retrieve data from the database.
Data Description Language (DDL)
Create create objects
 Alter
structurally change objects
 Drop
drop objects within the database
(Objects are tables, views, indexes etc.)

3
The SQL Command Set continued

Data Control Language (DCL)


Grant
specify access rights on objects
Revoke remove access rights
These statements are NOT case-sensitive
Each of the statements should be terminated
with a semicolon ‘;’ at the end of the last line,
OR a slash can be used on the following line.
4
The Select Statement
This is the most commonly used
statement, to query database
information.
 The informal syntax is a follows:

SELECT column_name(s)
FROM table_name(s)
WHERE constraint(s);
‘*’ asks for all the columns to be returned.
5
The Select Statement: data retrieval.
6

The top line ‘SELECT’ describes the column
name(s) from which you are attempting to build
the query.

The second line ‘FROM’ lists the tables or
relations that contain the data necessary to
service the query

The third line, the ‘WHERE’ constraint is optional.
The WHERE clause allows you to specify the
tuples (rows) to be returned.
The Select Statement: Examples


7
The basic statement:
If we wish to display all of the tuples (rows) of a given
relation (i.e. table):
SELECT *
FROM EMP; (; end of statement to be executed)

We can constrain this by requesting only those
employee’s with the name ‘Allen’
SELECT *
FROM EMP
WHERE ENAME = ‘ALLEN’; (this is the constraint)

‘Allen’ is enclosed in quotation marks as we are
searching on a column data type which is a string
Select Statement Examples

If we want to search on numeric columns:
– SELECT *
– FROM DEPT
– WHERE DEPTNO = 10;
– SELECT *
– FROM EMP
– WHERE EMPNO = 7654;
8
The ‘Where’ Clause




This clause is used to restrict the output to show
a subset of the rows from a table.
It must be placed after the FROM clause
The WHERE clause has the following format:
WHERE Operators may be:



Comparative Operators
= != < > <= >= <> and
Logical Operators
AND, OR, NOT
SELECT *
FROM EMP
WHERE EMPNO = 7521 AND ENAME = ‘WARD’;
9
Where Clause Example

If we said:
SELECT *
FROM EMP
WHERE EMPNO = 7369 AND ENAME = ‘WARD’;
We would return an empty table as there are no tuples
(rows) that satisfy both of these constraints.
However if we said:
SELECT *
FROM EMP
WHERE EMPNO = 7876 OR ENAME = ‘KING’
Two tuples would be returned as these would satisfy
one or other of the constraints.
10
Select statement: retrieving attributes

You can retrieve attribute values by specifying
columns: The command is
– SELECT COLUMN_NAME (S)
– FROM TABLE_NAME (S);
– SELECT ENAME
– FROM EMP;
– Will return one column, and the values from the ename
column (14 rows).
– SELECT EMPNO,ENAME,JOB
– FROM EMP;
– Will return three columns, in that order (14 rows)
11
Where clause: more constraints

Row and Columns constraints can be set as
follows:
SELECT EMPNO, ENAME
FROM EMP
WHERE SAL > 2000;
Returns only those columns empno and ename from the emp
table and only those rows with a balance greater than
2000.
12
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL BETWEEN 1500 AND 3000;
Displays all those employees who earn between 1500 and
3000 (four columns and eight rows in this case)
Where clause: The ‘Like’ operator

If we want to search for employees whose name
begins with the letter ‘A’
We can say:
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE ENAME LIKE ‘A%’;
This will display two tuples (rows) i.e. all those employees
whose names begin with A and three columns.
The ‘%’ is a wildcard operator.
13
Where clause: ‘Not In’ operator

You can use the ‘NOT IN’ operator as
follows:
SELECT ENAME, JOB, SAL
FROM EMP
WHERE JOB NOT IN (‘SALESMAN’, ‘MANAGER’);
This will return those employees who are not
salesmen or managers.
You can also use the operator !=
14
Where clause: multiple conditions

If you want to find all the clerks in
department 30 who earn less than $1000
you can say:
SELECT ENAME,JOB, SAL
FROM EMP
WHERE DEPTNO = 30
AND JOB = ‘CLERK’
AND SAL < 1000;
This will return 1 row for James, a clerk who earns
$950.
15
Joins



Oracle provides you with the capability to gather
and manipulate data over several tables using the
JOIN feature.
A join is one of the fundamental operations in
relational algebra.
The basic Structure is:



16
SELECT table1.column1, table1.column2,
table2.column1
FROM table1, table2, tablen
WHERE join criteria;
Joining Tables: Example

We want to know where ALLEN works. The EMP table
does not hold the location details but the DEPT table
does.
SELECT ename, loc
FROM emp,dept
WHERE emp.deptno = dept.deptno;
AND ename = ‘ALLEN’
We need to distinguish between the deptno in the two
tables therefore they are prefixed with the table name.
Here we have an equi-join in the WHERE clause.
17
Joining Tables:Use of
Abbreviations/aliases
SELECT d.*, ename, job
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.deptno = (30,40)
ORDER BY e.deptno;
The alias d.* will retrieve all the column names in the DEPT
Table.
We use aliases to identify which column name belong to
which
table as some column names will exist in more than one
table
for example, deptno.
18
Joining Tables: Outer Join

19
Normally when tables are joined we use primary/foreign
keys which are generally defined as NOT NULL, however to
join non key elements we may use an OUTER JOIN.
 i.e Department 40 in table DEPT has no matching
employees in table EMP so normal join queries would
not retrieve department 40.
 The symbol for outer joins is an (+)
– SELECT DEPT.DEPTNO, DNAME, JOB, ENAME
– FROM DEPT, EMP
– WHERE DEPT.DEPTNO = EMP.DEPTNO(+)
– AND DEPT.DEPTNO = (30,40)
– ORDER BY DEPT.DEPTNO;
Views





20
A view is a type of window that allows you to view selected
portions of data.
Advantages:
Security: a secure table may contain sensitive or
confidential information that prevents it from general
circulation. You can deny access to the emp table itself but
instead provide access to a VIEW without the salary or
commission details.
A view can be constructed on a need to know basis.
Convenience: instead of using complex queries to get a
perspective on your data, you can create a view which
allows you to obtain the same information with a simple
query.
Views

No additional memory is required for a view as the view is
created from the base tables every time the view is called.
 CREATE VIEW view_name AS
 (SELECT columns FROM table_name WHERE
row_selector);
CREATE VIEW EMP10 AS
SELECT EMPNO, ENAME. JOB
FROM EMP
WHERE DEPTNO = 10;
A message will be displayed ‘view created’ you can
check your view by: SELECT * FROM EMP10;
21
Views

You can add, delete or update data in a view.
UPDATE emp10
SET job = ‘CLERK’
Where ename = ‘MILLER’;

Delete, rename or drop a view.
DELETE FROM emp10
WHERE ename = ‘Wilson’;
RENAME emp10 to emp11;
DROP VIEW emp11;
22
Queries: Nested Sub Queries

23
When one of the conditions in a WHERE clause is a query
itself it is known as a nested sub-query. It is used for stepwise processing.
SELECT ename, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE
ename = ‘JONES’)
– The sub query is enclosed in brackets.
– The values compared across the outer query and the
subquery must be of the same datatype.
– Nested query is performed in step 1 and its result is
used in step 2 with the outer query.
Nested Sub Queries

Example: Find the person who earns the highest
salary:





24
Step 1: find the maximum salary
Step 2: Find the person whose salary is equal to the
maximum salary
SELECT ename, sal
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);
Next week

More DML
– additional SELECT clauses
 Insert, Alter and Delete


25
DDL – CREATing, ALTERing and
DROPping objects
Useful Texts




26
Rolland, F.D (1997), Essence of Databases
(Essence of Computing), Prentice-Hall. ISBN
0137278276
Date C.J. (2000); An Introduction to Database
Systems, Addison Wesley, (or any earlier
editions)
Patrick J.P. (2002), SQL Fundamentals (2nd
edition), Prentice Hall, ISBN 0130669474
SQL at w3schools (click to follow the link)