SQL- Topics For Mainframe and DWH Professionals

Source: It career coach

 SQL QUERY DEVELOPMENT

MODULE 1: THE SELECT STATEMENT

  1. What is the SELECT statement?
  2. How to eliminate duplicate rows in SELECT statements
  3. How to sort or order results in SELECT statements
  4. FROM clause
  5. WHERE clause
  6. GROUP BY clause
  7. HAVING clause
  8. WHERE clause
  9. NULL, NOT NULL
  10. AND, OR NOT
  11. TOP clause
  12. Identity / Auto-numbering fields

MODULE 2: COMPLEX QUERIES

  1. What is a table join?
  2. Inner Join, Left Outer Join, Right Outer Join, Cross Join, Self Join
  3. UNION, UNION ALL
  4. Aggregate Functions
  5. Using the COUNT Function
  6. Exploring the SUM, AVG Functions
  7. Using the MIN, MAX Functions
  8. INSERT, UPDATE, DELETE, TRUNCATE queries
  9. Working with Subqueries
  10. Using EXISTS, NOT EXISTS , ANY in Subqueries
  11. Using Correlated Subqueries, Nested Subqueries
  12. Working with more T-SQL Functions:
  13. Using IsNumeric , ROUND, LEFT , RIGHT SUBSTRING, LEN Functions
  14. Using REPLACE, STUFF, LOWER, UPPER, the LTRIM, RTRIM Functions
  15. Using the LTRIM, RTRIM, GETDATE , MONTH, DAY, YEAR Functions
  16. Using the DATEPART, DATENAME, DATEADD, DATEDIFF Functions
  17. Using the ISNULL, COALESCE Functions

COURSE 3: DATABASE DEVELOPMENT

MODULE 1: CREATING THE DATABASE

  1. Creating / dropping a database
  2. Creating / dropping a table
  3. Creating / dropping columns
  4. Creating /dropping Primary keys & Foreign keys
  5. Creating / dropping Indexes
  6. Unique Indexes
  7. Non-unique indexes
  8. Creating / dropping Constraints
  9. Unique constraints
  10. Check constraints
  11. Implementing Relationships
  12. Creating / deleting relationships
  13. Implementing Delete Rules, Update Rules

MODULE 2: WORKING WITH VIEWS

  1. Implementing Views
  2. Creating / dropping a view
  3. Using Views to Secure Data
  4. Modifying Data in a View
  5. Modifying Views
  6. Renaming Views
  7. Deleting Views
  8. Implementing Indexed Views
  9. Implementing Clustered / Non-Clustered Views

MODULE 3: WORKING WITH FUNCTIONS & TRIGGERS

  1. What is a Function?
  2. Building and Working with User-Defined Functions
  3. Scalar, Inline Table-Valued, Multi-Statement Table-Valued Functions
  4. What is a Trigger?
  5. Creating / deleting INSTEAD OF Triggers
  6. When to use Triggers?

COURSE 4: DATABASE DEVELOPMENT

MODULE 1: STORED PROCEDURES

  1. Designing a Stored Procedure in the Query Editor
  2. Using T-SQL to Create a Stored Procedure
  3. Declaring and Working with Variables
  4. Input Parameters
  5. Output Parameters
  6. Controlling the Flow of Execution
  7. Using IF … ELSE
  8. Working with BEGIN … END
  9. Exploring GOTO, RETURN, and Labels
  10. Working with the CASE Statement
  11. Exploring the WHILE Statement
  12. The SET NOCOUNT Statement
  13. Using the @@RowCount System Variable
  14. Using the @@TranCount System Variable
  15. Using the @@Identity System Variable
  16. Using the SCOPE_IDENTITY()
  17. Using the @@Error System Variable
  18. Handling Runtime Errors
  19. Returning Success and Failure Information from a Stored Procedure
  20. What is a Transaction?
  21. Types of Transactions
  22. Implementing Transactions
  23. Stored Procedures and Temporary Tables
  24. Stored Procedures and Security

MODULE 2: SQL QUERY / STORED PROCEDURE OPTIMIZATION

  1. Analyzing SQL Execution Plans
  2. Analyzing Database Statistics
  3. Index Analysis and Tuning
  4. Stored Procedure Recompilation
  5. Handling Blocking and Deadlocks
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.