《4420DatabaseFundamentalsChapter7IntroductiontoSQL》.ppt

IS 4420Database Fundamentals Chapter 7:Introduction to SQL Leon Chen Systems Development Life Cycle Part Four: Implementation Chapter 7 – Introduction to SQL Chapter 8 – Advanced SQL Chapter 9 – Client/Server Environment Chapter 10 – Internet Chapter 11 – Data Warehousing Overview Define a database using SQL data definition language Work with Views Write single table queries Establish referential integrity SQL Overview Structured Query Language The standard for relational database management systems (RDBMS) SQL-92 and SQL-99 Standards – Purpose: Specify syntax/semantics for data definition and manipulation Define data structures Enable portability Specify minimal (level 1) and complete (level 2) standards Allow for later growth/enhancement to standard SQL Environment Catalog A set of schemas that constitute the description of a database Schema The structure that contains descriptions of objects created by a user (base tables, views, constraints) Data Definition Language (DDL) Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) Commands that maintain and query a database Data Control Language (DCL) Commands that control a database, including administering privileges and committing data SQL Data types (from Oracle 9i) String types CHAR(n) – fixed-length character data, n characters long Maximum length = 2000 bytes VARCHAR2(n) – variable length character data, maximum 4000 bytes LONG – variable-length character data, up to 4GB. Maximum 1 per table Numeric types NUMBER(p,q) – general purpose numeric data type INTEGER(p) – signed integer, p digits wide FLOAT(p) – floating point in scientific notation with p binary digits precision Date/time type DATE – fixed-length date/time in dd-mm-yy form SQL Database Definition Data Definition Language (DDL) Major CREATE statements: CREATE SCHEMA – defines a portion of the database owned by a particular user CREATE TABLE – defines