Data A&E Essentials

-

 SQL Server Index/Topics:

  • Introduction to SQL Server
  • SQL Server Overview
  • Structured Query Language (SQL)
  • Databases
  • Tables
    • Create, Alter, Rename, Temp, Derieved)
  • Data Types
  • SQL Basic Commands 
    • (DML, DDL, TCL, INSERT, TRUNCATE, PIVOT, MERGE, CASE, CTE)
  • Constraints
    • (PK, FK, CHECK, DEF, UNIQUE)
  • Joins
  • Subqueries
  • Built in Functions
  • Operators
  • Decision Making Statements
  • Views
  • Indexes
    • CLUSTERED, NON-CLUSTERED, FILTERED)
  • Advanced SQL:
    • Table Partitions
    • Try Catch errors
    • Views
    • T-SQL Programming
    • Stored Procedures
    • Triggers
    • User Defined Functions
    • Cursors
  • Advanced SQL Functions:
    • Aggregate Functions
    • String Functions
    • Date functions
    • system methods
    • Ranking Functions
    • Logical Functions
    • Analytics functions
    • Conversion functions
  • Normalization
  • Transactions (Optional)
  • Locks (Optional)
  • Rules and Defaults (Optional)
  • Backup and Recovery

Case Study
Examples for Practice
Interview Preparation tips and prac



Cloud Technologies:

  • Azure Cloud
    • Basics:
      • Introduction to Azure Cloud
      • Services Available
      • Subscription, resource group
    • Technologies/Services:
      • Azure SQL Server
      • Azure Data Factory
      • Azure Analysis Services
      • Azure Blob Storage
      • Azure Logic Apps
      • Azure Functions
      • Azure Key Vaults
      • Azure Data Bricks (PySpark)


Reporting Tools:

  • Power BI
    • Power BI Desktop
    • Power BI service

    

Programming Languages:

  • Python


Comments

Post a Comment

Popular posts from this blog

TIME-SERIES SQL

-
  🔥 TIME-SERIES SQL (MSSQL SERVER ONLY) 📌 Sample Table (Used in All Queries) CREATE TABLE transactions ( user_id INT , txn_time DATETIME, amount INT ); 🟢 SIMPLE LEVEL 1️⃣ Running Total (Cumulative Sum) SELECT user_id, txn_time, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY txn_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM transactions; 2️⃣ Row-Based Moving Average (Last 3 Records) SELECT user_id, txn_time, amount, AVG(amount) OVER ( PARTITION BY user_id ORDER BY txn_time ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM transactions; 3️⃣ Time Difference Between Events SELECT user_id, txn_time, LAG(txn_time) OVER ( PARTITION BY user_id ORDER BY txn_time ) AS prev_time, DATEDIFF( MINUTE , LAG(txn_time) OVER (PARTITION BY user_id ORDER ...
Read more

PySpark Interview Questions with Detailed Answers

-
  Spark Internals & Execution 1. Explain the full lifecycle of a Spark job — from code to DAG to task execution. Definition: The Spark job lifecycle begins when the driver program initiates an action (e.g., .collect() , .count() ). Here's a step-by-step breakdown: User Code: You write transformations and actions in PySpark. Logical Plan: Spark creates a logical plan based on your transformations. Optimization: Catalyst optimizer optimizes the logical plan. Physical Plan: Spark creates a physical plan with stages. DAG (Directed Acyclic Graph): Stages are translated into a DAG of tasks. Task Scheduling: Tasks are distributed across executors. Execution: Executors run the tasks in parallel. Healthcare Example: In processing electronic health records (EHR), you may filter patients by diagnosis, join with lab results, and then aggregate. Spark translates this pipeline into optimized stages and tasks. 2. What is the difference between transformations and actions? Why are tra...
Read more

SCD TYPE 2 – INTERVIEW QUESTIONS + MERGE CODE

-
 Below is a COMPLETE, interview-ready guide to SCD Type 2 using MERGE , covering ALL POSSIBLE QUESTIONS (theory + edge cases) AND production-grade SQL code . This is curated for senior data engineer / Azure / Databricks / SQL Server interviews. 🔹 SCD TYPE 2 – INTERVIEW QUESTIONS + MERGE CODE 1️⃣ What is SCD Type 2? Answer: SCD Type 2 maintains full historical changes by: Expiring old records Inserting a new row for every change Typical columns: effective_start_date effective_end_date is_current version (optional) 2️⃣ SCD Type 2 Table Design (Interview MUST) 🎯 Dimension Table CREATE TABLE dim_customer ( customer_sk INT IDENTITY ( 1 , 1 ), customer_id INT , name VARCHAR ( 100 ), city VARCHAR ( 100 ), effective_start_date DATE , effective_end_date DATE , is_current CHAR ( 1 ) ); 🎯 Staging Table CREATE TABLE stg_customer ( customer_id INT , name VARCHAR ( 100 ), city VARCHAR ( 100 ), load_date ...
Read more