Database Concepts
Ninth Edition
Chapter 3
Structured Query Language
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Learning Objectives
• Learn basic SQL statements for creating database structures
• Learn basic SQL statements for adding data to a database
• Learn basic SQL SELECT statements and options for
processing a single table
• Learn basic SQL SELECT statements for processing multiple
tables with subqueries
• Learn basic SQL SELECT statements for processing multiple
tables with joins
• Learn basic SQL statements for modifying and deleting data
from a database
• Learn basic SQL statements for modifying and deleting
database tables and constraints
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Structured Query Language
Learn basic SQL statements for creating
database structures
• Structured Query Language (SQL) is not a complete
programming language; rather, it is a data sublanguage
– Developed by IBM in the late 1970’s
– Endorsed and adopted by ANSI in 1992
– Endorsed as a standard by the International Organization
for Standardization (ISO)
– It is text oriented
– Can be used by MS Access using Query by Example
(QBE)
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
SQL Statement Categories
Learn basic SQL statements for creating
database structures
• SQL statement categories are as follows:
– Data definition language (DDL)
Statements used to create database structures
– Data manipulation language (DML)
Statements used to query, insert, modify and delete data
– SQL/Persistent stored modules (SQL/PSM)
Statements to extend SQL by adding procedural
programming capabilities
– Transaction control language (TCL)
Statements used to mark transaction boundaries
– Data control language (DCL)
Statements used to grant and revoke database permissions
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Wedgewood Pacific Example
Learn basic SQL statements for creating
database structures
• This chapter uses the Wedgewood Pacific (WP) company as an
example.
– Founded in 1957 in Seattle, WA
– Manufacture and sell consumer drone aircraft
– In January, 2018, the FAA said that over one million drones had
been registered
– WP’s database has four tables (Department, Employee, Project,
and Assignment)
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Wedgewood Pacific Relations
Learn basic SQL statements for creating
database structures
• The following relations are used for the Wedgewood Pacific (WP)
example used in this chapter:
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
WP’s Referential Integrity
Constraints
Learn basic SQL statements for creating
database structures
• A referential integrity constraint is used to link (or reference) relations.
This means that a foreign key in a relation must also exist in the
relation in which it serves as the primary key. WP’s referential
integrity constraints:
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.1(a) Database Column
Characteristics for the WP Database
Access 2019, Windows 10, Microsoft Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.1(b) DEPARTMENT Table
Column Name Type Key Required Remarks
DepartmentName Short Text (35) Primary Key Yes Blank
BudgetCode Short Text (30) No Yes Blank
OfficeNumber Short Text (15) No Yes Blank
DepartmentPhone Short Text (12) No Yes Blank
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.1(c) EMPLOYEE Table
Column Name Type Key Required Remarks
EmployeeNumber AutoNumber Primary Key Yes Surrogate Key
FirstName Short Text (25) No Yes Blank
LastName Short Text (25) No Yes Blank
Department Short Text (35) Foreign Key Yes Links to DepartmentName in
DEPARTMENT
Position Short Text (35) No No Blank
Supervisor Number Foreign No Long Integer. Links to
Key EmployeeNumber in
EMPLOYEE
OfficePhone Short Text (12) No No Blank
EmailAddress Short Text (100) No Yes Blank
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.1(d) PROJECT Table
ColumnName Type Key Required Remarks
ProjectID Number Primary Key Yes Long Integer
ProjectName Short Text No Yes Blank
(50)
Department Short Text Foreign Key Yes Links to
(35) DepartmentName in
DEPARTMENT
MaxHours Number No Yes Double
StartDate Date No No Blank
EndDate Date No No Blank
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.1(e) ASSIGNMENT Table
Column Name Type Key Required Remarks
ProjectID Number Primary Key, Yes Long Integer
Foreign Key Links to ProjectID in
PROJECT
EmployeeNumber Number Primary Key, Yes Long Integer
Foreign Key Links to
EmployeeNumber in
EMPLOYEE
HoursWorked Number No No Double
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.2(a) DEPARTMENT Table
Sample Data for the WP Database
DepartmentName BudgetCode OfficeNumber DepartmentPhone
Administration BC-100-10 BLDG01-210 360-285-8100
Legal BC-200-10 BLDG01-220 360-285-8200
Human Resources BC-300-10 BLDG01-230 360-285-8300
Finance BC-400-10 BLDG01-110 360-285-8400
Accounting BC-500-10 BLDG01-120 360-285-8405
Sales and Marketing BC-600-10 BLDG01-250 360-285-8500
InfoSystems BC-700-10 BLDG02-210 360-285-8600
Research and Development BC-800-10 BLDG02-250 360-285-8700
Production BC-900-10 BLDG02-110 360-285-8800
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.2(b) Sample Data for the WP
Database: EMPLOYEE Table (1 of 2)
Employee FirstName LastName Department Position Supervisor OfficePhone EmailAddress
Number
1 Mary Jacobs Administration CE O 360-285-8110 Mary.Jacobs@WP.com
2 Rosalie Jackson Administration Admin Assistant 1 360-285-8120 Rosalie.Jackson@WP.com
3 Richard Bandalone Legal Attorney 1 360-285-8210 Richard.Bandalone
@ WP.com
4 George Smith Human Resources HR3 1 360-285-8310 George.Smith@WP.com
5 Alan Adams Human Resources HR1 4 360-285-8320 Alan.Adams@WP.com
6 Ken Evans Finance CFO 1 360-285-8410 Ken.Evans@WP.com
7 Mary Abernathy Finance FA3 6 360-285-8420 Mary.Abernathy@WP.com
8 Tom Caruthers Accounting FA2 6 360-285-8430 Tom.Caruthers@WP.com
9 Heather Jones Accounting FA2 6 360-285-8440 Heather.Jones@WP.com
10 Ken Numoto Sales and Marketing SM3 1 360-285-8510 Ken.Numoto@WP.com
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.2(b) Sample Data for the WP
Database: EMPLOYEE Table (2 of 2)
Employee FirstName LastName Department Position Supervisor OfficePhone EmailAddress
Number
11 Linda Granger Sales and SM2 10 360-285-8520 Linda.Granger@WP.com
Marketing
12 James Nestor InfoSystems CIO 1 360-285-8610 James.Nestor@WP.com
13 Rick Brown InfoSystems IS2 12 Blank Rick.Brown@WP.com
14 Mike Nguyen Research and CTO 1 360-285-8710 Mike.Nguyen@WP.com
Development
15 Jason Sleeman Research and RD3 14 360-285-8720 Jason.Sleeman@WP.com
Development
16 Mary Smith Production OPS3 1 360-285-8810 Mary.Smith@WP.com
17 Tom Jackson Production OPS2 16 360-285-8820 Tom.Jackson@WP.com
18 George Jones Production OPS2 17 360-285-8830 George.Jones@WP.com
19 Julia Hayakawa Production OPS1 17 Blank Julia.Hayakawa@WP.com
20 Sam Stewart Production OPS1 17 Blank Sam.Stewart@WP.com
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.2(c) Sample Data for the WP
Database: PROJECT Table
ProjectID ProjectName Department MaxHours StartDate EndDate
1000 2019 Q3 Production Plan Production 100.00 05/10/19 06/15/19
1100 2019 Q3 Marketing Plan Sales and Marketing 135.00 05/10/19 06/15/19
1200 2019 Q3 Portfolio Analysis Finance 120.00 07/05/19 07/25/19
1300 2019 Q3 Tax Preparation Accounting 145.00 08/10/19 10/15/19
1400 2019 Q4 Production Plan Production 100.00 08/10/19 09/15/19
1500 2019 Q4 Marketing Plan Sales and Marketing 135.00 08/10/19 09/15/19
1600 2019 Q4 Portfolio Analysis Finance 140.00 10/05/19 Blank
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.2(d) Sample Data for the WP
Database: ASSIGNMENT Table
ProjectID EmployeeNumber HoursWorked
1000 1 30.00
1000 6 50.00
1000 10 50.00
1000 16 75.00
1000 17 75.00
1100 1 30.00
1100 6 75.00
Partial List of Data
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.3 The Microsoft Access 2019
Options Object Designers Page
Access 2019, Windows 10, Microsoft Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
SQL for Data Definition Language
(DDL)
Learn basic SQL statements for creating
database structures
• The SQL data definition statements include:
– CREATE
to create database objects
– ALTER
to modify the structure and/or characteristics of database
objects
– DROP
to delete database objects
– TRUNCATE
to delete table data while keeping the structure
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
SQL CREATE TABLE Statement
Learn basic SQL statements for creating
database structures
• The SQL CREATE TABLE statement format is as follows:
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.4 SQL CREATE TABLE
Statements
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Data Types
• There are many data types available in all DBMS products. To
see these you can refer to page 146 in your text.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.6 Creating Primary Keys
with SQL Table Constraints
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.7 Creating Foreign Keys with SQL Table
Constraints
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.8 Processing the SQL CREATE TABLE
Statements Using MySQL 8.0
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.9 Processing the SQL CREATE TABLE
Statements Using MS SQL Server 2017
SQL Server 2017, SQL Server Management Studio, Microsoft Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.10 Processing the SQL CREATE TABLE
Statements Using Oracle Database XE
Oracle Database XE, SQL Developer 18.4, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
SQL for Data Manipulation (DML) –
Inserting Data
• SQL DML is used to query databases and to modify data in the
tables.
• There are three possible data modification operations:
– INSERT (adding data to a relation)
– UPDATE (modifying data in a relation)
– DELETE (deleting data in a relation)
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Inserting Data
Learn basic SQL SELECT for adding data to a
database
The order of the columns on the INSERT command does not
matter as long as the values match the order of the columns as
seen below.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
The SQL Query Framework
Learn basic SQL SELECT statements and
options for processing a single table
• The basic framework for the SQL Query statement has
three statements as follows:
– the SQL SELECT clause
specifies which columns are to be listed in the
query results
– the SQL FROM clause
specifies which tables are to be used in the query
– the SQL WHERE clause
specifies which rows are to be listed in the query
results
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Reading Specified Columns from a
Single Table
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries Displaying All
Columns
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries Specifying
Column Order
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.12 SQL Query Results in the
MySQL Workbench
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.13 SQL Query Results in the
Microsoft SQL Server Management Studio
SQL Server 2017, SQL Server Management Studio, Microsoft Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.14 SQL Query Results in the Oracle
SQL Developer
Oracle Database XE, SQL Developer 18.4, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Reading Specified Rows from a
Single Table
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries The DISTINCT
Keyword
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries The WHERE
Clause
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.15 SQL Comparison
Operators
SQL Comparison Operators
Operator Meaning
= Is equal to
<> Is NOT Equal to
< Is less than
> Is greater than
<= Is less than OR equal to
>= Is greater than OR equal to
IN Is equal to one of a set of values
NOT IN Is NOT equal to any of a set of values
BETWEEN Is within a range of numbers (includes the end points)
NOT BETWEEN Is NOT within a range of numbers (includes the end points)
LIKE Matches a set of characters
NOT LIKE Does NOT match a set of characters
IS NULL Is equal to NULL
IS NOT NULL Is NOT equal to NULL
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries Using
Comparison Operators
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries Reading
Specified Columns and Rows
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries Sorting the
Results of a Query
Learn basic SQL SELECT statements and
options for processing a single table
By default, SQL Server sorts in
ascending order. If you need the sort in
descending order it would be:
ORDER BY Department DESC;
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries Sorting by Multiple Columns
Learn basic SQL SELECT statements and options for processing a
single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries SQL WHERE
Clause Options
Learn basic SQL SELECT statements and
options for processing a single table
• Three options for the SQL WHERE clauses are:
– compound clauses
– ranges
– wildcards
• An example of the compound clause is below:
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.16 SQL Logical Operators
SQL Logical Operators
Operator Meaning
AND Both arguments are TRUE
OR One or the other or both of the arguments are TRUE
NOT Negates the associated operator
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: SQL WHERE
Clauses Using Ranges of Values
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: SQL WHERE
Clauses Using Wildcards
Learn basic SQL SELECT statements and
options for processing a single table
When using an underscore it means that any character can occur
in the spot occupied by the underscore.
Notice below that an underscore is used for each space
needed.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: SQL WHERE
Clauses That Use NULL Values
Learn basic SQL SELECT statements and
options for processing a single table
To include or exclude rows that contain NULL values, we use the
IS NULL or IS NOT NULL comparison values.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: SQL Queries
That Perform Calculations
Learn basic SQL SELECT statements and
options for processing a single table
• It is possible to perform certain types of calculations in SQL
query statements.
– One group of calculations involves the use of SQL built-in
functions.
– Another group involves simple arithmetic operations on the
columns in the SELECT statement.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.17 SQL Built-in Aggregate
Functions
SQL Built-in Aggregate Functions
Function Meaning
COUNT(*) Count the number of rows in the table
COUNT Count the number of rows in the table where column {Name} IS
({Name}) NOT NULL
SUM Calculate the sum of all values (numeric columns only)
AVG Calculate the average of all values (numeric columns only)
MIN Calculate the minimum value of all values
MAX Calculate the maximum value of all values
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: Using SQL
Built-in Aggregate Functions
Learn basic SQL SELECT statements and
options for processing a single table
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: Using Multiple
Aggregate Functions in an SQL
Statement
Learn basic SQL SELECT statements and
options for processing a single table
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: Using
Expressions in SQL SELECT
Statements
Learn basic SQL SELECT statements and
options for processing a single table
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: Grouping Rows
Using SQL SELECT Statements
Learn basic SQL SELECT statements and
options for processing a single table
• In SQL, you can use the SQL GROUP BY clause to group rows
by common values.
– This is a powerful feature, but it can be difficult to
understand.
– What do we mean by a group? Consider the EMPLOYEE
table in Figure 3.18 on the next slide, where we show the
employees grouped by which department they work in.
– Because there are 9 departments, we have the employees
divided into 9 groups.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.18 Department Groups in
the EMPLOYEE Table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: Using the
GROUP BY Clause
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Single Table Queries: Using the GROUP
BY Clause with the HAVING Clause
Learn basic SQL SELECT statements and
options for processing a single table
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: SQL For
Data Manipulation (DML)
Learn basic SQL SELECT Statements for
processing multiple tables with subqueries
• The queries considered so far have involved data from a single
table. However, at times, more than one table must be
processed to obtain the desired information. SQL provides two
different techniques for querying data from multiple tables:
– the SQL Subquery
– the SQL Join
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: Querying
with Subqueries
Learn basic SQ L SELECT Statements for
processing multiple tables with subqueries
My SQ L Community Server 8.0, My SQ L Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.19 Using Primary Key and
Foreign Key Values in the SQL WHERE
Clause in an Implicit SQL JOIN
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: Querying
with Joins (1 of 2)
Learn basic SQL SELECT Statements for
processing multiple tables with Joins
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: Querying
with Joins (2 of 2)
Learn basic SQL SELECT Statements for
processing multiple tables with Joins
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: Using the
JOIN ON with the GROUP BY
Learn basic SQL SELECT Statements for
processing multiple tables with Joins
The JOIN ON syntax still requires a statement of primary
key to foreign key as shown below:
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.22 Using Primary Key and
Foreign Key Values in the SQL ON
Clause in an Explicit SQL Join
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: Comparing
Subqueries and Joins
Learn basic SQL SELECT Statements for
processing multiple tables with Joins
• Subqueries and joins both process multiple tables, but
they differ slightly.
– A subquery can only be used to retrieve data from the
top table
– A join can be used to obtain data from any number of
tables.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Multiple Table Queries: SQL Inner
Joins
Learn basic SQL SELECT Statements for
processing multiple tables with Joins
• SQL Inner Join is also referred to as an SQL equijoin.
• An Inner Join only displays data from the rows that match
based on join conditions:
– if a row has a value that does not match the WHERE
clause condition, that row will not be included in the
join result
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Figure 3.25 Types of SQL JOINS
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
SQL for Data Manipulation (DML)–
Data Modification and Deletion
Learn basic SQL for modifying and deleting
data from a database
• The SQL DML contains commands for the three possible
data modifications operations:
– Insert
– Modify
– Delete
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Modifying Data Example 1
Learn basic SQL for modifying and deleting
data from a database
To see the results, we use the following command:
MySQL Community Server 8.0, MySQL Workbench, Oracle Corporation.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Modifying Data Example 2
Learn basic SQL for modifying and deleting
data from a database
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Deleting Data
Learn basic SQL for modifying and deleting
data from a database
The example above is a valid statement, but note that if you
fail to include the WHERE clause then you will have just
deleted all records in the table.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
SQL For Data Definition (DDL) –
Table and Constraint Modification
and Deletion
Learn basic SQL statements for modifying and
deleting database tables and constraints
• Two of the most useful data definition SQL statements
are:
– the SQL DROP TABLE statement
– the SQL ALTER TABLE statement
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
The SQL DROP TABLE Statement
Learn basic SQL statements for modifying and
deleting database tables and constraints
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
The SQL ALTER TABLE Statement
Learn basic SQL statements for modifying and
deleting database tables and constraints
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
Copyright
This work is protected by United States copyright laws and is
provided solely for the use of instructors in teaching their
courses and assessing student learning. Dissemination or sale of
any part of this work (including on the World Wide Web) will
destroy the integrity of the work and is not permitted. The work
and materials from it should never be made available to students
except by instructors using the accompanying text in their
classes. All recipients of this work are expected to abide by these
restrictions and to honor the intended pedagogical purposes and
the needs of other instructors who rely on these materials.
Copyright © 2020, 2017, 2015 Pearson Education, Inc. All Rights Reserved
