SQL DDL - PowerPoint PPT Presentation

1 / 34
About This Presentation
Title:

SQL DDL

Description:

Join combining columns (fields) from multiple tables. Tables can be real or virtual ... How does this differ from inner join? Employees With No Paychecks ... – PowerPoint PPT presentation

Number of Views:47
Avg rating:3.0/5.0
Slides: 35
Provided by: john217
Category:
Tags: ddl | sql | join

less

Transcript and Presenter's Notes

Title: SQL DDL


1
SQL DDL
2
DDL Data Definition Language
  • CREATE TABLE
  • CREATE INDEX
  • ALTER TABLE
  • DROP TABLE
  • DROP INDEX
  • There are about 20 variations of the CREATE,
    ALTER and DROP commands

3
Create Table Syntax
  • CREATE TABLE table_name (

column_name_1 column_name_2 column_name_N table_at
tributes )
data_type data_type data_type
attributes , attributes , ... attributes ,
Note SQL is case insensitive and spacing
insensitive, but very sensitive to structure,
keywords, delimiters, etc.
4
Create Table Syntax
  • CREATE TABLE table_name (

column_name Use letters digits Avoid special
symbols First Name
data_type
attributes ,
5
Create Table Syntax
  • CREATE TABLE table_name (

column_name
data_type Char(X) Varchar(X) Int Decimal(X,Y) Mon
ey Datetime
attributes ,
6
Create Table Syntax
  • CREATE TABLE table_name (

column_name
data_type
attributes , Null or Not Null Default
Values Primary Key References (i.e.,
Foreign Key) Check Constraint (i.e.,
Validation Rule)
7
Sample Database
Note Parents before children
8
Employee Table
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null

What is the difference between Char(20) and
Varchar(20)? What is the difference between
Varchar(20) and Varchar(MAX)?
9
Employee Table - Simple PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Varchar(10) Primary Key

By default, primary keys are clustered.
10
Employee Table - Simple PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Primary Key

By default, primary keys are clustered.
11
Employee Table - Simple PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Primary Key
    Clustered

By default, primary keys are clustered.
12
Employee Table - Simple PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Primary Key
    Nonclustered

By default, primary keys are clustered. You can
request a non-clustered PK if you want to use
clustering on one of your secondary indices.
13
Employee Table - Simple PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Primary Key

User-supplied unique integer
14
Employee Table - Autonumber PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Identity Primary
    Key

DB-supplied unique integer 1, 2, 3, ...
15
Employee Table - Autonumber PK
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Identity(1000,10)
    Primary Key

DB-supplied unique integer 1000, 1010, 1020, ...
16
Employee Table - Check Constraint
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Identity Primary
    Key ,
  • PayType Char(1) Not Null Default
    'H'
  • Check (PayType 'H' Or PayType
    'F')

17
Employee Table - Check Constraint
  • CREATE TABLE tblEmployee (
  • FirstName Varchar(20) Not Null ,
  • MiddleName Varchar(20) Null ,
  • LastName Varchar(30) Not Null ,
  • EmployeeID Int Identity Primary
    Key ,
  • PayType Char(1) Not Null Default
    'H'
  • Check (PayType 'H' Or PayType
    'F') ,
  • PayRate Money Not Null
  • Check (PayRate gt 0) )

18
Check Number of Characters
  • LEN(X) counts number of characters in X
  • LastName Varchar(30) Not Null
  • Check ( LEN(LastName)gt2 ) ,
  • Zip Char(5) Null
  • Check ( Len(Zip)5 OR Zip IS NULL )

19
Check Number of Characters
  • LEN(X) counts number of characters in X
  • LastName Varchar(30) Not Null
  • Check ( LEN(LastName)gt2 ) ,
  • Zip Varchar(10) Null
  • Check ( Len(Zip)5 OR LEN(Zip)10
  • OR Zip IS NULL )

20
Paycheck Table - Foreign Key
  • CREATE TABLE tblPaycheck (
  • PayDate DateTime Not Null ,
  • EID Int Not Null
  • References tblEmployee(EmployeeID)

21
Paycheck Table - Simple Check
  • CREATE TABLE tblPaycheck (
  • PayDate DateTime Not Null ,
  • EID Int Not Null
  • References tblEmployee(EmployeeID) ,
  • NetPay Money Not Null Check (NetPay gt 0)
    ,
  • GrossPay Money Not Null Check (GrossPay gt 0)

22
Paycheck Table - Composite PK
  • CREATE TABLE tblPaycheck (
  • PayDate DateTime Not Null ,
  • EID Int Not Null
  • References tblEmployee(EmployeeID) ,
  • NetPay Money Not Null Check (NetPay gt 0)
    ,
  • GrossPay Money Not Null Check (GrossPay gt 0)
    ,
  • Primary Key ( PayDate , EID )

By default, primary keys are clustered.
23
Paycheck Table - Composite PK
  • CREATE TABLE tblPaycheck (
  • PayDate DateTime Not Null ,
  • EID Int Not Null
  • References tblEmployee(EmployeeID) ,
  • NetPay Money Not Null Check (NetPay gt 0)
    ,
  • GrossPay Money Not Null Check (GrossPay gt 0)
    ,
  • Primary Key Clustered ( PayDate , EID )

By default, primary keys are clustered.
24
Paycheck Table - Composite PK
  • CREATE TABLE tblPaycheck (
  • PayDate DateTime Not Null ,
  • EID Int Not Null
  • References tblEmployee(EmployeeID) ,
  • NetPay Money Not Null Check (NetPay gt 0)
    ,
  • GrossPay Money Not Null Check (GrossPay gt 0)
    ,
  • Primary Key Nonclustered ( PayDate , EID )

You can request a non-clustered PK if you want to
use clustering on one of your secondary indices.
25
Paycheck Table - Composite Check
  • CREATE TABLE tblPaycheck (
  • PayDate DateTime Not Null ,
  • EID Int Not Null
  • References tblEmployee(EmployeeID) ,
  • NetPay Money Not Null Check (NetPay gt 0)
    ,
  • GrossPay Money Not Null Check (GrossPay gt 0)
    ,
  • Primary Key ( PayDate , EID ) ,
  • Check (NetPay lt GrossPay) )

26
Create Index Syntax
  • CREATE INDEX index_name
  • ON table_name (
  • column_name_1 order ,
  • column_name_2 order ,
  • column_name_N order )
  • order ASC or DESC (ASC is default)

27
Create Index Syntax
  • CREATE NONCLUSTERED INDEX index_name
  • ON table_name (
  • column_name_1 order ,
  • column_name_2 order ,
  • column_name_N order )
  • By default, secondary indices are non-clustered.

28
Create Index Syntax
  • CREATE CLUSTERED INDEX index_name
  • ON table_name (
  • column_name_1 order ,
  • column_name_2 order ,
  • column_name_N order )
  • You can request a clustered secondary index if
    the
  • table does not already have a clustered index.

29
Create Index Syntax
  • CREATE INDEX index_name
  • ON table_name (
  • column_name_1 order ,
  • column_name_2 order ,
  • column_name_N order )
  • By default, secondary indices allow duplicate
    values.

30
Create Index Syntax
  • CREATE UNIQUE INDEX index_name
  • ON table_name (
  • column_name_1 order ,
  • column_name_2 order ,
  • column_name_N order )
  • You can create a unique index, if needed.
  • This would allow 11 relationships to be handled.
    (FK)

31
Secondary Indices
  • CREATE INDEX idxNameSort
  • ON tblEmployee ( LastName , FirstName )
  • CREATE INDEX idxSalarySort
  • ON tblEmployee ( PayRate DESC )
  • CREATE INDEX idxHighPay
  • ON tblPaycheck ( PayDate, NetPay DESC )

32
Alter Table Syntax
  • ALTER TABLE table_name
  • ADD column_name data_type attribute
  • DROP COLUMN column_name
  • ALTER COLUMN column_name new_data_type
  • new_attribute

33
Employee Table Revisions
  • ALTER TABLE tblEmployee
  • ADD Gender Char(1) Not Null Default 'M'
  • Check (Gender 'F' Or Gender 'M' )
  • ALTER TABLE tblEmployee
  • DROP COLUMN MiddleName

34
Drop Syntax
  • DROP TABLE table_name
  • DROP INDEX table_name.index_name
  • DROP TABLE tblPaycheck
  • DROP INDEX tblEmployee.idxSalarySort
Write a Comment
User Comments (0)
About PowerShow.com