Chapter 6 Basic SQL 6 1 SQL Data Definition and Data Types Basic SQL SQL Structured Query Language Considered one of the major reasons for the commercial success of relational databases Appeared 1974 Last stable release SQL 2011 Core specification Standards ANSI since 1986 ISO IEC 9075 since 1987 4 Basic SQL Statements for data definitions queries and updates DDL DML and VDL Plus specialized extensions which may be implementation specific 5 Basic SQL SQL language is case insensitive keywords namespaces SQL data values are case sensitive For readability some case style conventions may be used Each statement in SQL ends with a semicolon 6 Schema and Catalog Concepts in SQL SQL schema In most systems a Database Identified by a schema name Includes an authorization identifier and descriptors for each element e g CREATE SCHEMA COMPANY AUTHORIZATION Jsmith Schema elements include Tables Constraints Views Domains and other constructs 7 SQL Terminology Table row and column used for relational model terms relation tuple Terminology and attribute CREATE statement Main SQL command for data definition 8 Schema and Catalog Concepts in SQL CREATE SCHEMA statement CREATE SCHEMA schema name auth e g CREATE SCHEMA COMPANY AUTHORIZATION Jsmith Catalog Named collection of schemas in an SQL environment SQL environment Installation of an SQL compliant RDBMS on a computer system 9 The CREATE TABLE Command in SQL Specify a new relation table Provide name Specify attributes and initial constraints Can optionally specify schema CREATE TABLE COMPANY EMPLOYEE or CREATE TABLE EMPLOYEE 1 0 The CREATE TABLE Command in SQL 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 1 1 CREATE TABLES for Company Data 1 2 CREATE TABLES for Company Data 1 3 CREATE TABLE Command Does the order of table creation matter Some foreign keys may cause errors Specified either via Circular references Or because they refer to a table that has not yet been created 1 4 Attribute Data Types and Domains in SQL Different dialects of SQL may have different types Microsoft SQL Server Oracle 11g 10g XE etc MySQL IBM DB2 PostgreSQL SQLite 1 5 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 1 6 Attribute Data Types and Domains in SQL 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 Use DateTime instead Ten positions Components are YEAR MONTH and DAY in the form YYYY MM DD 1 7 Attribute Data Types and Domains in SQL Custom 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 1 9 6 2 Specifying Constraints in SQL Specifying Constraints in SQL Basic constraints Key and referential integrity constraints Restrictions on attribute domains and NULLs Constraints on individual tuples within a relation 2 1 Giving Names to Constraints Keyword CONSTRAINT Explicitly name a constraint Useful for later altering E g Alter Table Department Drop constraint DEPTSK 2 2 Specifying Attribute Constraints and Not Null and Default value NOT NULL NULL is not permitted for a particular attribute Default value DEFAULT value 2 3 Specifying Attribute Constraints and Check clause CHECK clause Dnumber INT NOT NULL CHECK Dnumber 0 AND Dnumber 21 CREATE TABLE Persons ID int NOT NULL LastName varchar 255 NOT NULL FirstName varchar 255 Age int CHECK Age 18 2 4 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 2 5 Another Example CHECK clauses at the end of a CREATE TABLE statement CREATE TABLE Persons ID int NOT NULL LastName varchar 255 NOT NULL FirstName varchar 255 Age int City varchar 255 CONSTRAINT CHK Person CHECK Age 18 AND City Sandnes Is the Above constraint equivalent to the following two constraints CONSTRAINT CHK Age CHECK Age 18 CONSTRAINT CHK City CHECK City Sandnes 2 6 Specifying Key Integrity Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY Or Dnumber INT not null Primary Key Dnumber UNIQUE clause Specifies alternate secondary keys Dname VARCHAR 15 UNIQUE 2 7 Specifying Key and Referential Integrity Constraints 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 2 8 6 3 Basic Retrieval Queries in SQL 3 0 Basic Retrieval Queries in SQL SELECT statement One basic statement for retrieving information from a database Basic form of the SELECT statement 3 1 Projection attributes SELECT Attributes whose values are to be retrieved Selection condition WHERE Boolean condition that must be true for any retrieved tuple Logical comparison operators and 3 2 Some Queries 3 3 Some Queries 3 4 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 3 5 Aliasing Renaming and Tuple Variables The SQL AS keyword Attribute or Table Aliases SELECT CustomerID AS ID CustomerName AS Customer FROM Customers Examples SELECT attribute AS attr alias name FROM table name AS table alias name FROM subquery AS virtual table name CAVEAT SQL syntax allows AS to be implied A missing comma in a SELECT clause can be parsed as an implied AS 3 6 SELECT CustomerID ID CustomerName Customer FROM Customers Unspecified WHERE Clause and Use of the Asterisk Missing WHERE clause Indicates no condition on tuple selection CROSS PRODUCT All possible tuple combinations 3 7 Unspecified WHERE Clause and Use of the Asterisk Specify an asterisk Retrieve all the attribute values of the selected tuples 3 8 Tables as Sets in SQL SQL does not automatically eliminate duplicate tuples in query results Use the keyword DISTINCT in the