1 / 120

Chapter 5

Chapter 5. Structured Query Language (SQL). In this chapter, you will learn:. The basic commands and functions of SQL How SQL is used for data manipulation (to add, modify, delete, and retrieve data) How to use SQL to query a database to extract useful information.

sumana
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 (SQL)

  2. In this chapter, you will learn: • The basic commands and functions of SQL • How SQL is used for data manipulation (to add, modify, delete, and retrieve data) • How to use SQL to query a database to extract useful information

  3. In this chapter, you will learn: • How SQL is used for data administration (to create tables, indexes, and views) • About more advanced SQL features such as updatable views, stored procedures, and triggers

  4. Introduction to SQL • Ideal database language • Create database and table structures • Perform basic data management chores (add, delete, and modify) • Perform complex queries to transform data into useful information

  5. Introduction to SQL • SQL is the ideal DB language • Data definition language (create tables and define access right to tables) • Data manipulation language (update and manipulate data within database tables)

  6. Good Reasons to Study SQL • Easy to learn • Portable to many database specification, only minor differences among various SQLs defined in Oracle, SQL server, DB2, Informix, Access, my SQL, …

  7. Good Reasons to Study SQL • ANSI standardization effort led to de facto query standard for relational database (SQL-99 or SQL3) • Forms basis for present and future DBMS integration efforts • Becomes catalyst in development of distributed databases and database client/server architecture

  8. Data Definition Commands • Database model • PRODUCT and VENDOR

  9. 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 • Schema: a group of database objects (tables, indexes and relationships) • Database administrator creates structure or schema • Logical group of tables or logical database • Groups tables by owner • Enforces security

  10. Data Definition Commands • ANSI standard to create database schema • CREATE SCHEMA AUTHORIZATION <creator> • Example:CREATE SCHEMA AUTHORIZATION JONES

  11. Data Dictionary Table

  12. Creating Table Structure • Data Types • Number: • NUMBER(L,D) • INTEGER • SMALLINT • DECIMAL(L,D) • Character: • CHAR(L) • VARCHAR(L)

  13. Creating Table Structure • Data Types • Date • DATE • Others • TIME • TIME-STAMP • REAL • DOUBLE • FLOAT • ODL • …

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

  15. Example: CREATE TABLE VENDOR ( V_CODE INTEGER NOT NULL UNIQUE V_NAME VARCHAR(35) NOT NULL V_CONTACT VARCHAR(15) NOT NULL V_AREACODE CHAR(3) NOT NULL V_PHONE CHAR(8) NOT NULL V_STATE CHAT(2) NOT NULL V_ORDER CHAR(1) NOT NULL PRIMARY KEY(V_CODE) );

  16. Example: CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) NOT NULL UNIQUE P_DESCRIPT VARCHAR(35) NOT NULL P_INDATE DATE NOT NULL P_ONHAND SMALLINT NOT NULL P_MIN SMALLINT NOT NULL P_PRICE NUMBER(8,2) NOT NULL P_DISCOUNT NUMBER(5,2) NOT NULL V_CODE INTEGER PRIMARY KEY(P_CODE) FOREIGN KEY(V_CODE) REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE); Can not delete vendor table You can change vendor’s code

  17. Using Domains • Domain is set of permissible values for a column • Definition requires: • Name • Data type • Default value • Domain constraint or condition CREATE DOMAIN <domain_name> AS DATA_TYPE[DEFAULT <default_value>] [CHECK (<condition>)]

  18. Using Domains • Example: • You can use domain name instead of attribute’s data type, when you define a new table CREATE DOMAIN MARITAL_STATUS AS VARCHAR(8) AS DATA_TYPECHECK (VALUE IN (‘Single’, ‘Married’, ‘Divorced’, ‘Widowed’)); CREATE TABLE EMPLOYEE ( EMP_NUM INTEGER NOT NULL CONSTRAIN EMPKRY KEY (EMP_NUM), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_STATUS MARITAL_STATUS NOT NULL);

  19. Using Domains • Draw domain values from other tables by including a SELECT statement int eh CHECK clause CREATE DOMAIN DISCOUNTING_RATES AS NUMBER(5,2) CHECK (VALUE IN (SELECT DISCOUNT FROM DISCOUNTBL); DISCOUNT attributes in DISCOUNTBL table

  20. Using Domains • Delete domain • Not all database support create domain like Oracle Prevent deleting until no attributes based on the domain DROP DOMAIN <domain_name> [RESTRICT |CASCADE] DROP DOMAIN MARITAL_STATUS CASCADE; Change data type to the one defined In domain’s definition

  21. 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

  22. CREATE TABLE PRODUCT ( P_CODE VARCHAR(10) NOT NULL UNIQUE P_DESCRIPT VARCHAR(35) NOT NULL P_INDATE DATE NOT NULL P_ONHAND SMALLINT NOT NULL P_MIN SMALLINT NOT NULL P_PRICE NUMBER(8,2) NOT NULL P_DISCOUNT NUMBER(5,2) NOT NULL V_CODE INTEGER PRIMARY KEY(P_CODE) FOREIGN KEY(V_CODE) REFERENCES VENDOR ON DELETE RESTRICT ON UPDATE CASCADE); • Some database does this automatically without explicit specifications

  23. Data Manipulation Commands Common SQL Commands Table 5.3

  24. Data Entry and Saving • Enters data into a table INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.); INSERT INTO VENDOR VALUES (21225, ‘my company’, ‘jun ni’, ‘319’,’232-2194’,’IA’,’Y’);

  25. Data Entry and Saving • Use form (GUI) to enter data

  26. Data Entry and Saving • Saves changes to disk COMMIT <table names> ;

  27. Listing Table Contents and Other Commands • Allows table contents to be listed SELECT <attribute names> FROM <table names>; SELECT * FROM PRODUCT; SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONANG, P_MIN, P_PRICE, P_DISCOUNT, V_CODE FROM PRODUCT;

  28. Listing Table Contents and Other Commands • UPDATE command makes data entry corrections UPDATE PRODUCT SET P_INDATE=’01/18/2003’ WHERE P_CODE=’13-Q2/P2’

  29. Listing Table Contents and Other Commands • ROLLBACK command restores database back to previous condition if COMMIT hasn’t been used ROLLBACK;

  30. Listing Table Contents and Other Commands • DELETE command removes table row DELETE FROM PRODUCT WHERE P_CODE=‘2238/QPD’; DELETE FROM PRODUCT WHERE P_MIN=5; 5 ROWS ARE DELETED

  31. Queries • Creating partial listings of table contents SELECT <column(s)>FROM <table name>WHERE <conditions>; Table 5.4 Mathematical Operators

  32. Examples • Mathematical operators SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;

  33. Examples • Mathematical operators SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344; Not equal

  34. Examples • Mathematical operators SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE FROM PRODUCT WHERE P_PRICE<=10;

  35. Examples • Mathematical operators on character attributes • Mathematical operators on dates SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’; SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHERE P_INDATE >= ‘01/20/2002’;

  36. Computed Columns • New columns can be created through valid computations or formulas • Formulas may contain mathematical operators • May contain attributes of any tables specified in FROM clause • Alias is alternate name given to table or column in SQL statement SELECT P_DESCRIPT,P_ONHAND,P_PRICE,P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT;

  37. Operators • Logical: AND, OR, NOT SELECT * FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288;

  38. Operators • Rules of precedence • Conditions within parenthesis executed first • Boolean algebra SELECT * P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCTWHERE (P_PRICE<50 AND P_INDATE>’01/05/2002’) OR V_CODE=24288;

  39. Special Operators • BETWEEN - defines limits • IS NULL - checks for nulls SELECT * FROM PRODUCT WHERE P_PRICE BETWEEN 500.00 AND 100.00; SELECT P_CODE, P_DESCRIPT FROM PRODUCT WHERE P_MIN IS NULL;

  40. Special Operators • LIKE - checks for similar string (wildcard) SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM VENDOR WHERE V_CONTACT LIKE ‘Smith%’;

  41. Special Operators • IN - checks for value in a set SELECT * FROM PRODUCT WHERE V_CODE=21344 OR V_CODE=24288; SELECT * FROM PRODUCT WHERE V_CODE IN (21344,24288); MORE EFFICIENTLY

More Related