PowerPoint PresentationChapter 4 OutlineBasic SQLSQL Data Definition and Data TypesThe CREATE TABLE Command in SQLThe CREATE TABLE Command in SQL (cont’d.)Slide 7Slide 8Slide 9Slide 10Attribute Data Types and Domains in SQLAttribute Data Types and Domains in SQL (cont’d.)Slide 13Specifying Constraints in SQLSpecifying Attribute Constraints and Attribute DefaultsSlide 16Specifying Key and Referential Integrity ConstraintsSpecifying Key and Referential Integrity Constraints (cont’d.)Giving Names to ConstraintsSpecifying Constraints on Tuples Using CHECKBasic Retrieval Queries in SQLThe SELECT-FROM-WHERE Structure of Basic SQL QueriesThe SELECT-FROM-WHERE Structure of Basic SQL Queries (cont’d.)Slide 24Slide 25Ambiguous Attribute NamesAliasing, Renaming, and Tuple VariablesUnspecified WHERE Clause and Use of the AsteriskUnspecified WHERE Clause and Use of the Asterisk (cont’d.)Tables as Sets in SQLTables as Sets in SQL (cont’d.)Substring Pattern Matching and Arithmetic OperatorsOrdering of Query ResultsDiscussion and Summary of Basic SQL Retrieval QueriesINSERT, DELETE, and UPDATE Statements in SQLThe INSERT CommandThe DELETE CommandThe UPDATE CommandSummaryCopyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-WesleyChapter 4Basic SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheChapter 4 OutlineSQL Data Definition and Data TypesSpecifying Constraints in SQLBasic Retrieval Queries in SQLINSERT, DELETE, and UPDATE Statements in SQLAdditional Features of SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheBasic SQLSQL language Considered one of the major reasons for the commercial success of relational databasesSQL Structured Query LanguageStatements for data definitions, queries, and updates (both DDL and DML)SQL is a declarative languageCopyright © 2011 Ramez Elmasri and Shamkant NavatheSQL Data Definition and Data TypesTerminology:Table, row, and column used for relational model terms relation, tuple, and attributeCREATE statementMain SQL command for data definitionCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe CREATE TABLE Command in SQLSpecify a new relation Provide nameSpecify attributes and initial constraintsCan optionally specify schema:CREATE TABLE COMPANY.EMPLOYEE ...orCREATE TABLE EMPLOYEE ...Copyright © 2011 Ramez Elmasri and Shamkant NavatheThe CREATE TABLE Command in SQL (cont’d.)Base tables (base relations)Relation and its tuples are actually created and stored as a file by the DBMSVirtual relationsCreated through the CREATE VIEW statementCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe CREATE TABLE Command in SQL (cont’d.)Some foreign keys may cause errors Specified either via: •Circular references •Or because they refer to a table that has not yet been createdCopyright © 2011 Ramez Elmasri and Shamkant NavatheAttribute Data Types and Domains in SQLBasic data typesNumeric data types •Integer numbers: INTEGER, INT, and SMALLINT•Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISIONCharacter-string data types •Fixed length: CHAR(n), CHARACTER(n)•Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)Copyright © 2011 Ramez Elmasri and Shamkant NavatheAttribute Data Types and Domains in SQL (cont’d.)Bit-string data types •Fixed length: BIT(n)•Varying length: BIT VARYING(n)Boolean data type •Values of TRUE or FALSE or NULLDATE data type •Ten positions•Components are YEAR, MONTH, and DAY in the form YYYY-MM-DDCopyright © 2011 Ramez Elmasri and Shamkant NavatheAttribute Data Types and Domains in SQL (cont’d.)Domain Name used with the attribute specificationMakes it easier to change the data type for a domain that is used by numerous attributes Improves schema readabilityExample:•CREATE DOMAIN SSN_TYPE AS CHAR(9);Copyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Constraints in SQLBasic constraints:Key and referential integrity constraintsRestrictions on attribute domains and NULLsConstraints on individual tuples within a relationCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Attribute Constraints and Attribute DefaultsNOT NULL NULL is not permitted for a particular attributeDefault valueDEFAULT <value> CHECK clauseDnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);Copyright © 2011 Ramez Elmasri and Shamkant NavatheCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Key and Referential Integrity ConstraintsPRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relationDnumber INT PRIMARY KEY;UNIQUE clause Specifies alternate (secondary) keysDname VARCHAR(15) UNIQUE;Copyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Key and Referential Integrity Constraints (cont’d.)FOREIGN KEY clauseDefault operation: reject update on violationAttach referential triggered action clause•Options include SET NULL, CASCADE, and SET DEFAULT•Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE•CASCADE option suitable for “relationship” relationsCopyright © 2011 Ramez Elmasri and Shamkant NavatheGiving Names to ConstraintsKeyword CONSTRAINTName a constraintUseful for later alteringCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Constraints on Tuples Using CHECKCHECK clauses at the end of a CREATE TABLE statementApply to each tuple individuallyCHECK (Dept_create_date <= Mgr_start_date);Copyright © 2011 Ramez Elmasri and Shamkant NavatheBasic Retrieval Queries in SQLSELECT statementOne basic statement for retrieving information from a databaseSQL allows a table to have two or more tuples that are identical in all their attribute valuesUnlike relational modelMultiset or bag behaviorCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe SELECT-FROM-WHERE Structure of Basic SQL QueriesBasic form of the SELECT statement:Copyright © 2011 Ramez Elmasri and Shamkant NavatheThe SELECT-FROM-WHERE Structure of Basic SQL Queries (cont’d.)Logical comparison operators=, <, <=, >, >=, and <>Projection attributesAttributes whose values are to be retrievedSelection
View Full Document