Index

A B C D E F G H-I J K L M N O P Q R S T U V W-X-Y-Z

Symbols and Numerics

[top]

[] (brackets) wildcard, 58-59

, (commas) characters, 16

|| (concatenation) operator, 63

@ character, 193

^ character, 59

@@ERROR variable, 204

@@IDENTITY global variable, 195

_ (underscore) wildcard, 57-58

% (percent sign) wildcard, 54

| (pipe) symbol, 247

A

[top]

ABS() function, 79

Access, using, 235

accessing cursor data, 208-210

adding rows to tables, 250

advanced data filtering, 43

advanced joins, creating, 125-128, 132-134

aggregate functions, 81

ALL argument, 89

AVG(), 82

combining, 90

COUNT(), 84-85

DISTINCT arguments, 89

joins, 132-133

MAX(), 85

MIN(), 86-87

SUM(), 87-88

aliases, 66-67, 91

alternate uses, 67

names, 68

tables, 125-128, 132

ALL argument, aggregate functions, 89

ALL clause, 95

Allaire ColdFusion, 233-234

ALTER TABLE statement, 169-170

CHECK constraints, 219

CONSTRAINT syntax, 215-217

syntax, 248

AND operator, 43-44

ANSI SQL, 11

applications

data filtering, 36

ODBC client, 245

portable code, 72

working in SQL, 233

Access, 235

Allaire ColdFusion, 233-234

DB2, 235

Microsoft ASP, 236

arguments

aggregate, 90

DISTINCT, 89

AS keyword, 66, 126-129, 134

ASC keyword, 33

ASP (Microsoft), 236-237

authentication, 224

authorization, 224

AVG() function, 82

DISTINCT argument, 89

NULL values, 83

B

[top]

BETWEEN operator, 40

BINARY datatype, 258

BIT datatype, 256

brackets ([]) wildcard, 58-59

C

[top]

calculated fields, 61-63, 66

mathematical calculations, 68-70

subqueries, 108-109

views, 184

Cartesian Product, 117-119

cascading delete feature, 217

case-sensitivity

sort orders, 33

SQL statements, 16

CFQUERY CFQUERY tag pair, 234

CHAR string datatype, 255

check constraints, 218

checking

for no value, 40

range of values, 40

clauses, 28

ALL, 95

HAVING, 96

ordering, SELECT statements, 101

positioning, 42

WHERE

combining, 43, 46

joins, 117

operators, 37

positioning, 37-39

clients, formatting, 62

CLOSE statements, closing cursors, 211

closing cursors, 211

code, commenting, 72, 195

codes, portable, 72

ColdFusion (Allaire), 233-234

columns, 7. See also fields

aliases, 66-68

assigning new values, 156

breaking data correctly, 8

derived, 68

foreign keys, 216

GROUP BY clause, 95

Identity fields, 195

INSERT SELECT statements, 151

inserting omitting columns, 149

list (using INSERT statements), 148

naming, fully qualified, 117

NULL value, 40, 166

padded spaces, 64

primary keys, 9-10, 167

retrieving, 14

all, 18

multiple, 16

unknown, 18

separating names in queries, 16

sorting data, 30

by mulitple columns, 29-30

by non-selected columns, 29-31

descending on multiple columns, 33

specifying by relative position, 96

updating multiple, 156

values, deleting, 157

combined queries, 137

creating, 138-139

sorting results, 143

UNION statement rules, 140

combining

aggregate functions, 90

WHERE clauses, 43, 46

commas (,), 16

commenting code, 23-24, 72, 195

COMMIT statements, 201, 248

commits, 199

comparing datatypes, 258

compatibility

datatype, 8

operator, 37

complex joins, views, 179

concatenating fields, 62

concatenation operators, 63

conditions (joins), 134

configuring ODBC data sources, 245

CONSTRAINT syntax, ALTER TABLE statements, 217

constraints, 213-214

cautions, 214

check, 218

foreign keys, 216

speed, 223

syntax, 215

unique, 217

copying

data, table to table, 152

tables, 152-153, 164

COS() function, 79

COUNT() function, 82-85, 90, 133

COUNT* subquery, 108

CREATE INDEX statement, 221, 248-249

CREATE TABLE statement, 163

CONSTRAINT syntax, 215

DEFAULT keyword, 168

required information, 164

syntax, 249

CREATE VIEW statement, 179, 249

creating

calculated fields, 61-63, 66

mathmatical calculations, 68-70

subqueries, 108-109

ColdFusion pages, 234, 242

combined queries, 138-139

cursors, 207

groups, 94-95

indexes, 221, 248

joins, 116

advanced, 125-126, 132, 134

self, 126-127

savepoints, 203

search patterns, 53

stored procedures, 188, 191-194, 249

tables, 163-165, 249

triggers, 223

views, 179

reusable, 180

rules and restrictions, 177

uses, 177

CROSS JOIN, 119

CT statement results, 21

currency datatypes, 256

cursors, 205

accessing data, 208-210

closing, 211

deallocating resources, 211

limitations, 206

opening, 208

options, 206

using, 207

Web-based applications, 206

Customers table, 227

D

[top]

data

breaking correctly, 8

calculated fields, 61

Cartesian Product, 119

consistency with stored procedures, 189

copying, table to table, 152

deleting, 157, 160

filtering, 35

advanced, 43

application level, 36

checking against single values, 38

checking for a range of values, 40

checking for nonmatches, 38-39

indexes, 221

views, 183

wildcards, 53

formatting, 17

grouping, 93, 99

filtering groups, 96

GROUP BY clause, 99

nesting, 95

ORDER BY clause, 100

inserting, 145-146, 219

joins, 115

manipulation functions, 71

date and time, 73, 76, 78

numeric, 79

multiple occurences, 114

numeric functions, 73

ODBC database integration, 245

referential integrity, 213

retrieved

inserting, 150

reformatting with views, 180-183

retrieving, 13, 81, 84-85

all columns, 18

multiple columns, 16

security, 224

sorting, 27, 99

by column position, 30

by mulitple columns, 29-30

by non-selected columns, 29-31

descending on multiple columns, 33

specifying direction, 31

summarizing, 81, 84-85

text functions, 73

transaction processing, 198

unsorted, 15

updating, 155-156, 160

data and time datatypes, 257

Database Management System. See DBMS

databases, 5-6

constraints, 214

check, 218

syntax, 215

unique, 217

cursors

accessing data, 208-210

closing, 211

creating, 207

opening, 208

using, 207

dropping objects, 250

filtering, 36

indexes

cautions, 221

creating, 221

searching, 220

ODBC, 245

order entry systems, 226

scalability, 115

schemas, 7

search patterns, 53

security, 224

software, 6

subqueries, 103

tables, 6

creating, 163, 249

triggers, 222

transaction processing, 197

datatypes, 8, 254

binary, 258

compatibility, 8

currency, 256

data and time, 257

defining, 216, 219

numeric, 256

reasons for use, 253

string, 254

date and time functions, 73, 76-78

DATE dataype, 257

DATEPART() function, 77

DATETIME datatype, 257

DB2, 235

DBMS (Database Management System), 6

accidental table deletion, 172

affecting sort order, 28

cascading delete feature, 217

constraints, 214

cursor options, 206

datatypes, 254

DB2, 235

functions, 71

indexes, 221

interactive tools, 115

joins, 121

LIKE operator, 54

security mechanisms, 224

separating statements, 15

specific operators, 41

SQL extensions, 11

stored procedures, 190

transaction processing, 200

triggers, 223

TRIM functions, 66

UNION keyword, 139

UNION statements, 143

UPDATE statements, 155

user-defined datatypes, 219

views

creating, 176

rules and restrictions, 178

DECIMAL datatype, 256

DECLARE statements

creating, cursors, 207

stored procedures, 193

DEFAULT keyword, 168

defining

datatypes, 216, 219

foreign keys, 216

ODBC Data Sources, 246

primary keys, 10, 215

DELETE FROM statement, 158

DELETE statement, 157

FROM keyword, 159

guidelines, 160

rollbacks, 201

security privileges, 158

syntax, 250

transaction processing, 199

triggers, 222

TRUNCATE TABLE statement, 160

WHERE clause, 158

deleting

cascading delete feature, 217

column values, 157

data, 157, 160

rows, 250

tables, 171

preventing accidental deletion, 172

derived columns. See aliases

DESC keyword, 31

dictionary sort order, 33

displaying statement output, 14

DISTINCT argument

AVG() function, 89

COUNT() function, 90

downloading ready-to-use data files, 230

DROP statement, syntax, 250

DROP TABLE statement, 171

dropping database objects, 250

duplicate rows, eliminating, 141-142

E

[top]

eliminating duplicate rows, 141-142

EQUIJOIN, 120

establishing primary keys, 10

EXCEPT statements, 143

EXECUTE statements, 190-191

executing stored procedures, 190-191

EXP() function, 79

explicit commits, 201

extensions, 11

F

[top]

FETCH statement, 208, 210

fields, 62

aliases, 66

alternate uses, 67

names, 68

calculated, 61-63, 66

mathematical calculations, 68-70

subqueries, 108-109

views, 184

concatenating, 62

filtering

by subquery, 104-105

data, 35

advanced, 43

application level, 36

checking against single values, 38

checking for a range of values, 40

checking for nonmatches, 38-39

indexes, 221

views, 183

groups, 96

wildcards, 53

fixed length strings, 254

FLOAT datatype, 256

foreign keys, 159, 216

benefits, 217

defining, 216

formatting

clients, 62

data, 17

retrieved data with views, 180-183

servers, 62

statements, 165

subqueries, 106

FROM clause, 116

FROM keyword

DELETE statement, 159

UPDATE statement, 157

full outer joins, 132

fully qualified column names, 117

functions, 71-72, 75

aggregate, 81

AVG(), 82

combining, 90

COUNT(), 84-85

DISTINCT, 89

MAX(), 85

MIN(), 86-87

SUM(), 87-88

data manipulation, 71

date and time, 73, 76-78

numeric, 73, 79

problems, 71

system, 73

text, 73

text manipulation, 74

SOUNDEX() function, 75

UPPER() function, 73

G

[top]

global variables, @@IDENTITY, 195

GRANT statements, 224

graphical interfaces, 115

GROUP BY clause. See also ORDER BY clause

creating groups, 94-95

relative position of columns, 96

grouping

data, 93, 99

GROUP BY clause, 99

ORDER BY clause, 100

operators, 46

groups

creating, 94-95

filtering, 96

nested, 95

H-I

[top]

HAVING clauses, 96

Identity fields, 195

implementing transactions, 200

IN operator, 47

advantages, 49

combining with NOT operator, 51

including duplicate rows, 141-142

indexes, 220

cautions, 221

creating, 221, 248

revisiting, 222

searching, 220

inner joins, 120, 129

INSERT SELECT statement, 151

SELECT INTO statement comparison, 152

syntax, 251

INSERT statement

columns lists, 148

INTO keyword, 146

omitting columns, 149

partial rows, 149

rollbacks, 204

safety, 146

security privileges, 145

syntax, 250

table layout, 147

transaction processing, 199

triggers, 222

VALUES, 148

inserting

columns, 149

data, 145-146

INTO keyword, 146

retrieved, 150

rows, 146

multiple, 152

partial, 149-150

INT datatype, 256

interactive DBMS tools, 115

INTERSECT statements, 143

INTO keyword, 146

IS NULL clause, 41

J

[top]

joining tables, 113, 115

aliases, 125-128, 132

multiple, 121-123

natural joins, 129

performance concerns, 121

joins

aggregate functions, 132-133

Cartesian Product, 117

conditions, 134

creating, 116

advanced, 125-128, 132-134

self, 126-128

CROSS, 119

DBMS interactive tools, 115

EQUIJOIN, 120

Inner, 120

natural, 129

outer, 129, 132

full, 132

types, 132

performance considerations, 121

pros, 115

self, instead of subqueries, 128

types, 126

views, 179

WHERE clause, 117-119

K

[top]

keys

foreign, 216

primary, 9

keywords, 13

AND, 44

AS, 66, 126-129, 134

DEFAULT, 168

FROM, 157

IN, 49

INTO, 146

NOT, 50

OR, 45

REFERENCES, 217

UNION, 138

UNIQUE, 218

L

[top]

languages, SQL, 10, 259-263

LEFT keyword, 131

LIKE operator, 53-54

limiting SELECT statement results, 20-23

local variables, @ character, 193

LONG RAW datatype, 258

LTRIM() function, 66

M

[top]

managing transactions, 199

COMMIT statements, 201

ROLLBACK statements, 201

SAVEPOINT statements, 202

manipulating tables, 163

complex structure changes, 170

deleting, 171

manipulation functions, 71

date and time, 73, 76-78

numeric, 79

text, 74

mathematical calculations, performing, 68-70

mathematical operators, 69

MAX() function, 82, 85-86, 90

Microsoft Access. See Access

Microsoft ASP, 236-237

Microsoft Query, 238

MIN() function, 82, 86

DISTINCT argument, 90

non-numeric data, 87

multiple tables, joining, 123

multiple rows, inserting, 152

MySQL, 240-241

N

[top]

names, aliases, 126

naming

aliases, 68, 91

columns, fully qualified, 117

indexes, 221

tables, renaming, 172

natural joins, 129

navigating tables, 205

NCHAR string datatype, 255

nested groups, 95

NOT NULL values, 166

NOT operator, 49-51, 60

NULL values, 40

AVG() functions, 83

empty strings, 167

primary keys, 167

specifying, 167

tables, 166

numeric datatypes, 256

numeric functions, 73

numeric manipulation functions, 79

NVARCHAR string datatype, 255

O

[top]

obtaining sample tables and scripts, 229

ODBC

ASP, 236

data sources, 245-246

dates, 257

versions, 245

omitting columns, 149

OPEN CURSOR statements, 208

OPEN statements, opening cursors, 208, 211

opening cursors, 208, 211

operators, 38

AND, 43-44

BETWEEN, 40

compatibility, 37

concatenation, 63

DBMS specific, 41

grouping related, 46

HAVING clause, 96

IN, 47-49

LIKE, 53

mathematical, 69

NOT, 49-51

OR, 45

predicates, 54

WHERE clause, 37

OR operator, 45

Oracle

commits, 202

copying data between tables, 153

cursors

closing, 211

creating, 208

retrieving data, 208

date and time manipulation functions, 77

savepoints, 203

stored procedures, 191-194

triggers, 223

Oracle 8, 241

Oracle Express, 241

ORDER BY clause, 99

positioning, 29

SELECT statement, 28

UNION statements, 142

order entry systems, 226

order of evaluation

parenthesis, 47

WHERE clauses, 46

ordering clauses, SELECT statements, 101

OrderItems table, 228-229

Orders table, 228

outer joins, 129

full, 132

syntax, 130

types, 132

overwriting tables, 165

P

[top]

padded spaces, 64

parenthesis, 46-47

partial rows, inserting, 149-150

percent sign (%) wildcard, 54

performance

indexes, 221

joins, 121

SQL experimentation, 123

subqueries, 108

UNION statements, 140

views, 177

PHP, 242

PI() function, 79

pipe (|) symbol, 247

placeholders. See savepoints

portable code, 72

positioning

sorting data by column position, 30

WHERE clause, 37-39

PostgreSQL, 243

predicates, 54

primary keys, 9-10

Customer table, 227

defining, 10, 215

NULL values, 167

OrderItems table, 229

Orders table, 228

Products table, 227

unique constraints, 217

Vendors table, 226

processing

stored procedures, 189

subqueries, 106

transactions, 198

transactions. See transaction processing

Products table, 226-227

Q

[top]

QMF (Qery Management Facility) utility, 235

queries, 103

aggregate functions, 81

combined, 137

creating, 138-139

sorting results, 143

UNION statement rules, 140

WHERE clauses, 137

combining, 105

data formatting, 17

filtering results, 35

internal query optimizer, 140

multiple WHERE clauses, 140

result sets, 205

subqueries, 103

table aliases, 126

unsorted data results, 15

views, 176

wild cards (*), 18

quotes

numeric values, 256

string values, 255

quotes (‘’), 39

R

[top]

RAW datatype, 258

ready-to-use data files, downloading, 230

REAL datatype, 256

REFERENCES keyword, 217

referential integrity, 213, 217

reformatting retrieved data with views, 180-183

relational databases

referential integrity, 213

sort order, 28

relational tables, 113-114

relative position, columns, 96

RENAME statement, 172

renaming tables, 172

replacing tables, 165

reserved words, 259-263

restrictions, views, 177

result sets, 205

retrieving

columns, unknown, 18

data, 13, 81, 84-85

all columns, 18

FETCH statements, 208-210

individual columns, 14

inserting, 150

mulitiple columns, 16

reusable views, creating, 180

revisiting indexes, 222

REVOKE statements, 224

RIGHT keyword, 131

ROLLBACK statement, 201, 251

rollbacks, 201

savepoints, 203

statements, 204

using, 199

rows, 9

adding to tables, 250

cursors, 205

default values, 168

deleting, 250

duplicate, 141-142

filtering, 96

inserting, 146

check constraints, 219

multiple, 152

partial, 149-150

joins, 129

returning with UNION statements, 141

updating, 252

RTRIM() function, 64-65

rules

constraints, 214

views, 177

S

[top]

samples

scripts, obtaining, 229

tables, 226, 229

SAVEPOINT statements, 202

savepoints, 199, 203-204

scalablity, 115

scale, 115

schemas, 7

scripts

ASP.NET, 237

comments, 23-24

downloading, 230

PHP, 242

samples, obtaining, 229

search patterns, 53-55

searching

indexes, 220

wildcards, 53

% character, 54

[] characters, 58-59

^ character, 59

_ character, 57-58

security

data, 224

DELETE statement, 158

INSERT statements, 145

UPDATE statement, 155

SELECT * FROM statements, 179

SELECT INTO statements, 152

SELECT statement, 13

AS keyword, 66

AVG() function, 82

clauses, ordering, 101

concatenating columns, 63-64

creating groups, 94-95

DISTINCT keyword, 19-20

FROM clause, 116

GROUP BY clause, 94

inner joins, 130, 133

IS NULL clause, 41

joins, 115-116

ORDER BY clause, 28

DESC keyword, 31

positioning, 29

results, limiting, 20-23

retrieving individual columns, 14

subqueries, 104-106, 110

syntax, 252

UNION keyword, 138-140

WHERE clause, 35

combined queries, 137

combining, 44

IN operator, 47

NOT operator, 49-51

OR operators, 45

quotes, 39

self-joins, creating, 126-128

semicolons (;), 15

separating statements, 15

sequence (clauses), 101

servers, formatting, 62

SET command, 156

simplifying joins with views, 179

SIN() function, 79

Single Column Only subqueries, 107

SMALLDATETIME datatype, 257

SMALLINT datatype, 256

sorting

by non-selected columns, 29

case-sensitivity issues, 33

combined query results, 143

data, 27, 99

by column position, 30

descending on multiple columns, 33

multiple columns, 29-30

non-selected columns, 31

ORDER BY clause, 100

retrieved, 27

specifying direction, 31

datatype functionality, 253, 262

SOUNDEX() function, 75

spaces, padded, 64

specifying

dates, 257

defualt values, 168

NULL values, 167

sort direction, 31

statements, 14

speed

constraints versus triggers, 223

deleting data, 160

SQL, 10

advanced features, 213

advantages, 11

ALL clause, 95

column aliases, 66

commits, 201

cursors

closing, 211

creating, 207

retrieving data, 210

DATEPART() function, 77

deleting/updating data, 161

experimentation with operations, 123

extensions, 11

Identity fields, 195

INNER JOIN syntax, 120

INSERT statements, 146

keywords, 13

local variables, @ character, 193

reserved words, 259-263

savepoints, 203

statements, clauses, 28

stored procedures, 193

transaction processing, 200

triggers, 223

working in popular applications, 233

SQL Server Management Studio, 239

SQRT() function, 79

statements

ALTER TABLE, 169-170, 248

case, 16

CFQUERY/CFQUERY tag pairs, 234

clauses, 28

COMMIT, 248

CREATE INDEX, 221, 248-249

CREATE TABLE

required information, 164

syntax, 249

CREATE VIEW, 179, 249

DELETE, 157, 160

FROM keyword, 159

syntax, 250

displaying output, 14

DROP, 250

DROP TABLE, 171

formatting, 165

GRANT, 224

grouping related operators, 46

INSERT

omitting columns, 149

safety, 146

security privileges, 145

syntax, 250

VALUES, 148

INSERT SELECT, syntax, 251

OPEN CURSOR, 208

RENAME, 172

REVOKE, 224

ROLLBACK, syntax, 251

rollbacks, 199, 204

SELECT, 13

DISTINCT keyword, 19-20

results, limiting, 20-23

syntax, 252

specifying, 14

stored procedures. See stored procedures

syntax, 247

terminating, 15

UNION, 138-140

UPDATE, 155-156, 160, 252

white space, 16

writing, 225

stored procedures, 187

benefits, 189

commenting code, 195

creating, 188, 191-194, 249

executing, 190-191

functionality, 194

Identity fields, 195

justification, 188

Oracle, 191-194

syntax, 189

triggers, 222

storing

datatype functionality, 253

date and time values, 257

numeric values, 255

strings, 254

string datatypes, 254

strings

fixed length, 254

quotes, 255

search, wildcards, 54

variable-length, 254

subqueries, 103

calculated fields, 108-109

COUNT*, 108

filtering by, 104-105

formatting, 106

joins, 127

performance, 108

processing, 106

SELECT statement, 104

self joins instead, 128

Single Column Only, 107

UPDATE statement, 157

WHERE lauses, 107

SUM() function, 82, 87-88

summarizing data, 81, 84-85

syntax

ALTER TABLE statement, 248

COMMIT statement, 248

constraints, 215

CREATE INDEX statement, 248-249

CREATE TABLE statement, 249

CREATE TABLE statements, 163

CREATE VIEW statement, 249

DELETE statement, 250

DROP statement, 250

INERT statement, 251

INSERT statement, 250

outer joins, 130

ROLLBACK statement, 251

SELECT statement, 252

statements, 247

stored procedures, 189

transaction processing, 200

triggers, 223

UPDATE statement, 252

system functions, 73

T

[top]

tables, 6

aliases, 125-128, 132

calculated fields, 61

columns, 7

NULL value, 40

primary keys, 10

constraints, 214

check, 218

syntax, 215

unique, 217

copying, 152-153, 164

copying data to tables, 152

creating, 163-165, 249

cursors

accessing data, 208-210

closing, 211

creating, 207

opening, 208

Customer, 227

data, copying, 153

datatypes, 8

deleting, 171-172

deleting data, 157-159

indexes

cautions, 221

creating, 221

searching, 220

inserting data, 146

multiple rows, 152

partial rowas, 149

retrieved, 150

joining, 113-115

aliases, 132

Cartesian Product, 117

multiple, 121-123

natural joins, 129

performance concerns, 121

manipulating, 163

NULL values, 166

OrderItems, 228-229

Orders, 228

Products, 226-227

referential integrity, 213

relational, 113-114

renaming, 172

replacing existing, 165

rows, 9

adding, 250

cursors, 205

deleting, 250

filtering, 96

updating, 252

samples, 226, 229

schemas, 7

security, 224

stored procedures, 189

triggers, 222

creating, 223

functionality, 222

updating, 155-156, 169-170

Vendors, 226

views

creating, 249

uses, 176

virtual, 175-176

tags, ColdFusion, 234

TAN() function, 79

terminating statements, 15

text manipulation functions, 74

SOUNDEX() function, 75

UPPER() function, 73

TEXT string datatype, 255

TINYINT datatype, 256

tools, DBMS interactive, 115

TOP argument, 90

TOP PERCENT argument, 90

to_char() function, 78

to_number() functions, 78

transactions, 197

blocks, ROLLBACK statements, 251

COMMIT statements, 201

implementing, 200

managing, 199

COMMIT statements, 201

ROLLBACK statements, 201

SAVEPOINT statements, 202

ROLLBACK statements, 201

SAVEPOINT statements, 202

savepoints, 199, 204

writing to databases, 248

triggers

creating, 223

functionality, 222

speed, 223

syntax examples, 223

TRIM() function, 66

trimming padded spaces, 64

troubleshooting accidental table deletion, 172

TRUNCATE TABLE statement, 160

U

[top]

underscore (_) wildcard, 57-58

UNION ALL statements, 142

UNION statements

combined queries, creating, 138-139

duplicate row handling, 141

limits, 140

ORDER BY clause, 142

rules, 140

types, 143

UNION, 140

unions. See combined queries

unique constraints, 217

UNIQUE keyword, 218

unsorted data, 15

UPDATE statement

cautions, 155

FROM keyword, 157

guidelines, 160

security privileges, 155

SET command, 156

subqueries, 157

syntax, 252

table names, 156

transaction processing, 199

triggers, 222

WHERE clause, 156

updating

data, 155-156, 160

multiple columns, 156

tables, 169-170

UPPER() function, 73

user-defined datatypes, 219

V

[top]

values

columns, deleting, 157

concatenation, 63

default, 168

NULL, 167

searching for (indexes), 220

trimming padded space, 65

VARBINARY datatype, 258

variable-length strings, 254

Vendors table, 226

views

calculated fields, 184

creating, 177-180, 249

DBMS consistency, 176

filtering unwanted data, 183

joins, 179

performance concerns, 177

reformatting retrieved data, 180-183

rules and restrictions, 177

SELECT statement, 175

uses, 176

virtual tables, 175-176

W-X-Y-Z

[top]

web-based applications, cursors, 206

WHERE clause, 35

BETWEEN operator, 40

combining, 43

order of evaluation, 46

with queries, 137

DELETE statements, 158

filtering, 97

IN operator, 47

joins, 117-119

NOT operators, 49, 51

operators, 37

OR operators, 45

parenthesis, 47

positioning, 37, 39

quotes, 39

SOUNDEX() function, 76

subqueries, 107

UPDATE statements, 155-156

wildcards, 53

white space, SQL statements, 16

whitespace, 16

wildcards, 18, 53

[] (brackets) characters, 58-59

_ (underscore) character, 57-58

^ character, 59

natural joins, 129

positioning in search patterns, 55

search patterns, 55

wrappers, ODBC, 245

writing

SQL statements, 225

stored procedures, 189