ch03-Basic SQL

Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
4
DataBase System
3.2 Setting Up the Database
Data Definition Language (DDL) provide the abilities to setting up a database.
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
2
DataBase System
3.1 Introduction
SQL is of the ability to implementing database in a computerized environment.
The SQL is an non-procedural (structural) language.
Power of SQL:
Data
definition (DDL)
manipulation (DML)
( Include Other database object definition )
3.2.1 Domain Types in SQL
char(n):
Fixed length character string, with user-specified
length n.
varchar(n): int:
Variable length character strings, with userspecified maximum length n. Integer (a finite subset of the integers that is machinedependent). Small integer (a machine-dependent subset of the integer domain type). Fixed point number, with user-specified precision of p digits, with d digits to the right of decimal point.
3
DataBase System
3.1 Introduction
History of SQL
IBM
Sequel (Specifying QUeries As Relational Expression, 1972) language developed as part of System R project at the IBM San Jose Research Lab SEQUEL (Structured English QUEry Language,1974) Renamed SQL (Structured Query Language, 1976) ANSI and ISO standard SQL: SQL-86, SQL-89 (SQL1) SQL-92 (SQL2) SQL:1999 (SQL3) SQL:2003 Commercial systems offer most, if not all, SQL-92 features, plus varying feature sets from later standards and special proprietary features.
real, double float(n): date:

precision: Floating point and double-precision floating point numbers, with machine-dependent precision. Floating point number, with user-specified precision of at least n digits. Dates, containing a (4 digit) year, month and date Time of day, in hours, minutes and seconds. Example: date ‘2005-7-27’
7
DataBase System
3.2.1 Domain Types in SQL
blob:
binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system). character large object -- object is a large collection of character data.
DDL allows the specification of not only a set of relations but also information about each relation, including:
The
Байду номын сангаас
schema for each relation.
The domain
time:

Example: time ‘09:00:30’
date plus time of day
time ‘09:00:30.75’
timestamp:

Example: timestamp ‘2005-7-27 09:00:30.75’
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
9
DataBase System
3.2.3 Creating Tables
Syntax
An SQL relation is defined using the create table command:
physical storage structure of each relation on disk.
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
5
DataBase System
Database Systems
DataBase System
Ch3 Basic SQL Query Language
3.1 Introduction 3.2 Setting Up the Database 3.3 Queries

3.3.1 Simple Select Statements 3.3.2 Subqueries
Interactive Data
Embedded
Integrity
SQL and dynamic SQL
Authorization (Security)
Transaction Control
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
smallint:
numeric(p,d):
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China
6
DataBase System
3.2.1 Domain Types in SQL
of values associated with each attribute.
Integrity constraints The
set of indices to be maintained for each relations.
Security and
The
authorization information for each relation.


Creating table CUSTOMERS in SQL Schema: CUSTOMERS ( cid, cname, city, discnt ) CREATE TABLE customers ( cid char(4) PRIMARY KEY , cname char(20) NOT NULL, city char(30), discnt int CHECK (VALUES > 0) ); Other tables: AGENTS ( aid, aname, city, percent ) PRODUCTS ( pid, pname, city, quantity, price )

clob:
User-defined domain:
Example: create domain money numeric(12, 2)
Su Xiangyang , School of Computer Science & Engineering , Xidian University , China

create table <table-name> ( [ [ <column-name1 data_type> [<column_constraint>] ,