1 / 21

Chapter # 7 Introduction to Structured Query Language (SQL)

Chapter # 7 Introduction to Structured Query Language (SQL). Part I. SQL functions fit into two broad categories: Data definition language Data manipulation language Basic command set has vocabulary of less than 100 words

amberp
Download Presentation

Chapter # 7 Introduction to Structured Query Language (SQL)

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 # 7 Introduction to Structured Query Language (SQL) Part I

  2. SQL functions fit into two broad categories: Data definition language Data manipulation language Basic command set has vocabulary of less than 100 words American National Standards Institute (ANSI) prescribes a standard SQL Several SQL dialects exist Introduction to SQL

  3. SQL Family SQL (Structured Query Language)

  4. The database model In this chapter, a simple database with these tables is used to illustrate commands: CUSTOMER INVOICE LINE PRODUCT VENDOR Focus on PRODUCT and VENDOR tables Data Definition Commands

  5. Data Definition Commands (2)

  6. Two tasks must be completed: Create database structure Create tables that will hold end-user data First task: RDBMS creates physical files that will hold database Differs substantially from one RDBMS to another Creating the Database CREATE DATABASE <DATABASENAME>; CREATE TABLE states ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR(25), population INT(9) );

  7. Authentication DBMS verifies that only registered users are able to access database Log on to RDBMS using user ID and password created by database administrator CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY'password'; GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; Schema Group of database objects that are related to each other The Database Schema

  8. Data type selection is usually dictated by nature of data and by intended use Supported data types: Number(L,D), Integer, Smallint, Decimal(L,D) Char(L), Varchar(L), Varchar2(L) Date, Time, Timestamp Real, Double, Float Interval day to hour Many other types Data Types

  9. Use one line per column (attribute) definition Use spaces to line up attribute characteristics and constraints Table and attribute names are capitalized NOT NULL specification UNIQUE specification Primary key attributes contain both a NOT NULL and a UNIQUE specification RDBMS will automatically enforce referential integrity for foreign keys Command sequence ends with semicolon Creating Table Structures

  10. Creating Table Structures (Example)

  11. NOT NULL constraint Ensures that column does not accept nulls UNIQUE constraint Ensures that all values in column are unique DEFAULT constraint Assigns value to attribute when a new row is added to table. The end user may, of course, enter a value other than the default value. CHECK constraint Validates data when attribute value is entered SQL Constraints

  12. In this chapter, Oracle is used to illustrate SQL constraints. For example, note that the following SQL command sequence uses the DEFAULT and CHECK constraints to define the table named CUSTOMER. SQL Constraints

  13. When primary key is declared, DBMS automatically creates unique index Often need additional indexes Using CREATE INDEX command, SQL indexes can be created on basis of any selected attribute Using the CREATE INDEX command, SQL indexes can be created on the basis of any selected attribute. The syntax is: SQL Indexes CREATE [UNIQUE] INDEX indexname ON tablename(column1 [, column2])

  14. For example, based on the attribute P_INDATE stored in the PRODUCT table, the following command creates an index named P_INDATEX: CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE); Using the UNIQUE index qualifier, you can even create an index that prevents you from using a value that has been used before. Such a feature is especially useful when the index attribute is a candidate key whose values must not be duplicated: CREATE UNIQUE INDEX P_CODEX ON PRODUCT(P_CODE); SQL Indexes

  15. INSERT SELECT UPDATE DELETE ROLLBACK COMMIT Data Manipulation Commands

  16. INSERT Used to enter data into table Basic Syntax: Example Adding Table Rows INSERT INTO columnname VALUES (value1, value2, … , valueN); INSERT INTO VENDORVALUES (21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y');

  17. When entering values, notice that: Row contents are entered between parentheses Character and date values are entered between apostrophes Numerical entries are not enclosed in apostrophes Attribute entries are separated by commas A value is required for each column Use NULL for unknown values Adding Table Rows (2) INSERT INTO PRODUCTVALUES ('BRT-345','Titanium drill bit','18-Oct-09', 75, 10, 4.50, 0.06, NULL);

  18. Changes made to table contents are not physically saved on disk until: Database is closed Program is closed COMMIT command is used Syntax: Saving Table Changes NOTE TO MS ACCESS USERS MS Access doesn't support the COMMIT command because it automatically saves changes after the execution of each SQL command. COMMIT [WORK]; • Will permanently save any changes made to any table in the database

  19. SELECT Used to list contents of table Basic Syntax: Listing (Showing) Table Rows SELECT columnlist FROM tablename; Example: SELECT * FROM PRODUCT; • Columnlist represents one or more attributes, separated by commas • Asterisk can be used as wildcard character to list all attributes

  20. THE END

More Related