30+ SQL Interview Questions

Author: neptune | 05th-Jan-2023
#Interview #SQL

Q 1. What are the subsets of SQL? Explain them. 

The following are the three subsets of SQL: 

  • Data Definition Language (DDL) – It allows end-users to CREATE, ALTER, and DELETE database objects.

  • Data Manipulation Language (DML) – With this, you can access and manipulate data. It allows you to Insert, Update, Delete, and Retrieve data from the database.

  • Data Control Language (DCL) – This lets you control access to the database. It includes the Grant and Revoke permissions to manipulate or modify the database.

Q 2. What is the primary key?

1. A primary key constraint uniquely identifies each row/record in a database table. 

2. Primary keys must contain unique values.

3. Null value and duplicate values are not allowed to be entered in the primary key column.

4. A table can have only one primary key. It can consist of single or multiple fields.


Q 3. What is a foreign key?

A foreign key (referencing key) is used to link two tables together. It is a column or a combination of columns whose values match a Primary Key in a different table. 

It acts as a cross-reference between tables because it references the primary key of another table and established a link between them.

Q 4. Explain the different types of indexes in SQL.

There are three types of indexes in SQL:

1. Unique Index – It does not allow a field to have duplicate values if the column is unique indexed.

2. Clustered Index – This index defines the order in which data is physically stored in a table. It reorders the physical order of the table and searches based on key values. There can be only one clustered index per table.

3. Non-Clustered Index – It does not sort the physical order of the table and maintains a logical order of the data. Each table can have more than one non-clustered index.


Q 5. What is the difference between TRUNCATE and DELETE?

This is one of the most commonly asked SQL interview questions.

DELETE

TRUNCATE

Delete command is used to delete a specified row in a table.

Truncate is used to delete all the rows from a table.

You can roll back data after using the delete statement.

You cannot roll back data.

It is a DML command.

It is a DDL command.

It is slower than a truncate statement.

It is faster.

Q 6. What is the difference between below cmds.

SELECT * FROM MyTable WHERE MyColumn <> NULL

SELECT * FROM MyTable WHERE MyColumn IS NULL

The first syntax will not work because NULL means ‘no value’, and you cannot use scalar value operators. This is why there is a separate IS – a NULL predicate in SQL.


Q 7. Can you name different types of MySQL commands?

SQL commands are divided into the following categories –

  • Data Definition Language (DDL)

  • Data Manipulation Language (DML)

  • Data Control Language (DCL)

  • Transaction Control Language (TCL)

Q 8. Explain different DDL commands in MySQL.

DDL commands include –

  • CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.

  • DROP – Used to delete objects

  • ALTER – Used to change database structures

  • TRUNCATE – It erases all records from a table, excluding its database structure

  • COMMENT – Used to add comments to the data dictionary

  • RENAME – Used to rename a database object


Q 9. Explain different DML commands in MySQL.

This is one of the most popularly asked SQL interview questions.

DML commands include –

  • SELECT – Used to select specific database data

  • INSERT – Used to insert new records into a table

  • UPDATE – It helps in updating existing records

  • DELETE – Used to delete existing records from a table

  • MERGE – Used to UPSERT operation (insert or update)

  • CALL – It is used when you need to call a PL/SQL or Java subprogram

  • EXPLAIN PLAN – Used to interpret data access path

  • LOCK TABLE – Used to control concurrency

Q 10. Explain different DCL commands in MySQL.

DCL commands are –

  • GRANT – It provides user access privileges to the database

  • DENY – Used to deny permissions to users

  • REVOKE – Used to withdraw user access by using the GRANT command

Q 11. Explain different TCL commands in MySQL.

DCL commands include –

  • COMMIT – Used to commit a transaction

  • ROLLBACK – Used to roll back a transaction

  • SAVEPOINT – Used to roll back the transaction within groups

  • SET TRANSACTION – Used to specify transaction characteristics


Q 12. What are the different types of Database relationships in MySQL?

There are three types of Database Relationship –

  • One-to-one – Both tables can have only one record

  • One-to-many – The single record in the first table can be related to one or more records in the second table

  • Many-to-many – Each record in both the tables can be related to any number of records

Q 13. What is Normalization?

Normalization is a database design technique to organize tables to reduce data redundancy and data dependency.

Q 14. What are the different types of Normalization?

There are six different types of Normalization –

  • First Normal Form (1NF)

  • Second Normal Form (2NF)

  • Third Normal Form (3NF)

  • Boyce-Codd Normal Form (BCNF)

  • Fourth Normal Form (4NF)

  • Fifth Normal Form (5NF)


Q 15. How many TRIGGERS are allowed in the MySQL table?

Ans: 6 triggers are allowed in the MySQL table.

  • BEFORE INSERT

  • AFTER INSERT

  • BEFORE UPDATE

  • AFTER UPDATE

  • BEFORE DELETE 

  • AFTER DELETE

Q 16. What is a constraint, and how many levels of constraints are there?

Constraints are the representation of a column to enforce data entity and consistency. 

There are two levels of constraint – 

  • Column level – Limits only column data

  • Table level – Limits whole table data

Following are the most used constraints that can be applied to a table:

  • NOT NULL

  • UNIQUE

  • CHECK

  • DEFAULT

  • PRIMARY KEY

  • FOREIGN KEY

Q 17. What is Data Integrity?

Data integrity attributes to the accuracy, completeness, and consistency of the data in a database. It also refers to the safety and security of data and is maintained by a collection of processes, rules, and standards that were implemented during the design phase. 

Three types of data integrity are:

  • Column Integrity

  • Entity Integrity

  • Referential Integrity


Q 18. What is Referential Integrity?

Referential integrity is a relational database concept that suggests that the accuracy and consistency of data should be maintained between primary and foreign keys.

Q 19. What is the ACID property in SQL?

ACID is short for Atomicity, Consistency, Isolation, Durability

It ensures Data Integrity during a transaction.

Atomicity: It means either all the operations (insert, update, delete) inside a transaction take place or none.

Consistency: Consistency ensures that the data must meet all the validation rules. Irrespective of whatever happens in the middle of the transaction, Consistency property will never leave your database in a half-completed state.

Isolation: It means that every transaction is individual. One transaction can’t access the result of other transactions until the transaction is completed. 

Durability: It implies that maintaining updates of committed transactions is important. It refers to the ability of the system to recover committed transaction in case of failure.


Q 20. Explain string functions in SQL?

SQL string functions are used for string manipulation.

Following are the extensively used SQL string functions:

  • UPPER(): Converts character data to upper case

  • LOWER(): Converts character data to lower case

  • SUBSTRING() : Extracts characters from a text field

  • RTRIM(): Removes all whitespace at the end of the string

  • LEN(): Returns the length of the value in a text field

  • REPLACE(): Updates the content of a string.

  • LTRIM(): Removes all whitespace from the beginning of the string

  • CONCAT(): Concatenates function combines multiple character strings

Q 21. How can you create an SQL table from another table without copying any values from the old table?

Syntax

CREATE TABLE new_table

  AS (SELECT *

      FROM old_table WHERE 1=2);

This will create a new table with the same structure as the old table with no rows copied.


Q 22. Write the SQL query to update the student names by removing leading and trailing spaces.

This can be done by using ‘Update’ command with ‘LTRIM’ and ‘RTRIM’ function.

Syntax:

UPDATE StudentDetails 

SET FullName = LTRIM(RTRIM(FullName));

Q 23. Write the SQL query to fetch alternate records from a table

Records can be fetched for odd and even row numbers:

Syntax to fetch even numbers:

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0

Syntax to fetch odd numbers:

Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

Q 24. How do you return a hundred books starting from the 15th?

The syntax will be: 

SELECT book_title FROM books LIMIT 15, 100. 

The first number in LIMIT is the offset, and the second is the number.


Q 25. Name the default port for the MySQL server.

The default port for the MySQL server is 3306.

Q 26. How do you implement Enums and sets internally in MySQL? 

To implement an ENUM column, use the given syntax: 

CREATE TABLE table_name ( … col ENUM (‘value1′,’value2′,’value3’), … );

Q 27. What are the different groups of data types in MySQL?

There are three groups of data types in MySQL, as listed below:

String Data Types – BINARY, VARBINARY, TINYBLOB, CHAR, NCHAR, VARCHAR, NVARCHAR, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, LONGBLOB, LONGTEXT, ENUM, SET, MEDIUMTEXT.

Numeric Data Types – MEDIUMINT, INTEGER, BIGINT, FLOAT, BIT, TINYINT, BOOLEAN, SMALLINT, DOUBLE, REAL, DECIMAL.

Date and Time Data Types – TIMESTAMP, TIME, DATE, DATETIME, YEAR.


Q 28. What is the default storage engine in MySQL?

InnoDB is the default storage engine in MySQL.

Q 29. Explain the use of the NVL() function. 

The NVL() function converts the Null value to the other value.


Q 30. What is join in SQL?

Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

INNER JOIN − Returns rows when there is a match in both tables.

LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

FULL OUTER JOIN − Returns rows when there is a match in one of the tables.

SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Example of Inner Join:

SELECT table1.col1, table2.col2,…, table1.coln

FROM table1

INNER JOIN table2

ON table1.commonfield = table2.commonfield;

Similarly you can apply all joins.


Q 31. Difference between DBMS and RDBMS?

RDBMS: Data is stored in table format

DBMS: Data is stored in file format


RDBMS: Multiple data elements are accessible together

DBMS: Individual access of data elements is possible


RDBMS: Data in the form of a table are linked together

DBMS: There is no connection between the data


RDBMS: It supports multiple users

DBMS: It supports single user


RDBMS: It features multiple level of security while handling data

DBMS: There is low security while handling data


RDBMS: Example: Oracle, SQL Server

DBMS: Example: XML, Microsoft Access


These questions will definitely help you in preparing for interviews.

All the Best!!!





Related Blogs
5. Solution of Hacker Rank Weather Observation Station 8.
Author: neptune | 23rd-Jan-2023
#SQL #Hackerrank
Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates...

The Blunder | Hackerrank
Author: neptune | 21st-Nov-2022
#SQL #Hackerrank
Write a query calculating the amount of error (i.e.: average monthly salaries), and round it up to the next integer...

7.Solution of Hacker Rank The Report
Author: neptune | 23rd-Jan-2023
#SQL #Hackerrank
Problem Statement : generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8...

Where you applied OOPs in Automation Testing?
Author: neptune | 28th-Aug-2023
#Interview #Java
You may face this question Where you have applied OOPs concept in Automation Framework? in almost all the Selenium Interviews. Let's learn OOP’s concept in Java before going further...

4. Solution of Hacker Rank Weather Observation Station 6.
Author: neptune | 23rd-Jan-2023
#SQL #Hackerrank
Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates...

3. Solution of Hacker Rank Weather Observation Station 4.
Author: neptune | 23rd-Jan-2023
#SQL #Hackerrank
Problem Statement : Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table...

The PADS | Hackerrank
Author: neptune | 21st-Nov-2022
#SQL #Hackerrank
Problem Statement: Generate the following two result sets: 1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession...

6. Solution of Hacker Rank Employee Salaries.
Author: neptune | 23rd-Jan-2023
#SQL #Hackerrank
Problem Statement : Query that prints a list of employee names for employees in Employee having a salary greater than $2000 per month and experience less than 10 months...

1. Basic SQL Select Query of Hacker Rank.
Author: neptune | 20th-Apr-2022
#SQL #Hackerrank
Problem Statement : Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA...

2. Solution of Hacker Rank Weather Observation Station 3.
Author: neptune | 23rd-Jan-2023
#SQL #Hackerrank
Problem Statement : Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer...

Core Python Syllabus for Interviews
Author: neptune | 26th-Jul-2023
#Python #Interview
STRING MANIPULATION : Introduction to Python String, Accessing Individual Elements, String Operators, String Slices, String Functions and Methods...

Mostly asked Python Interview Questions - 2023.
Author: neptune | 30th-May-2023
#Python #Interview
Python interview questions for freshers. These questions asked in 2022 Python interviews...

Top 50+ Selenium Interviews Questions 2023 based on Years of Experience
Author: neptune | 02nd-Apr-2023
#Selenium #Testing #Interview
Every interview difficulty is based on how many years of experience you have in that field. For the Selenium Automation Tester I have divided the question on the number of years of experience...

Top 10 Selenium Interview Questions with answers (2021).
Author: neptune | 02nd-Apr-2023
#Selenium #Interview
In this article I will cover top 10 Selenium interview questions...

20 TestNG Interview Questions
Author: neptune | 17th-Dec-2022
#Selenium #Interview
There is always more than one test or method in the class. If we do not prioritize these tests or methods, then the methods are selected alphabetically and executed while execution...

Top 10 Selenium Interview Questions.
Author: neptune | 02nd-Apr-2023
#Selenium #Interview
Locator is a command that tells Selenium IDE which GUI elements (like Text Box, Buttons, Check Boxes etc) we are going to use or perform some automation task...

The Key to QA Success: Understanding How Important Grooming Is?
Author: neptune | 19th-Sep-2023
#Testing #Interview
We will delve into the importance of grooming & ceremony for QA testers, key points to highlight their significance...

Black Mirror Season 6: A Glimpse into the Future of Technology and Society
Author: neptune | 27th-Apr-2023
#Interview
Black Mirror Season 6, starring Salma Hayek and Aaron Paul, promises more violence and thought-provoking explorations of technology and society...

Top 20+ Appium Interview Questions and Answers (2023)
Author: neptune | 30th-May-2023
#Interview
This article provides a comprehensive list of 20 common interview questions on Appium mobile automation, covering various topics and providing solutions for each question...

Skills Required for Full-Stack Developer at IBM Onsite, CA
Author: neptune | 25th-Feb-2024
#Interview #Jobs
The company's commitment to pushing the boundaries of what is possible necessitates a team of skilled professionals...

What is a Module in Node.js?
Author: neptune | 20th-Jun-2024
#Interview #Node.js
In Node.js, a module is a reusable block of code whose existence does not accidentally impact other code...

View More