New user? Register as member

FUNDAMENTALS OF MICROSOFT SQL SERVER 2019

Master the fundamentals of SQL Server 2019, the industry-leading database management platform from Microsoft. In this course, beginners will learn the core skills they need to create and manage SQL Server databases.

You don't need any prior knowledge of databases or SQL query language.

Objective

The course begins with the basics, showing how to install SQL Server and log into a local database server. Then, you'll learn how to create new databases, design tables, define relationships, write queries, and use stored procedures and built-in functions. Plus, you will practise how to perform crucial database administration tasks, such as backups and restores, security enhancements, and performance monitoring.

Outline

1. Get Started

• Core concepts
• SQL Server 2019 editions
• Install SQL Server Developer Edition
• Install Management Studio
• Enable features with Configuration Manager

2. Log into the Server

• Management Studio first steps
• Authentication and user permissions
• Create a new user account

3. Create Databases

• Create the first database
• Database configuration options
• Table structures
• Create a simple table
• Edit a table's structure
• Enter data into a table using the GUI
• Import data from CSV files
• Create tables from flat files
• Install a sample database

4. Table Design for Healthy Databases

• Understand data types
• Table column properties
• Primary key fields
• Establish a default value
• Check constraints
• Unique constraints
• Foreign keys
• Create a foreign key column
• Create relationships between tables

5. Structured Query Language

• Use the T-SQL editor
• Create a table with T-SQL
• Add data to a table
• Retrieve records with SELECT
• Filter returned results with WHERE
• Sort records
• Delete records from a table
• Update records in a table
• Join related tables
• LEFT JOIN and RIGHT JOIN
• Remove a table from the database

6. Query Performance

• Create a view of the data
• Data table indexes
• Create additional indexes on a table
• Columnstore indexes
• View execution plans
• SQL Server's Query Store
• Rebuild indexes

7. Query Programmability

• Aggregate functions
• Use built-in functions
• Leverage user-defined scalar functions
• Introduction to stored procedures
• Create stored procedures
• Parameterised stored procedures

8. Backup and Restore

• Create a full backup of the database
• Create a differential backup
• Restore a backup
• Automate backups
• Business continuity solutions

9. Security

• Database schemas
• Dynamic data masking
• Always On data encryption

10. Monitoring and Management

• Explore the role of system databases
• Prevent excessive tempdb autogrowth
• Review the SQL Server error log
• Dynamic management views
• Database Console Commands (DBCC)

Who should attend

1. If you are aspiring to learn SQL Server (Microsoft's version of database)
2. You want to work with SQL databases
3. Thinking about your own data storage needs

Methodology

This is a two days hands-on course. The trainer will explain how to make a database and maintain it using step-by-step practice. You will also be taught tips & tricks for good database management. You will design your own database and also modify existing databases. You will get ample practise so that you can apply them in practical situations in database management.

Profile of Harjinthar Singh

Dr. Harjinthar Singh attained his PhD in Electrical, Electronics and Communications Engineering in 2012 from London South Bank University. He started his career as a Product Planner with SONY from August 1995 to December 2002. He worked as a Software Engineering Lecturer with London South Bank University from January 2003 to December 2011 and a freelance IT Trainer with LinuxCraft UK Ltd from January 2002 to March 2010. He worked as a Senior Staff Researcher with MIMOS Berhad, Malaysia from June 2012 to July 2016, Computer Science Senior Lecturer from December 2016 to November 2017 with IUMW. He is now a Principal Trainer with a Singapore IT training company since November 2017.

He has a total of 26 years of experience working in the industry, lecturing and training field. He is also a HRDC TTT-certified Trainer & AITD International Member.