The Relational Model 2:

1
Chapter 3

• The Relational Model 2
• SQL

Database Management
2
Objectives

• Introduce the SQL language.
• Discuss the use of simple and compound conditions
  in SQL.
• Present the use of computed fields in SQL.
• Examine the use of SQL built-in functions.

3
Objectives

• Discuss the use of nested SQL queries.
• Examine grouping in SQL.
• Examine the way tables can be joined in SQL.
• Discuss the union operator in SQL.

4
Database Creation

• CREATE TABLE is used to describe the layout of a
  table.
• Typical restrictions are
• The table or column name can be no longer than 18
  characters.
• The name must start with a letter.
• The name can contain letters, numbers, and
  underscores (_).
• The name cannot contain spaces.

5
Database Creation

• INTEGER
• Numbers with or without a decimal point.
• SMALLINT
• Uses less space than INTEGER.
• DECIMAL (p,q)
• P number of digits, q number of decimal places.

6
Database Creation

• CHAR (n)
• Character string n places long.
• DATE
• Dates in DD-MON-YYYY or MM/DD/YYYY

7
Figure 3.1Simple Retrieval
8
Figure 3.2 SQL Query to List Part Table
9
Figure 3.3 SQL Query with a WHERE Condition
10
Table 3.1 Comparison Operators
11
Figure 3.4 SQL Query to Find Customer 124s Name
12
Figure 3.5 SQL Query to Find Customer Numbers
13
Figure 3.6Compound Conditions
14
Figure 3.7 SQL Query with a Negative Compound
Condition
15
Figure 3.8Computed Fields
16
Figure 3.9Sorting
17
Figure 3.10Built-In Functions
18
Figure 3.11 Built-In Functions
19
Figure 3.12Nesting Queries
20
Figure 3.13 Nesting Queries
21
Figure 3.14 Using Subqueries
22
Figure 3.15Grouping
23
Figure 3.16 SQL Query Using HAVING Clause
to Impart Conditions on Groups
24
Joining Tables

• In the SELECT clause, you indicate all the fields
  you want to display.
• In the FROM clause, you list all the tables
  involved in the query.
• In the WHERE clause, you give the condition that
  restricts the data to be retrieved to only those
  rows from the two tables that match.

25
Figure 3.17 SQL Query to Join Data from Two
Different Tables
26
Figure 3.18 Join Query with a Compound Condition
27
Figure 3.19Union
28
Figure 3.20Updating Tables
29
Figure 3.21 and Figure 3.22 Adding and Deleting
Data