Download DataBase Development and Implementation Lec08 - Relational DB Languages SQL and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
DBDI/ Lecture 8
Relational DB Languages
SQL
Dr. Ala Al-Zobaidie
The slides are based on the textbook Database Systems by Connolly & Begg 30/05/2007 DBDI / SQL 2
Lecture’s Objectives /1 - SQL
• Purpose and importance of SQL.
• How to retrieve data from database using
SELECT and:
- Use compound WHERE conditions.
- Sort query results using ORDER BY.
- Join tables together.
- Use of Aliases
30/05/2007 DBDI / SQL 3
Purpose and importance /1 - SQL
• DB Language:
- Create the DB & relation structures;
- Perform various operations
- Handle simple & complicated queries
• Perform these tasks with:
- minimal user effort &
- command structure/syntax must be easy to learn.
• Must be portable.
30/05/2007 DBDI / SQL 4
Purpose and importance /2 - SQL
• SQL is a transform-oriented language with
2 major components:
– DDL
– DML
• SQL did not contain flow of control
commands.
- Programming,
- job-control language,
- interactively by the decisions of user.
Objectives of SQL /
• SQL is relatively easy to learn:
- non-procedural - you specify what information you require, rather than how to get it;
- it is essentially free-format.
Objectives of SQL /
Consists of standard English words:
CREATE TABLE Staff(staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2));
INSERT INTO Staff VALUES ('SG16', 'Brown', 8300);
SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000;
30/05/2007 DBDI / SQL 7
Objectives of SQL /
- Can be used by range of users including DBAs, management, application developers, and other types of end users.
- An ISO standard now exists for SQL, making it both the formal and de facto standard language for relational databases.
30/05/2007 DBDI / SQL 8
History of SQL
- In 1974Æ 'Structured English Query Language' (SEQUEL).
- 1976 Æ SEQUEL/
- IBM produced a prototype DBMS called System R , based on SEQUEL/2.
- Roots of SQL predates System R project.
- In late 70s Æ ORACLE.
- In 1987 Æ ANSI & ISO
- In 1989, ISO Æ 'Integrity Enhancement Feature'.
- In 1992 Æ SQL2 or SQL/92.
- In 1999 Æ SQL
30/05/2007 DBDI / SQL 9
Importance of SQL
• Part of application architectures
• It is strategic choice of many large and
organizations
• SQL is Federal Information Processing
Standard (FIPS)
• SQL is used in other standards (e.g. IRDS
& RDA Standards).
30/05/2007 DBDI / SQL 10
Writing SQL Commands /
SQL statement consists of reserved words
and user-defined words.
– Reserved words
- fixed part of SQL & must be spelt exactly as required & cannot be split across lines.
– User-defined words
- made up by user & represent names of various database objects such as relations, columns, views.
Writing SQL Commands /
- case insensitive except literal
- indentation & lineation
- extended BNF notation
- Upper-case letters Æ Reserved Words.
- Lower-case letters Æ user-defined words.
- | Æ a choice
- { } Æ a required element
- [ ] Æ a optional element
- … Æ repetition
- Literals are constants used in SQL statements.
- non-numeric literals Æ enclosed in single quotes (e.g. ‘London’).
- numeric literals Æ not be enclosed in quotes (e.g. 650.00).
SELECT Statement
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] } FROM TableName [alias] [, ...] [WHERE condition] [GROUP BY columnList] [HAVING condition] [ORDER BY columnList]
30/05/2007 DBDI / SQL 19
Example 5.4 Calculated Fields
Produce a list of monthly salaries for all staff, showing staff number, first and last names, and salary details. SELECT staffNo, fName, lName, salary/ FROM Staff;
30/05/2007 DBDI / SQL 20
Example 5.4 Calculated Fields
• To name column, use AS clause:
SELECT staffNo, fName, lName,
salary/12 AS monthlySalary
FROM Staff;
30/05/2007 DBDI / SQL 21
Example 5.5 Comparison Search
Condition
List all staff with a salary greater than 10,000. SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary > 10000;
30/05/2007 DBDI / SQL 22
Example 5.6 Compound Comparison
Search Condition
List addresses of all branch offices in
London or Glasgow.
SELECT *
FROM Branch WHERE city = 'London' OR city = 'Glasgow';
Example 5.7 Range Search Condition
List all staff with a salary between 20,000 and 30,000.
SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary BETWEEN 20000 AND 30000;
- BETWEEN test includes the endpoints of range.
Example 5.7 Range Search Condition
30/05/2007 DBDI / SQL 25
Example 5.7 Range Search Condition
- Also a negated version NOT BETWEEN.
- BETWEEN does not add much to SQL's expressive power Could also write:
SELECT staffNo, fName, lName, position, salary FROM Staff WHERE salary>=20000 AND salary <= 30000;
- Useful, though, for a range of values.
30/05/2007 DBDI / SQL 26
Example 5.8 Set Membership
List all managers and supervisors.
SELECT staffNo, fName, lName, position FROM Staff WHERE position IN ('Manager', ‘Supervisor');
30/05/2007 DBDI / SQL 27
Example 5.8 Set Membership
- There is a negated version (NOT IN).
- IN does not add much to SQL's expressive power.
- Could have expressed this as:
SELECT staffNo, fName, lName, position FROM Staff WHERE position='Manager' OR position=‘Supervisor';
- IN is more efficient when set contains many values.
30/05/2007 DBDI / SQL 28
Example 5.9 Pattern Matching
Find all owners with the string 'Glasgow' in their address. SELECT clientNo, fName, lName, address, telNo FROM PrivateOwner WHERE address LIKE '%Glasgow%';
Example 5.9 Pattern Matching
- SQL has two special pattern matching
symbols:
- %: sequence of zero or more characters;
- _ (underscore): any single character.
- LIKE '%Glasgow%' means a sequence of
characters of any length containing
' Glasgow '.
Example 5.10 NULL Search Condition
List details of all viewings on property PG4 where a comment has not been supplied.
- There are 2 viewings for property PG4, one with and one without a comment.
- Have to test for null explicitly using special keyword IS NULL: SELECT clientNo, viewDate FROM Viewing WHERE propertyNo = 'PG4' AND comment IS NULL;
30/05/2007 DBDI / SQL 37
Example 5.12 Multiple Column Ordering
30/05/2007 DBDI / SQL 38
Using Aliases in SQL
List the name of the manager of the employee ALLEN.(Ref Q4 of tutorial 7)
Emp (EmpNo, Ename, Job, Mgr, HireDate, Sal, Comm, DeptNo); Select ename from emp where empno = ( Select mgr from emp where ename =‘ALLEN’); (return ‘BLAKE’) Select m .ename from emp e , emp m where m .empno = e .mgr and (return ‘BLAKE’) e .ename= ‘ALLEN’;
30/05/2007 DBDI / SQL 39
Summary
• Purpose of SQL
• History and Standards
• Basic Syntax of a Select Statement
• The basic 5 Search Conditions
• Examples from DreamHome
• Aliases
• Nested Query
