"A language that doesn't affect..."

	A language that doesn't affect the way you think about programming is not worth knowing.
	- Alan Perlis, “Epigrams in Computing”, SIGPLAN, 1982

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)

Moshe M. Zloof
Query-by-Example (QBE)

	Introduced by IBM in 1975
	Graphical interface to SQL
	Has influenced the query interfaces of other DB systems:
		Paradox
		Access

Query-By-Example

Edgar F. Codd
Relational Algebra/Calculus

	Developed from 1971-1974
	Relational Algebra - a procedural language:
		Relations
		Relational operators
	Relational Calculus - a declarative language with equivalent power.

Structured Query Language

Access Query Types

	Retrieval queries:
		Select queries
	Modification queries:
		Make-table queries
		Delete queries
		Update queries
		Append queries

Projection Queries

Selection Queries

Conditions

Join Queries

The Database Schema

Combining Operations

Sorting

Grouping & Aggregate Functions

Arithmetic

SQL

	Structured Query Language:
		Specially designed for data queries and updates
		Command-line based
	It is the industry standard

Using SQL

Using SQL

Using SQL

Basic Query Types

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

SELECT Syntax

Single-Table Queries

	Q: Get a list of all the products.

The SELECT Clause 1

	Q: Get names, categories and prices of all the products.

The SELECT Clause 2

	Q: Get the total value of each product in stock.

The SELECT Clause 3

	Q: Can SELECT return duplicates or not?

The SELECT Clause 4

	Q: Get a list of the category types for products.

The WHERE Clause 1

	Q: Get the foreign customers.

The WHERE Clause 2

	Q: Get the Customers at 100 Main Street, New York.

The WHERE Clause 3

	Q: Get the products without images.

The ORDER BY Clause

	Q: Get the Employees in alphabetical order.

Multiple-Table Queries

	Q: Get the list of products for each customer order.

Grouping and Aggregation 1

	Q: Count the products in each category.

Grouping and Aggregation 2

	Q: Get the categories with more than 3 products.

Set Operations

	Q: Get the names of all suppliers and customers.

Inserting Data

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

Updating Data

	Q: Change Carnivorous’s address.

Deleting Data

	Q: Remove Carnivorous from the Customers table.

Importing External Data

	Frequently, data from other sources must be imported in bulk.
	Approaches:
		an SQL INSERT command file
		a specialized import facility