1. What is SQL?

SQL stands for Structured Query Language. It is the standard language for RDBMS and
is useful in handling organized data with entities or variables with relations between
them. SQL is used for communicating with databases.

According to ANSI, SQL is used for maintaining RDBMS and for performing different
operations of data manipulation on different datatypes by using the features of SQL. It
is a database language that is used for the creation and deletion of databases. It can
also be used, among other things, to fetch and modify the rows of a table.

2. What is a primary key?

Primary keys are unique values used to identify records. Think of this as a unique
identification card such as an Aadhar Card, or PAN Card of the data. It cannot have
null values and must have a unique value just like your PAN Card number, or Aadhar
Number. Only one primary key is allowed in one table (one Aadhar, or PAN Card per
person). It can have one or more fields making the primary key a composite key.

QUERY
1 CREATE TABLE Games (
GameID INT PRIMARY KEY,
Title VARCHAR(100) NOT NULL,
ReleaseYear INT,
Price DECIMAL(10, 2))

3. What is Foreign Key?

Foreign keys are used to establish a relationship between records in different tables,
much like a reference or linkage that connects various pieces of data. Think of this as
a reference number on your PAN Card or Aadhar Card that links to another official
document or record. A foreign key must match a primary key in another table,
ensuring that the data in one table is consistent with the data in another table. It can
accept null values if the relationship is optional. Unlike primary keys, a table can have
multiple foreign keys, each linking to different tables.

QUERY
1 CREATE TABLE Games (
2 GameID INT PRIMARY KEY, -- Unique identifier for each game
3 Title VARCHAR(100) NOT NULL, -- Title of the game
4 ReleaseYear INT, -- Year the game was released
5 Price DECIMAL(10, 2), -- Price of the game
6 PublisherID INT, -- Foreign key to reference Publisher
7 FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID) -- Define
8 foreign key relationship

4. What is the difference between Primary key and foreign key

A primary key is a unique identifier for each record within a table, ensuring that
every entry is distinct and can be uniquely accessed. It must contain unique values
and cannot have nulls, as it is crucial for maintaining the integrity and uniqueness of
data within that table. For example, in a Publisher table, the PublisherID serves as the
primary key, ensuring each publisher is uniquely identifiable.

On the other hand, a foreign key is a column (or set of columns) in one table that
creates a link to the primary key of another table. It is used to establish and enforce a
relationship between the two tables, ensuring referential integrity by requiring that
each value in the foreign key column matches a value in the primary key column of
the referenced table. Unlike a primary key, a foreign key can contain duplicate values
and may also include nulls if the relationship is optional. For instance, in the Games
table, the PublisherID acts as a foreign key that references the PublisherID in the
Publisher table, thereby linking each game to a specific publisher and maintaining the
relationship between the two tables.

5. Explain the different types of SQL commands.

Types of SQL Languages

 DDL: DDL is that part of SQL that defines the data structure of the database in the
initial stage when the database is about to be created. It is mainly used to create
and restructure database objects. Commands in DDL are:
o Create table

QUERY
1 CREATE TABLE Games ( GameID INT PRIMARY KEY, Title VARCHAR(100) NOT
NULL,Genre VARCHAR(50), ReleaseYear INT, Price DECIMAL(10, 2)

Inserting values into table –

QUERY
1 INSERT INTO Games (GameID, Title, Genre, ReleaseYear, Price)
2 VALUES
3 (1, 'Grand Theft Auto V', 'Action', 2015, 29.99),
4 (2, 'Valorant', 'FPS', 2020, 0.00),
5 (3, 'Red Dead Redemption 2', 'Action', 2018, 59.99),
6 (4, 'Wukong', 'RPG', 2024, 49.99),
7 (5, 'Cyberpunk 2077', 'RPG', 2020, 39.99),
8 (6, 'Hades', 'Rogue', 2020, 24.99);

Retrieving data –

QUERY
1 select * from Games
Alter table
If you want to add a new column to the Games table, such as Platform

QUERY
1 ALTER TABLE Games
2 ADD Platform VARCHAR(50);
3 If you want to change the data type of the Title column to allow a longer length:
4 ALTER TABLE Games
5 ALTER COLUMN Title VARCHAR(150) NOT NULL;

Drop table

DROP TABLE Games;

 DML: DML is used to manipulate already existing data in a database, i.e., it helps
users to retrieve and manipulate data. It is used to perform operations such as
inserting data into the database through the insert command, updating data with
the update command, and deleting data from the database through the delete
command.
 DCL: DCL is used to control access to the data in the database. DCL commands are
normally used to create objects related to user access and to control the
distribution of privileges among users. The commands that are used in DCL are
Grant and Revoke.
 TCL: TCL is used to control the changes made by DML commands. It also authorizes
the statements to assemble in conjunction with logical transactions. The
commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and
Transaction

6. Create a Games table with the following columns and data types?

 GameID as an integer and primary key

 Title as a variable character field with a maximum length of 100 characters and not
nullable
 Genre as a variable character field with a maximum length of 50 characters
 ReleaseYear as an integer
 Price as a decimal with a precision of 10 and scale of 2
Games table QUERY
1 CREATE TABLE Games (
2 GameID INT PRIMARY KEY, -- Unique identifier for each game
3 Title VARCHAR(100) NOT NULL, -- Title of the game
4 ReleaseYear INT, -- Year the game was released
5 Price DECIMAL(10, 2) -- Price of the game
6 );
7

Insert data into the game table

How would you insert the following 6 rows into the Games table with the columns
GameID, Title, Genre, ReleaseYear, and Price?

QUERY
1 INSERT INTO Games (GameID, Title, Genre, ReleaseYear, Price)
2 VALUES
3 (1, 'Grand Theft Auto V', 'Action', 2015, 29.99),
4 (2, 'Valorant', 'FPS', 2020, 0.00),
5 (3, 'Red Dead Redemption 2', 'Action', 2018, 59.99),
6 (4, 'Wukong', 'RPG', 2024, 49.99),
7 (5, 'Cyberpunk 2077', 'RPG', 2020, 39.99),
8 (6, 'Hades', 'Rogue', 2020, 24.99);

7. Create a Purchases table with the following columns and data types?

 PurchaseID as an integer and primary key

 UserID as an integer to identify the user making the purchase
 GameID as an integer and foreign key linking to the Games table
 PurchaseDate as a date to record the date of the purchase
 Quantity as an integer to indicate the number of copies purchased
 TotalAmount as a decimal with a precision of 10 and scale of 2 for the total amount
spent
 Establish a foreign key relationship between GameID in the Purchases table and
GameID in the Games table

Purchases table Query

1 CREATE TABLE Purchases (
2 PurchaseID INT PRIMARY KEY, -- Unique identifier for each purchase
3 UserID INT, -- Identifier for the user making the purchase
4 GameID INT, -- Foreign key linking to the Games table
5 PurchaseDate DATE, -- Date of the purchase
6 Quantity INT, -- Number of copies purchased
7 TotalAmount DECIMAL(10, 2), -- Total amount spent on the
8 purchase
9 FOREIGN KEY (GameID) REFERENCES Games(GameID) -- Establishing the
relationship
);

Insert data into the Purchases table

How would you insert the following 6 rows into the Purchases table with the columns
PurchaseID, UserID, GameID, PurchaseDate, Quantity, and TotalAmount?

QUERY
1 INSERT INTO Purchases (PurchaseID, UserID, GameID, PurchaseDate, Quantity,
2 TotalAmount)
3 VALUES
4 (1, 101, 1, '2024-06-15', 1, 29.99),
5 (2, 102, 2, '2024-07-22', 3, 0.00),
6 (3, 103, 3, '2024-08-10', 1, 59.99),
7 (4, 104, 4, '2024-09-05', 2, 99.98),
8 (5, 105, 5, '2024-10-01', 1, 39.99),
(6, 106, 6, '2024-10-05', 1, 24.99);

8. Create a Reviews table with the following columns and data types.

 ReviewID as an integer and primary key

 GameID as an integer and foreign key linking to the Games table
 UserID as an integer to identify the user who wrote the review
 ReviewDate as a date to record the date of the review
 Rating as an integer with a check constraint ensuring the value is between 1 and
10
 Comment as a text field for the review comment
 Establish a foreign key relationship between GameID in the Reviews table and
GameID in the Games table

Reviews table Query

1 CREATE TABLE Reviews (
2 ReviewID INT PRIMARY KEY, -- Unique identifier for each
3 review
4 GameID INT, -- Foreign key linking to the Games table
5 UserID INT, -- Identifier for the user who wrote the review
6 ReviewDate DATE, -- Date of the review
7 Rating INT CHECK (Rating BETWEEN 1 AND 10), -- Rating given by the user (1
8 to 10 scale)
9 Comment TEXT, -- Review comment
FOREIGN KEY (GameID) REFERENCES Games(GameID) -- Establishing the
relationship
);

How would you insert the following 6 rows into the Reviews table with the columns
ReviewID, GameID, UserID, ReviewDate, Rating, and Comment?

QUERY
1 INSERT INTO Reviews (ReviewID, GameID, UserID, ReviewDate, Rating, Comment)
2 VALUES
3 (1, 1, 101, '2024-06-16', 10, 'An epic open-world game with endless possibilities.'),
4 (2, 2, 102, '2024-07-23', 8, 'Fast-paced and fun, though it can get repetitive.'),
5 (3, 3, 103, '2024-08-11', 9, 'Stunning graphics and immersive story.'),
6 (4, 4, 104, '2024-09-06', 7, 'Promising game with interesting mechanics.'),
7 (5, 5, 105, '2024-10-02', 6, 'Great concept but has some bugs.'),
8 (6, 6, 106, '2024-10-06', 9, 'Fantastic rogue-like experience with excellent replay
value.')

9. Explain the types of SQL joins.

There are four different types of SQL Joins: (Inner) Join: An inner join is used to
retrieve the records that have matching values in tables involved in the join. It
combines rows from two tables based on a related column and returns only the
matching record. Inner Join is mostly used to join queries.

INNER JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID,p.PurchaseDate,
p.Quantity,p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
INNER JOIN Reviews r ON p.GameID = r.GameID AND p.UserID = r.UserID;

LEFT JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID, p.PurchaseDate,
p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
LEFT JOIN Reviews r ON p.GameID = r.GameID AND p.UserID = r.UserID;

RIGHT JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID, p.PurchaseDate,
p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
RIGHT JOIN Reviews ON p.GameID = r.GameID AND p.UserID = r.UserID;

FULL JOIN
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID, p.PurchaseDate,
p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
FULL JOIN Reviews r ON p.GameID = r.GameI ANp.UserID = r.UserID

CROSS JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID,p.GameID, p.PurchaseDate,
p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
CROSS JOIN Reviews r;

10. What are the uses of SQL?

SQL databases are your data powerhouse. Using them, you can do the following:

1. Create New Databases: It is like planting seeds for future growth

2. Insert Data: You can add new information to your database, like filling out forms.
3. Delete Data: Remove outdated information you no longer need. It’s cleaning
house.
4. Update Records: Modify existing data to keep it accurate and relevant.
5. Retrieve Data: Fetch relevant data you need; think of it as using a library’s
catalogue search.
6. Create/Drop Tables: Build or remove structures to organize your data; imagine
setting up or taking down shelves in your garage.
7. Create Functions/Views: Customize how you access your data; this is creating
shortcuts or custom views.
8. Convert Data Types: Transform data into a different format; think of changing a
recipe from metric to imperial.

11. What is the ACID property in a database?

When we talk about transactions, ACID here stands for Atomicity, Consistency,
Isolation and Durability which acts as a checklist to check on reliability.

 Atomicity: It means either the whole transaction succeeds or none of it will do. It is
like going to a departmental store and buying a complete range of items if one
item is missing, you do not buy anything at all.
 Consistency: It is about how well the data sticks to the rules. Just imagine ensuring
your recipe menu does all the steps correctly; in case any step is omitted or done
wrongly then you will not serve the dish.
 Isolation: Picture it as managing multiple transactions without interference. For
instance, in a busy kitchen where every chef has their own dish; no one will
interfere with someone else’s recipe.
 Durability: Look at it as how well the transaction can stand against interruptions.
You can think of this as having your message passed through despite harsh
weather, once sent always delivered regardless of external factors.

12. What is normalization and what are its types?

Data normalization is like tidying your house by arranging it into a city. This way,
every information bit has its own storage, and nothing is dependent on something
else except for when it’s necessary. Thus, we reduce chaos and ambiguity. It’s like
sort of putting all the clothes into different boxes so that you can easily find them
whenever you need them.

There are various types of Normalization which are discussed below:

In the First Normal Form (1NF), each data in your cell should be made up of only
indivisible values as if ensuring that every house in your city has a single owner with
no shared rooms or multiple keys.

The Second Normal Form (2NF) eliminates partial dependencies so that any non-key
attribute is fully functionally dependent on the primary key. This is the same as every
house in your city having one owner to avoid shared ownership.

The Third Normal Form (3NF) deals with transitive dependencies which means a non-
key attribute must not transitively depend on another non-key attribute. It’s just like
making sure house ownership in your city doesn’t rely on owning other things such as
another car.

BCNF is a stricter version of 3NF, where every functional dependency X -> Y, X is a

super key, thus, all the rules are clear, and it makes the organization smooth. It is like
a city plan to make sure that all the houses have a single owner who is the owner of
the house himself without any intermediaries.

13. What is the difference between DBMS and RDBMS?

A Database Management System is like a normal paper filing cabinet in that each
folder represents a data item. This behaves well when one is keeping a few pieces of
information in a simple way. When the data becomes too much or when one wants to
get a certain kind of information from the cabinet, it becomes hard to find. This
happens because the files are unlinked or are unstructured.

In the case of Relational Database Management Systems, they are just like modern
libraries that put books into tables with unique identification aspects. Therefore, any
kind of data can be linked or grouped together so that we can be able to tap into vast
amounts of information. Moreover, this helps us to understand the buried relations
between the data.

14. What are the applications of SQL?

Think of SQL as a toolbox for streamlining your information resources:

Parameters DBMS RDBMS
Access Data elements need to Multiple data elements can be accessed at
be accessed separately the same time
Relationship No relationship Data in tables are related to each other
Between Data between data
Normalization It is not present It is present
Distributed It does not support It supports distributed database
Database distributed databases.
Data Storage Data is stored in either Data is stored in a tabular structure with
Format a navigational or headers being the column names and the
hierarchical form rows containing the corresponding values
Amount of Data It deals with a small It deals with a larger amount of data
quantity of data
Data Redundancy It is prevalent Keys and indexes do not allow data
redundancy
Number of Users It supports a single- It supports multiple users
user
Data Fetching It is slower for large It is speedy due to the relational approach
amounts of data
Data Security Low-security levels Multiple levels of data security exist
when it comes to data
manipulation
Software and Low High
Hardware
Requirements
Examples XML, Window Registry, MySQL, SQL Server, Oracle, Microsoft
etc. Access, PostgreSQL, etc.
 Data Integration Scripts: Equally, when writing data integration scripts, think of
SQL as your personal data librarian. It helps you to combine various databases and
sources into one single output.
 Setting and Running Analytical Queries: SQL allows you to question your data like
an intelligent detective who can recognize patterns in the information provided.
 Retrieving Subsets of Information: For instance, it’s like a treasure map guiding you
to specific pieces of information you want whether for processing transactions or
analyzing while working with it.
 Adding, Updating, and Deleting Data: Using SQL is almost like reshaping and
cleaning up raw material with some simple commands. With the help of SQL,
inserting new records, modifying ones that already exist or removing some
irrelevant information is only a matter of seconds.

15. What are the subsets of SQL?

Depending on what you want to do, SQL queries come primarily in four flavours:

DDL (Data Definition Language): This is for defining the database structure.

1. CREATE: to construct databases, tables, and indexes.

2. DROP: to drop databases, tables, and views.
3. ALTER: change the table’s structure (e.g. adding or dropping columns).

DML (Data Manipulation Language): It is used to manipulate data in the database.

1. INSERT: For adding new records

2. UPDATE: To modify existing records
3. DELETE: Deleting of records
4. SELECT INTO: Copying data from one table into another

DCL (Data Control Language): These control user access rights and permissions.
1. GRANT: Assigns privileges to database objects.
2. REVOKE: Withdraws privileges granted earlier from a certain object in the
database.

TCL (Transaction Control Language): Basically, it consists of transaction

management and commands that are used in the manipulation of that information
within a given transaction.

1. COMMIT: To save changes permanently.

2. ROLLBACK: It undoes changes made during a transaction.
3. SAVEPOINT: This defines a point where the current transaction can be rolled back
too in case an error occurs.
4. SET TRANSACTION: This is used for setting up transaction characteristics.

Together they ensure efficient management, security and integrity of your database.

95% learner satisfaction score post completion of the program*

500% salary hike received by a working professional post completion of the course*

Fresher earned 30 LPA salary package on completion of the course*

53% of learners received 50% and above salary hike post completion of the program*

85% of the learners achieved their training objectives within 9 months of course
completion*

95% learner satisfaction score post completion of the program*

500% salary hike received by a working professional post completion of the course*

Fresher earned 30 LPA salary package on completion of the course*

53% of learners received 50% and above salary hike post completion of the program*

85% of the learners achieved their training objectives within 9 months of course
completion*

95% learner satisfaction score post completion of the program*

Process Advisors
*Subject to Terms and Condition

16. What is a DEFAULT constraint?

Imagine you’re setting up a game with rules, and default constraints in SQL are like
the default settings you establish before you start playing.

Defining Default Values: Think of default constraints as setting the starting point for a
game. You establish a default value for a column in a table, so if no other value is
specified when you add a new record, it automatically gets this default value.

Example: Let’s say you have a table for employee data, and you want to set a default
salary for new employees. You can add a default constraint to the salary column,
specifying that if no salary is provided, it defaults to a specific value, like $85,000.

Creating a Default Constraint: It’s like creating a rule for your game. You start by
creating a new table for your data. Then, when defining the structure of your table,
you add a default constraint to the column where you want to apply the rule. This
ensures that every time a new record is added to the table without specifying a value
for that column, it automatically follows the default rule you’ve set.

So, default constraints in SQL act as handy rules that ensure consistency in your data,
providing a default value unless you specify otherwise. It’s like having a safety net to
catch any missing information and keep your database organized.

17. What is a UNIQUE constraint?

Think of unique constraints as a lottery ticket. Just like we have different lottery
numbers for every ticket, in the same manner, in the column where the unique
constraint is applied, only unique values will be allowed.
A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However,
unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.

Intermediate SQL Interview Questions and Answers

18. What are SQL operators?

SQL operators are the special keywords or characters that perform specific
operations. They are also used in SQL queries. These operators can be used within the
WHERE clause of SQL commands. Based on the specified condition, SQL operators
filter the data.

The SQL operators can be categorized into the following types:

 Arithmetic Operators:For mathematical operations on numerical data

o addition (+)
o subtraction (-)
o multiplication (*)
o division (/)
o remainder/modulus (%)

 Logical Operators: For evaluating the expressions and returning results in True or
False

o ALL
o AND
o ANY
o ISNULL
o EXISTS
o BETWEEN
o IN
o LIKE
o NOT
o OR
o UNIQUE

 Comparison Operators: For comparisons of two values and checking whether they
are the same or not

o equal to (=)
o not equal to (!= or <>)
o less than (<),
o greater than (>;)
o less than or equal to (&<=)
o greater than or equal to (>=)
 o not less than (!<)
o not greater than (!>)

 Bitwise Operators: For bit manipulations between two expressions of integer type.
It first performs the conversion of integers into binary bits and then applied
operators

o AND (& symbol)

o OR (|, ^)
o NOT (~)

 Compound Operators: For operations on a variable before setting the variable’s

result to the operation’s result

o Add equals (+=)

o subtract equals (-=)
o multiply equals (*=)
o divide equals (/=)
o modulo equals (%=)

 String Operators: For concatenation and pattern matching of strings

o + (String concatenation)
o += (String concatenation assignment)
o % (Wildcard)
o [] (Character(s) matches)
o [^] (Character(s) not to match)
o _ (Wildcard match one character)

19. How can you handle exceptions in SQL Server?

TRY and CATCH blocks handle exceptions in SQL Server. Put the SQL statement in the
TRY block and write the code in the CATCH block to handle expectations. If there is an
error in the code in the TRY block, then the control will automatically move to the
CATCH block.

20. Explain the types of SQL joins.

There are four different types of SQL Joins: (Inner) Join: An inner join is used to
retrieve the records that have matching values in tables involved in the join. It
combines rows from two tables based on a related column and returns only the
matching record. Inner Join is mostly used to join queries.

INNER JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID,p.PurchaseDate,
 p.Quantity,p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
INNER JOIN Reviews r ON p.GameID = r.GameID AND p.UserID = r.UserID;

LEFT JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID, p.PurchaseDate,
2 p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
3 LEFT JOIN Reviews r ON p.GameID = r.GameID AND p.UserID =
4 r.UserID;<&#91;/code&#93;
5
6 <img class="alignnone size-full wp-image-313265"
7 src="https://intellipaat.com/blog/wp-content/uploads/2015/09/image020.jpg"
8 alt="image020" width="570" height="115" />
9
<b>RIGHT JOIN</b>

<b>QUERY</b>

[code language="sql"]SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID,

p.PurchaseDate, p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment
FROM Purchases p RIGHT JOIN Reviews ON p.GameID = r.GameID AND p.UserID =
r.UserID;

FULL JOIN
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID, p.PurchaseDate,
p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
FULL JOIN Reviews r ON p.GameID = r.GameI ANp.UserID = r.UserID;

CROSS JOIN

QUERY
1 SELECT p.PurchaseID, p.UserID AS PurchaserID,p.GameID, p.PurchaseDate,
p.Quantity, p.TotalAmount, r.ReviewID, r.Rating, r.Comment FROM Purchases p
 CROSS JOIN Reviews r;

21. Explain Inner Join with an example.

Inner Join gives us those records that have matching values in two tables.

INNER JOIN
1 SELECT p.PurchaseID, p.UserID AS PurchaserID, p.GameID,p.PurchaseDate,
2 p.Quantity,p.TotalAmount, r.ReviewID, r.Rating, r.Comment
3 FROM Purchases p INNER JOIN
4 Reviews r ON p.GameID = r.GameID
5 AND p.UserID = r.UserID;

22. What is an index?

The indexes in a database are like the index in a book. They are the ones to assist you
in getting the info at a fast pace. For example, take a library where there is no
catalog, you would have to take a long time to find a book. The index is a tool that
allows you to find books faster. Also, in the case of SQL, index creation is the reason
for faster searches by means of the index which is the roadmap to the relevant data.

Syntax:

CREATE INDEX idx_title ON Games (Title);

23. Explain the types of indexes.

Primary Index: Think of it as the main key to a building. It is unique and provides
entrance into all rooms. In the same way that a primary index in a database uniquely
identifies each row and speeds up searches.

Secondary Index: Imagine this like one departmental index in a noticeably big library.
Whereas the primary index leads you to the book, the secondary index helps narrow
down your search within specific sections, so you find it quicker.
Clustered Index: Picture this as organizing books on shelves with alphabetical titles.
Consequently, it organizes data physically in a table that matches the sequence of the
index thereby facilitating easy retrieval just like finding them on shelves well
arranged.

Non-Clustered Index: Just like card catalogues, a clustered index sorts out data on the
shelf while a non-clustered index gives another lookup for some information that can
be narrowed down further for ease in searching.

24. What are entities and relationships?

Visualize a database as a digital world where entities are the characters and
relationships are the connections between them.

Entities: Visualize entities as the characters in a novel, each one representing an

individual, a location, or an item that keeps data. For instance, when considering
employees, projects, and wages in company databases they appear as separate
entities just like in any novel story.

Relationships: Relationships are contacts that help to achieve this interconnection of

the characters as it is done in stories. For example, within university information
systems, the student entity is linked with the department entity because there is an
association among them; it could be representative of various departments attended
by students.

So, for example with regards to the database world, you can say that all casts
involved are these entities, and their relationships complete plot lines creating a
narrative out of data.

25. What do you mean by data integrity?

Data integrity is like a loyal protector, guaranteeing data accuracy and consistency. It
is there from the creation to deletion, like a lifelong companion. Integrity constraints
are its rules that make sure that data is following the guidelines. It is your faithful
friend, guaranteeing your data is correct and dependable.

26. How would you find the second highest priced game from the Games
table?

QUERY
1 select * from Games
2 select max(Price) from Games
3 where Price not in (select max(Price)
4 from Games)

Output –
27. Why is the FLOOR function used in SQL Server?

The FLOOR() function helps to find the largest integer value for a given number, which
can be an equal or lesser number.

28. State the differences between clustered and non-clustered indexes

 Clustered Index: It is used to sort the rows of data by their key values. A clustered
index is like the contents of a phone book. We can directly open the book on
David’s index (for “David, Thompson”) and find information for all Davids right next
to each other. Since the data are located next to each other, it helps a lot in
fetching the data based on range-based queries. A clustered index is actually
related to how the data is stored; only one clustered index is possible per table.
 Non-Clustered Index: It stores data at one location and indexes at another location.
The index has pointers that point to the location of the data. As the indexes in a
non-clustered index are stored in a different place, there can be many non-
clustered indexes for a table.

State the differences between the Clustered and Non-clustered indexesState the
differences between the Clustered and Non-clustered indexes

Now, we will see the major differences between clustered and non-clustered indexes:
Parameters Clustered Index Non-Clustered Index
Used For Sorting and storing records Creating a logical order for data rows;
physically in memory pointers are used for physical data files
Methods for Stores data in the leaf nodes Never stores data in the leaf nodes of the
Storing of the index index
Size Quite large Comparatively, small
Data Fast Slow
Accessing
Additional Not required Required to store indexes separately
Disk Space
Type of Key By default, the primary key of It can be used with the unique constraint
a table is a clustered index on the table that acts as a composite key
Main Feature Improves the performance of Should be created on columns used in
data retrieval Joins

Clustered Index Example:

Create clustered index ci_gameid on games(gameid)

Non Clustered Index Example:

Create nonclustered index ci_title on games(Title)

29. What is a “TRIGGER” in SQL?

Triggers are the silent guardians of the database that are automatically fired upon the
occurrence of an event such as data insertion, updating, or deletion. They are
protective keepers who enforce the rules and do not tolerate any unauthorized
changes likely to alter data illegally. The triggers are sentinels with an invisible break
that protects the treasure.

The syntax used to generate the trigger function is as follows:

Create trigger tr_persons_insteadof_insert on games

after insert

as

begin

print ‘You have inserted the data in the table’

End

30. What is a data warehouse?

A data warehouse is a large store of accumulated data, from a wide range of sources,
within an organization. The data helps drive business decisions.

31. What do you know about CDC in SQL Server?

CDC refers to change data capture. It captures recent INSERT, DELETE, and UPDATE
activity applied to SQL Server tables. It records changes to SQL Server tables in a
compatible format.

32. What is the difference between SQL and MySQL?

Now Let’s compare the difference between SQL and MySQL.

SQL MySQL
It is a structured query language It is a database management system
used in a database
It is used for query and operating It allows data handling, storing, and modification
database systems, in an organized manner
It is always the same It keeps updating
It supports only a single storage It supports multiple storage engines
engine
The server is independent During backup sessions, the server blocks the
database

33. State the differences between SQL and PL/SQL

SQL PL/SQL
It is a database-structured query language It is a programming language for a
database that uses SQL
It is an individual query that is used to It is a block of codes used to write the
execute DML and DDL commands entire procedure or a function
It is a declarative and data-oriented It is a procedural and application-
language oriented language
It is mainly used for data manipulation It is used for creating applications
It provides interaction with the database It does not provide interaction with the
server database server
It cannot contain PL/SQL code It can contain SQL because it is an
extension of SQL

34. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each group.
COUNT(), MAX(), MIN(), SUM(), AVG(), and STDEV() are some of the most widely used
group functions.
1 select COUNT(*) as count_of_games from Games

1 select min(Price) as min_price from Games

1 select max(Price) as max_price from Games

1 select sum(Price) as sum_price from Games

1 select avg(Price) as avg_price from Games

1 select STDEV(Price) as variance_price from Games

35. What do you understand about a character manipulation function?

Character manipulation functions are used for the manipulation of character data
types.
Some of the character manipulation functions are as follows:

UPPER: It returns the string in uppercase.

select UPPER(Title) as upper_title from Games

LOWER: It returns the string in lowercase.

select lower(Title) as lower_title from Games

CONCAT: It is used to concatenate two strings.

select CONCAT(Title,Genre) as concat_title_genere from Games

LENGTH: It is used to get the length of a string.

select len(Title) as length from Games

36. What is Identity?

Identity is used in SQL to automatically generate a unique number whenever a new

record is inserted into a table.

Since the primary key is unique for each record, this primary field is added as the
Identity field so that it is incremented when a new record is inserted.

The identity value starts at 1 and is incremented by 1 whenever a new record is

inserted.

Syntax:
1 CREATE TABLE Games (
2 GameID INT identity,
3 Title VARCHAR(100) NOT NULL,
4 Genre VARCHAR(50),
5 ReleaseYear INT,
6 Price DECIMAL(10, 2)
7 primary key(gameid)
8 );

37. What are the types of relationships in SQL Server databases?

Relationships are developed by interlinking the columns of one table with the column
of another table. There are three different types of relationships, which are as follows:

 One-to-one relationship
 Many-to-one relationship
 Many-to-many relationship

SQL Advanced Interview Questions for Experienced

38. Which command is used to find out the SQL Server version?

The following command is used to identify the version of SQL Server:

Select SERVERPROPERTY(‘productversion’) as version

39. What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null value.

SELECT COALESCE(NULL, 1, 2, ‘MYSQL’) as coalesce_example

40. What do you know about magic tables in SQL Server?

A magic table can be defined as a provisional logical table that is developed by an

SQL Server for tasks such as insert, delete, or update (DML) operations. The
operations recently performed on the rows are automatically stored in magic tables.
Magic tables are not physical tables; they are just temporary internal tables.

41. What are the types of views in SQL?

In SQL, the views are classified into four types. They are the following:

 Simple View: It is a view based on a single table and does not have a GROUP BY
clause or other features.
 Complex View: It is a view built from several tables and includes a GROUP BY
clause as well as functions.
 Inline View: It is a view built on a subquery in the FROM clause, which provides a
temporary table and simplifies a complicated query.
 Materialized View: It is a view that saves both the definition and the details. It
builds data replicas by physically preserving them.

CREATE VIEW SimpleView AS

SELECT GameID, Title, Genre, ReleaseYear, Price

FROM Games;

CREATE VIEW ComplexView AS

SELECT Genre, COUNT(*) AS NumberOfGames, AVG(Price) AS AveragePrice

FROM Games

GROUP BY Genre;

SELECT GameID, Title, Genre, Price

FROM (SELECT GameID, Title, Genre, Price FROM Games WHERE Price > 50) AS
InlineView;
CREATE VIEW MaterializedView WITH SCHEMABINDING AS

SELECT GameID, Title, Genre, ReleaseYear, Price

FROM dbo.Games;

— Create a unique clustered index on the view to materialize it

CREATE UNIQUE CLUSTERED INDEX IX_MaterializedView ON MaterializedView

(GameID);
SQL Interview Questions for 3 Years Experienced

42. How many authentication modes are there in SQL Server? What are
they?

Two authentication modes are available in SQL Server. They are as follows:

 Windows Authentication Mode: It allows authentication for Windows but not for SQL
Server.
 Mixed Mode: It allows both types of authentication—Windows and SQL Server.

43. What is a function in SQL Server?

A function is an SQL Server database object. It is basically a set of SQL statements

that allow input parameters, perform processing, and return results only. A function
can only return a single value or table. The ability to insert, update, and delete
records in database tables is not available.
1 CREATE FUNCTION dbo.fn_CalculateDiscountedPrice
2 (
3 @Price DECIMAL(10, 2),
4 @DiscountPercentage DECIMAL(5, 2)
5 )
6 RETURNS DECIMAL(10, 2)
7 AS
8 BEGIN
9 DECLARE @DiscountedPrice DECIMAL(10, 2);
1 -- Calculate the discounted price
0 SET @DiscountedPrice = @Price - (@Price * @DiscountPercentage / 100);
1 -- Return the result
1 RETURN @DiscountedPrice;
1 END;
2 SELECT GameID, Title, Genre, ReleaseYear, Price,
1 dbo.fn_CalculateDiscountedPrice(Price, 10) AS DiscountedPrice FROM Games;
3
1
4
1
5
44. What is SQL Server Agent?

SQL Server Agent plays an important role in the daily work of SQL Server
administrators or DBAs. This is one of the important parts of SQL Server. The aim of
the server agent is to easily implement tasks using a scheduler engine that enables
the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to
store scheduled management task information.

45. What are views? Give an example.

Views are virtual tables used to limit the tables that we want to display. Views are
nothing but the result of an SQL statement that has a name associated with it. Since
views are not physically present, they take less space to store.
Let us consider an example. In the following employee table, say we want to perform
multiple operations on the records with the gender “Female”. We can create a view-
only table for the female employees from the entire employee table.

Now, let us implement it on SQL Server.

CREATE VIEW SimpleView AS

SELECT GameID, Title, Genre, ReleaseYear, Price

FROM Games;

46. State the differences between views and tables.

Views Tables
A view is a virtual table that is A table is structured with a set number of
extracted from a database. columns and a boundless number of rows.
A view does not hold the data itself. A table contains data and stores it in
databases.
A view is utilized to query certain A table holds fundamental client information
information contained in a few distinct and cases of a characterized object.
tables.
In a view, we will get frequently In a table, changing the information in the
queried information. database changes the information that appears
in the view.
47. What do you understand by Self Join? Explain using an example

Self Join in SQL is used for joining a table with itself. Here, depending on some
conditions, each row of the table is joined with itself and with other rows of the table.
1 SELECT g1.Title AS Game1, g2.Title AS Game2
2 FROM Games g1, Games g2
3 WHERE g1.Genre = g2.Genre AND g1.GameID <> g2.GameID;

48. What is the difference between Union and Union All operators?

The union operator is used to combine the result set of two or more select
statements. For example, the first select statement returns the fish shown in Image A,
and the second statement returns the fish shown in Image B. The Union operator will
then return the result of the two select statements as shown in Image A U B. If there is
a record present in both tables, then we will get only one of them in the final result.
SELECT Title AS Name, NULL AS PurchaseDate

FROM Games

UNION

SELECT NULL AS Name, PurchaseDate

FROM Purchases;

49. Can you identify the employee who has the third-highest salary from the
given employee table (with salary-related data)?
Below is a simple query to find out which employee has the third-highest salary. The
functions RANK, DENSE RANK, and ROW NUMBER are used to obtain the increasing
integer value by imposing the ORDER BY clause in the SELECT statement, based on
the ordering of the rows. The ORDER BY clause is necessary when the RANK, DENSE
RANK, or ROW NUMBER functions are used. On the other hand, the PARTITION BY
clause is optional.
1 WITH RankedGames AS (
2 SELECT
3 GameID,
4 Title,
5 Price,
6 ROW_NUMBER() OVER (ORDER BY Price DESC) AS PriceRank
7 FROM
8 Games
9 )
1 SELECT
0 GameID,
1 Title,
1 Price
1 FROM
2 RankedGames
1 WHERE
3 PriceRank = 3;
1
4
1
5
1
6
1
7
50. How would you find the second-highest price in a table?

SELECT MAX(Price) AS SecondHighestPrice

FROM Games
WHERE Price < (SELECT MAX(Price) FROM Games); [/code]

Explanation:

 SELECT MAX(Price): This selects the maximum salary from the filtered results.
 WHERE Salary < (SELECT MAX(Salary) FROM Games): The subquery selects
the highest salary in the Games table, and the outer query finds the maximum
salary that is less than this highest salary.

51. What is an effective way to prevent SQL injection in your queries?

The effective way to prevent SQL injection attacks is through input validation and
parameterized queries, which include prepared statements. The developer can
sanitize all the inputs, not only just the web form input because the application code
should never use the input directly.

52. What is the significance of an index in a database, and how do you use
it?

Database indexing helps the DBMS find the specific rows in a table very quickly. The
most common database index is the B-tree Index. A B-tree index is a situation where
the data has few distinct values, such as name, dates or state codes.
53. What is the significance of transactions, and how do you ensure their
consistency?

Transactions help to ensure the data consistency and integrity of the data; they also
protect against errors by grouping many actions into a single unit.

Transaction funds from one account to another are the best example of ensuring data
consistency; the total value of the funds in both accounts is the same at the start and
end of each transaction.

54. How will you optimize a slow-moving SQL query? What are some of the
optimization techniques?

We can optimize a slow-moving SQL query by using indexing in the DBMS to find the
specific rows in a table very quickly.

There are several optimization techniques:

1. Indexing
2. Using Distinct
3. Having and Where clauses
4. Avoiding correlated subqueries
5. Limit
6. Column statistics

55. How do you optimize a slow performing query?

To optimize a slow-performing query, follow these key strategies:

1. Analyze Execution Plan: Use tools like EXPLAIN to understand how the database
executes the query and identify bottlenecks.
2. Simplify the Query: Remove unnecessary complexity and select only the
columns you need instead of using SELECT *.
3. Use Indexes: Create indexes on columns frequently used in WHERE clauses or
joins to speed up data retrieval. Consider composite indexes for multi-column
queries.
4. Optimize Joins: Ensure joins are done on indexed columns and use efficient join
types.
5. Limit Results: Use LIMIT or pagination to manage large result sets efficiently.
6. Tune Database Configuration: Adjust database settings and update statistics to
better align with your workload.
7. Regular Maintenance: Periodically rebuild indexes and clean up old data to
maintain optimal performance.

By applying these techniques, you can enhance your query performance and ensure
your database operates more efficiently.
56. How do you EXPLAIN to analyze a query?

Here’s a concise guide to analyzing queries in SQL Server:

1. Use SET SHOWPLAN:

o Enable: SET SHOWPLAN_ALL ON;

o Run Query: SELECT * FROM Orders WHERE CustomerID = 123;
o Disable: SET SHOWPLAN_ALL OFF;

2. View Actual Execution Plan in SSMS:

o Enable: Click “Include Actual Execution Plan” or press Ctrl + M.

o Run Query: SELECT * FROM Orders WHERE CustomerID = 123;
o Review Plan: Check the “Execution Plan” tab.

3. Utilize SQL Server Profiler and Database Engine Tuning Advisor:

o Profiler: Capture and analyze query performance.

o Tuning Advisor: Get index and optimization recommendations.

4. Get Execution Statistics:

o Use: SET STATISTICS IO ON; SET STATISTICS TIME ON;

o Run Query: SELECT * FROM Orders WHERE CustomerID = 123;
o Review Stats: Check I/O and timing details.

These methods will help you diagnose and improve query performance effectively.

57. How do you use window functions (row number,rank etc)?

1. ROW_NUMBER()

Purpose: Assigns a unique sequential integer to rows within a result set.

Example: Assign a unique row number to each game based on its release year within
each genre

SELECT

GameID,

Title,

Genre,

ReleaseYear,

Price,
 ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY ReleaseYear DESC) AS
RowNum

FROM Games;

Explanation:

 This query numbers games within each genre, starting with the most recent
release.

2. RANK()

Purpose: Assigns a rank to rows within a partition, with gaps in ranking for ties.

Example: Rank games by price within each genre.

SELECT

GameID,

Title,

Genre,

ReleaseYear,

Price,

RANK() OVER (PARTITION BY Genre ORDER BY Price DESC) AS PriceRank

FROM Games;

Explanation:

 This query ranks games within each genre by their price, with ties receiving the
same rank and leaving gaps in the sequence.

3. DENSE_RANK()

Purpose: Similar to RANK(), but without gaps for ties.

Example: Rank games by price within each genre without gaps.

SELECT

GameID,

Title,

Genre,
 ReleaseYear,

Price,

DENSE_RANK() OVER (PARTITION BY Genre ORDER BY Price DESC) AS

DensePriceRank

FROM Games;

Explanation:

 This query ranks games within each genre by price, with no gaps in the ranking for
ties.

4. NTILE()

Purpose: Divides the result set into a specified number of roughly equal parts.

Example: Divide games into quartiles based on their price.

SELECT

GameID,

Title,

Genre,

ReleaseYear,

Price,

NTILE(4) OVER (ORDER BY Price DESC) AS PriceQuartile

FROM Games;

Explanation:

 This query divides all games into 4 quartiles based on their price, with each
quartile containing roughly the same number of games.

5. LAG()

Purpose: Provides access to data from a row at a specified offset before the current
row.

Example: Get the price of the previous game in the order of release dates.

SELECT

GameID,
 Title,

Genre,

ReleaseYear,

Price,

LAG(Price, 1, NULL) OVER (ORDER BY ReleaseYear) AS PreviousPrice

FROM Games;

Explanation:

 This query shows the price of the previous game based on release year. If no
previous game exists, it defaults to NULL.

6. LEAD()

Purpose: Provides access to data from a row at a specified offset after the current
row.

Example: Get the price of the next game in the order of release dates.

SELECT

GameID,

Title,

Genre,

ReleaseYear,

Price,

LEAD(Price, 1, NULL) OVER (ORDER BY ReleaseYear) AS NextPrice

FROM Games;

Explanation:

 This query shows the price of the next game based on release year. If no next
game exists, it defaults to NULL.

These examples demonstrate how to leverage window functions to analyze and rank
data within the Games table.

58. What is denormalization and when is it acceptable?

Denormalization is the process of intentionally introducing redundancy into a
database design by merging tables or duplicating data to improve query performance
and simplify certain operations. This is done after normalization to a certain extent,
which aims to minimize redundancy and dependency. Denormalization can make
certain queries faster by reducing the need for complex joins and aggregations, but it
can also lead to increased data redundancy and potential inconsistencies.

When is Denormalization Acceptable?

Denormalization is considered acceptable in the following scenarios:

1. Performance Optimization:

o Complex Queries: When a database has complex queries involving multiple

joins and aggregations, denormalization can reduce the need for these
operations by consolidating related data into fewer tables.
o Read-Heavy Workloads: In environments where read operations
significantly outnumber write operations, denormalization can improve query
performance by reducing the number of joins required.

2. Reporting and Data Warehousing:

o Data Warehouses: Denormalization is commonly used in data warehousing

and OLAP (Online Analytical Processing) systems to optimize query
performance and simplify reporting. Star schemas and snowflake schemas
are examples of denormalized designs used for efficient data retrieval.
o Aggregated Data: For reporting purposes, denormalized tables may store
pre-aggregated or summarized data to speed up report generation.

3. Simplified Application Logic:

o Application Performance: When the application requires frequent access

to combined or pre-aggregated data, denormalization can simplify
application logic and reduce the number of database calls needed.

4. Historical Data and Archiving:

o Historical Records: Denormalized tables may be used to store historical

data or logs in a way that improves retrieval performance for historical
queries.

Trade-offs and Considerations

1. Increased Data Redundancy:

o Data Consistency: Denormalization can lead to data redundancy, which

increases the risk of inconsistencies. Updating the same data in multiple
places can become complex and error-prone.
2. Maintenance Complexity:

o Data Integrity: Maintaining data integrity in denormalized databases

requires careful design and implementation of update mechanisms, such as
triggers or application logic.

3. Storage Overhead:

o Increased Storage: Redundant data means increased storage

requirements, which can be a concern in environments with large volumes of
data.

Best Practices for Denormalization

 Evaluate Query Patterns: Perform denormalization based on specific query

performance needs rather than applying it indiscriminately.
 Balance: Find a balance between normalization and denormalization to ensure
efficient data access while minimizing redundancy.
 Monitor and Adjust: Continuously monitor database performance and adjust
denormalization strategies as necessary to address evolving requirements and
performance issues.

In summary, denormalization is acceptable when it provides clear benefits in terms of

query performance, application simplicity, or reporting efficiency, but it should be
approached with an understanding of the potential trade-offs and careful
consideration of the overall database design.

59. How to design a database for scalability?

For designing a scalable database in Microsoft SQL Server (MSSQL), here

are the key points:

1. Normalize Data:

 Start with normalization to reduce redundancy and improve data

integrity.
 Use third normal form (3NF) as a baseline but consider denormalization
for performance improvements if needed.

2. Use Indexes Wisely:

 Create Indexes: Add indexes on columns frequently used in queries,

filters, and joins.
  Monitor and Optimize: Use the SQL Server Management Studio (SSMS)
Index Tuning Wizard and the Database Engine Tuning Advisor to
optimize indexing.

3. Optimize Queries:

 Write Efficient Queries: Use best practices to avoid complex joins and
full table scans.
 Analyze Query Plans: Use the Actual Execution Plan (press Ctrl + M in
SSMS) to examine how queries are executed and identify potential
bottlenecks.

4. Implement Partitioning:

 Table Partitioning: Use SQL Server’s partitioning feature to split large

tables into manageable pieces based on ranges (e.g., date ranges) or
list values.
 Index Partitioning: Apply partitioning to indexes for better performance.

5. Employ Data Sharding:

 Horizontal Scaling: Distribute data across multiple SQL Server

instances or databases. Implement sharding in application logic or use
distributed databases if supported.

6. Use Caching:

 Query Caching: Utilize SQL Server’s built-in caching mechanisms to

speed up frequent queries.
 Application-Level Caching: Implement caching solutions like Redis or
Memcached for frequently accessed data.

7. Implement Read Replicas:

 Replication: Set up SQL Server Replication (Transactional, Merge, or

Snapshot) to create read replicas that handle read queries and reduce
load on the primary database.
 Always On Availability Groups: Use Always On Availability Groups for
high availability and read scaling.

8. Design for High Availability:

 Failover Clustering: Use SQL Server Failover Clustering for high

availability with automatic failover capabilities.
 Backups: Regularly perform backups and implement a robust recovery
plan using SQL Server Backup and Restore features.
 9. Optimize Database Configuration:

 Tune Settings: Adjust SQL Server configuration settings (e.g., max

memory, max degree of parallelism) to match workload requirements.
 Monitor Performance: Use SQL Server Performance Monitor and DMVs
(Dynamic Management Views) to track and optimize performance.

10. Plan for Future Growth:

 Scalability Testing: Perform stress testing and capacity planning to

evaluate how your database handles increased loads.
 Scalable Architecture: Consider SQL Server’s cloud-based solutions
(e.g., Azure SQL Database) or distributed databases for future growth.

These steps focus on utilizing SQL Server’s specific features and tools to ensure your
database design can scale effectively.

60. Write a query to pivot data from rows to columns

To pivot data from rows to columns in SQL Server, you can use the PIVOT operator.
For the Games table, let’s say we want to pivot the Price of games to show the
average price of games for each Genre per ReleaseYear.

QUERY
1 SELECT
2 ReleaseYear,
3 Genre,
4 AVG(Price) AS AvgPrice
5 FROM Games
6 GROUP BY ReleaseYear, Genre;
7
8
9 -- Step 2: Pivot data
1 SELECT
0 ReleaseYear,
1 ISNULL([Action], 0) AS Action,
1 ISNULL([RPG], 0) AS RPG,
1 ISNULL([FPS], 0) AS FPS
2 -- Add more genres as needed
1 FROM
3 (SELECT
1 ReleaseYear,
4 Genre,
1 AVG(Price) AS AvgPrice
5 FROM Games
1 GROUP BY ReleaseYear, Genre) AS SourceTable
6 PIVOT
1 (SUM(AvgPrice)
7 FOR Genre IN ([Action], [RPG], [FPS])) AS PivotTable;
1
8
1
9
2
0
2
1
2
2
2
3
2
4
2
5

NOTE!

Replace [Action], [RPG], and [FPS] with the actual genre names you have in your
table.
If you have a dynamic set of genres, you may need to use dynamic SQL to generate
the pivot columns dynamically.

SQL Interview Questions for 5 Years Experienced

61. What is wrong with the following SQL query?

1 SELECT genre, AVG(price) FROM games WHERE AVG(price)&amp;>30 GROUP
BY genre

When this command is executed, it gives the following error:

1 Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery contained
in the HAVING clause or a select list; the column being aggregated is an outer
reference.
1 Msg 147, Level 16, State 1, Line 1
2 Invalid column name ‘genre’.

This means that whenever we work with aggregate functions and use the GROUP BY
clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we
should use the HAVING clause.
When we use the HAVING clause, the GROUP BY clause should come first, followed by
the HAVING clause.
1 SELECT Genre, AVG(Price) AS AveragePrice
2 FROM Games
3 GROUP BY Genre
4 HAVING AVG(Price) > 30;
62. What do you know about the stuff() function?

The stuff() function deletes a part of the string and then inserts another part into the
string, starting at a specified position.
Syntax:
1 STUFF(String1, Position, Length, String2)[/code
2
3 Here, String1 is the one that will be overwritten. The position indicates the starting
4 location for overwriting the string. Length is the length of the substitute string,
5 and String2 is the string that will overwrite String1.
6
7 <strong>Example:</strong>
[code language="sql"]
select stuff(‘SQL Tutorial’,1,3,’Python’)

This will change ‘SQL Tutorial’ to ‘Python Tutorial’

Output:
1 Python Tutorial

63. What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In other
words, we can consider a stored procedure to be a function consisting of many SQL
statements to access the database system. We can consolidate several SQL
statements into a stored procedure and execute them whenever and wherever
required.
A stored procedure can be used as a means of modular programming, i.e., we can
create a stored procedure once, store it, and call it multiple times as required. This
also supports faster execution when compared to executing multiple queries.
Syntax:
1 CREATE PROCEDURE procedure_name
2 AS
3 Sql_statement
4 GO;
5 To execute we will use this:
6 EXEC procedure_name

Example:

We are going to create a stored procedure that will help us extract the age of the
employees.
1 CREATE PROCEDURE GetGameTitlesAndPrices
2 AS
3 BEGIN
4 SELECT Title, Price
5 FROM Games;
6 END;
7 GO

Now, we will execute it.

1 exec GetGameTitlesAndPrices

Output:
64. What do you understand about a temporary table? Write a query to
create a temporary table

A temporary table helps us store and process intermediate results. Temporary tables
are created and can be automatically deleted when they are no longer used. They are
very useful in places where temporary data needs to be stored.
Syntax:
1 -- Create a temporary table with the same structure as the Games table
2 CREATE TABLE #TempGames (
3 GameID INT,
4 Title VARCHAR(100),
5 Genre VARCHAR(50),
6 ReleaseYear INT,
7 Price DECIMAL(10, 2)
8 );
9 -- Insert records into the temporary table
1 INSERT INTO #TempGames (GameID, Title, Genre, ReleaseYear, Price)
0 VALUES (1, 'Grand Theft Auto V', 'Action', 2015, 29.99),
1 (2, 'Valorant', 'FPS', 2020, 0.00),
1 (3, 'Red Dead Redemption 2', 'Action', 2018, 59.99);
1 -- Select all records from the temporary table
2 SELECT * FROM #TempGames;
1 - Drop the temporary table when done (optional, as it will be dropped
3 automatically at the end of the session)DROP TABLE #TempGames;
1
4
1
5
1
6

Output:
65. What is a database cursor? How to use a database cursor?

A database cursor is a control that allows you to navigate around a table’s rows or
documents. It can be referred to as a pointer for a row in a set of rows. Cursors are
extremely useful for database traversal operations such as extraction, insertion, and
elimination.

 After any variable declaration, DECLARE a cursor. A SELECT statement must always
be aligned with the cursor declaration.
 To initialize the result set, OPEN statements must be called before fetching the
rows from the result table.
 To grab and switch to the next row in the result set, use the FETCH statement.
 To deactivate the cursor, use the CLOSE expression.
 Finally, use the DEALLOCATE clause to uninstall the cursor description and clear all
the resources associated with it.

1 Here is an example SQL cursor:

 2 -- Declare a variable for the cursor
3 DECLARE @Title VARCHAR(100);
4 -- Declare the cursor for selecting game titles with prices greater than 30
5 DECLARE db_cursor CURSOR FOR
6 SELECT Title
7 FROM Games
8 WHERE Price > 30; -- Example condition, adjust as needed
9 -- Open the cursor
1 OPEN db_cursor;
0 -- Fetch the next row from the cursor into the variable
1 FETCH NEXT FROM db_cursor INTO @Title;
1 -- Loop through the cursor and print each title
1 WHILE @@FETCH_STATUS = 0
2 BEGIN
1 PRINT @Title; -- You can replace this with any processing logic you need --
3 Fetch the next row from the cursor
1 FETCH NEXT FROM db_cursor INTO @Title;
4 END;
1 -- Close and deallocate the cursor
5 CLOSE db_cursor;
1 DEALLOCATE db_cursor;
6
1
7
1
8
1
9
2
0
2
1

Explanation:

 Variable Declaration:

o DECLARE @Title VARCHAR(100);: Declares a variable to hold the title from

each row fetched by the cursor.

 Cursor Declaration:

o DECLARE db_cursor CURSOR FOR …: Defines a cursor to select game titles

from the Games table where the price is greater than 30. You can adjust the
condition as needed.

 Cursor Operations:

o OPEN db_cursor;: Opens the cursor.

o FETCH NEXT FROM db_cursor INTO @Title;: Fetches the first row and stores
the Title in the @Title variable.
 o WHILE @@FETCH_STATUS = 0 BEGIN … END;: Loops through all rows
returned by the cursor until there are no more rows.
o PRINT @Title;: Prints the game title (or replace this with other logic if
needed).

 Close and Deallocate:

o CLOSE db_cursor;: Closes the cursor after processing.

o DEALLOCATE db_cursor;: Deallocates the cursor to free up resources.

66. What is the use of the INTERSECT operator?

The INTERSECT operator helps combine two select statements and returns only those
records that are common to both select statements. After we get Table A and Table B
over here, and if we apply the INTERSECT operator on these two tables, then we will
get only those records that are common to the result of the select statements of these
two tables.

1 Syntax:
2 SELECT column_list FROM table1
3 INTERSECT
4 SELECT column_list FROM table2
5 Now, let us take a look at an example of the INTERSECT operator.
6 -- Query to get games from the Games table
7 SELECT GameID, Title FROM Games
8 INTERSECT
9 -- Query to get purchased games from the Purchases table
1 SELECT GameID, Title FROM Games1
0
67. Describe how to delete duplicate rows using a single statement but
without any table creation.

Let us use the Games table where the column names are GameID, TITLE, GENRE, and
RELEASE_YEAR, PRICE.
Adding some duplicate records in the table –
1 INSERT INTO Games (GameID, Title, Genre, ReleaseYear, Price) VALUES
2 (7, 'Grand Theft Auto V', 'Action', 2015, 29.99), (8, 'Valorant', 'FPS', 2020, 0.00)

We can see the duplicate rows in the above table.

1 DELETE g1 FROM Games g1, Games g2 WHERE g1.title =g2.title AND g1.gameid
>g2.gameid

The SQL query above will delete the rows, where the name fields are duplicated, and
it will retain only those unique rows in which the names are unique and the GameID
fields are the lowest, i.e., the rows with GameIDs 7 and 8 are deleted, while the rows
with GameIDs 1 and 2 are retained.
68. Explain database white box testing and black box testing.

The white box testing method mainly deals with the internal structure of a particular
database, where users hide specification details. The white box testing method
involves the following:

 As the coding error can be detected by testing the white box, it can eliminate
internal errors.
 To check for the consistency of the database, it selects the default table values.
 This method verifies the referential integrity rule.
 It helps perform the module testing of database functions, triggers, views, and SQL
queries.

The black box testing method generally involves interface testing and database
integration. The black box testing method involves the following:

 Mapping details
 Verification of incoming data
 Verification of outgoing data from the other query functions

69. What is Blocking and Troubleshooting?

Blocking: Blocking occurs when one session holds a lock on a specific resource and a
second SPID attempts to acquire a conflicting lock type on the same resource.

Troubleshooting: To start the troubleshooting, you first have to define the symptoms.
Troubleshooting starts with identifying the biggest CPU resource users. The most
common SQL server performance symptoms are CPU, memory, network, and slow-
running queries.

70. What is an Optimal Disk Configuration for a server?

Optimal Disk Configuration involves strategically organizing and using storage

resources on a server, which helps us achieve the best performance and reliability for
a specific workload. The main aim of optimal disk configuration is to minimize
bottlenecks and ensure efficient support for the read and write demands of the
database.

71. What is a Deadlock or a live Deadlock, and how do you resolve it?

A deadlock is a situation where a set of processes are blocked because each process
is holding the resource and waiting for the other resource. A live deadlock is just like a
deadlock-like situation where the processes block each other with a repeated state
change yet make no progress.

There are several ways to prevent a deadlock or live deadlock situation:

1. Acquired multiple locks for a thread.

2. Abort and restart the process.
3. Timeouts
4. Transaction Rollback

72. What are statistics in SQL, and how do you update them?

Statistics in SQL help us compute the standard statistics, which help us execute the
SQL queries more efficiently. The statistics will help us understand the total structure
of the data. There are various functions that we can use in statistics, such as MEAN,
MAX, MIN, MEDIAN, MODE, and Standard Deviation, and we can also use inferential
statistics in SQL, like t-test, f-test, ANOVA, and analytics functions.

Updating statistics for a specific table

1. Go to the menu of the table and choose Definition.

2. Open the Optimizer Statistics tab page.
3. Choose the Update option in the context menu of the Table Statistics field.
4. Define the sample type and size that you want to use to generate the statistics.

73. What is an efficient structure to speed up the table reads?

With the help of Database Indexing we can improve the speed of the table read.

Database indexing helps DBMS find the specific rows in a table very quickly. The most
common database index is the B-tree Index.that has few distinct values, such as
name, dates, or state codes.

SQL Technical Interview Questions

74. What is the difference between the DELETE and TRUNCATE commands?

 DELETE:This query is used to delete or remove one or more existing tables.

 TRUNCATE:This statement deletes all the data inside a table.

The differences between DELETE and TRUNCATE commands are the following:

 TRUNCATE is a DDL command, and DELETE is a DML command.

 With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can
accomplish a trigger.
 If a table is referenced by foreign key constraints, then TRUNCATE will not work.
So, if we have a foreign key, we have to use the DELETE command.

Example of Delete:
1 select * from games

Output:

1 delete from games where GameID = 3

Output:
Example of Truncate:
1 select * from games

Output:

1 truncate table games

Output:

This deletes all the records from a table.

75. What is the difference between the DROP and TRUNCATE commands?

If a table is dropped, all things associated with that table are dropped as well. This
includes the relationships defined on the table with other tables, access privileges,
and grants that the table has, as well as the integrity checks and constraints.

To create and use the table again in its original form, all the elements associated with
the table need to be redefined.

However, if a table is truncated, there are no such problems as mentioned above. The
table retains its original structure.

76. What are the third-party tools that are used in SQL Server?

The following is the list of third-party tools that are used in SQL Server:

 SQL CHECK
 SQL DOC 2
 SQL Backup 5
 SQL Prompt
 Litespeed 5.0

77. Can we link SQL Server with others?

Yes, SQL Server can be linked with other database systems using various methods.
One common method is through the use of linked servers. Linked servers allow SQL
Server to establish connections and access data from other database platforms. By
configuring appropriate settings and creating the necessary connections, SQL Server
can interact with databases such as MySQL, Oracle, PostgreSQL, and more, enabling
data integration and querying across multiple systems.

Also, check out the blog on PostgreSQL vs. MySQL.

78. What are some common clauses used with SELECT queries in SQL?

There are many SELECT statement clauses in SQL. Some of the most commonly used
clauses with SELECT queries are as follows:

 FROM
The FROM clause defines the tables and views from which data can be interpreted.
The tables and views listed must exist at the time the question is given.
 WHERE
The WHERE clause defines the parameters that are used to limit the contents of
the results table. You can test for basic relationships or for relationships between a
column and a series of columns using subselects.
 GROUP BY
The GROUP BY clause is commonly used for aggregate functions to produce a
single outcome row for each set of unique values in a set of columns or
expressions.
 ORDER BY
The ORDER BY clause helps in choosing the columns on which the table’s result
should be sorted.
 HAVING
The HAVING clause filters the results of the GROUP BY clause by using an
aggregate function.

79. Explain the difference between OLTP and OLAP.

OLTP: It stands for Online transaction processing, and we can consider it a category of
software applications that are efficient for supporting transaction-oriented programs.
One of the important attributes of the OLTP system is its potential to maintain
consistency. The OLTP system often follows decentralized planning to avoid single
points of failure. This system is generally designed for a large audience of end users
to perform short transactions. The queries involved in such databases are generally
simple, require a fast response time, and, in comparison, return only a few records.
So, the number of transactions per second acts as an effective measure for those
systems.
OLAP: It stands for online analytical processing, and it is a category of software
programs that are identified by a comparatively lower frequency of online
transactions. For OLAP systems, the efficiency of computing depends heavily on the
response time. Hence, such systems are generally used for data mining or
maintaining aggregated historical data, and they are usually used in multidimensional
schemas.

80. What is Hybrid OLAP?

Hybrid OLAP (HOLAP) uses a combination of multidimensional data structures and

relational database tables to store multidimensional data. The aggregations for a
HOLAP partition are stored by analysis services in a multidimensional structure. The
facts are stored in a relational database.

81. How can you copy data from one table to another table?

To copy data from one table to another in SQL Server (MSSQL), you can use
the INSERT INTO … SELECT statement. This allows you to copy data from one table to
another, either all rows or just specific rows based on a condition.

General Syntax
1 INSERT INTO destination_table (column1, column2, ...)
2 SELECT column1, column2, …
3 FROM source_table
4 WHERE condition; -- Optional, use to filter rows

Copy Data from Games to GamesBackup

1 INSERT INTO GamesBackup(GameID, Title, Genre, ReleaseYear, Price)SELECT
GameID, Title, Genre, ReleaseYear, Price FROM Games;

82. What is the difference between BETWEEN and IN operators in SQL?

The BETWEEN operator is employed to identify rows that fall within a specified range
of values, encompassing numerical, textual, or date values. It returns the count of
values that exist between the two defined boundaries.

On the other hand, the IN operator serves as a condition operator utilized for
searching values within a predetermined range. When multiple values are available
for selection, the IN operator is utilized.

83. What is the difference between HAVING and WHERE clauses?

The main difference between the ‘HAVING’ and ‘WHERE’ clauses in SQL is that the
‘WHERE’ clause operates on individual rows of data, while the ‘HAVING’ clause is used
to filter aggregated data. The ‘WHERE’ clause cannot be used with aggregate
functions, whereas the ‘HAVING’ clause specifically filters results based on aggregate
conditions.

Let us consider the employee table below.

84. How can you create empty tables with the same structure as another
table?

This can be achieved by fetching the records of one table into a new table using the
INTO operator while fixing a WHERE clause to be false for all records. In this way, SQL
prepares the new table with a duplicate structure to accept the fetched records.
However, there are no records that will get fetched due to the WHERE clause in
action. Therefore, nothing is inserted into the new table, thus creating an empty table.
#games table
Syntax

SELECT * INTO new_table FROM Games

85. How will you update the prices in a product column by increasing 5% of
the prices in each row?

#games table

We can update the prices of the product columns by using the UPDATE method, which
is part of the DML.

UPDATE Games SET Price = Price * 1.05;

Explanation:

 UPDATE Games: Specifies the table you want to update, which is Games.
 SET Price = Price * 1.05: Updates the Price column by multiplying the current
price by 1.05. This effectively increases each price by 5%.

86. How will you fetch the most recent entries in a database?

We can fetch the most recent entries in a database by using the ORDER BY clause
along with the timestamp column in descending order. #game stable

SELECT * FROM Games ORDER BY ReleaseYear DESC

87. How will you calculate the average price of products in each category?

To calculate the average price of products in each category, we can use the
aggregate function (AVG) with the price column and group it by the category column.

#gamestable

SELECT Genre, AVG(Price) AS AveragePrice FROM Games GROUP BY Genre;

88. How will you calculate the total sales in each category of a product sales
table?

To calculate the total sales in each category of a product sales table, we can use the
aggregate function (SUM) with the sales amount column and group it by the category
column.

# purchases and games table

SELECT g.Genre, SUM(p.TotalAmount) AS TotalSales FROM Purchases p JOIN Games g

ON p.GameID = g.GameID GROUP BY g.Genre;
Explanation:

 SELECT g.Genre, SUM(p.TotalAmount) AS TotalSales: Selects the Genre from the

Games table and calculates the total sales (SUM(p.TotalAmount)) for each genre.
The result is aliased as TotalSales.
 FROM Purchases p: Specifies the Purchases table as the source of sales data.
 JOIN Games g ON p.GameID = g.GameID: Joins the Purchases table with the Games
table on the GameID column to get the genre information for each purchase.
 GROUP BY g.Genre: Groups the results by Genre so that SUM(p.TotalAmount)
computes the total sales within each genre group.

89. How will you find the IDs or details where there have been no entries in
terms of sales?

#games,purchases table

To find the IDs or details where there have been no entries in terms of sales, we can
use the LEFT JOIN or NOT EXISTS clause.

Assume we have two tables: ‘product’ with product details and ‘sales’ with sales data.

Left Joins:

SELECT g.GameID, g.Title FROM Games g LEFT JOIN Purchases p ON

g.GameID=p.GameID WHERE p.GameID IS NULL;

Explanation:
 SELECT g.GameID, g.Title: Retrieves the GameID and Title from the Games
table.
 FROM Games g: Specifies the Games table as the primary table.
 LEFT JOIN Purchases p ON g.GameID = p.GameID: Performs a left join with
the Purchases table based on the GameID. This join includes all records from
Games and the matched records from Purchases. If no match is found, the result
from Purchases will be NULL.
 WHERE p.GameID IS NULL: Filters the results to include only those rows from
Games where no corresponding entry exists in Purchases, indicating no sales.

SQL Scenario-Based Interview Questions

90. Suppose there is a database where information about the employees in

various verticals is stored. Your task is to find the average salary of each
vertical and the highest salary among the lot.

To find the average salary of each vertical and the highest salary among the
employees, we can use the group by clause along with the aggregate functions (AVG
and MAX).

SELECT MAX(Price) AS HighestPrice FROM Games;

Explanation:

 SELECT MAX(Price) AS HighestPrice: Retrieves the highest price from the Price
column of the Games table.
 FROM Games: Specifies the Games table as the source.

91. Given data where store inventory is stored, your task is to find the top 3
products in each category in terms of prices.

#games, purchase table

To find the top 3 products in each category in terms of price, we can group by clause
along with the aggregate function (MAX) with the price column and set the limit as 3
in descending order.

WITH RankedGames AS (
SELECT GameID,Title, Genre, Price,
ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY Price DESC) AS Rank
FROM Games
)
SELECT GameID,Title, Genre, Price FROM RankedGames WHERE Rank <= 3;

Explanation:

 WITH RankedGames AS (…): Defines a common table expression (CTE) that ranks
games within each genre.
 ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY Price DESC) AS Rank:
Assigns a rank to each game within its genre based on price in descending order.
 PARTITION BY Genre: Resets the row number for each genre.
 ORDER BY Price DESC: Orders the games within each genre by price in descending
order.
 SELECT … FROM RankedGames WHERE Rank <= 3: Filters the ranked games to
get only the top 3 games per genre.

92. Write an SQL query to find the month-on-month sales of a specific

product in a store.

To calculate the month-on-month sales of a specific product in a store, we can use a

combination of date functions and aggregate functions.

GENERAL QUERY

— Specify the GameID for the specific product

#games, purchases table
DECLARE @SpecificGameID INT = 1; — Replace with the actual GameID

WITH MonthlySales AS ( SELECT p.GameID, g.Title, YEAR(p.PurchaseDate) AS

SalesYear, MONTH(p.PurchaseDate) AS SalesMonth, SUM(p.Quantity * g.Price) AS
TotalSalesAmount FROM Purchases p JOIN Games g ON p.GameID = g.GameID
WHERE
p.GameID = @SpecificGameID
GROUP BY
p.GameID,
g.Title,
YEAR(p.PurchaseDate),
MONTH(p.PurchaseDate)
)
SELECT
Title,
SalesYear,
SalesMonth,
TotalSalesAmount
FROM
MonthlySales
ORDER BY
SalesYear,
SalesMonth;
93. Suppose in an organization, employees are mapped under managers.
Write an SQL query that will fetch you the managers and employees working
under them.

#games, purchases, reviews

To fetch the managers and employees working under them, we can use a self-join to
fetch the managers and the employees working under them.

GENERAL QUERY

WITH GameDetails AS (
SELECT g.GameID, g.Title AS GameTitle, g.Genre, g.Price, r.ReviewID, r.Rating,
r.Comment, p.PurchaseID, p.Quantity, p.TotalAmount FROM Games LEFT JOIN Reviews
r ON g.GameID = r.GameID LEFT JOIN
Purchases p ON g.GameID = p.GameID
)
SELECT GameID, GameTitle, Genre, Price, ReviewID, Rating, Comment, PurchaseID,
Quantity, TotalAmount FROM GameDetails ORDER BY GameID, ReviewID, PurchaseID;
Explanation:

 WITH GameDetails AS (…): Defines a common table expression (CTE) to gather

game details along with related reviews and purchases.
 LEFT JOIN Reviews r ON g.GameID = r.GameID: Joins the Games table with Reviews
to get review details for each game.
 LEFT JOIN Purchases p ON g.GameID = p.GameID: Joins the Games table with
Purchases to get purchase details for each game.
 SELECT … FROM GameDetails: Retrieves the combined information from the CTE.
 ORDER BY GameID, ReviewID, PurchaseID: Orders the results by game, then
review, and then purchases to maintain a structured view.

94. In a store inventory, your task is to fetch the total quantity of the top
product purchased by the customers. #games,purchases table

To fetch the total quantity of the top product purchased by the customers, we can use
a group by clause along with the limit in descending order.

GENERAL QUERY

WITH TotalQuantities AS (
SELECT g.GameID, g.Title, SUM(p.Quantity) AS TotalQuantity FROM
Games g JOIN Purchases p ON g.GameID = p.GameID
GROUP BY g.GameID, g.Title
),
TopProduct AS (
SELECT TOP 1 GameID, Title, TotalQuantity FROM TotalQuantities ORDER BY
TotalQuantity DESC
)
SELECT GameID, Title, TotalQuantity FROM TopProduct;

Explanation:

1. WITH TotalQuantities AS (…): Defines a CTE to calculate the total quantity

purchased for each game.

o SUM(p.Quantity) AS TotalQuantity: Computes the total quantity

purchased for each game.
o GROUP BY g.GameID, g.Title: Groups the results by GameID and Title to
aggregate the quantities for each game.

2. TopProduct AS (…): Defines a second CTE to fetch the top product based on the
total quantity purchased.

o TOP 1: Selects the top product with the highest total quantity.
o ORDER BY TotalQuantity DESC: Orders the products by total quantity in
descending order to get the top one.

3. SELECT … FROM TopProduct: Retrieves the details of the top product from the
TopProduct CTE.

95. Mention different types of replication in SQL Server?

In SQL Server, three different types of replications are available:

 Snapshot replication
 Transactional replication
 Merge replication

96. How do you stay up to date with new SQL features and best practices?

Staying up-to-date with SQL features and best practices involves a combination of
following reliable sources, engaging with the community, and continuous learning.
Here’s a breakdown of how you can keep your SQL knowledge current:

1. Official Documentation and Release Notes

 Database Vendors: Regularly check the official documentation and release notes
from major database vendors like Microsoft SQL Server, PostgreSQL, MySQL, and
Oracle. These sources provide information about new features, enhancements, and
best practices directly from the creators of the software.

2. Online Communities and Forums

 Engage with the SQL community on Stack Overflow to see common issues and
solutions.
 Subreddits like r/SQL or r/Database often have discussions about new trends and
best practices.
 Database-Specific Forums: Participate in forums or mailing lists dedicated to
specific SQL databases (e.g., PostgreSQL mailing lists, MySQL forums).

3. Blogs and Websites

 Follow blogs from reputable tech websites and experts who write about SQL and
database management.
 Many database vendors have their own blogs where they share insights and
updates.

4. Conferences and Meetups

 Attend conferences such as SQL PASS Summit, Oracle OpenWorld, or PostgreSQL

Conference.
 Join local tech meetups or user groups focused on SQL and databases to network
with professionals and learn from their experiences.

97. Can you explain a complex SQL concept to a non tech person?

Certainly! Let’s explain Common Table Expressions (CTEs) in SQL using a simple
analogy.

Imagine you’re working on a big project and need to prepare a detailed report. You
decide to break the report into smaller sections to make it easier to manage and
understand. Each section is like a sub-task that you’ll complete before putting
everything together in the final report.

A Common Table Expression (CTE) in SQL is like creating these smaller sections or
sub-tasks to simplify a complex query. It allows you to define a temporary result set
that you can refer to multiple times within a query, making the query easier to read
and manage.

1. Temporary View or Table:

o A CTE is like a temporary table or view that exists only for the duration of a
single SQL query. You define it at the beginning of the query and then use it
like a regular table in the rest of the query.

2. How to Define a CTE:

o You use the WITH keyword followed by the CTE name and the query that
defines it.
3. Using a CTE:

o After defining the CTE, you can use it in the main part of your query as if it
were a regular table.

Example in Simple Terms

Let’s say you have a list of employees and you want to find out which employees are
in the top 10% of earners and also work in a specific department.

Step 1: Define the CTE

 CTE Name: TopEarners

 Purpose: To calculate and list the top 10% of earners.

Step 2: Use the CTE in the Main Query

 You can then use this CTE to filter results based on the department.

SQL Query Example

Here’s how you might write this in SQL: #games,purchases table

— Define the CTE

WITH TotalSales AS (
SELECT
g.GameID,
g.Title,
SUM(p.Quantity * g.Price) AS TotalSalesAmount
FROM
Games g
JOIN
Purchases p ON g.GameID = p.GameID
GROUP BY
g.GameID, g.Title
),
Top10Percent AS (
SELECT
GameID,
Title,
TotalSalesAmount,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY TotalSalesAmount) OVER () AS
PercentileThreshold
FROM
TotalSales
)
— Select games with total sales in the top 10%
SELECT
GameID,
Title,
TotalSalesAmount
FROM
Top10Percent
WHERE
TotalSalesAmount >= PercentileThreshold;

Explanation:

1. TotalSales AS (…): Defines a CTE to calculate the total sales amount for each
game.

o SUM(p.Quantity * g.Price) AS TotalSalesAmount: Calculates the total

sales amount for each game.
o GROUP BY g.GameID, g.Title: Groups the results by GameID and Title.

2. Top10Percent AS (…): Defines a CTE to calculate the threshold for the top 10%
of games based on total sales.

o PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY

TotalSalesAmount) OVER () AS PercentileThreshold: Calculates the
90th percentile threshold for total sales.
3. SELECT … FROM Top10Percent WHERE TotalSalesAmount >=
PercentileThreshold: Filters the games to include only those with total sales
amounts in the top 10%.