MS SQL

MS Sql Server DBA

MS SQL

MS SQL

SQL Overview

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet)


• Outlining SQL as the cornerstone of database activity
• Applying the ANSI/ISO standards
• Describing the fundamental building blocks: tables, columns, primary keys and foreign keys
Building the Database Schema
• Creating tables and columns
• Building tables with CREATE TABLE
• Modifying table structure with ALTER TABLE
• Adding columns to an existing table
• Removing tables with DROP TABLE
Protecting data integrity with constraints
• Guaranteeing uniqueness with primary key constraints
• Enforcing integrity with foreign key constraints
• Imposing business rules with check constraints
• Enabling and disabling constraints
• Removing constraints with ALTER TABLE
Improving performance with indexes
• Expediting data retrieval with indexes
• Recommending guidelines for index creation
Manipulating Data
• Modifying table contents
• Adding table rows with INSERT
• Changing row content with UPDATE
• Removing rows with DELETE
Applying transactions
• Atomic Consistent Isolated Durable (ACID) rules
• Controlling transactions with COMMIT and ROLLBACK
Writing Single Table Queries
• Retrieving data with SELECT
• Restricting rows with the WHERE filter
• Sorting the result with ORDER BY
• Handling NULL values in expressions
• Avoiding NULL value pitfalls in filter conditions
Querying Multiple Tables
• Applying the ANSI/ISO standard join syntax
• Matching related rows with INNER JOIN
• Including nonmatched rows with OUTER JOIN
• Creating a Cartesian product with CROSS JOIN
Combining results with set operators
• Stacking results with UNION
• Identifying matching rows with INTERSECT
• Utilizing EXCEPT to find nonmatching rows
Employing Functions in Data Retrieval
• Processing data with row functions
• Conditional formatting with the CASE expression
• Utilizing the CASE expression to simulate IF tests
• Dealing with NULL values
Performing analysis with aggregate functions
• Summarizing data using SUM, AVG and COUNT
• Finding the highest/lowest values with MAX and MIN
• Defining the summary level with GROUP BY
• Applying filter conditions with HAVING
Constructing Nested Queries
• Applying subqueries in filter conditions
• Correlated vs. noncorrelated subqueries
• Testing the existence of rows
Including subqueries in expressions
• Placing subqueries in the column list
• Creating complex expressions containing subqueries
• Handling subqueries that return no rows
Developing In-Line and Stored Views
• Breaking down complex problems
• Selecting data from a query result set
• Subqueries in the FROM clause
• Creating views in a database
• Building reusable code
• Updateable vs. non-updateable views


Back to Top
Content