0% found this document useful (0 votes)
5 views42 pages

SQL Server Basics

The document provides an overview of Relational Database Management Systems (RDBMS) in SQL Server, detailing its structure, components, and functions such as data storage, retrieval, and manipulation. It distinguishes between databases and data warehouses, emphasizing their roles in real-time transactions versus historical data analysis. Additionally, it covers key concepts in data modeling, normalization, SQL queries, and performance optimization techniques in SQL Server.

Uploaded by

chavauha25
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
5 views42 pages

SQL Server Basics

The document provides an overview of Relational Database Management Systems (RDBMS) in SQL Server, detailing its structure, components, and functions such as data storage, retrieval, and manipulation. It distinguishes between databases and data warehouses, emphasizing their roles in real-time transactions versus historical data analysis. Additionally, it covers key concepts in data modeling, normalization, SQL queries, and performance optimization techniques in SQL Server.

Uploaded by

chavauha25
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 42

An RDBMS (Relational Database Management System) in SQL

Server is a software system used to manage and organize data


in a relational database. It stores data in tables with rows and
columns, using structured query language (SQL) to query and
manipulate the data. SQL Server ensures data integrity,
security, and supports relationships between different tables
using keys, enforcing constraints and rules to maintain
consistency
Database:
A database is an organized collection of structured data that
allows for easy storage, retrieval, management, and
manipulation of data. It is mainly used for handling
transactional data in real-time. Databases support operations
like INSERT, UPDATE, DELETE, and SELECT for handling real-
time data. For example, a hospital database may store patient
information, appointments, and treatment records.
Data Warehouse:
A data warehouse is a centralized system that stores historical
data for analysis and reporting. It supports complex queries and
decision-making and works with ETL processes to integrate
data from various sources. Examples include Snowflake,
Amazon Redshift, and Google Big Query
1. Data Warehouse Architecture:
o Data warehouse architecture typically consists of:
 Data Sources: Various source systems from
where the data is extracted.
 Staging Area: A temporary area where raw
data is loaded before it is transformed.
 ETL Layer: The process responsible for
extracting, transforming, and loading the data
into the warehouse.
 Data Warehouse: The main repository that
stores transformed data.
 Business Intelligence Tools: Used by end-
users to query and visualize data.

Key Benefits of a Data Warehouse:


 Centralized Data: A data warehouse consolidates data
from various systems, providing a single point of access
for analysis and reporting.
 Historical Analysis: Storing historical data allows
businesses to track trends and make data-driven
decisions.
 Improved Decision-Making: With structured data,
business analysts can easily generate reports and insights
that help make informed decisions.
 Performance Optimization: Data warehouses are
optimized for read-heavy operations, making querying and
reporting faster than in transactional databases.
 Data Consistency: By cleaning and transforming data
during the ETL process, a data warehouse ensures
consistent data across the organization.

Key Difference:
 Database is used for daily operations and real-time
transactions, while a data warehouse is used for storing
large volumes of historical data for analysis and reporting.
Consistent data means data that is accurate, reliable, and
uniform across all systems or records. It doesn't have
contradictions or errors, and it follows the same format and
rules everywhere. This helps ensure that the information is
trustworthy and can be used confidently for analysis and
decision-making.
Data Modeling is the process of designing and
structuring a database to store and organize data
effectively. It helps in ensuring that data is stored in an
efficient, consistent, and accessible way. Here’s a short
explanation of key data modeling concepts:
1. Entities:
 An entity represents a real-world object or
concept, like a customer, product, or employee.
 Each entity has attributes (characteristics), like a
customer's name, age, and address.
2. Relationships:
 A relationship defines how entities are related to
each other. For example, a Customer places an
Order.
 Relationships can be:
o One-to-One (1:1): One entity is related to
only one of another entity.
o One-to-Many (1:N): One entity is related to
many others.
o Many-to-Many (M:N): Many entities are
related to many others.
3. Primary Key (PK):
 A Primary Key uniquely identifies each record in
an entity (e.g., CustomerID).
4. Foreign Key (FK):
 A Foreign Key is a column in one table that refers
to the Primary Key in another table. It establishes
relationships between tables.
5. Normalization:
 Normalization is the process of organizing data to
reduce redundancy and improve data integrity. It
involves splitting tables to ensure that each table
contains data related to a single entity.
Normalization is the process of organizing data
to reduce redundancy and improve integrity.
There are different normal forms (NF) that help
in this process:
1. First Normal Form (1NF):
 Goal: Eliminate duplicate data and ensure each
field contains only atomic (single) values.
 What it means: Each column should contain only
one value per row, and each row must be unique.
 Example: Instead of having a column that lists
multiple phone numbers for a person, store each
phone number in a separate row.
2. Second Normal Form (2NF):
 Goal: Remove partial dependency, which means
every non-key column should depend on the
entire primary key.
 What it means: The table should already be in
1NF, and there should be no partial dependency of
attributes on part of the primary key.
 Example: If a table has both OrderID and
ProductID as a composite primary key, any non-key
field like ProductName should depend on the entire
composite key, not just ProductID.
3. Third Normal Form (3NF):
 Goal: Remove transitive dependency, where non-
key columns depend on other non-key columns.
 What it means: The table should already be in
2NF, and there should be no dependencies
between non-key columns.
 Example: If EmployeeID → DepartmentID and
DepartmentID → DepartmentName, the
DepartmentName should be moved to a separate
table to avoid transitive dependency.
In short:
 1NF: Ensure all fields have single, atomic values.
 2NF: Eliminate partial dependency (every non-key
field depends on the whole primary key).
 3NF: Remove transitive dependency (non-key
fields should not depend on other non-key fields).
6. Schema:
 A Schema defines the structure of the database,
including tables, relationships, and constraints.
7. ER Diagram (Entity-Relationship Diagram):
 ER Diagram is a visual representation of the
entities and their relationships in a database. It’s a
key tool in data modeling.
Summary:
Data modeling helps define the structure of data in a
way that improves storage efficiency, reduces
redundancy, and ensures data integrity.
SQL SERVER BASICS
Database Understanding in SQL Server refers to the knowledge of
how a database is structured, its components, and how data is
stored, accessed, and managed within the SQL Server
environment. This understanding is crucial for designing,
maintaining, and optimizing SQL Server databases.
Key Elements of Database Understanding in SQL Server:
1. Tables: The basic storage units where data is stored in rows
and columns.
2. Indexes: Structures that speed up data retrieval by allowing
faster lookups.
3. Primary and Foreign Keys: Constraints that enforce
relationships between tables and maintain data integrity.
4. Views: Virtual tables created by querying one or more tables
to simplify complex queries.
5. Stored Procedures: Predefined SQL code that can be
executed to perform tasks like data manipulation or
reporting.
6. Triggers: Special types of stored procedures that
automatically execute in response to certain events (e.g.,
INSERT, UPDATE, DELETE).
7. Schemas: Organizational containers that help manage
database objects (tables, views, etc.) and define their
structure.
8. Normalization: The process of organizing data to eliminate
redundancy and improve data integrity.
9. Transactions: Units of work that ensure data is processed
reliably (with properties like ACID — Atomicity, Consistency,
Isolation, Durability).
10. Backup and Recovery: Strategies for safeguarding data
and ensuring that data can be recovered in case of failure.
In short:
Database Understanding in SQL Server involves knowledge of
how data is organized, managed, and maintained within a SQL
Server database, including its structure, relationships, and
security features.
 Accuracy: Data must be correct and free from errors. For instance, a financial report must accurately reflect the actual
amounts and transactions. Any mistakes or inaccuracies (such as typos or wrong values) compromise the integrity of the
data.

 Consistency: Data must remain consistent across all systems and over time. For example, if a customer’s contact details
are updated in one part of a database, they should be automatically updated in all related sections (such as customer
orders, support records, etc.). Inconsistent data can lead to confusion and errors in decision-making.

 Reliability: Data must be dependable and trustworthy. For example, when you retrieve data from a system or database, it
should be the same every time, provided nothing has changed. The system should be resistant to corruption or
unauthorized modifications, ensuring the data can be relied upon for critical decisions

DBMS
A DBMS (Database Management System) in SQL Server is
software that helps you create, manage, and interact with
databases. It provides a structured way to store, retrieve, and
manipulate data. SQL Server is a type of DBMS developed by
Microsoft, which uses Structured Query Language (SQL) to
interact with the database.
Key Functions of DBMS in SQL Server:
1. Data Storage: Organizes data in tables, rows, and columns
for efficient access and management.
2. Data Retrieval: Allows users to retrieve data using SQL
queries.
3. Data Manipulation: Supports operations like inserting,
updating, and deleting data.
4. Data Integrity: Enforces rules to ensure data is accurate,
consistent, and reliable.
5. Security: Provides authentication and authorization to
control access to data.
6. Backup and Recovery: Manages data backup and ensures
recovery in case of failure.
In short:
A DBMS in SQL Server manages databases, ensuring efficient
storage, retrieval, and security of data while providing tools for
maintenance and querying through SQL.

SQL Queries
SQL Queries in SQL Server are commands written in Structured
Query Language (SQL) used to interact with and manipulate
databases. SQL queries allow you to retrieve, insert, update, and
delete data in SQL Server databases, as well as define and
manage the structure of the database.
Common Types of SQL Queries:
1. SELECT: Retrieves data from one or more tables.
o Example: SELECT * FROM Employees WHERE Age > 30.
2. INSERT: Adds new rows of data into a table.
o Example: INSERT INTO Employees (Name, Age,
Department) VALUES ('John', 25, 'HR');
3. UPDATE: Modifies existing data in a table.
o Example: UPDATE Employees SET Age = 26 WHERE
Name = 'John’.
4. DELETE: Removes data from a table.
o Example: DELETE FROM Employees WHERE Name =
'John’.
5. JOIN: Combines data from multiple tables based on a related
column.
o Example: SELECT Employees. Name, Departments.
Name FROM Employees JOIN Departments ON
Employees.DepartmentID = Departments.ID.
6. GROUP BY: Groups rows that have the same values into
summary rows.
o Example: SELECT Department, COUNT (*) FROM
Employees GROUP BY Department.
7. HAVING: Filters groups after a GROUP BY query.
o Example: SELECT Department, COUNT (*) FROM
Employees GROUP BY Department HAVING COUNT (*) >
5;
8. ORDER BY: Sorts the result set by one or more columns.
o Example: SELECT * FROM Employees ORDER BY Age
DESC;
9. Subqueries: A query inside another query, used for complex
conditions.
o Example: SELECT * FROM Employees WHERE
DepartmentID IN (SELECT ID FROM Departments
WHERE Name = 'HR');
10. Aggregate Functions: Functions like SUM (), AVG(),
MAX(), MIN() that perform calculations on multiple rows of
data.
 Example: SELECT AVG(Salary) FROM Employees.
Common Interview Questions for SQL Server:
Based on your 1.8 years of experience, here are some types of
SQL queries they may ask in an interview:
1. Basic SQL Queries: Select, Insert, Update, Delete operations.
o Example: "Write a query to get the top 5 highest-paid
employees."
2. Joins: Inner Join, Left Join, Right Join, Full Outer Join.
o Example: "Explain the difference between INNER JOIN
and LEFT JOIN. Write a query to list all orders and the
customer details."
3. Grouping and Aggregation: Group By, HAVING, aggregate
functions like COUNT, SUM, AVG.
o Example: "Write a query to find the total sales by each
department."
4. Subqueries: Using subqueries in SELECT, Where, or HAVING
clauses.
o Example: "Write a query to get employees whose
salary is higher than the average salary in the
company."
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG (salary) FROM employees).
5. Performance Optimization: Using indexing, writing efficient
queries.
o Example: "How would you optimize a query that takes
too long to execute?"

To optimize a slow query in SQL Server:

 Use Indexes: Create indexes on columns used in JOIN, WERE, or ORDER BY clauses to
speed up data retrieval.
 Avoid SELECT *: Only retrieve the columns you need to reduce data processing and
transfer.
 Rewrite Subqueries as Joins: Convert subqueries into JOIN operations, which are
often faster.
 Review Execution Plan: Use SQL Server's execution plan (SET SHOWPLAN_ALL ON)
to identify bottlenecks like missing indexes or expensive operations.
 Filter Early: Apply restrictive conditions early in the WHERE clause to limit the
dataset processed.
 Update Statistics: Ensure table statistics are up to date to help the SQL Server query
optimizer make better decisions.

6.SQL Functions: Using functions like CASE, COALESCE,


ISNULL.
o Example: "Write a query that categorizes employees
into 'Junior' and 'Senior' based on their years of
experience."
SELECT employee_id, employee_name,experience_years,
CASE WHEN experience_years < 5 THEN 'Junior' ELSE
'Senior'
END AS employee_category
FROM employees.
7.Data Modifications: Inserting, updating, and deleting data
with conditions.
o Example: "How do you update multiple rows in a table
based on a condition?"

UPDATE employees
SET salary = CASE
WHEN department_id = 1 THEN 60000
WHEN department_id = 2 THEN 55000
ELSE salary
END,
job_title = CASE
WHEN department_id = 1 THEN 'Senior Developer'
WHEN department_id = 2 THEN 'Junior Developer'
ELSE job_title
END
WHERE department_id IN (1, 2);

7. Transactions: Using BEGIN, COMMIT, and ROLLBACK for


managing transactions.
1. Example: "What is the difference between COMMIT and
ROLLBACK in SQL transactions?
 COMMIT: The COMMIT command is used to save all changes made during the
current transaction permanently to the database.
BEGIN TRANSACTION.
UPDATE employees SET salary = 50000 WHERE department_id = 1;
COMMIT.
 ROLLBACK: command is used to undo all changes made during the current
transaction. It reverts the database to its state before the transaction started.
BEGIN TRANSACTION.
UPDATE employees SET salary = 50000 WHERE department_id = 1;
ROLLBACK.

Aspect COMMIT ROLLBACK

Makes changes Undoes changes, reverts to


Purpose
permanent original state

Reverts all changes made


Effect Saves all changes
during the transaction

After a successful When an error occurs, or you


Use case
transaction want to discard changes

Transaction Ends the transaction Cancels the transaction and


State and commits data undoes all changes

Joins
What are Joins in SQL? IN SQL, joins are used to combine records
from two or more tables based on a related column. Joins allow
you to retrieve data that is spread across multiple tables, linking
them together in a meaningful way.
There are different types of joins in SQL:
1. INNER JOIN:
o Returns only the rows where there is a match in both
tables.
2. LEFT JOIN (or LEFT OUTER JOIN):
o Returns all rows from the left table, and the matched
rows from the right table. If there is no match, NULL
values are returned for columns
3. RIGHT JOIN (or RIGHT OUTER JOIN):
o Returns all rows from the right table, and the matched
rows from the left table. If there is no match, NULL
values are returned for columns from the left table.
4. FULL OUTER JOIN:
o Returns all rows when there is a match in one of the
tables. It returns NULL for non-matching rows from
either side.
5. CROSS JOIN:
o Returns the Cartesian product of both tables, i.e., it
returns all possible combinations of rows from both
tables.
6. SELF JOIN:
o A join where a table is joined with itself. It's used when
you want to relate rows within the same table.

VIEWS: -A view in SQL Server is a virtual table that represents the


result of a stored query. It doesn't store data itself but presents
data from one or more tables when queried.
Uses of Views in SQL Server:
 A view in SQL is a saved query that simplifies complex
operations (like joins or calculations), matking queries
easier to write and maintain.
 Security: Views can limit access to specific columns or
rows, protecting sensitive data.
 Abstraction: Views hide complex details and provide a
simpler way to access data.
 Data Consistency: Views ensure consistent results for
different users or parts of an app.
 Views can be read-only or updatable, depending on their
complexity.
 Views don’t store data but offer a convenient way to
access and present it.

CREATE VIEW EmployeeDetails AS


SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;
SELECT * FROM EmployeeDetails;/
 Accurate means the data is correct.
 Reliable means the data is dependable and consistent over time.
consistent means maintaining stability and correctness without
unexpected changes or errors.

CTE
A CTE (Common Table Expression) in SQL Server is like a
temporary table that exists only while you’re running a query.
You create it using the WITH keyword, and it helps make
complex queries easier to read and write.
 Temporary: The CTE only exists during the query execution
and is not stored in the database.
 Readability: It breaks down complex logic into simpler parts,
making the query easier to understand.
 Recursion: CTEs are useful for problems where you need to
handle data that relates to itself, like showing an employee
and their manager in a company.
EX: - WITH Sales Employees AS (
SELECT Employed, Name FROM Employees WHERE
Department = 'Sales')
SELECT * FROM Sales Employees.

MERGE
The MERGE statement in SQL is used to perform insert, update, or
delete operations on a target table based on the results of a
source table. It allows you to combine multiple operations in a
single query.
EXAMPLE
MERGE INTO target_table AS target
USING source_table AS source
ON target.column = source.column
WHEN MATCHED THEN
UPDATE SET target.column = source.column
WHEN NOT MATCHED BY TARGET THEN
INSERT (column1, column2) VALUES (source.column1,
source.column2)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
WHEN NOT MATCHED BY SOURCE: If a record exists in the target
(Employees) but does not exist in the source (NewEmployees),
that record is deleted from the target table.
EXAMPLE:-
Suppose you have two tables: Employees and New Employees.
You want to update the salaries of employees based on the New
Employees table, and if the employee doesn't exist in Employees,
you want to insert them.
MERGE INTO Employees AS e
USING NewEmployees AS ne
ON e.EmployeeID = ne.EmployeeID
WHEN MATCHED THEN
UPDATE SET e.Salary = ne.Salary
WHEN NOT MATCHED BY TARGET THEN
INSERT (EmployeeID, FirstName, LastName, Salary)
VALUES (ne.EmployeeID, ne.FirstName, ne.LastName,
ne.Salary)
WHEN NOT MATCHED BY SOURCE THEN
DELETE.

Stored Procedure
A Stored Procedure in SQL is a set of SQL commands that are
saved in the database and can be run whenever needed. It's like a
reusable function or script that helps simplify complex or
repetitive tasks.
Key Points:
 Predefined: Stored procedures are written once and stored
in the database.
 Reusable: You can call (run) a stored procedure any time
without rewriting the code.
 Parameters: You can pass values to a stored procedure (e.g.,
search criteria) to get specific results.
EX:_1) CREATE PROCEDURE GetEmployeesByDept
@DeptName VARCHAR(50) -- This is a parameter
AS
BEGIN
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department = @DeptName.
END.
2. Run the Stored Procedure:
EXEC GetEmployeesByDept 'Sales’.
Why Use Stored Procedures?
 Save Time: Write complex queries once and run them
whenever needed.
 Efficiency: Stored procedures are fast because they're
precompiled.
 Security: You can control access, allowing users to run
procedures without directly accessing data.

Functions:
A function in SQL is a stored program that accepts input
parameters, performs an operation, and returns a result.
Functions are used to simplify complex expressions or
calculations and can be used directly in SQL queries.
Key Points:
 Input Parameters: Functions accept parameters (inputs).
 Return Value: Functions always return a single value or a
table.
 Used in Queries: You can use a function in SELECT, WHERE,
HAVING, etc.
Example 1: Scalar Function (Single Value)
A scalar function returns a single value. For example, creating a
function to calculate the square of a number:
CREATE FUNCTION GetSquare (@Number INT)
RETURNS INT
AS
BEGIN
RETURN @Number * @Number.
END.
SELECT dbo.GetSquare(5); -- Output: 25
This function returns the square of the number 5.
Example 2: Table-Valued Function (Returns Table)
A table-valued function (TVF) returns a table instead of a single
value. For example, a function to get employees from a specific
department:
CREATE FUNCTION GetEmployeesByDept (@DeptName
VARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department = @DeptName
);
SELECT * FROM dbo.GetEmployeesByDept('Sales');
This will return all employees from the 'Sales' department.
Summary:
 Scalar functions return a single value.
 Table-valued functions return a table. Functions help make
SQL queries cleaner and more reusable.

Performance tuning
Performance tuning in SQL Server means improving the speed
and efficiency of your database and queries. queries run faster,
use fewer resources (like memory or CPU), and provide quicker
results. The goal is to make sure
Key Steps in Performance Tuning:
1. Optimizing Queries: Write simpler, more efficient SQL
queries.
2. Using Indexes: Create indexes to help SQL Server find data
quickly.
3. Analyzing Execution Plans: Check how SQL Server is running
your query to find slow parts.
4. Database Design: Organize tables and relationships in a way
that makes them efficient.

INDEX: -
An index is a set of keys made up of single or multiple columns in
a table or view
In SQL Server, an index is a database object that improves the
speed of data retrieval operations on a table. It works like a table
of contents in a book, allowing the database to quickly locate the
rows that match a query, rather than scanning the entire table.
Key Points:
 Faster Queries: Indexes speed up queries that search, filter,
or sort data, especially in large tables.
 Improved Performance: They are created on columns that
are frequently searched or used in JOIN and ORDER BY
operations.
 Extra Storage: While indexes improve performance, they
also use additional storage and can slow down data
modification operations (like INSERT, UPDATE, DELETE).
Types of Indexes:
1. Clustered Index: Defines the physical order of rows in the
table. There can only be one clustered index per table.
CREATE CLUSTERED INDEX index_name

ON table name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

2. Non-Clustered Index: A separate structure that points to the


data, allowing faster access. Multiple non-clustered indexes
can be created on a table.
CREATE CLUSTERED INDEX index_name
ON table name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

3. Unique Index: Ensures that values in the indexed column(s)


are unique.
CREATE [UNIQUE] INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

4. Full-Text Index: Used for efficient searches on large text-


based columns.
5. Filtered Index: A non-clustered index that only indexes a
subset of rows, based on a filter condition.
Clustered Index
 Definition: A clustered index determines the physical order
of data in a table. There can only be one clustered index per
table because the data rows are physically sorted on the
disk based on the indexed column.
 Use Case: It's used when you need to retrieve data quickly
based on the key column(s).
Non-Clustered Index
 Definition: A non-clustered index does not affect the
physical order of the data. It creates a separate structure
that points to the data rows. You can have multiple non-
clustered indexes on a table.
 Use Case: Best for improving the performance of queries
where you need to search based on columns other than the
primary key.

Triggers in SQL Server


In SQL Server, triggers are special types of stored procedures that
automatically execute (or "fire") in response to certain events or
actions on a table or view. Triggers are used to enforce business rules,
maintain data integrity, or perform auditing tasks when specific
changes occur in a database.
Key Points About Triggers:
1. Automatic Execution: Triggers are fired automatically in response
to data modification events such as INSERT, UPDATE, or DELETE on
a table or view.
2. Event-Driven: A trigger is invoked based on a predefined event
occurring on a table or view.
3. Database Integrity: Triggers can be used to enforce business
logic, audit changes, or keep the data consistent across related
tables.
Types of Triggers in SQL Server:
1. DML Triggers (Data Manipulation Language Triggers):- (TABLE
LEVEL TRIGGERS)
o These triggers are executed when data is modified in a table
(via INSERT, UPDATE, or DELETE).
Types of DML Triggers:
o AFTER Trigger (Post-Trigger): Executes after the data
modification event is completed.
o INSTEAD OF Trigger: Executes instead of the data
modification event. This allows you to define a custom
action when an INSERT, UPDATE, or DELETE is attempted.
Often used to perform complex operations or to prevent
certain actions.
Example:
o AFTER INSERT Trigger: This trigger fires after a new row is
inserted into a table.
o INSTEAD OF DELETE Trigger: This trigger fires instead of a
delete operation, allowing custom logic to be executed.
2. DDL Triggers (Data Definition Language Triggers):- (DATABASE
LEVEL TRIGGEERS)
o These triggers are executed in response to certain database
schema changes like CREATE, ALTER, and DROP operations
on tables, views, stored procedures, etc.
Example: A DDL trigger might be set to log schema changes when a
table is altered.
3. LOGON and LOGOFF Triggers:- (SERVER LEVEL TRIGGERS)
o These triggers are used to capture logon and logoff events
for auditing or custom processing, such as logging user
access to a database.

ROW_NUMBER () function
The ROW_NUMBER () function in SQL Server assigns a unique
sequential number to each row in a result set, based on a
specified order. The numbering starts at 1 for the first row and
increments by 1 for each subsequent row.
Syntax:- ROW_NUMBER() OVER (PARTITION BY <column> ORDER
BY <column>)
WITH RowNum AS (
SELECT EMP_ID, EMP_NAME, DEPT, SALARY,
ROW_NUMBER() OVER (PARTITION BY EMP_NAME, DEPT
ORDER BY EMP_ID) AS RowNum
FROM EMPNAME
)
DELETE FROM EMPNAME
WHERE EMP_ID IN (
SELECT EMP_ID FROM RowNum WHERE RowNum > 1);
Table:
EMP_I EMP_NA DEP SALAR
D ME T Y

Sale
1 John 50000
s
EMP_I EMP_NA DEP SALAR
D ME T Y

2 Jane IT 60000

Sale
3 John 50000
s

4 Emily HR 55000

Sale
5 John 50000
s
Result:
Example Data After Removal:
EMP_I EMP_NA DEP SALAR
D ME T Y

Sale
1 John 50000
s

2 Jane IT 60000

4 Emily HR 55000

HIGHEST EXPENDITURE AND LOWEST EXPENDITURES


Example using ROW NUMBER FUNCTION:
WITH SalaryRank AS (
SELECT Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees
)
SELECT Salary
FROM SalaryRank
WHERE RowNum = 5;

Example using DENSE_RANK():


WITH SalaryRank AS (
SELECT Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
)
SELECT Salary
FROM SalaryRank
WHERE Rank = 5;
LOWEST EXPENDITURE
WITH SalaryRank AS (
SELECT Salary,
DENSE_RANK() OVER (ORDER BY Salary ASC) AS Rank
FROM Employees
)
SELECT Salary FROM SalaryRank
WHERE Rank = 3;

SQL
SQL (Structured Query Language) is a standardized programming
language used to manage and manipulate relational databases. It
is designed for querying, updating, and managing data stored in
a structured format, typically in tables that consist of rows and
columns. SQL allows users to interact with databases to perform
various operations, such as:
1. Querying Data: SQL is primarily used to retrieve data from
databases using SELECT statements. For example, you can
fetch specific records or perform calculations like sums and
averages on the data.
sql
Copy code
SELECT name, age FROM users WHERE age > 30;
2. Inserting Data: SQL enables users to add new records (rows)
into a database table using the INSERT INTO statement.
sql
Copy code
INSERT INTO users (name, age) VALUES ('John Doe', 25);
3. Updating Data: SQL allows modification of existing data
within a table using the UPDATE statement.
sql
Copy code
UPDATE users SET age = 26 WHERE name = 'John Doe';
4. Deleting Data: You can delete records from a table using the
DELETE statement.
sql
Copy code
DELETE FROM users WHERE name = 'John Doe';
5. Creating and Modifying Database Structures: SQL can be
used to create new tables, alter existing tables, or remove
them entirely using commands like CREATE TABLE, ALTER
TABLE, and DROP TABLE.
sql
Copy code
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);
6. Managing Database Access and Security: SQL allows
administrators to grant or revoke access to different parts of
the database using commands like GRANT and REVOKE.
SQL works with relational databases, where data is organized into
tables that can be linked together using keys (such as primary
keys and foreign keys). Some popular relational database
management systems (RDBMS) that use SQL include:
 MySQL
 PostgreSQL
 SQLite
 Microsoft SQL Server
 Oracle Database
SQL is crucial in database management because it allows users
and systems to interact with data in an efficient, organized, and
structured way.

Difference Between INNER JOIN and LEFT JOIN:


INNER JOIN:
 The INNER JOIN keyword returns rows when there is a match in both
tables. If there is no match, the row will not be included in the result set.
 It only returns the records where there is a common match in both tables
based on the condition specified.
LEFT JOIN (or LEFT OUTER JOIN):
 The LEFT JOIN keyword returns all rows from the left table (the table
mentioned first in the query) and the matching rows from the right table
(the table mentioned second).
 If there is no match in the right table, the result will still include the rows
from the left table, with NULL values for columns from the right table.
Example:Let's say you have two tables:
1. Customers:
o customer_id

o name

o email

2. Orders:
o order_id

o customer_id (foreign key referencing customer_id in the Customers


table)
o order_date

o amount

INNER JOIN Example:


If you want to list all orders along with the customer details where there is a
matching customer for the order, you will use an INNER JOIN.
SELECT o.order_id, o.order_date, o.amount, c.customer_id, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
Explanation:
 INNER JOIN between the orders table (o) and customers table (c) ensures
that only orders that have a matching customer are listed.
 If there is no matching customer for an order, that order will not appear in
the result.
LEFT JOIN Example:
If you want to list all orders along with the customer details, even if some orders
do not have a matching customer, you would use a LEFT JOIN.
SELECT o.order_id, o.order_date, o.amount, c.customer_id, c.name, c.email
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
Explanation:
 LEFT JOIN between the orders table (o) and customers table (c) will return
all orders.
 If an order does not have a matching customer (e.g., customer_id is
missing or invalid), the result will still include the order, but the customer
columns (customer_id, name, email) will contain NULL values for that
order.
Example Data:Let's assume the following data in the tables:
Customers:

customer_
name email
id

john@example.c
1 John Doe
om

Jane jane@example.c
2
Smith om

Alice alice@example.
3
Brown com

Orders:

order_i customer order_dat amou


d _id e nt

2024-12-
101 1 250
01

2024-12-
102 2 300
02

2024-12-
103 NULL 150
03

104 3 2024-12- 200


order_i customer order_dat amou
d _id e nt

04

INNER JOIN Result:


Using INNER JOIN, the result would only show orders that have a matching
customer:

order_i order_dat amou customer


name email
d e nt _id

2024-12- john@example.c
101 250 1 John Doe
01 om

2024-12- Jane jane@example.c


102 300 2
02 Smith om

2024-12- Alice alice@example.


104 200 3
04 Brown com

Order 103 is excluded because it has no matching customer (i.e., customer_id is


NULL).
LEFT JOIN Result:
Using LEFT JOIN, the result would show all orders, even the one without a
customer:

order_i order_dat amou customer


name email
d e nt _id

2024-12- john@example.c
101 250 1 John Doe
01 om

2024-12- Jane jane@example.c


102 300 2
02 Smith om

2024-12-
103 150 NULL NULL NULL
03

2024-12- Alice alice@example.


104 200 3
04 Brown com

Order 103 is included with NULL values for the customer details because it
doesn't have a corresponding customer in the customer’s table.
Summary:
 INNER JOIN returns only rows where there is a match in both tables.
 LEFT JOIN returns all rows from the left table and matching rows from the
right table, with NULL for unmatched rows from the right table.
Difference between INNER JOIN and RIGHT JOIN:
1. INNER JOIN:

o Definition: An INNER JOIN returns only the rows where there is a match
between the two tables based on the specified condition.

o Behavior: If there is no matching record in one of the tables, those rows


will not appear in the result set.

o Use Case: It is used when you only want the rows where there is a match in
both tables.

Example: If you're joining orders and customers, you will get only the orders that are
associated with customers, and vice versa. Orders without a customer and customers
without orders won't be included.

2. RIGHT JOIN (also called RIGHT OUTER JOIN):

o Definition: A RIGHT JOIN returns all rows from the right table, and the
matching rows from the left table. If no match is found, the result will show
NULL for columns from the left table.

o Behavior: It includes all records from the right table, even if there is no
matching record in the left table.

o Use Case: It is used when you want to include all records from the right
table, even if they don't have a matching record in the left table.

Example: If you use a RIGHT JOIN between orders and customers, you will get all
customers, including those without orders, with NULL in the order-related columns where
no match is found.

Example Scenario:Let's say we have the following two tables:

orders table:

order_i customer_ order_dat


d id e

2024-01-
1 101
01

2024-01-
2 102
02

2024-01-
3 103
03

2024-01-
4 104
04

customers table:
customer_ customer_na
id me

101 Alice

102 Bob

103 Charlie

105 David

1. Using INNER JOIN:

SELECT orders.order_id, orders.order_date, customers.customer_id,


customers.customer_name

FROM orders

INNER JOIN customers

ON orders.customer_id = customers.customer_id;

Result:

order_i order_dat customer customer_na


d e _id me

2024-01-
1 101 Alice
01

2024-01-
2 102 Bob
02

2024-01-
3 103 Charlie
03

Explanation:

 The query returns only the rows where both an order and a customer exist with
matching customer_id.

 Order 4 from the orders table and customer 105 from the customers table are
excluded because there is no match for either.

2. Using RIGHT JOIN:

SELECT orders.order_id, orders.order_date, customers.customer_id,


customers.customer_name

FROM orders

RIGHT JOIN customers

ON orders.customer_id = customers.customer_id;

Result:

order_i order_dat customer customer_na


d e _id me

1 2024-01- 101 Alice


order_i order_dat customer customer_na
d e _id me

01

2024-01-
2 102 Bob
02

2024-01-
3 103 Charlie
03

NULL NULL 105 David

 Explanation: The query returns all customers, including David (customer 105) who
doesn't have any orders.

 For customers who do not have any orders, the order_id and order_date will be
NULL since there is no corresponding record in the orders table.

Summary of Differences:

 INNER JOIN: Returns only matching rows between two tables.

 RIGHT JOIN: Returns all rows from the right table, and matching rows from the left
table, filling with NULL where there is no match from the left table.

Good understanding on Data modeling concepts


ChatGPT said:
Data modeling is the process of creating a conceptual representation of data and
its relationships, which helps in organizing and structuring data in a way that is
efficient and meaningful. It's a critical aspect of database design and is essential
for ensuring data is stored, retrieved, and managed effectively. Here’s an
overview of key concepts in data modeling:
1. Types of Data Models
 Conceptual Data Model: High-level overview of the system, representing
business concepts and the relationships between them. It's more abstract
and independent of any specific database system.
 Logical Data Model: Represents the logical structure of the data, showing
how data elements are related. It doesn't consider physical
implementation details but adds more structure than the conceptual
model.
 Physical Data Model: Focuses on how the data will be physically stored in a
database system. It includes things like indexing, partitioning, and storage
details.
2. Entities and Attributes
 Entity: An object or thing in the real world that can have data stored about
it (e.g., Customer, Order, Product).
 Attribute: A property or characteristic of an entity (e.g., Customer Name,
Order Date).
3. Relationships
 Data models define how entities are related to each other:
o One-to-One: Each entity in the relationship relates to only one other
entity (e.g., each person has one passport).
o One-to-Many: One entity relates to many others (e.g., one
department can have many employees).
o Many-to-Many: Multiple entities relate to multiple others (e.g.,
students enrolling in multiple courses, and each course can have
multiple students).
4. Normalization
Normalization is the process of organizing data to reduce redundancy and
dependency. It involves dividing a database into two or more tables and defining
relationships between them.
 1st Normal Form (1NF): Ensures that each column contains atomic
(indivisible) values and that each record is unique.
 2nd Normal Form (2NF): Ensures that all non-key attributes are fully
functionally dependent on the primary key.
 3rd Normal Form (3NF): Ensures that there are no transitive dependencies
(non-key attributes depending on other non-key attributes).
5. Primary and Foreign Keys
 Primary Key: A unique identifier for a record in a table. It ensures that each
record can be uniquely identified.
 Foreign Key: A field in one table that uniquely identifies a row of another
table. It's used to establish a relationship between two tables.
6. Indexes
 Indexes are used to speed up the retrieval of data from the database. They
are typically created on columns that are frequently queried or used for
sorting.
7. ER Diagrams (Entity-Relationship Diagrams)
 ER diagrams are graphical representations of entities and their
relationships. They help in visualizing the structure of a database.
Common symbols in ER diagrams include:
o Rectangles for entities

o Ellipses for attributes

o Diamonds for relationships

o Lines connecting entities to their relationships

8. Data Integrity
 Ensuring the accuracy and consistency of data over its lifecycle. There are
different types of integrity:
o Entity Integrity: Each entity must have a unique identifier (primary
key).
o Referential Integrity: Ensures that foreign keys are valid, meaning
they refer to existing records in the parent table.
o Domain Integrity: Ensures that data in a column meets specific
constraints (e.g., valid range for age, proper format for email).
9. Star Schema and Snowflake Schema
 Star Schema: Used primarily in data warehousing, where a central fact
table is connected to dimension tables. It's simpler and more
straightforward.
 Snowflake Schema: A more complex version of the star schema, where
dimension tables are further normalized into additional tables.
10. Data Warehousing and OLAP (Online Analytical Processing)
 Data models in OLAP systems are designed to support fast querying and
analysis. Star schemas and snowflake schemas are often used to organize
data in a way that makes it easier to perform complex queries and
generate reports.
11. Dimensional Modeling
 This is used in data warehousing for structuring data in a way that makes
it easier to analyze. It includes concepts like facts (quantifiable data) and
dimensions (descriptive data, like time or location).
12. Denormalization
 The process of combining tables to reduce the number of joins required in
complex queries. While denormalization can improve read performance, it
can also introduce redundancy and affect write operations.
13. Data Modeling Tools
 There are various tools that assist in designing data models, such as
ER/Studio, Lucidchart, Microsoft Visio, and MySQL Workbench.
Best Practices in Data Modeling:
 Understand the business needs: Work closely with stakeholders to
understand the data's purpose.
 Keep the model simple: Avoid overcomplicating things. Only add
complexity when absolutely necessary.
 Document thoroughly: Good documentation ensures that the data model
can be understood and maintained over time.
 Iterate and refine: Data models should evolve as business requirements
change.

Basic SQL Questions:


1.What is SQL Server, and how does it work?
o SQL Server is a relational database
management system (RDBMS) by
Microsoft used to store, manage, and
retrieve data using SQL (Structured
Query Language).
2.What is the difference between CHAR and
VARCHAR data types in SQL Server?
o CHAR is for fixed-length strings, while
VARCHAR is for variable-length strings.
VARCHAR saves space if the string length
varies.
3.What are PRIMARY KEY and FOREIGN KEY
constraints, and what is their purpose?
o PRIMARY KEY uniquely identifies a record
in a table. FOREIGN KEY links two tables
by referencing the PRIMARY KEY of
another table.
4.What is a NULL value in SQL? How does it
differ from an empty string or zero?
o NULL means the absence of data, while
an empty string ("") or zero (0) is
considered a value.
5.Explain the difference between DELETE and
TRUNCATE commands in SQL Server.
o DELETE removes rows one by one and
can be rolled back. TRUNCATE removes
all rows and cannot be rolled back.
6.What is a subquery and when would you use
it?
o A subquery is a query inside another
query. It's used to retrieve data that will
be used in the main query.
7.What is the difference between INNER JOIN
and OUTER JOIN?
o INNER JOIN returns only matching rows,
while OUTER JOIN returns all rows from
one table and matching rows from
another.
Intermediate SQL Questions:
8.What are Index and Clustered Index in SQL
Server? What is the difference between
them?
o Index improves query performance. A
Clustered Index organizes data rows
physically in the table based on the
indexed column.
9.What is the purpose of GROUP BY in SQL, and
when would you use it?
o GROUP BY groups rows by specified
columns. It's used with aggregate
functions like COUNT, SUM, or AVG.
10. Explain the use of HAVING clause with an
example.
 HAVING filters groups in the result set after
using GROUP BY. Example: SELECT
Department, COUNT(*) FROM Employees
GROUP BY Department HAVING COUNT(*) > 5.
11. What is a Stored Procedure? How does it
differ from a Function?
 A Stored Procedure is a precompiled SQL
statement. A Function returns a value and
can be used in queries like expressions.
12. What is a CTE (Common Table Expression)
in SQL Server? Can you give an example of
when to use it?
 A CTE is a temporary result set used in
queries. It’s helpful for recursive queries or
simplifying complex queries.
13. What are Window Functions in SQL
Server, and how does ROW_NUMBER(),
RANK(), and DENSE_RANK() differ?
 Window functions calculate over a set of
rows. ROW_NUMBER() gives a unique number,
RANK() handles ties with gaps, and
DENSE_RANK() ranks without gaps.
14. Explain Normalization and the differences
between 1NF, 2NF, and 3NF.
 Normalization reduces data redundancy.
o 1NF: No repeating groups, atomic values.
o 2NF: No partial dependency (fully
depends on the primary key).
o 3NF: No transitive dependency (non-key
columns depend only on the primary
key).
Advanced SQL Questions:
15. How does SQL Server handle
transactions? What are the ACID properties
of a transaction?
 SQL Server handles transactions to ensure
data integrity. ACID stands for Atomicity,
Consistency, Isolation, and Durability.
16. What is the difference between SINGLE
and MULTI transactions in SQL Server?
 SINGLE transaction is a single unit of work.
MULTI transactions involve multiple
operations that may commit or rollback
together.
17. How would you optimize a slow-
performing SQL query?
 Use indexes, analyze execution plans, avoid
subqueries, and rewrite queries for
efficiency.
18. What is Query Execution Plan? How do
you analyze and optimize it?
 The execution plan shows how SQL Server
executes a query. You can optimize it by
analyzing steps that take time, like table
scans, and replacing them with indexes.
19. Can you explain SQL Server Profiler and
how it helps in troubleshooting?
 SQL Server Profiler captures and analyzes
SQL queries to help identify performance
issues, long-running queries, and other
problems.
20. What is Dynamic SQL? How is it used,
and when should it be avoided?
 Dynamic SQL is SQL code generated and
executed at runtime. It should be avoided
due to potential security risks like SQL
injection.
21. What are Triggers in SQL Server? Can you
give an example of how to use a trigger?
 A trigger is a special stored procedure that
automatically runs when data changes.
Example: a trigger to log updates to a table.
22. What is SQL Injection, and how can you
prevent it in SQL Server?
 SQL Injection is a security vulnerability
where attackers manipulate queries. Use
parameterized queries or stored procedures
to prevent it.
23. What are Temporary Tables and Table
Variables? What is the difference between
them?
 Temporary tables are stored in the tempdb
and can be indexed. Table variables exist
only in the scope of a query and cannot be
indexed.
24. How does SQL Server handle concurrency
and locking?
 SQL Server uses locks to ensure data
integrity. It can handle concurrency using
isolation levels, which control how locks are
applied.
SQL Server Security Questions:
25. What is SQL Server Authentication? What
is the difference between Windows
Authentication and SQL Server
Authentication?
 SQL Server Authentication is used to log in
with a username and password. Windows
Authentication uses Windows login
credentials.
26. How do you manage user permissions in
SQL Server?
 Use GRANT, DENY, and REVOKE commands to
manage user permissions on database
objects.
27. What are Roles in SQL Server? How do
they work to manage access control?
 Roles group users with similar permissions.
You can assign permissions to roles instead
of individual users.
28. What is the concept of Data Encryption in
SQL Server, and how do you implement it?
 Data encryption protects sensitive data. SQL
Server provides encryption through
Transparent Data Encryption (TDE) and
Always Encrypted features.
Backup and Recovery Questions:
29. Explain the different types of Backup in
SQL Server (Full, Differential, Transaction
Log).
 Full Backup: A complete backup of the
database.
 Differential Backup: Backs up only changes
since the last full backup.
 Transaction Log Backup: Backs up all the
transaction log changes since the last
backup.
30. How do you restore a SQL Server
database? What options do you have for
recovery?
 Use the RESTORE command to restore a
database. Options include WITH
NORECOVERY, WITH RECOVERY, and WITH
STANDBY for different types of recovery.
31. What is Point-in-Time Recovery? How
does it work?
 Point-in-Time Recovery restores a database
to a specific moment using transaction log
backups, allowing recovery to any precise
time.
Rank dense rank row number
The RANK, DENSE_RANK, and ROW_NUMBER are three different window
functions used in SQL to assign a rank or a unique number to rows based on
the ordering of the data in a result set. The key difference between them lies
in how they handle ties (i.e., when two or more rows have the same value in
the column by which the ranking is determined).

Here's a breakdown of each:

1. RANK()

 Definition: The RANK() function assigns a rank to each row within the
partition of the result set. If two or more rows have the same value in
the ordering column, they receive the same rank, but the next rank will
be skipped.

 Handling Ties: If two rows have the same value, they will receive the
same rank, and the next rank will be incremented by the number of tied
rows. For example, if two rows are tied at rank 2, the next rank will be 4.

Example:

Valu Rank
e ()

10 1

10 1
Valu Rank
e ()

20 3

30 4

2. DENSE_RANK()

 Definition: The DENSE_RANK() function also assigns a rank to each row


within the partition. However, it does not skip ranks when there are ties.
If two rows are tied, they will have the same rank, and the next rank will
be the next consecutive integer.

 Handling Ties: If two rows have the same value, they will receive the
same rank, and the next rank will just be the next number (i.e., no
gaps).

Example:

Valu Dense_Ran
e k()

10 1

10 1

20 2

30 3

3. ROW_NUMBER()

 Definition: The ROW_NUMBER() function assigns a unique, sequential


number to each row in the result set. It does not consider ties, so each
row gets a unique number, even if the values in the ordering column are
the same.

 Handling Ties: The row number is assigned based on the order of the
rows, and it simply increments by 1 for each row, even when there are
ties.

Example:

Valu Row_Numbe
e r()

10 1

10 2

20 3

30 4

Key Differences in Handling Ties:

 RANK(): Skips ranks when there are ties.

 DENSE_RANK(): Does not skip ranks when there are ties.


 ROW_NUMBER(): Assigns a unique number to each row regardless of
ties.

Use Cases:

 RANK() is useful when you want to account for ties but still acknowledge
the skipped positions.

 DENSE_RANK() is useful when you want to assign consecutive numbers,


even if there are ties.

 ROW_NUMBER() is useful when you simply need a unique number for


each row, regardless of ties.

Example Scenario:

Consider a scenario where you are ranking salespeople based on their sales
figures:

Salespers Sale
on s

Alice 500

Bob 500

Charlie 700

Dave 600

Using each of the functions:

1. RANK():

o Alice: Rank 1

o Bob: Rank 1

o Charlie: Rank 3

o Dave: Rank 2

2. DENSE_RANK():

o Alice: Rank 1

o Bob: Rank 1

o Charlie: Rank 2

o Dave: Rank 3

3. ROW_NUMBER():

o Alice: Row 1

o Bob: Row 2

o Charlie: Row 3

o Dave: Row 4

This should give you a clearer view of the differences between these ranking
functions!

You might also like