Overview

In today's competitive environment, information is one of the most important factors in determining the success of an organization. If you are able to manage and retrieve information efficiently, you can streamline the organization's processes and give it a competitive edge. Some data analysis tasks may require that you query multiple tables simultaneously or create complex views of data. In the course SQL Querying: Fundamentals, you learned the basics of SQL querying. In this course, you will build on this foundation, learning advanced querying techniques, such as writing nested queries and using views to simplify repetitive querying tasks. You will also learn how to perform common database administration tasks such as duplicating tables; modifying table structures; inserting, updating, or deleting data; and indexing tables to optimize query performance. You will also learn how to work with multiple tasks as a transaction. This course is intended for individuals who have basic SQL skills and who want to expand their skillset with more advanced tasks. In this course, you will: use nested queries to generate query output; manipulate table data by inserting, modifying, and deleting data; manipulate the structure of tables; work with views to present data; index data to improve querying efficiency; and manage database transactions. This course requires that you have Microsoft SQL Server Express and Microsoft SQL Server Management Studio (SSMS) installed on a Windows PC. The course setup instructions provided in the first module of the course go into more detail about the hardware and software requirements.

Syllabus

Using Nested Queries

You have performed some fairly complex queries using a basic SELECT statement. However, SQL provides advanced capabilities that enable you to take your queries further. In this course, you will use these advanced capabilities to build on the fundamentals of SQL querying. You will start by performing various types of nested queries.

Manipulating Table Data

Although database client applications typically provide features within the user interface to manage content, in many cases you may need to set up database structure and content before you deploy an application. Or you might need to perform maintenance tasks outside the client application. Using SQL, you can directly perform such tasks. In this lesson, you will use SQL to insert, update, and delete rows.

Manipulating the Table Structure

Over time, you might need to change your database's structure. You can use SQL to add new tables and new columns, or change the types of data stored within a particular column.

Working with Views

Some database users may not know how to write SQL queries, or may not want to be bothered to write a query to view data coming from multiple tables. Or you may want to hide certain columns of data from the view of end users. Database views enable you to control what data users see, like a query, but they can be quickly invoked to present data in a particular way. In this lesson, you will create views and use them to manipulate data, modify views, and delete views.

Indexing Data

How Microsoft® SQL Server® retrieves the data that satisfies a query can have a huge impact on your database server's performance, especially if you have very large tables. One of the strategies you can use to improve the efficiency of SQL Server is to create indexes to support your most frequently executed queries.

Managing Transactions

Some database transactions must be accomplished through more than one SQL statement. If a problem occurs at any point in the transaction, you need to roll back each step of the entire transaction to get the database back to its original state. SQL provides features that enable you to accomplish this.