Advanced SQL

  • Learn about the structure, variables, and valued functions of a table.
  • Get an understanding of the concept of correlated subqueries in SQL.
  • Acquire skills in testing conditions, declaring variables, and looping.

Key Points About Advanced SQL

237 Learners Recommended

Duration: 2 Days*

Book Now

Advanced SQL

Course Overview

SQL is the structured query language which is a domain-specific language for storing, manipulating, and retrieving data in a relational database. SQL is used for the query, insertion, updating and modifying the data in a database system.

Advanced SQL developers are now substantially in demand by the IT industry. SQL allows users to retrieve, sort, filter, aggregate, and calculate data stored in a SQL server database. SQL is a universal language quickly adapted to another RDBMS such as MySQL, Oracle, and Microsoft Access.

This 2-days Advanced SQL training course improves the knowledge and understanding of fundamentals and advanced concepts of SQL. This course focuses on sharing new SQL techniques and subqueries that allow delegates to understand its core concepts. They will also learn how to create and manage the table, correlated subqueries, using exists, DCL, TCL, underlying transactions, variables, testing conditions, scaling emerging, and dynamic technological networks. This course makes SQL users confident in executing complicated commands when viewing, managing tables, and running complex queries. 

Course Outline

Table Structure

  • Objects under the Customer Orders Database
  • Creating a Table from the Object Explorer
  • The Customer Table with Customer Id as Primary Key
  • Click Refresh under Tables
  • The Supplier Table
  • The Orders Table
    • The Orders Table Foreign Key Relationships
  • The Product Table
  • The Order Details Table
    • The Orders Detail Table with Multi-Column Primary Key
    • The Order Details and Product Table Foreign Key Relationship
  • The TABLES in the CUSTOMER ORDER Database
    • DATA for CUSTOMER Table
    • Data for SUPPLIER Table
    • Data for Product Table
    • Data for ORDERS table
    • Data for Order Details

Subqueries

  • Understand Subqueries
  • Using ALL, ANY, and IN
  • Correlated Subqueries
  • Using EXISTS

DCL

  • What is DCL?
  • DCL Commands
    • GRANT Command
    • REVOKE Command
  • Privileges and Roles

TCL

  • Basic Transactions
    • Properties of Transactions
    • Transaction Control
    • The COMMIT Command
    • The ROLLBACK Command
    • The SAVEPOINT Command
    • The SET TRANSACTION Command
  • Rolling Back

Variables

  • Declaring Variables
  • SET versus SELECT
  • Tricks with Variables and Row sets
  • Global Variables

Testing Conditions

  • IF/ELSE Statement
  • Using CASE Where Possible

Looping

  • Syntax of WHILE
  • Objects
  • Dropping Objects

Rules

  • What are Rules
  • Creating Syntax Rules

Stored Procedure Basics

  • Creating Stored Procedures
  • Three Ways to Execute
    • Using Object Explorer
    • Using the query window
    • Calling from another Application
  • Stored Procedures with Parameters
  • Pros and Cons of Stored Procedures
  • System Stored Procedures

Parameters and Return Values

  • Default Values
  • Output Parameters
  • Using RETURN

Cursors

  • Understand Cursors
    • Types of Cursor
  • Syntax of Fetching Rows

Triggers

  • Understand the Trigger
  • Two Types of Triggers

Avoiding Scalar Functions

  • Scalar Functions
  • Disadvantages
  • Advantages

Temporary Tables and Table Variable

  • Using Temporary Tables
  • Creating Table Variables
  • Pros and Cons of Each Approach

Table-Valued Functions

  • In-line Table-Valued Functions
  • Multi-Statement Table-Valued Functions
  • Limitations of User-Defined Functions

Derived Tables and CTEs

  • Using Derived Tables
  • Common Table Expressions (CTEs)

Error-Handling

  • Using TRY/CATCH
  • System Error Functions
  • Custom Error Messages
  • The Obsolete @@Error Function

Debugging

  • The SQL Server Debugger
    • Stepping Through Transact-SQL Code
    • Viewing Debugger Information

There are no formal prerequisites, but the delegate should have prior knowledge of SQL and attended Introduction to SQL training course before taking this Advanced SQL course.

This course is designed for individuals who have work with SQL and would also like to enhance their skills and knowledge.

The Advanced SQL Cost in United Kingdom Starts from £1425.00

Please see our SQL Training courses available in United Kingdom

telephone

Get More Information

Still have questions? We’re happy to help.

calendar

Dates & Prices

Choose a Location

Choose a Month

Learn from the comfort of your home or office

Join discussions via computer or mobile device

Interact with subject matter experts

40 Hours (on average)

90 Days Access

Certificate upon completion

Schedule and location of your choice

Save on travel costs

Monitor employee development

Key points about this course

237 Learners Recommended

Duration: 2 Days*

Add additional features

6 Months Access - £ 387.00

1 Year Access - £ 776.00

Personal career coach and career services

Your online (Advanced SQL) package

Upon purchase you will receive a password via the email you used to purchase the course.

You will then be able to login to our online learning platform with your email and password.

You will have access to the platform for 90 days to complete your course.

Course Onsite Enquiry

Advanced SQL

Complete the steps below to receive a quote or more information

Your details

How many employees need training test?

When would you like to take the course test?

How many employees need training?

Prev

Discover how we are doing

Our goal is to always provide training of the highest quality for your organisation.

We aim for customer satisfaction across all areas, including price, quality, and learning support. We always use the feedback from clients to continually improve and ensure we surpass expectation. Have a look at our customer reviews that showcase the standard we offer.

97% price

Response Rate

99% price

Positive Rating

Steps For Success

Leading Path To Success

works