20 SQL QUESTIONS FOR INTERVIEW PREPARATION
Since RDBMS is one of the most widely used databases today, SQL skills are required for the majority of job roles. In this SQL Interview Questions blog, we will introduce you to the most frequently asked SQL questions (Structured Query Language).
This blog is an excellent resource for learning all of the SQL concepts.
Our Top 20 SQL Interview Questions blog is a one-stop resource for interview preparation.
1. What exactly is RDBMS? What distinguishes it from DBMS?
RDBMS (Relational Database Management System) is an abbreviation for Relational Database Management System. The primary distinction between RDBMS and DBMS is that RDBMS stores data in the form of a collection of tables, with relationships specified between the common fields of the tables. Most modern database management systems, including MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift, are built on RDBMS.
2. What exactly are SQL Constraints?
Constraints define the rules that govern the data in the table. It can be applied to single or multiple fields in a SQL table when the table is created or after the table has been created using the ALTER TABLE command. The following are the constraints:
NOT NULL – Prevents a NULL value from being inserted into a column.
CHECK – Determines whether all values in a field satisfy a set of criteria.
DEFAULT – If no value is specified for the field, a default value will be assigned to it.
UNIQUE – Ensures that the values in the field are unique.
INDEX – Indexes a field to speed up record retrieval.
PRIMARY KEY – Uniquely identifies each record in a table.
A FOREIGN KEY -guarantees the referential integrity of a record in another table.
3. What is the meaning of a primary key?
The PRIMARY KEY constraint uniquely identifies each row in a table. It includes a NOT NULL constraint and must include UNIQUE data.
A table in SQL can only have one primary key, which is made up of a single data column.
CREATE TABLE Students ( /* Create table with a single field as primary key */
ID INT NOT NULL
Name VARCHAR(255)
PRIMARY KEY (ID)
);
CREATE TABLE Students ( /* Create table with multiple fields as primary key */
ID INT NOT NULL
LastName VARCHAR(255)
FirstName VARCHAR(255) NOT NULL,
CONSTRAINT PK_Student
PRIMARY KEY (ID, FirstName)
);
ALTER TABLE Students /* Set a column as primary key */
ADD PRIMARY KEY (ID);
ALTER TABLE Students /* Set multiple columns as primary key */
ADD CONSTRAINT PK_Student /*Naming a Primary Key*/
PRIMARY KEY (ID, FirstName);
Create a SQL statement to insert the primary key ‘t id’ into the table ‘teachers.’
Create a SQL statement that adds the primary key constraint ‘pk a’ to the table ‘table a’ and the fields ‘col b, col c’.
4. What does it mean to be bound by a UNIQUE constraint?
A UNIQUE constraint ensures that each value in a column is distinct. This guarantees that the column(s) are distinct and that each row can be identified independently. In contrast to primary keys, each table can have a variety of constraints. The code syntax for UNIQUE is very similar to that of PRIMARY KEY and can be used interchangeably.
CREATE TABLE Students ( /* Create table with a single field as unique */
ID INT NOT NULL UNIQUE
Name VARCHAR(255)
);
CREATE TABLE Students ( /* Create table with multiple fields as unique */
ID INT NOT NULL
LastName VARCHAR(255)
FirstName VARCHAR(255) NOT NULL
CONSTRAINT PK_Student
UNIQUE (ID, FirstName)
);
ALTER TABLE Students /* Set a column as unique */
ADD UNIQUE (ID);
ALTER TABLE Students /* Set multiple columns as unique */
ADD CONSTRAINT PK_Student /* Naming a unique constraint */
UNIQUE (ID, FirstName);
5. What exactly is the meaning of normalisation?
The process of efficiently structuring structured data in a database is known as normalisation. It entails creating tables, as well as establishing relationships between them and defining rules for those interactions. These rules can keep inconsistency and redundancy under control, making the database more adaptable.
6. What Exactly Is Denormalization and How Does It Work?
Denormalization is the inverse of normalisation, transforming a normalised schema into one with redundant data. The use of redundancy and the retention of redundant data improves performance. The overheads in the query processor caused by an over-normalized structure are the motivation for denormalization.
7. What is the distinction between the statements DROP and TRUNCATE?
When a table is removed, everything associated with it is also removed. This includes the table’s associations with other tables, as well as the table’s integrity checks and restrictions, access privileges, and other grants. All of these relationships, checks, limitations, privileges, and associations must be redefined to recreate and use the table in its original form. When a table is truncated, none of the aforementioned issues arises, and the table retains its original structure.
8. What exactly is a Recursive Stored Procedure (RSP)?
A recursive stored procedure is one that calls itself repeatedly until a boundary condition is met. This recursive function allows programmers to deploy the same set of code multiple times as needed. Some SQL programming languages limit the depth of recursion to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may result in system crashes.
DELIMITER $$ /* Set a new delimiter => $$ */
CREATE PROCEDURE calctotal( /* Create the procedure */
IN number INT, /* Set Input and Ouput variables */
OUT total INT
) BEGIN
DECLARE score INT DEFAULT NULL; /* Set the default value => "score" */
SELECT awards FROM achievements /* Update "score" via SELECT query */
WHERE id = number INTO score;
IF score IS NULL THEN SET total = 0; /* Termination condition */
ELSE
CALL calctotal(number+1); /* Recursive call */
SET total = total + score; /* Action after recursion */
END IF;
END $$ /* End of procedure */
DELIMITER ; /* Reset the delimiter */
9. What exactly is a PL/SQL cursor?
A PL/SQL cursor is nothing more than a pointer to a memory area containing SQL statements and information about statement processing. This memory space is known as a context area. This special area makes use of a feature known as a cursor to retrieve and process more than one row.
In short, the cursor selects multiple rows from the database, and these rows are processed individually within a programme.
Cursors are classified into two types:
Implicit Cursor
When you run any of the commands SELECT INTO, INSERT, DELETE, or UPDATE, Oracle automatically creates a cursor.
Oracle handles the execution cycle of these cursors and returns the cursor’s information and status by using the cursor attributes ROWCOUNT, ISOPEN, FOUND, and NOTFOUND.
Explicit Cursor:
This cursor is a SELECT statement that was explicitly declared in the declaration block.
The programmer must control the cursors’ execution cycle from OPEN to FETCH and close.
Oracle defines the execution cycle while executing the SQL statement and associates a cursor with it.
The cycle of Explicit Cursor Execution:
Explicit cursors are used in many cases due to the flexibility of defining our own execution cycle.
Cursor Declaration:
The first step in using an explicit cursor is declaring it.
The declaration can take place in either a package or a block.
CURSOR cursor name IS query; where cursor name is the cursor’s name and the query is the query to retrieve data from any table.
Open Cursor:
Before fetching rows from the cursor, the cursor must be opened.
To open a cursor, use the following syntax: GO TO cursor name;
When the cursor is opened, Oracle parses the query and binds variables and executes the SQL statements.
Oracle determines the execution plan and the result set after associating the cursor parameters and host variables and posting these. The cursor is set to point at the first row of the result set.
Fetch from cursor:
The FETCH statement is used to store the current row’s contents in variables.
FETCH cursor name INTO variable list; To get all of the rows of a result set, each row must be fetched.
Close Cursor:
Once all of the rows have been retrieved, the cursor must be closed with the CLOSE statement.
CLOSE cursor name; is the syntax.
The instructions instruct Oracle to free the cursor’s memory.
Cursors declared in procedures or anonymous blocks are closed by default after they are executed.
Cursors declared in packages must be explicitly closed because the scope is global.
Closing a cursor that hasn’t been opened results in an INVALID CURSOR exception.
10. How can an unnamed PL/SQL Exception Block be given a name?
This is possible with the Pragma EXCEPTION INIT.
The programmer can instruct the compiler to provide custom error messages based on business logic by overriding the pre-defined messages during compilation time.
Syntax:
DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name, error_code);
BEGIN
// PL/SQL Logic
EXCEPTION
WHEN exception_name THEN
// Steps to handle exception
END;
11. How do you add comments to PL/SQL code?
Comments are sentences that have no effect on the functionality and are used to improve the readability of the code. They are classified into two types:
Single Line Comment: We can make this by using the symbol — and writing what we want to say as a comment next to it.
Multi-Line comment: These are comments that can be specified across multiple lines, with the syntax /* comment information */.
SET SERVEROUTPUT ON;
DECLARE
-- Hi There! I am a single line comment.
var_name varchar2(40) := 'I love PL/SQL' ;
BEGIN
/*
Hi! I am a multi line
comment. I span across
multiple lines
*/
dbms_output.put_line(var_name);
END;
/
Output:
I love PL/SQL
12. What virtual tables are available during the database trigger’s execution?
The virtual tables available during database trigger execution are the THEN and NOW tables. THEN.column and NOW.column are the names of the table columns.
For insert-related triggers, only the NOW.column is available.
For DELETE-related triggers, only THEN.column values are available.
UPDATE triggers are supported for both virtual table columns.
13. What is the distinction between the commands DELETE and TRUNCATE?
TRUNCATE is used to delete all of the rows from a table and free up the space they take up. The DELETE command deletes only the rows from the table that match the where clause criterion, or all rows if no condition is specified. It does not, however, free up the space occupied by the table.
14. What exactly is a user-defined function? What are the various types of it?
SQL user-defined functions function in the same way that functions in other programming languages do, accepting parameters, performing complex calculations, and returning a value. They’re written in such a way that the reasoning can be used again and again. SQL functions that are user-defined
SQL user-defined functions are classified into two types:
As previously stated, user-defined scalar functions return a single scalar value.
Functions with Table Values: Table-valued functions are user-defined functions that output a table.
Inline: returns a table data type based on a single SELECT statement.
Unlike inline, which returns a tabular result set, multiple SELECT statements can be used within the function body.
15. Distinguish between cursors declared in procedures and those declared in package specifications.
Cursors declared in procedures will have a local scope, and thus cannot be used by other procedure
a cursor declared in package specifications has global scope, which means it can be used and accessed by other procedures.
16. What are the differences between aggregate and scalar functions?
An aggregate function operates on a set of values to produce a single scalar value. Aggregate functions are frequently used in conjunction with the SELECT statement’s GROUP BY and HAVING clauses. The following are some of the most commonly used SQL aggregate functions:
AVG() – Calculates the mean of a collection of values.
COUNT() – Counts the total number of records in a specific table or view.
MIN() – Calculates the minimum of a collection of values.
MAX() – Calculates the maximum of a collection of values.
SUM() – Calculates the sum of a collection of values.
FIRST() – Fetches the first element in a collection of values.
LAST() – Fetches the last element in a collection of values.
Based on the input value, a scalar function returns a single value. The following are some of the most commonly used SQL scalar functions:
LEN() – Calculates the total length of the given field (column).
UCASE() – Converts a collection of string values to uppercase characters.
LCASE() – Converts a collection of string values to lowercase characters.
MID() – Extracts substrings from a collection of string values in a table.
CONCAT() – Concatenates two or more strings.
RAND() – Generates a random collection of numbers of a given length.
ROUND() – Calculates the round-off integer value for a numeric field (or decimal point values).
NOW() – Returns the current date & time.
FORMAT() – Sets the format to display a collection of values.
17. What exactly is a Recursive Stored Procedure (RSP)?
A recursive stored procedure is one that calls itself repeatedly until a boundary condition is met. This recursive function allows programmers to deploy the same set of code multiple times as needed. Some SQL programming languages limit the depth of recursion to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may result in system crashes.
DELIMITER $$ /* Set a new delimiter => $$ */
CREATE PROCEDURE calctotal( /* Create the procedure */
IN number INT, /* Set Input and Ouput variables */
OUT total INT
) BEGIN
DECLARE score INT DEFAULT NULL; /* Set the default value => "score" */
SELECT awards FROM achievements /* Update "score" via SELECT query */
WHERE id = number INTO score;
IF score IS NULL THEN SET total = 0; /* Termination condition */
ELSE
CALL calctotal(number+1); /* Recursive call */
SET total = total + score; /* Action after recursion */
END IF;
END $$ /* End of procedure */
DELIMITER ; /* Reset the delimiter */
18. What exactly is an OLTP database?
Online Transaction Processing (OLTP) refers to a class of software that can support transaction-oriented programmes. A critical feature of an OLTP system is its ability to maintain concurrency. To avoid single points of failure, OLTP systems are frequently dispersed. These systems are frequently designed for a large number of users who perform a small number of transactions. Database searches are frequently simple, with sub-second response times and a small number of returned records. Here’s an example of an OLTP system in action.
19. What exactly is a query?
A query is a request for information or data from a database table or tables. A database query can be of two types: select queries and action queries.
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;
20 . What are some of the most common SQL clauses used with SELECT queries?
Some SQL clauses that are commonly used with a SELECT query are as follows:
The WHERE clause in SQL is used to filter records that must meet certain criteria.
The SQL ORDER BY clause is used to sort data in ascending (ASC) or descending (DESC) order based on the field(s) specified (DESC).
SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;
The SQL GROUP BY clause is used to group entries with identical data and can be used in conjunction with aggregation procedures to provide summarised database results.
To filter records in SQL, the HAVING clause is used in conjunction with the GROUP BY clause. It differs from the WHERE clause in that it does not allow for the filtering of aggregated records.
SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != "INDIA"
GROUP BY country
HAVING COUNT(studentID) > 5;
This is the end of our topic. We hope we helped you in some way. The topics covered in this Core SQL Interview Questions blog are among the most sought-after skillsets for a SQL professional, according to recruiters. These SQL Interview Questions will undoubtedly assist you in acing your job interview.
Best wishes for your interview!
If you still have any queries, please let us know in the comment section.
