SQL Commands

1
SQL Commands
2
Learning Objectives

• Understand SQL commands

3
Acknowledgments

• These slides have been adapted from Thomas
  Connolly and Carolyn Begg

4
ISO SQL Data Types
5
Integrity Enhancement Feature

• Consider five types of integrity constraints
• Required data.
• Domain constraints.
• Entity integrity.
• Referential integrity.
• Enterprise constraints.

6
Integrity Enhancement Feature

• Required Data
• position VARCHAR(10) NOT NULL
• Domain Constraints
• (a) CHECK
• sex CHAR NOT NULL
• CHECK (sex IN (M, F))

7
Integrity Enhancement Feature

• (b) CREATE DOMAIN
• CREATE DOMAIN DomainName AS dataType
• DEFAULT defaultOption
• CHECK (searchCondition)
• For example
• CREATE DOMAIN SexType AS CHAR
• CHECK (VALUE IN (M, F))
• sex SexType NOT NULL

8
Integrity Enhancement Feature

• searchCondition can involve a table lookup
• CREATE DOMAIN BranchNo AS CHAR(4)
• CHECK (VALUE IN (SELECT branchNo
• FROM Branch))
• Domains can be removed using DROP DOMAIN
• DROP DOMAIN DomainName
• RESTRICT CASCADE

9
IEF - Entity Integrity

• Primary key of a table must contain a unique,
  non-null value for each row.
• ISO standard supports FOREIGN KEY clause in
  CREATE and ALTER TABLE statements
• PRIMARY KEY(staffNo)
• PRIMARY KEY(clientNo, propertyNo)
• Can only have one PRIMARY KEY clause per table.
  Can still ensure uniqueness for alternate keys
  using UNIQUE
• UNIQUE(telNo)

10
IEF - Referential Integrity

• FK is column or set of columns that links each
  row in child table containing foreign FK to row
  of parent table containing matching PK.
• Referential integrity means that, if FK contains
  a value, that value must refer to existing row in
  parent table.
• ISO standard supports definition of FKs with
  FOREIGN KEY clause in CREATE and ALTER TABLE
• FOREIGN KEY(branchNo) REFERENCES Branch

11
IEF - Referential Integrity

• Any INSERT/UPDATE that attempts to create FK
  value in child table without matching candidate
  key value in parent is rejected.
• Action taken that attempts to update/delete a
  candidate key value in parent table with matching
  rows in child is dependent on referential action
  specified using ON UPDATE and ON DELETE
  subclauses
• CASCADE - SET NULL
• SET DEFAULT - NO ACTION

12
IEF - Referential Integrity

• CASCADE Delete row from parent and delete
  matching rows in child, and so on in cascading
  manner.
• SET NULL Delete row from parent and set FK
  column(s) in child to NULL. Only valid if FK
  columns are NOT NULL.
• SET DEFAULT Delete row from parent and set each
  component of FK in child to specified default.
  Only valid if DEFAULT specified for FK columns
• NO ACTION Reject delete from parent. Default.

13
IEF - Referential Integrity

• FOREIGN KEY (staffNo) REFERENCES Staff
  ON DELETE SET NULL
• FOREIGN KEY (ownerNo) REFERENCES Owner ON
  UPDATE CASCADE

14
IEF - Enterprise Constraints

• Could use CHECK/UNIQUE in CREATE and ALTER TABLE.
• Also have
• CREATE ASSERTION AssertionName
• CHECK (searchCondition)
• which is very similar to the CHECK clause.

15
IEF - Enterprise Constraints

• CREATE ASSERTION StaffNotHandlingTooMuch
• CHECK (NOT EXISTS (SELECT staffNo
• FROM PropertyForRent
• GROUP BY staffNo
• HAVING COUNT() gt 100))

16
Data Definition

• SQL DDL allows database objects such as schemas,
  domains, tables, views, and indexes to be created
  and destroyed.
• Main SQL DDL statements are
• CREATE SCHEMA DROP SCHEMA
• CREATE/ALTER DOMAIN DROP DOMAIN
• CREATE/ALTER TABLE DROP TABLE
• CREATE VIEW DROP VIEW
• Many DBMSs also provide
• CREATE INDEX DROP INDEX

17
Data Definition

• Relations and other database objects exist in an
  environment.
• Each environment contains one or more catalogs,
  and each catalog consists of set of schemas.
• Schema is named collection of related database
  objects.
• Objects in a schema can be tables, views,
  domains, assertions, collations, translations,
  and character sets. All have same owner.

18
CREATE SCHEMA

• CREATE SCHEMA Name
• AUTHORIZATION CreatorId
• DROP SCHEMA Name RESTRICT CASCADE
• With RESTRICT (default), schema must be empty or
  operation fails.
• With CASCADE, operation cascades to drop all
  objects associated with schema in order defined
  above. If any of these operations fail, DROP
  SCHEMA fails.

19
CREATE TABLE

• CREATE TABLE TableName
• (colName dataType NOT NULL UNIQUE
• DEFAULT defaultOption
• CHECK searchCondition ,...
• PRIMARY KEY (listOfColumns),
• UNIQUE (listOfColumns), ,
• FOREIGN KEY (listOfFKColumns)
• REFERENCES ParentTableName (listOfCKColumns),
• ON UPDATE referentialAction
• ON DELETE referentialAction ,
• CHECK (searchCondition) , )

20
CREATE TABLE

• Creates a table with one or more columns of the
  specified dataType.
• With NOT NULL, system rejects any attempt to
  insert a null in the column.
• Can specify a DEFAULT value for the column.
• Primary keys should always be specified as NOT
  NULL.
• FOREIGN KEY clause specifies FK along with the
  referential action

21
Example 1 - CREATE TABLE

• CREATE DOMAIN OwnerNumber AS VARCHAR(5)
• CHECK (VALUE IN (SELECT ownerNo FROM
  PrivateOwner))
• CREATE DOMAIN StaffNumber AS VARCHAR(5)
• CHECK (VALUE IN (SELECT staffNo FROM Staff))
• CREATE DOMAIN PNumber AS VARCHAR(5)
• CREATE DOMAIN PRooms AS SMALLINT
• CHECK(VALUE BETWEEN 1 AND 15)
• CREATE DOMAIN PRent AS DECIMAL(6,2)
• CHECK(VALUE BETWEEN 0 AND 9999.99)

22
Example 1 - CREATE TABLE

• CREATE TABLE PropertyForRent (
• propertyNo PNumber NOT NULL, .
• rooms PRooms NOT NULL DEFAULT 4,
• rent PRent NOT NULL, DEFAULT 600,
• ownerNo OwnerNumber NOT NULL,
• staffNo StaffNumber
• Constraint StaffNotHandlingTooMuch .
• branchNo BranchNumber NOT NULL,
• PRIMARY KEY (propertyNo),
• FOREIGN KEY (staffNo) REFERENCES Staff
• ON DELETE SET NULL ON UPDATE CASCADE .)

23
ALTER TABLE

• Add a new column to a table.
• Drop a column from a table.
• Add a new table constraint.
• Drop a table constraint.
• Set a default for a column.
• Drop a default for a column.

24
Example 2(a) - ALTER TABLE

• Change Staff table by removing default of
  Assistant for position column and setting
  default for sex column to female (F).
• ALTER TABLE Staff
• ALTER position DROP DEFAULT
• ALTER TABLE Staff
• ALTER sex SET DEFAULT F

25
Example 2(b) - ALTER TABLE

• Remove constraint from PropertyForRent that
  staff not allowed to handle more than 100
  properties at time. Add new column to Client
  table.
• ALTER TABLE PropertyForRent
• DROP CONSTRAINT StaffNotHandlingTooMuch
• ALTER TABLE Client
• ADD prefNoRooms PRooms

26
DROP TABLE

• DROP TABLE TableName RESTRICT CASCADE
• e.g. DROP TABLE PropertyForRent
• Removes named table and all rows within it.
• With RESTRICT, if any other objects depend for
  their existence on continued existence of this
  table, SQL does not allow request.
• With CASCADE, SQL drops all dependent objects
  (and objects dependent on these objects).

27
Views

• View
• Dynamic result of one or more relational
  operations operating on base relations to produce
  another relation.
• Virtual relation that does not necessarily
  actually exist in the database but is produced
  upon request, at time of request.

28
Views

• Contents of a view are defined as a query on one
  or more base relations.
• With view resolution, any operations on view are
  automatically translated into operations on
  relations from which it is derived.
• With view materialization, the view is stored as
  a temporary table, which is maintained as the
  underlying base tables are updated.

29
SQL - CREATE VIEW

• CREATE VIEW ViewName (newColumnName ,...)
• AS subselect
• WITH CASCADED LOCAL CHECK OPTION
• Can assign a name to each column in view.
• If list of column names is specified, it must
  have same number of items as number of columns
  produced by subselect.
• If omitted, each column takes name of
  corresponding column in subselect.

30
SQL - CREATE VIEW

• List must be specified if there is any ambiguity
  in a column name.
• The subselect is known as the defining query.
• WITH CHECK OPTION ensures that if a row fails to
  satisfy WHERE clause of defining query, it is not
  added to underlying base table.
• Need SELECT privilege on all tables referenced in
  subselect and USAGE privilege on any domains used
  in referenced columns.

31
Example 3 - Create Horizontal View

• Create view so that manager at branch B003 can
  only see details for staff who work in his or her
  office.
• CREATE VIEW Manager3Staff
• AS SELECT
• FROM Staff
• WHERE branchNo B003

32
Example 4 - Create Vertical View

• Create view of staff details at branch B003
  excluding salaries.
• CREATE VIEW Staff3
• AS SELECT staffNo, fName, lName, position, sex
• FROM Staff
• WHERE branchNo B003

33
Example 5 - Grouped and Joined Views

• Create view of staff who manage properties for
  rent, including branch number they work at, staff
  number, and number of properties they manage.
• CREATE VIEW StaffPropCnt (branchNo,
  staffNo, cnt)
• AS SELECT s.branchNo, s.staffNo, COUNT()
• FROM Staff s, PropertyForRent p
• WHERE s.staffNo p.staffNo
• GROUP BY s.branchNo, s.staffNo

34
Example 3 - Grouped and Joined Views
35
SQL - DROP VIEW

• DROP VIEW ViewName RESTRICT CASCADE
• Causes definition of view to be deleted from
  database.
• For example
• DROP VIEW Manager3Staff

36
SQL - DROP VIEW

• With CASCADE, all related dependent objects are
  deleted i.e. any views defined on view being
  dropped.
• With RESTRICT (default), if any other objects
  depend for their existence on continued existence
  of view being dropped, command is rejected.

37
Advantages of Views

• Data independence
• Currency
• Improved security
• Reduced complexity
• Convenience
• Customization
• Data integrity

38
Disadvantages of Views

• Update restriction
• Structure restriction
• Performance

39
Transactions

• SQL defines transaction model based on COMMIT and
  ROLLBACK.
• Transaction is logical unit of work with one or
  more SQL statements guaranteed to be atomic with
  respect to recovery.
• An SQL transaction automatically begins with a
  transaction-initiating SQL statement (e.g.,
  SELECT, INSERT).
• Changes made by transaction are not visible to
  other concurrently executing transactions until
  transaction completes.

40
Transactions

• Transaction can complete in one of four ways
• - COMMIT ends transaction successfully, making
  changes permanent.
• - ROLLBACK aborts transaction, backing out any
  changes made by transaction.
• - For programmatic SQL, successful program
  termination ends final transaction successfully,
  even if COMMIT has not been executed.
• - For programmatic SQL, abnormal program end
  aborts transaction.

41
Transactions

• New transaction starts with next
  transaction-initiating statement.
• SQL transactions cannot be nested.
• SET TRANSACTION configures transaction
• SET TRANSACTION
• READ ONLY READ WRITE
• ISOLATION LEVEL READ UNCOMMITTED
• READ COMMITTEDREPEATABLE READ SERIALIZABLE

42
Access Control - Authorization Identifiers and
Ownership

• Authorization identifier is normal SQL identifier
  used to establish identity of a user. Usually has
  an associated password.
• Used to determine which objects user may
  reference and what operations may be performed on
  those objects.
• Each object created in SQL has an owner, as
  defined in AUTHORIZATION clause of schema to
  which object belongs.
• Owner is only person who may know about it.

43
Privileges

• Actions user permitted to carry out on given base
  table or view
• SELECT Retrieve data from a table.
• INSERT Insert new rows into a table.
• UPDATE Modify rows of data in a table.
• DELETE Delete rows of data from a table.
• REFERENCES Reference columns of named table in
  integrity constraints.
• USAGE Use domains, collations, character sets,
  and translations.

44
Privileges

• Can restrict INSERT/UPDATE/REFERENCES to named
  columns.
• Owner of table must grant other users the
  necessary privileges using GRANT statement.
• To create view, user must have SELECT privilege
  on all tables that make up view and REFERENCES
  privilege on the named columns.

45
GRANT

• GRANT PrivilegeList ALL PRIVILEGES
• ON ObjectName
• TO AuthorizationIdList PUBLIC
• WITH GRANT OPTION
• PrivilegeList consists of one or more of above
  privileges separated by commas.
• ALL PRIVILEGES grants all privileges to a user.

46
GRANT

• PUBLIC allows access to be granted to all present
  and future authorized users.
• ObjectName can be a base table, view, domain,
  character set, collation or translation.
• WITH GRANT OPTION allows privileges to be passed
  on.

47
Example 7/8 - GRANT

• Give Manager full privileges to Staff table.
• GRANT ALL PRIVILEGES
• ON Staff
• TO Manager WITH GRANT OPTION
• Give users Personnel and Director SELECT and
  UPDATE on column salary of Staff.
• GRANT SELECT, UPDATE (salary)
• ON Staff
• TO Personnel, Director

48
Example 9 - GRANT Specific Privileges to PUBLIC

• Give all users SELECT on Branch table.
• GRANT SELECT
• ON Branch
• TO PUBLIC

49
REVOKE

• REVOKE takes away privileges granted with GRANT.
• REVOKE GRANT OPTION FOR
• PrivilegeList ALL PRIVILEGES
• ON ObjectName
• FROM AuthorizationIdList PUBLIC
• RESTRICT CASCADE
• ALL PRIVILEGES refers to all privileges granted
  to a user by user revoking privileges.

50
REVOKE

• GRANT OPTION FOR allows privileges passed on via
  WITH GRANT OPTION of GRANT to be revoked
  separately from the privileges themselves.
• REVOKE fails if it results in an abandoned
  object, such as a view, unless the CASCADE
  keyword has been specified.
• Privileges granted to this user by other users
  are not affected.

51
REVOKE
52
Example 10/11 - REVOKE Specific Privileges

• Revoke privilege SELECT on Branch table from all
  users.
• REVOKE SELECT
• ON Branch
• FROM PUBLIC
• Revoke all privileges given to Director on Staff
  table.
• REVOKE ALL PRIVILEGES
• ON Staff
• FROM Director