© keith vander linden, 2005 1 a language that doesn't affect the way you think about...

1

© Keith Vander Linden, 2005

A language that doesn't affect the way you think about programming is not worth knowing.

- Alan Perlis, “Epigrams in Computing”, SIGPLAN, 1982

2

© Keith Vander Linden, 2005

Database Languages

● Database languages provide features for:– Building a database schema– Retrieving data from a database– Manipulating data in a database

● Two common languages:– Query-By-Example (QBE) – Structured Query Language (SQL)

(Sections 9.5-8)

3

© Keith Vander Linden, 2005Image from www.hp.com July, 2001

Moshe M. ZloofQuery-by-Example (QBE)

● Introduced by IBM in 1975● Graphical interface to SQL● Has influenced the query interfaces of

other DB systems:– Paradox– Access

4

© Keith Vander Linden, 2005

Query-By-Example

5

© Keith Vander Linden, 2005

Edgar F. CoddRelational Algebra/Calculus

● Developed from 1971-1974● Relational Algebra - a procedural

language:– Relations

– Relational operators

● Relational Calculus - a declarative language with equivalent power.

Image from www.computer.org, July, 2001

6

© Keith Vander Linden, 2005

Structured Query Language

7

© Keith Vander Linden, 2005

The Database Schema

8

© Keith Vander Linden, 2005

Access Query Types

● Retrieval queries:– Select queries

● Modification queries:– Make-table queries– Delete queries– Update queries– Append queries

9

© Keith Vander Linden, 2005

Projection Queries

10

© Keith Vander Linden, 2005

Selection Queries

11

© Keith Vander Linden, 2005

Conditions

12

© Keith Vander Linden, 2005

Join Queries

13

© Keith Vander Linden, 2005

Combining Operations

14

© Keith Vander Linden, 2005

Sorting

15

© Keith Vander Linden, 2005

Grouping & Aggregate Functions

16

© Keith Vander Linden, 2005

Arithmetic

17

© Keith Vander Linden, 2005

SQL

● Structured Query Language:– Specially designed for data queries and

updates– Command-line based

● It is the industry standard

18

© Keith Vander Linden, 2005

Access

Jet Engine

Using SQL

Jet DB

Interface

19

© Keith Vander Linden, 2005

MSDE EngineSQL ServerJet EngineOracle Engine

Using SQL

Oracle DB Jet DB

Access Interface

SQL Server DB

MSDE DB

ODBC Driver

ODBC Driver

ODBC Driver

ODBC Driver

VBApplication

Java Applet

JDBC Driver

20

© Keith Vander Linden, 2005

DB Engine

Using SQL

DB

ODBC Driver

JDBC Driver

Web Browser Network Web Server

21

© Keith Vander Linden, 2005

Basic Query Types

● Single-table queries● Multiple-table queries● Aggregation and Grouping● Set Operations● Database Modifications

22

© Keith Vander Linden, 2005

SELECT Syntax

SELECT <attributes or expressions>FROM <tables>[WHERE <conditions on the attributes>][GROUP BY <attribute list> [HAVING <conditions>] ][ORDER BY <attribute list>]

23

© Keith Vander Linden, 2005

Single-Table QueriesQ: Get a list of all the products.

SELECT * FROM Products;

24

© Keith Vander Linden, 2005

The SELECT Clause 1Q: Get names, categories and prices of all the products.

SELECT name, unitPrice, categoryFROM Products;

25

© Keith Vander Linden, 2005

The SELECT Clause 2Q: Get the total value of each product in stock.

SELECT name, (unitPrice * inStock)FROM Products;

26

© Keith Vander Linden, 2005

The SELECT Clause 3Q: Can SELECT return duplicates or not?

SELECT categoryFROM Products;

27

© Keith Vander Linden, 2005

The SELECT Clause 4Q: Get a list of the category types for products.

SELECT DISTINCT categoryFROM Products;

28

© Keith Vander Linden, 2005

The WHERE Clause 1Q: Get the foreign customers.

SELECT firstName, lastName, country FROM CustomersWHERE country <> 'USA';

29

© Keith Vander Linden, 2005

The WHERE Clause 2Q: Get the Customers at 100 Main Street, New York.

SELECT firstName, lastName, street, cityFROM CustomersWHERE street='100 Main St.'AND city='New York';

30

© Keith Vander Linden, 2005

The WHERE Clause 3Q: Get the products without images.

SELECT name, imageFROM ProductsWHERE image IS NULL;

31

© Keith Vander Linden, 2005

The ORDER BY ClauseQ: Get the Employees in alphabetical order.

SELECT lastName+', '+firstName AS fullNameFROM CustomersORDER BY lastName, firstName;

32

© Keith Vander Linden, 2005

Multiple-Table QueriesQ: Get the list of products for each customer order.

SELECT orderId, name, quantityFROM Products, OrderDetailsWHERE id=productID;

33

© Keith Vander Linden, 2005

Multiple-Table Queries 2Q: Get the names of the products and customers that order them.

SELECT name, firstName, lastNameFROM Products, OrderDetails, Orders, CustomersWHERE Products.ID=OrderDetails.productID AND OrderDetails.orderID = Orders.ID AND Orders.customerID = Customers.ID;

34

© Keith Vander Linden, 2005

Grouping and Aggregation 1Q: Count the products in each category.

SELECT category, Count(category)FROM ProductsGROUP BY categoryORDER BY Count(category) DESC;

35

© Keith Vander Linden, 2005

Grouping and Aggregation 2

Q: Get the categories with more than 3 products.

SELECT category, Count(category)FROM ProductsGROUP BY categoryHAVING Count(category) > 3;

36

© Keith Vander Linden, 2005

Set OperationsQ: Get the names of all suppliers and customers.

SELECT name FROM Suppliers UNIONSELECT firstName+’ ’+lastName FROM Customers;

37

© Keith Vander Linden, 2005

Inserting DataQ: Add Wile E’s alter ego to the customers list.

INSERT INTO Customers(id,firstName,lastName)VALUES (14,'Carnivorous','Vulgarus');

38

© Keith Vander Linden, 2005

Updating DataQ: Change Carnivorous’s address.

UPDATE CustomersSET street = '1 Cave Lane'WHERE id = 14;

39

© Keith Vander Linden, 2005

Deleting DataQ: Remove Carnivorous from the Customers table.

DELETE FROM CustomersWHERE id = 14;

40

© Keith Vander Linden, 2005

Importing External Data

● Frequently, data from other sources must be imported in bulk.

● Approaches:– an SQL INSERT command file– a specialized import facility