Basic Retrieval Queries in SQL

Retrieving data is the primary purpose of a database system, and SQL provides a powerful and flexible mechanism for doing so using the SELECT statement. Unlike relational algebra, which is procedural, SQL is a declarative language: the user specifies what data is required, not how to retrieve it.

---

SQL vs Relational Model (Important Conceptual Difference)

In the formal relational model, relations are sets of tuples, so duplicate tuples are not allowed.

In SQL, tables are treated as multisets (bags) by default:

• Duplicate rows are allowed

• Duplicate results are not eliminated automatically

• Duplicate elimination must be explicitly requested

This distinction is crucial when interpreting SQL query results.

---

SELECT–FROM–WHERE Structure of Basic SQL Queries

The fundamental structure of an SQL retrieval query is:

SELECT <attribute list>
FROM <table list>
WHERE <condition>;

Clause Functions

Clause	Purpose
SELECT	Specifies attributes to display (projection)
FROM	Specifies tables needed for the query
WHERE	Specifies selection and join conditions

---

Query Processing Conceptually

1. The DBMS forms a Cartesian product of tables in the FROM clause

2. The WHERE clause filters tuples (selection + join)

3. The SELECT clause projects required attributes

---

Example 1: Simple Selection and Projection

Retrieve birth date and address of employee named John B. Smith

SELECT Bdate, Address
FROM EMPLOYEE
WHERE Fname = 'John' AND Minit = 'B' AND Lname = 'Smith';

• Uses only one table

• WHERE clause filters tuples

• SELECT clause chooses attributes

Equivalent to:

• Selection + Projection in relational algebra

---

Example 2: Join Query

Retrieve employees working in the ‘Research’ department

SELECT Fname, Lname, Address
FROM EMPLOYEE, DEPARTMENT
WHERE Dname = 'Research' AND Dnumber = Dno;

Types of conditions:

• Dname = 'Research' → selection condition

• Dnumber = Dno → join condition

This is a select–project–join query.

---

Example 3: Multiple Joins

SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber
  AND Mgr_ssn = Ssn
  AND Plocation = 'Stafford';

• Multiple tables joined

• Each result tuple combines:

  • A project

  • Its controlling department

  • The department manager

---

Ambiguous Attribute Names, Aliasing, and Tuple Variables

Ambiguous Attribute Names

When multiple tables contain attributes with the same name, attributes must be qualified:

EMPLOYEE.Lname
DEPARTMENT.Dname

This prevents ambiguity.

---

Aliasing Tables (Tuple Variables)

Aliases simplify queries and are mandatory when:

• The same table appears more than once in a query

Example: Self-join

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;

• E: employee (subordinate)

• S: supervisor

• This is a recursive (self-join) query

Aliases:

• Improve readability

• Enable self-joins

• Are strongly recommended

---

Missing WHERE Clause and Use of Asterisk (*)

Missing WHERE Clause

• Single table → all rows selected

• Multiple tables → Cartesian product

SELECT Ssn
FROM EMPLOYEE;

SELECT Ssn, Dname
FROM EMPLOYEE, DEPARTMENT;

⚠️ Danger: forgetting join conditions leads to huge, incorrect results.

---

Asterisk (*) Usage

SELECT *
FROM EMPLOYEE;

• Retrieves all attributes

• Can be table-qualified:

SELECT EMPLOYEE.*
FROM EMPLOYEE;

---

Tables as Sets vs Multisets in SQL

Duplicate Tuples in SQL

SQL does not remove duplicates by default because:

1. Duplicate elimination is expensive

2. Users may want duplicates

3. Aggregate functions often need duplicates

---

DISTINCT Keyword

SELECT DISTINCT Salary
FROM EMPLOYEE;

• Removes duplicate rows

• Without DISTINCT → behaves as SELECT ALL

---

Set Operations in SQL

Operation	Description
UNION	Combines results, removes duplicates
EXCEPT	Set difference
INTERSECT	Common tuples

Conditions:

• Relations must be type compatible

• Same number and order of attributes

Example (UNION):

(SELECT Pnumber FROM PROJECT ...)
UNION
(SELECT Pnumber FROM WORKS_ON ...);

Multiset versions:

• UNION ALL

• EXCEPT ALL

• INTERSECT ALL

---

Pattern Matching and Arithmetic Operators

LIKE Operator (Substring Matching)

Wildcards:

• % → zero or more characters

• _ → exactly one character

Example:

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Address LIKE '%Houston,TX%';

---

Escaping Wildcards

'AB\_CD\%EF' ESCAPE '\'

---

Arithmetic Operators

Operator	Meaning
+	Addition
-	Subtraction
*	Multiplication
/	Division

Example (salary increase):

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE E, WORKS_ON W, PROJECT P
WHERE E.Ssn = W.Essn
  AND W.Pno = P.Pnumber
  AND P.Pname = 'ProductX';

---

BETWEEN Operator

WHERE Salary BETWEEN 30000 AND 40000;

Equivalent to:

Salary >= 30000 AND Salary <= 40000

---

6.3.6 Ordering Query Results

SQL results are unordered by default.

ORDER BY Clause

ORDER BY D.Dname, E.Lname, E.Fname;

• Default order: ascending (ASC)

• Use DESC for descending order

ORDER BY D.Dname DESC, E.Lname ASC;

---

Summary of Basic SQL Retrieval Queries

General Query Format

SELECT <attribute list>
FROM <table list>
[WHERE <condition>]
[ORDER BY <attribute list>];

Key Takeaways

• SQL is declarative

• SELECT = projection

• WHERE = selection + join

• FROM = involved tables

• SQL supports:

  • Joins

  • Set operations

  • Pattern matching

  • Arithmetic expressions

  • Ordering of results