Advanced SQL Scripting

Course details

• This course is a follow-up course on our highly popular Understanding SQL Scripting course.
•  In the Advanced SQL(Structured Query Language) course, you will create advanced SQL queries to manipulate and index tables. 
• You will work with advanced queries to create and work with subqueries, creating and manipulating view and index tables. 
• You will learn how to do Table Design, creating tables with Constraints and Backing Up Tables.
• You will also create and committ transactions so that you can choose to save or cancel data entry process and learn rollback transactions.
• You will also learn Database Security in creating & managing users, roles and privileges. 
• Granting permissions to roles and modifying privileges.

Prerequisite:

• Follow up course on Understanding Sql Scripting course.
• Fundamental SQL and Relational Database knowledge required.

Certificate Of Attendance:

• Certificate Of Attendance will be awarded to students completing the course.

Training Methodology & Materials:

• Practical case study sessions to enhance concept.
• Well-designed lab sessions to further enhance the understanding of the courseware given.

Course Outline:

1. Advanced SQL Querying Using Subqueries

• Search Based on an Unknown Value
• Search Based on Multiple Unknown Values
• Compare a Value with Unknown
• Search Based on the Existence of Records
• Generate Output Using Correlated Subqueries
• Filter Grouped Data Within Subqueries
• Perform Multiple-level Subqueries
• Generating Output Using Nested Subqueries

2. Advanced Data Manipulation Statement

• The Output Clause Statement
• The Truncate Clause Statement
• The Merge Statement

3. Manipulating Table Structure

• Table Design
• Create a Table with Constraints
• Primary Key/Primary Key Constraints
• Foreigh Key/Foreign Key Constraints
• Default Constraints/Checking Constraints
• Add or Drop Table Columns
• Add or Drop Constraints
• Modify the Table Structures / Column Structures
• Backup Up Tables

4. Working with Views

• Create a View
• Create View Statement /Schema Binding /Top Keyword.
• Create View with Schema Binding
• Manipulating Data in View
• Creating Aliases
• Modify and Drop Views

5. Indexing Data

• Creating Indices
• The Create Index Statement
• Drop Indices
• The Drop Indices Statement

6.Managing Transactions

• Create Transactions
• The Rollback Tran Statement
• How to Create Transactions
• Creating Transactions
• Committing Transactions

7.Database Security - Roles & Privileges

• DCL Commands
• The SQL Grant Command
• The SQL Revoke Command
• System and Object Privileges
• Create, Modifying and Droping Roles
• Create, Modifying and Deleting Privileges