Chapter 7 SQL for Database Construction and Application

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 Deceased. Date must follow Birth. Date ARTIST Artist. ID Can’t be null ARTIST Name must be unique ARTIST Nationality ARTIST Birthdate Angola birth. Date has to precede current date; birthdate must precede deceased. Date

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 Employee. FK FOREIGN KEY (Employee. Num) REFERENCES EMPLOYEE (Employee. Number) ON UPDATE CASCADE ON DELETE NO ACTION; • ALTER TABLE CUSTOMER ADD My. Column Char(5) NULL; • ALTER TABLE CUSTOMER DROP COLUMN My. Column;

More ALTER examples • ALTER TABLE CUSTOMER ADD CONSTRAINT My. Constraint CHECK ([Name] NOT IN ('Robert No Pay')); • ALTER TABLE CUSTOMER DROP CONSTRAINT My. Constraint; • DROP TABLE [TRANSACTION];

More ALTER examples • ALTER TABLE CUSTOMER_ARTIST_INT DROP CONSTRAINT Customer_Artist_Int_Customer. FK; • ALTER TABLE [TRANSACTION] DROP CONSTRAINT Transaction. Customer. FK; • DROP TABLE CUSTOMER;

INSERT examples • INSERT INTO ARTIST ([Name], Nationality, Birthdate, Deceased. Date) VALUES ('Tamayo', 'Mexican', 1927, 1998); • INSERT INTO ARTIST ([Name], Nationality, Birthdate) SELECT [Name], Nationality, Birthdate FROM IMPORTED_ARTIST;