|
Section 1 An introduction to SQL |
|
|
|
Chapter 1 An introduction to relational databases and SQL |
|
|
|
An introduction to client/server systems |
|
|
4 | (6) |
|
The hardware components of a client/server system |
|
|
4 | (2) |
|
The software components of a client/server system |
|
|
6 | (2) |
|
Other client/server system architectures |
|
|
8 | (2) |
|
An introduction to the relational database model |
|
|
10 | (8) |
|
How a database table is organized |
|
|
10 | (2) |
|
How the tables in a relational database are related |
|
|
12 | (2) |
|
How the columns in a table are defined |
|
|
14 | (2) |
|
How relational databases compare to other data models |
|
|
16 | (2) |
|
An introduction to SQL and SQL-based systems |
|
|
18 | (4) |
|
|
|
18 | (2) |
|
A comparison of Oracle, DB2, MySQL, and SQL Server |
|
|
20 | (2) |
|
The Transact-SQL statements |
|
|
22 | (12) |
|
An introduction to the SQL statements |
|
|
22 | (2) |
|
Typical statements for working with database objects |
|
|
24 | (2) |
|
How to query a single table |
|
|
26 | (2) |
|
How to join data from two or more tables |
|
|
28 | (2) |
|
How to add, update, and delete data in a table |
|
|
30 | (2) |
|
|
|
32 | (2) |
|
How to work with other database objects |
|
|
34 | (4) |
|
|
|
34 | (2) |
|
How to work with stored procedures, triggers, and user-defined functions |
|
|
36 | (2) |
|
How to use SQL from an application program |
|
|
38 | (12) |
|
Common data access models |
|
|
38 | (2) |
|
How to use ADO.NET from a .NET application |
|
|
40 | (2) |
|
Visual Basic code that retrieves data from a SQL Server database |
|
|
42 | (2) |
|
C# code that retrieves data from a SQL Server database |
|
|
44 | (6) |
|
Chapter 2 How to use the Management Studio |
|
|
|
An introduction to SQL Server 2019 |
|
|
50 | (4) |
|
A summary of the SQL Server 2019 tools |
|
|
50 | (2) |
|
How to start and stop the database engine |
|
|
52 | (1) |
|
How to enable remote connections |
|
|
52 | (2) |
|
An introduction to the Management Studio |
|
|
54 | (4) |
|
How to connect to a database server |
|
|
54 | (2) |
|
How to navigate through the database objects |
|
|
56 | (2) |
|
How to manage the database files |
|
|
58 | (6) |
|
|
|
58 | (1) |
|
|
|
58 | (2) |
|
How to back up a database |
|
|
60 | (1) |
|
How to restore a database |
|
|
60 | (2) |
|
How to set the compatibility level for a database |
|
|
62 | (2) |
|
How to view and modify the database |
|
|
64 | (6) |
|
How to create database diagrams |
|
|
64 | (2) |
|
How to view the column definitions of a table |
|
|
66 | (1) |
|
How to modify the column definitions |
|
|
66 | (2) |
|
How to view the data of a table |
|
|
68 | (1) |
|
How to modify the data of a table |
|
|
68 | (2) |
|
|
|
70 | (8) |
|
How to enter and execute a query |
|
|
70 | (2) |
|
How to handle syntax errors |
|
|
72 | (2) |
|
How to open and save queries |
|
|
74 | (2) |
|
An introduction to the Query Designer |
|
|
76 | (2) |
|
How to view the documentation for SQL Server |
|
|
78 | (8) |
|
How to display the SQL Server documentation |
|
|
78 | (1) |
|
How to look up information in the documentation |
|
|
78 | (8) |
|
Section 2 The essential SQL skills |
|
|
|
Chapter 3 How to retrieve data from a single table |
|
|
|
An introduction to the SELECT statement |
|
|
86 | (4) |
|
The basic syntax of the SELECT statement |
|
|
86 | (2) |
|
SELECT statement examples |
|
|
88 | (2) |
|
How to code the SELECT clause |
|
|
90 | (14) |
|
How to code column specifications |
|
|
90 | (2) |
|
How to name the columns in a result set |
|
|
92 | (2) |
|
How to code string expressions |
|
|
94 | (2) |
|
How to code arithmetic expressions |
|
|
96 | (2) |
|
|
|
98 | (2) |
|
How to use the DISTINCT keyword to eliminate duplicate rows |
|
|
100 | (2) |
|
How to use the TOP clause to return a subset of selected rows |
|
|
102 | (2) |
|
How to code the WHERE clause |
|
|
104 | (12) |
|
How to use comparison operators |
|
|
104 | (2) |
|
How to use the AND, OR, and NOT logical operators |
|
|
106 | (2) |
|
How to use the IN operator |
|
|
108 | (2) |
|
How to use the BETWEEN operator |
|
|
110 | (2) |
|
How to use the LIKE operator |
|
|
112 | (2) |
|
How to use the IS NULL clause |
|
|
114 | (2) |
|
How to code the ORDER BY clause |
|
|
116 | (10) |
|
How to sort a result set by a column name |
|
|
116 | (2) |
|
How to sort a result set by an alias, an expression, or a column number |
|
|
118 | (2) |
|
How to retrieve a range of selected rows |
|
|
120 | (6) |
|
Chapter 4 How to retrieve data from two or more tables |
|
|
|
How to work with inner joins |
|
|
126 | (14) |
|
How to code an inner join |
|
|
126 | (2) |
|
When and how to use correlation names |
|
|
128 | (2) |
|
How to work with tables from different databases |
|
|
130 | (2) |
|
How to use compound join conditions |
|
|
132 | (2) |
|
|
|
134 | (2) |
|
Inner joins that join more than two tables |
|
|
136 | (2) |
|
How to use the implicit inner join syntax |
|
|
138 | (2) |
|
How to work with outer Joins |
|
|
140 | (6) |
|
How to code an outer join |
|
|
140 | (2) |
|
|
|
142 | (2) |
|
Outer joins that join more than two tables |
|
|
144 | (2) |
|
Other skills for working with joins |
|
|
146 | (4) |
|
How to combine inner and outer joins |
|
|
146 | (2) |
|
|
|
148 | (2) |
|
|
|
150 | (10) |
|
|
|
150 | (1) |
|
Unions that combine data from different tables |
|
|
150 | (2) |
|
Unions that combine data from the same table |
|
|
152 | (2) |
|
How to use the EXCEPT and INTERSECT operators |
|
|
154 | (6) |
|
Chapter 5 How to code summary queries |
|
|
|
How to work with aggregate functions |
|
|
160 | (4) |
|
How to code aggregate functions |
|
|
160 | (2) |
|
Queries that use aggregate functions |
|
|
162 | (2) |
|
How to group and summarize data |
|
|
164 | (8) |
|
How to code the GROUP BY and HAVING clauses |
|
|
164 | (2) |
|
Queries that use the GROUP BY and HAVING clauses |
|
|
166 | (2) |
|
How the HAVING clause compares to the WHERE clause |
|
|
168 | (2) |
|
How to code complex search conditions |
|
|
170 | (2) |
|
How to summarize data using SQL Server extensions |
|
|
172 | (12) |
|
How to use the ROLLUP operator |
|
|
172 | (2) |
|
How to use the CUBE operator |
|
|
174 | (2) |
|
How to use the GROUPING SETS operator |
|
|
176 | (2) |
|
How to use the OVER clause |
|
|
178 | (6) |
|
Chapter 6 How to code subqueries |
|
|
|
An introduction to subqueries |
|
|
184 | (4) |
|
|
|
184 | (2) |
|
How subqueries compare to joins |
|
|
186 | (2) |
|
How to code subqueries in search conditions |
|
|
188 | (12) |
|
How to use subqueries with the IN operator |
|
|
188 | (2) |
|
How to compare the result of a subquery with an expression |
|
|
190 | (2) |
|
How to use the ALL keyword |
|
|
192 | (2) |
|
How to use the ANY and SOME keywords |
|
|
194 | (2) |
|
How to code correlated subqueries |
|
|
196 | (2) |
|
How to use the EXISTS operator |
|
|
198 | (2) |
|
Other ways to use subqueries |
|
|
200 | (4) |
|
How to code subqueries in the FROM clause |
|
|
200 | (2) |
|
How to code subqueries in the SELECT clause |
|
|
202 | (2) |
|
Guidelines for working with complex queries |
|
|
204 | (4) |
|
A complex query that uses subqueries |
|
|
204 | (2) |
|
A procedure for building complex queries |
|
|
206 | (2) |
|
How to work with common table expressions |
|
|
208 | (8) |
|
|
|
208 | (2) |
|
How to code a recursive CTE |
|
|
210 | (6) |
|
Chapter 7 How to insert, update, and delete data |
|
|
|
How to create test tables |
|
|
216 | (2) |
|
How to use the SELECT INTO statement |
|
|
216 | (1) |
|
How to use a copy of the database |
|
|
216 | (2) |
|
|
|
218 | (6) |
|
How to insert a single row |
|
|
218 | (1) |
|
How to insert multiple rows |
|
|
218 | (2) |
|
How to insert default values and null values |
|
|
220 | (2) |
|
How to insert rows selected from another table |
|
|
222 | (2) |
|
How to modify existing rows |
|
|
224 | (6) |
|
How to perform a basic update operation |
|
|
224 | (2) |
|
How to use subqueries in an update operation |
|
|
226 | (2) |
|
How to use joins in an update operation |
|
|
228 | (2) |
|
How to delete existing rows |
|
|
230 | (4) |
|
How to perform a basic delete operation |
|
|
230 | (2) |
|
How to use subqueries and joins in a delete operation |
|
|
232 | (2) |
|
|
|
234 | (6) |
|
How to perform a basic merge operation |
|
|
234 | (1) |
|
How to code more complex merge operations |
|
|
234 | (6) |
|
Chapter 8 How to work with data types |
|
|
|
A review of the SQL data types |
|
|
240 | (10) |
|
|
|
240 | (2) |
|
|
|
242 | (2) |
|
|
|
244 | (2) |
|
|
|
246 | (2) |
|
The large value data types |
|
|
248 | (2) |
|
|
|
250 | (12) |
|
How data conversion works |
|
|
250 | (2) |
|
How to convert data using the CAST function |
|
|
252 | (2) |
|
How to convert data using the CONVERT function |
|
|
254 | (2) |
|
How to use the TRY_CONVERT function |
|
|
256 | (2) |
|
How to use other data conversion functions |
|
|
258 | (4) |
|
Chapter 9 How to use functions |
|
|
|
How to work with string data |
|
|
262 | (6) |
|
A summary of the string functions |
|
|
262 | (4) |
|
How to solve common problems that occur with string data |
|
|
266 | (2) |
|
How to work with numeric data |
|
|
268 | (4) |
|
A summary of the numeric functions |
|
|
268 | (2) |
|
How to solve common problems that occur with numeric data |
|
|
270 | (2) |
|
How to work with date/time data |
|
|
272 | (12) |
|
A summary of the date/time functions |
|
|
272 | (4) |
|
How to parse dates and times |
|
|
276 | (2) |
|
How to perform operations on dates and times |
|
|
278 | (2) |
|
How to perform a date search |
|
|
280 | (2) |
|
How to perform a time search |
|
|
282 | (2) |
|
Other functions you should know about |
|
|
284 | (20) |
|
How to use the CASE function |
|
|
284 | (2) |
|
How to use the UF and CHOOSE functions |
|
|
286 | (2) |
|
How to use the COALESCE and ISNULL functions |
|
|
288 | (2) |
|
How to use the GROUPING function |
|
|
290 | (2) |
|
How to use the ranking functions |
|
|
292 | (4) |
|
How to use the analytic functions |
|
|
296 | (8) |
|
Section 3 Database design and implementation |
|
|
|
Chapter 10 How to design a database |
|
|
|
How to design a data structure |
|
|
304 | (16) |
|
The basic steps for designing a data structure |
|
|
304 | (2) |
|
How to identify the data elements |
|
|
306 | (2) |
|
How to subdivide the data elements |
|
|
308 | (2) |
|
How to identify the tables and assign columns |
|
|
310 | (2) |
|
How to identify the primary and foreign keys |
|
|
312 | (2) |
|
How to enforce the relationships between tables |
|
|
314 | (2) |
|
|
|
316 | (2) |
|
How to identify the columns to be indexed |
|
|
318 | (2) |
|
How to normalize a data structure |
|
|
320 | (14) |
|
|
|
320 | (2) |
|
How to apply the first normal form |
|
|
322 | (2) |
|
How to apply the second normal form |
|
|
324 | (2) |
|
How to apply the third normal form |
|
|
326 | (2) |
|
When and how to denormalize a data structure |
|
|
328 | (6) |
|
Chapter 11 How to create a database and its tables with SQL Statements |
|
|
|
|
|
334 | (4) |
|
The SQL statements for data definition |
|
|
334 | (2) |
|
Rules for coding object names |
|
|
336 | (2) |
|
How to create databases, tables, and indexes |
|
|
338 | (8) |
|
|
|
338 | (2) |
|
|
|
340 | (2) |
|
|
|
342 | (2) |
|
How to use snippets to create database objects |
|
|
344 | (2) |
|
|
|
346 | (6) |
|
An introduction to constraints |
|
|
346 | (2) |
|
How to use check constraints |
|
|
348 | (2) |
|
How to use foreign key constraints |
|
|
350 | (2) |
|
How to change databases and tables |
|
|
352 | (4) |
|
How to delete an index, table, or database |
|
|
352 | (2) |
|
|
|
354 | (2) |
|
How to work with sequences |
|
|
356 | (4) |
|
|
|
356 | (1) |
|
|
|
356 | (2) |
|
|
|
358 | (1) |
|
|
|
358 | (2) |
|
How to work with collations |
|
|
360 | (8) |
|
An introduction to encodings |
|
|
360 | (2) |
|
An introduction to collations |
|
|
362 | (2) |
|
|
|
364 | (2) |
|
How to specify a collation |
|
|
366 | (2) |
|
The script used to create the AP database |
|
|
368 | (8) |
|
|
|
368 | (1) |
|
How the DDL statements work |
|
|
368 | (8) |
|
Chapter 12 How to create a database and its tables with the Managment Studio |
|
|
|
How to work with a database |
|
|
376 | (2) |
|
|
|
376 | (1) |
|
|
|
376 | (2) |
|
|
|
378 | (10) |
|
How to create, modify, or delete a table |
|
|
378 | (2) |
|
How to work with foreign key relationships |
|
|
380 | (2) |
|
How to work with indexes and keys |
|
|
382 | (2) |
|
How to work with check constraints |
|
|
384 | (2) |
|
How to examine table dependencies |
|
|
386 | (2) |
|
|
|
388 | (8) |
|
How to generate scripts for databases and tables |
|
|
390 | (2) |
|
How to generate a change script when you modify a table |
|
|
392 | (4) |
|
Section 4 Advanced SQL skills |
|
|
|
Chapter 13 How to work with views |
|
|
|
|
|
396 | (4) |
|
|
|
396 | (2) |
|
|
|
398 | (2) |
|
How to create and manage views |
|
|
400 | (8) |
|
|
|
400 | (2) |
|
Examples that create views |
|
|
402 | (2) |
|
How to create an updatable view |
|
|
404 | (2) |
|
How to delete or modify a view |
|
|
406 | (2) |
|
|
|
408 | (6) |
|
How to update rows through a view |
|
|
408 | (2) |
|
How to insert rows through a view |
|
|
410 | (1) |
|
How to delete rows through a view |
|
|
410 | (2) |
|
How to use the catalog views |
|
|
412 | (2) |
|
How to use the View Designer |
|
|
414 | (4) |
|
How to create or modify a view |
|
|
414 | (1) |
|
|
|
414 | (4) |
|
Chapter 14 How to code scripts |
|
|
|
An introduction to scripts |
|
|
418 | (4) |
|
|
|
418 | (2) |
|
The Transact-SQL statements for script processing |
|
|
420 | (2) |
|
How to work with variables and temporary tables |
|
|
422 | (8) |
|
How to work with scalar variables |
|
|
422 | (2) |
|
How to work with table variables |
|
|
424 | (2) |
|
How to work with temporary tables |
|
|
426 | (2) |
|
A comparison of the five types of Transact-SQL table objects |
|
|
428 | (2) |
|
How to control the execution of a script |
|
|
430 | (12) |
|
How to perform conditional processing |
|
|
430 | (2) |
|
How to test for the existence of a database object |
|
|
432 | (2) |
|
How to perform repetitive processing |
|
|
434 | (2) |
|
|
|
436 | (2) |
|
|
|
438 | (2) |
|
How to use surround-with snippets |
|
|
440 | (2) |
|
Advanced scripting techniques |
|
|
442 | (16) |
|
How to use the system functions |
|
|
442 | (2) |
|
How to change the session settings |
|
|
444 | (2) |
|
|
|
446 | (2) |
|
A script that summarizes the structure of a database |
|
|
448 | (4) |
|
How to use the SQLCMD utility |
|
|
452 | (6) |
|
Chapter 15 How to code stored procedures, functions, and triggers |
|
|
|
Procedural programming options in Transact-SQL |
|
|
458 | (2) |
|
|
|
458 | (1) |
|
Stored procedures, user-defined functions, and triggers |
|
|
458 | (2) |
|
How to code stored procedures |
|
|
460 | (24) |
|
An introduction to stored procedures |
|
|
460 | (2) |
|
How to create a stored procedure |
|
|
462 | (2) |
|
How to declare and work with parameters |
|
|
464 | (2) |
|
How to call procedures with parameters |
|
|
466 | (2) |
|
How to work with return values |
|
|
468 | (2) |
|
How to validate data and raise errors |
|
|
470 | (2) |
|
A stored procedure that manages insert operations |
|
|
472 | (6) |
|
How to pass a table as a parameter |
|
|
478 | (2) |
|
How to delete or change a stored procedure |
|
|
480 | (2) |
|
How to work with system stored procedures |
|
|
482 | (2) |
|
How to code user-defined functions |
|
|
484 | (10) |
|
An introduction to user-defined functions |
|
|
484 | (2) |
|
How to create a scalar-valued function |
|
|
486 | (2) |
|
How to create a simple table-valued function |
|
|
488 | (2) |
|
How to create a multi-statement table-valued function |
|
|
490 | (2) |
|
How to delete or change a function |
|
|
492 | (2) |
|
|
|
494 | (16) |
|
|
|
494 | (2) |
|
How to use AFTER triggers |
|
|
496 | (2) |
|
How to use INSTEAD OF triggers |
|
|
498 | (2) |
|
How to use triggers to enforce data consistency |
|
|
500 | (2) |
|
How to use triggers to work with DDL statements |
|
|
502 | (2) |
|
How to delete or change a trigger |
|
|
504 | (6) |
|
Chapter 16 How to manage transactions and locking |
|
|
|
How to work with transactions |
|
|
510 | (8) |
|
How transactions maintain data integrity |
|
|
510 | (2) |
|
SQL statements for handling transactions |
|
|
512 | (2) |
|
How to work with nested transactions |
|
|
514 | (2) |
|
How to work with save points |
|
|
516 | (2) |
|
An introduction to concurrency and locking |
|
|
518 | (6) |
|
How concurrency and locking are related |
|
|
518 | (2) |
|
The four concurrency problems that locks can prevent |
|
|
520 | (2) |
|
How to set the transaction isolation level |
|
|
522 | (2) |
|
How SQL Server manages locking |
|
|
524 | (6) |
|
Lockable resources and lock escalation |
|
|
524 | (2) |
|
Lock modes and lock promotion |
|
|
526 | (2) |
|
|
|
528 | (2) |
|
|
|
530 | (6) |
|
Two transactions that deadlock |
|
|
530 | (2) |
|
Coding techniques that prevent deadlocks |
|
|
532 | (4) |
|
Chapter 17 How to manage database security |
|
|
|
How to work with SQL Server login IDs |
|
|
536 | (12) |
|
An introduction to SQL Server security |
|
|
536 | (2) |
|
How to change the authentication mode |
|
|
538 | (2) |
|
|
|
540 | (2) |
|
How to delete or change login IDs or passwords |
|
|
542 | (2) |
|
How to work with database users |
|
|
544 | (2) |
|
|
|
546 | (2) |
|
How to work with permissions |
|
|
548 | (10) |
|
How to grant or revoke object permissions |
|
|
548 | (2) |
|
The SQL Server object permissions |
|
|
550 | (2) |
|
How to grant or revoke schema permissions |
|
|
552 | (2) |
|
How to grant or revoke database permissions |
|
|
554 | (2) |
|
How to grant or revoke server permissions |
|
|
556 | (2) |
|
|
|
558 | (16) |
|
How to work with the fixed server roles |
|
|
558 | (2) |
|
How to work with user-defined server roles |
|
|
560 | (2) |
|
How to display information about server roles and role members |
|
|
562 | (2) |
|
How to work with the fixed database roles |
|
|
564 | (2) |
|
How to work with user-defined database roles |
|
|
566 | (2) |
|
How to display information about database roles and role members |
|
|
568 | (2) |
|
How to deny permissions granted by role membership |
|
|
570 | (2) |
|
How to work with application roles |
|
|
572 | (2) |
|
How to manage security using the Management Studio |
|
|
574 | (14) |
|
How to work with login IDs |
|
|
574 | (2) |
|
How to work with the server roles for a login ID |
|
|
576 | (2) |
|
How to assign database access and roles by login ID |
|
|
578 | (2) |
|
How to assign user permissions to database objects |
|
|
580 | (2) |
|
How to work with database permissions |
|
|
582 | (6) |
|
Chapter 18 How to work with XML |
|
|
|
|
|
588 | (4) |
|
|
|
588 | (2) |
|
|
|
590 | (2) |
|
How to work with the xml data type |
|
|
592 | (12) |
|
How to store data in the xml data type |
|
|
592 | (2) |
|
How to work with the XML Editor |
|
|
594 | (2) |
|
How to use the methods of the xml data type |
|
|
596 | (4) |
|
An example that parses the xml data type |
|
|
600 | (2) |
|
Another example that parses the xml data type |
|
|
602 | (2) |
|
How to work with XML schemas |
|
|
604 | (6) |
|
How to add an XML schema to a database |
|
|
604 | (2) |
|
How to use ah XML schema to validate the xml data type |
|
|
606 | (2) |
|
How to view an XML schema |
|
|
608 | (1) |
|
How to drop an XML schema |
|
|
608 | (2) |
|
Two more skills for working with XML |
|
|
610 | (10) |
|
How to use the FOR XML clause of the SELECT statement |
|
|
610 | (4) |
|
How to use the OPENXML statement |
|
|
614 | (6) |
|
Chapter 19 How to work with BLOBs |
|
|
|
|
|
620 | (2) |
|
Pros and cons of storing BLOBs in files |
|
|
620 | (1) |
|
Pros and cons of storing BLOBs in a column |
|
|
620 | (1) |
|
When to use FTLESTREAM storage for BLOBs |
|
|
620 | (2) |
|
How to use SQL to work with a varbinary(max) column |
|
|
622 | (2) |
|
How to create a table with a varbinary(max) column |
|
|
622 | (1) |
|
How to insert, update, and delete binary data |
|
|
622 | (1) |
|
How to retrieve binary data |
|
|
622 | (2) |
|
A .NET application that uses a varbinary(max) column |
|
|
624 | (10) |
|
The user interface for the application |
|
|
624 | (2) |
|
The event handlers for the form |
|
|
626 | (2) |
|
A data access class that reads and writes binary data |
|
|
628 | (6) |
|
How to use FILESTREAM storage |
|
|
634 | (14) |
|
How to enable FILESTREAM storage on the server |
|
|
634 | (2) |
|
How to create a database with FILESTREAM storage |
|
|
636 | (2) |
|
How to create a table with a FILESTREAM column |
|
|
638 | (1) |
|
How to insert, update, and delete FILESTREAM data |
|
|
638 | (1) |
|
How to retrieve FILESTREAM data |
|
|
638 | (2) |
|
A data access class that uses FILESTREAM storage |
|
|
640 | (8) |
|
Appendix A How to set up your computer for this book |
|
|
|
Three editions of SQL Server 2019 Express |
|
|
648 | (1) |
|
The tool for working with all editions of SQL Server |
|
|
648 | (2) |
|
How to install SQL Server 2019 Express |
|
|
650 | (1) |
|
How to install SQL Server Management Studio |
|
|
650 | (2) |
|
How to install the files for this book |
|
|
652 | (2) |
|
How to create the databases for this book |
|
|
654 | (1) |
|
How to restore the databases for this book |
|
|
654 | (2) |
|
How to install Visual Studio 2019 Community |
|
|
656 | |
