This session covers the foundational aspects of data, its quality, and the evolution and necessity of Business Intelligence in modern decision-making.
Big DataData Collection refers to "facts which have similar attributes or characteristics."
No particular format or sequence, no data model, not easily usable. Doesn't follow rules or semantics with no easily identifiable structure.
Similar entities are grouped with insufficient metadata. No fixed data model but contains tags and elements.
Conforms to a data model with data stored in rows and columns. Attributes in a group are consistent, residing in fixed fields. Definition, format, and meaning are explicitly known.
| Data Type | Distribution |
|---|---|
| Unstructured | 80% |
| Semi-structured | 10% |
| Structured | 10% |
Data quality is depicted as a hierarchy leading to action:
Data is obtained from multiple disparate sources (integrated).
Data correctly represents the real-world entities and values it is intended to model.
All required data is present and no critical information is missing.
Data values are uniform across different datasets and systems without contradictions.
Data is available and up-to-date when needed for decision-making processes.
A repository of information that serves as a backing storage for applications, enabling efficient data management and retrieval.
Repository of information that is used as a backing storage for some applications.
Stores current data for immediate access and operations.
Used for daily operational tasks and business processes.
Focus on specific business processes and operational workflows.
Typically ranges from 100 MB to GB in size.
Supports transaction processing for data integrity and consistency.
Used by officials, DBAs, and database professionals.
Handles day-to-day transactions that result from enterprise operations, designed for frequent queries and update operations.
Day-to-day handling of transactions that result from enterprise operation.
Stores data in normalized form to remove duplication.
Stores data in normalized format with high granularity and detailed format.
Designed mainly to support frequent queries and update operations.
Analyzes historical data from data warehouses for strategic decision making using complex queries.
Utilizes historical data in a data warehouse for purpose of making management decisions.
Analyzes historical data (terabytes) using complex queries.
Mostly data aggregation and less update operations.
Gives information for strategic decision making.
This session delves into data warehousing, data mining, and different approaches to building data warehouses, along with key measurement terminology and decision types.
Data Mining & Data Warehouse ApproachesA repository that deals with multiple subject areas. It is not a product, it's a data environment. Data warehousing is the process of designing, building, and maintaining a data warehouse system.
Subject areas are populated from one or more databases
Data consolidated from multiple sources
Data is permanent and stable over time
Tracks historical changes over time
Stores the historical data
Users are knowledge workers who make informed decisions
Size ranges from 100 GB to TB scale
Supports analytical processing for insights
Used for long term informational requirements and decision support
Multiple databases & systems
Extract, Transform, Load
Centralized repository
Reports & Insights
Strategic decisions
Process of discovering meaningful correlations, patterns and trends by selecting through large amounts of data stored in repositories.
It employs pattern recognition technologies, as well as statistical and mathematical techniques.
Convert raw data into analyzable format
Store transformed data efficiently
Present insights and findings
Identify recurring patterns and relationships in data
Apply statistical methods to understand data distribution
Use mathematical algorithms to predict outcomes
Leverage AI algorithms for automated insights
Identify product associations and customer buying patterns
Group customers based on similar characteristics and behaviors
Forecast future trends and outcomes using historical data
Identify unusual patterns indicating fraudulent activities
The approach for building a data warehouse is based on business requirements. Two primary methodologies exist, each with distinct philosophies and implementation strategies.
Dimension Modelling (Bottom-Up)
DW is made up of all the data marts in an enterprise.
(bottom-up approach)
3NF - More Tables, More Joins (Top-Down)
An enterprise has one data warehouse; and data marts source their information from the data warehouse.
(top-down approach)
Understanding the hierarchy of measurement concepts from basic counts to actionable insights
Countability
The basic unit of quantification - what can be counted or measured
Degree of measure
A standard unit that provides context and scale to measurements
Metric to track performance
A specific metric used to monitor and evaluate performance
Composite set of indicators
A combined measure made up of multiple indicators
Measurable value to check efficiency of company
Critical metrics that directly measure success against strategic objectives
Understanding decision types based on impact and frequency - from strategic long-term planning to operational day-to-day activities
Long-term, organization-wide decisions that shape the future direction and competitive position
Mid-term decisions that implement strategic plans through resource allocation and process optimization
Day-to-day decisions that ensure smooth operations and immediate task execution
This session focuses on Business Analytics, its various types, and the comprehensive framework of Business Intelligence components.
Business Analytics & BI Component FrameworkIt allows users to examine and manipulate data to drive positive business actions. Business users can make well-informed, fact-based decisions to support their organization's tactical and strategic goals.
What happened?
Why it happened?
What is happening?
Why it is happening?
What will happen?
Historical Data
Current Analysis
Predictions
Business Decisions
Understanding the key differences between Business Intelligence and Business Analytics - from reporting capabilities to predictive modeling and testing methodologies.
Track key performance indicators and business metrics
Set thresholds for automatic notifications
Visual representation of business data
Measure performance against objectives
Multi-dimensional data analysis
On-demand data exploration
Apply statistical methods to data
Discover patterns in large datasets
Forecast future outcomes and trends
Test multiple variables simultaneously
Descriptive analytics - What happened and why
Predictive & prescriptive analytics - What will happen
Together they provide complete business insight
Business Analytics encompasses four key types, progressing from understanding the past to automating future decisions.
What happened?
Analyzes historical data to understand past performance and trends
Why did it happen?
Investigates causes and reasons behind past outcomes
What will happen?
Forecasts future outcomes using statistical models and ML
What should I do?
Recommends optimal actions based on predictions
Provides insights and recommendations to aid human decision-making
Automates decisions based on predefined rules and algorithms
Business Intelligence tools span across data extraction, reporting, and analysis phases of the BI lifecycle.
Enterprise data integration platform
ETL and data integration tool
Business intelligence suite
Visual analytics platform
Microsoft's analytics service
Statistical analysis software
Predictive analytics platform
Data Extraction
Visualization
Insights Generation
A comprehensive framework that outlines all essential components and their relationships in a Business Intelligence ecosystem.
Business drivers: manufacturing, telecommunications, IT, banking, finance, transportation, travel, energy.
When a strategy is implemented against certain business goals, then certain costs are involved. However, the final output of this process should create such business whose ratio to the costs involved should be feasible rather.
It is the benefit to the investor resulting from an investment of some resource.
Indicator of how profitable a company is relative to its total assets. It is the rate at which invested Capital.
It is a financial estimate intended to help buyers and owners determine the direct and indirect costs of a product or system.
Comparative measure that evaluates the TCO and any additional benefits, such as the mobility of laptops when compared to desktops.
Seamless integration of people, project and job activities and smooth functioning of entire program.
The process of development consists of:
The BI Component Framework integrates Business Layer requirements and value metrics with Program Management oversight, guiding the Development process to create a robust Business Intelligence ecosystem that delivers measurable business outcomes.
This session introduces Multi-dimensional Data Modeling, addressing challenges with relational models and explaining key concepts like surrogate keys, dimensions, facts, and schemas.
Multi-dimensional Data Modeling (MDDM)Database is concurrently updated by millions of users. Results in transactional locks on data, slower and time consuming data read operation.
Data is highly normalized usually in 3NF, requires data to be fetched from multiple tables, more no. of joins.
Alphanumeric primary keys Joins between tables with alphanumeric keys result in high memory consumption which is why numeric columns are to be used.
Transactional data is scattered over multiple tables. Makes aggregation and summarization difficult.
Data like month, year, quarter values for a date component is not available. Unable to do time series based analysis.
Arrangement of database tables designed to optimize, query, analyze and reporting needs of an enterprise.
It helps with data retrieval operation and also called Dimensional or Dimension model.
Summarizes data, for ex. products with highest profit margin for last three quarters, would give a more clearer picture than product sales details.
Simplifies performance related problems:
It is an additional primary key of dimension table which is populated using ETL (extract, transform, load) process.
Primary keys are usually alphanumeric and are designed with intent for providing additional information about it. For analysis we need to form multiple joins and these primary keys create high memory consumption which is why numeric columns are to be used.
Capture changes in master data. For example: petrol prices change frequently. In a transactional processing system, updating the record is easy. But for time series analysis in a data warehouse, we need historical data. A surrogate key allows us to have multiple rows of the same product with different effective dates to preserve history.
Descriptive information used to group transactional data. This provides context to analyze transactional information. It provides the "who, what, where, when, why, and how" context surrounding a business process event. It may have attributes/columns.
Transactional data which is being analyzed by business user.
A set of related dimensional attribute (Columns).
Panipat is a city in the state of Haryana in India.
Hierarchy Facilitates different view of summarized data.
For ex. viewing sales details by country (less details) vs. viewing sales details by city (more details).
Drill down: in-depth reporting.
An attribute of hierarchy at which data is collected or stored in the fact table.
If you store data in the fact table for each city on daily basis, then city and day are grains.
Data collected / basic attribute from dimension table, retrieved to the fact table.
An arrangement of fact and dimension table, called schema. You can use ER diagram or logical data model or physical data model to represent details of the schema.
A Fact table sales is connected to dimensions customer, date, product and geography (Star schema).
This session continues with MDDM, focusing on various types of dimensions, detailed explanations of different schema types, and categories of facts.
Multi-dimensional Data Modeling (MDDM)Dimension whose attribute value changes infrequently.
Old data is replaced with new data, hence history will not be preserved.
New record is inserted to add new information. Both old and new record coexist in the table to preserve history. The new record with new info is assigned a new primary key.
Change the dimension structure, add a new column or modify existing columns.
Dimension whose one or more attribute values change rapidly over a period of time.
Age of a person, test score of student, credit history or account status of customer.
A single dimension needed to be used for different context (in analysis), such dimensions are role playing.
Dimension without any descriptive or contextual information. In real life, degenerate dimension doesn't have their own separate existence. It is good to keep such data along with transaction data to avoid managing one extra table.
In real life, these are rarely used.
Dimension table that consists of columns such as column with few unique values, typically status Flags (yes/no), boolean values (true/false), or classifications (male/female).
Contains attributes that neither belong to the fact table nor dimension tables.
They can have values such as true/false or yes/no which have no correlation with each other, you can convert such values into a descriptive attribute.
Example: Sales fact table in a retail store can have fields like transaction id, coupon-indicator, and Customer-card-type (silver, gold, platinum). The above mentioned fields can be combined into a single junk dimension to be used in a warehouse.
A Schema defines the structure of a data warehouse, organizing fact and dimension tables to optimize analytical queries. Below are the primary types of schemas used in multi-dimensional data modeling.
The Star Schema is the simplest data warehouse schema. It features a central "fact" table surrounded by denormalized dimension tables (no joins), allowing all related attributes to one table for faster querying.
Simpler queries, faster aggregation, quicker initial setup.
Data redundancy due to denormalization.
The Snowflake Schema is an extension of the Star Schema where dimension tables are normalized into multiple related tables. This reduces data redundancy but increases the complexity of the conceptual design and requires extra effort for more joins.
Reduced data redundancy, better data integrity, smaller disk space usage.
More complex queries (more joins), slower query performance for some operations, harder to maintain.
The Fact Constellation Schema involves multiple fact tables that share dimension tables. It is useful when there are complex business processes that require collection of measures and dimensions at different levels or when there is a need to avoid excessive normalization while handling large amounts of data.
Flexibility to model multiple processes, can share dimension tables across fact tables.
Very complex to design and manage, difficult to implement and query.
Can be summarized across all dimensions (star, snowflake schema).
Can be summarized across one or few dimensions but not across all. (Usually cannot be summed across time dimension).
Cannot be summarized across any dimension.
Headless table (a data table that is created without keys and has large volume of data but doesn't require to involve business logic).
This session introduces the fundamentals of Unix and Shell Scripting, covering its features, importance in BI, file system, permissions, and basic commands.
Unix/Shell ScriptingUnix adds more functionality to BI by providing powerful tools for data collection, data warehousing, data integration, data manipulation, data loading (in to star schema) and reporting.
Unix scripts are for automated data loading into data warehouse.
Unix shell scripting is used for data cleaning and data manipulation.
Shell scripts play an important role in ETL process.
Unix scripts are used to prepare a new file in case of error.
Describes the structure and organization of how data is stored on a disk.
Occupies beginning of the FS.
Maintains state of the FS (its size, where to find inode list etc.).
It follows the super block. Internal representation of file.
Contains data (size of blocks can vary from 512 bytes to 8 KB).
The root ('/') is the highest directory.
Consistent placement of file data.
Create and delete files.
Dynamic growing attributes.
Protection of file data.
It allocates read/write data in blocks.
Keeps track files with inode numbers.
Info regarding owner, group etc.
Permissions are set for 3 classes:
(r)
(w)
(x)
Uses numbers for 'chmod'
chmod 755 file.txt
chmod 777 file.txt
Uses characters to add (+), remove (-), or set (=) permissions
u=owner
g=group
o=others
a=all
r=read,
w=write,
x=execute
chmod u+x file.sh
chmod go-rwx file.txt
Note: chmod is used for setting/changing file/directory permissions.
Path from the root ('/').
/home/user/report.txt
Path relative to current directory.
report.txt
pwd
(present working directory)
Command [Options] [Arguments]
Options are used to modify the shell.
Is a command interpreter. It helps to run programs.
Variables are used to store output.
Accepts user input.
Executes commands.
An internal command.
An executable file that contains a series of shell commands.
A .out executable file that contains object code and binary data.
cd new_dir
(change directory)
cd /home/user/new_dir
(absolute path)
cd -
(moves to previous directory)
cd
(doesn't affect present directory)
cd ..
(moves up one directory)
pwd
cal
mkdir
rmdir
touch
cp
mv
rm
clear
ls
date
| Command | Description / Example |
|---|---|
who am I |
Displays the specific user (user id plus 10.123.198.255) |
who |
Lists all users currently logged in. |
clear |
Clears the screen. |
date |
Displays date and time. Example: date +%D (outputs date in MM/DD/YY)Example: date +%T (outputs time in HH:MM:SS)Example: date +%m (outputs month in short form)Example: date +%y (outputs year in 2 digits)
|
cal |
Displays calendar. Example: cal 2 2014 (Feb, February 2014)Example: cal -3 (previous, current, next month)Example: cal -y (shows year calendar)
|
ls |
Lists directory contents. Example: ls -l (long listing format: permissions, owner, group, size, date)Example: ls -lh (reverse sort)Example: ls -la (all files including hidden)Example: ls -lt (recursive list)
|
cat |
Concatenates and displays file content. Assume: file1.txt contains "World", and file2.txt contains "Nation"Example: cat file1.txt file2.txt# Output: World Nation Example: cat file1.txt file2.txt > output.txtExample: cat output.txt# Output: "merge to main files" |
touch |
Creates empty files or updates timestamps. Example: touch newfile
|
cp |
Copies files/directories. Example: cp file1.txt file2.txt (recursive copy of directory)Example: cp -r new_dir_one_dir_two
|
mv |
Moves or renames files/directories. Example: mv oldname.txt newname.txtExample: mv file.txt parent/child/newdirectory
|
rm |
Removes files/directories. Example: rm file.txtExample: rm -r directory (recursive removal)
|
df |
Disk free: reports file system disk space usage. |
du |
Disk usage: estimates file space usage. |
This session dives into advanced Unix commands, focusing on redirection, piping, utility commands, and a range of text processing tools.
Unix/Shell Scripting>
Standard output redirection (overwrite).
<
Standard input.
2>
Standard error.
|
Pipe (output of one command becomes input of another).
ls -l | wc -l
Counts the number of files/directories in the current directory
ps
Process status
nohup
Runs commands in the background, immune to hangups
nohup sort content.txt &
ps -ef | grep nohup.out
cat nohup.outzip
Compresses files
zip temp.zip temp.txtunzip
Decompresses files
unzip temp.zip temp.txt
# Original file is deleted and unzipped file remains.The tr command is used to translate or delete characters.
$ cat sample.txt | tr '[a-z]' '[A-Z]'
# Converts lower case letters to upper case
$ cat sample.txt | tr -d ' '
# Deletes all space characters
The cut command is used to extract characters or fields from each line of a file or stdin.
$ cut -c 1-5 sample.txt
# Extracts characters 1 to 5 from each line
$ cut -d ':' -f 1,3 /etc/passwd
# Extracts 1st and 3rd fields delimited by ':'
The head command displays the first part of a file, and tail displays the last part.
$ head -5 sample.txt
# Displays the first 5 lines
$ tail -10 sample.txt
# Displays the last 10 lines
$ tail -f logfile.log
# Continuously monitors the file for new content (-f flag)
The cmp command compares two files byte by byte and reports the first difference.
$ cmp file1.txt file2.txt
# Compares two files and shows the byte and line where they differ
The comm command compares two sorted files line by line and produces three columns of output:
$ comm file1.txt file2.txt
# Compares two sorted files
$ comm -12 file1.txt file2.txt
# Shows only lines common to both files
The wc command counts lines, words, and characters in a file.
$ wc sample.txt
# Shows lines, words, and characters
$ wc -l sample.txt
# Shows only the line count
The sort command sorts the lines of a text file.
$ sort sample.txt
# Sorts the file in ascending order
$ sort -r sample.txt
# Sorts in reverse (descending) order
$ sort -n numbers.txt
# Sorts numerically (useful for numeric data)
$ sort -u sample.txt
# Sorts and removes duplicate lines (unique)
The grep command searches for patterns in files and prints matching lines.
$ grep "error" logfile.log
# Searches for the word "error" in the log file
$ grep -n "warning" logfile.log
# Shows line numbers with matching lines
$ grep -i "ERROR" logfile.log
# Case-insensitive search
$ grep -v "success" logfile.log
# Shows all lines that don't contain "success"
This session concludes Unix/Shell Scripting with in-depth coverage of AWK and SED, along with essential shell scripting constructs like variables, arguments, loops, and conditional statements.
Unix/Shell ScriptingPattern scanning and processing language.
awk 'BEGIN {print "Initializing"} {print "Processing line:", $0} END {print "Finished"}' param.txt
# Example: if param.txt has two lines "line1" and "line2"
# Output:
# Initializing
# Processing line: line1
# Processing line: line2
# Finishedawk 'BEGIN {total=0} {total++} END {print "Total lines:", total}' param.txt
# Example: if param.txt has 3 lines, output: Total lines: 3# Assume 'emp.dat' contains:
# John Doe 10000
# Jane Smith 15000
awk '{print NF, $NF}' emp.dat
# Output:
# 3 10000
# 3 15000awk '{sum += $3} END {print "Total Salary:", sum}' emp.dat
# Output: Total Salary: 25000Processes data line by line as a continuous stream.
Supports powerful pattern matching using regex and conditions.
Allows custom actions and scripts for flexible data processing.
Efficiently reads and processes text files and streams.
Handles complex string manipulations and positional operations.
Non-interactive command. Supports single regular expression. Used for filtering.
's'
Substitute
'a'
Append
'i'
Insert
'c'
Change
'd'
Delete
'p'
Print
line 1: apple banana
line 2: cherry apple
line 3: banana grapesed 's/apple/orange/' text.txt# line 1: orange banana
# line 2: cherry orange
# line 3: banana grapesed 's/apple/orange/g' text.txt# line 1: orange banana
# line 2: cherry orange
# line 3: banana grapesed '1a\
New data after line 1' text.txt# line 1: apple banana
# New data after line 1
# line 2: cherry apple
# line 3: banana grapesed '1i\
New data before line 1' text.txt# New data before line 1
# line 1: apple banana
# line 2: cherry apple
# line 3: banana grapesed '1d' text.txt# line 2: cherry apple
# line 3: banana grapesed '1,2d' text.txt# line 3: banana grapesed -n '1p' text.txt# line 1: apple bananaEssential building blocks for shell script automation and control flow.
#!/bin/bash
num1=10
num2=20
echo "sum of num1 and num2 is: $((num1 + num2))"
echo "difference: $((num1 - num2))"
echo "product: $((num1 * num2))"
echo "division: $((num1 / num2))"
echo "modulo: $((num1 % num2))"Note: Escape sequence: '\n' for newline, '\t' for tab.
#!/bin/bash
echo "Name of the file: $0"
echo "Total number of arguments passed: $#"
echo "The arguments passed: $*"
echo "The 1st argument: $1"
echo "The 2nd argument: $2"
echo "The 3rd argument: $3"
echo "sum of args passed: $(( $1 + $2 + $3 ))"
# Example output when run as: ./script.sh 10 20 30
# Name of the file: ./script.sh
# Total number of arguments passed: 3
# The arguments passed: 10 20 30
# The 1st argument: 10
# The 2nd argument: 20
# The 3rd argument: 30
# Sum of args passed: 60#!/bin/bash
for i in 10 20 30; do
echo "Output: $i"
done
# Output:
# 10
# 20
# 30#!/bin/bash
a=10
while [ $a -gt 0 ]; do
echo "Value of a: $a"
a=$(( a - 1))
done
# Output: Countdown from 10 to 1#!/bin/bash
a=0
until [ $a -eq 10 ]; do
echo "Value of a: $a"
a=$(( a + 1))
done
# Output: Count up from 0 to 9'-eq'
Equal to
'-ne'
Not equal to
'-lt'
Less than
'-le'
Less than or equal to
'-gt'
Greater than
'-ge'
Greater than or equal to
'='
Equal to
'!='
Not equal to
'-z'
String is null (zero length)
'-n'
String is not null (non-zero length)
'-f'
File exists and is a regular file
'-d'
File exists and is a directory
'-s'
File exists and has size > 0 (not empty)
'-r'
File exists and is readable
'-w'
File exists and is writable
'-x'
File exists and is executable
'&&'
AND (both commands execute if successful)
'||'
OR (second command executes if first is unsuccessful)
#!/bin/bash
read -p "Username: " user_var
read -sp "Password: " pass_var -sp for silent input
if [ "$user_var" = "YourUsername" ] && [ "$pass_var" = "YourPassword" ]; then
echo "Welcome $user_var"
else
echo "Invalid Username or Password"
fi#!/bin/bash
read -p "File or Directory Name: " fname
if [ -f "$fname" ]; then
echo "$fname is a regular file."
elif [ -d "$fname" ]; then
echo "$fname is a directory."
else
echo "$fname does not exist or is not a regular file/directory."
fi#!/bin/bash
echo "Please enter a fruit:"
read FRUIT_INPUT
case $FRUIT_INPUT in
"apple") echo "You like apples.";;
"banana") echo "You like bananas.";;
*) echo "Unknown fruit.";;
esacbreak
Exit loop, 'break n' (exit nth loop).
continue
Jump to start of loop, 'continue n' (continue nth loop).
exit
Terminate program.
# a simple function
my_function() {
echo "Hello from my function!"
# Function can be called like a command & it appends to all arguments passed to the function
}# Function with return value
add_numbers() {
local num1=$(( $1 + 2 )) # local makes var a local variable
# Return from a function
return $num1
}
# Calling Functions
my_function -arg1 -arg2
add_numbers 5 # Call with parameter
# So when you call the main add_numbers 5 # Returns 7${#string} or $(echo $string | wc -c)
Substitute a match of regex in string.
$(echo "$string" | sed 's/find/replace/')
Global substitute (all matches).
${string:start_pos,length}
Returns index of first character of substring.
${#string}
Returns length of string.
${string:start_pos,length}
Extract substring.
$(echo "$string" | tr 'a-z' 'A-Z')
Removing a substring.
$(echo "$command" | awk '{print toupper($0)}' or ${string^^})
Removing a string.
${#string} or $(expr length "$string")
Convert between number bases.
$(echo "$string" | tr 'A-Z' 'a-z')
Returns phonetic representation for searching similar sounding words.
This session begins our deep dive into Advanced SQL Concepts, covering SSMS, database types, sequences, data types, and the powerful ROLLUP and CUBE operators.
Advanced SQL ConceptsMost user-friendly interface to perform common database tasks. A database in SQL Server is made up of a collection of tables containing rows and columns and stores structured data.
Built-in databases.
'master'
Contains system-level info, logins, config. SQL Server cannot start without it.
'model'
Template for creating new user-defined databases.
'resource'
Read-only database, contains all system objects. Logical appearance in 'sys' schema.
'msdb'
Used for scheduling jobs, auto-backup of database, sending alerts.
'tempdb'
Temporary workspace for user and internal objects. Content recreated on restart.
Created by users to store data.
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which it was created. It can be used to generate primary key values automatically, similar to identity columns, but offers more flexibility as a single sequence can be used by multiple tables, and values can be retrieved before insertion into a table.
-- Creating a sequence named Emp_Seq that starts at 101 and increments by 1
CREATE SEQUENCE Emp_Seq
START WITH 101
INCREMENT BY 1;
-- Using the sequence to insert a value into an Employees table
-- This assumes EmployeeID is not an IDENTITY column, but takes values from a sequence
INSERT INTO Employees (EmployeeID, Name)
VALUES (NEXT VALUE FOR Emp_Seq, 'John Doe');Note: There should be only one Identity column on a table, and its value will be generated automatically.
'numeric(p, s)'
Exact numeric data. 'p' (precision) total digits, 's' (scale) digits after decimal. Example: numeric(5,2) for 123.45.
'decimal(p, s)'
Similar to 'numeric'.
'money'
Monetary data type.
'date'
Stores date only (YYYY-MM-DD).
'time'
Stores time only (HH:MM:SS).
'datetime'
Stores both date and time.
Generates subtotals for hierarchical grouping and a grand total. Order of columns matters.
Consider the following 'Sales' data:
-- Sales Table Sample Data:
-- Country | State | Amount
-- --------|-------|--------
-- INDIA | AP | 10
-- INDIA | KA | 20
-- USA | NY | 30
-- USA | CA | 40SELECT Country, State, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ROLLUP(Country, State);-- Country | State | TotalSales
-- --------|-------|------------
-- INDIA | AP | 10
-- INDIA | KA | 20
-- INDIA | NULL | 30 -- Subtotal for INDIA (sum of AP and KA)
-- USA | NY | 30
-- USA | CA | 40
-- USA | NULL | 70 -- Subtotal for USA (sum of NY and CA)
-- NULL | NULL | 100 -- Grand Total (sum of all amounts)Produces subtotals for all possible combinations of grouping columns and a grand total. Order of columns does not typically matter.
Using the same 'Sales' data as above:
SELECT Country, State, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY CUBE(Country, State);-- Country | State | TotalSales
-- --------|-------|------------
-- INDIA | AP | 10
-- INDIA | KA | 20
-- INDIA | NULL | 30 -- Subtotal for INDIA (Country only)
-- USA | CA | 40
-- USA | NY | 30
-- USA | NULL | 70 -- Subtotal for USA (Country only)
-- NULL | AP | 10 -- Subtotal for State AP (across all countries)
-- NULL | KA | 20 -- Subtotal for State KA (across all countries)
-- NULL | NY | 30 -- Subtotal for State NY (across all countries)
-- NULL | CA | 40 -- Subtotal for State CA (across all countries)
-- NULL | NULL | 100 -- Grand TotalReturns a specified number or percentage of rows. WITH TIES includes rows that tie for the last position.
SELECT TOP (n | n PERCENT) [WITH TIES] Column(s) FROM Table [ORDER BY Column]
SELECT TOP 10 * FROM products
ORDER BY Price DESC;SELECT TOP 5 PERCENT * FROM employees
ORDER BY Salary DESC;SELECT TOP 3 WITH TIES EmployeeName, Salary
FROM Employees
ORDER BY Salary DESC;Creates a new table in the default filegroup and then inserts the query results into it. It checks constraints on the new table. 'NOT NULL' constraints are imported.
Automatically creates a table based on the column structure of the SELECT query.
Populates the newly created table with data from the query results.
Enforces constraints on the new table including NOT NULL definitions.
Creates the table in the default filegroup unless otherwise specified.
SELECT * INTO EmployeeBackup
FROM Employees;SELECT EmployeeName, Salary INTO SalaryReport
FROM Employees
WHERE Salary > 50000;SELECT E.EmployeeName, E.Salary, D.DepartmentName
INTO EmployeeDepartmentReport
FROM Employees E
JOIN Departments D ON E.DeptID = D.DeptID;SELECT INTO cannot be used if the target table already exists. To insert data into an existing table, use INSERT INTO SELECT instead.
This session continues with Advanced SQL, covering NULL handling, temporary tables, identity columns, the differences between TRUNCATE and DELETE, and various ranking functions.
Advanced SQL ConceptsCritical SQL Server settings that control how NULL values are handled in comparisons and string concatenations.
'col' = NULL) results in UNKNOWNNULL = NULL is UNKNOWN. Use IS NULL or IS NOT NULL to check for NULLs.NULL = NULL evaluates to TRUENULLTables created for temporary storage of intermediate results.
Visible to the current session. Dropped when session ends.
CREATE TABLE #localTemp (ID INT, Name VARCHAR(50));Visible to all sessions. Dropped when all sessions referencing them disconnect.
CREATE TABLE ##globalTemp (OrderID INT, Amount DECIMAL(10,2));Automatically generates numeric values for new rows (usually for primary keys). IDENTITY(seed, increment)
The IDENTITY property automatically generates unique numeric values for rows as they are inserted into a table.
CREATE TABLE employees (
empId INT IDENTITY(1,1) PRIMARY KEY, -- Starts at 1, increments by 1
empname VARCHAR(25) NOT NULL
);
INSERT INTO employees (empname) VALUES ('Mark'); -- ID auto-generatedIdentity_insert allows explicit insertion into an identity column when set ON.
Both remove rows, but differ in operation, logging, and performance.
| Feature | TRUNCATE TABLE | DELETE FROM |
|---|---|---|
| Operation | DDL (Data Definition Language). Deallocates data pages. | DML (Data Manipulation Language). Removes row by row. |
| Transaction Log | Minimal logging (page deallocation). Faster for large tables. | Logs each deleted row. Slower for large tables. |
| Rollback | Can be rolled back if in explicit transaction, but complex due to page deallocation. | Fully transactional, can always be rolled back. |
| Identity Column | Resets to seed value. | Does NOT reset. Continues from last value. |
| WHERE Clause | Cannot use. Removes all rows. | Can use to remove specific rows. |
| Triggers | Does not fire DELETE triggers. | Fires DELETE triggers. |
| Table Locks | Acquires exclusive table lock. | Acquires row/page/table locks. |
| Performance | Faster on large tables. | Slower on large tables. |
Assigns a rank to each row within an ordered partition of a result set.
Window functions that assign ranks based on row ordering within partitions:
Assigns a unique, sequential integer. Ties get different numbers arbitrarily.
SELECT ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS RankNum FROM Employees;Assigns same rank to ties, then skips subsequent rank numbers.
SELECT RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS RankNum FROM Employees;Assigns same rank to ties, does NOT skip subsequent rank numbers.
SELECT DENSE_RANK() OVER (PARTITION BY Dept ORDER BY Salary DESC) AS RankNum FROM Employees;Divides rows into 'n' groups. Useful for quartiles, deciles, percentiles.
SELECT NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile FROM Employees;This final SQL session covers conversion, date/time functions, error handling with TRY...CATCH, and the powerful concept of stored procedures, concluding with a review of practical SQL snippets.
Advanced SQL ConceptsConvert data from one type to another. CAST is ANSI standard, CONVERT is SQL Server specific with formatting options.
ANSI standard. Converts a value to a specified data type.
SELECT CAST(100 AS DECIMAL(5,2)); -- Converts Integer 100 to decimal 100.00
SELECT CAST('2023-12-31' AS DATE); -- Converts string to date typeSQL Server specific, with optional style for formatting.
SELECT CONVERT(VARCHAR(10), GETDATE(), 'MM/DD/YYYY'); -- Converts current date to 'MM/DD/YYYY' string
SELECT CONVERT(INT, 123.45); -- Converts decimal to integerFunctions for retrieving, manipulating, and calculating date and time values.
Returns current system date and time.
Returns integer part of date. Interval can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, etc.
SELECT DATEPART(YEAR, GETDATE()); -- Year
SELECT DATEPART(QUARTER, GETDATE()); -- Quarter
SELECT DATEPART(MONTH, GETDATE()); -- Month (1-12)
SELECT DATEPART(MONTH, GETDATE()); -- Month
SELECT DATEPART(DAY, GETDATE()); -- Day of month
SELECT DATEPART(DAYOFYEAR, GETDATE()); -- Day of year
SELECT DATEPART(WEEK, GETDATE()); -- Week (1-53)
SELECT DATEPART(HOUR, GETDATE()); -- Hour
SELECT DATEPART(MINUTE, GETDATE()); -- Minute
SELECT DATEPART(WEEKDAY, GETDATE()); -- Weekday (1-7)
SELECT DATEPART(MINUTE, GETDATE()); -- MinuteAdds/subtracts interval. Useful for calculating future or past dates.
SELECT DATEADD(MONTH, 3, GETDATE()); -- Adds 3 months to current date
SELECT DATEADD(DAY, -7, GETDATE()); -- Subtracts 7 days from current dateCalculates difference between two dates in specified interval.
SELECT DATEDIFF(DAY, '2023-01-01', GETDATE()); -- Days between Jan 1, 2023 and todayRobust error handling in T-SQL. Catch exceptions and handle them gracefully.
TRY...CATCH blocks allow you to handle errors gracefully without stopping execution:
BEGIN TRY
-- TRY block
SELECT 1 / 0; -- Example: division by zero
SELECT 'Success!' AS Message;
END TRY
BEGIN CATCH
-- Error handling logic
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;Returns the error number.
Returns the error severity level.
Returns the error state.
Returns name of the procedure where error occurred.
Returns the error message text.
Returns the line number where error occurred.
Precompiled collections of SQL statements stored as named objects. Enable reusability and improved performance.
Improved performance.
Reduced network congestion.
Code reusability.
Maintainability and Reliability.
Built-in procedures for administrative tasks (e.g., 'sp_who', 'sp_helpdb').
Created by users for specific business logic.
CREATE PROCEDURE WelcomeProcedure
AS
BEGIN
PRINT 'Welcome to stored procedure execution!';
END;CREATE PROCEDURE GetCustomerDetails @CustomerId INT , @CustomerName VARCHAR(50) OUTPUT
AS
BEGIN
SELECT @CustomerName = CustomerName
FROM Customers
WHERE CustomerID = @CustomerId;
END;
-- Execute the procedure
DECLARE @Name VARCHAR(50);
EXEC GetCustomerDetails @CustomerId = 1, @CustomerName = @Name OUTPUT;
PRINT @Name;CREATE PROCEDURE GetAllCustomers
AS
BEGIN
SELECT * FROM Customers;
END;
-- Execute the procedure
EXEC GetAllCustomers;WITH ENCRYPTION: Encrypts procedure definition.
DROP PROCEDURE: Deletes a procedure.
Comprehensive collection of practical SQL queries covering key concepts from all sessions.
SELECT TOP 10 ProductName, Price FROM products ORDER BY Price DESC;SELECT TOP 3 WITH TIES EmployeeName, Salary FROM Employees ORDER BY Salary DESC;SELECT DATEPART(YEAR, GETDATE());SELECT DATEADD(MONTH, 3, GETDATE());SELECT DATEDIFF(DAY, '2023-01-01', GETDATE());CREATE TABLE Emp (
EmpID INT IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(50)
);INSERT INTO Emp (EmpName) VALUES ('John'); -- EmpID will be auto-generatedTRUNCATE TABLE Emp;DELETE FROM Emp WHERE EmpID = 1;BEGIN TRY
SELECT 1 / 0; -- This will cause a division by zero error
END TRY
BEGIN CATCH
-- Selects various error details when an error occurs
SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE();
END CATCH;CREATE PROCEDURE CheckUserStatus
@gender CHAR(1),
@maritalStatus VARCHAR(10)
AS
BEGIN
IF NOT (@gender IN ('M', 'F'))
BEGIN
RETURN -1; -- Invalid gender
END
IF NOT (@maritalStatus IN ('Married', 'Single'))
BEGIN
RETURN -2; -- Invalid marital status
END
-- Further logic here if inputs are valid
END;-- Assumes Emp_Seq sequence is created and Employee_Bangalore table exists.
INSERT INTO Employee_Bangalore VALUES (NEXT VALUE FOR Emp_Seq, 'John');-- Sample Sales table as shown in ROLLUP section above.
SELECT Country, [State], SUM(Amount) AS TotalSales FROM Sales GROUP BY ROLLUP(Country, [State]);-- Sample Sales table as shown in CUBE section above.
SELECT Country, [State], SUM(Amount) AS TotalSales FROM Sales GROUP BY CUBE(Country, [State]);-- Returns the first non-NULL expression among its arguments
SELECT COALESCE(NULL, 'Default Value'); -- Output: 'Default Value'
SELECT COALESCE(10, NULL, 20); -- Output: 10SELECT ROUND(49.99, 1); -- Rounds 49.99 to one decimal place, Output: 50.0
SELECT FLOOR(49.99); -- Returns the largest integer less than or equal to 49.99, Output: 49-- SUBSTR is typically SUBSTRING in T-SQL
SELECT SUBSTRING('Example String', 1, 7); -- Standard T-SQL equivalent
-- Returns 'Example' (illustrative, use SUBSTRING in T-SQL)
-- CAP, SHRT, LONG are likely conceptual functions for demonstration, not standard SQL
-- SELECT CAP('example'); -- Convert to uppercase first letter (illustrative)
-- SELECT SHORT('VeryLongString'); -- Shorter string (illustrative)
-- SELECT LONG('Short'); -- Extend string (illustrative)
-- CHR is common in SQL dialects
SELECT CHAR(65); -- Returns 'A' (SQL Server, Oracle equivalent)
-- REVERSE is common in SQL dialects
SELECT REVERSE('Text'); -- Returns 'txeT' (SQL Server)This session introduces fundamental probability concepts, including experiments, sample spaces, events, and basic probability rules, along with the product and sum rules for counting.
Probability & Counting TechniquesFoundational concepts for understanding probability theory and uncertain outcomes.
An activity whose outcome is uncertain.
Example: Rolling a die, drawing a card, value of stock.
Set of all possible outcomes.
Example: For a die roll, S = {1, 2, 3, 4, 5, 6}.
A subset of the sample space.
Example: Getting an even number on a die roll E = {2, 4, 6}.
P(E) = Number of favorable outcomes / Total number of possible outcomes
In a bag, there are 3 Red balls and 2 Blue balls. Total = 5 balls. The probability of drawing a Red ball is P(Red Ball) = 3/5 = 0.6.
Occurrence of one does not affect the other.
Example: Tossing a coin twice. The outcome of the first toss does not affect the second.
Cannot occur at the same time. P(A โฉ B) = 0.
Example: When rolling a single die, the event of rolling an even number {2, 4, 6} and the event of rolling an odd number {1, 3, 5} are mutually exclusive.
0 โค P(E) โค 1
P(not A) = 1 - P(A) (Complement Rule)
If A and B are mutually exclusive: P(A โช B) = P(A) + P(B) (Addition Rule for Mutually Exclusive Events)
General Addition Rule: P(A โช B) = P(A) + P(B) - P(A โฉ B)
Note: A โฉ B: Event A AND Event B both occurred.
A โช B: Event A OR Event B occurred (or both).
Fundamental rules for counting the number of ways to accomplish tasks or arrange objects.
If there are $m$ ways to do one task and $n$ ways to do a second task, then there are $m \times n$ ways to do both.
If there are 5 ways to go from A to B, 6 ways from B to C, 5 ways from C to D, and 4 ways from D to E, the total number of ways to travel from A to E is:
N(Total Ways) = 5 ร 6 ร 5 ร 4 = 600
If a task can be done in $m$ ways OR $n$ ways (mutually exclusive), then $m + n$ ways total.
You can choose a main course from 3 options (Chicken, Fish, Veg) OR a dessert from 2 options (Cake, Ice Cream). If you only pick one item total, then there are 3 + 2 = 5 total choices.
This final session on Probability & Counting Techniques covers permutations, combinations, measures of central tendency, dispersion, and shape, concluding with practical quiz questions.
Probability & Counting TechniquesUnderstanding arrangements and selections in probability
Arrangements where order matters.
Formula:
P(n, r) = n! / (n-r)!
Example (Arrangement of 3 letters):
Arrange 3 distinct letters (e.g., A, B, C) from a set of 3. Here n=3, r=2.
P(3, 2) = 3! / (3-2)! = 3! / 1! = 6 รท 1 = 6
Possible arrangements: AB, AC, BA, BC, CA, CB
Selections where order does not matter.
Formula:
C(n, r) = n! / (r!(n-r)!)
Example (Cricket Team Selection):
Select 9 players from a squad of 20, where 2 specific players are always selected and 3 are never selected.
1. Total players in squad: 20
2. Players always selected: 2
3. Players never selected: 3
4. Remaining pool to choose from: 20 - 2 - 3 = 15 players
5. Players still needed for the team: 9 - 2 = 7 players
C(15, 7) = 15! / (7!(15-7)!) = 15! / (7! ร 8!) = (15 ร 14 ร 13 ร 12 ร 11 ร 10 ร 9) / (7 ร 6 ร 5 ร 4 ร 3 ร 2 ร 1) = 6435
Answer: There are 6435 ways to select the team.
Understanding data location and typical values
Average value. Sensitive to outliers.
Formulas:
Sample Mean (xฬ): ฮฃx / n
Population Mean (ฮผ): ฮฃx / N
Middle value when data is ordered. Less affected by extreme values.
Example: For dataset {1, 3, 5, 7, 9}, Median is 5
Example: For dataset {2, 3, 4}, Median is (2+3)/2 = 2.5
Most frequent value. A dataset can have multiple modes.
Example: For {1, 2, 2, 3, 4}, Mode is 2
Example: For {1, 2, 2, 3}, Modes are 1 and 2 (bimodal)
Understanding data spread and variation
Difference between maximum and minimum values.
Formula:
Range = Max Value - Min Value
Example: For dataset {10, 20, 5, 25, 15}, Range = 25 - 5 = 20
Average of squared differences from the mean.
Formulas:
Population Variance (ฯยฒ): ฮฃ(x - ฮผ)ยฒ / N
Sample Variance (sยฒ): ฮฃ(x - xฬ)ยฒ / (n-1)
Square root of variance. Same units as data.
Formulas:
Population SD (ฯ): โ(ฮฃ(x - ฮผ)ยฒ / N)
Sample SD (s): โ(ฮฃ(x - xฬ)ยฒ / (n-1))
Spread of middle 50% data. Robust to outliers.
Formula:
IQR = Qโ - Qโ
Example: For ordered data {1, 2, 3, 5, 6, 7, 8}, Qโ = (2+3)/2 = 2.5, Qโ = (6+7)/2 = 6.5, IQR = 6.5 - 2.5 = 4
Understanding distribution symmetry and peakedness
Measures asymmetry of distribution.
Positive Skew (Right-skewed):
Tail on right is longer. Mean > Median > Mode
Example: Income distribution in a country (few high earners)
Negative Skew (Left-skewed):
Tail on left is longer. Mean < Median < Mode
Example: Scores on a very easy test (most score high)
Measures "tailedness" and peakedness of distribution.
Leptokurtic (High Kurtosis):
Heavy tails, sharp peak (more outliers)
Mesokurtic (Normal Kurtosis):
Similar to normal distribution
Platykurtic (Low Kurtosis):
Light tails, flat peak (fewer outliers)
Test your understanding with practical problems
Total students: 400
Students enrolled in Data Science: 320
Find the probability that a randomly selected student is enrolled in Data Science.
Solution:
P(Data Science Student) = 320 / 400 = 0.8
Total students: 1000
Total girls: 410
Girls from Class XII: 10% of 410 = 41
Find the probability that a randomly selected girl is from Class XII.
Solution:
P(Class XII | Girl) = (Number of Class XII Girls) / (Total Number of Girls) = 41 / 410 = 0.1
How many 4-letter passwords can be created from the letters A, B, C, D, E (with no repetition)?
Solution:
This is a permutation problem since order matters in passwords.
P(5, 4) = 5! / (5-4)! = 5! / 1! = 120 / 1 = 120 passwords
How many ways can a committee of 3 members be selected from a group of 8 people?
Solution:
This is a combination problem since order doesn't matter for committee membership.
C(8, 3) = 8! / (3!(8-3)!) = 8! / (3! ร 5!) = (8 ร 7 ร 6) / (3 ร 2 ร 1) = 336 / 6 = 56 ways