SQL stands structured query language and is a database management system. It is a widely used language. Recruiters are constantly searching for individuals with SQL skills. If you’re looking for a job and need to brush up your skills, these top SQL interview questions will help you do so.
Top SQL Interview Questions
- Comparison between Truncate and Delete?
| TRUNCATE | DELETE |
| It deletes entire rows of the table | Usually, it is used to delete the row |
| Entire table is locked | Rows of the table is locked before deleting the row (record) |
| Execution of truncate is faster compare to delete | Execution of delete is slower compare to truncate |
| There is no log file so we cannot rollback the truncate operation | We can rollback the delete operation |
| Where clause cannot be implemented in Truncate operation | Where clause can be implemented in Delete operation |
- What is the difference between Truncate and Drop?
| TRUNCATE | DROP |
| It deletes entire rows of the table | Drop is used to delete the entire structure and the contents of the table |
| It does not remove any free space from memory | It does remove free space from memory |
| View of the table is present | View of the table is not present |
| Integrity constraints will not be removed | Integrity constraints will be removed |
| Syntax:TRUNCATE TABLE TABLE_NAME; | Syntax:DROP TABLE TABLE_NAME; |
- Define a Candidate key?
Candidate key is a collection of keys which is a subset of superkey. All keys which are used to identify record uniquely are known as candidate key
- Difference between RDBMS and DBMS?
| RDBMS | DBMS |
| It stores the information in tabular form | It stores the information as a file |
| Normalization has to be there in RDBMS | Normalization is not there in DBMS |
| It has designed for multiple access | It has designed for single access |
| It has designed to handle large amount of data | It has designed to handle small amount of data |
| Since the data stored in RDBMS as tables, so there will be relationship between tables | Since the data stored in DBMS as file, so there will be no relationship between tables |
| It supports distributed database | It doesn’t support distributed database |
- What is B-tree in sql?
It is a technique used to move through the rows based on the index key. It is used to represent a multilevel index. Mainly it contains three parts:
- Block pointer – It is used to represent the child
- Keys – It is the searching criteria
- Record pointer – It points to the secondary memory data
- What is the difference between B-Tree and B+ Tree?
| B-Tree | B+ Tree |
| Data pointer is stored in leaf as well as internal nodes | Data pointer is stored only in leaf nodes |
| Searching is slower, deletion is complex | Searching is faster, deletion is easy |
| No redundant search key is present | Redundant keys may present |
| Leaf nodes not linked together | Leaf nodes are linked together like linked list |
- What is the difference between Group By and Order By clause?
| GROUP BY | ORDER BY |
| It help us to group the rows that have same values | It help us to sort the result in descending or ascending order |
| It performs the operation on rows | It performs the operation on columns |
| It can be allowed in create, view statement | It cannot be allowed in create, view statement |
| It is always used before order by clause | It is always used after the group by clause |
- Write a query to find the nth highest salary and explain it?
| ID | SALARY |
| 1 | 10000 |
| 2 | 20000 |
| 3 | 20000 |
| 4 | 30000 |
| 5 | 40000 |
| 6 | 50000 |
Consider employee table
Query:
SELECT ID, SALARY FROM EMPLOYEE E1
WHERE N-1 = ( SELECT COUNT( DISTINCT SALARY ) FROM EMPLOYEE E2 WHERE E2.SALARY > E1.SALARY);
Consider we want to find second highest salary I.e; N=2
Therefore n-1=2-1=1
E1, E2 both represents EMPLOYEE table,
Each row from E1 is compared with E2 in sub query I.e; E2.SALARY>E1.SALARY;
Consider E1.SALARY is 10000 and compare with all the rows of E2 in sub query. The result of sub query is 5 but 1 not equal to 5
Similarly all the rows is compared
When E1.SALARY is 40000 and compare with all the rows in E2 in sub query
The result of sub query is 1 which is equal to n-1 (where n=2)
Output of the query will be
| ID | SALARY |
| 5 | 40000 |
- What are the different types of indexes?
- Unique Index: It helps us to maintain data integrity by confirming that no two rows of data in a table have identical key values.For primary key, unique index is applied by default.So that confirms that the values in the index key columns are unique.
2. Clustered Index: It reorders the normal(physical) order of the table and the search is based on the key values. We can create only one clustered index per table
3. Non-Clustered Index: It not alter the normal(physical) order of the table and maintains the logical order of the data. Each table can have many non-clustered indexes. - Comparison of Clustered Index and Non- Clustered Index?
| Clustered Index | Non-Clustered Index |
| Clustered Index is faster than Non-Clustered Index | Non-Clustered Index is slower than Clustered Index |
| One table can consist only one clustered Index | One table can consist multiple Non-clustered Index |
| It changes the way records are stored in a database as it sorts out rows by the column which is set to be clustered index. | It doesn’t changes the way it was sorted but it creates a separate object within a table which points back to the original table rows after searching |
- Comparison between SQL and PL-SQL.
| SQL | PL-SQL |
| It is Structural Query Language for database. | It is a programming language using SQL for a database. |
| It does not contains variables | It contains variables and other data types |
| It does not contain for loop, while loop and if controls | It contains for loop, while loop and if controls as well |
| It will execute each operation at a time | It will execute multiple operation at a time |
| It can easily interacts with database server | It cannot easily interacts with database server |
| It is a declarative language. | It is a procedural language |
| It is a data oriented language. | It is application oriented language |
| SQL is used to write queries, create and execute DDL and DML statements | PL/SQL is used to write program blocks, functions, procedures, triggers |
- What is PL- SQL?
PL/SQL stands for procedural language extension to SQL. It contains procedural features of programming language and SQL both. It was developed by Oracle Corporation in the early of 90’s to enhance the capabilities of SQL.
- What is the basic structure of PL-SQL?
PL/SQL uses block structure as its basic structure. PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block.
Three sections of PL-SQL:
- Declaration Section (optional)
- Execution Section (mandatory)
- Exception handling section(optional)
This brings us to the end of the blog on SQL Interview Questions. We hope that you are now better acquainted with the concepts. All the best for your upcoming interview.
My name is Sardar Ayaz a professional content writer and SEO expert having Proven record of excellent writing demonstrated in a professional portfolio Impeccable grasp of the English language, including idioms and current trends in slang and expressions. I have ability to work independently with little or no daily supervision with strong interpersonal skills and willingness to communicate with clients, colleagues, and management.
I can produce well-researched content for publication online and in print, organize writing schedules to complete drafts of content or finished projects within deadlines. I have 12 years’ experience to develop related content for multiple platforms, such as websites, email marketing, product descriptions, videos, and blogs.
I use search engine optimization (SEO) strategies in writing to maximize the online visibility of a website in search results