Transcript Systems Analysis and design with Databases

An Introduction To SQL - Part 1
(Special thanks to Geoff Leese)






Select Statements
The Where Clause
Manipulating data using the EMP and DEPT tables
using Oracle/MySQL 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 may find it useful to keep your lecture material
with you during the tutorials.

Data Manipulation Language (DML)
◦ Insert
◦ Update
◦ Delete


allows you to enter new rows
allows you to change existing rows
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.)

Data Control Language (DCL)
◦ Grant
◦ Revoke
specify access rights on objects
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.
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7839
KING
PRESIDENT
-
11/17/1981
5000
-
10
7698
BLAKE
MANAGER
7839
05/01/1981
2850
-
30
7782
CLARK
MANAGER
7839
06/09/1981
2450
-
10
7566
JONES
MANAGER
7839
04/02/1981
2975
-
20
7788
SCOTT
ANALYST
7566
12/09/1982
3000
-
20
7902
FORD
ANALYST
7566
12/03/1981
3000
-
20
7369
SMITH
CLERK
7902
12/17/1980
800
-
20
7499
ALLEN
SALESMAN
7698
02/20/1981
1600
300
30
7521
WARD
SALESMAN
7698
02/22/1981
1250
500
30
7654
MARTIN
SALESMAN
7698
09/28/1981
1250
1400
30
7844
TURNER
SALESMAN
7698
09/08/1981
1500
0
30
7876
ADAMS
CLERK
7788
01/12/1983
1100
-
20
7900
JAMES
CLERK
7698
12/03/1981
950
-
30
7934
MILLER
MANAGER
7782
01/23/1982
1300
-
10
DEPTNO
DNAME
LOC
10
20
30
40
50
ACCOUNTING
RESEARCH
SALES
OPERATIONS
HR
NEW YORK
DALLAS
CHICAGO
BOSTON
LONDON



This is the most commonly used statement
Used 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.

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 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 employees 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

If we want to search on numeric columns:
SELECT *
FROM DEPT
WHERE DEPTNO = 10;
SELECT *
FROM EMP
WHERE EMPNO = 7654;




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';

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.
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.

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)

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.
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)

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.

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 !=

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.



MySQL 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:
SELECT table1.column1, table1.column2,
table2.column1
FROM table1, table2, tablen
WHERE join criteria;
◦ 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.
SELECT d.*, ename, job from EMP e, DEPT d
WHERE e.deptno = d.deptno AND (e.deptno = 40 or
e.deptno=30) 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 belongs to
which table as some column names will exist in more than
one table for example, deptno.

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 LEFT JOIN
◦ i.e Department 40 in table DEPT has no matching employees
in table EMP so normal join queries would not retrieve
department 40
SELECT dept.deptno, dname, job, ename
FROM dept LEFT JOIN emp
on dept.deptno = emp.deptno
WHERE dept.deptno IN (30, 40)
ORDER BY dept.deptno;





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.

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;

You can add, delete or update data in a view.
UPDATE EMP10
SET job = 'MANAGER'
Where ename = 'MILLER';

Delete, rename or drop a view.
DELETE FROM EMP10
WHERE ename = 'WILSON';
RENAME EMP10 to EMP11;
DROP VIEW EMP11;

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 step-wise
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.

Example: Find the person who earns the highest salary:
◦ 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);

More DML
◦ – additional SELECT clauses
◦ Insert, Alter and Delete

DDL – CREATing, ALTERing and DROPping
objects




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)