50 Top SQL Interview Questions and Answers

Posted in /   /  

50 Top SQL Interview Questions and Answers
vinaykhatri

Vinay Khatri
Last updated on March 19, 2024

    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:

    1. DDL (Data Definition Language)
    2. DML (Data Manipulation Language)
    3. DCL (Data Control Language)
    4. 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:

    1. A tomicity
    2. C onsistency
    3. I solation
    4. 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:

    FAQs


    Once you learn and master the SQL language and gain an in-depth understanding of databases and RDBMS, keep practicing what you learned to develop proficiency. In addition, you can refer to commonly asked SQL interview questions, as mentioned in this article. These questions will help you brush up on your knowledge and ace your interviews.

    If you opt for the traditional path, make sure to have a bachelor's degree in CS or IT. Otherwise, choose a comprehensive online course or Bootcamp that helps you develop basic knowledge of CS fundamentals, databases, and SQL. Certify your SQL skills with certifications. Work as an intern to gain practical knowledge and experience. Finally, apply for the job position as an SQL developer in different companies.

    Some essential or must-have skills of an SQL developer include SQL, databases, T-SQL, stored procedures, SSIS, SSRS, ETL, operating systems, data warehouse, and data analysis.

    According to glassdoor, an SQL developer in India makes an average salary of INR 4.5 lakhs per year, while, in the United States, it is $93K per annum.

    Yes, SQL is an easy-to-learn query language. The syntax of SQL includes simple English keywords that make it an easily understandable language.

    Leave a Comment on this Post

    0 Comments