top of page

Top 100 SQL Interview Questions You Must Prepare In 2023 P3

What is “Trigger” in SQL? Please explain with example?

​

In SQL, a trigger is a database object that is associated with a table and automatically executes a set of actions or SQL statements when a specific event occurs. Triggers are used to enforce data integrity, implement business rules, log changes, or perform additional actions based on the data modification events such as INSERT, UPDATE, or DELETE.

Here's an example to illustrate the usage of a trigger:

Let's say we have two tables: "orders" and "order_audit". Whenever a new order is inserted into the "orders" table, we want to log the details of that order in the "order_audit" table. We can use a trigger to achieve this:

sql

CREATE TRIGGER log_order_insert AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO order_audit (order_id, customer_id, order_date) VALUES (NEW.order_id, NEW.customer_id, NEW.order_date); END;

 

In this example, the trigger named "log_order_insert" is created to execute after an INSERT operation on the "orders" table. The AFTER INSERT clause specifies the trigger event. The ON orders clause specifies the table to which the trigger is associated.

​

The FOR EACH ROW clause indicates that the trigger is executed for each row affected by the INSERT operation.

Within the trigger body, we use the NEW keyword to refer to the newly inserted row. In this case, we extract the values of the "order_id", "customer_id", and "order_date" columns from the newly inserted row and insert them into the "order_audit" table.

​

So, whenever a new row is inserted into the "orders" table, the trigger is fired automatically, and the details of the inserted order are logged into the "order_audit" table.

Triggers provide a way to automate actions in response to specific events, maintaining data integrity, enforcing business rules, and implementing various database functionalities. They are a powerful tool in SQL for performing custom actions alongside data modifications.

What is the difference between cross join and natural join?

The difference between a cross join and a natural join lies in how they combine rows from two tables. Let's explore each join type:

  1. Cross Join (or Cartesian Join):

    • A cross join returns the Cartesian product of two tables, meaning it combines each row from the first table with every row from the second table. It results in a total number of rows equal to the product of the rows in both tables.

    • Syntax: SELECT * FROM table1 CROSS JOIN table2;

    For example, if table1 has 3 rows and table2 has 4 rows, a cross join between them would produce 12 rows (3 rows × 4 rows).

  2. Natural Join:

    • A natural join automatically matches columns with the same name in both tables and returns the rows where the values in these columns are equal. It performs an implicit join based on column names and does not require explicitly specifying the join condition.

    • Syntax: SELECT * FROM table1 NATURAL JOIN table2;

    For example, if table1 and table2 have a column named "id", a natural join between them will match rows where the "id" values are equal. Only the matched rows will be included in the result set.

It's important to note that while a cross join combines all possible combinations of rows, a natural join combines rows based on the equality of specific columns with matching names. Cross join typically results in a large number of rows, while a natural join only returns rows with matching column values.

When using a natural join, it's crucial to consider the column names in both tables and ensure they have the same name and meaning. If the column names are not consistent or if there are multiple columns with the same name, the natural join may produce unexpected results. In such cases, it's often better to use an explicit join condition or specify the columns explicitly in the join condition using other join types like INNER JOIN or LEFT JOIN.

What is subquery in SQL? Explain with example?

In SQL, a subquery (also known as an inner query or nested query) is a query that is embedded within another query. It allows you to use the result of one query (the subquery) as a part of another query (the main query). Subqueries are enclosed within parentheses and can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, and HAVING clauses.

​

Subqueries are commonly used to retrieve intermediate results or perform calculations based on those results. They provide a way to break down complex queries into smaller, more manageable parts. The result of a subquery can be a single value, a single row, a single column, or a result set with multiple rows and columns.

Here's an example to illustrate the usage of a subquery:

Suppose we have two tables: "orders" and "customers". We want to retrieve the names of customers who have placed orders. We can achieve this using a subquery in the WHERE clause:

sql

SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders);

In this example, the subquery (SELECT customer_id FROM orders) is embedded within the main query's WHERE clause. The subquery retrieves the customer IDs from the "orders" table. The result of the subquery, which is a list of customer IDs who have placed orders, is then used in the main query to retrieve the customer names from the "customers" table.

Subqueries allow you to perform operations based on intermediate results. They can be used to filter data, compare values, perform calculations, or even join multiple tables. Subqueries can greatly enhance the flexibility and power of your SQL statements by allowing you to work with subsets of data and leverage the results of one query in another.

What is limitation of subquery?

While subqueries are a powerful feature in SQL, they also have some limitations that you should be aware of:

  1. Performance Impact: Subqueries can sometimes have a negative impact on query performance. When a subquery is used, it is executed for each row of the outer query. This can result in slower execution times, especially if the subquery involves complex operations or accesses large amounts of data. It's important to optimize your queries and consider alternative approaches if performance becomes an issue.

  2. Readability and Maintainability: Subqueries can make SQL statements more complex and harder to read and maintain. As the number of subqueries and their complexity increases, the query can become convoluted and difficult to understand. It's important to strike a balance between using subqueries for their benefits and keeping the code readable and maintainable.

  3. Inefficient Nesting: Subqueries can be nested within each other, but excessive nesting can lead to inefficient and hard-to-manage queries. It's generally recommended to limit the depth of nesting and consider alternative approaches like using joins or temporary tables if the nesting becomes too complex.

  4. Lack of Flexibility: In some cases, subqueries may not offer the same level of flexibility as other SQL constructs. For example, subqueries cannot be used in certain parts of a query, such as the SELECT clause or the GROUP BY clause. In such situations, you may need to restructure your query or use alternative techniques.

  5. Result Set Size: Subqueries that return large result sets can pose challenges, especially when used in contexts where only a single value or a limited number of rows is expected. In such cases, you may need to ensure that the subquery returns an appropriate result set to avoid issues like performance degradation or data truncation.

It's important to consider these limitations when using subqueries in SQL. While subqueries are a valuable tool for performing complex operations and leveraging intermediate results, it's essential to optimize their usage, maintain readability, and choose the most appropriate approach based on the specific requirements of your queries.

Correlated subquery and Non-Correlated subquery explain with example?

  1. Non-Correlated Subquery:

    • A non-correlated subquery is a subquery that can be executed independently of the outer query. The subquery does not reference any columns from the outer query, and its result is evaluated only once.

    • Example:

      sql

      SELECT employee_name FROM employees WHERE employee_id IN (SELECT employee_id FROM departments WHERE department_name = 'Sales');

      In this example, the subquery (SELECT employee_id FROM departments WHERE department_name = 'Sales') is a non-correlated subquery because it doesn't reference any columns from the outer query. It retrieves the employee IDs of employees in the "Sales" department. The result of the subquery is used in the main query's WHERE clause to retrieve the names of employees who have an ID present in the subquery result.

  2. Correlated Subquery:

    • A correlated subquery is a subquery that is executed for each row processed by the outer query. The subquery references one or more columns from the outer query, creating a relationship between the subquery and the outer query.

    • Example:

      SQL

      SELECT product_name FROM products p WHERE product_price > (SELECT AVG(product_price) FROM products WHERE category_id = p.category_id);

      In this example, the subquery (SELECT AVG(product_price) FROM products WHERE category_id = p.category_id) is a correlated subquery because it references the category_id column from the outer query's products table. The subquery calculates the average price of products in the same category as the current row being processed in the outer query. The result of the subquery is used in the main query's WHERE clause to retrieve the product names where the price is greater than the average price of products in their respective categories.

Correlated subqueries allow you to perform operations based on values from the outer query, enabling more dynamic and context-dependent computations. However, they may impact performance as the subquery is executed repeatedly for each row of the outer query. It's important to use correlated subqueries judiciously and optimize them when necessary.

Non-correlated subqueries, on the other hand, are independent of the outer query and can be evaluated just once. They are generally more efficient and straightforward to work with.

 What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators?

The main difference between the 'BETWEEN' and 'IN' condition operators in SQL is how they are used to compare values.

  1. BETWEEN:

    • The 'BETWEEN' operator is used to check if a value falls within a specified range, inclusively. It is typically used with numerical or date/time data types.

    • Syntax: value BETWEEN lower_bound AND upper_bound

    For example, to retrieve all employees with salaries between 2000 and 3000, you can use the 'BETWEEN' operator:

    SQL

    SELECT employee_name FROM employees WHERE salary BETWEEN 2000 AND 3000;

    This query will return the names of employees whose salary falls within the specified range.

  2. IN:

    • The 'IN' operator is used to check if a value matches any value in a specified list or set of values. It is commonly used in SQL queries to filter data based on multiple specific values.

    • Syntax: value IN (value1, value2, ...)

    For example, to retrieve all customers from a specific city or country, you can use the 'IN' operator:

    sql

    SELECT customer_name FROM customers WHERE city IN ('New York', 'London', 'Paris');

    This query will return the names of customers who are located in either New York, London, or Paris.

In summary, the 'BETWEEN' operator is used to check if a value falls within a range, while the 'IN' operator is used to check if a value matches any value in a specified list. 'BETWEEN' is suitable for range-based comparisons, while 'IN' is useful for comparing against multiple specific values.

What are clauses ?What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?

In SQL, clauses are components of a SQL statement that provide additional instructions or conditions to modify the behavior of the statement. They are used to filter, sort, group, or perform other operations on the data retrieved from a database table. The main difference between the 'HAVING' clause and the 'WHERE' clause is the stage at which they are applied in the query execution process and the type of data they operate on:

  1. WHERE Clause:

    • The 'WHERE' clause is used in the 'SELECT', 'UPDATE', 'DELETE', and 'MERGE' statements to filter rows based on specified conditions. It is applied during the retrieval or modification of data, before any grouping or aggregating is done.

    • Syntax: SELECT column1, column2 FROM table_name WHERE condition

    For example, to retrieve all employees with a salary greater than 3000, you can use the 'WHERE' clause:

    SQL

    SELECT employee_name FROM employees WHERE salary > 3000;

    This query will return the names of employees who have a salary greater than 3000.

  2. HAVING Clause:

    • The 'HAVING' clause is used in the 'SELECT' statement with the 'GROUP BY' clause to filter rows based on specified conditions after grouping and aggregation have been performed. It operates on the result set generated by the 'GROUP BY' clause.

    • Syntax: SELECT column1, column2 FROM table_name GROUP BY column1, column2 HAVING condition

    For example, to retrieve the total sales of products for categories with a total sales greater than 5000, you can use the 'HAVING' clause:

    SQL

    SELECT category, SUM(sales) AS total_sales FROM products GROUP BY category HAVING SUM(sales) > 5000;

    This query will return the category and total sales of products for categories where the total sales exceed 5000.

In summary, the 'WHERE' clause is used to filter rows based on conditions before any grouping or aggregation takes place, while the 'HAVING' clause is used to filter groups of rows after grouping and aggregation have been performed. The 'WHERE' clause operates on individual rows, whereas the 'HAVING' clause operates on grouped rows.

What is Dynamic SQL ? How to execute?

Dynamic SQL refers to the technique of constructing and executing SQL statements dynamically at runtime, rather than writing static SQL statements directly in the code. It allows you to create SQL statements on the fly based on variable conditions or dynamic requirements.

Dynamic SQL is typically used when you need to build SQL statements that vary based on user input, runtime conditions, or dynamic data. It provides flexibility and allows you to generate SQL statements dynamically to cater to specific scenarios.

Here's an example of how to execute dynamic SQL:

  1. Building the SQL Statement:

    • First, you need to construct the dynamic SQL statement as a string. You can concatenate strings, include variables, and use control flow statements to build the desired SQL statement.

    • Example:

      SQL

    • DECLARE @sqlStatement NVARCHAR(MAX); DECLARE @tableName NVARCHAR(50); SET @tableName = 'employees'; SET @sqlStatement = 'SELECT * FROM ' + @tableName;

    In this example, the SQL statement is constructed dynamically by concatenating the table name stored in the variable @tableName with the static portion of the SQL statement.

  2. Executing the Dynamic SQL:

    • After building the dynamic SQL statement, you can execute it using the appropriate method provided by your database system. The method to execute dynamic SQL may vary depending on the programming language and database system you are using.

    • Example using SQL Server:

      sqlCopy code

      EXEC sp_executesql @sqlStatement;

    In this example, the sp_executesql stored procedure is used to execute the dynamic SQL statement stored in the variable @sqlStatement.

It's important to be cautious when using dynamic SQL to prevent SQL injection attacks and ensure the integrity and security of your database. You should properly validate and sanitize any user input or dynamically generated values that are included in the dynamic SQL statement.

Dynamic SQL provides flexibility, but it also introduces complexity and potential security risks. Therefore, it should be used judiciously and with proper safeguards.

What are the various levels of SQL constraints?

In SQL, constraints are used to define rules and restrictions on the data that can be stored in database tables. They help maintain data integrity and enforce business rules. There are different levels of constraints in SQL, including:

  1. Column-Level Constraints:

    • Column-level constraints are applied to individual columns within a table. They define rules specific to the values in that column.

    • Examples of column-level constraints include:

      • NOT NULL: Ensures that a column cannot have a NULL value.

      • UNIQUE: Ensures that each value in the column is unique.

      • PRIMARY KEY: Enforces uniqueness and identifies a unique record in the table.

      • FOREIGN KEY: Establishes a link between two tables based on the values of a column.

  2. Table-Level Constraints:

    • Table-level constraints are applied to the entire table, rather than individual columns. They define rules that involve multiple columns or the entire table.

    • Examples of table-level constraints include:

      • CHECK: Defines a condition that must be satisfied for all rows in the table.

      • DEFAULT: Specifies a default value for a column when no value is provided.

      • UNIQUE: Enforces uniqueness across multiple columns together.

  3. Database-Level Constraints:

    • Database-level constraints are applied to the entire database or multiple tables within the database. They define rules that span across multiple tables or involve relationships between tables.

    • Examples of database-level constraints include:

      • REFERENTIAL INTEGRITY: Enforces the integrity of relationships between tables using primary and foreign keys.

      • TRIGGERS: Automatically perform certain actions when specified conditions are met.

      • RULES: Define complex conditions or actions for data manipulation.

Each level of constraint serves a specific purpose and helps ensure the accuracy, consistency, and integrity of data within the database. By applying appropriate constraints, you can define the rules that govern the structure and behavior of your data, ensuring it adheres to the desired business rules and constraints.

List some case manipulation functions in SQL?

In SQL, there are several case manipulation functions that allow you to change the case (uppercase or lowercase) of characters within strings. Here are some commonly used case manipulation functions:

  1. UPPER():

    • The UPPER() function converts all characters in a string to uppercase.

    • Example: SELECT UPPER('hello world') Result: 'HELLO WORLD'

  2. LOWER():

    • The LOWER() function converts all characters in a string to lowercase.

    • Example: SELECT LOWER('Hello World') Result: 'hello world'

  3. INITCAP():

    • The INITCAP() function converts the first character of each word in a string to uppercase and the remaining characters to lowercase.

    • Example: SELECT INITCAP('hello world') Result: 'Hello World'

  4. PROPER():

    • The PROPER() function converts the first character of each word in a string to uppercase and the remaining characters to lowercase.

    • Example: SELECT PROPER('hello world') Result: 'Hello World'

What are the different set operators available in SQL?

In SQL, there are several set operators available that allow you to combine and manipulate the results of multiple queries. The most commonly used set operators are:

  1. UNION:

    • The UNION operator combines the result sets of two or more SELECT statements into a single result set. It eliminates duplicate rows from the combined result.

    • Example: SELECT column1 FROM table1 UNION SELECT column1 FROM table2

  2. UNION ALL:

    • The UNION ALL operator combines the result sets of two or more SELECT statements into a single result set. It includes all rows from the combined result, including duplicates.

    • Example: SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2

  3. INTERSECT:

    • The INTERSECT operator returns the common rows between the result sets of two or more SELECT statements. It only includes rows that appear in all the result sets.

    • Example: SELECT column1 FROM table1 INTERSECT SELECT column1 FROM table2

  4. EXCEPT or MINUS:

    • The EXCEPT or MINUS operator returns the rows that are present in the result set of the first SELECT statement but not in the result set of the second SELECT statement. It effectively subtracts one result set from another.

    • Example: SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2

What is an ALIAS command in SQL?

In SQL, the ALIAS command is used to assign a temporary name or alias to a table or column in a query. It allows you to refer to the table or column by the assigned alias name instead of its original name within the query. The ALIAS command is also known as the AS keyword.

The main purposes of using aliases are:

  1. Readability: Aliases can make the SQL query more readable by providing shorter or more descriptive names for tables or columns.

  2. Self-joins: When a table is joined with itself in a query, aliases are used to create separate references to the same table to distinguish between the different instances.

Here are some examples of using aliases in SQL:

  1. Alias for Tables:

    SQL

    SELECT e.employee_id, e.employee_name, d.department_name FROM employees AS e INNER JOIN departments AS d ON e.department_id = d.department_id;

    In this example, the aliases "e" and "d" are assigned to the tables "employees" and "departments," respectively. The aliases are used to refer to the tables in the SELECT statement and the JOIN condition.

  2. Alias for Columns:

    sql

    SELECT employee_name AS name, salary * 12 AS annual_salary FROM employees;

    In this example, aliases are used to rename the columns "employee_name" and "salary * 12" as "name" and "annual_salary," respectively. The aliases are used in the SELECT statement to refer to the renamed columns.

Using aliases can make the SQL query more concise and easier to understand, especially when dealing with complex queries involving multiple tables and columns. It enhances the readability and maintainability of the query.

What are aggregate and scalar functions?

In SQL, functions are used to perform calculations or operations on data. Two common categories of functions are aggregate functions and scalar functions.

  1. Aggregate Functions:

    • Aggregate functions operate on a set of rows and return a single value that summarizes or aggregates the data. They are often used in combination with the GROUP BY clause to calculate values based on groups of rows.

    • Examples of aggregate functions include:

      • SUM(): Calculates the sum of values in a column.

      • AVG(): Calculates the average of values in a column.

      • COUNT(): Counts the number of rows or non-null values in a column.

      • MAX(): Returns the maximum value in a column.

      • MIN(): Returns the minimum value in a column.

    Example:

    SQL

    SELECT SUM(salary) AS total_salary, AVG(age) AS avg_age, COUNT(*) AS total_employees FROM employees;

  2. Scalar Functions:

    • Scalar functions operate on individual values and return a single value. They are used to perform operations or transformations on data within a single row.

    • Examples of scalar functions include:

      • CONCAT(): Concatenates two or more strings.

      • SUBSTRING(): Extracts a portion of a string based on a specified position and length.

      • UPPER(): Converts a string to uppercase.

      • LOWER(): Converts a string to lowercase.

      • DATEPART(): Extracts a specific part (year, month, day, etc.) from a date or time value.

    Example:

    SQL

    SELECT employee_name, UPPER(last_name) AS uppercase_last_name, CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

    In this example, scalar functions are used to manipulate string values, such as converting the last name to uppercase and concatenating the first name and last name to form the full name.

Aggregate functions are typically used in conjunction with the GROUP BY clause to calculate aggregated values for groups of rows, while scalar functions are used to perform operations or transformations on individual values within a row.

How can you fetch alternate records from a table ?

To fetch alternate records from a table in SQL, you can use the ROW_NUMBER() function in combination with a filtering condition. Here's an example:

SQL

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY column_name) AS row_num FROM table_name ) AS temp WHERE row_num % 2 = 1;

In this example:

  1. The inner query assigns row numbers to each record in the table using the ROW_NUMBER() function. The ROW_NUMBER() function generates a unique number for each row based on the specified ordering (in this case, the "column_name").

  2. The outer query selects the records from the inner query where the row number (row_num) is odd (row_num % 2 = 1).

By filtering on the row numbers, you can fetch alternate records from the table. Adjust the "column_name" and "table_name" placeholders in the query with the actual column name and table name from which you want to fetch the records.

Note that the ordering specified in the ROW_NUMBER() function determines the sequence in which the alternate records are fetched. If you want a different ordering, you can modify the "ORDER BY" clause in the ROW_NUMBER() function accordingly.

What is a View in MS SQL server?

n MS SQL Server, a view is a virtual table that is based on the result of a predefined query. It is a saved SQL query that is given a name and can be used as a regular table in subsequent queries. A view does not store any data itself but rather retrieves data from one or more underlying tables or views when it is queried.

Here are some key points about views in MS SQL Server:

  1. Structure: A view consists of a SELECT statement that defines the columns and rows to be included in the view. It can be created using the CREATE VIEW statement.

  2. Data Retrieval: When a view is queried, it executes the underlying SELECT statement and returns the result set. The result set is presented as a virtual table, which can be used in subsequent queries, joins, or subqueries.

  3. Data Modification: Depending on the configuration, views can be updatable, meaning that data can be inserted, updated, or deleted through the view. However, there are certain restrictions on updatable views, such as not allowing modifications on views with complex expressions or multiple base tables.

  4. Security and Abstraction: Views provide a level of security by allowing users to access specific columns or rows of a table without granting them direct access to the underlying table. Views can also be used to simplify complex queries and provide an abstraction layer over the database schema.

  5. Query Optimization: SQL Server's query optimizer can optimize queries involving views by considering the underlying query and applying optimization techniques such as query rewriting and query folding.

  6. View Maintenance: Views can be altered or dropped using the ALTER VIEW and DROP VIEW statements, respectively. However, modifying a view may require appropriate permissions on the underlying objects.

Here's an example of creating a simple view in MS SQL Server:

SQL

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

In this example, "view_name" is the name of the view, and the SELECT statement defines the columns and rows to be included in the view based on the "table_name" and "condition". Once the view is created, it can be used like a regular table in subsequent queries.

Views provide a way to encapsulate complex logic, simplify data access, and enhance security in database systems. They are especially useful when you want to present a subset of data or perform calculations on the fly without modifying the underlying tables.

what are query optimization technique in SQL?

Query optimization is an important aspect of SQL performance tuning, aimed at improving the efficiency and execution speed of SQL queries. Here are some common query optimization techniques used in SQL:

  1. Indexing: Indexes are data structures that improve the speed of data retrieval operations. By creating indexes on columns frequently used in search conditions (e.g., WHERE clauses, JOIN conditions), the database engine can quickly locate the relevant data. Proper indexing can significantly enhance query performance.

  2. Query Rewriting: The database optimizer may rewrite a query to find more efficient execution plans. It involves transforming the original query into an equivalent form that can be executed more efficiently. This may include reordering JOIN operations, eliminating redundant calculations, or simplifying complex expressions.

  3. Join Optimization: Optimizing JOIN operations is crucial for query performance. Techniques such as choosing the most efficient JOIN algorithm (e.g., nested loops, hash join, merge join) and reordering JOIN operations based on the size and selectivity of the tables involved can greatly impact performance.

  4. Subquery Optimization: Subqueries can be rewritten or optimized by the database engine to improve performance. This may involve converting correlated subqueries into JOIN operations or using temporary tables to cache intermediate results.

  5. Predicate Pushdown: Predicate pushdown involves pushing filtering conditions as close to the data source as possible. By applying filters early in the query execution process, unnecessary data can be eliminated, reducing the amount of data to be processed.

  6. Caching and Query Plan Reuse: Database systems often cache query execution plans to avoid redundant optimization and parsing steps. Reusing cached query plans for similar queries can save processing time and improve overall performance.

  7. Statistics and Cost-Based Optimization: Query optimizers rely on statistics to estimate the selectivity and cardinality of query operations. Keeping statistics up to date allows the optimizer to make informed decisions about the most efficient query plan based on cost-based optimization algorithms.

  8. Query Hints: Query hints provide directives to the optimizer on how to execute a query. While they should be used judiciously, query hints can be employed in certain scenarios to guide the optimizer towards a more efficient execution plan.

It's important to note that query optimization techniques can vary across different database management systems, and the specific techniques available may depend on the particular database system you are using. Understanding the underlying principles of query optimization and analyzing query execution plans can help identify potential areas for optimization and improve SQL query performance.

What is the updated TRIM function used for?

In SQL, the TRIM function is used to remove leading and trailing spaces (or other specified characters) from a string. It helps to clean up and normalize string values by eliminating unnecessary whitespace or specified characters. The TRIM function has been available in SQL for a long time, and its purpose remains the same.

However, it's worth mentioning that different database systems may have slightly different variations of the TRIM function and may support additional features. For example, some database systems provide variations such as LTRIM (left trim) and RTRIM (right trim) functions to specifically remove spaces or characters from one side of the string.

In addition to removing spaces, the updated TRIM function in some database systems may offer the following enhancements:

  1. Trimming Specific Characters: The updated TRIM function allows you to specify characters other than whitespace to be removed from the string. For example, you can trim specific characters like commas, dots, or any other character you specify.

  2. Trimming Leading or Trailing Characters: With the updated TRIM function, you can specify whether to trim characters from the leading (left) side of the string, the trailing (right) side of the string, or both sides.

Here's an example that demonstrates the usage of the updated TRIM function to remove specific characters from a string:

SQL

SELECT TRIM(',' FROM ' , , Hello,World, , ')

In this example, the TRIM function removes the leading and trailing spaces as well as any occurrences of commas (',') from the given string. The result would be the string "Hello,World".

​

It's important to consult the documentation of the specific database system you are using to understand the exact syntax and features of the TRIM function supported by that system, as it may vary slightly between database vendors.

What are data cleansing technique in SQL?

Data cleansing, also known as data scrubbing or data cleaning, refers to the process of identifying and correcting or removing errors, inconsistencies, and inaccuracies in a dataset. In SQL, there are various techniques and functions that can be used for data cleansing. Here are some commonly used data cleansing techniques in SQL:

  1. Removing Duplicates: Use the DISTINCT keyword or the GROUP BY clause to identify and remove duplicate records from a dataset. This ensures that each record is unique and eliminates redundant data.

  2. Handling Missing Values: Use functions like ISNULL, COALESCE, or CASE statements to handle missing or NULL values in the dataset. You can replace the missing values with a default value or apply appropriate logic to handle them based on the specific requirements.

  3. Removing or Correcting Invalid Data: Use filtering conditions and WHERE clauses to identify and remove or correct invalid data. For example, you can use regular expressions or string functions to identify and remove records that do not conform to a specific format or pattern.

  4. Standardizing Data Formats: Use functions like UPPER, LOWER, INITCAP, or other string manipulation functions to standardize the formats of data. This includes converting text to uppercase or lowercase, capitalizing the first letter of each word, or applying consistent date or numeric formats.

  5. Parsing and Splitting Data: Use string functions like SUBSTRING, CHARINDEX, or SPLIT_PART to parse and split data stored in a single column into multiple columns. This is useful when dealing with data that is combined or delimited in a single field.

  6. Handling Outliers: Use statistical functions and techniques to identify and handle outliers in the data. This may involve removing or correcting extreme values that do not fit within the expected range or distribution.

  7. Validating Data Integrity: Apply constraints, such as primary key, foreign key, and check constraints, to ensure data integrity and enforce data validation rules. This helps to prevent the insertion of invalid or inconsistent data into the database.

  8. Data Transformation: Use transformation functions to convert data from one type to another. For example, you can use functions like CAST or CONVERT to change the data type of a column to align with the desired format or to perform calculations correctly.

It's important to note that data cleansing techniques may vary depending on the specific requirements and characteristics of the dataset. The choice of technique will depend on the nature of the data quality issues and the desired outcome.

How to replace characters in string with SQL?

To replace characters in a string with SQL, you can use the REPLACE function. The REPLACE function allows you to search for a specific character or substring within a string and replace it with another character or substring. The syntax for the REPLACE function is as follows:

SQL

REPLACE(string_expression, search_expression, replacement_expression)

  • string_expression: The original string in which you want to replace characters.

  • search_expression: The character or substring you want to find and replace within the original string.

  • replacement_expression: The character or substring that will replace the search_expression within the original string.

Here's an example to illustrate how to use the REPLACE function to replace characters in a string:

cheat sheet of SQL

SELECT: Retrieve data from a table.
Example: SELECT * FROM table_name;

INSERT INTO: Insert new records into a table.
Example: INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Modify existing records in a table.
Example: UPDATE table_name SET column1 = value1 WHERE condition;

DELETE FROM: Remove records from a table.
Example: DELETE FROM table_name WHERE condition;

CREATE TABLE: Create a new table.
Example: CREATE TABLE table_name (column1 datatype, column2 datatype);

ALTER TABLE: Modify the structure of an existing table.
Example: ALTER TABLE table_name ADD column datatype;

DROP TABLE: Delete a table from the database.
Example: DROP TABLE table_name;

WHERE: Specify conditions to filter rows in a SELECT statement.
Example: SELECT * FROM table_name WHERE condition;

GROUP BY: Group rows based on one or more columns.
Example: SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

HAVING: Filter groups based on conditions in a GROUP BY query.
Example: SELECT column1, COUNT() FROM table_name GROUP BY column1 HAVING COUNT() > 10;

ORDER BY: Sort the result set in ascending or descending order.
Example: SELECT * FROM table_name ORDER BY column1 DESC;

COUNT: Count the number of rows or non-null values.
Example: SELECT COUNT(*) FROM table_name;

CONCAT: Concatenate two or more strings.
Example: SELECT CONCAT(column1, ' ', column2) FROM table_name;

DATEPART: Extract a specific part (year, month, day, etc.) from a date.
Example: SELECT DATEPART(year, date_column) FROM table_name;

PRIMARY KEY: Define a column as a unique identifier for a table.
Example: CREATE TABLE table_name (id INT PRIMARY KEY, column1 datatype);

FOREIGN KEY: Create a relationship between two tables.
Example: CREATE TABLE table_name (id INT PRIMARY KEY, foreign_id INT, FOREIGN KEY (foreign_id) REFERENCES other_table(id));

SUBSTRING: Extract a portion of a string.
Example: SELECT SUBSTRING(column1, 1, 3) FROM table_name;

GETDATE: Retrieve the current date and time.
Example: SELECT GETDATE();

VIEW: Create a virtual table based on a SELECT statement.
Example: CREATE VIEW view_name AS SELECT column1, column2 FROM table_name;

STORED PROCEDURE: Create a reusable set of SQL statements.
Example: CREATE PROCEDURE procedure_name AS BEGIN SELECT * FROM table_name; END;

Difference between SQL and NO sql?

SQL and NoSQL (Not Only SQL) are two different approaches to managing and organizing data in databases. Here are the main differences between SQL and NoSQL:

Data Model:
SQL: SQL databases follow a structured and rigid data model known as the relational model. Data is organized into tables with predefined schemas, and relationships between tables are established using keys and foreign keys.
NoSQL: NoSQL databases have a flexible and dynamic data model. They can store unstructured, semi-structured, or structured data in various formats such as key-value pairs, documents, wide-column, or graph structures.
Schema:
SQL: SQL databases enforce a predefined schema, which means that the structure of the data must be defined before inserting data into the database. Changes to the schema often require altering the table structure and can be complex and time-consuming.
NoSQL: NoSQL databases are schema-less or have a flexible schema. They allow for dynamic and on-the-fly changes to the structure of the data, which makes them more adaptable to evolving requirements.
Scalability:
SQL: SQL databases typically scale vertically by increasing the hardware resources (CPU, memory, storage) of a single server. They are generally designed for handling structured data and have limitations on scalability.
NoSQL: NoSQL databases are designed to scale horizontally by distributing data across multiple servers or clusters. They can handle large volumes of data and high traffic loads, making them suitable for big data applications and scalable architectures.
Query Language:
SQL: SQL databases use the Structured Query Language (SQL) for querying and manipulating data. SQL provides a standardized way to interact with the database using declarative queries.
NoSQL: NoSQL databases often have their query languages or APIs specific to the database type. The query languages may vary, but they generally provide efficient and optimized operations for data retrieval and manipulation.
ACID Compliance:
SQL: SQL databases are generally ACID (Atomicity, Consistency, Isolation, Durability) compliant, which ensures data integrity, consistency, and reliability.
NoSQL: NoSQL databases may prioritize scalability and performance over strict ACID compliance. They often provide flexible consistency models (such as eventual consistency) to achieve high availability and scalability.
Use Cases:
SQL: SQL databases are commonly used in applications that require structured data, transactions, and complex relationships. They are suitable for applications such as e-commerce, financial systems, and traditional enterprise applications.
NoSQL: NoSQL databases are often preferred for handling unstructured or semi-structured data, high-velocity data, real-time analytics, content management systems, and applications with rapidly changing requirements.
It's important to note that SQL and NoSQL are not mutually exclusive, and the choice of database type depends on the specific requirements and characteristics of the application. Some modern databases also provide hybrid approaches that combine SQL and NoSQL features to offer more flexibility and scalability.

Scenario based SQL interview questions. 

  1. Scenario: You have a table called "Employees" with columns "EmployeeID", "FirstName", "LastName", and "Salary". Write a SQL query to retrieve the top 5 highest-paid employees.
    SQL Query: SELECT TOP 5 EmployeeID, FirstName, LastName, Salary FROM Employees ORDER BY Salary DESC;

  2. Scenario: You have two tables, "Orders" and "Customers", with a common column "CustomerID". Write a SQL query to retrieve all orders along with the corresponding customer information.
    SQL Query: SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

  3. Scenario: You want to calculate the total revenue generated from each product category. You have a table called "Products" with columns "ProductID", "ProductName", and "CategoryID", and a table called "Orders" with columns "OrderID", "ProductID", and "Quantity".
    SQL Query: SELECT Products.CategoryID, SUM(Orders.Quantity * Products.Price) AS TotalRevenue FROM Products INNER JOIN Orders ON Products.ProductID = Orders.ProductID GROUP BY Products.CategoryID;

  4. Scenario: You have a table called "Students" with columns "StudentID", "FirstName", "LastName", and "Age". Write a SQL query to retrieve the count of students in each age group.
    SQL Query: SELECT Age, COUNT(*) AS Count FROM Students GROUP BY Age;

  5. Scenario: You have a table called "Employees" with columns "EmployeeID", "FirstName", "LastName", and "DepartmentID". Write a SQL query to retrieve the employee details along with their department name.
    SQL Query: SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;.

  6. ​

Scenario: You have a "Customers" table and a "Orders" table. Write a SQL query to retrieve the names of customers who have placed orders.
SQL Query: SELECT c.name FROM Customers c INNER JOIN Orders o ON c.customer_id = o.customer_id;

Scenario: You need to find the total revenue generated by each product in the "Sales" table. Write a SQL query to calculate the sum of sales for each product.
SQL Query: SELECT product_id, SUM(sales_amount) AS total_revenue FROM Sales GROUP BY product_id;

Scenario: You have a "Employees" table with columns for employee_id, name, and salary. Write a SQL query to find the average salary of all employees.
SQL Query: SELECT AVG(salary) AS average_salary FROM Employees;

Scenario: You want to retrieve the top 5 highest-paid employees from the "Employees" table. Write a SQL query to fetch the required data.
SQL Query: SELECT * FROM Employees ORDER BY salary DESC LIMIT 5;

Scenario: You have a "Students" table with columns for student_id, name, and age. Write a SQL query to find the count of students in each age group (0-10, 11-20, 21-30, etc.).
SQL Query: SELECT CASE WHEN age <= 10 THEN '0-10' WHEN age <= 20 THEN '11-20' ELSE '21+' END AS age_group, COUNT(*) AS count FROM Students GROUP BY age_group;

SQL all function List

  1. Aggregate Functions:

    • COUNT(): Returns the number of rows or non-null values in a column.

    • SUM(): Calculates the sum of values in a column.

    • AVG(): Calculates the average of values in a column.

    • MIN(): Finds the minimum value in a column.

    • MAX(): Finds the maximum value in a column.

  2. String Functions:

    • CONCAT(): Concatenates two or more strings together.

    • UPPER(): Converts a string to uppercase.

    • LOWER(): Converts a string to lowercase.

    • SUBSTRING(): Extracts a portion of a string.

    • TRIM(): Removes leading and trailing spaces from a string.

  3. Date and Time Functions:

    • GETDATE(): Retrieves the current date and time.

    • DATEPART(): Extracts a specific part (year, month, day, etc.) from a date.

    • DATEADD(): Adds or subtracts a specific interval from a date.

  4. Numeric Functions:

    • ROUND(): Rounds a number to a specified precision.

    • CEILING(): Rounds a number up to the nearest integer.

    • FLOOR(): Rounds a number down to the nearest integer.

    • ABS(): Returns the absolute value of a number.

  5. Conditional Functions:

    • CASE WHEN: Performs conditional operations based on specified conditions.

    • COALESCE(): Returns the first non-null value from a list of expressions.

    • NULLIF(): Compares two expressions and returns null if they are equal.

  6. Conversion Functions:

    • CAST(): Converts one data type to another.

    • Casting a Numeric Value to a Different Data Type:

    • ​​

      • Cast examples:

    • SELECT CAST(123.45 AS INT); -- Converts 123.45 to an integer (result: 123) SELECT CAST(10 AS DECIMAL(5,2)); -- Converts 10 to a decimal with precision 5 and scale 2 (result: 10.00)

    • Casting a String Value to a Different Data Type:

    • sqlCopy code

    • SELECT CAST('2022-01-01' AS DATE); -- Converts the string '2022-01-01' to a date data type SELECT CAST('45' AS FLOAT); -- Converts the string '45' to a float data type

    • Casting a Date/Time Value to a Different Data Type:

    • sqlCopy code

    • SELECT CAST(GETDATE() AS VARCHAR(20)); -- Converts the current date and time to a string SELECT CAST('2022-05-01' AS DATETIME); -- Converts the string '2022-05-01' to a datetime data type

    • Casting a Boolean Value to a Different Data Type:

    • sqlCopy code

    • SELECT CAST(1 AS BIT); -- Converts the value 1 to a bit data type (true) SELECT CAST('TRUE' AS VARCHAR(5)); -- Converts the string 'TRUE' to a varchar

    • CONVERT(): Converts one data type to another (database-specific).

  8. ​

bottom of page