Enhance Your Data Analytics Career With Real-World Data Science Exercises

Course Duration & Payment Details

Days: Tuesday & Thursday
Timing: 07:00 to 08:30pm
Duration: 12 hours
Starting from: Tuesday, 19 January 2021
Last date of registration: Friday, 15 January 2021

Actual Fee: 18000/-  | Early bird discount: 9000/-
How to Pay: Online Payment
Bank: Habib Bank Limited (26 street, Badar Commercial, DHA Karachi)
Title: Irfan
Account No: 0011557900292501 | PK02HABB0011557900292501

What You’ll Learn:

If you have no technical background, don’t be afraid! We’ve distilled our knowledge and experience using SQL into a short course so that by the end, you’ll have the raw skills to do some real data analysis for your company using SQL – a language virtually EVERY company users note: this course teaches you real-world SQL – not just the theory in abstract, but real skills you can use to get more data-driven in your current job.

• Complete literal SELECT statements.
• Complete basic SELECT statements querying against a single table
• Filter data using the WHERE clause
• Sort data using the ORDER BY clause
• Learn about database design and normalization
• Using joins, return columns from multiple tables in the same query
• Understand the difference between the INNER JOIN, LEFT/RIGHT OUTER JOIN, and FULL OUTER JOIN
• Complete SQL statements that use aggregate functions
• Group data together using the GROUP BY clause
• Filter groups of data using the HAVING clause

How This Course is Structured:

• In this course we’ll be pretending we’re a real business (i.eBlockbuster) so the stuff you learn will be easy to apply to your own situation/company abstract/theoretical mumbo jumbo.
• We’ll go through queries a real business would run while also teaching you the raw skills undergirding those queries so you can adapt those skills to create custom queries for your own specific purposes
• To reinforce learning, we have exercises and quizzes scattered throughout the course so you can learn by doing
• We’ll have a bonus section where new lectures will be added occasionally (including student-requested lectures, more advanced topics, strategies for getting unstuck, etc.)
• We’ll be actively involved in the discussion board answering any questions you might have! Don’t be afraid to ask!

SQL Server – From the Basic

• Install SQL Server – Step by Step – Developer Edition
• Connecting to SQL Server using SSMS
• Creating altering and dropping a database
• Creating and working with tables
• Adding a default constraint
• Cascading referential integrity constraint
• Adding a check constraint
• Identity Column
• How to get the last generated identity column value
• Unique key constraint
• Select statement
• Group by
• Joins
• Advanced or intelligent joins
• Self-join
• Different ways to replace NULL
• Coalesce function
• Union and union all
• Stored procedures
• Stored procedures with output parameters
• Stored procedure output parameters or return values
• Advantages of stored procedures
• Built-in string functions
  • LEFT, RIGHT, CHARINDEX, and SUBSTRING functions
  • Replicate, Space, Patindex, Replace, and Stuff string functions
  • DateTime functions
  • IsDate, Day, Month, Year, and DateTime DateTime functions
  • Date, DateAdd, and DateDiff functions
  • Cast and Convert functions
  • Mathematical functions
  • Scalar user-defined functions
  • Inline table-valued functions
  • Multi statement table-valued functions
  • Important concepts related to functions
  • Choose function
  • IIF function
  • TRY PARSE function
  • TRY CONVERT function
  • EOMONTH function
  • DATEFROMS function
  • Difference between DateTime and SmallDateTime
  • DateTimeFroms function
  • Difference between DateTime and DateTime
  • Offset fetch next
• Temporary tables
• Indexes
• Clustered and non clustered indexes
• Unique and Non-Unique Indexes
• Advantages and disadvantages of indexes
• Views
• Updatable views
• Indexed views
• View limitations
• DML triggers
• After update trigger
• Instead of insert trigger
• Instead of update triggers
• Derived tables and common table expressions
• CTE
• Updatable common table expressions
• Recursive CTE
• Database normalization
• Second normal form and third normal form
• Pivot
• Error handling
• Transactions
• Transactions and ACID Tests
  • Subqueries in SQL
• Correlated subquery in SQL
• Creating a large table with random data for performance testing
• What to choose for performance SubQuery or Joins
• Cursors
• Replacing cursors using joins

• List all tables in a SQL Server database using a query
• Writing re runnable SQL Server scripts
• Alter database table columns without dropping the table
• Optional parameters stored procedures
• Merge
• SQL server concurrent transactions
• SQL server dirty read example
• SQL server lost update problem
• Non-repeatable read example
• Phantom reads example
• Snapshot isolation level
• Read committed snapshot isolation level
• Difference between snapshot isolation and read committed snapshot
• SQL Server deadlock example
• SQL Server deadlock victim selection
• Logging deadlocks
• SQL Server deadlock analysis and prevention
• Capturing deadlocks in SQL profiler
• SQL Server deadlock error handling
• Handling deadlocks in ado net
• Retry logic for deadlock exceptions
• How to find blocking queries
• SQL Server except operator
• Difference between except and not
• Intersect operator
• Difference between union intersect and except
• Cross apply and outer apply
• DDL Triggers
• Server scoped ddl triggers
• SQL server trigger execution order
• Audit table changes
• Logon Triggers
• Select into
• Difference between where and having
• Table-valued parameters
• Send datatable as parameter to stored procedure
• Grouping Sets
• Rollup
• Cube
• Difference between cube and rollup
• Grouping function
• GROUPING ID function
• Debugging sql server stored procedures
• Over clause
• Row Number function
• Rank and Dense Rank
• Difference between rank dense rank and row number in SQL
• Calculate running total
• NTILE function
• Lead and Lag functions
• FIRST VALUE function
• Window functions
• Difference between rows and range
• LAST VALUE function
• UNPIVOT
• Reverse PIVOT table
• Identifying object dependencies
• sys dm SQL referencing entities
• Difference between sequence and identity
• How to check GUID is null or empty
• Dynamic SQL
• Implement search web page using ASP NET and Stored Procedure
• Implement search web page using ASP NET and Dynamic SQL
• Prevent SQL injection with dynamic SQL
• Dynamic SQL in Stored Procedure
• SQL Server query plan cache
• Exec vs sp execute SQL
• Dynamic SQL table name variable
• Quote name function
• Dynamic SQL vs Stored Procedure
• Dynamic SQL output parameter
• Temp tables in dynamic SQL