Basic SQL

Structured Query Language

 Learning Objectives
• To understand about Data definition, queries,
updates, constraint specification, and view definition
• To explore the following:
• Data definition commands for creating tables
• Commands for constraint specification
• Simple retrieval queries
• Database update commands
 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
 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)
– Core specification
– Plus specialized extensions
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
 Schema and Catalog Concepts in SQL

• SQL schema
– Identified by a schema name
– Includes an authorization identifier and
descriptors for each element
• Schema elements include
– Tables, constraints, views, domains, and other
constructs
• Each statement in SQL ends with a semicolon
 Schema and Catalog Concepts in SQL
(cont’d.)
• CREATE SCHEMA statement
– 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
 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 ...
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
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
 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)
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
 Attribute Data Types and Domains in SQL
(cont’d.)
• Additional data types
– Timestamp data type (TIMESTAMP)
• Includes the DATE and TIME fields
• Plus a minimum of six positions for decimal fractions of
seconds
• Optional WITH TIME ZONE qualifier
– INTERVAL data type
• Specifies a relative value that can be used to increment
or decrement an absolute value of a date, time, or
timestamp
 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);
 Specifying Constraints in SQL
• Basic constraints:
– Key and referential integrity constraints
– Restrictions on attribute domains and NULLs
– Constraints on individual tuples within a relation
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);
 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;
 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
 Giving Names to Constraints
• Keyword CONSTRAINT
– Name a constraint
– Useful for later altering
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);
 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
The SELECT-FROM-WHERE Structure of Basic
SQL Queries
• Basic form of the SELECT statement:
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
 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
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)
 Unspecified WHERE Clause
and Use of the Asterisk
• Missing WHERE clause
– Indicates no condition on tuple selection
• CROSS PRODUCT
– All possible tuple combinations
 Unspecified WHERE Clause
and Use of the Asterisk (cont’d.)
• Specify an asterisk (*)
– Retrieve all the attribute values of the selected
tuples
 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
 Tables as Sets in SQL (cont’d.)
• Set operations
– UNION, EXCEPT (difference), INTERSECT
– Corresponding multiset operations: UNION ALL,
EXCEPT ALL, INTERSECT ALL)
 Substring Pattern Matching and Arithmetic
Operators
• LIKE comparison operator
– Used for string pattern matching
– % replaces an arbitrary number of zero or more
characters
– underscore (_) replaces a single character
• Standard arithmetic operators:
– Addition (+), subtraction (–), multiplication (*), and
division (/)
• BETWEEN comparison operator
 Ordering of Query Results
• Use ORDER BY clause
– Keyword DESC to see result in a descending
order of values
– Keyword ASC to specify ascending order
explicitly
– ORDER BY D.Dname DESC, E.Lname
ASC, E.Fname ASC
 Discussion and Summary
of Basic SQL Retrieval Queries
 INSERT, DELETE, and UPDATE Statements in
SQL
• Three commands used to modify the
database:
– INSERT, DELETE, and UPDATE
 The INSERT Command
• Specify the relation name and a list of values
for the tuple
 The DELETE Command
• Removes tuples from a relation
– Includes a WHERE clause to select the tuples to be
deleted
 The UPDATE Command
• Modify attribute values of one or more
selected tuples
• Additional SET clause in the UPDATE
command
– Specifies attributes to be modified and new values
 Additional Features of SQL
• Techniques for specifying complex retrieval
queries
• Writing programs in various programming
languages that include SQL statements
• Set of commands for specifying physical
database design parameters, file structures for
relations, and access paths
• Transaction control commands
Additional Features of SQL (cont’d.)
• Specifying the granting and revoking of
privileges to users
• Constructs for creating triggers
• Enhanced relational systems known as object-
relational
• New technologies such as XML and OLAP
 Summary
• SQL
– Comprehensive language
– Data definition, queries, updates, constraint
specification, and view definition
• Covered in this module:
– Data definition commands for creating tables
– Commands for constraint specification
– Simple retrieval queries
– Database update commands