Chapter 5 - PowerPoint PPT Presentation

chapter 5 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 5 PowerPoint Presentation
play fullscreen
1 / 16
Chapter 5
182 Views
Download Presentation
nuru
Download Presentation

Chapter 5

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Chapter 5 Structured Query Language (SQL1) Revision

  2. In this lecture, you will learn: • Data definition commands • Data manipulation commands

  3. ( Defining database, table structures, table relationship) Data Definition Commands

  4. Data Definition Commands • Create database structure • Holds all tables and is a collection of physical files stored on disk • DBMS automatically creates tables to store metadata • CREATE DATABASE <databasename> • Example:CREATE DATABASE TDB2111

  5. Creating Table Structure • Tables store end-user data • May be based on data dictionary entries CREATE TABLE <table name>(<attribute1 name and attribute1 type,attribute2 name and attribute2 type,attribute3 name and attribute3 type,primary key designation,foreign key designation and foreign key requirement>);

  6. Data Type • Data Types include:CHAR, INTEGER, VARCHAR, DECIMAL, DATE(see page 110) • Some Examples:CHAR(25), INTEGER, SMALLINT, DECIMAL(9,2), DATE

  7. SQL Integrity Constraints • Adherence to entity integrity and referential integrity rules is crucial • Entity integrity enforced automatically if primary key specified in CREATE TABLE command sequence • Referential integrity can be enforced in specification of FOREIGN KEY • Other specifications to ensure conditions met: • ON DELETE RESTRICT • ON UPDATE CASCADE

  8. Data Manipulation Commands (retrieve, add, delete, update data)

  9. Data Manipulation Commands Common SQL Commands Table 5.3

  10. Listing Table Contents and Other Commands • INSERT command makes data entry • UPDATE command makes data entry corrections • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used • DELETE command removes table row • Allows table contents to be listed SELECT <attribute names> FROM <table names>;

  11. Data Entry (INSERT) INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); • Enters data into a table • INSERT INTO BOOK VALUES (‘0180’,’Shyness’,’PSY’,7.65 ); • values of attributes must be listed in the correct order/sequence • values of character attributes are enclosed in ‘ ‘

  12. Commit & Rollback • Saves changes to disk COMMIT <table names> ; COMMIT Book; if you know something has gone wrongROLLBACK; takes you back to the last COMMIT

  13. Update (modify data) • UPDATE BOOK SET Book_Type=‘MYS’, Book_Price=9.95 WHERE Book_Code=‘0180’;can set the value of more that one attribute in a single UPDATE statement by separating the list of attributes and their values with commas

  14. Delete (Remove data) • DELETE FROM BOOKWHERE Book_Code=‘0180’;the WHERE clause does not have to be written in terms of the primary keyDELETE FROM BOOKWHERE Book_Type=‘PSY’; would delete all the psy books • Note:DELETE FROM BOOKWHERE Pub_Code EXISTS; deletes all tuples where there is a non-null Pub_Code

  15. SELECT (retrieve certain/all data) • SELECT * FROM BOOK; • SELECT Book_Code, Book_Title FROM BOOK; • SELECT * FROM BOOK WHERE Book_Code=‘0181’; • SELECT Book_Code, Pub_Name FROM BOOK, PUBLISHER WHERE BOOK.Pub_Code = PUBLISHER.PubCode