Master SQL Server 2012 with Murach's SQL Server 2012 for Developers
Murach's SQL Server 2012 for Developers: A Comprehensive Guide
If you want to learn how to develop database applications using SQL Server 2012, you need a book that covers both the SQL skills and the developer skills that you need. That's what Murach's SQL Server 2012 for Developers offers you. In this article, I'll give you an overview of what this book is about, why you should learn SQL Server 2012, and how this book differs from other SQL books.
Murach\'s SQL Server 2012 for Developers
Introduction
What is Murach's SQL Server 2012 for Developers?
Murach's SQL Server 2012 for Developers is a book that teaches you how to use SQL Server 2012 as a database developer. It covers both the essential and the advanced SQL skills that you need to query, manipulate, and manage data in a relational database. It also covers the database developer skills that you need to use SQL Server with Visual Studio, ASP.NET web applications, and ADO.NET Entity Framework.
This book is divided into three parts. Part 1 covers the essential SQL skills that every database developer should know. Part 2 covers the advanced SQL skills that will make you more productive and efficient as a database developer. Part 3 covers the database developer skills that will help you integrate SQL Server with your .NET applications.
Why should you learn SQL Server 2012?
SQL Server is one of the most popular and powerful relational database management systems (RDBMS) in the world. It is used by millions of organizations and developers for various purposes, such as data analysis, business intelligence, web development, mobile development, cloud computing, and more.
SQL Server 2012 is the latest version of SQL Server that introduces many new features and enhancements that make it more reliable, scalable, secure, and easy to use. Some of these features include:
AlwaysOn Availability Groups: This feature allows you to create groups of databases that can fail over together as a unit, ensuring high availability and disaster recovery.
Contained Databases: This feature allows you to isolate databases from the server instance, making them easier to move, manage, and secure.
Columnstore Indexes: This feature allows you to create indexes that store data in a columnar format, improving the performance of analytical queries.
Sequence Objects: This feature allows you to create objects that generate sequential values, similar to identity columns, but with more flexibility and control.
FileTables: This feature allows you to store files and documents in a special type of table that integrates with the Windows file system, enabling full-text search and semantic search.
Data Quality Services: This feature allows you to improve the quality of your data by cleansing, matching, and standardizing it.
Integration Services: This feature allows you to create and run packages that perform various data integration tasks, such as extracting, transforming, and loading (ETL) data.
Reporting Services: This feature allows you to create and publish reports that present data in various formats, such as tables, charts, maps, and gauges.
Analysis Services: This feature allows you to create and query multidimensional and tabular models that provide advanced data analysis capabilities, such as OLAP, data mining, and business intelligence.
By learning SQL Server 2012, you will be able to take advantage of these features and more, and develop database applications that are fast, reliable, secure, and easy to maintain.
How is this book different from other SQL books?
There are many SQL books out there, but Murach's SQL Server 2012 for Developers is different from them in several ways. Here are some of the reasons why this book is unique and effective:
It covers both the SQL skills and the developer skills that you need. Most SQL books focus only on the SQL skills, but not on how to use them with .NET applications. This book covers both aspects, so you can learn how to use SQL Server as a database developer.
It uses a paired-pages format that makes learning easy. Each topic is presented in two pages: the left page explains the concept, and the right page shows an example. This way, you can see how the concept works in practice, and you can easily flip through the pages to review or reference a topic.
It provides practical exercises that reinforce your learning. Each chapter ends with a set of exercises that test your understanding of the material and give you practice in applying it. The exercises are based on realistic scenarios that simulate real-world database development tasks.
It includes a downloadable sample database that you can use for learning and testing. The book comes with a sample database called AP that contains data for a fictitious company called AdventureWorks Products. You can use this database to follow along with the examples and exercises in the book, and to experiment with your own queries and code.
If you want a book that covers both the SQL skills and the developer skills that you need to develop database applications using SQL Server 2012, Murach's SQL Server 2012 for Developers is the book for you.
Part 1: The Essential SQL Skills
Chapter 1: An introduction to relational databases and SQL
In this chapter, you will learn what a relational database is, how it is organized, and how it is used. You will also learn what SQL is, how it works, and how it is used to communicate with a relational database. You will learn about the following topics:
The components of a relational database: tables, columns, rows, keys, relationships, constraints, indexes, views, stored procedures, functions, triggers
The types of SQL statements: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), TCL (Transaction Control Language)
The basic syntax of SQL statements: keywords, identifiers, literals, operators, expressions
The tools for working with SQL Server: Management Studio (SSMS), Query Editor (QE), Object Explorer (OE), Solution Explorer (SE), Database Diagrams (DD)
Chapter 2: How to use the Management Studio
In this chapter, you will learn how to use the Management Studio (SSMS), which is the main tool for working with SQL Server. You will learn how to perform various tasks using SSMS, such as:
How to connect to a server instance
How to create and open a query window
How to execute SQL statements
How to view and save query results
How to use query options and shortcuts
How to use IntelliSense and code snippets
Chapter 3: How to retrieve data from a single table
In this chapter, you will learn how to use the SELECT statement, which is the most common and important SQL statement. You will learn how to use the SELECT statement to retrieve data from a single table, and how to use various clauses and options to modify the result set. You will learn about the following topics:
How to use the SELECT clause to specify the columns to retrieve
How to use the FROM clause to specify the table to retrieve data from
How to use the WHERE clause to filter the rows based on a condition
How to use the ORDER BY clause to sort the rows based on one or more columns
How to use the TOP clause to limit the number of rows to retrieve
How to use the DISTINCT keyword to eliminate duplicate rows
How to use aliases to rename columns and tables
How to use comments to document your queries
Chapter 4: How to retrieve data from two or more tables
In this chapter, you will learn how to use joins, which are a way of combining data from two or more tables based on a common column. You will learn how to use different types of joins, such as inner joins, outer joins, cross joins, and self joins. You will also learn how to use subqueries, which are queries nested within other queries. You will learn about the following topics:
How to use inner joins to retrieve matching rows from two or more tables
How to use outer joins to retrieve matching and non-matching rows from two or more tables
How to use cross joins to retrieve all possible combinations of rows from two or more tables
How to use self joins to join a table with itself
How to use join conditions and join types to control how tables are joined
How to use subqueries in the WHERE, FROM, and SELECT clauses
How to use correlated subqueries and EXISTS operators
How to use IN and NOT IN operators with subqueries
Chapter 5: How to code summary queries
In this chapter, you will learn how to use aggregate functions, which are functions that perform calculations on a set of values and return a single value. You will also learn how to use the GROUP BY and HAVING clauses, which are used to group and filter rows based on aggregate values. You will learn about the following topics:
How to use aggregate functions such as SUM, AVG, MIN, MAX, COUNT, and DISTINCT
How to use the GROUP BY clause to group rows by one or more columns
How to use the HAVING clause to filter groups based on a condition
How to use aggregate functions with NULL values
How to use aggregate functions with subqueries
How to use aggregate functions with joins
Chapter 6: How to code subqueries
Chapter 6: How to code subqueries
In this chapter, you will learn more about subqueries, which are queries nested within other queries. You will learn how to use different types of subqueries, such as scalar subqueries, multi-valued subqueries, table-valued subqueries, and common table expressions. You will also learn how to use operators and functions with subqueries, such as ANY, ALL, SOME, IN, NOT IN, EXISTS, NOT EXISTS, and RANK. You will learn about the following topics:
How to use scalar subqueries to return a single value
How to use multi-valued subqueries to return a set of values
How to use table-valued subqueries to return a table of values
How to use common table expressions to create temporary named subqueries
How to use ANY, ALL, and SOME operators with subqueries
How to use IN and NOT IN operators with subqueries
How to use EXISTS and NOT EXISTS operators with subqueries
How to use RANK function with subqueries
Chapter 7: How to insert, update, and delete data
In this chapter, you will learn how to use the INSERT, UPDATE, and DELETE statements, which are used to modify data in a table. You will learn how to use these statements with various options and clauses, such as OUTPUT, FROM, WHERE, and JOIN. You will also learn how to use transactions and locking to ensure data integrity and concurrency. You will learn about the following topics:
How to use the INSERT statement to add new rows to a table
How to use the UPDATE statement to modify existing rows in a table
How to use the DELETE statement to remove existing rows from a table
How to use the OUTPUT clause to return modified rows
How to use the FROM clause with UPDATE and DELETE statements
How to use the WHERE clause with UPDATE and DELETE statements
How to use joins with UPDATE and DELETE statements
How to use transactions and locking to control data modifications
Chapter 8: How to work with data types and functions
In this chapter, you will learn how to work with data types and functions in SQL Server. You will learn how to use various data types, such as numeric, character, date and time, binary, and special data types. You will also learn how to use various functions, such as string functions, date and time functions, math functions, conversion functions, and logical functions. You will learn about the following topics:
How to use numeric data types such as int, decimal, float, money
Chapter 8: How to work with data types and functions
In this chapter, you will learn how to work with data types and functions in SQL Server. You will learn how to use various data types, such as numeric, character, date and time, binary, and special data types. You will also learn how to use various functions, such as string functions, date and time functions, math functions, conversion functions, and logical functions. You will learn about the following topics:
How to use numeric data types such as int, decimal, float, money
How to use character data types such as char, varchar, nchar, nvarchar
How to use date and time data types such as date, time, datetime, datetime2
How to use binary data types such as binary, varbinary
How to use special data types such as xml, uniqueidentifier, sql_variant
How to use string functions such as LEN, SUBSTRING, REPLACE, UPPER, LOWER
How to use date and time functions such as GETDATE, DATEPART, DATEDIFF, DATEADD
How to use math functions such as ABS, ROUND, CEILING, FLOOR
How to use conversion functions such as CAST, CONVERT
How to use logical functions such as CASE, COALESCE, NULLIF
Part 2: The Advanced SQL Skills
Chapter 9: How to design a database
In this chapter, you will learn how to design a database using the principles of database normalization. You will learn how to identify the entities and attributes of a database, how to define the relationships and constraints among them, and how to create a logical data model using an entity-relationship diagram (ERD). You will also learn how to convert a logical data model into a physical data model using SQL Server tools. You will learn about the following topics:
How to identify the entities and attributes of a database
How to define the primary keys and foreign keys of a database
How to define the cardinality and optionality of relationships
How to apply the rules of normalization to eliminate data anomalies
How to create an entity-relationship diagram (ERD) using symbols and notation
How to use SQL Server tools to create a database diagram from an ERD
How to use SQL Server tools to generate SQL scripts from a database diagram
Chapter 10: How to create and maintain a database
Chapter 10: How to create and maintain a database
In this chapter, you will learn how to use the Data Definition Language (DDL) statements to create and maintain a database. You will learn how to use the CREATE statement to create tables, indexes, views, stored procedures, functions, triggers, and other database objects. You will also learn how to use the ALTER and DROP statements to modify and delete database objects. You will learn about the following topics:
How to use the CREATE TABLE statement to create a table with columns, constraints, and indexes
How to use the ALTER TABLE statement to add, modify, or drop columns, constraints, and indexes
How to use the DROP TABLE statement to delete a table
How to use the CREATE INDEX statement to create clustered and nonclustered indexes
How to use the ALTER INDEX and DROP INDEX statements to modify and delete indexes
How to use the CREATE VIEW statement to create a view based on one or more tables or views
How to use the ALTER VIEW and DROP VIEW statements to modify and delete views
How to use the CREATE PROCEDURE statement to create a stored procedure that contains one or more SQL statements
How to use the ALTER PROCEDURE and DROP PROCEDURE statements to modify and delete stored procedures
How to use the CREATE FUNCTION statement to create a function that returns a scalar value or a table of values
How to use the ALTER FUNCTION and DROP FUNCTION statements to modify and delete functions
How to use the CREATE TRIGGER statement to create a trigger that executes when a data modification event occurs on a table or view
How to use the ALTER TRIGGER and DROP TRIGGER statements to modify and delete triggers
Chapter 11: How to create and use views
In this chapter, you will learn more about views, which are virtual tables that are based on one or more tables or views. You will learn how to create and use views for various purposes, such as simplifying queries, hiding complexity, enforcing security, and improving performance. You will learn about the following topics:
How to create simple views using the CREATE VIEW statement
How to query views using the SELECT statement
How to modify views using the ALTER VIEW statement
How to delete views using the DROP VIEW statement
How to create complex views using joins, subqueries, aggregate functions, and other SQL features
How to create indexed views using the CREATE INDEX statement
Chapter 11: How to create and use views
In this chapter, you will learn more about views, which are virtual tables that are based on one or more tables or views. You will learn how to create and use views for various purposes, such as simplifying queries, hiding complexity, enforcing security, and improving performance. You will learn about the following topics:
How to create simple views using the CREATE VIEW statement
How to query views using the SELECT statement
How to modify views using the ALTER VIEW statement
How to delete views using the DROP VIEW statement
How to create complex views using joins, subqueries, aggregate functions, and other SQL features
How to create indexed views using the CREATE INDEX statement
How to use views with INSERT, UPDATE, and DELETE statements
How to use views with stored procedures and functions
How to use views to implement security and permissions
How to use views to optimize query performance and execution plans
Chapter 12: How to create and use stored procedures
In this chapter, you will learn more about stored procedures, which are precompiled SQL statements that are stored in the database. You will learn how to create and use stored procedures for various purposes, such as encapsulating logic, improving performance, and enhancing security. You will learn about the following topics:
How to create simple stored procedures using the CREATE PROCEDURE statement
How to execute stored procedures using the EXECUTE statement or the EXEC keyword
How to modify stored procedures using the ALTER PROCEDURE statement
How to delete stored procedures using the DROP PROCEDURE statement
How to create complex stored procedures using parameters, variables, control-of-flow statements, error handling, and other SQL features
How to use output parameters and return values with stored procedures
How to use stored procedures with INSERT, UPDATE, and DELETE statements
How to use stored procedures with views and functions
How to use stored procedures to implement security and permissions
How to use stored procedures to optimize query performance and execution plans
Chapter 13: How to use transactions and locking
In this chapter, you will learn how to use transactions and locking in SQL Server. Transactions are units of wor