220 likes | 285 Views
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
E N D
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 American National Standards Institute (ANSI) prescribes a standard SQL Several SQL dialects exist Introduction to SQL
SQL Family SQL (Structured Query Language)
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
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) );
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
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
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
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
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
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])
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
INSERT SELECT UPDATE DELETE ROLLBACK COMMIT Data Manipulation Commands
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');
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);
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
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