Transcript SQL Queries - data maintenance

SQL queries
Data Maintenance
Data maintenance
• In addition to asking question to the
database (queries), we can also maintain
the data itself (insert, update, delete)
• More of a ”command” than a query…
– INSERT – adds new rows of data
– UPDATE – modifies existing data
– DELETE – removes rows of existing data
RHS – SOC
2
INSERT
• Simple functionality – insert a row of data
into a specified table:
INSERT INTO <tableName>
VALUES (<valuelist>)
RHS – SOC
3
INSERT
• Example:
INSERT INTO Movie
VALUES (8, ’Memento’, ’USA’, 2000,
’Thriller’, 0)
RHS – SOC
4
INSERT
• Things to notice about INSERT
– The value list must match the field list for the
table into which the record is inserted
– If we try to insert a record with a key field
which already exists, we will get an error
– Null values can be inserted if the table
definition allows it
– The field list can be specified explicitly (see
notes)
RHS – SOC
5
UPDATE
• Updates the value(s) for specified field(s),
for the rows mathcing a given condition
UPDATE <tableName>
SET field1 = value1, field2 = value2,…
WHERE <condition>
RHS – SOC
6
UPDATE
• Example:
UPDATE Movie
SET prodYear = 2001
WHERE (title = ’Memento’)
RHS – SOC
7
UPDATE
• Things to notice about UPDATE
– For each field update, the type of the value
must match the type of the field
– The WHERE clause is optional – if you leave
it out, all records in the table are updated!
– It is not considered an error if zero rows are
changed, so pay attention to the condition in
the WHERE clause…
RHS – SOC
8
DELETE
• Deletes all the rows mathcing a given
condition :
DELETE FROM <tableName>
WHERE <condition>
RHS – SOC
9
DELETE
• Example:
DELETE FROM Movie
WHERE (title = ’Memento’)
RHS – SOC
10
DELETE
• Things to notice about DELETE
– No need to specify a field list
– The WHERE clause is optional – if you leave
it out, all records in the table are deleted!
– It is not considered an error if zero rows are
deleted, so pay attention to the condition in
the WHERE clause
– Double-check your WHERE clause 
RHS – SOC
11
Exercise 8 – SQL queries
•
•
•
Use the MovieInformation database, defined in exercise 1
Before starting, take a backup of the MovieInformation database…
With the data in place, run the below commands on the database
–
–
–
–
•
INSERT INTO Actor VALUES (8, ’John Hurt’, ’UK’, '01/22/1940', ’Yes’, 0)
INSERT INTO Casting VALUES (6,8)
UPDATE Movie SET prodYear = prodYear + 1
DELETE FROM Casting WHERE (actorId = 8)
Now formulate commands yourself, in order to:
– Insert data about a movie called ”Willow” in the Movie table (you can find the
data on the Internet, or make it up yourself)
– Insert data representing the fact that Jean Reno appeared in Willow
– Update the name of the movie ”Seven” to ”Se7en”
– Update the number of Oscars won by actors from France by one
– Delete all movies with the character ’7’ in the title (watch the condition!!)
RHS – SOC
12
Creating tables
• It is even possible to create new tables
using SQL
• Possible, but we will usually do it through
the DBMS GUI anyway…
• Complete syntax for creating a new table
is rather complicated
RHS – SOC
13
Creating tables
• Creating a table (simplified):
CREATE TABLE Director
(<field definitions>
PRIMARY KEY <field list>)
RHS – SOC
14
Creating tables
• Each field definition contains
– The field name
– The data type for the field
– Can the field be NULL or not
– Must the field be unique
– Does the field have a default value
RHS – SOC
15
Creating tables
• Example (simplified):
CREATE TABLE Director
( directorId numeric(3) NOT NULL,
name
varchar(30) NOT NULL,
country
varchar(20) NOT NULL,
born
date
NOT NULL,
PRIMARY KEY (directorId))
RHS – SOC
16
Exercise 9 – SQL queries
•
•
Use the MovieInformation database, defined in exercise 1
Create a new table in the MovieInformation database called MovieStudio,
with the following fields:
–
–
–
–
–
–
–
–
•
MovieStudio identifier
Name of the studio
Founder of the studio
Year the studio was founded
Number of employees
City of studio headquarter
Country of studio headquarter
Is the studio still producing movies
Consider how the rest of the database should be changed, in order to
include information about movie studios
RHS – SOC
17