1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
# Unit 5
Labs 2: Tables, Views, SQL Queries and Triggers
## Section 1: SQL as a Data Definition Language (DDL)
Read Chapter 6: Introduction to SQL
* Origins of the SQL standard
* The SQL Environment
* Defining a database in SQL
ANSI SQL was first published in 1986.
Updated in:
* 1989
* 1992
* 1999
* 2003
* 2006
* 2008
Concepts were first articulated in 1970 by E.F. Codds paper
"A Relational Model of Data for Large Shared Data Banks".
IBM devs started to build System R which was to demo an
implementation of a relational model in a dbms.
They used a language named "Sequel" which was renamed SQL.
SQL/DS: the first relational DBMS.
DB2: introduced in 1983
Original purpose of SQL standard:
1. specify syntax and semantics of SQL DDL and DML
2. define data structures and basic operations for designing, accessing, maintaining, controlling and protecting a SQL database.
3. portability between database definition and application modules between DBMS's.
4. Specify minimal and complete standards
5. Extension point for handling referential integrity, transaction management, user defined functions, join operators and character sets.
A standard provides
* Reduced training costs
* Productivity
* Application portability
* Application longevity
* Reduced dependence on a single vendor
* Cross system communication
The SQL Environment
SQL is used to:
* create tables
* translate user requests
* maintain data dictionary and system catalog
* update and maintain the tables
* establish security
* carry out back and recovery procedures
> RDBMS: a database management system that manages data as a collection of tables in which all data relationships are represented by common values in related tables.
Each database is contained in a catalog, which describes any object that is a part of the database.
> Catalog: A set of schemas that, when put together, constitute a description of a database.
> Schema: A structure that contains descriptions of objects created by a user, such as base tables, views and constraints as part of a database.
Each catalog must also contain an information schema.
* tables
* views
* attributes
* privileges
* constraints
* domains
SQL commands can be classified into three types:
1. DDL: Data definition language
2. DML: Data manipulation language
3. DCL: Data control language
> DDL: Commands used to define a database, including those for creating, altering and dropping tables and establishing constraints.
> DML: Commands used to maintain and query a database, including those for updating, inserting, modifying and querying data.
> DCL: Commands used to control a database, including those for administering privileges and committing (saving) data.
Each DBMS has a defined list of data types that it can handle.
* numeric
* string
* date/time
* graphic data
* spatial data
* image data
Defining a database in SQL
```sql
CREATE SCHEMA database_name;
AUTHORIZATION owner_user mokha;
```
To define a portion of a database that a particular user owns.
```sql
CREATE SCHEMA
```
To define a new table and columns
```sql
CREATE TABLE
```
To define a new view
```sql
CREATE VIEW
```
## Section 2: SQL as a Data Manipulation Language (DML)
Read Chapter 6: Introduction to SQL
* "Inserting, updating and deleting data"
* "Internal schema definition in RDBMS's"
* "Processing single tables"
Terms:
* scalar aggregate
* vector aggregate
* virtual table
* dynamic view
* materialized view
on 3: Advanced SQL and Data Control Commands
Read Chapter 7: Advanced SQL
* all sections
|
