Contents
If you are a aspiring software tester or preparing for technical interviews of testing these are Top 10 SQL and DBMS based interview questions which you may not want to miss:
1. What is the difference between Database and DBMS in SQL?
Database: Database in SQL is a collection of data that is stored in such a format such that it can be easily and efficiently accessed. Relational a Non- Relational databases are the most widely used databases.
DBMS: DBMS in SQL is the acronym for Database Management System. It is a Software that is used to define, create and maintain a database and it provides a controlled access to the data.
2. What are Primary key and Foreign Key in SQL?
Primary Key: Primary key in SQL is a column or a group of columns in a table that can uniquely identify each and every row of the table . It is a unique and not null.
Foreign Key: Foreign Key in SQL is a column or a group of columns in a table, that refers to primary key in another table. It maintains referential integrity in the database.
The table which contains the foreign key is called the child table and the table with primary key is the referenced or parent table.
3. What are the different types of Joins in SQL?
JOIN clause in SQL is used to combine two or more tables together based on a common field/column between them.
Types of JOINS In SQL:
1. CROSS JOIN : A CROSS join in SQL returns all the possible combinations of rows of two tables . It is also known as cartesian product.
2. INNER JOIN : A INNER join in SQL basically returns rows from joined tables that have matching rows. It does not include rows from either table that have no matching rows in other.
3.LEFT OUTER JOIN/ LEFT JOIN: A LEFT join in SQL returns all the rows in the left table and matching rows in the right-hand table.
4. RIGHT OUTER JOIN/ RIGHT JOIN:A RIGHT join in SQL returns all the rows in the Right table and matching rows in the left-hand table.
5.FULL OUTER JOIN: A FULL OUTER join in SQL returns rows that are matching in both the tables along with the remaining rows in both the tables.
4. What is meant by Normalization in DBMS?
Normalization in SQL is a database design technique used in DBMS by which we can reduce or remove redundancy from a table. It helps in removing duplicated data from relational tables. 1 st Normal form (NF), 2 NF ,3 NF and Boyce – Codd NF are the common types of Normalization Forms .
5. What is meant by Stored Procedure in SQL?
Stored Procedure in SQL is set of SQL statements that are stored so that they can used again and again at any point of time thus saving time . In simple terms, When a web application is created in JavaScript you do not want to write the SQL code in between , this leads to a messier and unreadable code . So the most commonly used queries are stored in the form of stored procedure and are executed when and wherever required.
6.What is indexing in SQL and how is it Beneficial?
Indexing in SQL is a phenomena by which the rate at which data retrieved
is increased. Index acts as a quick lookup tables to find records which users
may need frequently.
Different types of Indexes in SQL:
1. Clustered Index
2. Non-Clustered Index
3. Column Store Index
4. Filtered Index
5. Hash Index
6. Unique Index
7. What are the different types of Constraints in SQL?
Constraints in SQL are used to specify set of rules on the Tables .
Different types of constraints in SQL are as follows:
1. CHECK – It ensures that the values in a column satisfies a specific
condition.
2. DEFAULT – It sets a default value for a column if no value is specified.
3. FOREIGN KEY – It prevents actions that would destroy links between tables.
4. PRIMARY KEY – It is a combination of a NOT NULL and UNIQUE. It
uniquely identifies each row in a table.
5. NOT NULL – It ensures that a column cannot have a NULL value.
6. UNIQUE –It ensures that all values in a column are different
8. Write a Query to Find the Duplicate Rows in a Table in SQL?
Table : Orders
SELECT Order ID,COUNT(Order ID)
FROM Orders
GROUP BY Order ID
HAVING COUNT(Order ID) > 1
9. What are the differences between delete, drop and truncate?
10. Write a Query to find the 2nd Highest Salary of Employee from an Employee Table in SQL?
Table : Employee
SELECT MAX(SALARY) FROM Employee WHERE SALARY <
(SELECT MAX(SALARY) FROM Employee);
