Learning Microsoft Transact - SQL

Trace the evolution of databases from unstructured data to structured relational models, guided by Dr. E. F. Codd and the rise of sql-based systems including Microsoft sql server and Oracle.

Define a database as an organized collection of data that is easily accessed, updated, protected, and stored, with digital databases offering fast sorting and data integrity considerations.

Compare OLTP and OLAP databases to reveal their distinct structures and how they govern inserts, updates, deletes, and fast data retrieval.

Clarifies common sql acronyms and terminology, from ddl and dml to dmv and rdbms, and explains stored procedures, user defined functions, resultsets, the query optimizer, and scripts.

Explore SQL Server editions—from Express Edition for learning to Enterprise Edition for full features—and get hands-on with Transact-SQL by installing SQL Server 2014 and practicing code.

Download SQL Server Express with tools from Microsoft and install SQL Server Management Studio, meeting Windows 7 SP1+, 512 MB RAM, 1 GHz processor, and 6 GB disk space.

Install sql server express by running the downloaded executable, performing a standalone installation, and selecting core features like database engine services and management tools, with license terms accepted, part 1.

Continue installing SQL Server Express in part 2, configure instance type, authentication mode, and administrator accounts, monitor the installation progress, and complete setup of SQL Server 2014 Express.

Explore SQL Server Management Studio on Windows 7. Connect to your instance, browse databases via Object Explorer, and use the query editor with pinning and auto-hide for a productive workflow.

Learn how to start SQL Server Management Studio on Windows 8/2012 via the start screen, pin the SSMS tile to the taskbar, and access related SQL Server services.

Discover how to use the query analyzer in SQL Server Management Studio to write and execute queries against SQL Server Express, view results, and customize the editor with line numbers.

Attach the Adventure Works 2012 sample database to SQL Server Express via Management Studio, placing the data files in the folder for ready tables and learning select, insert, modify queries.

Discover how SQL is a declarative language, not procedural, and how the database engine determines results, with execution versus syntax order and the from and where clauses.

Learn how sql books online serves as the definitive source for sql server documentation and transact sequel language reference, and how to switch between online and local help.

Explore the four categories of Transact-SQL: data manipulation language (select, insert, update, delete), data definition language (create, alter, draw), data control language (permissions), and transaction control language (commit, rollback).

Master the four core DML commands in T-SQL: select, insert, update, and delete, learning how each retrieves, adds, changes, or removes data across tables.

Explore how a query is executed by a five-step process—submission, parsing, binding, optimization, and execution—driven by the query optimizer and resulting in a cached execution plan.

Explore how to work with queries and scripts in SQL Server Management Studio, generate and save T-SQL commands from graphical actions, and script tables and views for learning and reuse.

Create your first database with the create database command in Transact-SQL, using test db as an example, then explore options, syntax, and defaults in SQL Server Management Studio.

Explore three core database creation options: file size and growth, file locations, and file groups. Review initial sizes, max size, growth behavior, and transaction log placement on separate disks.

Explore how file groups organize data files into containers for efficient administration and backups. Use primary and user-defined groups to place objects on specific disks and stripe I/O for performance.

Explore how tables organize data as rows and columns and plan their structure to store related information. Understand data types and primary keys to protect integrity and prevent duplicates.

Explore the three T-SQL character datatypes—char, varchar, and text—and their Unicode and non-Unicode storage, with fixed, variable, or maximum length usage.

Compare non-Unicode and Unicode character data types in Transact-SQL, reveal two-byte encoding for broader character sets, and illustrate using Unicode types (nchar, nvarchar) for global languages.

Master T-SQL numeric datatypes by comparing exact types (bigint, int, smallint, tinyint, decimal) with approximate types (float); learn to choose data types based on value range, memory use, and precision.

Explore how data type synonyms in Transact-SQL map ISO standard types, such as varchar and character varying, and learn how cross-platform environments affect code and table definitions.

Create a table named employee in the test database using the create table command, with last name and first name as varchar(25), hair color as varchar(12), and age as tinyint.

Explore how primary keys prevent duplicate rows and protect data integrity by using single-column keys or a composite key on last name and first name.

Add or change a primary key in transacts sequel by creating a table with a primary key or altering the table, naming the constraint and choosing clustered or non-clustered options.

Explore what schemas are in SQL Server and Transact-SQL, and how they serve as containers and namespaces for objects, with a default dbo schema and cross-schema references.

Explore how to work with schemas in Transact-SQL, including using default and explicit schemas, creating and transferring objects, and performing fully qualified name queries in Adventure Works.

Explore how synonyms provide an alternate, shorter name for a database object within schemas, creating a layer of abstraction that keeps client code working when the base object changes.

Learn how synonyms in Transact-SQL let you alias a table, such as ddh for human resources.employee department history, enabling shorter queries and easy updates when the base object changes.

Discover how the select statement retrieves data from one or more tables, selects columns, and uses where, group by, having, and order by to shape results.

learn column aliasing in Transact-SQL to rename result set columns for audiences, using as or simple aliasing, multiword aliases require quotes, and remember where clauses use the underlying column names.

Learn how to guarantee query results order with the order by clause, specify ascending or descending, and sort by multiple columns to control data presentation.

Discover how to retrieve related data from multiple tables using the join keyword, define primary foreign key relationships, and fetch names and phone numbers across related tables.

Explore joining two tables in a single query, selecting three columns from both tables, using table aliasing, filtering with a where clause, and ordering by last name and first name.

Explore inner, outer (left, right, full), and cross joins to combine two tables using on conditions, including primary key and foreign key relationships between student and address tables.

Demonstrate inner, left, right, and full joins with the student and address tables to match IDs and reveal second addresses, and explore cross joins for expanded results.

Refresh Intellisense in SQL Server Management Studio to clear red squiggles, ensure new tables are detected, and use the simple refresh steps via the menu or control shift r.

Explore aggregate functions in Transact-SQL, using sum, average, and count to turn many rows into a single value. See practical examples from sales order details to learn functions.

Group data with the group by clause to turn rows into a single row per group using aggregate functions. Grouping by product ID creates per-product summaries for reporting and metrics.

Explore how to use group by to compute average line totals by product, count orders, and apply having clauses, with practical examples in SQL.

Explore how the having clause filters groups after group by, using sum and average of line totals by product ID, with where limiting pre-group data.

Explore how to use where and having with grouped aggregates by product ID, calculating average line totals and sum totals, filtering groups, and ordering results for reporting.

Learn how SQL Server's select into creates a new table from a query result, copying column structure and identity properties but not indexes or constraints, for testing and reporting.

Explore multipart naming in Transact-SQL, using four-part and three-part naming to reference tables across servers, databases, and schemas, with defaulting rules and permissions considerations.

Explore the graphical query tool in sql server management studio to learn transact sql, build joins, and use the design query editor with the Adventure World 2012 database.

Learn how the insert statement adds rows to a table using into, values, and column lists; see optional into and positional data mapping.

Learn to insert multiple rows in one statement using into new data, ensuring each column aligns, with comma-separated rows like five rows, then verify with a select.

Use insert into with a select clause to move data into an existing table, and use select into to create a new table from a query.

Learn how to use the update statement to modify existing data, and the importance of a carefully crafted where clause, with a backup via select into, and avoid incorrect matches.

Apply the update statement safely by always including a where clause, validating target rows with a quick select, and consider transactions to protect data.

Use the delete statement to remove rows from a table, but always include a precise where clause to avoid deleting unintended data. Truncate offers an alternative.

Learn the select then delete technique to safely test a delete statement by running a preview via a select, verifying affected rows and the exact data, then applying delete.

Define delimiters in sql and use single quotes for literals, and brackets or double quotes for identifiers to handle spaces and complex names.

Master concatenation in Transact-SQL by using the plus sign to join text strings, insert spaces, alias the result as employee name, and format outputs for readability.

Master commenting in Transact-SQL by using single-line dashes and multi-line /* */ blocks, and place notes above code to guide future maintenance and troubleshooting, as in C#.

Declare T-SQL variables with the declare statement, prefixing names by @ and optionally initializing values. Explain scope as local to the batch and note global variables are actually system functions.

Learn how to set and select to assign values to variables in SQL, declare integers, cast for concatenation, and print results using Adventure Works 2012 data.

Discover how expressions and operators in SQL Server evaluate to a single data value. Learn about arithmetic, assignment, logical, and comparison operators, including modulo, in, between, exists, and like.

Learn how batches group one or more Transact-SQL statements, how the go command marks batch boundaries in the management studio, and how variable scope affects batching.

Explore using the execute command to run dynamic sql, manage variables in where clauses and table names, and build dynamic queries with string concatenation.

Master dynamic sql with the execute command, using print to verify delimiters and the command sent to SQL Server, and learn to pass a table name as a variable.

Explore if statements and begin and end blocks to conditionally execute code, using boolean expressions and else clauses, with a practical example of computing in_stock and applying a discount.

Explain how the go to statement redirects execution to labeled code in SQL Server, and caution against its use for readability and maintenance while noting rare necessary cases.

learn how to implement try catch blocks for protected code in SQL Server, enabling structured error handling, catching divide by zero errors, and logging errors without breaking the program.

Learn how the while loop repeats code while a condition holds, using break and continue to control execution. See an example that prints and loops through update and delete statements.

Learn how to use case statements to categorize list prices into price ranges for reporting, using when-then-else logic on the Adventure Works data.

Create and use views as virtual tables defined by a query, so end users can access only the data you expose. This provides a security abstraction and backward compatibility.

Stored procedures bundle Transact-SQL statements into named database objects; call them to execute consistent logic across applications, centralizing code on the SQL Server with permission control.

Create a stored procedure from a product sales query, run it, and learn how input parameters enable handling different product IDs without multiple procedures.

Define and use input parameters in a stored procedure, pass product IDs as integers, set default values, and alter procedures to run with or without explicit inputs.

Explore user-defined functions in Transact-SQL, including scalar and table-valued types, their use in queries, input parameters, and how they differ from stored procedures.

Create a scalar user-defined function named tax to compute tax from a numeric input, returning a decimal, and apply it in query against sales order detail in Adventure Works 2012.