DB C3 Basic SQL

Overview of SQL

History

• SQL-86: the first standard by ANSI and ISO standard.
• SQL-89: minor revision, an extended standard.
• SQL-92: currently supported by most commercial RDBMS.
• SQL-99: a major extension of SQL-92, partly supported now.
• following version: SQL:2003, SQL:2006, SQL:2008, SQL:2011, and most recently SQL:2016.

SQL Conformance levels

• Entry level SQL (入门级)
• Transitional SQL (过渡级)
• Intermediate SQL (中间级)
• Full SQL (完全级)

Now no DBMS products achieve Full SQL level.

SQL Parts

Data-Definition Language, DDL

Create	drop	alter
table	table	table
index	index
view	view
trigger	trigger

• Schema: Commands for defining relation schemas, deleting relations, and modifying relation schemas.
• Integrity: Commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.
• View definition: Commands for defining views.

Data-Manipulation Language ,DML

• Select … From

• Insert, Delete,Update

Commands to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.

Data-Control Language (DCL)

• Grant, Revoke

Commands for specifying access rights to relations and views.

Other Parts

• Transaction control: SQL includes commands for specifying the beginning and end points of transactions.
• Embedded SQL and dynamic SQL. Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, and Java.

Here shows the banking example through the chapter.

branch(branch-name, branch-city, assets)

customer(customer-name, customer-street, customer-city)

account(account-number, branch-name, balance)

loan(loan-number, branch-name, amount)

depositor(customer-name, account-number)

borrower(customer-name, loan-number)

Data Definition Language

SQL Data Definition / Domain types

• char(n): A fixed-length character string with user-specified length n. The full form is character.
• varchar(n): A variable-length character string with user-specified maximum length n. The full form is character varying.
• int: An integer (a finite subset of the integers that is machine-dependent). The full form is integer.
• smallint: A small integer (a machine-dependent subset of the integer type).
• numeric(p,d): A fixed-point number with user-specified precision. The number consists of $p$ digits (plus a sign). And $d$ of the $p$ digits are to the right of the decimal point.
  e.g numeric(3,1) allows 44.5, rather 444.5 nor 0.32.
• real: Floating-point floating-point numbers with machine-dependent precision.
• double precision: Double-precision floating-point numbers with machine-dependent precision.
• float(n): A floating-point number with precision of at least n digits.

Null values are allowed in all the domain types. Declaring an attribute to be not null prohibits null values for that attribute.

Create table -Schema Definition

We define an SQL relation by using the create table command.

create table r(A1 D1,
               A2 D2,
               ...,
               An Dn,
               ⟨integrity-constraint_1⟩,
               ...,
               ⟨integrity-constraint_k⟩);

• r is the name of the relation;
• Each $A_i$ is an attribute name in the schema of relation r
• Each $D_i$ is the data type of values in the domain of attribute $A_i$

e.g

create table department (dept_name varchar(20),
                         building  varchar(15), 
                         budget    numeric(12,2),
					   primary key (dept_name));
					   
create table department (dept_name varchar(20) primary key,
                         building  varchar(15), 
                         budget    numeric(12,2));				

A number of different integrity constraints are supported:

• primary key $(A_{j_1} , A_{j_2} ,…, A_{j_m})$ : The primary-key specification says that attributes $A_{j_1} , A_{j_2} ,…, A_{j_m}$ form the primary key for the relation. The primary-key attributes are required to be nonnull and unique.
• foreign key $(A_{k_1} , A_{k_2} ,…, A_{k_n})$ references $s$ : The foreign key specification says that the values of attributes $(A_{k_1} , A_{k_2} ,…, A_{k_n})$ for any tuple in the relation must correspond to values of the primary key attributes of some tuple in relation $s$.
• not null: The null value is not allowed for that attribute.

Drop and alter table

We delete all information about the dropped relation from the database by using the drop table command.

drop table r;
drop table department;

Be careful to use the DROP command!!!

We add attributes to an existing relation by using the alter table command.

alter table r add (A1 D1,
                   A2 D2,
                   ...,
                   An Dn);

$A$ is the name of the attribute to be added, and $D$ is the type of the added attribute. All tuples in the relation are assigned null as the value for the new attribute.

The alter table command can also drop attributes from a relation by

alter table r drop A;

The alter table command can also modify the attributes of a relation by

alter table r modify (A1, A2);

Create index

Basic Structure

Set Operations

Aggregate Functions

Null Values

Nested Subqueries

Views

Derived Relations

Modification of the Database

Joined Relations