Chapter 4 Basic SQL Copyright 2011 Pearson Education Inc Publishing as Pearson Addison Wesley Chapter 4 Outline SQL Data Definition and Data Types Specifying Constraints in SQL Basic Retrieval Queries in SQL INSERT DELETE and UPDATE Statements in SQL Additional Features of SQL Copyright 2011 Ramez Elmasri and Shamkant Navathe Basic SQL SQL language Considered one of the major reasons for the commercial success of relational databases SQL Structured Query Language Statements for data definitions queries and updates both DDL and DML SQL is a declarative language Copyright 2011 Ramez Elmasri and Shamkant Navathe SQL Data Definition and Data Types Terminology Table row and column used for relational model terms relation tuple and attribute CREATE statement Main SQL command for data definition Copyright 2011 Ramez Elmasri and Shamkant Navathe The CREATE TABLE Command in SQL Specify a new relation Provide name Specify attributes and initial constraints Can optionally specify schema CREATE TABLE COMPANY EMPLOYEE or CREATE TABLE EMPLOYEE Copyright 2011 Ramez Elmasri and Shamkant Navathe The 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 DBMS Virtual relations Created through the CREATE VIEW statement Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe The 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 created Copyright 2011 Ramez Elmasri and Shamkant Navathe Attribute Data Types and Domains in SQL Basic data types Numeric data types Integer numbers INTEGER INT and SMALLINT Floating point real numbers FLOAT or REAL and DOUBLE PRECISION Character 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 Navathe Attribute 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 NULL DATE data type Ten positions Components are YEAR MONTH and DAY in the form YYYY MM DD Copyright 2011 Ramez Elmasri and Shamkant Navathe Attribute Data Types and Domains in SQL cont d Domain Name used with the attribute specification Makes it easier to change the data type for a domain that is used by numerous attributes Improves schema readability Example CREATE DOMAIN SSN TYPE AS CHAR 9 Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying Constraints in SQL Basic constraints Key and referential integrity constraints Restrictions on attribute domains and NULLs Constraints on individual tuples within a relation Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying Attribute Constraints and Attribute Defaults NOT NULL NULL is not permitted for a particular attribute Default value DEFAULT value CHECK clause Dnumber INT NOT NULL CHECK Dnumber 0 AND Dnumber 21 Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying Key and Referential Integrity Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY UNIQUE clause Specifies alternate secondary keys Dname VARCHAR 15 UNIQUE Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying Key and Referential Integrity Constraints cont d FOREIGN KEY clause Default operation reject update on violation Attach 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 relations Copyright 2011 Ramez Elmasri and Shamkant Navathe Giving Names to Constraints Keyword CONSTRAINT Name a constraint Useful for later altering Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying Constraints on Tuples Using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK Dept create date Mgr start date Copyright 2011 Ramez Elmasri and Shamkant Navathe Basic Retrieval Queries in SQL SELECT statement One basic statement for retrieving information from a database SQL allows a table to have two or more tuples that are identical in all their attribute values Unlike relational model Multiset or bag behavior Copyright 2011 Ramez Elmasri and Shamkant Navathe The SELECT FROM WHERE Structure of Basic SQL Queries Basic form of the SELECT statement Copyright 2011 Ramez Elmasri and Shamkant Navathe The SELECT FROM WHERE Structure of Basic SQL Queries cont d Logical comparison operators and Projection attributes Attributes whose values are to be retrieved Selection condition Boolean condition that must be true for any retrieved tuple Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Copyright 2011 Ramez Elmasri and Shamkant Navathe Ambiguous Attribute Names Same name can be used for two or more attributes As long as the attributes are in different relations Must qualify the attribute name with the relation name to prevent ambiguity Copyright 2011 Ramez Elmasri and Shamkant Navathe Aliasing Renaming and Tuple Variables Aliases or tuple variables Declare alternative relation names E and S EMPLOYEE AS E Fn Mi Ln Ssn Bd Addr Sex Sal Sssn Dno Copyright 2011 Ramez Elmasri and Shamkant Navathe Unspecified WHERE Clause and Use of the Asterisk Missing WHERE clause Indicates no condition on tuple selection CROSS PRODUCT All possible tuple combinations Copyright 2011 Ramez Elmasri and Shamkant Navathe Unspecified WHERE Clause and Use of the Asterisk cont d Specify an asterisk Retrieve all the attribute values of the selected tuples Copyright 2011 Ramez Elmasri and Shamkant Navathe Tables as Sets in SQL SQL does not automatically eliminate duplicate tuples in query results Use the keyword DISTINCT in the SELECT clause Only distinct tuples should remain in the result Copyright 2011 Ramez Elmasri and Shamkant Navathe Tables as Sets in SQL cont d Set operations UNION EXCEPT difference INTERSECT Corresponding multiset operations UNION ALL EXCEPT ALL INTERSECT ALL Copyright 2011 Ramez Elmasri and Shamkant Navathe Substring Pattern Matching and Arithmetic Operators LIKE comparison operator
View Full Document
Unlocking...