sql-ddl.pdf, Exercises of History

Data Definition Language (DDL). Allows the specification of the database schema. • Data Manipulation Language (DML). Allows the specification of queries ...

Typology: Exercises

2021/2022

Uploaded on 09/12/2022

leyllin
leyllin 🇬🇧

4.3

(15)

241 documents

1 / 22

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
SQL$
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16

Partial preview of the text

Download sql-ddl.pdf and more Exercises History in PDF only on Docsity!

SQL

SQL

  • SQL = “Structured Query Language”
  • Standard query language for relational DBMSs
  • History: Developed at IBM in late 70s 1 st standard: SQL- 2 nd standard: SQL- 3 rd standard: SQL-99 or SQL3, well over 1000 pages “The nice thing about standards is that you have so many to choose from!” -Andrew S. Tannenbaum

SQL

data defini.on

language

SQL Data Definition Language (DDL)

  • Allows the specification of the database schema a set of relations with information about each relation
  • Schema information:
    • The schema of each relation
    • The domain of values associated with each attribute
    • Integrity constraints
  • Other information one can specify:
    • The set of indices to be maintained for each relation
    • Security and authorization information for each relation
    • The physical storage structure of each relation on disk

Domain Types in SQL

  • char(n) Fixed length character string, with user-specified length n
  • varchar(n) Variable length character strings, with user-specified maximum length n
  • int Integer (a finite subset of integers that is machine-dependent)
  • smallint Small integer (a machine-dependent subset of the integer domain type)

Domain Types in SQL

  • numeric(p, d) Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point
  • real, double precision Floating point and double-precision floating point numbers, with machine-dependent precision
  • float Floating point number, with user-specified precision of at least n digits

and others…

DROP TABLE Command

  • Used to remove a relation & its definition The relation can no longer be used in queries, updates, or any other commands since its description no longer exists
  • Syntax: DROP TABLE relationName
  • Example: DROP TABLE branch

ALTER TABLE Command

  • Used to add/drop attributes from a relation
  • Add attribute syntax: ALTER TABLE relationName ADD attribName attribDomain All tuples in the relation are assigned null as the default value of the new attribute
  • Drop attribute syntax: ALTER TABLE relationName DROP attribName Dropping of attributes not supported by many DBMSs

Integrity Constraints

  • Guard against accidental damage to the database by ensuring that authorized changes to the database do not result in a loss of data consistency.
  • Examples:
    • A savings account must have a balance greater than $10,000.
    • A salary of a bank employee must be at least $6.00 an hour
    • A customer must have a (non-null) phone number

SQL Integrity Constraints

  • On single relations:
    • not null
    • primary key
    • unique
    • check(P), where P is a predicate
  • On multiple relations:
    • foreign key

UNIQUE Constraint

  • Specifies that a set of attributes form a candidate key
  • Syntax: UNIQUE (AttrName 1 , …, AttrNamen)
  • Candidate keys are permitted to be null

(in contrast to primary keys)

CHECK Clause

  • Enforce a predicate (condition)
  • Syntax: CHECK (Predicate)
  • Example: Ensure that the values of the assets are non-negative CREATE TABLE branch (branch_name char (15), branch_city char (30), assets integer , primary key (branch_name), CHECK (assets >= 0) )

Referential Integrity

  • Ensures that a value that appears in one relation for a

given set of attributes also appears for a set of attributes in

another relation.

  • Example: If “La Jolla” is a branch name appearing in one of the tuples in the account relation, then there exists a tuple in the branch relation for branch “La Jolla”.

Referential Integrity

  • In the CREATE TABLE statement we can use:
    • The PRIMARY KEY clause to list primary key (PK) attributes.
    • The UNIQUE KEY clause to list candidate key attributes
    • The FOREIGN KEY clause to list foreign key (FK) attributes and the name of the relation referenced by the FK. By default, a FK references PK attributes of the referenced table.