ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 1
| Copyright © 2015 – 2018, Victorian Institute of Technology. The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT, other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology. INSERT, UPDATE AND DELETE STATEMENT Total Slides: 35 |
Page: 1
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 2
| Topics |
• INSERT INTO Statement
• Update Statement
• Delete Statement
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 3
| INSERT I | NTO Statement |
• The INSERT INTO statement is used to insert new records
in a table.
• It is possible to write the INSERT INTO statement in two
ways.
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 4
| INSERT I | NTO Statement (Cont…) |
• The first way specifies both the column names and the
values to be inserted.
– Syntax
• INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
– Example (Please use northwind database)
• INSERT INTO Customers (CustomerID, CompanyName,
ContactName, ContactTitle, Address, City, PostalCode, Country)
VALUES (‘AGL’, ‘Engergy Ltd.’, ‘Suzan’, ‘Owner’, ‘Flinders Street’,
‘Melbourne’, ‘VIC 3000’, ‘Australia’);
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 5
| INSERT I | NTO Statement (Cont…) |
• Second way is, if you are adding values for all the columns
of the table, you do not need to specify the column names in
the SQL query. However, make sure the order of the values
is in the same order as the columns in the table.
– Syntax
• INSERT INTO table_name
VALUES (value1, value2, value3, …);
– Example
• INSERT INTO Customers VALUES (‘Vphn’, ‘Vodafone’, ‘Michael’,
‘Owner’, ‘Flinders Street’, ‘Melbourne’, ‘Melbourne CBD’, ‘VIC
3000′, ‘Australia’, ‘0454256954’, ‘0398523541’);
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 6
| INSERT I | NTO Select Statement |
• Statement copies data from one table and inserts it into
another table.
• It requires that data types in source and target tables match.
• The existing records in the target table are unaffected.
• We can copy columns value from one table to another in
two ways
– Copy all columns from one table to another table.
– Copy only some columns from one table into another table.
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 7
| INSERT I | NTO Select Statement (Cont…) |
• Copy all columns from one table to another table.
• Syntax
– INSERT INTO table2
SELECT * FROM table1
WHERE condition;
• Example
• (use firstDB database and create a new table (PassportVic)
having same designing structure as it is in Passport table)
– INSERT INTO PassportVic
SELECT * FROM Passport
WHERE Province=‘VIC’;
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 8
| INSERT I | NTO Select Statement (Cont…) |
• Copy only some columns from one table into another table.
• Syntax
– INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;
• Example
• (use firstDB database and create a new table
(PassportCopy) having few columns (ID, InitialName,
Surname, Age) with same designing structure as it is in
Passport table for that respective columns.)
– INSERT INTO PassportCopy (ID, InitialName, Surname, Age)
SELECT PassportID, FirstName, LastName, Age FROM Passport;
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 9
| Update S | tatement |
• The UPDATE statement is used to modify the existing
records in a table.
• Syntax
– UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
• The following SQL statement updates the ContactName and
Address of record having CustomerID as ‘AGL’.
• Example (Updates single record)
– UPDATE Customers
SET ContactName = ‘Imran’, Address= ‘Queen Street’
WHERE CustomerID = ‘AGL’;
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 10
| Update S | tatement (Cont…) |
• It is the WHERE clause that determines how many records
that will be updated.
• The following SQL statement will update the City to
“Sydney” for all records where country is “Australia“:
• Example (Updates multiple records)
– UPDATE Customers
SET City = ‘Sydney’
WHERE Country = ‘Australia’;
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 11
| Update S | tatement (Cont…) |
• Be careful when updating records. If you omit the WHERE
clause, ALL records will be updated!.
• Example (Updates all records)
– UPDATE Customers
SET City = ‘Sydney’;
Note: Don’t try above example, it is just for explanation.
Don’t use Update statement without where unless it is required.
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 12
| Delete St | atement |
• The DELETE statement is used to delete existing records in
a table.
• Syntax
– DELETE FROM table_name
WHERE condition;
• Below example will delete a record having CustomerID as
“AGL”.
• Example
– DELETE FROM Customers
WHERE CustomerID=’AGL’;
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 13
| Delete St | atement (Cont…) |
• It is possible to delete all rows in a table without deleting the
table. This means that the table structure, attributes, and
indexes will be intact:
• Syntax
– DELETE FROM table_name;
or
– DELETE * FROM table_name;
• Example
– DELETE FROM Customers
Note: Don’t try above example, it is just for explanation.
Don’t use Delete statement without where unless it is required
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 14
| Copyright © 2015 – 2018, Victorian Institute of Technology. The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT, other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology. SQL VIEWS Total Slides: 35 |
Page: 14
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 15
| Topics |
• What Are User Views?
• Characteristics of User Views
• SQL for User Views
• Tables Used in Demonstrating Views Examples in Following
Slides
• Mechanics of Views
• Types of Userviews
• Why User Views
• User View Design Considerations
• View Designed for Access Control
• View designed for Ease of Query
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 16
| What Are | User Views? |
• A view is a “Virtual Table”
• Tables versus Views :
– Tables
• Store actual rows of data
• Occupies a particular amount of storage space
– User Views
• Derived or virtual tables that are visible to users
• Do not occupies any storage space
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 17
| Characte | ristics of User Views |
• Behave as if it contains actual rows of data, but in fact
contains none.
• Rows are derived from base table or tables from which the
view is defined.
• Like base tables, views can be queried, updated, inserted
into and deleted from, with a number of restrictions
• Integrity constraints cannot be defined explicitly for views
• Usually designed for end users thus providing natural
interfaces
• A view always shows up-to-date data. The database engine
recreates the data, using the view’s SQL statement, every
time a user queries a view.
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 18
| SQL for | ser Views |
• Syntax
– CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 19
Tables Used in Demonstrating Views
Examples in Following Slides
CREATE TABLE Movies (
VideoCode int NOT NULL,
MovieTitle varchar(255) NOT NULL,
MovieType varchar(255),
Rating int NOT NULL,
Producer varchar(255) NOT NULL,
RentalPrice int NOT NULL,
PRIMARY KEY (VideoCode)
);
CREATE TABLE Producers (
Producer int NOT NULL,
ProducerName varchar(255) NOT NULL,
CountryCode varchar(255),
PRIMARY KEY (Producer)
);
CREATE TABLE ProducerWebsite (
Producer int NOT NULL
FOREIGN KEY REFERENCES
Producers(Producer),
Website varchar(255) NOT NULL,
);
CREATE TABLE Customers (
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CountryCode varchar(255) NOT NULL,
MemberCategory varchar(255) NOT NULL,
Address varchar(255) NOT NULL,
PRIMARY KEY (CustomerID)
);
CREATE TABLE IssueTran (
TransactionID int NOT NULL,
RentalStatus varchar(255) NOT NULL,
VideoCode int NOT NULL FOREIGN KEY
REFERENCES Movies(VideoCode),
DateIssue datetime NOT NULL,
CustomerID int NOT NULL FOREIGN KEY
REFERENCES Customers(CustomerID),
PRIMARY KEY (TransactionID)
);
• Create following tables using below statements
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 20
| Mechani | s of Views |
• A view’s definition is represented by storing the text of query
(that defines the view) in the data dictionary.
– For example, if the View is created as follows:
• CREATE VIEW Nov20TranView AS
SELECT TransactionID, CustomerID, VideoCode
FROM IssueTran
WHERE DateIssue = ’20 Nov 2000′
– Now consider the following user-issued query to extract records from
view using where clause:
• SELECT * FROM Nov20TranView
WHERE VideoCode = 55
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 21
| Types of | Userviews |
• Selection (Specific Rows):
– Restrict access to only selected rows of a source table
Example: CREATE VIEW Nov30TranView AS
SELECT *
FROM IssueTran
WHERE DateIssue = ‘30 Nov 2000′
• Projection (Specific Columns):
– Restrict view to only certain columns of a table
• CREATE VIEW Nov10TranView AS
SELECT TransactionID, CustomerID, VideoCode
FROM IssueTran
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 22
| Types of | User Views (Cont…) |
• Specific Rows and Columns
– Subset view of information derived with both horizontal and vertical
dimensions
CREATE VIEW Nov5TranView AS
SELECT TransactionID,CustomerID,VideoCode
FROM IssueTran
WHERE DateIssue = ‘5 Nov 2000’
• Aggregated Views
– Summarized views are created using GROUP BY clause
CREATE VIEW TranForNovView AS
SELECT COUNT(TransactionID) AS NumberOfTransaction, DateIssue
FROM IssueTran
GROUP BY DateIssue
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 23
| Types of | Userviews (Cont…) |
• Views from Joined Tables
– Create views that join Multiple tables
• CREATE VIEW ColumbiaMoviesView AS
SELECT VideoCode, MovieTitle FROM Movies,Producers
WHERE Producers.Producer = Movies.Producer
AND ProducerName = ‘Columbia Pictures’
| Movies | Producers |
| VideoCode, MovieTitle, MovieType, Producer, ….. |
Producer, ProducerName CountryCode …. |
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 24
| Types of | User Views (Cont…) |
• Views from Joined Tables
– Create views that join Multiple tables
• CREATE VIEW MoviesProducerView AS
SELECT MovieTitle, ProducerName, WebSite
FROM Movies, Producers, ProducerWebSite
WHERE Producers.Producer = Movies.Producer
AND Producers.Producer = ProducerWebSite.Producer
| Movies |
| VideoCode, MovieTitle, MovieType, Producer, ….. |
| Producers |
| Producer, ProducerName CountryCode …. |
| ProducerWebSite |
| Producer , WebSite |
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 25
| Why Use | r Views |
• Benefits of User Views includes:
– Security
• Protect data from unauthorized access. Each user is given
permission to access the database via only a small set of views
that contain specific data the user is authorized to see.
– Query Simplicity
• Turning multiple table queries to single table queries, by drawing
data from several tables. It provides flexible and powerful data
access capabilities. It also improves productivity of end-user and
programmers by:
– Simplifying database access by presenting the structure of data that
is most natural to the user.
– Simplifying the use of routine and repetitive statements.
– Building up SELECT statements in several steps.
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 26
| Why Use | r Views (Cont…) |
– Natural Interface
• “Personalized” view of database structure, that make sense for
the user. Restructure or tailor the way in which tables are seen,
so that different users see it from different perspectives, thus
allowing more natural views of the same enterprise (e.g. item
names)
– Insulation from change
• Data independence – maintain independence among different
user views and between each user view and the physical
constructs
• A view can present a consistent image of the database structure,
even if the underlying source tables are restructured.
– Data Integrity
• DBMS can check data to ensure that it meets specified integrity
constraints.
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 27
| User Vie | Design Considerations |
• User View design is usually driven by specific application
requirements
– User View may be defined for individual user, or a group of users, of
the transaction or application
– User View may be defined to control and restrict access to specific
columns and/or rows in one or more tables
– User View can be defined to help simplify queries, application
development and maintenance
– User View may be derived from base tables or other userviews
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 28
| User Vie | Design Considerations (Cont…) |
USERVIEW
DESIGN
Userviews
Access Control
Requirements
Application/
Transaction
Requirements
Physical Database
Structure
User Groups’
Requirements
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 29
| View Des | igned for Access Control |
• Views to restrict access to database tables Movies.
– Users will not be given permission to use the actual table (i.e Movies) for query,
instead they are allowed only to use the UniversalMovies View.
User View Definition
CREATE VIEW UniversalMovies AS
SELECT * FROM Movies
WHERE Producer = ‘Universal’
To access the user View
SELECT * FROM UniversalMovies
WHERE Rating = 2
VideoCode MovieTitle MovieType Rating … Producer
| 1 | Star Trek 3: Search for Spock | Sci-fi | PG | Warner |
| 2 | Star Trek 4: The Voyage Home | Sci-fi | PG | Universal |
| 3 | Star Trek 5: The Final Frontier | Sci-fi | PG | Pixar |
| 4 | Demolition Man | Action | R | Universal |
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 30
View Designed for Different User Groups
(Cont…)
• Views facilitating Ease of Query for different User Group
| Base Table: Columns: |
Customers CustomerID, CustomerName, MemberCategory, Address,….. |
CREATE VIEW SingaporeCustomers AS
SELECT CustomerID,CustomerName,CountryCode
FROM Customers
WHERE CountryCode = ‘SIN’
SELECT *
FROM SingaporeCustomers
User View Definition To access the user View
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 31
View Designed for Different User Groups
(Cont…)
• Views facilitating Ease of Query for different User Group
| Base Table: Columns: |
Customers CustomerID, CustomerName, MemberCategory, Address,….. |
CREATE VIEW AsiaCustomers AS
SELECT CustomerID,CustomerName,CountryCode
FROM Customers
WHERE CountryCode IN (‘SIN’,’MAL’,’JAP’)
SELECT *
FROM AsiaCustomers
User View Definition To access the user View
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 32
| View des | igned for Ease of Query |
• Views for Unit Conversion:
Base Table: Movie
Columns: VideoCode, RentalPrice
| , … | |
| o access | User View |
Note: Prices are captured in US Dollars
CREATE VIEW MoviesAustralia
(VideoCode, AusPrice) AS
SELECT VideoCode, RentalPrice * 1.30
FROM Movies
SELECT *
FROM MoviesAustralia
WHERE VideoCode = 5
Base Table: Movie
VideoCode RentalPrice
1 1.50
2 2.00
3 2.50
4 1.00
| VideoCode 1 2 3 4 |
AusPrice 1.95 2.60 3.25 1.30 |
User View Definition View MoviesAustralia
ITDA1001 [Lesson 10] Copyright © 2018 VIT, All Rights Reserved 33
| Summar |
• Revision of Key Concepts
• Lab Work
– Lesson 10 – Activity 10 (Lesson_10 Activity.docx)
• Questions and Answer
The post ITDA1001 INSERT, UPDATE AND DELETE STATEMENT appeared first on My Assignment Online.