Top 45 Sql Interview Questions You Must Prepare 19.Mar.2024

A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. The stored procedure accepts input parameters and processes them and returns a single value such as a number or text value or a result set (set of rows).

SQL constraints are the set of rules that enforced some restriction while inserting, deleting or updating of data in the databases.

TCL commands are used to manage the changes made by DML statements.

COMMIT: To write and store the changes to the database

ROLLBACK: To restore the database since the last commit

Advantages of Views:

Views restrict access to the data because the view can display selective columns from the table.

Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing.

RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed using Structured Query Language (SQL)

  • A field in a Database table is a space allocated to store a particular record within a table.
  • A record (also called a row of data) is an ordered set of related data in a table.

SQL commands are segregated into following types:

  • DDL – Data Definition Language
  • DML – Data Manipulation Language
  • DQL – Data Query Language
  • DCL – Data Control Language
  • TCL – Transaction Control Language

  • One table can have only one clustered index but multiple nonclustered indexes.
  • Clustered indexes can be read rapidly rather than non-clustered indexes.
  • Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row

  • A UNIQUE KEY constraint is used to ensure that there are no duplication values in the field/column.
  • A PRIMARY KEY constraint uniquely identifies each record in a database table. All columns participating in a primary key constraint must not contain NULL values.
  • A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table is linked with the PRIMARY KEY of another table.

SQL string functions are used primarily for string manipulation.

Some of the widely used SQL string functions are:

  • LEN() : It returns the length of the value in a text field
  • LOWER() : It converts character data to lower case
  • UPPER() : It converts character data to upper case
  • SUBSTRING() : It extracts characters from a text field
  • LTRIM() : It is to remove all whitespace from the beginning of the string
  • RTRIM() : It is to remove all whitespace at the end of the string
  • CONCAT() : Concatenate function combines multiple character strings together
  • REPLACE() :To update the content of a string.

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.

SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments.  The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.

GRANT Command: This command is used provide database access to user apart from an administrator.

Syntax: GRANT privilege_name

ON object_name

TO {user_name|PUBLIC|role_name}

[WITH GRANT OPTION];

In above syntax WITH GRANT OPTIONS indicates that the user can grant the access to another user too.

REVOKE Command: This command is used provide database deny or remove access to database objects.

Syntax: REVOKE privilege_name

ON object_name

FROM {user_name|PUBLIC|role_name};

SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

Normalization is the process of table design to minimize the data redundancy.

There are different types of Noramalization forms in SQL:-

  • First Normal Form (1NF): It removes all duplicate columns from the table. Creates table for related data and identifies unique column values
  • First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines relationship between tables using primary key
  • Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key
  • Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as

Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory and cannot create files.

External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.

Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with ‘GROUP BY’ to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can.

DML commands are used for managing data present in the database.

SELECT: To select specific data from a database

INSERT: To insert new records into a table

UPDATE: To update existing records

DELETE: To delete existing records from a table

A table is a database object used to store records in a field in the form of columns and rows that holds data.

The relationship can be defined as the connection between more than one tables in the database.

There are 4 types of relationships:

  • One to One Relationship
  • Many to One Relationship
  • Many to Many Relationship
  • One to Many Relationship

SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP’s), execute queries, retrieve, insert, update, delete data against a database.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee Payroll, Employee Time Sheet etc.,

Database White Box Testing involves:

  • Database Consistency and ACID properties
  • Database triggers and logical views
  • Decision Coverage, Condition Coverage, and Statement Coverage
  • Database Tables, Data Model, and Database Schema
  • Referential integrity rules

Database Black Box Testing involves:

  • Data Mapping
  • Data stored and retrieved
  • Use of Black Box techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)

A schema is a collection of database objects of a User.

Types of Joins are as follows:

INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one column matched

Syntax: SELECT column_name(s) 

FROM table_name1 

INNER JOIN table_name2 

ON column_name 1=column_name 2;

LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from a LEFT table and its matched rows from a RIGHT table.

Syntax: SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON column_name 1=column_name 2;

RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from a LEFT table.

Syntax: SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON column_name1=column_name2;

FULL JOIN (FULL OUTER JOIN): This joins returns all when there is a match either in the RIGHT table or in the LEFT table.

Syntax: SELECT column_name(s)

FROM table_name1

FULL OUTER JOIN table_name2

ON column_name1=column_name2;

Collation is set of rules that check how the data is sorted by comparing it. Such as Character data is stored using correct character sequence along with case sensitivity, type, and accent.

DCL commands are used to create roles, grant permission and control access to the database objects.

GRANT: To provide user access

DENY: To deny permissions to users

REVOKE: To remove user access

NVL function is used to convert the null value to its actual value.

Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.

A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query.

A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.

There should be only one PRIMARY KEY in a table whereas there can be any number of UNIQUE Keys.

PRIMARY KEY doesn’t allow NULL values whereas Unique key allows NULL values.

DDL commands are used to define or alter the structure of the database.

CREATE: To create databases and database objects

ALTER: To alter existing database objects

DROP: To drop databases and databases objects

TRUNCATE: To remove all records from a table but not its database structure

RENAME: To rename database objects

Some of the constraints in SQL are : Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.

SQL aggregate functions return a single value, calculated from values in a column.

Some of the aggregate functions in SQL are as follows:

  • AVG() : This function returns the average value
  • COUNT() : This function returns the number of rows
  • MAX() : This function returns the largest value
  • MIN() : This function returns the smallest value
  • ROUND() : This function rounds a numeric field to the number of decimals specified
  • SUM() : This function returns the sum

‘Rename’ is a permanent name given to a table or column

‘Alias’ is a temporary name given to a table or column.

Following are the four properties of ACID. These guarantees that the database transactions are processed reliably.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

The difference between the Delete, Truncate and Drop command is:

  • Delete command is a DML command, it is used to delete rows from a table. It can be rolled back.
  • Truncate is a DDL command, it is used to delete all the rows from the table and free the space containing the table. It cant be rolled back.
  • Drop is a DDL command, it removes the complete data along with the table structure(unlike truncate command that removes only the rows). All the tables’ rows, indexes, and privileges will also be removed.

A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views and other database objects.

SQL is a structured query language that is used for manipulating and accessing the relational database, on the other hand, MySQL itself is a relational database that uses SQL as the standard database language.

There are two types of privileges used in SQL, such as

System Privilege: System privileges deal with an object of a particular type and specifies the right to perform one or more actions on it which include Admin allows a user to perform administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.

Object Privilege: This allows to perform actions on an object or object of another user(s) viz. table, view, indexes etc. Some of the object privileges are EXECUTE, INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX, REFERENCES etc.

Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,

Join is a query, which retrieves related columns or rows from multiple tables.

A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.