SQL is the acronym used for the Structured Query Language. When you go for an interview related to DBA (Database Administrator), you might have to face questions from RDBMS (Relational Database Management System). There are many RDBMS applications such as MySQL, MS SQL Server, SQLite, Oracle, that you must be aware of before you tackle the interview. In this article, we are going to discuss all those questions which are frequently asked during SQL interviews. We will start from the basics then move to the advanced level and also discuss some essential queries often used in SQL management. so let’s discuss SQL Interview Questions and Answers.
SQL Interview Questions and Answers
Here we have put the best SQL/Databasease Interview questions, which definitely going to help you to crack the interview.
1. What is Data?
Answer: Data can be defined as a set of values, facts, information, and statistics collected together for references 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 popular RDBMS
|Popular RDBMS||Latest Version||Release Date|
|MySQL||8.0.26||19 April 2018|
|Oracle Database||21.1||December 2020|
|PostgreSQL||13.4||08 August 2021|
5. What is DBMS?
Answer: DBMS stands for 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 modifications, insertions, updates, deletions, etc.
6. Name the different types of DBMS.
- Hierarchical databases
- Network databases
- Relational databases
- Object-oriented databases
- Graph databases
- ER model databases
- Document databases
7. Give some Key Features of SQL
- Non-Procedural language
- Unified language
- Common Language for all relational Databases
8. What is RDBMS?
Answer: Relational Database Management System (RDBMS) is a Database Management System (DBMS) which store data in a Relational model. RDBMS was first introduced in 1970 as an updated version of DBMS. Like DBMS, RDBMS gives us control over the database so we can create and modify the database.
9. How DBMS is different from RDBMS?
Answer: RDBMS stores Data in the form of Tables, whereas DBMS stores data in the form of files.
RDBMS supports multi-users, whereas DBMS support, single users.
10. Name some RDBMS software that uses SQL.
- MS SQL server
11. What is a Table in SQL?
Answer: A table is a collection of rows and columns which 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 two tables on the basis of the related column.
13. Name the different types of JOINS in SQL
- INNER JOIN
- RIGHT JOIN
- LEFT JOIN
- FULL JOIN
14. How SQL is 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, and the programming language is used by the developers to instruct a computer to perform an action in order to solve the problems.
There are many features and operations that a programming language offers that you won’t get in SQL.
15. Name the different Datatypes of SQL.
- Unicode Character
16. How the VARCHAR is different from the CHAR?
- A CHAR is used to store a fixed length of string whereas a VARCHAR is used to store a variable length of a string.
- The CHAR is faster than VARCHAR.
- A CHAR uses static memory allocation, whereas a VARCHAR uses Dynamic memory allocation.
17. Name the various DBMS languages.
- 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 which is used to create, modify, and delete the database structure but not the data.
Task performed by DDL
- CREATE (use to create objects in the database)
- ALTER (use to alter the database)
- DROP (use to delete the rows and columns from the database)
- TRUNCATE (it is used to delete all the rows and columns from the database)
- RENAME (it is used to rename the objects in 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.
- EXPLAIN PLAN
- LOCK TABLE
20. How DELETE clause is different from the TRUNCATE?
Answer: A 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 often group with DML statements
22. What is a DBA?
Answer: The DBA stands for the 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.
- Ordered index
- Hashed Index
24. What is the VIEW?
Answer: VIEW is a special clause used in SQL which is used to create a virtual table and occupies no space.
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
- 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 property that characterizes an entity or entity sets is 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.
- 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 which values match a primary key of another table.
32. What is HASHING?
Answer: It is a technique in which a given key field value is converted into an address of storage location by applying some operation on it.
33. Name the Different Techniques of Hashing
- Method of Division
- Division Reminder Method
- Midsquare Method
- Truncation Method
- Shifting Method
34. What are Domain Constraints?
Answer: Some attributes have some specific values in the real-world scenario for those we use Domain Constraint.
For e.g. the age of a person could not be a negative number it will always be positive.
35. What is Normalization?
Answer: Normalization is a method of minimizing or breaking down the complexity of a table structure 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.
36. What is Data Mining?
Answer: Data mining is a process where potentially useful and previously unknown data or set of information is extracted from a large volume of Data.
37. Name the techniques of Data Mining
- Association of Rules
- Fuzzy Logic classification
- Decision Tree
- Neural Network
- Genetic Algorithms
38. What is a transaction?
Answer: A transaction can be defined as a group of tasks performed on a database. A transaction is a very small unit of the program and it may contain several low-level tasks.
39. Name all the properties of the Transaction
Answer: The transaction has four basic properties which are known as ASID
40. Define the Atomicity property of Transaction.
Answer: A Transaction is an atomic unit of processing which means a transaction must be treated as an atomic unit, which is either perform all operations or none.
41. Define the durability property of 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.
42. What is the default sorting method of the ORDER BY clause?
Answer: By default ORDER BY following the ASC or Ascending sorting.
43. 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.
E.g for BETWEEN and IN
SELECT * FROM Employee WHERE emp_id BETWEEN 20 AND 30; SELECT * FROM Employee WHERE emp_id IN (30,34,37,38);
44. Write an SQL query to select all employees details whose name start with A
SELECT * FROM Employees WHERE emp_name like 'A%';
45. What is the difference between a NULL value and Zero?
Answer: A NULL value is used to represent those values that are not assigned or unavailable whereas Zero is an Integer that possesses a value.
46. Write a query to count all the records in the table student.
SELECT COUNT(*) FROM student;
47. Name all the Operators in the SQL.
- Arithmetic Operators
- Logical Operators
- Comparison operators
48. Write a query to show all the distinct cities from the table employees.
SELECT DISTINCT city FROM employees;
49. Name all the string functions in SQL.
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.
SELECT student_id AS id FROM student;
Consider These two Sample tables for further queries:
+------+--------+------+--------+-------+----------+ | 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 | +------+--------+------+--------+-------+----------+
+---------+-------------------+------------+ | 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.
SELECT Students.name, count(Students.name) as Books FROM Students JOIN LIBRARY ON Students.id = Library.Student_Id GROUP BY name;
+--------+-------+ | 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.
SELECT * FROM Students WHERE name LIKE "%i%";
+------+-------+------+--------+-------+----------+ | 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 which contains the details of the top 3 students who scored the highest marks.
CREATE VIEW top_student AS SELECT * FROM Students ORDER BY marks DESC LIMIT 3;
55. Write the query to delete the view top_student.
DROP VIEW top_student
56. How to write a comment in SQL RDBMS?
Answer: In SQL comments can be written with the help of /* symbol
/* This is a comment and SQL engine will not execute this statement */
57. Write a query to create a new column “phone” in the Students table.
ALTER TABLE Students ADD Phone VARCHAR(10);
58. Write a query to delete the column “phone” from the table students.
ALTER TABLE Students DROP Phone;
59. Show the details of those students who have borrowed more than 1 book from the library.
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;
+------+--------+------+-------+ | id | Name | age | BOOKS | +------+--------+------+-------+ | 2 | Naruto | 18 | 2 | | 6 | Robin | 20 | 3 | +------+--------+------+-------+
SQL always remains in demand and uses, right now mostly all the dynamic web-application use a SQL Database Management system to manage their application content and data. It does not matter which programming language you use there is an integration present for every programming language which helps in creating and managing SQL database. For every developer, SQL is an essential skill set, in many interviews, the interviewer put on from this topic. So we recommend you, go through these SQL Interview Questions before you appear in the 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: