CS计算机代考程序代写 SQL database Database Fundamentals

Database Fundamentals

Database Fundamentals
SQL Introduction – Data Manipulation Language

1

SQL Commands Overview – Data Manipulation
Key Data Manipulation SQL commands:
INSERT INTO – Creates new tuple(s) in a table in the database

DELETE FROM – Permanently removes tuple(s)

UPDATE – Modifies existing tuple(s) in a table in the database

SELECT – Retrieves one or more tuples of data (a Query)

What’s this for?
So we have created a set of tables.
How do we actually put data in our database?
How do we modify data that is already in our database?
How do we answer questions using our database data?

Example Database Design
Students(StudentID, Name, Email)
PK(StudentID)
CK(Email)
Courses(CourseID, Name, StudentNo)
PK(CourseID)
CK(Name)
Enrolments(StudentID, CourseID, DateEnrolled, Mark)
PK(StudentID, CourseID, DateEnrolled)
FK(StudentID) -> Students(StudentID)
FK(CourseID) -> Courses(CourseID)

Database Implementation
Possible Conceptual Designs

Example Database Design
CREATE TABLE Students (
StudentID int,
Name varchar(100) NOT NULL,
Email varchar(100) NOT NULL,
CONSTRAINT StudentPK PRIMARY KEY(StudentID),
CONSTRAINT UniqueEmail UNIQUE(Email)
);
CREATE TABLE Courses (
CourseID int,
Name varchar(100) NOT NULL,
StudentNo int DEFAULT 50,
CONSTRAINT CoursePK PRIMARY KEY(CourseID),
CONSTRAINT UniqueName UNIQUE(Name),
CONSTRAINT maxStudents CHECK(StudentNo BETWEEN 5 AND 150)
);
Students(StudentID, Name, Email)
PK(StudentID)
CK(Email)
Courses(CourseID, Name, StudentNo)
PK(CourseID)
CK(Name)

Example Database Design
CREATE TABLE Enrolments (
CourseID int NOT NULL,
StudentID int NOT NULL,
DateEnrolled date DEFAULT getDate(),
Mark int CHECK(Mark BETWEEN 0 AND 100),
CONSTRAINT EnrolmentPK PRIMARY KEY(CourseID, StudentID, DateEnrolled),
CONSTRAINT theStudent FOREIGN KEY(StudentID) REFERENCES Students(StudentID),
CONSTRAINT theCourse FOREIGN KEY(CourseID) REFERENCES Courses(CourseID)
);
Enrolments(StudentID, CourseID, DateEnrolled, Mark)
PK(StudentID, CourseID, DateEnrolled)
FK(StudentID) -> Students(StudentID)
FK(CourseID) -> Courses(CourseID)

INSERT INTO

SQL Commands Overview – INSERT INTO
Used to populate with data – your newly created database!
String data (text) must be wrapped in single ‘quotations’
Same applies to date strings: ‘01/03/2014’

Syntax:
INSERT INTO VALUES (x,y,z)
Inserts new values in the default order of the columns in the database table

INSERT INTO (att3, att2, att1) VALUES (z, y, x)
Inserts the values in a different order to the default order of the columns in the database table

Examples:
INSERT INTO Student VALUES (‘50011’, ‘Barry’);
INSERT INTO Student (StudentName, StudentID) VALUES (‘Stacy’, ‘50022’);
Be careful using this method if the order of columns changes.

Note the different order of StudentName vs StudentID. If the order is not specified, you must insert the values in order of the table column names (Left  Right)

Order specified in this statement
The SAME order of attributes when the table was created!!

SQL – INSERT new records
Syntax
INSERT INTO TableName [(AttributeList)]

Using values
INSERT INTO Departments (DeptName, City)
VALUES (‘Development’,’San Jose’)

Using a Sub-Query
INSERT INTO Student
SELECT StudentID, Email, Name
FROM otherStudentTable
WHERE Name IS NOT NULL

INSERT INTO Student (StudentID, StudentName)
SELECT StudentID, StudentName
FROM otherTable
WHERE Name IS NOT NULL;
Best Approach

Attribute List
Attribute Values

SQL Commands Overview – INSERT INTO
The order of the attributes (if present) are important (first value for the first attribute, and so on)
If the AttributeList is omitted, all the values must be supplied in the same order used when the table structure was created
CREATE Table (Col1, Col2, Col3)
INSERT INTO Table VALUES (val1, val2, val3)
Where Val1 is for Col1, Val2 for Col2 …

If the AttributeList does not contain all the attributes, the remaining attributes are assigned their DEFAULT values (or NULL if no default has been defined)
INSERT INTO Table(col1, col2) VALUES (val1, val2)
The new row inserted will be (val1, val2, defaultVal/NULL)
Assuming the table has 3 columns 
This WILL throw an ERROR if the attribute is set as NOT NULL and no DEFAULT has been specified

SQL Commands Overview – INSERT INTO

CREATE TABLE Departments(
Dept char(4),
DeptName varchar(100) NOT NULL,
CONSTRAINT deptPk PRIMARY KEY(Dept),
CONSTRAINT uniqueName UNIQUE(DeptName)
);
CREATE TABLE Employees (
RegNo char(6),
FirstName char(20) NOT NULL,
Surname char(20) NOT NULL,
Dept char(4),
Salary decimal(9) DEFAULT 0,
Bdate date,
CONSTRAINT employeePk PRIMARY KEY (RegNo),
CONSTRAINT uniueName UNIQUE (Surname, FirstName),
CONSTRAINT departmentsFk FOREIGN KEY (Dept) REFERENCES Departments(Dept)
);

Does the order matter??
Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

SQL Commands Overview – INSERT INTO

SQL> SELECT * FROM employees;
REGNO FIRSTNAME SURNAME DEPT SALARY BDATE
—— ———— ———- —— ———- ———–
E001 John Smiths CIS 40000 23-JAN-1980
E002 Peter Smiths CIS 0 NULL
INSERT INTO Departments VALUES (‘CIS’, ‘Computer and Information Science’);
INSERT INTO Employees VALUES (‘E001’, ‘John’, ‘Smiths’, ‘CIS’, 40000, ’23-JAN-1980′);
/* The value order MUST match the attribute order specified when the table was created (previous slide!) */
/* Dates with the month in text must be encased in single quotes if the Month contains text */
INSERT INTO Employees (Dept, Regno, Firstname, Surname)
VALUES (‘CIS’,’E002′,’Peter’,’Smiths’);
/* The value order MUST match the attribute order of the insert query */
In both cases:
values for PK and unique constraints must be satisfied
values must satisfy FK and other constraints
Best Approach

Delete From

SQL Commands Overview – DELETE FROM
The DELETE FROM statement removes all the tuples that satisfy the condition from a given table
The removal may result in deletions from other tables if a FOREIGN KEY constraint with CASECADE ON DELETE has been used

If the WHERE clause is omitted, DELETE FROM removes ALL tuples from the table:
DELETE FROM Departments; /* removes ALL departments from the table */

DELETE FROM Departments WHERE DeptName = ‘ITMS’ ;
/* removes ALL departments from the table with the name ITMS */

SQL Commands Overview – DELETE FROM
The WHERE clause dictates which rows of data should be deleted
Each row is compared to the WHERE clause and if it returns TRUE, the row is deleted

If you want to delete a specific record, what should the WHERE clause contain?
DELETE FROM Departments WHERE ??

SQL Commands Overview – DELETE FROM
Delete tuples
DELETE FROM Employees;
Deletes ALL tuples from the table, but not the table/schema itself

Delete from employees where regno = ‘E001’;
Deletes matching tuples (one tuple is deleted in previous example)

Delete the tuples and the schema (remove the table)
DROP TABLE Departments

Deletes all employees and Employee table followed by departments
Can the opposite be done?
If Employees contains a reference (FK) to Departments, what will happen?

DELETE FROM Employees;
DELETE FROM employees WHERE regno = ‘E001’;
DROP TABLE Employees;
DROP TABLE Departments;
Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

16

SQL Commands Overview – DELETE FROM
Syntax
DELETE FROM TableName [WHERE Condition]
Remove employee Neri Jackson

Remove departments without employees
DELETE FROM Employees
WHERE FirstName = ‘Neri’
AND Surname = ‘Jackson’
DELETE FROM Departments
WHERE DeptName NOT IN (
SELECT Dept FROM Employees
);

Update

SQL Commands Overview – UPDATE tableName SET
Syntax
UPDATE TableName
SET Attribute=
{, SET Attribute=}
[WHERE Condition]
Examples
UPDATE Employees
SET Salary = Salary + 5
WHERE RegNo = ‘M2047′
UPDATE Employees
SET Salary = Salary * 2.0
WHERE Dept = ‘Middle Management’
Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )
Note correction

SQL Commands Overview – UPDATE tableName SET
Since the language is set-oriented, the order of the statements can be important

If the statements are issued in this order, some employees may get a double raise!
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Salary < 30 UPDATE Employees SET Salary = Salary * 1.15 WHERE Salary >= 30
Query 1
Query 2
Yay! 

Remember SQL can be Fun!!

Table Creation, Data Manipulation. Query writing
SQL Introduction – Query Writing (SELECT)

22

So What’s This For?
You have got your tables/Database
You have got data in your database
Now lets start asking questions about our data

SQL as a query language
Remember – SQL expresses queries in a declarative way
Queries focus on the result, not how to obtain it
Queries are translated by the query optimiser into the procedural language internal to the DBMS
The DBMS Optimises the query and executes it against the database

The programmer should focus on readability, not on efficiency
In most cases! – only return the results you need

24

Select

SQL Commands Overview – SELECT x,y,z FROM
SELECT is used to retrieve data from the database
Remember SQL is Declarative
You don’t tell the DBMS how to get the data only about the data you want
Syntax:
SELECT FROM WHERE
Also called a SFW query
Examples:

Query Purpose
SELECT StudentID, StudentName FROM Student; Select specific columns from all tuples from Student
SELECT * FROM Student; Select all columns and all tuples from Student (display all records)
SELECT Student.* FROM Student; Select all columns and all tuples from Student specifically
SELECT * FROM Student WHERE StudentName = ‘Barry Williams’ Select all columns and all tuples with student name “Barry Williams”

SQL Commands Overview – SELECT x,y,z FROM
Syntax (abbreviated)
SELECT AttrExpr [[as] Alias {, AttrExpr [[as] Alias]}
FROM

[[as] Alias] {JOIN [[as]] Alias]}
[WHERE Condition]
< > – the part needs to be replaced by the actual table of interest
[ ] – the part is optional

The SELECT clause defines the target list of attributes/values to be returned
The FROM keyword defines the tables used by the query to obtain the attribute values
The WHERE clause Is included to determine which tuples should be retrieved
It specifies the conditions each tuple must match in order to be included in the final result

27

SQL Commands Overview – SELECT x,y,z FROM
When writing a query in SQL, you need to know the table schema (the attribute names) and what needs to be retrieved

Keywords (SELECT, WHERE etc) are not case sensitive but strings of text may be case sensitive depending on DBMS settings

A Simple test:

In the above query ‘S’ = ‘s’ returned true so MS-SQL in this instance is case insensitive

SQL Commands Overview – SELECT x,y,z FROM
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT *
FROM Product
WHERE Category=’Gadgets’
Product

PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks

“selection”

29

SQL Commands Overview – SELECT x,y,z FROM
SQL Queries return a new relations that are composed of the attributes used in the query
StudID Name
1 Mei
2 Phil

StudID Name
2 Phil

SELECT * FROM Students WHERE Name = ‘Phil’
StudID Name
1 Mei
2 Phil

Students
SELECT Name FROM Students
Name
Mei
Phil

Results
Students
Results

When you execute a SQL query over relations in the database you get back another relation.
The advantage of this is that we can take the values of this new relation and run them as part of another query
For example we could then query TableC and TableB for different information

30

SQL Commands Overview – SELECT x,y,z FROM
“selection” AND
“projection”
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT Pname, Price
FROM Product
WHERE Category = ‘Gadgets’
Product

PName Price
Gizmo $19.99
Powergizmo $29.99

31

SQL Commands Overview – SELECT x,y,z FROM
Notation
Product(PName, Price, Category, Manfacturer)
Answer(PName, Price, Manfacturer)
Input Schema
Results Output Schema
SELECT PName, Price, Manufacturer
FROM Product
WHERE Price > 100

32

WHERE Condition – Predicates
Predicate:
These are the expressions in the WHERE clause
They evaluate to TRUE, FALSE, or UNKNOWN for each row of data tested
Predicates are used in the search condition of WHERE clauses and HAVING clauses, the JOIN conditions of FROM clauses, and other constructs where a Boolean value is required

Predicates make use of Equality (=) and Inequality (<>)
See next slide for list of options

BETWEEN… AND…
price BETWEEN 100 and 150
price >= 100 AND price <= 150 SQL Commands Overview: WHERE Logical conditions often used to filter results: Use these to test values and combine tests with AND/OR as required 34 Condition Description = Equals <> Not equal
< Less than > Greater than
<= Less than or equal to >= Greater than or equal to
LIKE Partial matches (string comparison). Use ‘_’ for single unknown character
Use ‘%’ for zero or more unknown characters: LIKE ‘%a%’ returns all words containing ‘a’
NOT LIKE Not like the partial matches
IS NULL Test an attribute value is empty (ie, Null)
IS NOT NULL Test an attribute has a value (ie, not null)

Really Important

SQL Commands Overview: Duplicates – DISTINCT
To remove duplicate records use DISTINCT
SELECT City
FROM Departments
SELECT DISTINCT City
FROM Departments
City
London
Toulouse
Brighton
London
San Jose

City
London
Toulouse
Brighton
San Jose

same as:
SELECT ALL City
FROM Departments

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

35

SQL Commands Overview: Duplicates – DISTINCT
To remove duplicate records use DISTINCT
SELECT DISTINCT Surname
FROM Employees

SELECT Surname
FROM Employees

SQL Commands Overview: Renaming – AS
Columns in a select query can also be manipulated and renamed using the keyword ‘AS’

SELECT FirstName + ‘ ‘ + Surname AS Name, Salary
FROM Employees
37

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

SQL Commands Overview: ORDER BY
The ORDER BY re-orders the output of the query based on one or more selected attributes
ORDER BY Attr1 [ASC| DESC], …Attr_n [ASC| DESC]
Order By is ascending unless you specify the DESC keyword
ASC = ASCENDING, DESC = DESCENDING
Example
List the contents of Automobiles in descending order of make and model: (order model if make is the same)
SELECT *
FROM Automobiles
ORDER BY Make DESC,
Model DESC
CarRegNo Make Model DriverID
GHI789 Lancia Delta PZ10124436B
DEF456 BMW Z3 VR2030030Y
ABC123 BMW 323 VR2030030Y
BBB421 BMW 316 MI2020030U

Drivers ( FirstName, Surname, DriverID )
Automobiles ( CarRegNo, Make, Model, DriverID )

38

SQL Commands Overview: ORDER BY
The ORDER BY re-orders the output of the query based on one or more selected attributes
ORDER BY Attr1 [ASC| DESC], …Attr_n [ASC| DESC]
Order By is ascending unless you specify the DESC keyword
ASC = ASCENDING, DESC = DESCENDING
39
SELECT * FROM Employees
ORDER BY Employees.Surname ASC;

Is the same as:

SELECT * FROM Employees
ORDER BY Employees.Surname;

(ascending is assumed if not specified)

The Order By clauses is used to re-order the results of an SQL Select statement.
Following the Order By clause you specify the attribute that is going to be ordered in the result output followed by the direction of the ordering.

To sort

39

SQL Commands Overview: ORDER BY
The ORDER BY re-orders the output of the query based on one or more selected attributes
ORDER BY Attr1 [ASC| DESC], …Attr_n [ASC| DESC]
Order By is ascending unless you specify the DESC keyword
ASC = ASCENDING, DESC = DESCENDING
40
SELECT * FROM Employees
ORDER BY Employees.Surname ASC,
Employees.FirstName ASC;
Is the same as:

SELECT * FROM Employees
ORDER BY Employees.Surname,
Employees.FirstName;

(ascending is assumed if not specified)

The Order By clauses is used to re-order the results of an SQL Select statement.
Following the Order By clause you specify the attribute that is going to be ordered in the result output followed by the direction of the ordering.

To sort

40

SQL Commands Overview: SELECT TOP n
The [TOP n ] clause can be used to limit the number of matching results returned
Mainly used with ordered results (enables paging through results) – some system require the results to be ordered to use this function.
SELECT TOP 3 * FROM Employees
ORDER BY Surname
(ascending is assumed if not specified)

Is the same as:

SELECT TOP 3 FirstName, Surname
FROM Employees
ORDER BY Surname ASC

The Top n Clauses returns the top n matching results based on the sort order.

41

SQL – SELECT Syntax
The SQL SELECT Query is the most often used SQL statement

The select statement is the statement used to query the database.
It consists of 3 basic clauses, SELECT, FROM and WHERE clauses.

The From – identifies the relations that we want to query over
The Where – the conditions used to combine and filter the relations
The SELECT – the attributes or data to be returned
42

OK Sounds a Bit Complicated

How to write a SQL database query
You have all the data in the database.
Hopefully its systematic and normalized
You just need to figure out how to ask the question to get what you need.
A UML/Database Diagram WILL HELP.
Simple single table questions are straight forward
On multiple table questions you need JOIN the relations together with JOIN conditions
Follow the UML/Database Diagram relationship lines from FK  PK

Apply filtering conditions
WHERE x = y AND z like ‘me’

Hint

Work backwards!!

Go to the column(s) that your after and figure out how to get the data you want!!
What columns do I need (SELECT x, y, z)
What rows am I interested in (WHERE a =? AND b = ?)
What Table(s) do these come from? (FROM xxx)

SQL Commands Overview: WHERE
RegNo FirstName Surname Dept Office Salary City
001 Mary Brown Administration 10 45 London
002 Charles White Production 20 36 Toulouse
003 Gus Green Administration 20 40 Oxford
004 Jackson Neri Distribution 16 45 Dover
005 Charles Brown Planning 14 80 London
006 Laurence Chen Planning 7 73 Worthing
007 Pauline Bradshaw Administration 75 40 Brighton
008 Alice Jackson Production 75 46 Toulouse

Employees
DeptName Address City
Administration Bond Street London
Production Rue Victor Hugo Toulouse
Distribution Pond Road Brighton
Planning Bond Street London
Research Sunset Street San Jose

Departments

SQL Commands Overview: WHERE
Display names of employees who are in production department earning more than 40 thousand.
SELECT FirstName, Surname
FROM employees
WHERE dept = ‘Production’ AND salary > 40;

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

47

SQL Commands Overview: WHERE
Find all the information of the employees named Brown:
SELECT *
FROM Employees
WHERE Surname = ‘Brown’

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

SQL Commands Overview: WHERE
Find the first names and surnames of the employees who work in office number 20 of the Administration department:

SELECT FirstName, Surname
FROM Employees
WHERE Office = ’20’ AND
Dept = ‘Administration’
49

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

SQL Commands Overview: WHERE
Find the first names and surnames of the employees who work in either the Administration or the Production department:

SELECT FirstName, Surname
FROM Employees
WHERE Dept = ‘Administration‘ OR
Dept = ‘Production‘
50

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

SQL Commands Overview: WHERE
If no brackets are used then ‘AND’ is evaluated before ‘OR’
Find the first names of the employees named Brown who work in the Administration department or the Production department:
SELECT FirstName
FROM Employees
WHERE Surname = ‘Brown’ AND
(Dept = ‘Administration’ OR
Dept = ‘Production’)

If no brackets are used:
51

Table Schemas: Employees ( RegNo, FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

51

Database Setup

SQL Query – SELECT (Database Setup)

CREATE TABLE Employees(
Enum char(3),
Name varchar(100) NOT NULL,
Age int NULL,
Salary int NULL,
CONSTRAINT employeePk PRIMARY KEY (Enum)
);
CREATE TABLE Supervision(
Sid char(3),
Eid char(3),
CONSTRAINT supervisorFk FOREIGN KEY (Sid) REFERENCES Employees(Enum),
CONSTRAINT employeeFk FOREIGN KEY (Eid) REFERENCES Employees(Enum),
CONSTRAINT supervisorFk PRIMARY KEY (Sid, Eid)
);

SQL Query – SELECT (Database Setup)
Enum Name Age Salary
101 Mary Smith 34 40
103 Mary Bianchi 23 35
104 Luigi Neri 38 61
105 Nico Bini 44 38
210 Marco Celli 49 60
231 Siro Bisi 50 60
252 Nico Bini 44 70
301 Steve Smith 34 70
375 Mary Smith 50 65

Employees
Sid Eid
210 101
210 103
210 104
231 105
301 210
301 231
375 252

Supervision
Employees(Enum, Name, Age, Salary)
PK: Enum
Supervision(Sid, Eid)
PK: (Sid, Eid)
FK: Sid ~> Employees(Enum)
FK: Eid ~> Employees(Enum)
Logical Schema

SQL Query – SELECT (Database Setup)

INSERT INTO Employees VALUES
(‘101’, ‘Mary Smith’, 34, 40),
(‘103’, ‘Mary Bianchi’, 23, 35),
(‘104’, ‘Luigi Neri’, 38, 61),
(‘105’, ‘Nico Bini’, 44, 38),
(‘210’, ‘Marco Celli’, 49, 60),
(‘231’, ‘Siro Bisi’, 50, 60),
(‘252’, ‘Nico Bini’, 44, 70),
(‘301’, ‘Steve Smith’, 34, 70),
(‘375’, ‘Mary Smith’, 50, 65);
INSERT INTO Supervision VALUES
(210, 101),
(210, 103),
(210, 104),
(231, 105),
(301, 210),
(301, 231),
(375, 252);

Example 1 – Basic SELECT
Think: Asking a single table for an answer.
Find the number, the name and the age of each employee earning more than 40 thousand
Put required attributes in the SELECT clause:
the query asks for only 3 columns, so
SELECT Enum, Name, Age

Put required tables to get the attributes in the FROM clause:
It needs only one table to answer:
SELECT Enum, Name, Age
FROM Employees

Put filters in the WHERE clause:
WHERE salary > 40, so:
Enum Name Age
104 Luigi Neri 38
210 Marco Celli 49
231 Siro Bisi 50
252 Nico Bini 44
301 Steve Smith 34
375 Mary Smith 50

When Query is executed:
SELECT Enum, Name, Age
FROM Employees
WHERE salary > 40
Relational Schema:
Employees(Enum, Name, Age, Salary)
Supervision(Sid, Eid)

56

Example database – stored in a DBMS
FirstName Surname Dept Office Salary City
Mary Brown Administration 10 45 London
Charles White Production 20 36 Toulouse
Gus Green Administration 20 40 Oxford
Jackson Neri Distribution 16 45 Dover
Charles Brown Planning 14 80 London
Laurence Chen Planning 7 73 Worthing
Pauline Bradshaw Administration 75 40 Brighton
Alice Jackson Production 75 46 Toulouse

Employees
DeptName Address City
Administration Bond Street London
Production Rue Victor Hugo Toulouse
Distribution Pond Road Brighton
Planning Bond Street London
Research Sunset Street San Jose

Departments

57

Example database – stored in a DBMS
CREATE TABLE Departments(
DeptName varchar(50) PRIMARY KEY ,
Address varchar(50) NULL,
City varchar(50) NULL
);
CREATE TABLE dbo.Employees(
FirstName varchar(50) NOT NULL,
Surname varchar(50) NOT NULL,
Dept varchar(50) REFERENCES Departments(DeptName),
Office varchar(50) NULL,
Salary int NULL,
City varchar(50) NULL,
PRIMARY KEY (FirstName, Surname)
);
INSERT INTO Departments VALUES
(‘Administration’, ‘Bond Street’, ‘London’),
(‘Distribution’, ‘Pond Road’, ‘Brighton’),
(‘Planning’, ‘Bond Street’, ‘London’),
(‘Production’, ‘Rue Victor Hugo’, ‘Toulouse’),
(‘Research’, ‘Sunset Street’, ‘San Jose’);
INSERT INTO Employees VALUES
(‘Mary’, ‘Brown’, ‘Administration’, ’10’, 45, ‘London’),
(‘Charles’, ‘White’, ‘Production’, ’20’, 36, ‘Toulouse’),
(‘Gus’, ‘Green’, ‘Administration’, ’20’, 40, ‘Oxford’),
(‘Jackson’, ‘Neri’, ‘Distribution’, ’16’, 45, ‘Dover’),
(‘Charles’, ‘Brown’, ‘Planning’, ’14’, 80, ‘London’),
(‘Laurence’, ‘Chen’, ‘Planning’, ‘7’, 73, ‘Worthing’),
(‘Pauline’, ‘Bradshaw’, ‘Administration’, ’75’, 40, ‘Brighton’),
(‘Alice’, ‘Jackson’, ‘Production’, ’75’, 46, ‘Toulouse’);

Simple Query – SELECT *
Select all columns of all tuples of a table
SELECT * FROM tableName

Example
Find the salaries of ALL employees:

SELECT * FROM Employees

59

Simple Query – conjunction (“and”) Condition
Find the first names AND surnames of the employees who work in office number 20 of the Administration department:
SELECT FirstName, Surname
FROM Employees
WHERE Office = ’20’ AND
Dept = ‘Administration’
FirstName Surname
Gus Green

Execution output:
Table Schemas: Employees ( FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

60

Condition – disjunction (“or”)
Find the first names and surnames of the employees who work in either the Administration or the Production department:
SELECT FirstName, Surname
FROM Employees
WHERE Dept = ‘Administration’ OR
Dept = ‘Production’
FirstName Surname
Mary Brown
Charles White
Gus Green
Pauline Bradshaw
Alice Jackson

Execution output:
Table Schemas: Employees ( FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

61

Condition – Logic Operator Precedence
Find the first names of the employees named Brown who work in the Administration department or the Production department:
If not using brackets, ‘AND’ is evaluated before ‘OR’ (Left -> Right)

SELECT FirstName
FROM Employees
WHERE Surname = ‘Brown’ AND
(
Dept = ‘Administration’ OR
Dept = ‘Production’
)
FirstName
Mary

Execution output:
What is the difference if the condition is the following?
Surname = ‘Brown’ AND
Dept = ‘Administration’ OR Dept = ‘Production’

Table Schemas: Employees ( FirstName, Surname, Dept, Office, Salary, City )
FK(Dept) -> Departments(DeptName)

Departments ( DeptName, Address, City )

62

Condition – Pattern Matching: LIKE operator
s LIKE p: pattern matching on strings
p may contain two special symbols:
% = any sequence of characters
_ = any single character
63
SELECT *
FROM Products
WHERE PName LIKE ‘%gizmo%’

63

Condition – Pattern Matching: LIKE operator
Equality (=) is often too strong
‘London’ <> ‘London, UK’ <> ‘Richmond, London’
Need a way to match sub-strings
Find the employees with surnames that have ‘r’ as the second letter and end in ‘n’:
SELECT *
FROM Employees
WHERE Surname LIKE ‘_r%n’
FirstName Surname Dept Office Salary City
Mary Brown Administration 10 45 London
Gus Green Administration 20 40 Oxford
Charles Brown Planning 14 80 London

In patterns,
‘_’ means any single character and
‘%’ means any string of characters

64

Condition – Predicates for NULL values
Null values may mean that:
a value is not applicable
a value is applicable but unknown
it is unknown whether a value is applicable or not

SQL-89 used a two-valued logic
a comparison with NULL returned FALSE

SQL-2 uses a three-valued logic
a comparison with NULL returns UNKNOWN

To test for null values, you must use
Attribute IS NULL
Attribute IS NOT NULL

Do NOT USER Attribute=‘NULL’ or Attribute=NULL

65

VB002206.tif

/docProps/thumbnail.jpeg