SQL & PL/SQL Development Training

SQL Training in vizag

This course is designed for everyone who want to learn the basics and advanced level of SQL programming. Students will learn up to advanced level of SQL and gain hands-on experience creating simple database applications.

Programme Overview :

SQL is a primary and set of commands which are used to work in the relational database management databases. With SQL, we can query the data that we need and do so efficiently, playing into the strengths of the database engine.

We will be able to rapidly work on any relational databases by understanding SQL. SQL is worth learning because it’s not only simple to learn, but it also continues to be a highly in-demand skill.

Most of the professionals who wish to work as Data Analyst, Data Engineer, Data Scientist, Database Developer, ETL Developer (BI Developer), Report Developer, Test Engineer, Performance Engineer and/or Business Analyst, they should learn and get some extent of experience in SQL to continue day to day work.

Make most of the ample job opportunities in this field by learning hands-on and functional SQL, PL/SQL, T-SQL and client tools and techniques.

SQL Training in vizag

Programme Highlights

• Hands On Experience with Industry Top Two RDBMS Databases and Tools
• MS SQL SERVER
• ORACLE

World-class learning

• Learn from the best instructors and resources from anywhere in the world via our Online training programs.
• Our trainers are available 24*7 regardless of their location.
• Gain hands on industry and functional knowledge.

SQL Training in vizag

Programme Modules

Module 1 :Introduction of SQL, RDBS,Software’s and Tools

• History of SQL
• What is the Transact SQL and PL/SQL?
• Understand the difference in MS SQL Server database and Oracle database!
• SQL Server Versions and Editions
• SQL Server Management Studio Tool
• SQL Developer Tool
• DBeaver Tool
• DB Visualizer Tool
• Toad Tool

Module 2: DB Objects and SQL Commands

• Different Types of Objects in RDBMS databases
• What is the purpose of Table object?
• Understand Different terminologies like Columns, Rows, Data & Attributes
• SQL Commands
• Different data types in SQL
• DDL – Data Definition Language (CREATE, ALTER & DROP)
• DML – Data Manipulation Language (INSERT, UPDATE & DELETE)
• TCL – Transaction Control Language (BEGIN TRAN, COMMIT, ROLLBACK & SAVEPOINT)
• DCL – Data Control Language (GRANT & REVOKE)
• DRL – Data Retrieval Language (SELECT)

Module 3: Different Types of Operators

• Different Types of Operators in RDBMS databases
• Understand the query logical processing in SQL
• Comparison Operators (<, >, <=, >=, =, !=, <>)
• Logical Operators (NOT, AND, OR)
• Wildcard Search Operators (%, _, ^, [] )
• Combine Data Set Operators (UNION ALL, UNION, INTERSECT, EXCEPT & MINUS)

Module 4: Normalization Forms, Keys and Relationships

• Different Types of Normal Forms
• Normal Form 1 Rules
• Normal Form 2 Rules
• Normal Form 3 Rules
• Understand the concept of Key in a Dataset
• Different types of Keys
• Super Keys, Candidate Keys & Primary Key in a Table
• Understand the Parent and Child table’s
• Different types of Relationships
• One to One Relationship
• One to Many Relationship
• Many to Many Relationship

Module 5: Different Types of Constraints

• Different types of constraints
• Understand business rules in a dataset
• NULL & NOT NULL constraints
• Default constraints
• Check constraints
• Primary key constraints
• Foreign key constraints
• Unique key constraints

Module 6: Joins

• Different types of joins
• Rules and recommendations to join datasets
• Inner join
• Outer Joins – Left Join / Left Outer Join
• Outer Joins – Right Join / Right Outer Join
• Outer Joins – Full Join / Full Outer Join
• Cross join & Cartesian product
• Self join
• Equi join
• Update & delete the data from a table by using joins

SQL Training in vizag

Module 7: Built-in Functions

• Different types of functions
• Variables in SQL

• String functions:
• Sub string function, character index function, length function, left function, right function, trim function, upper & lower functions, replace & stuff functions, and reverse function, concat function, convert function…etc

• Date time functions:
• Getdate function, dateadd function, datepart function, month/year/day functions, datediff function…etc
• Display the date with different formats by using convert function

• NULL functions:
• Isnull function, coalesce function, nullif function, and iif function

• Aggregate functions:
• Sum function, avg function, min function, max function, count function
• Understand the Group by clause

• Windowing functions:
• Row number function, Rank function & Dense Rank function

Module 8: Sub Queries

• Understand the main purpose of Sub Query

• Different types of outputs return by a sub query
• Scalar output, Multi values output & Table valued output
• Dynamic value in WHERE claus

• Different types of sub queries
• Non correlated sub query and correlated sub query
• Derived Table (Sub query in FROM clause)
• Column expression (Sub query in SELECT class)
• Usage of EXIST & NOT EXIST keywords
• CTEs

Module 9: Ordering data sets, avoid duplicate data, fetch sample data

• OORDER BY clause
• DISTINCT keyword
• FETCH keyword
• TOP keyword
• OFFSET keyword

Module 10: Different types of tables and columns

• Local temporary tables
• Global temporary tables
• Table variables
• Identity columns
• Sequence objects

Module 11: Control flow statements

• BEGIN…END block
• IF…ELSE block
• GOTO label
• WAITFOR command
• CASE in SELECT & WHERE clauses
• WHILE Loops
• CURSORs
• TRY & CATH blocks
• THROW
• RETURN

Module 12: Merge and Triggers

• Data synchronization by using MERGE statemet
• Understand the purpose of Triggers in SQL
• INSERTED & DELETED table’s
• Triggers for INSERT, UPDATE & DELTE operations
• Data synchronization by using Triggers
• Maintain history & audit data by using Triggers
• Control the DML operations against tables by suing Triggers
• INSTEAD OF triggers

Module 13: Views and Functions

• Implement the views for different requirements
• Different types of functions
• Scalar & Table Values functions
• Implement functions with input parameters

Module 14: Transactions with Error/Exception Handling

• Characteristics of a transaction
• Atomicity, consistency, isolation, durability
• Understand the error handling and store error message in error logs
• Use TRY & CATCH blocks
• Commit and rollback the transactions.
• Behavior of a transaction

Module 15: Implement Stored Procedures

• Dynamic SQL statements
• Understand the usage of stored procedures
• Stored procedure with IN and OUT parameters
• Stored procedures with default parameter values
• Include error handling mechanism inside stored procedure
• Stored procedures to process new data to the database
• Stored procedures to process updates across multiple tables
• Implement stored procedures to archive the data from tables
• Implement stored procedures to pure the data from tables
• Implement stored procedures to process the business rules

Module 16: Performance Tuning

• Locks
• Deadlocks
• Heap and Cluster tables
• Indexes – Clustered and NON-Clustered
• Statistics
• Execution plans and Query optimizer
• Compress the tables
• Add missing indexes to the tables
• Logical partition of tables
• Rebuild and Reorganize the indexes
• Add the necessary indexes based on the SQL queries

Module 17: Export and Import the data

• Export data from database to Excel, CSV, Text files, and other databases
• Import the data from Excel, CSV, Text files to databases

Module 18: Security in SQL

• Grant and Revoke the access to users
• Different access roles in a database
• Encrypt and Decrypt the data in a table by using Passwords

Module 19: Automation in SQL

• Create and modify the jobs
• Schedule the jobs to run as per the requirements

Module 20: Database backups and Restore

• Database backup
• Database restore from the backup