Index

R

RAISERROR statement, 253

RAND function, 112

ranges, selecting data in, 55

RANK function, 99, 102

ProductCatalog database, 414, 423

raw data type, 533

RDBMS (Relational Database Management Systems)

see relational databases.

READ COMMITTED isolation level, 289

concurrency, 298

data consistency problems, 289

performance, 285

reading uncommitted data, 288

READ UNCOMMITTED isolation level, 287

concurrency, 298

data consistency problems, 287

Oracle, 288

performance, 285

read-only/read-write transactions, 287

transaction isolation levels, 286

READ_ONLY cursor type, 236

real data type

DB2, 535

Oracle, 534

SQL Server, 532

REFERENCES keyword

DB2, 321

InstantUniversity database, 518

Oracle, 317

ProductCatalog database, 398, 399, 401

SQL Server, 311

REFERENCING clause, 382

referential integrity, 358

see also integrity.

FOREIGN KEY constraint, 359, 433

triggers, 366

REGEXP keyword, 62

relational databases

abstraction layer, 5

attributes, 7

columns, 9

communicating with, 2

concurrency, 5

CONNECT command, 331, 511

connecting to

Access, 508

DB2, 505, 512

MySQL, 507

Oracle, 501, 511

SQL Server, 499, 512

creating, 330

Access, 331

InstantUniversity database, 514

ProductCatalog database, 395

data provider, 5

data types, 531

database access

DB2, 321

Oracle, 315, 316

SQL Server, 310

database engine, 5

database schemas, 8, 361

database-level triggers, 376

diagrammatic summary of terminology, 12

dropping, 331

entities, 7

key concepts, 7

normalization, 17, 18

performance-testing plan, 356

primary keys, 10

RDBMS introduced, 5

relationships between tables, 16

rows, 10

security, 301

tables, 8

transferring data between, 13

values, 8

relational integrity

see integrity.

relationships

between tables, 16

types, 19

many-to-many, 24

one-to-many, 20

primary keys, 17

ProductCatalog database, 390

removing rows

see DELETE statement.

REPEATABLE READ isolation level, 290

concurrency, 298

data consistency problems, 291

performance, 285

reading uncommitted data, 288

REPLACE statement

CREATE OR REPLACE, 206

CREATE VIEW command, 199

creating functions, 140

requirements

ProductCatalog database, 390

RESOURCE role

Oracle, 316, 320

responsibilities

see roles.

result sets, 247

Access, 247

DB2, 248

Oracle, 250

SQL Server, 247

retrieving data

see SELECT statement.

RETURN keyword, 252

RETURNING clause

DELETE statement, 80

UPDATE statement, 78

REVOKE command, 306

Access, 307

authorization, 304

DB2, 322, 323

MySQL, 326

Oracle, 316, 319, 320

SQL Server, 311

SQL99 syntax, 306

RIGHT function, 120

Access, 125

DB2, 123

MySQL, 124

Oracle, 122

SQL Server, 121

RIGHT OUTER JOIN keyword, 188

right outer joins, 183

RLIKE keyword, 62

role-based security, 453

bitmasked access mode, 455

creating tables

Permissions table, 465

PermissionsCategories table, 464

RolePermission table, 469

UserRoles table, 466

Users table, 460

encryption, 460

implementing, 454

permissions, 453, 464

reasons for implementing, 454

roles, 463

stored procedures, 470

creating permissions, 487

creating roles, 483

creating users, 471

deleting permissions, 488

deleting role, 484

deleting users, 474

loading permissions, 488

loading role description, 485

loading user details, 478

querying a roles permissions, 493

querying permissions, 490

querying roles, 485

querying users, 476

querying users permissions, 493

querying users roles, 496

setting a roles permissions, 492

updating permissions, 487

updating role, 484

updating users, 474

validating login, 480

users, 459

RolePermission table, 469

roles

see also role-based security.

authorization/permissions, 303

DB2, 322

Oracle, 316, 319

RDBMSs supporting roles, 303

SQL Server, 312, 313

SQL99 commands, 304

ROLLBACK command

autocommit mode, 272

automatic-transactions mode, 272

DB2, 282

MySQL, 283

Oracle, 279

SQL-99 syntax, 270

SQLServer, 275

transaction rollback, 267

Room table, 516

inserting data, 524

ROUND function, 114

rounding

accuracy of ORDER BY clause, 96

rounding numbers, 114

Access, 118

DB2, 115

MySQL, 117

Oracle, 116

SQL Server, 115

row-level triggers, 364

DB2, 382

Oracle, 375, 377

SQL Server, 367

ROWCOUNT keyword

ProductCatalog database, 408

rowid data type, 533

ROWNUM keyword, 100

ordering rows before sampling, 101

ProductCatalog database, 409, 411

ROWNUMBER function

ProductCatalog database, 414, 422, 423

rows, 8, 10

adding (see INSERT statement).

aggregated (see GROUP BY clause).

amending (see UPDATE statement).

counting (see COUNT function).

counting all rows (see ALL keyword).

counting unique rows (see DISTINCT keyword).

filtering

aggregated (see HAVING clause).

individually (see WHERE clause).

unique retrieval (see DISTINCT keyword).

grouping (see GROUP BY clause).

ordering (see ORDER BY clause).

removing (see DELETE statement).

retrieving (see SELECT statement).

sorting (see ORDER BY clause).