Chapter 3
This presentation is the property of its rightful owner.
Sponsored Links
1 / 36

Chapter 3 PowerPoint PPT Presentation


  • 419 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 3. The Relational Model 2: SQL. Objectives. Use simple and compound conditions in SQL Use computed fields in SQL Use built-in SQL functions Use subqueries in SQL Group records in SQL. Objectives. Join tables using SQL Perform union operations in SQL

Download Presentation

Chapter 3

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chapter 3

Chapter 3

The Relational

Model 2: SQL

Concepts of Database Management, 4th Edition, Pratt & Adamski


Objectives

Objectives

  • Use simple and compound conditions in SQL

  • Use computed fields in SQL

  • Use built-in SQL functions

  • Use subqueries in SQL

  • Group records in SQL

Concepts of Database Management, 4th Edition, Pratt & Adamski


Objectives1

Objectives

  • Join tables using SQL

  • Perform union operations in SQL

  • Use SQL to update database data

  • Use an SQL query to create a table in a database

Concepts of Database Management, 4th Edition, Pratt & Adamski


Table creation

Table Creation

  • SQL CREATE TABLE

    • Used to describe layout of a table

  • Typical restrictions placed by DBMS

    • Names cannot exceed 18 characters

    • Names must start with a letter

    • Names can contain only letters, numbers, and underscores (_)

    • Names cannot contain spaces

Concepts of Database Management, 4th Edition, Pratt & Adamski


Typical data types

Typical Data Types

  • INTEGER

    • Numbers without a decimal point

  • SMALLINT

    • Uses less space than INTEGER

  • DECIMAL(p,q)

    • P number of digits; q number of decimal places

  • CHAR(n)

    • Character string n places long

  • DATE

    • Dates in DD-MON-YYYY or MM/DD/YYYY

Concepts of Database Management, 4th Edition, Pratt & Adamski


Simple retrieval figures 3 1 3 2

Simple RetrievalFigures 3.1 - 3.2

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to List Part Table

Figures 3.3 - 3.4

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with Where Condition

Figures 3.5 - 3.6

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Comparison Operators

Figure 3.7

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Find Customer 148

Figures 3.8 - 3.9

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Find

All Customers in ‘Grove’

Figures 3.10 - 3.11

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

Query to find Customers with Credit Limit Exceeding Balance

Figures 3.12 - 3.13

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with Compound Condition using ‘AND’

Figures 3.14 - 3.15

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query using ‘OR’

Figures 3.16 - 3.17

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query using ‘NOT’

Figures 3.18 - 3.19

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

Query with ‘BETWEEN’ Operator

Figures 3.20 - 3.21

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with Computed Field

Figures 3.22 - 3.23

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with Computed

Field and Condition

Figures 3.24 - 3.25

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with ‘LIKE’ Operator

Figures 3.26 - 3.27

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with ‘IN’ Operator

Figures 3.28 - 3.29

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Sort Data

Figures 3.30 - 3.31

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Sort on Multiple Fields

Figures 3.32 - 3.33

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Count Records

Figures 3.34 - 3.35

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Count Records and Calculate a Total

Figures 3.36 - 3.37

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Perform Calculations and Rename Fields

Figures 3.38 - 3.39

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with Subquery

Figures 3.40 - 3.41

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Group Records

Figures 3.42 - 3.43

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Restrict Groups Figures 3.44 - 3.45

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query with ‘WHERE’

and ‘HAVING’ Clauses

Figures 3.46 - 3.47

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Join Tables

Figures 3.48 - 3.49

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

Query to Restrict Records in Join

Figures 3.50 - 3.51

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

Query to Join Multiple Tables

Figures 3.52 - 3.53

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Perform Union

Figures 3.54 - 3.55

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Update Data

Figures 3.56 - 3.57

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Delete Rows

Figure 3.58

Concepts of Database Management, 4th Edition, Pratt & Adamski


The relational model 2 sql

SQL Query to Create New Table

Figures 3.59 - 3.60

Concepts of Database Management, 4th Edition, Pratt & Adamski


  • Login