1 / 16

Chapter 5

Chapter 5. Structured Query Language (SQL1) Revision. In this lecture, you will learn:. Data definition commands Data manipulation commands. ( Defining database, table structures, table relationship). Data Definition Commands. Data Definition Commands. Create database structure

nuru
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related