Database

What are DBMS and RDBMS? Explain the difference between them.

DBMS stands for Database Management System, and RDBMS stands for Relational Database Management System. Both are software systems that manage and organize data, but they have some fundamental differences.

DBMS is a system that manages data in a database. It provides an interface for users to interact with the database, and it manages the storage of data on a computer. DBMS is not limited to relational databases and can handle other types of databases such as hierarchical, network, and object-oriented.

On the other hand, RDBMS is a type of DBMS that organizes data in a relational manner. It uses tables to store data, and each table has columns that define the attributes of the data and rows that represent individual records. RDBMS systems are based on the relational model proposed by Edgar Codd in 1970.

Some key differences between DBMS and RDBMS are:

  1. Data organization: DBMS can organize data in any way, whereas RDBMS organizes data in a tabular form with rows and columns.
  2. Data relationships: RDBMS can create relationships between tables, which allows for more efficient data retrieval and manipulation. DBMS, on the other hand, does not support such relationships.
  3. Data integrity: RDBMS has the ability to enforce data integrity rules, such as primary and foreign keys, to ensure that data is consistent and accurate. DBMS, on the other hand, does not enforce such rules.
  4. Data manipulation: RDBMS supports complex queries, joins, and aggregation functions to manipulate data, while DBMS provides basic data retrieval and modification capabilities.
  5. Scalability: RDBMS is highly scalable, which means it can handle large amounts of data, whereas DBMS has limitations in terms of scalability.

In summary, DBMS is a general term for a software system that manages data, while RDBMS is a specific type of DBMS that organizes data in a relational manner. RDBMS offers several advantages over DBMS, such as data integrity, scalability, and more efficient data retrieval and manipulation.

What are normalization and its types?

Normalization is a technique used in database design to organize data in a way that minimizes redundancy and avoids anomalies. It involves breaking down a database into multiple tables and establishing relationships between them. The aim is to achieve data consistency, accuracy, and integrity.

There are several levels of normalization, known as normal forms. Here are the three most common levels:

  1. First Normal Form (1NF): In 1NF, each column in a table must contain atomic values. This means that the data in each column should not be a composite or a multivalued attribute.
  2. Second Normal Form (2NF): In 2NF, all non-key attributes (i.e., columns that are not part of the primary key) must depend on the entire primary key, not just part of it. This means that there should be no partial dependencies.
  3. Third Normal Form (3NF): In 3NF, all non-key attributes must be dependent only on the primary key and not on any other non-key attributes. This means that there should be no transitive dependencies.

There are also higher normal forms, such as the Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Fifth Normal Form (5NF), which are used in more complex database designs.

What is denormalization?

Denormalization is the process of intentionally adding redundant data to a database in order to improve query performance or simplify data retrieval. It involves deliberately violating the rules of normalization in order to optimize read performance.

While normalization aims to reduce redundancy and improve data integrity, it can sometimes result in complex queries and slower read performance. Denormalization addresses this issue by duplicating some of the data in multiple tables, which can speed up query execution by reducing the number of joins required.

Denormalization is often used in data warehousing, where read performance is typically more important than write performance. By denormalizing the data, queries can be run more quickly, which is crucial in data analysis and business intelligence applications.

However, denormalization comes with a trade-off: it can make data updates more complex and increase the risk of data inconsistencies. Therefore, it is important to use denormalization judiciously and only when the performance benefits outweigh the risks.

Enlist some commands of DDL, DML, and DCL

DDL (Data Definition Language) commands are used to define the structure of a database and its objects. Here are some commonly used DDL commands:

  1. CREATE: used to create a new database object, such as a table, view, or index.
  2. ALTER: used to modify the structure of an existing database object, such as adding or removing columns from a table.
  3. DROP: used to delete a database object, such as a table or view.
  4. TRUNCATE: used to delete all the rows from a table.
  5. RENAME: used to change the name of a database object, such as a table or column.

DML (Data Manipulation Language) commands are used to manipulate the data stored in a database. Here are some commonly used DML commands:

  1. SELECT: used to retrieve data from one or more tables.
  2. INSERT: used to add new rows to a table.
  3. UPDATE: used to modify the data in one or more rows of a table.
  4. DELETE: used to remove one or more rows from a table.

DCL (Data Control Language) commands are used to manage the security and access of a database. Here are some commonly used DCL commands:

  1. GRANT: used to give privileges to a user or role to perform certain actions, such as SELECT or UPDATE.
  2. REVOKE: used to remove privileges from a user or role.
  3. DENY: used to prevent a user or role from performing certain actions, even if they have been granted the privileges to do so.

What are Constraints?

Constraints in a database are rules that enforce the integrity and accuracy of the data stored in tables. They are used to ensure that the data entered into a database is accurate, consistent, and valid. Constraints can be applied at the column level or table level, and they can be either defined at the time of table creation or added later.

Here are some common types of constraints in a database:

  1. Primary key constraint: ensures that each row in a table is uniquely identifiable. A primary key constraint is a combination of one or more columns that uniquely identify each row in the table.
  2. Foreign key constraint: ensures that the values in a column or set of columns in one table match the values in a column or set of columns in another table. This helps maintain referential integrity between related tables.
  3. Unique constraint: ensures that the values in a column or set of columns are unique within a table. This prevents duplicate data from being entered into the table.
  4. Check constraint: ensures that the data entered into a column meets a specific condition. For example, a check constraint can be used to ensure that a column containing dates only accepts valid dates.
  5. Not-null constraint: ensures that a column cannot contain null (i.e., empty) values.

Constraints can improve the quality and consistency of the data in a database by preventing users from entering incorrect or inconsistent data. They also help maintain data integrity and accuracy by enforcing data consistency and preventing data from being deleted or updated in a way that would violate the rules of the database.

What is SQL injection?

SQL injection is a type of attack on a database-driven application that exploits vulnerabilities in the way the application handles user input. In an SQL injection attack, a malicious user submits malicious SQL statements to the application’s input fields, which can be executed by the application’s database.

SQL injection attacks can have serious consequences, including unauthorized access to sensitive data, data theft, and even a complete takeover of the application. Here are some common techniques used in SQL injection attacks:

  1. Input validation bypass: attackers may attempt to submit specially crafted input that bypasses input validation and executes malicious SQL statements.
  2. Malicious input submission: attackers may submit SQL statements that are designed to retrieve or modify sensitive data, such as passwords, credit card information, or other personal data.
  3. Injection through cookies or headers: attackers may attempt to inject SQL code through cookies or headers, which can be used to bypass security measures.

SQL injection attacks can be prevented through proper input validation and sanitization of user input. This includes using parameterized queries, which separate the user input from the SQL statement and prevent SQL injection attacks. Additionally, using secure coding practices and keeping database software up to date can help prevent SQL injection attacks.

What is the difference between a Primary key and Unique Key?

A primary key and a unique key are both used to uniquely identify a row in a database table. However, there are some differences between them.

A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. It must have a unique value for each row and cannot contain null values. There can be only one primary key per table, and it is usually used as the main reference for relationships with other tables. The primary key is also used as a clustering key in some database systems.

A unique key, on the other hand, is a column or a set of columns that also has a unique value for each row in the table, but it can contain null values. There can be multiple unique keys per table, and they can be used to enforce data integrity and to prevent duplicate values in the table.

In summary, a primary key is a unique identifier for a row and must be unique and non-null, while a unique key also ensures uniqueness but can allow null values. A table can have only one primary key, but it can have multiple unique keys.

Why do we need indexing on Database Table Columns?

We use indexing on database table columns to improve the performance of data retrieval operations. Indexing creates a data structure that allows the database management system to quickly locate the data that matches a specific query or condition.

Without indexing, the database management system would need to scan every row of the table to find the data that matches a specific query or condition, which can be a slow and resource-intensive process, particularly for large tables.

By creating an index on one or more columns of a table, the database management system can store a copy of the selected columns in a separate data structure, known as an index, that can be searched more efficiently. When a query is executed that involves the indexed column(s), the database management system uses the index to quickly locate the rows that match the query criteria, rather than scanning every row in the table.

Indexing can greatly improve the performance of data retrieval operations, particularly for large tables, as it can reduce the amount of data that needs to be scanned and retrieved from disk. However, indexing also comes at a cost, as it requires additional storage space and can slow down the performance of write operations, such as inserts, updates, and deletes, as the index needs to be updated to reflect changes to the data.

What are clustered and non-clustered indexes in a Database?

In database systems, clustered and non-clustered indexes are two types of indexes that can be created on a table to improve the performance of data retrieval operations.

A clustered index determines the physical order of the data in a table. When a table has a clustered index, the rows are physically sorted and stored on disk based on the key values of the index. A table can have only one clustered index, as the data can be physically sorted in only one way. Because the data is stored in a specific order, a clustered index can improve the performance of data retrieval operations that involve range searches or sorting, as the database management system can access the data more efficiently.

A non-clustered index, on the other hand, creates a separate data structure that contains the indexed columns and a pointer to the actual row in the table. The data in the table is not physically sorted based on the index, but the index allows the database management system to quickly locate the data that matches a specific query or condition. A table can have multiple non-clustered indexes, as each index creates a separate data structure. Non-clustered indexes can improve the performance of data retrieval operations that involve searching, filtering, and sorting, as the database management system can access the data more efficiently.

In summary, a clustered index determines the physical order of the data in a table, while a non-clustered index creates a separate data structure that contains the indexed columns and a pointer to the actual row in the table. Clustered indexes are suitable for range searches or sorting, while non-clustered indexes are suitable for searching, filtering, and sorting.

What are Inner Join, Left Outer Join, and Right Outer Join?

In database systems, joins are used to combine data from multiple tables based on a common column or set of columns. There are several types of joins available, including inner join, left outer join, and right outer join.

  1. Inner Join:

An inner join returns only the rows that have matching values in both tables. It combines rows from two or more tables where the join condition is true. The syntax for an inner join is as follows:

SELECT column1, column2, …

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

For example, consider two tables: “customers” and “orders”. The customers table has columns “customer_id”, “name”, and “email”, and the orders table has columns “order_id”, “customer_id”, and “order_date”. We can use an inner join to find all the orders made by each customer with the following SQL query:

SELECT customers.name, orders.order_id, orders.order_date

FROM customers

INNER JOIN orders

ON customers.customer_id = orders.customer_id;

This query returns only the rows that have matching customer_id values in both the customers and orders tables.

  1. Left Outer Join:

A left outer join returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, the result will contain null values for the right table columns. The syntax for a left outer join is as follows:

SELECT column1, column2, …

FROM table1

LEFT OUTER JOIN table2

ON table1.column_name = table2.column_name;

For example, consider the same “customers” and “orders” tables as above. We can use a left outer join to find all the customers and their orders, even if they have not placed any orders, with the following SQL query:

SELECT customers.name, orders.order_id, orders.order_date

FROM customers

LEFT OUTER JOIN orders

ON customers.customer_id = orders.customer_id;

This query returns all the rows from the customers table, and the matching rows from the orders table. If a customer has not placed any orders, the result will contain null values for the order_id and order_date columns.

  1. Right Outer Join:

A right outer join is similar to a left outer join, but it returns all the rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, the result will contain null values for the left table columns. The syntax for a right outer join is as follows:

SELECT column1, column2, …

FROM table1

RIGHT OUTER JOIN table2

ON table1.column_name = table2.column_name;

For example, consider the same “customers” and “orders” tables as above. We can use a right outer join to find all the orders and their corresponding customers, even if the customers have not placed any orders, with the following SQL query:

SELECT customers.name, orders.order_id, orders.order_date

FROM customers

RIGHT OUTER JOIN orders

ON customers.customer_id = orders.customer_id;

This query returns all the rows from the orders table, and the matching rows from the customers table. If a customer has not placed any orders, the result will contain null values for the name column.

What do you understand by Self Join? Explain using an example

In a database system, a self-join is a join operation in which a table is joined with itself. Self-joins are often used when a table contains hierarchical or recursive data, such as an organization chart or a tree structure. By joining a table with itself, we can retrieve information about related records within the same table.

The syntax for a self-join is similar to that of a regular join, but it uses different table aliases to distinguish between the different instances of the same table. The join condition typically involves a comparison between columns from the two instances of the table.

Here’s an example to illustrate the concept of self-join. Let’s consider a table called “employees” that contains information about employees in a company. The table has columns “employee_id”, “name”, and “manager_id”, where the “manager_id” column contains the employee ID of the employee’s manager.

We can use a self-join to retrieve the names of employees and their managers. We can join the “employees” table with itself using the “manager_id” column to match employees with their respective managers:

SELECT e.name AS employee_name, m.name AS manager_name

FROM employees e

INNER JOIN employees m

ON e.manager_id = m.employee_id;

In this query, we have used table aliases “e” and “m” to refer to the different instances of the “employees” table. The join condition specifies that we want to match the “manager_id” column of the employee record with the “employee_id” column of the manager record.

This query will return a result set with two columns: “employee_name” and “manager_name”, where each row contains the name of an employee and the name of their manager. By using a self-join, we have been able to retrieve information about related records within the same table.

What are the types of relationships in SQL Server databases?

In a SQL Server database, relationships between tables can be established using foreign keys. There are three types of relationships that can be defined between tables in a SQL Server database:

  1. One-to-One Relationship:

In a one-to-one relationship, each record in one table is associated with only one record in another table, and vice versa. This type of relationship is not commonly used in databases, but it can be useful for partitioning data into smaller, more manageable tables. To establish a one-to-one relationship, a foreign key is added to one of the tables that references the primary key of the other table.

  1. One-to-Many Relationship:

In a one-to-many relationship, each record in one table is associated with one or more records in another table, but each record in the second table is associated with only one record in the first table. This is the most common type of relationship in databases. To establish a one-to-many relationship, a foreign key is added to the table that represents the “many” side of the relationship, which references the primary key of the table that represents the “one” side of the relationship.

  1. Many-to-Many Relationship:

In a many-to-many relationship, each record in one table is associated with one or more records in another table, and vice versa. This type of relationship requires a third table, called a junction table or bridge table, to establish the relationship between the two tables. The junction table contains foreign keys that reference the primary keys of both tables, allowing many-to-many relationships to be established between them.

Overall, these relationships provide a powerful way to organize and structure data in a SQL Server database, allowing data to be efficiently accessed and manipulated through queries and other database operations.

What is the difference between Drop, Truncate, and Delete Table commands in SQL?

In SQL, there are three commands that can be used to remove data from a table: DROP, TRUNCATE, and DELETE. While these commands may seem similar at first glance, there are important differences between them:

  1. DROP:

DROP table is a command that deletes the entire table from the database. It is a DDL (Data Definition Language) command, which means that it modifies the structure of the database. Once a table has been dropped, all of its data, as well as any associated indexes, constraints, triggers, and permissions, are removed from the database.

2. TRUNCATE:

TRUNCATE table is a command that removes all the data from the table, but leaves the table structure intact. It is a DDL command that is similar to DROP, but it is less powerful because it does not remove the entire table from the database. TRUNCATE also resets any auto-incrementing columns to their starting value, and it does not activate any triggers that may be associated with the table.

3. DELETE:

DELETE is a command that removes individual rows from a table based on a specified condition. It is a DML (Data Manipulation Language) command that only removes data, and it does not modify the structure of the database. When a row is deleted using the DELETE command, any associated triggers are activated, and the operation can be rolled back using a transaction.

In summary, DROP and TRUNCATE are more powerful commands that can remove the entire table or all of its data, respectively, while DELETE is a more granular command that removes individual rows based on a specified condition. It is important to use the appropriate command depending on the requirements of the task at hand, as using the wrong command can have unintended consequences.

What is the ACID property in a database?

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are the fundamental properties of a database transaction, and they ensure that the database remains consistent and reliable even in the face of errors, failures, or other unexpected events.

Here’s what each of the ACID properties means:

  1. Atomicity:

Atomicity ensures that a transaction is treated as a single, indivisible unit of work. If any part of a transaction fails, the entire transaction is rolled back, and the database is left unchanged. This means that the database remains in a consistent state, and no partial updates or incomplete operations are left behind.

  1. Consistency:

Consistency ensures that a transaction brings the database from one valid state to another valid state. In other words, the database remains in a valid state both before and after the transaction. If a transaction violates any of the constraints or rules of the database, it is rolled back, and the database is left unchanged.

  1. Isolation:

Isolation ensures that transactions operate independently of each other, even if they are executed concurrently. Each transaction should be isolated from other transactions to prevent interference or conflicts between them. Isolation is achieved through the use of locks, which prevent other transactions from accessing the data that is being modified by a transaction until the transaction is complete.

  1. Durability:

Durability ensures that once a transaction is committed, its effects are permanent and will survive any subsequent failures, such as power outages or system crashes. This means that the data is stored permanently in the database and can be retrieved even in the event of a system failure.

Overall, the ACID properties provide a set of guarantees that ensure the reliability and consistency of a database, even in the face of errors or failures. These properties are essential for any application that requires data integrity and consistency, such as financial systems, e-commerce websites, and other mission-critical applications.

What are database transaction Isolation levels?

Database transaction isolation levels are used to control how concurrent transactions interact with each other. There are four standard isolation levels defined by the ANSI/ISO SQL standard:

  1. READ UNCOMMITTED:

In this isolation level, transactions can read uncommitted changes made by other transactions, which can result in dirty reads, non-repeatable reads, and phantom reads. This level provides the least protection for data consistency.

  1. READ COMMITTED:

In this isolation level, transactions can only read committed changes made by other transactions, which prevents dirty reads. However, non-repeatable reads and phantom reads can still occur.

  1. REPEATABLE READ:

In this isolation level, transactions can only read data that was committed before the transaction began, and any changes made by other transactions after the transaction began are not visible. This prevents dirty reads and non-repeatable reads, but phantom reads can still occur.

  1. SERIALIZABLE:

In this isolation level, transactions are executed serially, as if they were executed one at a time. This provides the highest level of data consistency, preventing dirty reads, non-repeatable reads, and phantom reads, but it can also result in reduced concurrency and slower performance.

It’s worth noting that different database systems may implement these isolation levels differently, and some databases may also provide additional isolation levels beyond the standard four. It’s important to carefully consider the requirements of your application when choosing an isolation level, as each level provides a different balance of consistency and concurrency.

What is the difference between Union and Union All operators?

The UNION and UNION ALL operators in SQL are used to combine the results of two or more SELECT statements into a single result set. The main difference between these two operators is that UNION eliminates duplicate rows from the result set, while UNION ALL retains all rows, including duplicates.

Here’s a brief explanation of each operator:

  1. UNION:

The UNION operator combines the result sets of two or more SELECT statements into a single result set. When the operator is used, it removes any duplicate rows from the combined result set. This means that each row in the final result set is unique. For example:

SELECT column1 FROM table1

UNION

SELECT column1 FROM table2

In this example, the UNION operator combines the results of two SELECT statements, one that selects column1 from table1 and another that selects column1 from table2. The resulting set will contain all unique values of column1 from both tables.

  1. UNION ALL:

The UNION ALL operator is similar to UNION, except that it retains all rows, including duplicates. This means that the final result set may contain duplicate rows. For example:

SELECT column1 FROM table1

UNION ALL

SELECT column1 FROM table2

In this example, the UNION ALL operator combines the results of two SELECT statements, but it does not remove any duplicate rows. The resulting set will contain all values of column1 from both tables, including duplicates.

In summary, the UNION operator eliminates duplicate rows from the result set, while the UNION ALL operator retains all rows, including duplicates.

What is the difference between HAVING and WHERE clauses?

The WHERE and HAVING clauses are both used to filter data in a SQL query, but they operate on different parts of the query and serve different purposes.

  1. WHERE clause:

The WHERE clause is used to filter rows from a table based on a specified condition. It is used in the SELECT, UPDATE, and DELETE statements to specify which rows should be selected, updated, or deleted. The WHERE clause is used to filter rows before they are grouped or aggregated. For example:

SELECT column1, SUM(column2)

FROM table1

WHERE column3 = ‘value’

GROUP BY column1

In this example, the WHERE clause filters the rows from table1 where column3 is equal to ‘value’ before they are grouped by column1 and the sum of column2 is calculated.

  1. HAVING clause:

The HAVING clause is used to filter the results of an aggregate function in a GROUP BY clause. It is used to specify a condition that must be met by the grouped rows after they have been grouped by the GROUP BY clause. For example:

SELECT column1, SUM(column2)

FROM table1

GROUP BY column1

HAVING SUM(column2) > 100

In this example, the HAVING clause filters the results of the GROUP BY clause to only include rows where the sum of column2 is greater than 100.

In summary, the WHERE clause is used to filter rows before they are grouped or aggregated, while the HAVING clause is used to filter the results of an aggregate function after they have been grouped by the GROUP BY clause.

What is the difference between single-row functions and multiple-row functions?

Single-row functions and multiple-row functions are two types of SQL functions that operate on either one row or multiple rows of data.

Single-row functions:

Single-row functions are functions that operate on a single row of data and return a single output value for each row in the result set. They are used to manipulate individual values within a row and can be used in the SELECT, WHERE, and ORDER BY clauses. Examples of single-row functions include:

  • String functions: LOWER, UPPER, SUBSTR, CONCAT
  • Numeric functions: ROUND, TRUNC, ABS, SIGN
  • Date functions: MONTH, YEAR, DAY, TO_DATE

Multiple-row functions:

Multiple-row functions are functions that operate on multiple rows of data and return a single output value for the entire result set. They are used to perform calculations across multiple rows of data and are typically used with the GROUP BY clause to group the rows by a particular column. Examples of multiple-row functions include:

  • Aggregate functions: SUM, AVG, MIN, MAX, COUNT
  • Ranking functions: ROW_NUMBER, RANK, DENSE_RANK
  • Analytic functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

In summary, single-row functions operate on a single row of data and return a single output value for each row, while multiple-row functions operate on multiple rows of data and return a single output value for the entire result set.

What is the difference between char and varchar2?

In SQL, CHAR and VARCHAR2 are both data types used to store character string data. However, they have some key differences:

  1. Fixed vs. Variable length:

CHAR is a fixed-length data type, which means that it always allocates the same amount of storage space for a column, regardless of the actual length of the data in the column. VARCHAR2, on the other hand, is a variable-length data type, which means that it allocates storage space based on the actual length of the data in the column.

  1. Padding:

Because CHAR is a fixed-length data type, it pads any shorter values with blank spaces to fill the allocated storage space. VARCHAR2, being a variable-length data type, does not pad values with blank spaces.

  1. Storage Space:

CHAR columns take up more storage space compared to VARCHAR2 columns since they always allocate a fixed amount of storage space regardless of the actual length of the data in the column.

  1. Performance:

Since CHAR columns have a fixed length, they can provide better performance in some cases, especially when performing searches or comparisons using the = operator. On the other hand, VARCHAR2 columns have variable lengths and are more efficient when it comes to storage space.

In summary, CHAR is a fixed-length data type that pads shorter values with blank spaces, while VARCHAR2 is a variable-length data type that does not pad values with blank spaces. CHAR takes up more storage space but can provide better performance in certain cases, while VARCHAR2 is more space-efficient.

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

BETWEEN and IN are two different operators in SQL that are used to filter or retrieve data from a database. The main differences between them are:

  1. Usage:

BETWEEN is used to filter data based on a range of values. It requires two values, a minimum and a maximum value, to be specified, and it will return all records that fall within that range. IN, on the other hand, is used to filter data based on a list of specific values. It requires a list of values to be specified, and it will return all records that match any of those values.

  1. Syntax:

The syntax for BETWEEN is:

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

The syntax for IN is:

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1, value2, value3, …);

  1. Performance:

BETWEEN may perform better than IN when filtering a large range of values, as it can take advantage of indexes on the column being filtered. In contrast, IN may perform better when filtering a smaller number of values, as it may be faster to scan a smaller list of values than to search for a range of values.

In summary, BETWEEN is used to filter data based on a range of values, while IN is used to filter data based on a list of specific values. BETWEEN requires a minimum and maximum value to be specified, while IN requires a list of values to be specified. The choice of operator depends on the specific requirements of the query and the characteristics of the data being filtered.

How do you copy data from one table to another table?

To copy data from one table to another table in SQL, you can use the INSERT INTO statement. Here’s an example:

Suppose we have two tables named table1 and table2, both with the same columns column1, column2, and column3. We want to copy the data from table1 to table2.

First, we need to write an INSERT INTO statement that specifies the destination table table2 and the columns we want to copy data into:

INSERT INTO table2 (column1, column2, column3)

Next, we need to specify the source data we want to copy. We do this using a SELECT statement that retrieves the data from table1:

Finally, we put everything together and execute the statement:

INSERT INTO table2 (column1, column2, column3)

SELECT column1, column2, column3

FROM table1;

This statement copies all the data from table1 to table2 with the same column values.

Note that the columns in the destination table must match the columns in the source table in terms of number, data type, and order. If there are any mismatches, you will get an error.

What are the types of views in SQL?

In SQL, there are two main types of views:

Simple View:

A simple view is a virtual table that is based on a single SELECT statement. It is created using the CREATE VIEW statement and can be used to simplify complex queries by encapsulating them into a single view. Simple views can be used to hide complex queries and provide a simpler interface to the database user. Simple views do not have any underlying data and they are not physically stored in the database. Whenever a user queries a simple view, the database engine generates a result set based on the underlying SELECT statement.

Complex View:

A complex view is a view that is based on multiple tables or views. It is created using a SELECT statement that can contain joins, subqueries, and other advanced SQL constructs. Complex views are useful for aggregating data from multiple sources and presenting it in a single view. Complex views can also be used to restrict access to sensitive data by hiding certain columns or rows from the user. Complex views are physically stored in the database and can be updated like any other table, subject to the view’s constraints.

Overall, views are useful tools in SQL for creating virtual tables that provide a simplified and secure interface to the database user, and they can help simplify complex queries by encapsulating them into a single view.

What do you understand about a temporary table? Write a query to create a temporary table

A temporary table is a table that is created and used for a specific purpose within a single database session. Temporary tables are only visible within the session that created them and are automatically dropped when the session ends, so they do not persist beyond the current session.

Temporary tables can be useful in situations where you need to store intermediate results or manipulate data in a temporary way without affecting the original tables in the database.

Here is an example query to create a temporary table:

CREATE TEMPORARY TABLE temp_table (

id INT PRIMARY KEY,

name VARCHAR(50)

);

In this example, we are creating a temporary table named temp_table with two columns: id and name. The id column is defined as the primary key of the table, and the name column is defined as a variable-length character string with a maximum length of 50 characters.

Once the temporary table is created, you can insert data into it using INSERT statements, and you can query the data in the table using SELECT statements. When the session ends or the temporary table is dropped explicitly, the temporary table is deleted automatically and the data is lost.

Differences between views and tables.

In SQL, tables and views are both used to store and retrieve data, but they have some key differences:

  1. Definition: A table is a physical database object that stores data in a structured way, with rows and columns. A view, on the other hand, is a virtual table that does not store any data of its own, but is based on a SELECT statement that retrieves data from one or more tables. Views are created using a SELECT statement that defines the columns and data to be included in the view.
  2. Data modification: Tables can be modified directly using INSERT, UPDATE, and DELETE statements, whereas views are generally read-only and cannot be modified directly. Any modifications made to the data through a view will be reflected in the underlying tables.
  3. Data storage: Tables store data physically on disk, whereas views are virtual tables and do not store data on disk. Views are based on the data stored in one or more tables and present the data in a specific way, based on the SELECT statement used to define the view.
  4. Security: Views can be used to restrict access to certain columns or rows of data, while still allowing users to access other parts of the data. Tables do not have this level of security granularity.
  5. Performance: Views can improve query performance by allowing users to retrieve only the data they need, rather than querying the entire table. However, views can also have a negative impact on performance if they are too complex or are based on slow-running queries. Tables generally have faster performance because they are physical database objects optimized for storing and retrieving data.

Overall, tables and views are both important database objects used to store and retrieve data, but they have different purposes and characteristics that make them useful in different situations.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is a plan generated by a database management system that shows the steps involved in executing a particular SQL query or statement. The execution plan describes how the query will be executed by the database and how it will retrieve data from the tables.

You would use an execution plan to understand how a query is being executed by the database and to optimize the query if necessary. The execution plan can help identify bottlenecks, inefficient queries, missing indexes, or other performance issues in the database.

To view the execution plan, you can use a SQL client or an integrated development environment (IDE) such as SQL Server Management Studio or Oracle SQL Developer. In SQL Server Management Studio, you can view the execution plan by clicking on the “Include Actual Execution Plan” button in the toolbar before running the query. This will display the execution plan in a separate tab or window. In Oracle SQL Developer, you can use the “Explain Plan” feature to generate and view the execution plan for a query.

Once you have the execution plan, you can analyze it to understand how the query is executed and identify any performance issues. You can also use the execution plan to optimize the query by adding indexes or modifying the query to take advantage of existing indexes.

What are the NVL and the NVL2 functions in SQL? How do they differ?

The NVL and NVL2 functions are used in SQL to replace null values with another value or expression.

The NVL function takes two arguments. It returns the first argument if it is not null, otherwise it returns the second argument. Here is the syntax for the NVL function:

NVL(expression1, expression2)

If expression1 is not null, the function returns expression1. If expression1 is null, the function returns expression2. For example, the following query replaces null values in the salary column with 0:

SELECT NVL(salary, 0) FROM employees;

The NVL2 function is similar to the NVL function, but it takes three arguments. It returns the second argument if the first argument is not null, otherwise it returns the third argument. Here is the syntax for the NVL2 function:

NVL2(expression1, expression2, expression3)

If expression1 is not null, the function returns expression2. If expression1 is null, the function returns expression3. For example, the following query returns the string “Full Time” if the job_type column is not null, otherwise it returns the string “Part Time”:

SELECT NVL2(job_type, ‘Full Time’, ‘Part Time’) FROM employees;

So, the main difference between NVL and NVL2 is that NVL takes two arguments and returns the second argument if the first argument is null, while NVL2 takes three arguments and returns the third argument if the first argument is null.

What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

Both RANK() and DENSE_RANK() are window functions in SQL that are used to assign a rank to each row within a result set based on the values in a particular column. The main difference between these two functions is how they handle ties.

The RANK() function assigns the same rank to tied values, and then leaves gaps in the ranking sequence for the next distinct value. For example, if there are two rows with the same value, they will be assigned the same rank, and the next row will be assigned a rank equal to the sum of the number of tied rows and one. Here is the syntax for the RANK() function:

RANK() OVER (ORDER BY column_name)

The DENSE_RANK() function also assigns the same rank to tied values, but it does not leave gaps in the ranking sequence for the next distinct value. Instead, it assigns consecutive ranks to tied values. Here is the syntax for the DENSE_RANK() function:

DENSE_RANK() OVER (ORDER BY column_name)

Here is an example to illustrate the difference between these two functions. Consider the following table of sales data:

Product Sales
A 100
B 200
C 200
D 300

If we want to rank the products by their sales, we can use the RANK() and DENSE_RANK() functions as follows:

SELECT Product, Sales, RANK() OVER (ORDER BY Sales DESC) as Rank1, DENSE_RANK() OVER (ORDER BY Sales DESC) as Rank2

FROM SalesTable

The result of this query would be:

Product Sales Rank1 Rank2
D 300 1 1
B 200 2 2
C 200 2 2
A 100 4 3

As you can see, both RANK() and DENSE_RANK() assign rank 2 to the tied values for products B and C. However, RANK() leaves a gap in the ranking sequence, so product A is assigned rank 4, while DENSE_RANK() assigns consecutive ranks to tied values, so product A is assigned rank 3.

What is a “TRIGGER” in SQL?

In SQL, a trigger is a database object that is associated with a table and is automatically executed in response to certain events such as data modifications (INSERT, UPDATE, DELETE).

A trigger consists of procedural code that is executed before or after an event occurs on a table. The code can be written in a variety of languages, including SQL, PL/SQL, or T-SQL, depending on the database management system being used.

Triggers can be used to enforce business rules or data integrity constraints, to audit changes to the data, or replicate data changes to other tables or systems. For example, a trigger can be used to update a log table whenever a row is inserted, updated, or deleted from a table.

Here is an example of a trigger that logs all changes to a table:

CREATE TRIGGER audit_changes

AFTER INSERT OR UPDATE OR DELETE ON sales

FOR EACH ROW

BEGIN

INSERT INTO sales_audit (product, quantity, action_date, action_type)

VALUES (NEW.product, NEW.quantity, CURRENT_TIMESTAMP,

CASE

WHEN OLD.quantity IS NULL THEN ‘INSERT’

WHEN NEW.quantity IS NULL THEN ‘DELETE’

ELSE ‘UPDATE’

END);

END;

In this example, the trigger is created for the sales table and is executed after any insert, update, or delete operation on the table. The code within the trigger logs the details of the change (product, quantity, action_date, action_type) to the sales_audit table. The NEW and OLD keywords are used to refer to the new and old values of the row being inserted, updated, or deleted.

What is a cursor? How to use a cursor?

In SQL, a cursor is a database object that allows you to retrieve and manipulate a set of rows returned by a SELECT statement one at a time, instead of all at once. A cursor works by defining a result set, then executing a query to populate the result set, and finally allowing you to process each row of the result set individually.

Here is an example of how to create and use a cursor in SQL:

DECLARE @product_id INT;

DECLARE @product_name VARCHAR(50);

DECLARE product_cursor CURSOR FOR

SELECT product_id, product_name

FROM products;

OPEN product_cursor;

FETCH NEXT FROM product_cursor INTO @product_id, @product_name;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ‘Product ID: ‘ + CAST(@product_id AS VARCHAR(10)) + ‘, Product Name: ‘ + @product_name;

FETCH NEXT FROM product_cursor INTO @product_id, @product_name;

END;

CLOSE product_cursor;

DEALLOCATE product_cursor;

In this example, a cursor named product_cursor is created to select the product_id and product_name columns from the products table. The cursor is opened, and the first row of the result set is fetched into the @product_id and @product_name variables. A loop is then executed to process each row of the result set until there are no more rows to fetch.

Within the loop, the values of @product_id and @product_name are printed to the console using the PRINT statement. The next row of the result set is then fetched into the @product_id and @product_name variables, and the loop continues until there are no more rows to fetch.

Finally, the cursor is closed and deallocated using the CLOSE and DEALLOCATE statements.

It’s important to note that cursors should be used with caution, as they can be resource-intensive and can impact database performance if not used properly. Cursors are often used in scenarios where it is necessary to perform row-level operations, such as updating a subset of rows that meet certain criteria. However, whenever possible, it is generally better to use set-based operations that can be performed in a single SQL statement, as they are generally more efficient and less prone to errors.

What are  views? Give an example.

In SQL, a view is a virtual table that is based on the result of a SELECT statement. A view can be used to simplify complex queries, hide sensitive data, or provide a customized view of the data for different users or applications.

Here is an example of how to create and use a view in SQL:

CREATE VIEW product_view AS

SELECT product_id, product_name, price

FROM products

WHERE price > 50;

In this example, a view named product_view is created based on the result of a SELECT statement that selects the product_id, product_name, and price columns from the products table, but only for products with a price greater than 50.

Once the view is created, it can be used like a regular table in SQL queries, as shown below:

SELECT *

FROM product_view;

This query will return all the rows and columns from the product_view view, which is based on the products table but only includes products with a price greater than 50.

Views can also be used to simplify complex queries by combining multiple tables or by aggregating data. For example, the following view combines data from the customers, orders, and order_details tables to provide a view of total sales by customer:

CREATE VIEW customer_sales AS

SELECT c.customer_id, c.customer_name, SUM(od.unit_price * od.quantity) AS total_sales

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_details od ON o.order_id = od.order_id

GROUP BY c.customer_id, c.customer_name;

Once this view is created, it can be used to retrieve the total sales for each customer using a simple SELECT statement:

SELECT *

FROM customer_sales;

This query will return the customer ID, customer name, and total sales for each customer, based on the data in the customers, orders, and order_details tables.

What is a stored procedure? Give an example.

In SQL, a stored procedure is a precompiled collection of SQL statements that can be executed on the database server. A stored procedure is created and stored in the database as a database object, and can be invoked by other programs or scripts.

Here is an example of how to create and use a stored procedure in SQL:

CREATE PROCEDURE get_customer_orders

@customer_id INT

AS

BEGIN

SELECT *

FROM orders

WHERE customer_id = @customer_id;

END;

In this example, a stored procedure named get_customer_orders is created to select all orders from the orders table for a specific customer, based on the customer_id parameter that is passed to the stored procedure.

Once the stored procedure is created, it can be executed using the EXECUTE statement, as shown below:

EXECUTE get_customer_orders @customer_id = 1;

This statement will execute the get_customer_orders stored procedure and pass the value 1 as the customer_id parameter. The stored procedure will then select all orders from the orders table where the customer_id is 1.

Stored procedures can also include other SQL statements, such as INSERT, UPDATE, and DELETE statements, as well as control flow statements, such as IF/ELSE statements and loops. Stored procedures can be used to encapsulate complex business logic and data processing tasks, and can help to improve performance and security by reducing the amount of data transferred between the database server and client applications.

Difference between stored procedure and function

In SQL, both stored procedures and functions are database objects that can be used to encapsulate and execute blocks of code. However, there are some differences between stored procedures and functions in terms of their functionality and usage:

  1. Return value: A stored procedure does not return a value, while a function returns a value.
  2. Usage: A stored procedure is typically used to perform an action or a series of actions on the database, such as inserting or updating data, while a function is used to calculate a value or perform a calculation on the data.
  3. Control flow: A stored procedure can include control flow statements, such as IF/ELSE statements and loops, while a function cannot.
  4. Transaction handling: A stored procedure can be used to define a transaction, which is a set of database operations that must be executed as a single unit of work, while a function cannot.
  5. Invocation: A stored procedure is invoked using the EXECUTE statement or by calling the procedure from an application, while a function is invoked as part of a SQL expression, such as a SELECT statement.

Here is an example of a stored procedure and a function:

Stored procedure example:

CREATE PROCEDURE insert_customer

@customer_name VARCHAR(50),

@email VARCHAR(50)

AS

BEGIN

INSERT INTO customers (customer_name, email)

VALUES (@customer_name, @email);

END;

This stored procedure is used to insert a new customer into the customers table, and does not return a value.

Function example:

CREATE FUNCTION calculate_discount (@total_sales DECIMAL(10, 2))

RETURNS DECIMAL(10, 2)

AS

BEGIN

DECLARE @discount DECIMAL(10, 2);

IF @total_sales >= 1000 AND @total_sales < 5000

SET @discount = @total_sales * 0.1;

ELSE IF @total_sales >= 5000 AND @total_sales < 10000

SET @discount = @total_sales * 0.2;

ELSE IF @total_sales >= 10000

SET @discount = @total_sales * 0.3;

ELSE

SET @discount = 0;

RETURN @discount;

END;

This function is used to calculate a discount based on the total sales amount, and returns the calculated discount value. The function can be invoked as part of a SQL expression, such as a SELECT statement:

SELECT customer_name, total_sales, dbo.calculate_discount(total_sales) as discount

FROM customer_sales;

This query returns the customer name, total sales, and discount for each customer, based on the data in the customer_sales view and the calculation performed by the calculate_discount function.

What is a data warehouse?

A data warehouse is a large, centralized repository of data that is specifically designed to support business intelligence (BI) activities such as reporting, data analysis, and decision-making.

A data warehouse is different from a transactional database in that it is optimized for querying and analysis rather than for day-to-day operations. It is designed to consolidate data from a variety of sources, such as operational systems, external data sources, and other data warehouses, into a single, unified view of an organization’s data.

The data in a data warehouse is typically organized into a dimensional model, which is a way of organizing data into facts (i.e., numerical data about specific events or transactions) and dimensions (i.e., descriptive data about the context of those events or transactions, such as time, location, or customer). This organization allows for fast and efficient querying and analysis of data across multiple dimensions.

Data warehouses can be implemented using various technologies, including traditional relational database management systems (RDBMS), columnar databases, and cloud-based solutions. They often require a significant upfront investment in terms of time, resources, and infrastructure, but can provide significant benefits in terms of improved decision-making and operational efficiency.

Differences between SQL and PL/SQL

SQL (Structured Query Language) and PL/SQL (Procedural Language/Structured Query Language) are both programming languages used in Oracle databases, but they have some significant differences in their purpose, syntax, and functionality.

  1. Purpose: SQL is a declarative language used to manage and manipulate relational databases. It is primarily used for querying data, inserting, updating, and deleting records. PL/SQL, on the other hand, is a procedural language that extends SQL with programming constructs like loops, conditionals, and exception handling. It is used to develop stored procedures, functions, triggers, and packages.
  2. Syntax: SQL statements are written in a specific format that includes keywords like SELECT, FROM, WHERE, and GROUP BY. SQL statements are executed as a single command, and they typically return a result set. PL/SQL statements, on the other hand, are written in a block structure that includes a declaration section, an executable section, and an exception handling section.
  3. Functionality: SQL is designed to perform data manipulation and retrieval operations. It can create and alter database objects like tables, views, and indexes, but it cannot perform procedural operations like looping and conditional execution. PL/SQL, on the other hand, is a complete programming language that allows developers to create complex applications with branching logic, loops, and exception handling.
  4. Performance: SQL is optimized for query execution and can process large datasets quickly. PL/SQL, on the other hand, is optimized for procedural processing and can execute complex logic more efficiently than SQL.

In summary, SQL is used for querying and manipulating data, while PL/SQL is used for developing database applications and stored procedures. SQL is a declarative language, while PL/SQL is a procedural language. Finally, SQL is optimized for query execution, while PL/SQL is optimized for procedural processing.

How can you create an empty table with the same structure as another table?

To create an empty table with the same structure as an existing table, you can use the SQL command CREATE TABLE with the LIKE keyword. Here’s the syntax:

CREATE TABLE new_table_name

LIKE existing_table_name;

This will create a new table with the same columns, data types, and constraints as the existing table, but without any data in it.

For example, if you have an existing table called customers and you want to create a new table called new_customers with the same structure:

CREATE TABLE new_customers

LIKE customers;

This will create a new table new_customers with the same columns and data types as the customers table, but without any data. You can then use this new table to insert data or perform other operations as needed.

What is the difference between Nested Subquery and Correlated Subquery?

A subquery is a SQL query nested inside another SQL query. There are two types of subqueries: nested subqueries and correlated subqueries. The main difference between them is in how they are executed.

A nested subquery is a subquery that can be executed independently of the outer query. It is a stand-alone query that is executed first, and its results are used in the outer query. The inner query is executed only once and its result is passed to the outer query as a value. The outer query uses the result of the subquery to filter, join or perform any other operation on the data.

For example, consider the following SQL statement:

SELECT *

FROM employees

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE location_id = 1700

);

In this example, the subquery (SELECT department_id FROM departments WHERE location_id = 1700) is a nested subquery. It is executed first, and its result is used in the outer query to filter the employees table.

On the other hand, a correlated subquery is a subquery that depends on the outer query for its values. The inner query is executed for each row returned by the outer query. The result of the inner query depends on the values of the outer query, and it is used in the outer query to filter or perform any other operation on the data.

For example, consider the following SQL statement:

SELECT *

FROM employees e

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = e.department_id

);

In this example, the subquery (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) is a correlated subquery. It is executed for each row returned by the outer query, and its result depends on the value of the department_id column in the current row of the outer query.

In summary, the main difference between nested subqueries and correlated subqueries is that nested subqueries are executed independently of the outer query, while correlated subqueries are executed for each row returned by the outer query and depend on the values of the outer query for their result.

How to remove duplicate rows from the table?

To remove duplicate rows from a table, you can use the DISTINCT keyword in a SELECT statement to identify the unique rows and then create a new table with only those rows. Alternatively, you can use the GROUP BY clause in a SELECT statement to group the rows by their values and then select only the distinct groups. Here are the steps for each method:

Method 1: Using the DISTINCT keyword

Create a new table with the same structure as the original table (if necessary).

Use the DISTINCT keyword in a SELECT statement to select only the unique rows.

Insert the selected rows into the new table.

Optionally, drop the original table and rename the new table to the original table’s name.

Here’s an example:

— Create a new table with the same structure as the original table

CREATE TABLE new_table LIKE original_table;

— Insert only the distinct rows into the new table

INSERT INTO new_table SELECT DISTINCT * FROM original_table;

— Optionally, drop the original table and rename the new table

DROP TABLE original_table;

RENAME TABLE new_table TO original_table;

Method 2: Using the GROUP BY clause

  1. Use the GROUP BY clause in a SELECT statement to group the rows by their values.
  2. Use an aggregate function like MIN() or MAX() to select only one row from each group.
  3. Insert the selected rows into a new table.
  4. Optionally, drop the original table and rename the new table to the original table’s name.

Here’s an example:

— Create a new table with the same structure as the original table

CREATE TABLE new_table LIKE original_table;

— Insert only the distinct rows into the new table

INSERT INTO new_table

SELECT MIN(id), name, address, phone_number

FROM original_table

GROUP BY name, address, phone_number;

— Optionally, drop the original table and rename the new table

DROP TABLE original_table;

RENAME TABLE new_table TO original_table;

Note that in both methods, you may need to adjust the column names and data types to match your specific table structure

How will you list all the Customers from the Customer Table who have no Order(s) yet?

To list all the customers from the customer table who have no orders yet, you can use a LEFT JOIN between the customer and orders tables, and then filter out the customers who have orders by checking for NULL values in the orders table. Here’s an example query:

SELECT c.*

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

WHERE o.customer_id IS NULL;

In this query, the LEFT JOIN returns all customers from the customers table, along with any matching orders from the orders table. If a customer has no orders yet, the orders columns in the result set will contain NULL values.

The WHERE clause then filters out the customers who have orders by selecting only the rows where the customer_id column in the orders table is NULL.

By selecting only, the columns from the customers table (with c.*), the result set will contain only the customers who have no orders yet, along with all their corresponding columns from the customers table.

======================================================

In this post, I have listed Database SQL Queries Interview questions. These SQL queries are really important from an interview point of view.
These SQL  Queries cover most of the database concepts and these SQLs  are more frequently interviewed asking questions

SQL Query to find total employees department-wise(include department with zero employees)?

Let’s say I want to get department name and total employees count in each department (include department with zero employees) and sort employees count in ascending order?

SQL query to find average salary department wise?

Get department no and average salary

Get department no, department name and average salary

Get department no, department name and average salary(sort average salary in ascending/descending order

SQL Query to find 2nd highest salary of employee?

SQL Query to get 2nd highest salary

SQL Query to get 3rd highest salary

SQL Query to find the Nth highest salary of an employee?

To get 2nd Highest salary to replace N with 2 and to get 3rd Highest salary to replace N with  3 etc…..

Below are the SQL queries to get 2nd/3rd highest salary:

2nd Form of SQL query to get Nth Highest salary is:

Here to get 2nd highest salary to replace N with 2, To get 3rd highest salary to replace N with 3 etc…

SQL Query to find Nth highest salary in Oracle using ROW_NUMBER() function?

To get first/second/3rd/4th etc…   highest salary replace N with 1,2,3,4 etc…

Here N = 1,2,3,4…

For Example, to get 2nd highest salary below is the SQL Query:

SQL Query to find Nth highest salary in Oracle using DENSE_RANK() function?

To get first/second/3rd/4th etc…   highest salary replace N with 1,2,3,4 etc…

Here N = 1,2,3,4…

For Example, to get 2nd highest salary below is the SQL Query:

Find Nth Highest Salary Using RANK() Function?

Put N = 1,2,3,4,…etc to get 1st/2nd/3rd/4th ..highest salary in blow SQL query

SQL query to find duplicate rows in the table?

SQL query to create a table with the same structure of the Existing table?

SQL query to create a table with the same structure with data of the Existing table?

NOTE: In WHERE  Clause you to provide any false condition like 1=2 or 1=10, ‘A’ = ‘B’ 

Create SQL Query to get information of Employee where Employee is not assigned to the department?

How to fetch all the records from Employee whose joining year or month or day is given?

SQL Query to find all Employees with its managers?

How to display  Number from 1 to Given limit with a SQL query?

SQL Both Queries display numbers from 1 to 60 :

SQL Query to list deptno, dept name for all the departments in which there are no employees?

How to write SQL Query to get a total salary from salary and commission?

SQL Query to Find the Joining date of Employee in YYYY-MON-DD format?

Write a SQL Query to find the maximum salary of each department?

get get deptno,dname &  max salary department wise:

get deptno,dname &  max salary department wise and sort max salary in ascending order:

Write SQL Query to get Total Salary department wise where more than 5 employees exist?

SQL query to increase salary by 20 percent for all employees in department number 10 and 20?

SQL Query to find the Nth minimum salary of employees?

Put N= 1,2,3,4… to get first/second/3rd/4th… minimum salary

For Example to get 3rd minimum salary

To get 4th minimum salary

Selecting ODD or EVEN rows from a table in Oracle-explained two methods?

SQL query to find the total salary department wise?

SQL query to find total salary department wise and sort total salary in ascending/descending order?

SQL query to find the total number of employees in each department?

SQL to find departments whose the number of employees is greater than 4?

You May Also Like:

Introduction to Hibernate 5
Latest hibernate distribution Zip file download link
Hibernate 5 distribution binary details
Create SessionFactory in Hibernate5 using hibernate.cfg.xml
Create SessionFactory in Hibernate5 without hibernate.cfg.xml
Save and persist an entity example in hibernate
Hibernate CRUD(Create,Read,Update and Delete) example
Dirty checking in hibernate example
Understanding hibernate Configuration File
Why to use hibernate dialect?
Hibernate hbm2ddl property
What are the benefits of using hibernate?

That’s all about Database SQL Queries Interview questions
If you have any feedback or suggestion please feel free to drop in below comment box.