Index_C

calculated columns, 37

expressions containing, 108, 109

modifying data with views, 198

subqueries, 148

UNION operator, 159

calculations in SQL, 107

CALL keyword

DB2, 203

executing stored procedures, 434

Oracle, 203

Cartesian product, 167

CASE ... WHEN statement, 226, 227

case-sensitivity

pattern matching, 58

use of quotes/square brackets, 32

CAST function

DB2, 115

MySQL, 117

Oracle, 116

RDBMSs supporting, 136

SQL Server, 115

casting

casting functions

CONVERT, 136

DEC/DECIMAL, 136

DOUBLE, 136

FLOAT, 136

generic casting functions, 136

INT/INTEGER, 136

TO_NUMBER, 135

casting to numbers, 135

Access, 136

DB2, 136

MySQL, 136

Oracle, 135

SQL Server, 135

casting to strings, 133

Access, 135

DB2, 134

MySQL, 135

Oracle, 134

SQL Server, 134

Category table

inserting rows, 403

ProductCatalog database, 398

CEIL/CEILING functions, 114

Access, 118

DB2, 115

MySQL, 117

Oracle, 116

SQL Server, 115

changing rows

see UPDATE statement.

CHAR function, 44, 134

character data types, 14, 531

Access, 537

DB2, 534

MySQL, 536

Oracle, 533

SQL Server, 532

characters, special

see operators; pattern matching.

CHARINDEX function, 120, 121

CHECK constraint, 338

triggers, 366

Class table, 517

inserting data, 525

CLOB data type

DB2, 534

Oracle, 533

CLOSE keyword

using cursors, 238

code downloads

Apress web site, 27

coding

stored procedures, 225

column aliases, 35

columns, 9

averages (see AVG function).

calculations on columns, 37

default INSERT values, 69

maximum value (see MAX function).

minimum value (see MIN function).

retrieving all columns, 29

retrieving multiple columns, 34

retrieving single column, 33

totals (see SUM function).

Command Center, DB2

executing SQL statements, 504

transaction options, 282

commands

see statements.

comments, 27

Access, 28

MySQL, older versions, 28

COMMIT command

autocommit mode, 271

automatic-transactions mode, 272

committing transactions, 271

DB2, 282

making changes visible, 273

MySQL, 283

Oracle, 279

SQLServer, 275

Commit Coordinator

two-phase commit, 298

comparison operators, 51, 108

CONCAT function, 41

DB2, 43

MySQL, 125, 135

ProductCatalog database, 444

concatenating strings

|| operator, 41

Access, 45

ANSI mode, 41

CONCAT function, 41

DB2, 43

MySQL, 41

numeric value conversion, 40

Oracle, 43

SQL Server, 40

concurrency, 5, 285

see also transaction isolation levels.

conditional statements, 50

pattern matching (LIKE), 57

ranges (BETWEEN), 55

set of values (IN), 56

stored procedures, 226

DB2, 231

Oracle, 230

SQL Server, 228

WHERE clause, 50

combining WHERE clauses, 54

comparison operators, 51

NULLs, 63

conditional triggers

DB2, 385

Oracle, 378

CONNECT command, 331, 511

DB2, 322, 323, 505, 512

Oracle, 316, 317, 511

SQL Server, 512

connection, closing, 512

consistency

see also transaction isolation levels.

reading committed data, 289

reading uncommitted data, 287

transactions, 268

CONSTRAINT keyword

InstantUniversity database, 520

referential integrity, 359

constraints

ALTER TABLE command, 341

CHECK constraint, 338

FOREIGN KEY constraint, 23, 331, 358

InstantUniversity database, 518

integrity of data tables, 11

NOT NULL constraint, 333

InstantUniversity database, 515

PRIMARY KEY constraint, 11, 335

InstantUniversity database, 515

simulating using triggers, 366

UNIQUE constraint, 16, 337

CONTAINS keyword, 442

CONTAINS SQL keyword, 141

CONTINUE error handler, 259

CONTROL permission, 321, 323

CONVERT function, 136

copying tables, 339

correlated subqueries, 147

ProductCatalog database, 426

COS function, 112

COUNT function, 84

keywords used in, 84

NULL, treatment of, 84

ProductCatalog database, 428, 430

using with ALL keyword, 84

using with DISTINCT keyword, 84

counter data type, 537

Course table, 515

inserting data, 524

CREATE DATABASE command, 330

InstantUniversity database, 514

CREATE FUNCTION command, 137

CREATE PACKAGE command, 250

Oracle example, 448

CREATE PROCEDURE command, 204

Access, 215

DB2, 214

Oracle, 206

ProductCatalog database, 433

role-based security 470–496

SQL Server, 205, 445

CREATE ROLE command

Oracle, 319

CREATE statements

Oracle, 315

SQL Server, 314

statement-level permissions, 304

CREATE TABLE command, 331

CHECK constraint, 338

copying tables, 340

FOREIGN KEY constraint, 359

InstantUniversity database

Class table, 518

Course table, 516

Enrollment table, 521

Exam table, 520

Professor table, 515

Room table, 516

Student table, 519

StudentExam table, 523

MySQL, 283

PRIMARY KEY constraint, 336

ProductCatalog database

Category table, 398

Department table, 396

Product table, 399

ProductCategory table, 401

role-based security

Permissions table, 465

PermissionsCategories table, 464

RolePermission table, 469

Roles table, 463

UserRoles table, 467

Users table, 461

UNIQUE constraint, 337

CREATE TRIGGER command

DB2, 385

Oracle, 377, 378

SQL99 syntax, 365

CREATE VIEW command, 194

Access, 193

DB2, 197

MySQL, 193

Oracle, 196

REPLACE statement, 199

SQL Server, 195

WITH CHECK OPTION clause, 198

CROSS JOIN operator, 169

currency data types, 531

Access, 537

SQL Server, 532

cursors, 234

cursor options/types , 236–237

DB2, 236, 244

declaring, 219, 234

implicit cursors, 238

lock type options, 236

opening, 237

Oracle, 234, 242

returning result sets from procedures, 247

role-based security, 477

scope related options, 236

scrollability related options, 236

SQL Server, 235, 239

Transact-SQL syntax, 235

options, 236

CURSOR_ALREADY_OPEN exception, 256