Murach's SQL Server 2022 for developers

The latest edition of Murach’s classic SQL Server book does a better job than ever of teaching the SQL statements that every database developer should know. Each chapter features clear examples, easy-to-understand explanations, best practices, and helpful tips. To start, this book presents the SQL statements for retrieving and updating the data in a database. These are the SQL statements that you’ll use every day. Then, it shows how to design a database, how to implement that design, and how to work with database features like views, scripts, stored procedures, functions, triggers, transactions, and security. Finally, it shows how to work with an Azure SQL database that runs in the cloud as a service that’s available from the Azure cloud computing platform. The result? You’ll be able to write SQL that’s thoroughly professional, and you’ll be able to work far more effectively than most of your colleagues. Every Murach book guarantees high quality. The realistic examples show how each feature works in context. The exercises at the end of each chapter let you gain valuable hands-on experience. And the distinctive “paired-pages” format is ideal for learning and reference. Murach's SQL Server 2022 Table of Contents Expanded Contents Introduction Section 1 - An introduction to SQL Chapter 1 - An introduction to relational databases and SQL An introduction to client/server systems The hardware components of a client/server system The software components of a client/server system Other client/server system architectures An introduction to the relational database model How a database table is organized How the tables in a relational database are related How the columns in a table are defined An introduction to SQL and SQL-based systems A brief history of SQL A comparison of four relational databases The Transact-SQL statements An introduction to the SQL statements Typical statements for working with database objects How to query a single table How to join data from two or more tables How to add, update, and delete data in a table SQL coding guidelines Perspective Chapter 2 - How to use Management Studio An introduction to SQL Server 2022 A summary of SQL Server 2022 tools How to start and stop the database engine How to enable remote connections An introduction to Management Studio How to connect to a database server How to navigate through the database objects How to view and modify the database How to create database diagrams How to view the column definitions of a table How to modify the column definitions How to view the data of a table How to modify the data of a table How to work with queries How to enter and execute a query How to handle syntax errors How to open and save queries How to view the documentation for SQL Server Perspective Section 2 - The essential SQL skills Chapter 3 - How to retrieve data from a single table An introduction to the SELECT statement The basic syntax of the SELECT statement SELECT statement examples How to code the SELECT clause How to code column specifications How to name the columns in a result set How to code string expressions How to code arithmetic expressions How to use functions How to use the DISTINCT keyword to eliminate duplicate rows How to use the TOP clause to return a subset of selected rows How to code the WHERE clause How to use comparison operators How to use the AND, OR, and NOT logical operators How to use the IN operator How to use the BETWEEN operator How to use the LIKE operator How to use the IS NULL clause How to code the ORDER BY clause How to sort a result set by a column name How to sort a result set by an alias, an expression, or a column number How to retrieve a range of selected rows Perspective Chapter 4 - How to retrieve data from two or more tables How to work with inner joins How to code an inner join When and how to use table aliases How to work with tables from different databases How to use compound join conditions How to use a self-join Inner joins that join more than two tables How to use the implicit inner join syntax How to work with outer joins How to code an outer join Outer join examples Outer joins that join more than two tables Other skills for working with joins How to combine inner and outer joins How to use cross joins How to work with unions How to combine data from different tables How to combine data from the same table How to use the EXCEPT and INTERSECT operators Perspective Chapter 5 - How to code summary queries How to work with aggregate functions How to code aggregate functions Queries that use aggregate functions How to group and summarize data How to code the GROUP BY and HAVING clauses Queries that use the GROUP BY and HAVING clauses How the HAVING clause compares to the WHERE clause How to code complex search conditions How to summarize data using SQL Server extensions How to use the ROLLUP operator How to use the CUBE operator How to use the GROUPING SETS operator How to use the OVER clause Perspective Chapter 6 - How to code subqueries An introduction to subqueries How to use subqueries How subqueries compare to joins How to code subqueries in search conditions How to use subqueries with the IN operator How to compare the result of a subquery with an expression How to use the ALL keyword How to use the ANY and SOME keywords How to code correlated subqueries How to use the EXISTS operator Other ways to use subqueries How to code subqueries in the FROM clause How to code subqueries in the SELECT clause Guidelines for working with complex queries A complex query that uses subqueries A procedure for building complex queries How to work with common table expressions How to code a CTE How to code a recursive CTE Perspective Chapter 7 - How to insert, update, and delete data How to create test tables How to use a copy of the database How to use the SELECT INTO statement How to insert new rows How to insert a single row How to insert multiple rows How to insert default values and null values How to insert rows selected from another table How to modify existing rows How to perform a basic update operation How to use subqueries in an update operation How to use joins in an update operation How to delete existing rows How to perform a basic delete operation How to use subqueries and joins in a delete operation How to merge rows How to perform a basic merge operation How to code more complex merge operations Perspective Chapter 8 - How to work with data types A review of the SQL data types Data type overview The numeric types The string types The date/time types The large value types How to convert data How data conversion works How to convert data using the CAST function How to convert data using the CONVERT function How to use the TRY_CONVERT function How to use other data conversion functions Perspective Chapter 9 - How to use functions How to work with string data A summary of the string functions How to solve common problems that occur with string data How to work with numeric data A summary of the numeric functions How to search for floating-point numbers How to work with date/time data A summary of the date/time functions How to parse dates and times How to perform operations on dates and times How to perform a date search How to perform a time search How to use the DATE_BUCKET function Other functions you should know about How to use the CASE expression How to use the IIF and CHOOSE functions How to use the COALESCE expression and the ISNULL function How to use the GROUPING function How to use the GREATEST and LEAST functions How to use the ranking functions How to use the analytic functions Perspective Section 3 - Database design and implementation Chapter 10 - How to design a database How to design a data structure The basic steps for designing a data structure How to identify the data elements How to subdivide the data elements How to identify the tables and assign columns How to identify the primary and foreign keys How to enforce the relationships between tables How normalization works How to identify the columns to be indexed How to normalize a data structure The seven normal forms How to apply the first normal form How to apply the second normal form How to apply the third normal form When and how to denormalize a data structure Perspective Chapter 11 - How to create and maintain a database with SQL An introduction to DDL How to create databases, tables, and indexes How to create a database How to create a table How to create an index How to use constraints An introduction to constraints How to use check constraints How to use foreign key constraints How to change databases and tables How to delete an index, table, or database How to alter a table How to work with sequences How to create a sequence How to use a sequence How to delete a sequence How to alter a sequence How to work with collations An introduction to character sets and encodings An introduction to collations How to view collations How to specify a collation The script used to create the AP database How the script works How the DDL statements work Perspective Chapter 12 - How to create and maintain a database with Management Studio How to work with a database How to create or delete a database How to attach or detach a database How to back up and restore a database How to set the compatibility level for a database How to work with tables How to create, modify, or delete a table How to work with foreign key relationships How to work with indexes and keys How to work with check constraints How to examine table dependencies How to generate scripts How to generate scripts for databases and tables How to generate a change script when you modify a table Perspective Section 4 - Advanced SQL skills Chapter 13 - How to work with views An introduction to views How views work Benefits of using views How to create and manage views How to create a view Examples that create views How to create an updatable view How to delete or modify a view How to use views How to update rows through a view How to insert rows through a view How to delete rows through a view How to use the catalog views Perspective Chapter 14 - How to code scripts An introduction to scripts How to work with scripts The Transact-SQL statements for script processing How to work with variables and temporary tables How to work with scalar variables How to work with table variables How to work with temporary tables A comparison of the five types of Transact-SQL table objects How to control the execution of a script How to perform conditional processing How to test for the existence of a database object How to perform repetitive processing How to use a cursor How to handle errors How to use surround-with snippets Advanced scripting techniques How to use the system functions How to change the session settings How to use dynamic SQL Perspective Chapter 15 - How to code stored procedures, functions, and triggers Procedural programming options in Transact-SQL Scripts Stored procedures, user-defined functions, and triggers How to code stored procedures An introduction to stored procedures How to create a stored procedure How to declare and work with parameters How to call procedures with parameters How to work with return values How to validate data and raise errors A stored procedure that manages insert operations How to pass a table as a parameter How to delete or change a stored procedure How to work with system stored procedures How to code user-defined functions An introduction to user-defined functions How to create a scalar-valued function How to create and use a table-valued function How to delete or change a function How to code triggers How to create a trigger How to use AFTER triggers How to use INSTEAD OF triggers How to use triggers to enforce data consistency How to delete or change a trigger Perspective Chapter 16 - How to manage transactions and locking How to work with transactions How transactions maintain data integrity SQL statements for handling transactions How to work with nested transactions How to work with save points An introduction to concurrency and locking The four concurrency problems that locks can prevent How to set the transaction isolation level How SQL Server manages locking Lockable resources and lock escalation Lock modes and lock promotion Lock mode compatibility How to prevent deadlocks Two transactions that deadlock Coding techniques that prevent deadlocks Perspective Chapter 17 - How to manage database security How to work with SQL Server login IDs An introduction to SQL Server security How to change the authentication mode How to create login IDs How to delete or change login IDs or passwords How to work with database users How to work with schemas How to work with permissions How to grant or revoke object permissions The SQL Server object permissions How to grant or revoke schema permissions How to grant or revoke database permissions How to grant or revoke server permissions How to work with roles How to work with the fixed server roles How to work with user-defined server roles How to display information about server roles and role members How to work with the fixed database roles How to work with user-defined database roles How to display information about database roles and role members How to deny permissions granted by role membership How to work with application roles How to manage security using Management Studio How to work with login IDs How to work with the server roles for a login ID How to assign database access and roles by login ID How to assign user permissions to database objects How to work with database permissions Perspective Section 5 - An introduction to Azure Chapter 18 - How to use Azure Data Studio An introduction to Azure Data Studio How to open the Connection dialog box How to connect to a database server How to navigate through the database objects How to view and modify the database How to view the column definitions of a table How to modify the column definitions How to view the data of a table How to modify the data How to work with queries How to enter and run a query How to handle syntax errors How to open and save queries How to use the Explorer window More skills as you need them How to work with extensions How to visualize data Perspective Chapter 19 - How to work with Azure SQL How to create and configure an Azure SQL database An introduction to Azure SQL How to create a SQL database How to configure a SQL server How to configure a SQL database How to use Data Studio to work with an Azure SQL database How to connect to a SQL database How to run a script that creates the tables for a SQL database How to query a SQL database How to migrate from SQL Server to Azure SQL An introduction to Data Migration Assistant How to migrate a SQL Server database to Azure Perspective Appendix A - How to set up your computer for this book How to install SQL Server 2022 Express How to install SQL Server Management Studio How to download the files for this book How to create the databases for this book How to restore the databases for this book How to install Azure Data Studio Index Murach booklist Software and downloadable files for this book Back Cover