SQL Interview Questions And Answers
1)Find 2nd Highest Salary in SQL
2)what is the query to return Name and Salary of employee Having Max Salary?
WHERE Salary = (SELECT Max(Salary) FROM Minions) LIMIT 1
3)select name, salaryfrom employee
order by salary desc
limit 1.3)Write an SQL query to find names of employees start with ‘A’?
The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in the where clause.
The Syntax for using LIKE is,
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern; LIKE: operator name pattern: exact value extracted from the pattern to get related data in result set.
The required query is:
SELECT * FROM Employees WHERE EmpName like 'A%' ;
| The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. | TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log. |
| DELETE command is slower than TRUNCATE command. | While the TRUNCATE command is faster than the DELETE command. |
| To use Delete you need DELETE permission on the table. | To use Truncate on a table we need at least ALTER permission on the table. |
| Identity of column retains the identity after using DELETE Statement on table. | Identity of the column is reset to its seed value if the table contains an identity column. |
| The delete can be used with indexed views. | Truncate cannot be used with indexed views |
| S.NO. | DROP | TRUNCATE |
|---|---|---|
| 1. | The DROP command is used to remove table definition and its contents. | Whereas the TRUNCATE command is used to delete all the rows from the table. |
| 2. | In the DROP command, table space is freed from memory. | While the TRUNCATE command does not free the table space from memory. |
| 3. | DROP is a DDL(Data Definition Language) command. | Whereas the TRUNCATE is also a DDL(Data Definition Language) command. |
| 4. | In the DROP command, a view of the table does not exist. | While in this command, a view of the table exists. |
| 5. | In the DROP command, integrity constraints will be removed. | While in this command, integrity constraints will not be removed. |
| 6. | In the DROP command, undo space is not used. | While in this command, undo space is used but less than DELETE. |
| 7. | The DROP command is quick to perform but gives rise to complications. | While this command is faster than DROP. |
7) Why do we use Commit and Rollback command?
| COMMIT | ROLLBACK |
|---|---|
| COMMIT permanently saves the changes made by the current transaction. | ROLLBACK undo the changes made by the current transaction. |
| The transaction can not undo changes after COMMIT execution. | Transaction reaches its previous state after ROLLBACK. |
| When the transaction is successful, COMMIT is applied. | When the transaction is aborted, ROLLBACK occurs. |
The below statement could be used to get data from multiple tables, so, we need to use join to get data from multiple tables.
Syntax :
SELECT tablenmae1.colunmname, tablename2.columnnmae FROM tablenmae1 JOIN tablename2 ON tablenmae1.colunmnam = tablename2.columnnmae ORDER BY columnname;
9) How would you write a query to identify employees belonging to a particular department?
Sample answer:
Finding data entries belonging to a particular group (in this case, employees belonging to a particular department) can be achieved in a few ways. These include:
- Use SELECT, FROM, and WHERE statements
- Use SELECT, FROM, GROUP BY, and HAVING statements
- Use SELECT, FROM, INNER JOIN, and WHERE statements
Sample answer:
A column in a table can be deleted by following these steps:
- Use ‘ALTER TABLE table name’ to select the table with the column you want to delete
- Use ‘DROP COLUMN column name’ to select the column you want to delete
- 11)What are some of the most important aggregate functions?
Sample answer:
Aggregate values are used to perform calculations on a set of values to return a single value. Some of the most widely used aggregate functions are:
- AVG calculates the average set of values
- COUNT counts the total number of rows in a table
- MIN finds the minimum value in a table
- MAX finds the maximum value in a table
- SUM calculates the sum of the values
- 12)How would you write a query to find employees with the same salary from an employee table?
Sample answer:
To find employees with the same salary, the following solution can be used:
- Use the SELECT statement to specify the relevant table
- Use the FROM statement to specify the employee column
- Use the WHERE statement to specify the salary criteria, for example:
‘WHERE salary IN
(SELECT salary
FROM employee
WHERE employee.employee_id <> employee.employee_id)’
- 13)
How do you remove duplicate rows from a table?
Sample answer:
There are several ways to remove duplicate rows from a table. These include:
- Using Common Table Expressions (CTE) with the ROW_NUMBER function to identify and remove duplicate rows
- Using the RANK function with the PARTITION BY clause
- Using the GROUP BY clause with the COUNT function, and then replacing SELECT with DELETE FROM
14. How do you create a trigger with SQL?
Sample answer:
A trigger is a type of stored procedure that runs when a specific event occurs, such as when a new record is added to the database.
Trigger creation varies depending on the RDBMS. Some systems feature a CREATE TRIGGER statement, while others require the users to navigate to a triggers folder in the toolbar. Once created, users must write the trigger’s code, specifying its conditions and effect.
15. What are the main differences between HAVING and WHERE SQL clauses?
Sample answer:
The key differences between HAVING and WHERE SQL clauses are:
- The WHERE clause is used in row operations, whereas the HAVING clause is used in column operations
- The WHERE clause comes before GROUP BY in a query, whereas the HAVING clause comes after GROUP BY
- The WHERE clause cannot be used with aggregate functions, contrary to the HAVING clause
16. How can sensitive information be stored securely in a database?
Sample answer:
Encrypting databases with sensitive information is crucial for security reasons. Encryption can be implemented in several ways. For example, you could:
- Create a master key
- Use symmetric encryption
- Create a certificate protected by the master key
17. How would you write a query to find the sixth highest-earning employee from an employee table?
Sample answer:
A correlated subquery can be used to find the sixth highest-earning employee in a table. The steps are as follows:
- Use the SELECT statement to specify the employee name and salary columns
- Use the FROM statement to specify the relevant table
- Use the WHERE statement to specify the salary criteria, for example:
‘WHERE statement 6-1 = (SELECT COUNT(DISTINCT salary) FROM #Employee e2
WHERE e2.salary > e1.salary)’
18. What are some ways to prevent duplicate entries when making a query?
Sample answer:
There are several methods to avoid duplicate entries when making a query, such as to:
- Create a unique index
- Add the DISTINCT keyword to the SELECT statement
- Use the NOT EXISTS or NOT IN commands
- 19)
How to read TOP 5 records from a table using a SQL query?
- Ans)SELECT * FROM Departments WHERE ROWNUM <= 5;
How to read LAST 5 records from a table using a SQL query?
In order to select the last 5 records we need to find (count of total number of records – 5) which gives the count of records from first to last but 5 records.
Using the MINUS function we can compare all records from DEPARTMENTS table with records from first to last but 5 from DEPARTMENTS table which give the last 5 records of the table as result.
MINUS operator is used to return all rows in the first SELECT statement that are not present in the second SELECT statement.
Query:
SELECT * FROM Departments MINUS SELECT * FROM Departments WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM Departments);21)How to select UNIQUE records from a table using a SQL Query?
METHOD-1: Using GROUP BY Function
GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns. The GROUP BY clause returns one row per group. By applying GROUP BY function on all the source columns, unique records can be queried from the table.
Below is the query to fetch the unique records using GROUP BY function.
Query:
SELECT EMPLOYEE_ID, NAME, SALARY FROM EMPLOYEE GROUP BY EMPLOYEE_ID, NAME, SALARY;What is the difference between primary key and unique key?
Both primary and unique keys carry unique values but a primary key cannot have a null value, while a unique key can. In a table, there cannot be more than one primary key, but there can be multiple unique keys.
