SQL Interview Questions and Answers

Last updated on 05th Oct 2020, Blog, Interview Question

E-mail this post

(5.0) | 14215 Ratings 1654

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for the Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

Also, they are using different dialects, such as −

MS SQL Server using T-SQL,

Oracle using PL/SQL,

MS Access version of SQL is called JET SQL (native format) etc.

Below we have listed the top 100 SQL interview questions & answers for fresher and experienced candidates to clear the job interview.

1. What is Database?

Ans:

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modeling approaches.

2. What is DBMS?

Ans:

DBMS stands for Database Management System. DBMS is a system software responsible for the creation, retrieval, updation and management of the database. It ensures that our data is consistent, organized and is easily accessible by serving as an interface between the database and its end users or application softwares.

3. What is RDBMS? How is it different from DBMS?

Ans:

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2 and Amazon Redshift are based on RDBMS.

4. What is SQL?

Ans:

SQL stands for Structured Query Language. It is the standard language for relational database management systems. It is especially useful in handling organized data consisting of entities (variables) and relations between different entities of the data.

5. What is the difference between SQL and MySQL?

Ans:

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

6. What are Tables and Fields?

Ans:

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

7. What are Constraints in SQL?

Ans:

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 creation of table or after creation 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.

8. What is a Primary Key?

Ans:

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.

A table in SQL is strictly restricted to have one and only one primary key, which consists of single or multiple fields (columns).

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);

9. What is a UNIQUE constraint?

Ans:

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike the primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite 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);

10. What is a Foreign Key?

Ans:

A FOREIGN KEY comprises a single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

CREATE TABLE Students ( /* Create table with foreign key – Way 1 */
ID INT NOT NULL
Name VARCHAR(255)
LibraryID INT
PRIMARY KEY (ID)
FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);
CREATE TABLE Students ( /* Create table with foreign key – Way 2 */
ID INT NOT NULL PRIMARY KEY
Name VARCHAR(255)
LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);
ALTER TABLE Students /* Add a new foreign key */
ADD FOREIGN KEY (LibraryID)
REFERENCES Library (LibraryID);

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

Ans:

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;

12. What is a Self-Join?

Ans:

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;

Subscribe For Free Demo

Error: Contact form not found.

13. What is a Cross-Join?

Ans:

Cross join can be defined as a cartesian product of the two tables included in the join. The table after join contains the same number of rows as in the cross-product of the number of rows in the two tables. If a WHERE clause is used in cross join then the query will work like an INNER JOIN.

SELECT stu.name, sub.subject
FROM students AS stu
CROSS JOIN subjects AS sub;

14. What is an Index? Explain its different types.

Ans:

A database index is a data structure that provides quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.

CREATE INDEX index_name /* Create Index */
ON table_name (column_1, column_2);
DROP INDEX index_name; /* Drop Index */

There are different types of indexes that can be created for different purposes:

Unique and Non-Unique Index:

Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.

CREATE UNIQUE INDEX myIndex
ON students (enroll_no);

Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently.

Clustered and Non-Clustered Index:

Clustered indexes are indexes whose order of the rows in the database correspond to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas multiple non-clustered indexes can exist in the table.
The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index.
Clustering index can improve the performance of most query operations because they provide a linear-access path to data stored in the database.

15. What is the difference between Clustered and Non-clustered index?

Ans:

As explained above, the differences can be broken down into three small factors –

Clustered index modifies the way records are stored in a database based on the indexed column. Non-clustered index creates a separate entity within the table which references the original table.

Clustered index is used for easy and speedy retrieval of data from the database, whereas, fetching records from the non-clustered index is relatively slower.

In SQL, a table can have a single clustered index whereas it can have multiple non-clustered indexes.

16. What is Data Integrity?

Ans:

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

17. What is a Query?

Ans:

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;

18. What is a Subquery? What are its types?

Ans:

A subquery is a query within another query, also known as nested query or inner query . It is used to restrict or enhance the data to be queried by the main query, thus restricting or enhancing the output of the main query respectively. For example, here we fetch the contact information for students who have enrolled for the maths subject:

SELECT name, email, mob, address
FROM myDb.contacts
WHERE roll_no IN (
SELECT roll_no
FROM myDb.students
WHERE subject = ‘Maths’);

There are two types of subquery – Correlated and Non-Correlated.

A correlated subquery cannot be considered as an independent query, but it can refer to the column in a table listed in the FROM of the main query.

A non-correlated subquery can be considered as an independent query and the output of the subquery is substituted in the main query.

19. What is the SELECT statement?

Ans:

SELECT operator in SQL is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT * FROM myDB.students;

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

Ans:

Some common SQL clauses used in conjunction with a SELECT query are as follows:

WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.

SELECT *
FROM myDB.students
WHERE graduation_year = 2019
ORDER BY studentID DESC;

GROUP BY clause in SQL is used to group records with identical data and can be used in conjuction with some aggregation functions to produce summarized results from the database.

HAVING clause in SQL is used to filter records in combination with the GROUP BY clause. It is different from WHERE, since WHERE clause cannot filter aggregated records.

SELECT COUNT(studentId), country
FROM myDB.students
WHERE country != “INDIA”
GROUP BY country
HAVING COUNT(studentID) > 5;

21. What are UNION, MINUS and INTERSECT commands?

Ans:

The UNION operator combines and returns the result-set retrieved by two or more SELECT statements.
The MINUS operator in SQL is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.
The INTERSECT clause in SQL combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.
Certain conditions need to be met before executing either of the above statements in SQL –

Each SELECT statement within the clause must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement should necessarily have the same order

SELECT name FROM Students /* Fetch the union of queries */
UNION
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */
MINUS /* that aren’t present in contacts */
SELECT name FROM Contacts;
SELECT name FROM Students /* Fetch names from students */
INTERSECT /* that are present in contacts as well */
SELECT name FROM Contacts;

22. What is Cursor? How to use a Cursor?

Ans:

A database cursor is a control structure that allows for traversal of records in a database. Cursors, in addition, facilitates processing after traversal, such as retrieval, addition and deletion of database records. They can be viewed as a pointer to one row in a set of rows.

Working with SQL Cursor

1. DECLARE a cursor after any variable declaration. The cursor declaration must always be associated with a SELECT Statement.
2. Open cursor to initialize the result set. The OPEN statement must be called before fetching rows from the result set.
3. FETCH statement to retrieve and move to the next row in the result set.
4. Call the CLOSE statement to deactivate the cursor.
5. Finally use the DEALLOCATE statement to delete the cursor definition and release the associated resources.

DECLARE @name VARCHAR(50) /* Declare All Required Variables */
DECLARE db_cursor CURSOR FOR /* Declare Cursor Name*/
SELECT name
FROM myDB.students
WHERE parent_name IN (‘Sara’, ‘Ansh’)
OPEN db_cursor /* Open cursor and Fetch data into @name */
FETCH next
FROM db_cursor
INTO @name
CLOSE db_cursor /* Close the cursor and deallocate the resources */
DEALLOCATE db_cursor

23. What are Entities and Relationships?

Ans:

Entity: An entity can be a real-world object, either tangible or intangible, that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities. Each entity has some associated properties that provide it an identity.
Relationships: Relations or links between entities that have something to do with each other. For example – The employees table in a company’s database can be associated with the salary table in the same database.

24. List the different types of relationships in SQL.

Ans:

1. One-to-One – This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.
2. One-to-Many & Many-to-One – This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.
3. Many-to-Many – This is used in cases when multiple instances on both sides are needed for defining a relationship.
4. Self Referencing Relationships – This is used when a table needs to define a relationship with itself.

25. What is an Alias in SQL?

Ans:

An alias is a feature of SQL that is supported by most, if not all, RDBMSs. It is a temporary name assigned to the table or table column for the purpose of a particular SQL query. In addition, aliasing can be employed as an obfuscation technique to secure the real names of database fields. A table alias is also called a correlation name .
An alias is represented explicitly by the AS keyword but in some cases the same can be performed without it as well. Nevertheless, using the AS keyword is always a good practice.

SELECT A.emp_name AS “Employee” /* Alias using AS keyword */
B.emp_name AS “Supervisor”
FROM employee A, employee B /* Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;

26. What is a View?

Ans:

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.

27. What is Normalization?

Ans:

Normalization represents the way of organizing structured data in the database efficiently. It includes creation of tables, establishing relationships between them, and defining rules for those relationships. Inconsistency and redundancy can be kept in check based on these rules, hence, adding flexibility to the database.

28. What is Denormalization?

Ans:

Denormalization is the inverse process of normalization, where the normalized schema is converted into a schema which has redundant information. The performance is improved by using redundancy and keeping the redundant data consistent. The reason for performing denormalization is the overheads produced in the query processor by an over-normalized structure.

29. What are the various forms of Normalization?

Ans:

Normal Forms are used to eliminate or reduce redundancy in database tables. The different forms are as follows:

1. First Normal Form- A relation is in first normal form if every attribute in that relation is a single-valued attribute. If a relation contains a composite or multi-valued attribute, it violates the first normal form.

2. Second Normal Form- A relation is in second normal form if it satisfies the conditions for first normal form and does not contain any partial dependency. A relation in 2NF has no partial dependency, i.e., it has no non-prime attribute that depends on any proper subset of any candidate key of the table. Often, specifying a single column Primary Key is the solution to the problem.
3. Third Normal Form- A relation is said to be in the third normal form, if it satisfies the conditions for second normal form and there is no transitive dependency between the non-prime attributes, i.e.All non-prime attributes are determined only by the candidate keys of the relation and not by any other non-prime attribute.

4. Boyce-Codd Normal Form- A relation is in Boyce-Codd Normal Form if it satisfies the conditions for third normal form and for every functional dependency, Left-Hand-Side is super key. In other words, a relation in BCNF has non-trivial functional dependencies in the form X –> Y, such that X is always a super key.

30. What are the TRUNCATE, DELETE and DROP statements?

Ans:

DELETE statement is used to delete rows from a table.

DELETE FROM Candidates
WHERE CandidateId > 1000;

TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

TRUNCATE TABLE Candidates;

DROP command is used to remove an object from the database. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database.

DROP TABLE Candidates;

31. What is the difference between DROP and TRUNCATE statements?

Ans:

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.

32. What is the difference between DELETE and TRUNCATE statements?

Ans:

The TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

33. What are Aggregate and Scalar functions?

Ans:

An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. Following are the widely 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.

A scalar function returns a single value based on the input value. Following are the widely 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 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.

34. What is a User-defined function? What are its various types?

Ans:

The user-defined functions in SQL are like functions in any other programming language that accept parameters, perform complex calculations, and return a value. They are written to use the logic repetitively whenever required. There are two types of SQL user-defined functions:

Scalar Function: As explained earlier, user-defined scalar functions return a single scalar value.
Table Valued Functions: User-defined table-valued functions return a table as output.
Inline: returns a table data type based on a single SELECT statement.
Multi-statement: returns a tabular result-set but, unlike inline, multiple SELECT statements can be used inside the function body.

35. What is OLTP?

Ans:

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. To avoid single points of failure, OLTP systems are often decentralized. These systems are usually designed for a large number of users who conduct short transactions. Database queries are usually simple, require sub-second response times and return relatively few records.

36. What are the differences between OLTP and OLAP?

Ans:

OLTP stands for Online Transaction Processing, is a class of software applications capable of supporting transaction-oriented programs. An important attribute of an OLTP system is its ability to maintain concurrency. OLTP systems often follow a decentralized architecture to avoid single points of failure. These systems are generally designed for a large audience of end users who conduct short transactions. Queries involved in such databases are generally simple, need fast response times and return relatively few records. Number of transactions per second acts as an effective measure for such systems.

OLAP stands for Online Analytical Processing, a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. For OLAP systems, the effectiveness measure relies highly on response time. Such systems are widely used for data mining or maintaining aggregated, historical data, usually in multi-dimensional schemas.

37. What is Collation? What are the different types of Collation Sensitivity?

Ans:

Collation refers to a set of rules that determine how data is sorted and compared. Rules defining the correct character sequence are used to sort the character data. It incorporates options for specifying case-sensitivity, accent marks, kana character types and character width. Below are the different types of collation sensitivity:

Case sensitivity: A and a are treated differently.
Accent sensitivity: a and á are treated differently.
Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently.
Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.

38. What is a Stored Procedure?

Ans:

A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary. The sole disadvantage of stored procedure is that it can be executed nowhere except in the database and occupies more memory in the database server. It also provides a sense of security and functionality as users who can’t access the data directly can be granted access via stored procedures.

DELIMITER $$
CREATE PROCEDURE FetchAllStudents()
BEGIN
SELECT * FROM myDB.students;
END $$
DELIMITER ;

39. What is a Recursive Stored Procedure?

Ans:

A stored procedure which calls itself until a boundary condition is reached, is called a recursive stored procedure. This recursive function helps the programmers to deploy the same set of code several times as and when required. Some SQL programming languages limit the recursion depth to prevent an infinite loop of procedure calls from causing a stack overflow, which slows down the system and may lead to 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 */

40. How to create empty tables with the same structure as another table?

Ans:

Creating empty tables with the same structure can be done smartly by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.

SELECT * INTO Students_copy
FROM Students WHERE 1 = 2;

41. What is Pattern Matching in SQL?

Ans:

SQL pattern matching provides for pattern search in data if you have no clue as to what that word should be. This kind of SQL query uses wildcards to match a string pattern, rather than writing the exact word. The LIKE operator is used in conjunction with SQL Wildcards to fetch the required information.

SELECT *
FROM students
WHERE first_name LIKE ‘K%’

Omitting the patterns using the NOT keyword
Use the NOT keyword to select records that don’t match the pattern. This query returns all students whose first name does not begin with K.

SELECT *
FROM students
WHERE first_name NOT LIKE< ‘K%’

Matching a pattern anywhere using the % wildcard twice
Search for a student in the database where he/she has a K in his/her first name.

SELECT *
FROM students
WHERE first_name LIKE ‘%Q%’

Using the _ wildcard to match pattern at a specific position
The _ wildcard matches exactly one character of any type. It can be used in conjunction with % wildcard. This query fetches all students with letter K at the third position in their first name.

SELECT *
FROM students
WHERE first_name LIKE ‘__K%’

Matching patterns for specific length
The _ wildcard plays an important role as a limitation when it matches exactly one character. It limits the length and position of the matched results. For example –

SELECT * /* Matches first names with three or more letters */
FROM students
WHERE first_name LIKE ‘___%’
SELECT * /* Matches first names with exactly four characters */
FROM students
WHERE first_name LIKE ‘____’

42. What are transactions and their controls?

Ans:

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations like Creating, updating, deleting records performed in the database come from transactions.

In simple words, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as

COMMIT: It is used to save all changes made through the transaction.
ROLLBACK: It is used to roll back the transaction. All changes made by the transaction are reverted back and the database remains as before.
SET TRANSACTION: Set the name of the transaction.
SAVEPOINT: It is used to set the point where the transaction is to be rolled back.

43. What are the properties of the transaction?

Ans:

Properties of the transaction are known as ACID properties.

These are:

Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully or not. If not, then the transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes are undone.
Consistency: Ensures that all changes made through successful transactions are reflected properly on the database.
Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on others.
Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure.

44. What are triggers?

Ans:

Triggers in SQL are stored procedures used to create a response to a specific action performed on the table such as INSERT, UPDATE or DELETE. You can invoke triggers explicitly on the table in the database.

Action and Event are two main components of SQL triggers. When certain actions are performed, the event occurs in response to that action.

Syntax:

CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}
ON table_name [FOR [EACH] {ROW|STATEMENT}]
EXECUTE PROCEDURE functionname {arguments}

45. What is View in SQL?

Ans:

A View can be defined as a virtual table that contains rows and columns with fields from one or more tables.

Syntax:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

46. How can we update the view?

Ans:

SQL CREATE and REPLACE can be used for updating the view.

Execute the below query to update the created view.

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

47. Explain the working of SQL Privileges?

Ans:

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 objects by using commands like SELECT, INSERT, UPDATE, DELETE, ALL, etc.

GRANT Command: This command is used to provide database access to users other than the administrator.

Syntax:

GRANT privilege_name
ON object_name
TO {user_name|PUBLIC|role_name}
[WITH GRANT OPTION];

In the above syntax, the GRANT option indicates that the user can grant access to another user too.

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

Syntax:

REVOKE privilege_name
ON object_name
FROM {user_name|PUBLIC|role_name};

48. How many types of Privileges are available in SQL?

Ans:

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

System privilege: System privilege deals with the object of a particular type and provides users the right to perform one or more actions on it. These actions include performing administrative tasks, ALTER ANY INDEX, ALTER ANY CACHE GROUP CREATE/ALTER/DELETE TABLE, CREATE/ALTER/DELETE VIEW etc.
Object privilege: This allows you 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.

49. What is SQL Injection?

Ans:

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of a database in a way that once it is executed, the database is exposed to an attacker for the attack. This technique is usually used for attacking data-driven applications to have access to sensitive data and perform administrative tasks on databases.

For Example,

SELECT column_name(s) FROM table_name WHERE condition;

50. What is SQL Sandbox in SQL Server?

Ans:

SQL Sandbox is a safe place in the SQL server environment where untrusted scripts are executed. There are 3 types of SQL sandbox:

Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory as well as cannot create files.
External Access Sandbox: Users can access files without having the right to manipulate the memory allocation.
Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

51. What is the difference between SQL and PL/SQL?

Ans:

SQL is a Structured Query Language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

52. What is the use of the NVL function?

Ans:

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

53. What is the Cartesian product of the table?

Ans:

The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table.

For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.

54. What do you mean by Subquery?

Ans:

Query within another query is called a Subquery. A subquery is called an inner query which returns output that is to be used by another query.

55. How many row comparison operators are used while working with a subquery?

Ans:

There are 3-row comparison operators that are used in subqueries such as IN, ANY and ALL.

56. What is the difference between clustered and non-clustered indexes?

Ans:

The differences between the two are as follows:

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

57. How to write a query to show the details of a student from Students table whose name starts with K?

Ans:

Query:

SELECT * FROM Student WHERE Student_Name like ‘K%’;

Here ‘like’ operator is used to perform pattern matching.

58. What is the difference between Nested Subquery and Correlated Subquery?

Ans:

Subquery within another subquery is called Nested Subquery. If the output of a subquery depends on column values of the parent query table then the query is called Correlated Subquery.

SELECT adminid(SELECT Firstname+’ ‘+Lastname  FROM Employee WHERE
empid=emp. adminid)AS EmpAdminId FROM Employee;

The result of the query is the details of an employee from the Employee table.

59. State some properties of Relational databases?

Ans:

Properties are as follows:

In relational databases, each column should have a unique name.
The sequence of rows and columns in relational databases is insignificant.
All values are atomic and each row is unique.

60. What are Nested Triggers?

Ans:

Triggers may implement data modification logic by using INSERT, UPDATE, and DELETE statements. These triggers that contain data modification logic and find other triggers for data modification are called Nested Triggers.

61. What is a Cursor?

Ans:

A cursor is a database object which is used to manipulate data in a row-to-row manner.

Cursor follows steps as given below:

1. Declare Cursor
2. Open Cursor
3. Retrieve row from the Cursor
4. Process the row
5. Close Cursor
6. Deallocate Cursor

62. What do we need to check in Database Testing?

Ans:

In Database testing, the following thing is required to be tested:

Database connectivity
Constraint check
Required application field and its size
Data Retrieval and processing with DML operations
Stored Procedures
Functional flow

63. What is Database White Box Testing?

Ans:

Database White Box testing involves:

1. Database Consistency and ACID properties
2. Database triggers and logical views
3. Decision Coverage, Condition Coverage, and Statement Coverage
4. Database Tables, Data Model, and Database Schema
5. Referential integrity rules

64. What is Database Black Box Testing?

Ans:

Database Black Box testing involves:

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

65. How to select all records from the table?

Ans:

To select all the records from the table we need to use the following syntax:

Select * from table_name;

66. What is the syntax to add a record to a table?

Ans:

To add a record in a table INSERT syntax is used.

For Example,

INSERT into table_name VALUES (value1, value2..);

67. How do you add a column to a table?

Ans:

To add another column in the table, use the following command:

ALTER TABLE table_name ADD (column_name);

68. Define the SQL DELETE statement.

Ans:

DELETE is used to delete a row or rows from a table based on the specified condition.

The basic syntax is as follows:

DELETE FROM table_name
WHERE <Condition>

69. Define COMMIT?

Ans:

COMMIT saves all changes made by DML statements.

70. What is CHECK Constraint?

Ans:

A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

71. Is it possible for a table to have more than one foreign key?

Ans:

Yes, a table can have many foreign keys but only one primary key.

72. What are the possible values for the BOOLEAN data field?

Ans:

For a BOOLEAN data field, two values are possible: -1(true) and 0(false).

73. What is identity in SQL?

Ans:

An identity column in which SQL automatically generates numeric values. We can define a start and increment value of the identity column.

74. How to select random rows from a table?

Ans:

Using a SAMPLE clause we can select random rows.

For Example,

SELECT * FROM table_name SAMPLE(10);

75. Which TCP/IP port does SQL Server run?

Ans:

By default SQL Server runs on port 1433.

Sql Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

76. Write a SQL SELECT query that only returns each name only once from a table?

Ans:

To get the result as each name only once, we need to use the DISTINCT keyword.

SELECT DISTINCT name FROM table_name;

77. Explain DML and DDL?

Ans:

DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements.

DDL stands for Data Definition Language. CREATE, ALTER, DROP, RENAME are DDL statements.

78. Can we rename a column in the output of the SQL query?

Ans:

Yes, using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

79. Give the order of SQL SELECT?

Ans:

Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clauses are mandatory.

80. Suppose a Student column has two columns, Name and Marks. How to get names and marks of the top three students.

Ans:

SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*)
FROM Students s2 WHERE s1.marks = s2.marks)

81. What do you mean by ROWID?

Ans:

It’s an 18 character long pseudo column attached with each row of a table.

82. Define UNION, MINUS, UNION ALL, INTERSECT?

Ans:

MINUS – returns all distinct rows selected by the first query but not by the second.
UNION – returns all distinct rows selected by either query
UNION ALL – returns all rows selected by either query, including all duplicates.
INTERSECT – returns all distinct rows selected by both queries.

83. What is a transaction?

Ans:

A transaction is a sequence of code that runs against a database. It takes the database from one consistent state to another.

84. What is the difference between UNIQUE and PRIMARY KEY constraints?

Ans:

The differences are as follows:

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys.
The primary key cannot contain Null values whereas the Unique key can contain Null values.

85. What is a composite primary key?

Ans:

The primary key created on more than one column is called composite primary key.

86. What do you mean by query optimization?

Ans:

Query optimization is a process in which a database system compares different query strategies and selects the query with the least cost.

87. What is Referential Integrity?

Ans:

Set of rules that restrict the values of one or more columns of the tables based on the values of the primary key or unique key of the referenced table.

88. What is the Case function?

Ans:

Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of the multiple possible result expressions.

89. Define a temp table?

Ans:

A temp table is a temporary storage structure to store the data temporarily.

90. How can we avoid duplicating records in a query?

Ans:

By using the DISTINCT keyword, duplication of records in a query can be avoided.

91. Explain the difference between Rename and Alias?

Ans:

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column.

92. What are the advantages of Views?

Ans:

Advantages of Views are:

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.

93. List the various privileges that a user can grant to another user?

Ans:

SELECT, CONNECT, RESOURCES.

94. What is a schema?

Ans:

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

95. What is a Table?

Ans:

A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.

96. Does View contain Data?

Ans:

No, Views are virtual structures.

97. Can a View based on another View?

Ans:

Yes, A View is based on another View.

98. What is the difference between the HAVING clause and WHERE clause?

Ans:

Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause.

If the GROUP BY clause is not used then Having behaved like WHERE clause only.

99. What is the difference between Local and Global temporary tables?

Ans:

If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the DB but its rows disappear when the connection is closed.

100. What is CTE?

Ans:

A CTE or common table expression is an expression that contains a temporary result set which is defined in a SQL statement.

101. What are SQL comments?

Ans:

SQL comments can be inserted by adding two consecutive hyphens (–).

Are you looking training with Right Jobs?

Sap Hana Certification Training 11025 Learners
Postgresql Online Training 12022 Learners
Snowflake Training 11141 Learners

Request for Information

Name

Mobile

Select Course

SQL Interview Questions and Answers

Related Articles

Popular Courses

Latest Articles

Request for Information

Trending Courses

Trending Blog Articles

CONTACT

COMPANY

WORK WITH US

TERMS & POLICIES

Velachery

Tambaram

OMR

Porur

Anna Nagar

T. Nagar

Adyar

Thiruvanmiyur

Siruseri

Maraimalai Nagar

BTM Layout

Marathahalli

Rajaji Nagar

Jaya Nagar

Kalyan Nagar

Electronic City

Indira Nagar

HSR Layout

Hyderabad

Pune

SQL Interview Questions and Answers

1. What is Database?

2. What is DBMS?

3. What is RDBMS? How is it different from DBMS?

4. What is SQL?

5. What is the difference between SQL and MySQL?

6. What are Tables and Fields?

7. What are Constraints in SQL?

8. What is a Primary Key?

9. What is a UNIQUE constraint?

10. What is a Foreign Key?

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

12. What is a Self-Join?

Subscribe For Free Demo

13. What is a Cross-Join?

14. What is an Index? Explain its different types.

Unique and Non-Unique Index:

Clustered and Non-Clustered Index:

15. What is the difference between Clustered and Non-clustered index?

16. What is Data Integrity?

17. What is a Query?

18. What is a Subquery? What are its types?

19. What is the SELECT statement?

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

21. What are UNION, MINUS and INTERSECT commands?

22. What is Cursor? How to use a Cursor?

23. What are Entities and Relationships?

24. List the different types of relationships in SQL.

25. What is an Alias in SQL?

26. What is a View?

27. What is Normalization?

28. What is Denormalization?

29. What are the various forms of Normalization?

30. What are the TRUNCATE, DELETE and DROP statements?

31. What is the difference between DROP and TRUNCATE statements?

32. What is the difference between DELETE and TRUNCATE statements?

33. What are Aggregate and Scalar functions?

34. What is a User-defined function? What are its various types?

35. What is OLTP?

Get SQL Training for Beginners Covers Advanced Concepts from Industry Experts

36. What are the differences between OLTP and OLAP?

37. What is Collation? What are the different types of Collation Sensitivity?

38. What is a Stored Procedure?

39. What is a Recursive Stored Procedure?

40. How to create empty tables with the same structure as another table?

41. What is Pattern Matching in SQL?

42. What are transactions and their controls?

43. What are the properties of the transaction?

44. What are triggers?

45. What is View in SQL?

46. How can we update the view?

47. Explain the working of SQL Privileges?

48. How many types of Privileges are available in SQL?

49. What is SQL Injection?

50. What is SQL Sandbox in SQL Server?

51. What is the difference between SQL and PL/SQL?

52. What is the use of the NVL function?

53. What is the Cartesian product of the table?

54. What do you mean by Subquery?

55. How many row comparison operators are used while working with a subquery?

Get Advanced Practical Oriented SQL Training & Certification Course

56. What is the difference between clustered and non-clustered indexes?

57. How to write a query to show the details of a student from Students table whose name starts with K?

58. What is the difference between Nested Subquery and Correlated Subquery?

59. State some properties of Relational databases?

60. What are Nested Triggers?

61. What is a Cursor?

62. What do we need to check in Database Testing?

63. What is Database White Box Testing?

64. What is Database Black Box Testing?

65. How to select all records from the table?

66. What is the syntax to add a record to a table?

67. How do you add a column to a table?

68. Define the SQL DELETE statement.

69. Define COMMIT?

70. What is CHECK Constraint?

71. Is it possible for a table to have more than one foreign key?

72. What are the possible values for the BOOLEAN data field?

73. What is identity in SQL?

74. How to select random rows from a table?