Index_S

sample number of rows, retrieving, 94

SAVEPOINT command, 274

DB2 transactions, 283

Oracle transactions, 280

RDBMSs supporting savepoints, 274

transaction savepoints, 270, 274

schema-level triggers, 376

schemas

see database schemas.

scope related cursor options, 236

scripts, SQL, 27

SCROLL cursor option, 236

scrollability related cursor options, 236

SCROLL_LOCKS cursor type, 236

SearchCatalog stored procedure, 445

DB2, 449

Oracle, 448

SQL Server, 445

security

Access, 307

DB2, 321

integrated security

SQL Server, 309

MySQL, 324

Oracle, 315

permissions, 302

RDBMS, 301

SQL Server, 308

stored procedures, 262

users, 301, 302

views, 193, 303

SELECT statement, 29–35

* character, 4

aliases, 35

basic query structure, 4

calculations on columns, 37

combining results from queries, 158

concatenating strings

Access, 45

DB2, 43

MySQL, 41

Oracle, 43

SQL Server, 40

DISTINCT keyword, 47

EXCEPT operator, 164

FROM keyword, 4

GROUP BY clause, 90

INTERSECT operator, 164

MINUS operator, 164

permissions

DB2, 322

object-level permissions, 305

Oracle, 318

SQL Server, 311

retrieving multiple columns, 34

retrieving sample number of rows

Access, 94

DB2, 98

MySQL, 97

Oracle, 100

SQL Server, 94

retrieving single column, 33

specifying database within SELECT, 31

subqueries, 148

UNION operator, 159

use with OVER keyword, 103

WHERE clause, 4, 50

self-joins

ProductCatalog database, 419

sequences

see autonumbering.

SERIALIZABLE isolation level, 292

concurrency, 298

performance, 285

reading uncommitted data, 288

SESSION optional parameter, 294

SET commands

AUTOCOMMIT, 273

IMPLICIT_TRANSACTIONS, 273

TRANSACTION, 286

set data type, 536

SET keyword

ProductCatalog database, 408

stored procedures, 219

UPDATE statement, 75

set membership

see IN keyword.

set operators, 158

SHOW ERRORS command, 207

ProductCatalog database, 434

triggers, 376

simple joins, 167

SIN function, 112

single column retrieval, 33

single data type, 537

single row INSERT, 68

smalldatetime data type, 533

smallint data type

DB2, 449, 535

MySQL, 536

Oracle, 534

ProductCatalog database, 400

SQL Server, 532

smallmoney data type, 532

SOME operator, 147

sorting data

see ORDER BY clause.

spaces in names

use of quotes/square brackets, 32

special characters

see operators; pattern matching.

sprocs

see stored procedures.

SQL (Structured Query Language)

background, 3

basic statements, DML, 3

description, 1

dialects, 6

pronunciation, 1

querying database, 4

RDBMS variations, 6

entering SQL, 499

executing SQL, 499

SQL scripts, 27

SQL*Plus, Oracle, 501

SQL99 standard, 3

wrapping, 7

SQLCODE variable, 258

SQLEXCEPTION state, 259

SQLSTATE variable, 258

SQLWARNING state, 259

SQRT function, 112

START TRANSACTION statement, 269

statement-level permissions, 304

DB2, 324

Oracle, 320

REVOKE command, 306

SQL Server, 314

statement-level triggers, 364

DB2, 382

Oracle, 375

SQL Server, 367

statements, 26

see also keywords.

ALTER PROCEDURE, 216

ALTER SESSION, 293

ALTER TABLE, 340

ALTER TRIGGER, 370

ALTER VIEW, 199

BEGIN, 272

BEGIN TRANSACTION, 275

basic SQL statements, 3

CASE ... WHEN, 226

COMMIT, 271

CONNECT, 331

CREATE DATABASE, 330

CREATE FUNCTION, 137

CREATE OR REPLACE VIEW, 199

CREATE PACKAGE, 250

CREATE PROCEDURE, 204

CREATE ROLE, 319

CREATE TABLE, 331

CREATE TRIGGER, 365

CREATE VIEW, 194

DELETE, 78

DROP TABLE, 331

DROP TRIGGER, 365

DROP VIEW, 201

executing SQL statements, 499

Access, 508

DB2, 505

MySQL, 507

Oracle, 501

SQL Server, 499

GRANT, 304

INSERT, 67

keywords, benefit of, 26

RAISERROR, 253

REVOKE, 304, 306

ROLLBACK, 270

SAVEPOINT, 274

SELECT, 29

SET TRANSACTION, 286

SHOW ERRORS, 376

START TRANSACTION, 269

substituting using triggers, 366

TRUNCATE, 80, 332

UPDATE, 75

STATIC cursor type, 236

stored procedures, 203

Access, 215

ALTER PROCEDURE command, 216

coding, 225

conditional statements, 226, 228

creating, 204

DB2, 207, 449

DECLARE keyword, 217

DELETE statement, 441

DROP PROCEDURE command, 216

error handling, 252

executing, 203

functions, 482

INSERT statement, 438

loops, 233

MySQL, 203, 389

naming conventions, 457

Oracle, 206, 448

packages, 457

parameters, 220

permissions

DB2, 321, 324

MySQL, 326

Oracle, 316, 317, 320

SQL Server, 308–314

ProductCatalog database, 432–434, 436–437, 445

returning result sets, 247

returning values, 481, 482

role-based security, 453

creating, 470

granting permissions to roles, 492

permissions related sprocs, 486

role related sprocs, 483

user related sprocs, 471

security, 262

SQL Server, 205, 445

syntax, 204

triggers, 351, 363

UPDATE statement, 432

variables, 217

WHILE loop, 233

STR function

Access, 135

SQL Server, 134

numeric value conversion, 40

string data types, 14

Access, 537

string manipulation

see also casting.

Access, 120, 125

DB2, 120, 123

MySQL, 120, 124

Oracle, 120, 122

SQL Server, 120, 121

string manipulation functions, 119

CHARINDEX, 120

CONCAT, 41

INSTR, 120

LEFT, 120

LEN/LENGTH, 120

LOWER, 120

MID, 120

POSSTR, 120

RIGHT, 120

STR, 40

SUBSTR/SUBSTRING, 120

TO_CHAR, 134

TO_DAYS, 131

TO_NCHAR, 134

TO_NUMBER, 135

UPPER/UCASE, 120

strings

empty string, 119

Student table, 519

inserting data, 525

StudentExam table, 522

inserting data, 526

subqueries, 147

ALL operator, 156

ANY operator, 157

calculated columns, 148

correlated subqueries, 147

EXISTS operator, 155

IN keyword, 147

JOIN keyword, 151

multiple row returned, 152

MySQL, 151

non-correlated subqueries, 147, 154

single row returned, 151

using aliases with subqueries, 150

WHERE clause, in, 151

SUBSTR/SUBSTRING functions, 120

DB2, 123

MySQL, 124

Oracle, 122

SUM function, 87

using with DISTINCT keyword, 87

NULL, treatment of, 88

using with ALL keyword, 87

summarizing data

see aggregate functions.

SYS/SYSTEM users, 315

system administrator role, 303