In this article, we are going to discuss the most frequently asked SQL interview questions. We will start from the basic SQL interview questions and then move to the intermediate-level questions, then move to the advanced-level SQL interview questions, and also discuss some essential questions often used in SQL management.
SQL is the acronym for Structured Query Language. When you go for an interview for the role of a DBA (Database Administrator), you might have to face questions based on RDBMS (Relational Database Management System). There are many RDBMS applications such as MySQL, MS SQL Server, SQLite, and Oracle Database that you must be aware of.
Now, let's discuss the best SQL interview questions and answers.
https://www.youtube.com/watch?v=7S_tz1z_5bA&pbjreload=101
SQL Interview Questions and Answers
Here we have put together the best SQL and database interview questions that will help you to crack the SQL or database-based interview.
SQL Interview Questions for Freshers
1. What is data?
Answer: Data can be defined as a set of values, facts, information, and statistics collected together for reference or analysis.
2. What is a database?
Answer: The database can be defined as a collection of data that can be retrieved and modified.
3. What is SQL?
Answer: SQL stands for Structured Query Language, which is a standard language used for the RDBMS (Relational Database Management System). It is basically used to communicate or interact with the database so we can perform different queries on databases.
4. What is the latest version of SQL?
Answer: The latest version of some of the most popular relational database management systems are:
| Popular RDBMS | Latest Version | Release Date |
| MySQL | 8.0.27 | 19 October 2021 |
| Oracle Database | 19c (LTS) | 13 February 2019 |
| PostgreSQL | 14.0 | 30 September 2021 |
5. What is a DBMS?
Answer: DBMS stands for the database management system , which was initially introduced in 1960. It is used to store the data and allows us to perform queries on it, such as insertions, updates, and deletions.
6. Name the different types of DBMS.
Answer: Although there are many types of database management systems, the following are the most famous types of DBMSs:
- Hierarchical databases
- Network databases
- Relational databases
- Object-oriented databases
- Graph databases
- ER model databases
- Document databases
7. Give some key features of SQL.
Answer:
- It is a non-procedural language.
- It is a unified language.
- SQL is a common language for all relational databases.
8. What is an RDBMS?
Answer: A relational database management system (RDBMS) is a database management system that stores data in a relational model. RDBMS was first introduced in 1970 as an updated version of DBMS. Like DBMSs, relational databases give us control over the database so we can create and modify the database.
9. How is a DBMS different from an RDBMS?
Answer: An RDBMS stores data in the form of tables, whereas a DBMS stores data in the form of files. Relational databases support multi-users, whereas a DBMS supports single users.
10. Name some RDBMS software that uses SQL.
Answer:
- MySQL
- Oracle
- MS SQL server
- SQLite
- Sybase
- MS Access
- Ingres
11. What is a table in SQL?
Answer: A database table is a collection of rows and columns that have different attributes stored in it.
12. What does a JOIN clause do in SQL?
Answer: The JOIN clause is used to combine rows from two or more tables on the basis of the related column.
13. Name the different types of JOINS in SQL.
Answer:
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN
- FULL JOIN
Intermediate SQL Interview Questions
14. How is SQL different from a programming language?
Answer: SQL is a query language that is used to manipulate the data stored in the database or to create a database. A programming language, on the contrary, is used by the developers to instruct a computer to perform an action in order to solve a problem. There are many features and operations that a programming language offers that you won’t get in SQL.
15. Name the different data types of SQL.
Answer:
- Numeric
- Date
- Character
- Unicode Character
- Binary
- Miscellaneous
16. How is VARCHAR different from CHAR?
Answer:
- CHAR is used to store a fixed length of string, whereas VARCHAR is used to store a string of variable length.
- CHAR is faster than VARCHAR.
- VARCHAR uses dynamic memory allocation, whereas CHAR uses static memory allocation.
17. Name the various DBMS languages.
Answer:
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- DQL (Data Query Language)
18. Define DDL and name all the tasks performed by DDL.
Answer: DDL stands for data definition language. It is a set of SQL commands that are used to create, modify, and delete the database structure but not the data. Tasks performed by DDL:
- CREATE (Creates objects in the database.)
- ALTER (Used to alter the database.)
- DROP (Used to delete the rows and columns from the database.)
- TRUNCATE (Deletes all the rows and columns from the database.)
- RENAME (Renames the objects in the database.)
- COMMENT (Used for inserting a comment to the database.)
19. Explain DML and name its tasks.
Answer: DML stands for data manipulation language, and it is an area of SQL that allows changing the data within a database. DML tasks:
- SELECT
- INSERT
- UPDATE
- DELETE
- MERGE
- CALL
- EXPLAIN PLAN
- LOCK TABLE
20. How is DELETE different from TRUNCATE?
Answer: TRUNCATE is used to delete all the rows and columns or whole table altogether, whereas, with a DELETE clause, you can delete a specific row using the WHERE clause.
21. Explain DCL and name its tasks.
Answer: Data Control Language (DCL) is a component of the SQL statement that controls access of data to the database. The DCL statements are often grouped with DML statements. DCL tasks:
- GRANT
- CONNECT
- USAGE
- EXECUTE
- UPDATE
- SELECT
22. What is a DBA?
Answer: DBA stands for a database administrator, and it is a person who is responsible for the design, construction, and maintenance of a database.
23. Name the various type of indexes used on index sequential.
Answer:
- Ordered index
- Hashed index
24. What is the VIEW?
Answer: VIEW is a special clause used in SQL to create a virtual table and occupies no space. Example
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE CONDITION;
25. What is the primary key?
Answer: A primary key is a constraint or a column of a table in which all values are unique and contain no NULL value.
26. What are the constraints?
Answer: When we set some limits on the data types of the created table, those limits are known as constraints. Types of constraints:
- NOT NULL
- CHECK
- DEFAULT
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
27. What is an entity?
Answer: An entity is a person, place, thing, or concept about which information is recorded.
28. What are the attributes in DBMS?
Answer: The properties that characterize an entity or entity sets are called its attributes.
29. What are the keys?
Answer: A key in SQL is an attribute or a set of attributes that identifies a tuple in a relation. Keys are defined in a tuple to access or sequence the stored data in an efficient way.
30. Name the different types of keys.
Answer:
- Super Key
- Candidate key
- Primary Key
- Composite key
- Foreign key
- Alternate key
31. What is a foreign key?
Answer: A foreign key is an attribute of a table whose value matches a primary key of another table.
32. What is the default sorting method of the ORDER BY clause?
Answer: By default, ORDER BY follows the ASC or ascending sorting.
33. Name all the operators in SQL.
Answer:
- Arithmetic Operators
- Logical Operators
- Comparison operators
SQL Interview Question for Experienced Developers
34. What is hashing?
Answer: It is a technique in which a given key field value is converted into a storage address by applying some operation on it.
35. Name the different techniques of hashing.
Answer:
- Method of Division
- Division Reminder Method
- Midsquare Method
- Truncation Method
- Shifting Method
36. What are domain constraints?
Answer: Some attributes have some specific values in the real-world scenario for those we use domain constraints. For example, the age of a person could not be a negative number. It will always be positive.
37. What is normalization?
Answer: Normalization is a method of minimizing or breaking down the complexity of a database table into a simple table structure with the help of certain rules. It is used to reduce the redundancy in a table and remove the problems of inconsistency and disk space usage.
38. What is data mining?
Answer: Data mining is a process where potentially useful and previously unknown data or a set of information is extracted from a large volume of data.
39. Name the techniques of data mining.
Answer:
- Association of Rules
- Fuzzy Logic classification
- Decision Tree
- Neural Network
- Genetic Algorithms
40. What is a transaction?
Answer: A transaction can be defined as a group of tasks performed on a database. A transaction is a small unit of the program, and it may contain several low-level tasks.
41. Name all the properties of the transaction.
Answer: The transaction has four basic properties, which are known as ACID:
- A tomicity
- C onsistency
- I solation
- D urability
42. Define the atomicity property of a transaction.
Answer: A transaction is an atomic unit of processing, which means a transaction must be treated as a whole. In other words, it means either performing all operations or none.
43. Define the durability property of a transaction.
Answer: The durability property of the transaction states that the changes applied to the database by a committed transaction must persist in the database. The changes must not be lost because of any failure.
44. What is the difference between BETWEEN and IN clause in SQL?
Answer: The BETWEEN clause is used to query over a range of sets, whereas the IN clause is used to query for some specific sets inside the parenthesis. Example
SELECT * FROM Employee
WHERE emp_id BETWEEN 20 AND 30;
SELECT * FROM Employee
WHERE emp_id IN (30,34,37,38);
45. Write an SQL query to select all employees' details whose names start with A.
Answer:
SELECT * FROM Employees
WHERE emp_name like 'A%';
46. What is the difference between a NULL value and zero?
Answer: A NULL value is used to represent a value that is not assigned or unavailable, whereas zero is an integer that possesses a value.
47. Write a query to count all the records in the table student.
Answer:
SELECT COUNT(*)
FROM student;
48. Write a query to show all the distinct cities from the table employees.
Answer:
SELECT DISTINCT city
FROM employees;
49. Name all the string functions in SQL.
Answer:
- LEN()
- LOWER()
- UPPER()
- SUBSTRING()
- LTRIM()
- RTRIM()
- CONCAT()
- REPLACE()
50. Write a query to add a column ‘Home_City ’ to a table students.
Answer:
ALTER TABLE students ADD(Home_City);
51. Write a query and change the header name student_id of table student with temporary name id.
Answer:
SELECT student_id AS id
FROM student;
Consider these two sample tables for further queries: Students:
+------+--------+------+--------+-------+----------+ | id | name | age | grades | marks | Trade | +------+--------+------+--------+-------+----------+ | 1 | Luffy | 16 | A | 970 | Science | | 2 | Naruto | 18 | A | 960 | Humanity | | 3 | Zoro | 20 | A | 940 | Commerce | | 4 | Sanji | 21 | B | 899 | Humanity | | 5 | Nami | 17 | B | 896 | Science | | 6 | Robin | 20 | B | 860 | Humanity | | 7 | Goku | 27 | B | 860 | Humanity | +------+--------+------+--------+-------+----------+
Library:
+---------+-------------------+------------+ | Book_ID | Book_Name | Student_ID | +---------+-------------------+------------+ | 1124 | One Hundred years | 1 | | 1104 | The Great Escape | 2 | | 1209 | Beloved | 6 | | 1111 | Hollow | 4 | | 2351 | Invisible Man | 3 | | 1034 | A Passage | 2 | | 1211 | Hero | 6 | | 1188 | Your Name | 5 | | 1211 | Hero | 6 | | 1000 | My Hope | 8 | | 1000 | Go Away | 10 | +---------+-------------------+------------+
52. Write a query that displays the students' names and the number of books they borrowed from the library.
Answer:
SELECT Students.name, count(Students.name) as Books
FROM Students JOIN LIBRARY
ON Students.id = Library.Student_Id
GROUP BY name;
Output
+--------+-------+ | name | Books | +--------+-------+ | Luffy | 1 | | Naruto | 2 | | Robin | 3 | | Sanji | 1 | | Zoro | 1 | | Nami | 1 | +--------+-------+
53. Write a query that shows all the student details who have "i" in their name.
Answer:
SELECT *
FROM Students
WHERE name LIKE "%i%";
Output
+------+-------+------+--------+-------+----------+ | id | name | age | grades | marks | Trade | +------+-------+------+--------+-------+----------+ | 4 | Sanji | 21 | B | 899 | Humanity | | 5 | Nami | 17 | B | 896 | Science | | 6 | Robin | 20 | B | 860 | Humanity | +------+-------+------+--------+-------+----------+
54. Create a view of top_students that contains the details of the top 3 students who scored the highest marks.
Answer:
CREATE VIEW top_student AS
SELECT *
FROM Students
ORDER BY marks DESC
LIMIT 3;
55. Write the query to delete the view top_student.
Answer:
DROP VIEW top_student
56. How to write a comment in SQL RDBMS?
Answer:
In SQL, comments can be written with the help of the /* symbol.
Example:
/* This is a comment and the SQL engine will not execute this statement */
57. Write a query to create a new column "phone" in the Students table.
Answer:
ALTER TABLE Students ADD Phone VARCHAR(10);
58. Write a query to delete the column "phone" from the table students.
Answer:
ALTER TABLE Students DROP Phone;
59. Show the details of those students who have borrowed more than one book from the library.
Answer:
SELECT id, Name, age, COUNT(name) as BOOKS
FROM Students JOIN LIBRARY
ON Students.id = Library.Student_Id
GROUP BY name HAVING COUNT(name)>1;
Output:
+------+--------+------+-------+ | id | Name | age | BOOKS | +------+--------+------+-------+ | 2 | Naruto | 18 | 2 | | 6 | Robin | 20 | 3 | +------+--------+------+-------+
Conclusion
SQL always remains in demand. Right now, mostly all the dynamic web applications use a SQL database management system to manage their application content and data. It does not matter which programming language you use; there is database integration present for every programming language that helps in creating and managing SQL databases.
For every developer, SQL is an essential skill set. Thus, in many interviews, the interviewer asks questions related to it. So we recommend you go through these SQL interview questions before you appear in an interview.
And we hope all these SQL interview questions and answers help you to crack your SQL interview. If you like this article or have any suggestions, please let us know by commenting down below.
People are also reading:
Leave a Comment on this Post