SQL database is a very important topic in the field of Information Technology. Many IT companies want to test the interview candidates about this topic. This candidate has to know very well about the SQL database.
Let’s discuss about the top most important questions that are asked in the interview.
Distinguish between DBMS and RDBMS
DBMS is a software that is designed for the creation and maintainance of databases. Some popular DBMS software is MYSQL, PostgreSQL and MongoDB.
RDBMS is used specifically for the storage and management of data that are organized in tabular structures. Some popular RDBMS software is MYSQL, PostgreSQL, IBM DB2, SQL Server, Oracle Database, Microsoft Access, Amazon Redshift, etc.
What Do You Mean by “Database”?
A database is a systematic method for storing the data within a computer system. The same type of information is stored and may be accessed via various techniques. Databases are useful for effectively storing, retrieving, and managing the data.
Define SQL (Structured Query Language)
SQL is used for working with databases such as creating new databases and tables, fetching the data from the databases, and updating the databases. In the year 1986, the American National Standards Institute (ANSI) originally standardized SQL.
Define Constraints and Their Types
SQL constraints define the rules and regulations for the data that is stored within a table.
NOT NULL– This constraint guarantees that there should not be any NULL value in the column.
UNIQUE– This determines if the column values are unique.
PRIMARY KEY– It is a NOT NULL and UNIQUE combination.
FOREIGN KEY– This prevents any actions that could break the connections between tables.
CHECK– This verifies that the values within a column meet a particular condition.
DEFAULT– This assigns a predefined value to a column if no user-defined value is provided.
Differentiate Delete, Drop, and Truncate
Delete | Drop | Truncate |
Removes rows from a table | Removes a table from the database/data dictionary | – |
Can be rolled back | Cannot be rolled back | Cannot be rolled back |
Describe the Various Types of SQL Joins
- Cross join: A cross join generates a result set that contains every possible pairing of rows from the two tables. It is commonly referred as a Cartesian product.
- Inner join: Inner joins, also known as simple joins, retrieve the rows from the connected tables.
Other than that, there are few more joins available:
- Left outer join/left join
- Right outer join/right join
- Full outer join.
Differentiate between the Nested Subquery vs Correlated Subquery
First, the inner query is executed and only for one time in a nested subquery. The outcome of the inner query serves as the input for the execution of the outer query. It is known as “Bottom-up” approach.
The outer query runs first, and the inner query is done for each row of the outer query. It is known as “Top-down” approach.
Pattern Matching
WHERE Name LIKE ‘a%’WHERE Name LIKE ‘%a’WHERE Name LIKE ‘%a%’WHERE Name LIKE ‘_a%’WHERE CustomerName LIKE ‘a_%’WHERE ContactName LIKE ‘a%y’
Find the Second Highest Salary
We can easily find out the second highest salary in SQL using the following queries:
SELECT f_name, MAX (sal) AS sal FROM emploWHERE sal <> (SELECT MAX (sal) FROM emplo);
Differentiate between SQL vs MySQL
The SQL programming language is used for the interaction and management of relational databases.
MySQL is a class of DBMS that is non-relational and generally does not use SQL.
Conclusion
These questions are the most important database interview questions that are asked in the interview by the interviewer. Several database questions can be asked in a job interview. In this article, we discussed some of the most important database questions along with answers. Tons of questions are made in this subject and we discussed a few of them.