|
Section 1 An introduction to SQL |
|
|
|
Chapter 1 An introduction to relational databases and SQL |
|
|
|
An introduction to client/server systems |
|
|
4 | (1) |
|
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 | (1) |
|
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) |
|
An introduction to SQL and SQL-based systems |
|
|
16 | (1) |
|
|
|
16 | (2) |
|
A comparison of four relational databases |
|
|
18 | (2) |
|
The Transact-SQL statements |
|
|
20 | (1) |
|
An introduction to the SQL statements |
|
|
20 | (2) |
|
Typical statements for working with database objects |
|
|
22 | (2) |
|
How to query a single table |
|
|
24 | (2) |
|
How to join data from two or more tables |
|
|
26 | (2) |
|
How to add, update, and delete data in a table |
|
|
28 | (2) |
|
|
|
30 | (4) |
|
Chapter 2 How to use Management Studio |
|
|
|
An introduction to SQL Server 2022 |
|
|
34 | (1) |
|
A summary of SQL Server 2022 tools |
|
|
34 | (2) |
|
How to start and stop the database engine |
|
|
36 | (1) |
|
How to enable remote connections |
|
|
36 | (2) |
|
An introduction to Management Studio |
|
|
38 | (1) |
|
How to connect to a database server |
|
|
38 | (2) |
|
How to navigate through the database objects |
|
|
40 | (2) |
|
How to view and modify the database |
|
|
42 | (1) |
|
How to create database diagrams |
|
|
42 | (2) |
|
How to view the column definitions of a table |
|
|
44 | (1) |
|
How to modify the column definitions |
|
|
44 | (2) |
|
How to view the data of a table |
|
|
46 | (1) |
|
How to modify the data of a table |
|
|
46 | (2) |
|
|
|
48 | (1) |
|
How to enter and execute a query |
|
|
48 | (2) |
|
How to handle syntax errors |
|
|
50 | (2) |
|
How to open and save queries |
|
|
52 | (2) |
|
How to view the documentation for SQL Server |
|
|
54 | (8) |
|
Section 2 The essential SQL skills |
|
|
|
Chapter 3 How to retrieve data from a single table |
|
|
|
An introduction to the SELECT statement |
|
|
62 | (1) |
|
The basic syntax of the SELECT statement |
|
|
62 | (2) |
|
SELECT statement examples |
|
|
64 | (2) |
|
How to code the SELECT clause |
|
|
66 | (1) |
|
How to code column specifications |
|
|
66 | (2) |
|
How to name the columns in a result set |
|
|
68 | (2) |
|
How to code string expressions |
|
|
70 | (2) |
|
How to code arithmetic expressions |
|
|
72 | (2) |
|
|
|
74 | (2) |
|
How to use the DISTINCT keyword to eliminate duplicate rows |
|
|
76 | (2) |
|
How to use the TOP clause to return a subset of selected rows |
|
|
78 | (2) |
|
How to code the WHERE clause |
|
|
80 | (1) |
|
How to use comparison operators |
|
|
80 | (2) |
|
How to use the AND, OR, and NOT logical operators |
|
|
82 | (2) |
|
How to use the IN operator |
|
|
84 | (2) |
|
How to use the BETWEEN operator |
|
|
86 | (2) |
|
How to use the LIKE operator |
|
|
88 | (2) |
|
How to use the IS NULL clause |
|
|
90 | (2) |
|
How to code the ORDER BY clause |
|
|
92 | (1) |
|
How to sort a result set by a column name |
|
|
92 | (2) |
|
How to sort a result set by an alias, an expression, or a column number |
|
|
94 | (2) |
|
How to retrieve a range of selected rows |
|
|
96 | (6) |
|
Chapter 4 How to retrieve data from two or more tables |
|
|
|
How to work with inner joins |
|
|
102 | (1) |
|
How to code an inner join |
|
|
102 | (2) |
|
When and how to use table aliases |
|
|
104 | (2) |
|
How to work with tables from different databases |
|
|
106 | (2) |
|
How to use compound join conditions |
|
|
108 | (2) |
|
|
|
110 | (2) |
|
Inner joins that join more than two tables |
|
|
112 | (2) |
|
How to use the implicit inner join syntax |
|
|
114 | (2) |
|
How to work with outer joins |
|
|
116 | (1) |
|
How to code an outer join |
|
|
116 | (2) |
|
|
|
118 | (2) |
|
Outer joins that join more than two tables |
|
|
120 | (2) |
|
Other skills for working with joins |
|
|
122 | (1) |
|
How to combine inner and outer joins |
|
|
122 | (2) |
|
|
|
124 | (2) |
|
|
|
126 | (1) |
|
How to combine data from different tables |
|
|
126 | (2) |
|
How to combine data from the same table |
|
|
128 | (2) |
|
How to use the EXCEPT and INTERSECT operators |
|
|
130 | (6) |
|
Chapter 5 How to code summary queries |
|
|
|
How to work with aggregate functions |
|
|
136 | (1) |
|
How to code aggregate functions |
|
|
136 | (2) |
|
Queries that use aggregate functions |
|
|
138 | (2) |
|
How to group and summarize data |
|
|
140 | (1) |
|
How to code the GROUP BY and HAVING clauses |
|
|
140 | (2) |
|
Queries that use the GROUP BY and HAVING clauses |
|
|
142 | (2) |
|
How the HAVING clause compares to the WHERE clause |
|
|
144 | (2) |
|
How to code complex search conditions |
|
|
146 | (2) |
|
How to summarize data using SQL Server extensions |
|
|
148 | (1) |
|
How to use the ROLLUP operator |
|
|
148 | (2) |
|
How to use the CUBE operator |
|
|
150 | (2) |
|
How to use the GROUPING SETS operator |
|
|
152 | (2) |
|
How to use the OVER clause |
|
|
154 | (6) |
|
Chapter 6 How to code subqueries |
|
|
|
An introduction to subqueries |
|
|
160 | (1) |
|
|
|
160 | (2) |
|
How subqueries compare to joins |
|
|
162 | (2) |
|
How to code subqueries in search conditions |
|
|
164 | (1) |
|
How to use subqueries with the IN operator |
|
|
164 | (2) |
|
How to compare the result of a subquery with an expression |
|
|
166 | (2) |
|
How to use the ALL keyword |
|
|
168 | (2) |
|
How to use the ANY and SOME keywords |
|
|
170 | (2) |
|
How to code correlated subqueries |
|
|
172 | (2) |
|
How to use the EXISTS operator |
|
|
174 | (2) |
|
Other ways to use subqueries |
|
|
176 | (1) |
|
How to code subqueries in the FROM clause |
|
|
176 | (2) |
|
How to code subqueries in the SELECT clause |
|
|
178 | (2) |
|
Guidelines for working with complex queries |
|
|
180 | (1) |
|
A complex query that uses subqueries |
|
|
180 | (2) |
|
A procedure for building complex queries |
|
|
182 | (2) |
|
How to work with common table expressions |
|
|
184 | (1) |
|
|
|
184 | (2) |
|
How to code a recursive CTE |
|
|
186 | (6) |
|
Chapter 7 How to insert, update, and delete data |
|
|
|
How to create test tables |
|
|
192 | (1) |
|
How to use a copy of the database |
|
|
192 | (1) |
|
How to use the SELECT INTO statement |
|
|
192 | (2) |
|
|
|
194 | (1) |
|
How to insert a single row |
|
|
194 | (1) |
|
How to insert multiple rows |
|
|
194 | (2) |
|
How to insert default values and null values |
|
|
196 | (2) |
|
How to insert rows selected from another table |
|
|
198 | (2) |
|
How to modify existing rows |
|
|
200 | (1) |
|
How to perform a basic update operation |
|
|
200 | (2) |
|
How to use subqueries in an update operation |
|
|
202 | (2) |
|
How to use joins in an update operation |
|
|
204 | (2) |
|
How to delete existing rows |
|
|
206 | (1) |
|
How to perform a basic delete operation |
|
|
206 | (2) |
|
How to use subqueries and joins in a delete operation |
|
|
208 | (2) |
|
|
|
210 | (1) |
|
How to perform a basic merge operation |
|
|
210 | (1) |
|
How to code more complex merge operations |
|
|
210 | (6) |
|
Chapter 8 How to work with data types |
|
|
|
A review of the SQL data types |
|
|
216 | (1) |
|
|
|
216 | (2) |
|
|
|
218 | (2) |
|
|
|
220 | (2) |
|
|
|
222 | (2) |
|
|
|
224 | (2) |
|
|
|
226 | (1) |
|
How data conversion works |
|
|
226 | (2) |
|
How to convert data using the CAST function |
|
|
228 | (2) |
|
How to convert data using the CONVERT function |
|
|
230 | (2) |
|
How to use the TRY_CONVERT function |
|
|
232 | (2) |
|
How to use other data conversion functions |
|
|
234 | (4) |
|
Chapter 9 How to use functions |
|
|
|
How to work with string data |
|
|
238 | (1) |
|
A summary of the string functions |
|
|
238 | (4) |
|
How to solve common problems that occur with string data |
|
|
242 | (2) |
|
How to work with numeric data |
|
|
244 | (1) |
|
A summary of the numeric functions |
|
|
244 | (2) |
|
How to search for floating-point numbers |
|
|
246 | (2) |
|
How to work with date/time data |
|
|
248 | (1) |
|
A summary of the date/time functions |
|
|
248 | (4) |
|
How to parse dates and times |
|
|
252 | (2) |
|
How to perform operations on dates and times |
|
|
254 | (2) |
|
How to perform a date search |
|
|
256 | (2) |
|
How to perform a time search |
|
|
258 | (2) |
|
How to use the DATE_BUCKET function |
|
|
260 | (2) |
|
Other functions you should know about |
|
|
262 | (1) |
|
How to use the CASE expression |
|
|
262 | (2) |
|
How to use the OF and CHOOSE functions |
|
|
264 | (2) |
|
How to use the COALESCE expression and the ISNULL function |
|
|
266 | (2) |
|
How to use the GROUPING function |
|
|
268 | (2) |
|
How to use the GREATEST and LEAST functions |
|
|
270 | (2) |
|
How to use the ranking functions |
|
|
272 | (4) |
|
How to use the analytic functions |
|
|
276 | (8) |
|
Section 3 Database design and implementation |
|
|
|
Chapter 10 How to design a database |
|
|
|
How to design a data structure |
|
|
284 | (1) |
|
The basic steps for designing a data structure |
|
|
284 | (2) |
|
How to identify the data elements |
|
|
286 | (2) |
|
How to subdivide the data elements |
|
|
288 | (2) |
|
How to identify the tables and assign columns |
|
|
290 | (2) |
|
How to identify the primary and foreign keys |
|
|
292 | (2) |
|
How to enforce the relationships between tables |
|
|
294 | (2) |
|
|
|
296 | (2) |
|
How to identify the columns to be Indexed |
|
|
298 | (2) |
|
How to normalize a data structure |
|
|
300 | (1) |
|
|
|
300 | (2) |
|
How to apply the first normal form |
|
|
302 | (2) |
|
How to apply the second normal form |
|
|
304 | (2) |
|
How to apply the third normal form |
|
|
306 | (2) |
|
When and how to denormalize a data structure |
|
|
308 | (6) |
|
Chapter 11 How to create and maintain a database with SQL |
|
|
|
|
|
314 | (2) |
|
How to create databases, tables, and Indexes |
|
|
316 | (1) |
|
|
|
316 | (2) |
|
|
|
318 | (2) |
|
|
|
320 | (2) |
|
|
|
322 | (1) |
|
An introduction to constraints |
|
|
322 | (2) |
|
How to use check constraints |
|
|
324 | (2) |
|
How to use foreign key constraints |
|
|
326 | (2) |
|
How to change databases and tables |
|
|
328 | (1) |
|
How to delete an Index, table, or database |
|
|
328 | (2) |
|
|
|
330 | (2) |
|
How to work with sequences |
|
|
332 | (1) |
|
|
|
332 | (1) |
|
|
|
332 | (2) |
|
|
|
334 | (1) |
|
|
|
334 | (2) |
|
How to work with collations |
|
|
336 | (1) |
|
An introduction to character sets and encodings |
|
|
336 | (2) |
|
An introduction to collations |
|
|
338 | (2) |
|
|
|
340 | (2) |
|
How to specify a collation |
|
|
342 | (2) |
|
The script used to create the AP database |
|
|
344 | (1) |
|
|
|
344 | (1) |
|
How the DDL statements work |
|
|
344 | (8) |
|
Chapter 12 How to create and maintain a database with Managment Studio |
|
|
|
How to work with a database |
|
|
352 | (1) |
|
How to create or delete a database |
|
|
352 | (2) |
|
How to attach or detach a database |
|
|
354 | (2) |
|
How to back up and restore a database |
|
|
356 | (2) |
|
How to set the compatibility level for a database |
|
|
358 | (2) |
|
|
|
360 | (1) |
|
How to create, modify, or delete a table |
|
|
360 | (2) |
|
How to work with foreign key relationships |
|
|
362 | (2) |
|
How to work with Indexes and keys |
|
|
364 | (2) |
|
How to work with check constraints |
|
|
366 | (2) |
|
How to examine table dependencies |
|
|
368 | (2) |
|
|
|
370 | (1) |
|
How to generate scripts for databases and tables |
|
|
370 | (2) |
|
How to generate a change script when you modify a table |
|
|
372 | (6) |
|
Section 4 Advanced SQL skills |
|
|
|
Chapter 13 How to work with views An introduction to views |
|
|
378 | (20) |
|
|
|
378 | (2) |
|
|
|
380 | (2) |
|
How to create and manage views |
|
|
382 | (1) |
|
|
|
382 | (2) |
|
Examples that create views |
|
|
384 | (2) |
|
How to create an updatable view |
|
|
386 | (2) |
|
How to delete or modify a view |
|
|
388 | (2) |
|
|
|
390 | (1) |
|
How to update rows through a view |
|
|
390 | (2) |
|
How to insert rows through a view |
|
|
392 | (1) |
|
How to delete rows through a view |
|
|
392 | (2) |
|
How to use the catalog views |
|
|
394 | (4) |
|
Chapter 14 How to code scripts |
|
|
|
An introduction to scripts |
|
|
398 | (1) |
|
|
|
398 | (2) |
|
The Transact-SQL statements for script processing |
|
|
400 | (2) |
|
How to work with variables and temporary tables |
|
|
402 | (1) |
|
How to work with scalar variables |
|
|
402 | (2) |
|
How to work with table variables |
|
|
404 | (2) |
|
How to work with temporary tables |
|
|
406 | (2) |
|
A comparison of the five types of Transact-SQL table objects |
|
|
408 | (2) |
|
How to control the execution of a script |
|
|
410 | (1) |
|
How to perform conditional processing |
|
|
410 | (2) |
|
How to test for the existence of a database object |
|
|
412 | (2) |
|
How to perform repetitive processing |
|
|
414 | (2) |
|
|
|
416 | (2) |
|
|
|
418 | (2) |
|
How to use surround-with snippets |
|
|
420 | (2) |
|
Advanced scripting techniques |
|
|
422 | (1) |
|
How to use the system functions |
|
|
422 | (2) |
|
How to change the session settings |
|
|
424 | (2) |
|
|
|
426 | (6) |
|
Chapter 15 How to code stored procedures, functions, and triggers |
|
|
|
Procedural programming options in Transact-SQL |
|
|
432 | (1) |
|
|
|
432 | (1) |
|
Stored procedures, user-defined functions, and triggers |
|
|
432 | (2) |
|
How to code stored procedures |
|
|
434 | (1) |
|
An introduction to stored procedures |
|
|
434 | (2) |
|
How to create a stored procedure |
|
|
436 | (2) |
|
How to declare and work with parameters |
|
|
438 | (2) |
|
How to call procedures with parameters |
|
|
440 | (2) |
|
How to work with return values |
|
|
442 | (2) |
|
How to validate data and raise errors |
|
|
444 | (2) |
|
A stored procedure that manages insert operations |
|
|
446 | (6) |
|
How to pass a table as a parameter |
|
|
452 | (2) |
|
How to delete or change a stored procedure |
|
|
454 | (2) |
|
How to work with system stored procedures |
|
|
456 | (2) |
|
How to code user-defined functions |
|
|
458 | (1) |
|
An introduction to user-defined functions |
|
|
458 | (2) |
|
How to create a scalar-valued function |
|
|
460 | (2) |
|
How to create and use a table-valued function |
|
|
462 | (2) |
|
How to delete or change a function |
|
|
464 | (2) |
|
|
|
466 | (1) |
|
|
|
466 | (2) |
|
How to use AFTER triggers |
|
|
468 | (2) |
|
How to use INSTEAD OF triggers |
|
|
470 | (2) |
|
How to use triggers to enforce data consistency |
|
|
472 | (2) |
|
How to delete or change a trigger |
|
|
474 | (6) |
|
Chapter 16 How to manage transactions and locking |
|
|
|
How to work with transactions |
|
|
480 | (1) |
|
How transactions maintain data integrity |
|
|
480 | (2) |
|
SQL statements for handling transactions |
|
|
482 | (2) |
|
How to work with nested transactions |
|
|
484 | (2) |
|
How to work with save points |
|
|
486 | (2) |
|
An introduction to concurrency and locking |
|
|
488 | (1) |
|
The four concurrency problems that locks can prevent |
|
|
488 | (2) |
|
How to set the transaction isolation level |
|
|
490 | (2) |
|
How SQL Server manages locking |
|
|
492 | (1) |
|
Lockable resources and lock escalation |
|
|
492 | (2) |
|
Lock modes and lock promotion |
|
|
494 | (2) |
|
|
|
496 | (2) |
|
|
|
498 | (1) |
|
Two transactions that deadlock |
|
|
498 | (2) |
|
Coding techniques that prevent deadlocks |
|
|
500 | (4) |
|
Chapter 17 How to manage database security |
|
|
|
How to work with SQL Server login IDs |
|
|
504 | (1) |
|
An introduction to SQL Server security |
|
|
504 | (2) |
|
How to change the authentication mode |
|
|
506 | (2) |
|
|
|
508 | (2) |
|
How to delete or change login IDs or passwords |
|
|
510 | (2) |
|
How to work with database users |
|
|
512 | (2) |
|
|
|
514 | (2) |
|
How to work with permissions |
|
|
516 | (1) |
|
How to grant or revoke object permissions |
|
|
516 | (2) |
|
The SQL Server object permissions |
|
|
518 | (2) |
|
How to grant or revoke schema permissions |
|
|
520 | (2) |
|
How to grant or revoke database permissions |
|
|
522 | (2) |
|
How to grant or revoke server permissions |
|
|
524 | (2) |
|
|
|
526 | (1) |
|
How to work with the fixed server roles |
|
|
526 | (2) |
|
How to work with user-defined server roles |
|
|
528 | (2) |
|
How to display information about server roles and role members |
|
|
530 | (2) |
|
How to work with the fixed database roles |
|
|
532 | (2) |
|
How to work with user-defined database roles |
|
|
534 | (2) |
|
How to display information about database roles and role members |
|
|
536 | (2) |
|
How to deny permissions granted by role membership |
|
|
538 | (2) |
|
How to work with application roles |
|
|
540 | (2) |
|
How to manage security using Management Studio |
|
|
542 | (1) |
|
How to work with login IDs |
|
|
542 | (2) |
|
How to work with the server roles for a login ID |
|
|
544 | (2) |
|
How to assign database access and roles by login ID |
|
|
546 | (2) |
|
How to assign user permissions to database objects |
|
|
548 | (2) |
|
How to work with database permissions |
|
|
550 | (8) |
|
Section 5 An introduction to Azure |
|
|
|
Chapter 18 How to use Azure Data Studio |
|
|
|
An introduction to Azure Data Studio |
|
|
558 | (1) |
|
How to open the Connection dialog box |
|
|
558 | (2) |
|
How to connect to a database server |
|
|
560 | (2) |
|
How to navigate through the database objects |
|
|
562 | (2) |
|
How to view and modify the database |
|
|
564 | (1) |
|
How to view the column definitions of a table |
|
|
564 | (1) |
|
How to modify the column definitions |
|
|
564 | (2) |
|
How to view the data of a table |
|
|
566 | (1) |
|
|
|
566 | (2) |
|
|
|
568 | (1) |
|
How to enter and run a query |
|
|
568 | (2) |
|
How to handle syntax errors |
|
|
570 | (2) |
|
How to open and save queries |
|
|
572 | (2) |
|
How to use the Explorer window |
|
|
574 | (2) |
|
More skills as you need them |
|
|
576 | (1) |
|
How to work with extensions |
|
|
576 | (2) |
|
|
|
578 | (6) |
|
Chapter 19 How to work with Azure SQL |
|
|
|
How to create and configure an Azure SQL database |
|
|
584 | (1) |
|
An introduction to Azure SQL |
|
|
584 | (2) |
|
How to create a SQL database |
|
|
586 | (2) |
|
How to configure a SQL server |
|
|
588 | (2) |
|
How to configure a SQL database |
|
|
590 | (2) |
|
How to use Data Studio to work with an Azure SQL database |
|
|
592 | (1) |
|
How to connect to a SQL database |
|
|
592 | (2) |
|
How to run a script that creates the tables for a SQL database |
|
|
594 | (2) |
|
How to query a SQL database |
|
|
596 | (2) |
|
How to migrate from SQL Server to Azure SQL |
|
|
598 | (1) |
|
An introduction to Data Migration Assistant |
|
|
598 | (2) |
|
How to migrate a SQL Server database to Azure |
|
|
600 | (4) |
|
Appendix A How to set up your computer for this book |
|
|
|
How to install SQL Server 2022 |
|
|
|
|
|
604 | (1) |
|
How to install SQL Server Management Studio |
|
|
604 | (2) |
|
How to download the files for this book |
|
|
606 | (2) |
|
How to create the databases for this book |
|
|
608 | (1) |
|
How to restore the databases for this book |
|
|
608 | (2) |
|
How to install Azure Data Studio |
|
|
610 | |
