sql Interview Questions and Answers 2024

Administration1HJ5654$%^#$ / 20 Apr, 2024

Q1.  What is SQL?

SQL means Structured Query Language and is used to

 communicate with relational databases. It proposes a

standardized way to interact with databases, allowing

users to perform various operations on the data, including

 retrieval, insertion, updating, and deletion.

Q2. What are Constraints in SQL?

Constraints are used to specify the rules concerning

data in the table. It can be applied for single or multiple

 fields in an SQL table during the creation of the table

 or after creating using the ALTER TABLE command.

 The constraints are:

• NOT NULL - Restricts NULL value from being inserted into a column.
• CHECK - Verifies that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has
• been specified for the field.
• UNIQUE - Ensures unique values to be inserted into the field.
• INDEX - Indexes a field providing faster retrieval of records.
• PRIMARY KEY - Uniquely identifies each record in a table.
• FOREIGN KEY - Ensures referential integrity for a record in another table.

Q3. What are the different subsets of SQL?

• Data Definition Language (DDL) – It allows you to

perform various operations on the database such as

 CREATE, ALTER, and DELETE objects.

• Data Manipulation Language(DML) – It allows you

to access and manipulate data. It helps you to insert,

update, delete and retrieve data from the database.

• Data Control Language(DCL) – It allows you to

control access to the database. Example – Grant,

Revoke access permissions.

Q4.  What is the SELECT statement?

A SELECT command gets zero or more rows from one

or more database tables or views. The most frequent data

 manipulation language (DML) command is SELECT in

 most applications. SELECT queries define a result set,

but not how to calculate it, because SQL is a declarative

 programming language.

Q5. What are some common clauses used with SELECT query in SQL?

The following are some frequent SQL clauses used in

 conjunction with a SELECT query:

WHERE clause: In SQL, the WHERE clause is used to

 filter records that are required depending on certain criteria.
ORDER BY clause: The ORDER BY clause in SQL is used

to sort data in ascending (ASC) or descending (DESC) order

depending on specified field(s) (DESC).
GROUP BY clause: GROUP BY clause in SQL is used to group

 entries with identical data and may be used with aggregation

methods to obtain summarised database results.
HAVING clause in SQL is used to filter records in combination

with the GROUP BY clause. It is different from WHERE, since

the WHERE clause cannot filter aggregated records.

Q6.  What is a primary key in SQL?

It is a unique identifier for each record in a table. It ensures that

each row in the table has a distinct and non-null value in the

primary key column. Primary keys enforce data integrity and

create relationships between tables.

Q7.  Explain the difference between DELETE and TRUNCATE commands.

The DELETE command is used by professionals to remove

 particular rows from a table based on a condition, allowing

 you to selectively delete records. TRUNCATE, on the other

 hand, removes all rows from a table without specifying conditions

. TRUNCATE is faster and uses fewer system resources than

 DELETE but does not log individual row deletions.

Q8. How do you use the WHERE clause?

The WHERE clause within SQL queries serves the purpose

 of selectively filtering rows according to specified conditions,

thereby enabling you to fetch exclusively those rows that align

 with the criteria you define. For example:

SELECT * FROM employees WHERE department = 'HR';

Q9.  What are UNION, MINUS and INTERSECT commands?

The UNION operator is used to combine the results of two

tables while also removing duplicate entries.
The MINUS operator is used to return rows from the first

 query but not from the second query.
The INTERSECT operator is used to combine the results

 of both queries into a single row.
Before running either of the above SQL statements, certain

requirements must be satisfied –
Within the clause, each SELECT query must have the same

 amount of columns.
The data types in the columns must also be comparable.
In each SELECT statement, the columns must be in the same order.

Q10. What are the subsets of SQL?

SQL queries are divided into four main categories:

• Data Definition Language (DDL)
  DDL queries are made up of SQL commands that can

 be used to define the structure of the database and modify it.

• CREATE: Used to create databases, tables, indexes,

 views, and other database objects.

• DROP: Used to delete databases, tables, views, and

 other database objects.

• ALTER TABLE … DROP COLUMN: Used to remove a

column from an existing table.

• ALTER: Used to modify the structure of an existing table,

such as adding, deleting, or modifying columns.

• TRUNCATE: Used to remove all records from a

table but retains the table’s structure for future use.

• ALTER TABLE … ADD COLUMN: Used to add

a new column to an existing table.

• Data Manipulation Language (DML)
  These SQL queries are used to manipulate data in a database.
• SELECT INTO: Selects data from one table

and inserts it into another

• INSERT: Inserts data or records into a table
• UPDATE: Updates the value of any record in the database
• DELETE: Deletes records from a table
• Data Control Language (DCL)
  These SQL queries manage the access rights and

permission control of the database.

• GRANT:Grants access rights to database objects
• REVOKE:Withdraws permission from database objects
• Transaction Control Language (TCL)
  TCL is a set of commands that essentially manages the

 transactions in a database and the changes made by the

 DML statements. TCL allows statements to be grouped

 together into logical transactions.

• COMMIT:
• Commits an irreversible transaction, i.e., the previous

 image of the database before the transaction cannot be

 retrieved. When a transaction is committed using the COMMIT

 statement in SQL, it permanently saves the changes made

within the transaction to the database. Once committed, the

 changes cannot be rolled back or undone, and the previous state

of the database before the transaction cannot be retrieved

without restoring from a backup or utilizing other data recovery

methods.

• ROLLBACK:Reverts the steps in a transaction in case of an error
• SAVEPOINT:Sets a savepoint in the transaction to

which rollback can be executed

• SET TRANSACTION:Sets the characteristics of the transaction

Q11. What is an SQL alias?

An SQL alias serves as a transitory label bestowed upon either

a table or a column within a query, with the primary purpose

of enhancing the clarity of query outcomes or simplifying the

process of renaming columns for improved referencing.

For example:

SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees;

Q12. How to sort records in a table?

Using the ORDER BY statement:

SELECT * FROM table_name

ORDER BY col_1;

Q13. What is a Join? List its different types.

The SQL Join clause is used to combine records (rows)

from two or more tables in a SQL database based on a

 related column between the two.

There are four different types of JOINs in SQL:

• (INNER) JOIN: Retrieves records that have matching

values in both tables involved in the join. This is the widely

 used join for queries.

SELECT *

FROM Table_A

JOIN Table_B;

SELECT *

FROM Table_A

INNER JOIN Table_B;

• LEFT (OUTER) JOIN: Retrieves all the records/rows from

 the left and the matched records/rows from the right table.

SELECT *

FROM Table_A A

LEFT JOIN Table_B B

ON A.col = B.col;

• RIGHT (OUTER) JOIN: Retrieves all the records/rows from

 the right and the matched records/rows from the left table.

SELECT *

FROM Table_A A

RIGHT JOIN Table_B B

ON A.col = B.col;

• FULL (OUTER) JOIN: Retrieves all the records where there

 is a match in either the left or right table.

SELECT *

FROM Table_A A

FULL JOIN Table_B B

ON A.col = B.col;

Q 14. How to get the count of records in a table?

Using the COUNT() aggregate function with the

asterisk passed as its argument: SELECT COUNT(*)

 FROM table_name;.

Q15.  List the different types of relationships in SQL.

There are different types of relations in the database:
One-to-One – This is a connection between two tables in

 which each record in one table corresponds to the maximum

of one record in the other.

One-to-Many and Many-to-One – This is the most frequent

 connection, in which a record in one table is linked to

 several records in another.

Many-to-Many – This is used when defining a relationship

that requires several instances on each sides.

Self-Referencing Relationships – When a table has to

 declare a connection with itself, this is the method to employ.

Q16. What are SQL operators?

SQL operators are the special keywords or characters

that perform specific operations. They are also used in

SQL queries. These operators can be used within the WHERE

clause of SQL commands. Based on the specified condition,

 SQL operators filter the data.

The SQL operators can be categorized into the following types:

• Arithmetic Operators:For mathematical operations on numerical data
• addition (+)
• subtraction (-)
• multiplication (*)
• division (/)
• remainder/modulus (%)
• Logical Operators: For evaluating the expressions and

returning results in True or False

• ALL
• AND
• ANY
• ISNULL
• EXISTS
• BETWEEN
• IN
• LIKE
• NOT
• OR
• UNIQUE
• Comparison Operators: For comparisons of two values

 and checking whether they are the same or not

• equal to (=)
• not equal to (!= or <>)
• less than (<),
• greater than (>;)
• less than or equal to (&<=)
• greater than or equal to (>=)
• not less than (!<)
• not greater than (!>)
• Bitwise Operators: For bit manipulations between two

expressions of integer type. It first performs the conversion

 of integers into binary bits and then applied operators

• AND (& symbol)
• OR (|, ^)
• NOT (~)
• Compound Operators: For operations on a variable

 before setting the variable’s result to the operation’s result

• Add equals (+=)
• subtract equals (-=)
• multiply equals (*=)
• divide equals (/=)
• modulo equals (%=)
• String Operators: For concatenation and pattern

matching of strings

• + (String concatenation)
• += (String concatenation assignment)
• % (Wildcard)
• [] (Character(s) matches)
• [^] (Character(s) not to match)
• _ (Wildcard match one character)
• 
  
• 
  

Q17. What is the difference between SQL and MySQL?

Q18.  What is a Query?

A query is a request for data or information from a database

 table or combination of tables. A database query can be

either a select query or an action query.

SELECT fname, lname    /* select query */

FROM myDb.students

WHERE student_id = 1;

UPDATE myDB.students    /* action query */

SET fname = 'Captain', lname = 'America'

WHERE student_id = 1;

Q19. What is a View?

A view in SQL is a virtual table based on the result-set of

 an SQL statement. A view contains rows and columns, just

like a real table. The fields in a view are fields from one or

 more real tables in the database.

Q20. What is a join?

A clause used to combine and retrieve records from two

or multiple tables. SQL tables can be joined based on the

 relationship between the columns of those tables.

Q21. What is an index?

A special data structure related to a database table and used

 for storing its important parts and enabling faster data search

 and retrieval. Indexes are especially efficient for large databases,

where they significantly enhance query performance.

Q22. How to create a table?

Using the CREATE TABLE statement. For example, to create

 a table with three columns of predefined datatypes, we apply

 the following syntax:

CREATE TABLE table_name (col_1 datatype,

                         col_2 datatype,

                         col_3 datatype);

Q23. How to update a table?

Using the UPDATE statement. The syntax is:

UPDATE table_name

SET col_1 = value_1, column_2 = value_2

WHERE condition;

Q24. How to delete a table from a database?

Using the DROP TABLE statement.

The syntax is: DROP TABLE table_name;.

Q25.  What is a Self-Join?

A self JOIN is a case of regular join where a table is joined to

 itself based on some relation between its own column(s).

Self-join uses the INNER JOIN or LEFT JOIN clause and

a table alias is used to assign different names to the table within the query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",

B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"

FROM employee A, employee B

WHERE A.emp_sup = B.emp_id;

Q26. What is the DISTINCT statement and how do you use it?

This statement is used with the SELECT statement to filter

 out duplicates and return only unique values from a column

 of a table. The syntax is:

SELECT DISTINCT col_1

FROM table_name;

Q27. What are aggregate functions? Can you name a few?

Aggregate functions in SQL perform calculations on a set of

 values and return a single result.

• SUM: To calculate the sum of values in a column.
• COUNT: To count a column's number of rows or non-null values.
• AVG: To calculate the average of values in a column.
• MIN: To retrieve the minimum value in a column.
• MAX: To retrieve the maximum value in a column.

Q28.  How do you optimize SQL queries?

SQL query optimization involves improving the performance

of SQL queries by reducing resource usage and execution time.

Strategies include using appropriate indexes, optimizing query

structure, and avoiding costly operations like full table scans.

Q29. What is the difference between DROP and TRUNCATE

 statements?

If a table is dropped, all things associated with the tables are

 dropped as well. This includes - the relationships defined on

 the table with other tables, the integrity checks and constraints,

 access privileges and other grants that the table has. To create

 and use the table again in its original form, all these relations,

checks, constraints, privileges and relationships need to be redefined.

However, if a table is truncated, none of the above problems exist and

 the table retains its original structure.

Q30. What is the difference between CHAR and VARCHAR2

 datatype in SQL? 

Both of these data types are used for characters, but varchar2 is used

 for character strings of variable length, whereas char is used for character

 strings of fixed length. For example, if we specify the type as char(5) then

we will not be allowed to store a string of any other length in this variable,

 but if we specify the type of this variable as varchar2(5) then we will be

 allowed to store strings of variable length. We can store a string of length

3 or 4 or 2 in this variable.

Q31. What is normalization?

It is a process of analyzing the given relation schemas based on their

functional dependencies and primary keys to achieve the following

 desirable properties: 

1.      Minimizing Redundancy

2.      Minimizing the Insertion, Deletion, And Update Anomalies

Relation schemas that do not meet the properties are decomposed into

 smaller relation schemas that could meet desirable properties. 

Q32. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single

result for each group. COUNT(), MAX(), MIN(), SUM(), AVG(),

and VARIANCE() are some of the most widely used group functions.

Q33. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a

unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary field

is added as the AUTO_INCREMENT field so that it is incremented

when a new record is inserted.

The AUTO-INCREMENT value starts at 1 and is incremented by 1

 whenever a new record is inserted.

Syntax:

Q34. What is Denormalization?

Denormalization is a database optimization technique in which we

add redundant data to one or more tables. This can help us avoid costly

 joins in a relational database. Note that denormalization does not mean

 not doing normalization. It is an optimization technique that is applied

 after normalization. 

In a traditional normalized database, we store data in separate logical tables

 and attempt to minimize redundant data. We may strive to have only one

copy of each piece of data in the database. 

Q35. What is a Constraint?

Constraints are the rules that we can apply to the type of data in a table.

That is, we can specify the limit on the type of data that can be stored

in a particular column in a table using constraints. 

Q36. How to remove duplicate rows in SQL?

If the SQL table has duplicate rows, the duplicate rows must be removed.

Let’s assume the following table as our dataset:

The following SQL query removes the duplicate ids from the  table:

DELETE FROM table WHERE ID IN (
SELECT 
ID, COUNT(ID) 
FROM   table
GROUP BY  ID
HAVING 
COUNT (ID) > 1); 

Q37.  Write a SQL query to display the current date?

In SQL, there is a built-in function called GetDate() which helps to

 return the current timestamp/date.

Q38. What is the default data ordering with the ORDER BY statement,

 and how do you change it?

By default, the order is ascending. To change it to descending, we

need to add the DESC keyword as follows:

SELECT * FROM table_name

ORDER BY col_1 DESC;

Q39. What is the difference between a primary key and a unique key?

While both types of keys ensure unique values in a column of a table,

the first one identifies uniquely each record of the table, and the second

 one prevents duplicates in that column.

Q40. What is the default ordering of data using the ORDER BY clause?

How could it be changed?

The ORDER BY clause in MySQL can be used without the ASC or DESC

modifiers. The sort order is preset to ASC or ascending order when this

 attribute is absent from the ORDER BY clause.