Queries, Cursors, Views and Database Implementation
This module discusses queries, cursors and views and SQL Server Database Implementation.
MS SQL Server 2016: Queries, Cursors and Views is the second course in the SQL Server 2016 Database Implementation Certification Series.
This series is designed to:
- Provide the technical skills required to implement a database solution on Microsoft’s SQL Server 2016
- Help you prepare for the Microsoft’s 70-029 Exam, Designing and Implementing Databases with Microsoft SQL Server 2016
SQL Server is a relational database management system (RDBMS) developed by Microsoft. It uses the SQL (Structured Query Language) for interacting with the data stored in the system. This involves manipulation, retrieval, and management of the data. In this context, queries, cursors, and views play an integral role in the system's operation, each serving its own unique purpose.
- Queries: Queries are essentially the primary means of interacting with the SQL Server database. They allow users to perform actions such as retrieving data (SELECT), inserting new data (INSERT), updating existing data (UPDATE), and deleting data (DELETE). Beyond these basic operations, SQL queries also provide more complex functionalities such as joining tables, filtering data, grouping and aggregating data, and sorting results. The power of SQL Server, like other RDBMSs, comes from its ability to efficiently execute complex queries over large datasets.
- Cursors: A cursor in SQL Server is a database object that is used to manipulate rows in a result set one at a time. Unlike a query which operates on the whole result set at once, a cursor allows fine-grained control, enabling users to fetch, update, and delete rows individually. Cursors are typically used when complex logic is required that cannot be accomplished with a single query, or when row-by-row operations are necessary. However, due to their overhead, cursors should be used judiciously and often are a last resort.
- Views: Views are virtual tables in SQL Server that are defined by a query. They allow users to save a particular query as a named view, and then refer to that view in other queries just as if it were a table. This can simplify complex queries, enforce consistency across queries, and provide a layer of abstraction between the data and the user. In addition, views can be used to implement security policies by controlling which columns or rows are visible to different users. There are two types of views in SQL Server: standard views, which are essentially saved queries, and indexed views, which physically store the result set of the query for faster access.
In summary, queries, cursors, and views are vital tools that provide SQL Server users with the ability to interact and manipulate data stored within the database. They each cater to different requirements and scenarios and understanding their functionalities and usage is key to effective database management and operations.
After completing the course, you will be able to:
SELECT statements that retrieve and modify data
- Write queries that retrieve and modify data using joins and sub-queries
- Write queries that summarize data
- Create views of data
- Navigate through your table with cursors
How you will learn
As you probably know, you can interact with SQL Server in two ways:
- by issuing Transact-SQL commands, and
- by interacting with its GUI, or Graphical User Interface.
This course uses SlideShows and Mouseovers to explain the code syntax used in Transact-SQL commands, and simulations for practicing different elements of the SQL Server GUI.
Microsoft Exam Series
Queries, Cursors and Views is the second of three courses in the
SQL Server 2016 Database Implementation Certification Series
In the next lesson, the prerequisites for the course will be discussed.