Going for a SQL job interview? If yes, here’s the most asked SQL interview questions that will help you great in the interview.
Most Asked SQL Interview Questions
1. What is DBMS and RDBMS?
Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.
Relational Database Management System (RDBMS) store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.
2. What is database testing?
Database testing is checking the integrity of actual data in the front end with the data present in the database. It involves validating the data in the database, checking that there are no orphan records (record with a foreign key to a parent record that has been deleted”), no junk records are present, updating records in database and verify the value in the front end.
3. Write SQL Query to find second highest salary of Employee from the table Employee.
There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or sub query to solve this problem. Here is SQL query.
select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
4. What is PL/SQL? Also structure of PL/SQL?
PL/SQL is a programming language. SQL and procedural programming language constructs such as named blocks,iterations, conditional branching.
PL/SQL uses block structure as its basic two structure. Anonymous blocks or nested blocks can be used in PL/SQL.
5. What is the difference between primary key and unique key?
Primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.
Primary key constraint has automatic unique constraint defined on it.
Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns.
There can be many unique constraint defined per table, but only one Primary key constraint defined per table.
Composite key is a primary key with multiple columns as in case of some tables a single field might not guarantee unique and not null values, so a combination of multiple fields is taken as primary key.
Foreign key is used for enforcing referential integrity in which a field marked as foreign key in one table is linked with primary key of another table.
6. What are the types of user defined clusters?
Three types of user defined functions are.
- Scalar Functions.
- Inline Table valued functions.
- Multi statement valued functions.
Scalar returns unit, variant defined the return clause. Other two types return table as a return.
7. What is database normalization?
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships among them. Normalization is a bottom-up technique for database design.
The evolution of Normalization theories is below:
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- 4th Normal Form
- 5th Normal Form
- 6th Normal Form
8. Write SQL Query to find duplicate rows in a database?
You can use the following query to select distinct records:
SELECT * FROM emp a WHERE rowid = (SELECT MAX(rowid) FROM EMP b WHERE a.empno=b.empno)
9. What is collation?
Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.
10. What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which can be used for data entry and easy retrieval processing of data. This processing makes like easier on simplicity and efficiency. It is faster, more accurate results and expenses with respect to OTLP.
11. What is the difference between TRUNCATE and DROP statements?
TRUNCATE removes all the rows from the table, and it cannot be rolled back. DROP command removes a table from the database and operation cannot be rolled back.
12. What are the different types of joins in SQL?
Joins are used to combine records from multiple tables. The different types of joins in SQL are-
- Inner Join – To fetch rows from two tables having matching data in the specified columns of both the tables.
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
- Left Join – To fetch all rows from left table and matching rows of the right table
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
- Right Join – To fetch all rows from right table and matching rows of the left table
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
- Full Outer Join – To fetch all rows of left table and all rows of right table
SELECT * FROM TABLE1 FULL OUTER JOIN TABLE2 ON TABLE1.columnA = TABLE2.columnA;
- Self Join – Joining a table to itself, for referencing its own data
SELECT * FROM TABLE1 T1, TABLE1 T2 WHERE T1.columnA = T2.columnB;
These are the most asked SQL interview questions. Hope these will be very helpful in the interview. Good luck..:)