SQL Server Basics
SQL Server Basics
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?"
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.
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);
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.
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
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
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.
o name
o email
2. Orders:
o order_id
o amount
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:
2024-12-
101 1 250
01
2024-12-
102 2 300
02
2024-12-
103 NULL 150
03
04
2024-12- john@example.c
101 250 1 John Doe
01 om
2024-12- john@example.c
101 250 1 John Doe
01 om
2024-12-
103 150 NULL NULL NULL
03
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 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.
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.
orders table:
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
FROM orders
ON orders.customer_id = customers.customer_id;
Result:
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.
FROM orders
ON orders.customer_id = customers.customer_id;
Result:
01
2024-01-
2 102 Bob
02
2024-01-
3 103 Charlie
03
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:
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.
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.
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()
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()
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
Use Cases:
RANK() is useful when you want to account for ties but still acknowledge
the skipped positions.
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
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!