Transcript Chapter 7

Chapter 7
SQL for Database Construction
and
Application Processing
First create the database
New types of SQL statements
SQL vs Graphical tools
• Quicker
• Facilitates recreation of same tables
needed by applications
• Some applications need you to create
temporary tables which require SQL code
• SQL is standardized and DBMS
independent
– Except for some data types
Tables from View Ridge Database
Design of Chapter 6
SQL code to create
Data Type Reminder – SQL Server
Data Type Reminder – Oracle
Let’s try to create the WORK table
Table creation and deletion order
• Create parents before children
• Delete children before parents
Relationship Definitions
What would the default values &
data constraints be for View Ridge?
Table
Name
Column
Name
WORK
title
copy
Default Value
Constraint
original
ARTIST
DeceasedDate
DeceasedDate must
follow BirthDate
ARTIST
ArtistID
Can’t be null
ARTIST
Name
Name must be unique
ARTIST
Nationality
ARTIST
Birthdate
Angola
birthDate has to
precede current date;
birthdate must precede
deceasedDate
CHECK CONSTRAINTS
•
•
•
•
Similar to SQL WHERE
IN – provides list of valid values
NOT IN – for negatively expressed
constraints
LIKE – used for specification of decimal
values
Results
ALTER statements
• ALTER statement changes table
structure, properties, or constraints
after it has been created
Examples
•
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT EmployeeFK FOREIGN KEY
(EmployeeNum)
REFERENCES EMPLOYEE
(EmployeeNumber)
ON UPDATE CASCADE
ON DELETE NO ACTION;
• ALTER TABLE CUSTOMER ADD MyColumn Char(5)
NULL;
• ALTER TABLE CUSTOMER DROP COLUMN MyColumn;
More ALTER examples
• ALTER TABLE CUSTOMER ADD CONSTRAINT
MyConstraint CHECK ([Name] NOT IN
('Robert No Pay'));
• ALTER TABLE CUSTOMER DROP CONSTRAINT
MyConstraint;
• DROP TABLE [TRANSACTION];
More ALTER examples
• ALTER TABLE CUSTOMER_ARTIST_INT
DROP CONSTRAINT
Customer_Artist_Int_CustomerFK;
• ALTER TABLE [TRANSACTION]
DROP CONSTRAINT
TransactionCustomerFK;
• DROP TABLE CUSTOMER;
INSERT examples
•
INSERT INTO ARTIST ([Name],
Nationality, Birthdate,
DeceasedDate)
VALUES ('Tamayo', 'Mexican', 1927, 1998);
•
INSERT INTO ARTIST ([Name],
Nationality, Birthdate)
SELECT [Name], Nationality, Birthdate
FROM IMPORTED_ARTIST;