Practical Transact-SQL (T-SQL): Get your hands dirty

Learn advanced SQL Server database development and querying with hands-on, interactive, and Drag and Drop exercises

3.90 (25 reviews)
Udemy
platform
English
language
Other
category
106
students
19 hours
content
Sep 2021
last update
$19.99
regular price

What you will learn

This course will teach you everything you need to know to master advanced SQL Server database development and querying features of this popular DBMS:

You will master the concepts and terms you need for working with relational databases and SQL

You learn how to create a database using SQL statements and its tables using SQL statements or the Management Studio.

You learn how to use the Management Studio to work with databases and database objects

How to work with views, scripts, stored procedures, functions, table-value parameters, triggers, transactions, locking, and security

You learn to write SQL statements that retrieve and update the data in a database

Master the details of inner and outer joins, summary queries, and subqueries...it's all here

Description

NOTE: SQL Server is a hands-on product. No matter how much videos you watch, to master SQL Server, you need to get your hands dirty. Our course is not for the casual SQL Server database user who just wants to know how to write simple SELECT queries to extract data for ad-hoc reports. This course is for someone who is keen to master SQL Server databases for a career, or for whom SQL Server is an integral part of day to day activities. This course will take you longer to complete than most SQL courses. The course is based on three separate databases:

(1) the Accounts Payable (AP) database that is the main database we use for the coding examples in the lectures,

(2) the AcotexCollege database that is used for the over 90 exercises (spread over the lectures) that you perform after each lecture in SQL Server   Management Studio (SSMS), and

(3) The Hands-On database that is used for the interactive exercises that you can work through in either your SSMS or right within a custom program that we have provide for you to write and execute all your SQL scripts outside of SQL Server

In addition to the many hands on scripting exercises, we provide over 390 quizzes that cover all the topics we discuss in the course

YOU WILL NOT BENEFIT MUCH FROM THIS COURSE IF YOU DO NOT DO THE QUIZZES AND THE EXTENSIVE EXERCISES WE HAVE PROVIDED FOR EACH SECTION OF THE COURSE

======================================================================================================================

This course will teach you everything you need to know to master advanced SQL Server database development and querying features of this popular DBMS:

  1. Master the concepts and terms you need for working with relational databases and SQL

  2. Learn how to create a database using SQL statements and its tables using SQL statements or the Management Studio...valuable skill whether you are on the DBA track or not

  3. How to use the Management Studio to work with databases

  4. How to work with views, scripts, stored procedures, functions, table-value parameters, triggers, transactions, locking, and security

  5. How to write SQL statements that retrieve and update the data in a database

  6. How to work with inner and outer joins, summary queries, and subqueries...it's all here


    COURSE STRUCTURE:

  • The course is video-based. We utilize PowerPoint presentation to explain concepts and use SQL Server Management Studio (SSMS) to provide coding examples and exercises. We expect you to follow the codes we explain to you in your own SSMS. The course is based on the Accounts Payable (AP) database

1. VIDEO:

  • Concepts are explained in a videos utilizing PowerPoint and SQL Server Management Studio (SSMS)

2. QUIZZES:

  • Each lesson comes with about 25 multiple choice quizzes. We suggest that you immediately repeat the lesson if you do not score at least 22 points before you move to the next lesson. Total of 390 quizzes for the course

3. HANDS-ON EXERCISES:

We provide two separate modes of hands-on exercises:

(1) Exercise mode one: Each lesson comes with an average of 6 hands-on exercises (total of 90 for the course). Exercises are in a word document. We expect you to download and print and work through the exercises in your SQL Server Management Studio. Solutions for these exercises are provided in sql scripts that you can download. The solutions are there as a last resort to help you perform the exercise only when you have tried but could not write the code

(2) Exercise mode two: We also provide an interactive, hands-on exercises (75 altogether) including Drag & Drop and sql scripting that you can perform in either SSMS or within a custom user-interface we have provided. If you opt to use the second method, you can write and execute all your sql scripts within the custom interface we have provided without ever opening your SQL Server instance. This is an innovative and convenient way to show how learning SQL Server querying can be fun. We provide buttons and other means to help you move through the exercises very easily. You can have instant solution (e.g. sql script) to an exercise at the click of a button.

4. SOLUTIONS TO EXERCISES

We provide SQL scripts (*.sql) that contain solutions for the first set of exercises. You can open the files in SSMS and view the solutions. The second set of exercises can be performed within the user interface program we provide (Excel VBA program). The exercises are displayed within ActiveX controls (textboxes) and you can code and execute you scripts (solutions) within the program. Navigational buttons are provided to help you move back and forth within the exercises. Solutions to exercises can be view by clicking a button

GOOD LUCK

Content

How to create and maintain databases, tables, and sequences with SQL statements

An introduction to DDL: The SQL statement for data definition.
Rules for coding object names
How to create databases, tables, and indexes using DDL
How to create a table using DDL
How to use snippets to create database objects
How to use constraints
How to use check constraint
How to use foreign key constraints
How to change databases and tables
How to alter a table
How to work with sequences
How to create a database and its tables with SQL statements
Creating and maintaining databases and database objects with SQL: hands-on

How to use the Management Studio for database design

Use Management Studio to work with a database
How to work with tables in Management Studio
How to work with foreign key relationships using SSMS interface
How to work with indexes and keys using SSMS interface
How to work with check constraint using SSMS interface
How to examine table dependencies using SSMS
How to generate scripts for database and tables within SSMS
How to use the Management Studio for database design
Database design with the Management Studio

How to work with Views

An introduction to views
Benefit of using views
How to create and manage views
Examples that create views
How to create an updatable view
How to delete or modify a view
How to use the catalog view
How to use the View Designer
How to work with views
How to work with views: Hands-on exercises

How to code scripts

An introduction to scripts: How to work with scripts
The Transact-SQL statements for script processing
How to work with variables and temporary tables: Scalar variables
How to work with variables and temporary tables: Table variables
How to work with temporary tables
A comparison of the five types of Transact-SQL table objects
How to control the execution of a script
How to test the existence of a database object
How to perform repetitive processing
Handling Errors in a script
Using the surround-with snippets
Advanced scripting techniques
How to create dynamic SQL scripts
How to code scripts
How to code scripts: hands-on exercises

How to code Stored Procedures, Functions, and Triggers

How to code stored procedures: An introduction to stored procedures
How to create a stored procedure
How to declare and work with parameters
Calling stored procedures with parameters
How to work with return values from a stored procedure
Validating data and raising errors within stored procedure scripts
A stored procedure that manages insert operations
Passing a table as a parameter to a stored procedure
How to delete or modify a stored procedure
How to work with system stored procedures
How to code user-defined functions
How to create a scalar-valued function
Creating a simple table-valued function
Creating a multi-statement table-valued function
How to code triggers
How to use AFTER triggers
How to code INSTEAD OF triggers
Using triggers to enforce data consistency
How to delete or modify the definition of a trigger
How to code stored procedures, functions, and triggers
Stored procedures: hands-on exercises

How to manage transactions and locking

How transactions maintain data integrity
SQL statements for handling transactions
How to work with nested transactions
Working with transaction save points
An introduction to concurrency and locking
How to set the transaction isolation level
How SQL Server manages locking
Lock modes and lock promotion
How to prevent deadlocks
Coding techniques that prevents deadlocks
Quizzes on how to manage transactions and locking
Hands-on exercise: Transaction and Locking

How to manage database security

How transactions maintain data integrity
How to change the authentication mode
How to create login IDs
How to delete or change login IDs or password
How to work with database users
How to work with schemas
How to grant or revoke object permissions
Hand-on exercise
How to grant or revoke schema permissions
Granting or revoking database permissions
How to grant and revoke server permissions
How to work with fixed server roles
Working with user-defined server roles
How to display information about server roles and role members
How to work with the fixed database roles
How to work with user-defined database roles
How to display information about database roles and role members
How to deny permissions granted by a role member
How to work with application roles
How to manage security using the Management Studio
How to work with the server roles for a login ID
How to assign database access and roles by login ID
Assigning user permissions to database objects
How to work with database permissions
Quizzes on how to manage transactions and locking
Hands-on Exercises on database security

Screenshots

Practical Transact-SQL (T-SQL):  Get your hands dirty - Screenshot_01Practical Transact-SQL (T-SQL):  Get your hands dirty - Screenshot_02Practical Transact-SQL (T-SQL):  Get your hands dirty - Screenshot_03Practical Transact-SQL (T-SQL):  Get your hands dirty - Screenshot_04

Reviews

Marja
July 24, 2022
Een hele gedegen cursus met veel voorbeelden en oefeningen. De uitspraak is soms onduidelijk en de ondertiteling is niet goed maar het is wel goed te volgen met een beetje goede wil. Ik heb er veel van geleerd en verder komt het aan op oefenen.

Charts

Price

Practical Transact-SQL (T-SQL):  Get your hands dirty - Price chart

Rating

Practical Transact-SQL (T-SQL):  Get your hands dirty - Ratings chart

Enrollment distribution

Practical Transact-SQL (T-SQL):  Get your hands dirty - Distribution chart

Related Topics

3754496
udemy ID
1/6/2021
course created date
1/21/2021
course indexed date
Bot
course submited by