SQL Questions For Interview With Answers 2020

This article SQL Questions For Interview with answers is designed for those students and professionals to prepare for Job Interview and Exam certification.

SQL Questions For Interview with Answers 2020

SQL Questions For Interview: What is SQL?
Answer: SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do many things, for example – we can execute queries, we can insert records in a table, we can update records, we can create a database, we can create a table, we can delete a table etc.
SQL Questions For Interview: What is the difference between SQL and MySQL?
Answer: 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.
SQL Questions For Interview: What is a Database?
Answer: A Database is defined as a structured form of data which is stored in a computer or data in an organised manner and can be accessed in various ways. It is also the collection of schemas, tables, queries, views etc. Database helps us in easily storing, accessing and manipulation of data held in a computer. The Database Management System allows a user to interact with the database.
SQL Questions For Interview: Does SQL support programming language features ?
Answer: It is true that SQL is a language but it does not support programming as it is not a programming language, it is a command language. We do not have conditional statements in SQL like for loops or if..else, we only have commands which we can use to query, update , delete etc. data in the database. SQL allows us to manipulate data in a database.
SQL Questions For Interview: What is subquery?
Answer: A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.
SQL Questions For Interview: What are the types of subquery?
Answer: There are two types of subquery – Correlated and Non-Correlated.
A correlated subquery cannot be considered as independent query, but it can refer the column in a table listed in the FROM the list of the main query.

A Non-Correlated sub query can be considered as independent query and the output of subquery are substituted in the main query.
SQL Questions For Interview: What is a stored procedure?
Answer: Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.
SQL Questions For Interview: What is a trigger?
Answer: A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.
Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.
SQL Questions For Interview: What is Auto Increment?
Answer: Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
Mostly this keyword can be used whenever PRIMARY KEY is used.
SQL Questions For Interview: What is the difference between Cluster and Non-Cluster Index?
Answer: Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
SQL Questions For Interview: What is user defined functions?
Answer: User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.
SQL Questions For Interview: What are all types of user defined functions?
Answer: Three types of user defined functions are.
• Scalar Functions.
• Inline Table valued functions.
• Multi statement valued functions.
Scalar returns unit, variant defined the return clause. Other two types return table as a return.
SQL Questions For Interview: What is collation?
Answer: Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.
SQL Questions For Interview: What are all different types of collation sensitivity?
Answer: Following are different types of collation sensitivity -.
• Case Sensitivity – A and a and B and b.
• Accent Sensitivity.
• Kana Sensitivity – Japanese Kana characters.
• Width Sensitivity – Single byte character and double byte character.
SQL Questions For Interview: Advantages and Disadvantages of Stored Procedure?
Answer: Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.
SQL Questions For Interview: SQL Questions For Interview: What is Datawarehouse?
Answer: Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.
SQL Questions For Interview: What are local and global variables and their differences?
Answer: Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called.
Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.
SQL Questions For Interview: What is the difference between BETWEEN and IN operators in SQL?

Answer:

BETWEEN

The BETWEEN operator is used to fetch rows based on a range of values.
For example,
SELECT * FROM Students
WHERE ROLL_NO BETWEEN 20 AND 30;
This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30.
IN
The IN operator is used to check for values contained in specific sets.
For example,
SELECT * FROM Students
WHERE ROLL_NO IN (20,21,23);
This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

SQL Questions For Interview: Write an SQL query to find names of employee start with ‘A’?
Answer: The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in where clause.
The Syntax for using LIKE is,
SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern;
LIKE: operator name
pattern: exact value extracted from the pattern to get related data in
result set.
The required query is:
SELECT * FROM Employees WHERE EmpName like ‘A%’ ;
You may refer to this article on WHERE clause for more details on LIKE operator.
SQL Questions For Interview: What is the difference between CHAR and VARCHAR2 datatype in SQL?
Answer: Both of these datatypes 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 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.
Name different types of case manipulation functions available in SQL.
There are three types of case manipulation functions available in SQL. They are,
• LOWER: The purpose of this function is to return the string in lowercase. It takes a string as argument and returns the string by converting it into lower case.
Syntax:
• LOWER(‘string’)
• UPPER:The purpose of this function is to return the string in uppercase. It takes a string as argument and returns the string by converting it into uppercase.
Syntax:
• UPPER(‘string’)
• INITCAP:The purpose of this function is to return the string with first letter in uppercase and rest of the letters in lowercase.
Syntax:
• INITCAP(‘string’)
SQL Questions For Interview: What do you mean by data definition language?
Answer: Data definition language or DDL allows to execute queries like CREATE, DROP and ALTER. That is, those queries which define the data.
SQL Questions For Interview: What do you mean by data manipulation language?
Answer: Data manipulation Language or DML is used to access or manipulate data in the database.
It allows us to perform below listed functions:
• Insert data or rows in database
• Delete data from database
• Retrieve or fetch data
• Update data in database.
SQL Questions For Interview: What is the difference between primary key and unique constraints?
Answer: Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. The primary key creates the cluster index automatically but the Unique key does not.
SQL Questions For Interview: What is a view in SQL?
Answer: Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.
The CREATE VIEW statement of SQL is used for creating Views.
Basic Syntax:
CREATE VIEW view_name AS
SELECT column1, column2…..
FROM table_name
WHERE condition;
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows
SQL Questions For Interview: What is a Foreign Key?
Answer: A FOREIGN KEY comprises of 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);
SQL Questions For Interview: What is a join in SQL? What are the types of joins?
Answer: An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are:
• INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.
• LEFT JOIN:This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN
• RIGHT JOIN:RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
• FULL JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
To know about each of these joins in details, refer this.
SQL Questions For Interview: What is an index?
Answer: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on disk. To support faster access according to different values, faster search like binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on disk, but they allow faster search according to different frequently searched values.
SQL Questions For Interview: What are Tables and Fields?
Answer: 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.
SQL Questions For Interview: What are Constraints in SQL?
Answer: 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 creationg 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.
SQL Questions For Interview: What is a Primary Key?
Answer: 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 is comprised 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);
SQL Questions For Interview: What is a UNIQUE constraint?
Answer: 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 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);
SQL Questions For Interview: What is a Join? List its different types.?
Answer: 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.
(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;
SQL Questions For Interview: What is a Self-Join?
Answer: 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;
SQL Questions For Interview: What is a Cross-Join?
Answer: 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 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;
SQL Questions For Interview: What is an Index? Explain its different types.
Answer: 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.
SQL Questions For Interview: What is the difference between Clustered and Non-clustered index?
Answer: 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.
SQL Questions For Interview: What is Data Integrity?
Answer: 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.
SQL Questions For Interview: What is a Subquery? What are its types?
Answer: 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 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 subquery is substituted in the main query.
SQL Questions For Interview: What is a Query?
Answer: 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;
SQL Questions For Interview: What is the SELECT statement?
Answer: 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;
SQL Questions For Interview: What are some common clauses used with SELECT query in SQL?
Answer: Some common SQL clauses used in conjuction with a SELECT query are as follows:
WHERE clause in SQL is used to filter records that are necessary, based on specific conditions.
ORDER BY clause in SQL is used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).
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;
SQL Questions For Interview:What are UNION, MINUS and INTERSECT commands?
Answer: 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;
SQL Questions For Interview:What is Cursor? How to use a Cursor?
Answer: 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
DECLARE a cursor after any variable declaration. The cursor declaration must always be associated with a SELECT Statement.
Open cursor to initialize the result set. The OPEN statement must be called before fetching rows from the result set.
FETCH statement to retrieve and move to the next row in the result set.
Call the CLOSE statement to deactivate the cursor.
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
SQL Questions For Interview: What are Entities and Relationships?

Answer:

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.
List the different types of relationships in SQL.
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.
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.
Many-to-Many – This is used in cases when multiple instances on both sides are needed for defining a relationship.
Self Referencing Relationships – This is used when a table needs to define a relationship with itself.

SQL Questions For Interview:What is an Alias in SQL?
Answer: 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;
SQL Questions For Interview:What is a View?
Answer: 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.
SQL Questions For Interview:What is Normalization?
Answer: 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.
SQL Questions For Interview:What is Denormalization?
Answer: 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 query processor by an over-normalized structure.
SQL Questions For Interview: What are the various forms of Normalization?
Answer: Normal Forms are used to eliminate or reduce redundancy in database tables. The different forms are as follows:
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 composite or multi-valued attribute, it violates the first normal form. Let’s consider the following students table. Each student in the table, has a name, his/her address and the books they issued from the public library –
As we can observe, the Books Issued field has more than one values per record and to convert it into 1NF, this has to be resolved into separate individual records for each book issued. Check the following table in 1NF form –
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. Examples –
Example 1 – Consider the above example. As we can observe, Students Table in 1NF form has a candidate key in the form of [Student, Address] that can uniquely identify all records in the table. The field Books Issued (non-prime attribute) depends partially on the Student field. Hence, the table is not in 2NF. To convert it into 2nd Normal Form, we will partition the tables into two while specifying a new Primary Key attribute to identify the individual records in the Students table. The Foreign Key constraint will be set on the other table to ensure referential integrity.
Example 2 – Consider the following dependencies in relation R(W,X,Y,Z)
WX -> Y [W and X together determine Y]
XY -> Z [X and Y together determine Z]
Here, WX is the only candidate key and there is no partial dependency, i.e., any proper subset of WX doesn’t determine any non-prime attribute in the relation.
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.
Example 1 – Consider the Students Table in the above example. As we can observe, Students Table in 2NF form has a single candidate key Student_ID (primary key) that can uniquely identify all records in the table. The field Salutation (non-prime attribute), however, depends on the Student Field rather than the candidate key. Hence, the table is not in 3NF. To convert it into 3rd Normal Form, we will once again partition the tables into two while specifying a new Foreign Key constraint to identify the salutations for individual records in the Students table. The Primary Key constraint for the same will be set on the Salutations table to identify each record uniquely.
Example 2 – Consider the following dependencies in relation R(P,Q,R,S,T)
P -> QR [P together determine C]
RS -> T [B and C together determine D]
Q -> S
T -> P
For the above relation to exist in 3NF, all possible candidate keys in above relation should be {P, RS, QR, T}.
Boyce-Codd Normal Form
A relation is in Boyce-Codd Normal Form if 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. For example – In the above example, Student_ID serves as the sole unique identifier for the Students Table and Salutation_ID for the Salutations Table, thus these tables exist in BCNF. Same cannot be said for the Books Table and there can be several books with common Book Names and same Student_ID.
SQL Questions For Interview:What are the TRUNCATE, DELETE and DROP statements?
Answer: 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 is deleted and the table structure is removed from the database.
DROP TABLE Candidates;
SQL Questions For Interview:What is the difference between DROP and TRUNCATE statements?
Answer: 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.
SQL Questions For Interview:What is the difference between DELETE and TRUNCATE statements?
Answer: 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.
SQL Questions For Interview:What are Aggregate and Scalar functions?
Answer: 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.
Note: All aggregate functions described above ignore NULL values except for the COUNT function.
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 data & time.
FORMAT() – Sets the format to display a collection of values.
SQL Questions For Interview:What is User-defined function? What are its various types?
Answer: 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.
SQL Questions For Interview:What is OLTP?
Answer: 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. Here is an insight into the working of an OLTP system [ Note – The figure is not important for interviews ] –
SQL Questions For Interview:What are the differences between OLTP and OLAP?
Answer: 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.
SQL Questions For Interview:What is Collation? What are the different types of Collation Sensitivity?
Answer: 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.
SQL Questions For Interview:What is a Stored Procedure?
Answer: 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 ;
SQL Questions For Interview:What is a Recursive Stored Procedure?
Answer: 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 */
SQL Questions For Interview:How to create empty tables with the same structure as another table?
Answer: 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;
SQL Questions For Interview:What is Pattern Matching in SQL?
Answer: 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.
Using the % wildcard to perform a simple search
The % wildcard matches zero or more characters of any type and can be used to define wildcards both before and after the pattern. Search a student in your database with first name beginning with the letter K:
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 ‘__‘
SQL Questions For Interview:What are all the different types of indexes?

Answer:

There are three types of indexes -.
• Unique Index.
This indexing does not allow the field to have duplicate values if the column is unique indexed. Unique index can be applied automatically when primary key is defined.
• Clustered Index.
This type of index reorders the physical order of the table and search based on the key values. Each table can have only one clustered index.
• NonClustered Index.
NonClustered Index does not alter the physical order of the table and maintains logical order of data. Each table can have 999 nonclustered indexes.

SQL Questions For Interview:What is a Cursor?
Answer: A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.
SQL Questions For Interview:What is a relationship and what are they?
Answer: Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.
• One to One Relationship.
• One to Many Relationship.
• Many to One Relationship.
• Self-Referencing Relationship.
SQL Questions For Interview:What is CLAUSE?

Answer:

• SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.
• Example – Query that has WHERE condition
• Query that has HAVING condition.

SQL Questions For Interview:What is an ALIAS command?
Answer: ALIAS name can be given to a table or column. This alias name can be referred in WHERE clause to identify the table or column.
Example-.
Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID
Here, st refers to alias name for student table and Ex refers to alias name for exam table.
SQL Questions For Interview:What are aggregate and scalar functions?
Answer: Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on the input value.
Example -.
Aggregate – max(), count – Calculated with respect to numeric.
Scalar – UCASE(), NOW() – Calculated with respect to strings.
SQL Questions For Interview:How can you create an empty table from an existing table?
Answer:
Example will be -.
Select * into studentcopy from student where 1=2
Here, we are copying student table to another table with the same structure with no rows copied.
SQL Questions For Interview:How to fetch common records from two tables?
Answer: Common records result set can be achieved by -.
Select studentID from student. INTERSECT Select StudentID from Exam
SQL Questions For Interview:How to fetch alternate records from a table?
Answer: Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]
SQL Questions For Interview:How to select unique records from a table?
Answer: Select unique records from a table by using DISTINCT keyword.
Select DISTINCT StudentID, StudentName from Student.
SQL Questions For Interview:What is the command used to fetch first 5 characters of the string?
Answer: There are many ways to fetch first 5 characters of the string -.
Select SUBSTRING(StudentName,1,5) as studentname from student
Select LEFT(Studentname,5) as studentname from student
SQL Questions For Interview:What are different types of statements supported by SQL?
1) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.
Some of the DDL Commands are listed below
CREATE: It is used for creating the table.
CREATE TABLE table_name
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
ALTER: The ALTER table is used for modifying the existing table object in the database.
ALTER TABLE table_name
ADD column_name datatype
OR
ALTER TABLE table_name
DROP COLUMN column_name
2) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.
The Select statement is used as partial DML statement that is used to select all or relevant records in the table.
3) DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.
SQL Questions For Interview:Why do we use SQL constraints? Which constraints we can use while creating a database in SQL?
Answer: Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.
Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.
There are 5 major constraints are used in SQL, such as
• NOT NULL: That indicates that the column must have some value and cannot be left null
• UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
• PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity.
• FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
• CHECK: It is used to ensure whether the value in columns fulfills the specified condition
SQL Questions For Interview:What are transactions and their controls?
Answer: A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records 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 such as all changes made by the transaction are reverted back and database remains as before
• SET TRANSACTION: Set the name of transaction
• SAVEPOINT: It is used to set the point from where the transaction is to be rolled back
SQL Questions For Interview:What are properties of the transaction?
Answer: Properties of the transaction are known as ACID properties, such as
• Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
• Consistency: Ensures that all changes made through successful transaction are reflected properly on database
• Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
• Durability: Ensures that the changes made in the database with committed transactions persist as it is even after a system failure
SQL Questions For Interview:How many Aggregate Functions are available there in SQL?
Answer: There are 7 aggregate functions we use in SQL
• AVG(): Returns the average value from specified columns
• COUNT(): Returns number of table rows
• MAX(): Returns largest value among the records
• MIN(): Returns smallest value among the records
• SUM(): Returns the sum of specified column values
• FIRST(): Returns the first value
• LAST(): Returns Last value
SQL Questions For Interview:What are Scalar Functions in SQL?
Answer: Scalar Functions are as follows
• UCASE(): Converts the specified field in upper case
• LCASE(): Converts the specified field in lower case
• MID(): Extracts and returns character from the text field
• FORMAT(): Specifies the display format
• LEN(): Specifies the length of the text field
• ROUND(): Rounds up the decimal field value to a number
SQL Questions For Interview:How we can update the view?
Answer: SQL CREATE and REPLACE can be used for updating the view.
Following query syntax is to be executed to update the created view
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL Questions For Interview:How many types of Privileges are available in SQL?
Answer: 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.
SQL Questions For Interview:What is the difference between NVL function, IFNULL function, and ISNULL function?
Answer: These three functions work in the same way. These functions are used to replace NULL value with another value. Oracle developers use NVL function, MySQL developers use IFNULL function and SQL Server developers use ISNULL function.
Assume, some of the values in a column are NULL.
If you run below statement, you will get result as NULL
SELECT col1 * (col2 + col3) FROM Table1
Suppose any of the value in col3 is NULL then as I said your result will be NULL.
To overcome this we use NVL() function, IFNULL() function, ISNULL() Function.
ORACLE:
SELECT col1 * (col2 + NVL(col3,0)) FROM Table1
MySQL:
SELECT col1 * (col2 + IFNULL(col3,0)) FROM Table1
Also, you can use the COALESCE() function
SELECT col1 * (col2 + COALESCE(col3,0)) FROM Table1
SQL Server:
SELECT col1 * (col2 + ISNULL(col3,0)) FROM Table1
SQL Questions For Interview:How do you update F as M and M as F from the below table TestTable?

Answer:

SQL Questions For Interview:How to get each name only once from an employee table?
Answer: By using the DISTINCT keyword, we could get each name only once.
SELECT DISTINCT employee_name FROM employee_table;
SQL Questions For Interview:How to rename a column in the output of SQL query?
Answer: By using SQL AS keyword
SELECT column_name AS new_name FROM table_name;
SQL Questions For Interview:What is the order of SQL SELECT?
Answer: Order of SQL SELECT statement is as follows
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
SQL Questions For Interview:How to display the current date in SQL?
In SQL, there is a built-in function called GetDate() which helps to return the current date.
1 SELECT GetDate();
SQL Questions For Interview:What are the types of DBMS?
Answer: There are two types of DBMS
Relational Database Management System (RDBMS)
Non-Relational Database Management System
SQL Questions For Interview:What is RDBMS?
Answer: RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from a relational database can be accessed using Structured Query Language (SQL)
SQL Questions For Interview:What are the popular Database Management Systems in the IT Industry?
Answer: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,
SQL Questions For Interview:What are the different types of SQL commands?
Answer: SQL commands are segregated into the following types:
• DDL – Data Definition Language
• DML – Data Manipulation Language
• DQL – Data Query Language
• DCL – Data Control Language
• TCL – Transaction Control Language
SQL Questions For Interview:What are the different DDL commands in SQL?
Answer: 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
SQL Questions For Interview:What are the different DML commands in SQL?
Answer: 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
SQL Questions For Interview:What are the different DCL commands in SQL?
Answer: 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
SQL Questions For Interview:What are the different TCL commands in SQL?
Answer: 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
SQL Questions For Interview:What is the difference between Rename and Alias?
Answer: ‘Rename’ is a permanent name given to a table or column
‘Alias’ is a temporary name given to a table or column.
SQL Questions For Interview:How to avoid duplicate records in a query?
Answer: The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicated values.
SQL Questions For Interview:What is the difference between UNIQUE and PRIMARY KEY constraints?
Answer: 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.
SQL Questions For Interview:What is the difference between NULL value, Zero, and Blank space?
Answer: As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.
Null value is a field with no value.
Zero is a number
Blank space is the value we provide. The ASCII value of space is CHAR(32).
SQL Questions For Interview:How to Test for NULL Values?
Answer: A field with a NULL value is a field with no value. NULL value cannot be compared with other NULL values. Hence, It is not possible to test for NULL values with comparison operators, such as =, <, or <>. For this, we have to use the IS NULL and IS NOT NULL operators.
SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
SQL Questions For Interview:What is a CHECK constraint?
Answer: A CHECK constraint is used to limit the value that is accepted by one or more columns.
E.g. ‘Age’ field should contain only the value greater than 18.
CREATE TABLE EMP_DETAILS(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Age INT CHECK (AGE > 18), PRIMARY KEY (EmpID));
SQL Questions For Interview:What is a DEFAULT constraint?
Answer: DEFAULT constraint is used to include a default value in a column when no value is supplied at the time of inserting a record.
Define the SELECT INTO statement.
The SELECT INTO statement copies data from one table into a new table. The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause.
SELECT * INTO newtable FROM oldtable WHERE condition;
SQL Questions For Interview:What is the difference between Delete, Truncate and Drop command?
Answer: 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.
SQL Questions For Interview:How to get unique records from a table?
Answer: Some of the ways to fetch the first 5 characters of a string are as follows:

SELECT RIGHT(EmpName,5) AS EmployeeName FROM Employee
SELECT SUBSTRING(EmpName,1,5) AS EmployeeName FROM Employee
SQL Questions For Interview:How to add new Employee details in an Employee_Details table with the Answer: following details
Employee_Name: John, Salary: 5500, Age: 29?
INSERT into Employee_Details (Employee_Name, Salary, Age) VALUES (‘John’, 5500 , 29);
SQL Questions For Interview:How to add a column ‘Salary’ to a table Employee_Details?
ALTER TABLE Employee_Details ADD (Salary);
SQL Questions For Interview:What is SQL Injection
Answer: 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 access to sensitive data and perform administrative tasks on databases.
For Example: SELECT column_name(s) FROM table_name WHERE condition;
SQL Questions For Interview:What is SQL Sandbox in SQL Server?
Answer: 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.
• How to select random rows from a table?
Answer: Using a SAMPLE clause we can select random rows.
• Example:
• SELECT * FROM table_name SAMPLE(10);
SQL Questions For Interview:What are STUFF and REPLACE function?
Answer: STUFF Function: This function is used to overwrite existing character or inserts a string into another string. Syntax:
STUFF(string_expression,start, length, replacement_characters)
where,
string_expression: it is the string that will have characters substituted
start: This refers to the starting position
length: It refers to the number of characters in the string which are substituted.
replacement_string: They are the new characters which are injected in the string.
REPLACE function: This function is used to replace the existing characters of all the occurrences. Syntax:
REPLACE (string_expression, search_string, replacement_string)
Here every search_string in the string_expression will be replaced with the replacement_string.
So this brings us to the end of the SQL interview questions blog. I hope this set of SQL Interview Questions will help you ace your job interview.
SQL Questions For Interview:List the ways in which Dynamic SQL can be executed?
Answer: Following are the ways in which dynamic SQL can be executed:
• Write a query with parameters.
• Using EXEC.
• Using sp_executesql.
SQL Questions For Interview:What are the various levels of constraints?
Answer: Constraints are the representation of a column to enforce data entity and consistency. There are two levels of a constraint, namely:
• column level constraint
• table level constraint
SQL Questions For Interview:What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
Answer: HAVING clause can be used only with SELECT statement. It is usually used in a GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.
SQL Questions For Interview:What Is the Difference Between OSQL And Query Analyzer?
Answer: OSQL is the command-line tool that executes the query and displays the result same a query analyzer but the query analyzer is graphical and OSQL is a command-line tool. OSQL is quite useful for batch processing or executing remote queries.
SQL Questions For Interview:What is a live lock?
Answer: A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
SQL Questions For Interview:How SQL Server executes a statement with nested subqueries?
Answer: When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set.
SQL Questions For Interview:How do you add a column to an existing table?
Answer: ALTER TABLE Department ADD (AGE, NUMBER);
Which statement do you use to eliminate padded spaces between the month and day values in a function TO_CHAR(SYSDATE,’Month, DD, YYYY’) ?
To remove padded spaces, you use the “fm” prefix before the date element that contains the spaces. TO_CHAR(SYSDATE,’fmMonth DD, YYYY’)
SQL Questions For Interview:What are Checkpoint In SQL Server ?
Answer: When we done operation on SQL SERVER that is not commited directly to the database.All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL Server it will take long time because Checkpoint is also fired.
SQL Questions For Interview:Why we use OPENXML clause?
Answer: OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the DB.
SQL Questions For Interview:What is the use of SIGN function?
Answer: SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.
Example –
SIGN(-35) returns -1
SQL Questions For Interview:Can we check locks in database? If so, how can we do this lock check?
Answer: Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.
SQL Questions For Interview:What is Bulkcopy in SQL?
Answer: Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.
SQL Questions For Interview:What is the use of SET NOCOUNT ON/OFF statement?
Answer: By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).
SQL Questions For Interview:What is UPDATE_STATISTICS command?
Answer: UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.
SQL Questions For Interview:What are Magic Tables in SQL Server?
Answer: During DML operations like Insert, Delete, and Update, SQL Server creates magic tables to hold the values during the DML operations. These magic tables are used inside the triggers for data transaction.
SQL Questions For Interview:What is the difference between SUBSTR and CHARINDEX in the SQL Server?
Answer: The SUBSTR function is used to return specific portion of string in a given string. But, CHARINDEX function gives character position in a given specified string.
SUBSTRING(‘Smiley’,1,3)
Gives result as Smi
CHARINDEX(‘i’, ‘Smiley’,1)
SQL Questions For Interview:How can you create a login?
Answer: You can use the following command to create a login
CREATE LOGIN MyLogin WITH PASSWORD = ‘123’;
SQL Questions For Interview:What is ISNULL() operator?
Answer: ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.
SQL Questions For Interview:What is the use of FOR Clause?
Answer: FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.
SQL Questions For Interview:What will be the maximum number of index per table?
Answer: For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
SQL Questions For Interview:What is the difference between COMMIT and ROLLBACK?
Answer: Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.
SQL Questions For Interview:What is the difference between varchar and nvarchar types?
Answer: Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.
SQL Questions For Interview:What is the command used to Recompile the stored procedure at run time?
Answer: Stored Procedure can be executed with the help of keyword called RECOMPILE.
Example:
Exe <SPName> WITH RECOMPILE
SQL Questions For Interview:How to delete duplicate rows in SQL Server?
Answer: Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.
Where are SQL Server user names and passwords stored in SQL Server?
Answer: User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.
SQL Questions For Interview:What is the difference between GETDATE and SYSDATETIME?
Answer: Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.
SQL Questions For Interview:How data can be copied from one table to another table?
Answer: INSERT INTO SELECT
This command is used to insert data into a table which is already created.
SELECT INTO
This command is used to create a new table and its structure and data can be copied from existing table.
SQL Questions For Interview:What is TABLESAMPLE?
Answer: TABLESAMPLE is used to extract sample of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.
SQL Questions For Interview:Which command is used for user defined error messages?
Answer: RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.
What do mean by XML Datatype?
Answer: XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.
What is CDC?
Answer: CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server 2008.
What is Filtered Index?
Answer: Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered Index
What are the major enterprise relational database systems?
Answer: The four main enterprise options for SQL databases are:
• Microsoft SQL Server
• MySQL
• Oracle Database
• IBM DB2
Most enterprise versions of SQL databases come with their own “dialect” of SQL. For example, Oracle has created the PL/SQL extension of SQL, while Microsoft SQL Server uses the Transact-SQL language.
These different SQL versions are usually fairly similar on the whole. However, each has its own features and quirks to distinguish it from other options.
What is the difference between SQL and NoSQL databases?
Answer: Relational databases aren’t the only model for storing information in a database. Non-relational databases (also known as NoSQL databases) use a concept other than the relational model to store data.
There are several non-relational models for storing data, including:
• Key-value: Each item in the database is a key paired with a corresponding value.
• Document: Each item in the database is a document that stores data in a standard format, and is then associated with a unique key.
• Column: Data is stored in columns instead of rows, in order to improve performance for certain types of queries.
• Graph: Data is stored as a graph with nodes and edges that describe the relationships between different items.
Some of the most popular NoSQL databases are MongoDB, Apache Cassandra, HBase, and Redis.
Unlike SQL databases (which use SQL), NoSQL databases do not use a single, standard query language for interacting with data.
What is the difference between a primary key, a unique key, and a foreign key?
Answer: Primary keys, unique keys, and foreign keys are similar yet distinct concepts:
• A primary key is an attribute or attributes serving as a unique identifier for each record. For example, the primary key for students at a university could be their student ID numbers, since each student has a unique ID number. Each table can have only one primary key.
• A unique key is an attribute that must be different for each record. Unlike primary keys, there can be multiple unique keys in a table. For example, we may want students’ phone number to be a unique key, since no two students should have the same number. However, this would not be a good primary key, since students’ phone numbers may change during their studies.
• A foreign key is an attribute in one table that refers to a primary key in a different table. For example, suppose we have a table that contains the students in a given class. We might insert a foreign key in this table that refers to the primary key in another table (such as the students’ personal information).
What are the different types of clauses in SQL?
Answer: SQL clauses are used to qualify a database query by restricting or altering the values that it returns. The types of SQL clauses are:
• FROM: Used to specify which tables the data will be pulled from.
• WHERE: Used to filter results by requiring them to fulfill one or more conditions.
• ORDER BY: Used to specify how the results of a query should be sorted. For example, if you are returning students’ records, you may wish to order them by last name or by ID number.
• GROUP BY: Used to group together rows that have the same values. For example, you may wish to group together students based on their major.
• HAVING: Used in combination with the GROUP BY clause. It restricts the returned values to only those that fulfill a given condition.
• DISTINCT: Used to retrieve a table containing records with the duplicate values removed. For example, you may wish to count the number of different states or countries that students are from.
SQL Questions For Interview:What does a NULL value represent in SQL?
Answer: NULL is a special signifier in SQL that represents “no value.” A field with a NULL value is not equivalent to a field with a value of 0, or a field that contains whitespace.
NULL values are common when adding a record with one or more optional fields. If you do not enter a value for an optional field, it will take on the value NULL until you change the field’s value.
You can test whether a field has a NULL value by using the SQL operators IS NULL and IS NOT NULL.
SQL Questions For Interview:What are the goals and methods of database testing?
Answer: Database testing is necessary to ensure that a database adheres to the ACID properties described above.
Some of the most important database elements to test are:
• Transactions: Testers should verify that the database continues to fulfill the ACID properties after every transaction.
• Schema: A schema is the formal structure of a database. It describes how the data is organized and what relations exist between the records and fields. Testers should verify that the schema mapping between the front end and back end is correct. In addition, the schema should contain common-sense restrictions on the contents of a field (for example, ensuring that the ID number field is a numeric value).
• Triggers: A trigger is a database event that has been configured to occur once a given condition is fulfilled or another event takes place. Testers should verify that all triggers take place as expected.
SQL Questions For Interview:Why do we use SQL limitations? Which constraints can we use while making a database in SQL?
Answer: SQL Constraints are used to set the principles for all tables in the table. On the off chance that any imperatives get abused then it can prematurely end the activity that caused it.
Constraints are characterized while making the database itself with CREATE TABLE explanation or even after the table is made once with ALTER TABLE statement.
There are 5 noteworthy constraints we use as a part of SQL, for example,
• NOT NULL: That demonstrates that the section must have some value and we can’t leave it invalid
• Interesting: We use this constraint to guarantee that each line and section has one of a kind value and we make sure we don’t rehash any values in some other line or segment
• Essential KEY: We use this constraint as a part of the relationship with NOT NULL and UNIQUE imperatives, for example, on one or the mix of in excess of one segments to distinguish the specific record with a one of a kind character.
• Remote KEY: Generally, we use it to guarantee the referential uprightness of data in the table and furthermore coordinates the incentive in one table with another utilizing Primary Key
• CHECK: We use it to guarantee whether the incentive in sections satisfies the predetermined condition.
SQL Questions For Interview:Explain the working of SQL Privileges?
Answer: SQL GRANT and REVOKE charges are used to execute benefits in SQL various user conditions. The chairman of the database can give or repudiate benefits to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL and so on.
SQL Questions For Interview:What is SQL Sandbox in SQL Server?
Answer: SQL Sandbox is the protected place in SQL Server Environment where untrusted contents are executed. There are 3 sorts of SQL Sandbox, for example:
Safe Access Sandbox: Here a user can perform SQL tasks, for example, making put away systems, triggers and so forth yet can’t approach the memory and can’t make records.
External Access Sandbox: User can approach records without having a privilege to control the memory distribution.
Unsafe Access Sandbox: This contains untrusted codes where a user can approach memory.
SQL Questions For Interview:What is the Cartesian product of table?
Answer: The output of a Cross Join is a Cartesian Product. It returns lines combining each column from the primary table with each line of the second table. For Example, in the event that we join two tables having 15 and 20 products the Cartesian result of two tables will be 15×20=300 Rows.
SQL Questions For Interview:What are diverse Clauses that form a part of SQL?
Answer: Clauses used as a part of SQL:
Top SQL Interview Questions – SQL Clauses
• WHERE Clause: The clause serves to help characterize the condition, extract, and display tables that satisfy the given condition.
SQL Questions For Interview:What is “scheduled jobs” or “scheduled tasks“?
Answer: Scheduled jobs or tasks allows automated task management on regular or predictable cycles. One can schedule administrative tasks and decide the order of the tasks.
Name the encryption mechanisms in SQL server.
Ans. The encryption mechanism used in SQL server are –
• Transact-SQL functions
• Asymmetric keys
• Symmetric keys
• Certificates
• Transparent Data Encryption
SQL Questions For Interview:Name symmetric key encryption algorithms supported in SQL server?
Answer: SQL Server supports several symmetric key encryption algorithms, such as DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES
SQL Questions For Interview:Can you name different types of DDL commands?
Answer: DDL commands are divided into following –
CREATE – Used to create the database or its objects like table, index, function, views, triggers, etc.
DROP – Used to delete objects
ALTER – Used to change database structures
TRUNCATE – Used to erase all records from a table, excluding its database structure
COMMENT – Used to add comments to the data dictionary
RENAME – Used to rename a database object
SQL Questions For Interview:Name different DML commands in SQL?
Answer: DML commands are divided into following –
SELECT – Used to select specific database data
INSERT – Used to insert new records into a table
UPDATE – Used to update existing records
DELETE – Used to delete existing records from a table
MERGE – Used to UPSERT operation (insert or update)
CALL – Used to call a PL/SQL or Java subprogram
EXPLAIN PLAN – Used to interpret data access path
LOCK TABLE – Used to control concurrency
SQL Questions For Interview:Name different DCL commands in SQL?
Answer: DCL commands are –
GRANT – Used to provide user access privileges to the database
DENY – Used to deny permissions to users
REVOKE – Used to withdraw user access by using the GRANT command
SQL Questions For Interview:Name different TCL commands in SQL?
Answer: Different DCL commands are –
COMMIT – Used to commit a transaction
ROLLBACK – Used to roll back a transaction
SAVEPOINT – Used to roll back the transaction within groups
SET TRANSACTION – Used to specify transaction characteristics
SQL Questions For Interview:What is Referential Integrity?
Answer: Referential integrity is a relational database concept which suggests that accuracy and consistency of data should be maintained between primary and foreign keys.
SQL Questions For Interview:What is Business Intelligence?
Answer: Business intelligence (BI) includes technologies and practices for collecting, integrating, analyzing and presenting business information. It combines business analytics, data mining, data visualization, data tools and infrastructure, and best practices.
SQL Questions For Interview:What is faster between a table variable and a temporary table?
Answer: Between these, a table variable is faster mostly as it is stored in memory, whereas a temporary table is stored on disk. In case the size of table variable exceeds memory size then both the tables perform in a similar manner.
SQL Questions For Interview:How do you explain what SQL is to someone without a technical background?
Answer: SQL or Structured Query Language is a standardized programming language used to access or manipulate data in a database. It was designed to update, add, delete a row of data and retrieve subsets of information within the database.
In other words, a way to “talk” to databases by using coded commands, called SQL queries.
SQL Questions For Interview:What are the different type of keys and their uses?
Answer: SQL keys are sets of one or more columns that uniquely identify a record in a database table. They are used in fetching records or data-rows/column from data tables and connect tables or views in databases. Different types of keys are:
Candidate Key – A table identifies only one Primary Key for a single or more column. Candidate keys are potential and qualified keys to be the Primary Key.
Primary Key – Also called “Primary Keyword,” is a unique data identifier. It identifies telephone numbers, driver’s license numbers, or vehicle identification numbers(VIN).
Alternate Key – Alternate Keys are like a Primary Key that can be a column or set of columns on a table which values are always unique. They can contain null values unless it’s clearly specified with a NOT NULL integrity rule. Currently not selected as a primary key of the table but can also be the Primary Key.
Note: A table is composed of Candidate keys, but only one Candidate key can be a Primary key. Once the Primary key is specified, the rest are called Alternate keys.
Composite Key – Also known as a compound key or concatenated key. It is a combination of two or more columns in a table used to identify each table row uniquely. If columns are not combined and are taken individually, uniqueness is not guaranteed.
Foreign Key – In two tables’ relationship, a primary key of one table is referred to as a foreign key in another table. A foreign key can have duplicate values and can also keep null values if a column is defined to accept nulls.
Unique Key -An alternate key that accepts just one value and doesn’t allow duplicate values in a column.
Super Key – it’s a set of columns, where each column is functionally dependent. The Super key may hold additional columns that aren’t strictly required to identify each row uniquely. If you add any other column/attribute to a Primary Key, then it becomes a super key, like EmployeeID + FullName, is a Super Key.
SQL Questions For Interview:How we can refresh the view?
Answer: SQL CREATE and REPLACE can be used for refreshing the view.
The syntax for SQL Create and Refresh:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL Questions For Interview:What is the use of NVL work?
Answer: We use NVL work to change over the null a value to its real value.
SQL Questions For Interview:What are number line correlation administrators will use while working with a subquery?
Answer: There are 3-row comparison operators which we use in subqueries, for example, IN, ANY what not.
SQL Questions For Interview:How does the query above work exactly?
Answer: It’s actually pretty simple. First, the subquery (which is basically a derived table here, named TopOrderAmountsPerSalesperson) returns the orders with the highest dollar amounts per salesperson, and the associated salesperson ID. So, now we have each salesperson’s highest valued order and his/her ID in a derived table. That derived table (the results from the subquery) is then joined with the entire Orders table on the condition that the salesperson ID matches and that the Amount from the Orders table matches the MaxOrder amount returned from the derived table. What’s the point of this? Well, that join will give us the correct OrderNumber since it is matching on both the salesperson ID and the amount. Even if there are 2 rows with the same exact salesperson ID and amount it will not even matter because no matter which ordernumber is associated with that row, the result set will be exactly the same.
And remember that the whole reason we are doing this is to avoid the original problem with not being able to select a non-aggregated column with a group by.
Now, retrieving the salesperson name is simple. Try to figure it out on your own.
SQL Questions For Interview:Why does the selected column have to be in the group by clause or part of an aggregate function?
Answer: So, now you understand how to fix the error – but do you understand why it is a problem in the first place? Well, you should – because that is the most important thing to understand! So, let’s explain some more about why SQL gives that error shown above .
First off, let’s talk a little bit more about aggregate functions. You probably know what aggregate functions in SQL are – we used one in the example above. In case you forgot, aggregate functions are used to perform a mathematical function on the values inside a given column, which is passed into the aggregate function. Here are some of the commonly used aggregate functions:
AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
To illustrate why the SQL standard says that a selected column has to be in the group by clause or part of an aggregate function, let’s use another example. Suppose we have some tables called Starbucks_Stores and Starbucks_Employees. In case you don’t already know, Starbucks is a popular coffee shop/cafe in the USA
SELECT count(*) as num_employees, HourlyRate
FROM Starbucks_Employees JOIN Starbucks_Stores
ON Starbucks_Employees.StoreID = Starbucks_Stores.store_id
GROUP BY city
It looks like the SQL above would just return the number of Starbucks employees in each city, along with the HourlyRate – because it will group the employees based on whatever city they work in (thanks to the “group by city” statement).
SQL Questions For Interview:What are the constraints and what are their types?
Answer: These are commands you can use to set the rules for your data. Any operations violating the set rules are aborted by the constraints.
They are of 5 types:
     NOT NULL– columns cannot be left empty or null
     UNIQUE – ensures values in cells are unique and not repeated
      PRIMARY KEY – to identify a record
     FOREIGN KEY – ensures integrity of data
     CHECK – makes certain values in cells meet the set rules.
SQL Questions For Interview:What Clauses are used in SQL?
Answer: Clauses are commands in the SQL software, and there are 6 main types:
     WHERE
     ORDER BY
      GROUP BY
     HAVING
     JOIN
      USING
SQL Questions For Interview:Define SQL Update Statement?
Answer: Update statement is used to modify the data value in the table. General syntax for update is as below:
UPDATE table_name
SET column_name1 = value1,
column_name2 = value2,
column_nameN = valueN,
[WHERE condition]
SQL Questions For Interview:Define SQL Delete Statement?
Answer: Using this statement, we can delete the records in the entire table or specific record by specifying the condition.
DELETE FROM table_name [WHERE condition];
SQL Questions For Interview:What is Auto Increment feature in SQL?
Answer: Auto increment allows the user to create a unique number to be generated whenever a new record is inserted in the table. AUTO INCREMENT is the keyword for Oracle, AUTO_INCREMENT in MySQL and IDENTITY keyword can be used in SQL SERVER for auto-incrementing. Mostly this keyword is used to create the primary key for the table.
SQL Questions For Interview:What is Hibernate and its relation to SQL?
Answer: Hibernate is Object Relational Mapping tool in Java. Hibernate let’s us write object-oriented code and internally converts them to native SQL queries to execute against a relational database.
Hibernate uses its own language like SQL which is called Hibernate Query Language(HQL). The difference is that HQL boasts on being able to query Hibernate’s entity objects.
It also has an object-oriented query language in Hibernate which is called Criteria Query. It proves very beneficial and helpful to developers who primarily use objects in their front-end applications and Criteria Query can cater to those objects in even add SQL-like features such as security and restriction-access.
SQL Questions For Interview:What is a SQL Profiler?
Answer: The SQL Profiler is a Graphical User Interface that allows database developers to monitor and track their database engine activities. It features activity logging for every event occurring and provides analysis for malfunctions and discrepancies.
It basically is a diagnostic feature in SQL that debugs performance issues and provides a more versatile way of seeing which part in your trace file is causing a clog in your SQL transactions.
SQL Questions For Interview:How can we solve SQL Error: ORA-00904: invalid identifier?
Answer: This error usually appears due to syntax errors on calling a column name in Oracle database, notice the ORA identifier in the error code. Make sure you typed in the correct column name. Also, take special note on the aliases as they are the one being referenced in the error as the invalid identifier.
SQL Questions For Interview:How can we link a SQL database to an existing Android App?
Answer: It will require a JDBC (Java Database Connectivity) driver to link these two. Also, you must add the corresponding dependencies to your build.gradle file along with the permissions and grants.
SQL Questions For Interview:What to do when you forget your root password?
Answer: If you forgot or lost your root password, start the database with the command of “skip-grants-table”. After you set the new password, restart the database in normal mode and enter the new password.
SQL Questions For Interview:What should you do if the data disk is overloaded?
Answer: You might encounter situations where you fill-up the data disk. You can’t continue to do anything if it’s overloaded.
What you need to know during this SQL interview question is that in this situation is to apply what is known as a soft link. These links create a location where you can store your .frm and .idb files – and that is exactly what you should do. This will resolve the overload problem.
SQL Questions For Interview:What are the types of locks?
Answer: There are 2 types of locks:
Shared Lock: – In this type of locks, the data being locked can be read by other users / sessions. But it will not allow others to update the data that was being locked.
Exclusive Lock: – In this type of locks, data that is being locked cannot be read or updated by other users or sessions. It will be visible for read and write for the user who has locked it. Other users/session will have to wait till the lock is being released.
SQL Questions For Interview:What are the techniques of locking?
Answer: Database lock can be placed at different levels – on single row or multiple rows or on particular column or on entire table. This database locking at different level is known as locking granularity. Let us discuss them one by one below:
• Database Level Locking: – In this method, entire database is locked for update. Here, only one user or session will be active for any update and any other users cannot update the data. This method is not widely used, as it locks entire database. However, in Oracle the exclusive lock is same as Database lock and does not allow others to use entire database. It will be helpful when some support update is being executed like upgrading to new version of software etc.
• File Level Locking: – Here, entire database file will be locked. When we say database file, it may include whole table, or part of a table or part of multiple tables. Since file lock can include either whole or partial data from different tables, this type of lock is less frequent.
• Table Level Locking: -In this method, entire table will be locked. This will be useful when we need to update whole rows of the table. It will also be useful when we add/ remove some columns of the table where the changes affect entire table. Therefore, in Oracle this type of lock is also known as DDL lock.
• Page or Block Level Locking: – In this method, page or block of the database file will be locked for update. A page or block might contain entire or partial data of the table. This page or block represents space in memory location where data is occupied. This may contain entire table data or partial data. Hence this type of locking is also less frequent.
• Row Level Locking: – In this method entire row of a table is locked for update. It is most common type of locking mechanism.
• Column Level Locking: – In this method some columns of a row of a table is locked for update. This type of lock requires lots of resource to manage and release locks. Hence it is very less frequently used locking type.
SQL Questions For Interview:How to select 10 records from a table?
Answer:

MySQL: Using limit clause, example select * from Employee limit 10;
Oracle: Using ROWNUM clause, example SELECT * FROM Employee WHERE ROWNUM < 10;
SQL Server: Using TOP clause, example SELECT TOP 3 * FROM Employee;

SQL Questions For Interview:How can you maintain the integrity of your database on instances where deleting an element in a table result in the deletion of the element(s) within another table?
Answer: This is possible by invoking an SQL trigger which listens for any elements that are deleted in Table A and deletes the corresponding linked elements from Table B.
SQL Questions For Interview:What is the process of copying data from Table A to Table B?
Answer:

INSERT INTO TableB (columnOne, columnTwo, columnThree, …)
SELECT columnOne, columnTwo, columnThree, …
FROM TableA WHERE added_condtion;

SQL Questions For Interview:What are the differences between IN and EXISTS clause?
Answer: The apparent difference between the two is that the EXISTS keyword is relatively faster at execution compared to IN keyword. This is because the IN keyword must search all existing records while EXISTS keywords automatically stop when a matching record has been found.
Also, IN Statement operates within the ResultSet while EXISTS keyword operates on virtual tables. In this context, the IN Statement also does not operate on queries that associates with Virtual tables while the EXISTS keyword is used on linked queries.
SQL Questions For Interview:Does SQL support programming?
Answer: SQL refers to the Standard Query Language, which is not actually the programming language. SQL doesn’t have a loop, Conditional statement, logical operations, it can not be used for anything other than data manipulation. It is used like commanding (Query) language to access databases. The primary purpose of SQL is to retrieve, manipulate, update and perform complex operations like joins on the data present in the database.
SQL Questions For Interview:What are the types of operators available in SQL?
Answer: Operators are the special keywords or special characters reserved for performing particular operations and are used in the SQL queries. There is three type of operators used in SQL:
Arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/), etc.
Logical operators: ALL, AND, ANY, ISNULL, EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.
Comparison operator: =, !=, <>, <, >, <=, >=, !<, !>
SQL Questions For Interview:What is the usage of the DISTINCT keyword?
Answer: The DISTINCT keyword is used to ensure that the fetched value is only a non-duplicate value. The DISTINCT keyword is used to SELECT DISTINCT, and it always fetches different (distinct) from the column of the table.
SQL Questions For Interview:What is DBMS?
Answer: DBMS stands for Database Management System. This is a program which is used to control them. It is like a File Manager that manages data in a database rather than saving it in file systems.
Database management system is an interface between the database and the user. It makes the data retrieval, data access easier.
Database management system is a software which provides us the power to perform operations such as creation, maintenance and use of a data of the database using a simple query in almost no time.
Without the database management system, it would be far more difficult for the user to access the data of the database.
SQL Questions For Interview:What are the different types of database management systems?
Answer: There are four types of database:
Hierarchical databases (DBMS)
Relational databases (RDBMS)
Network databases (IDMS)
Object-oriented databases
SQL Questions For Interview:What is NoSQL?
Answer: It stands for Not Only SQL and provides an alternative to relational databases. Instead of tabular data stores, they use graph stores, key-value stores, document databases, and wide-column stores. It is popular in the agile development world as developers don’t have to finalize the data model before storing information.
How to use a specific database
Here is the SQL command used to select the database containing the tables for your SQL statements:
USE fcc_sql_guides_database;
SELECT and FROM clauses
Use SELECT to determine which columns of the data you want to show in the results. There are also options you can use to show data that is not a table column.
The following example shows two columns selected from the “student” table, and two calculated columns. The first of the calculated columns is a meaningless number, and the other is the system date.
SELECT studentID, FullName, 3+2 AS five, now() AS currentDate FROM student;
SQL Questions For Interview:What are the types of normalization available in SQL and how do you use them?
Answer: Normalization is an important data quality and performance tool in SQL. Applicants who know how to use this feature show that they’re experienced at maintaining SQL databases. What to look for in an answer:
• Definition of normalization in SQL
• Discussion of the different SQL normalization types
• Explanation of the most appropriate times to use each normalization type
Example: “Normalization is a way to improve database efficiency by reducing the overall size of the database and its dependencies. I use this process to accelerate data access speed and improve overall SQL performance.”
SQL Questions For Interview:Can you access or query remote SQL Server database from a Mac, Linux or Ubuntu machine?
Answer: Yes, you can connect or query your remote SQL Server database from your Mac, Linus or Ubuntu machines using Azure Data Studio tool.
SQL Questions For Interview:What is Azure Data Studio?
Answer: Azure Data Studio is an alternative way to SQL Server Management Studio (SSMS) which you can run only on windows machines to query, editing and data development tasks. Azure Data Studio offers a modern editor experience to connect with a remote SQL Server database. It helps us to query and manage data across multiple sources with intellisense.
SQL Questions For Interview:What are the differences between char and nchar?

Answer:

These data type is used to stores characters but these are different in many cases as given below:
char:
This is a fixed length characters data type. It takes one byte per character and used to store non-Unicode characters. Suppose, you declare a field with char(20) then it will allocate memory for 20 characters whether you are using only 10 characters. Hence memory for 10 characters which is empty will be wasted.
nchar:
This is like as char data type but it takes two bytes per character and used to store Unicode characters means multiple languages (like Hindi, Chinese, etc.) characters in the database.

SQL Questions For Interview:How would apply date range filter?
Answer: You can use simple condition >= and <= or similar or use between/and but the trick is to know your exact data type.
Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function.
You also have to be aware of different time zones and server time zone.
To increase query performance you may still want to use between however you should be aware of proper format you should use if not it might misbehave during filtering.
SQL Questions For Interview:How can you combine two tables/views together? For instance one table contains 100 rows and the other one contains 200 rows, have exactly the same fields and you want to show a query with all data (300 rows). This sql interview question can get complicated?
Answer: You use UNION operator. You can drill down this question and ask what is the difference between UNION and UNION ALL. More tricky question are how to sort the view (you use order by at the last query), how to name fields so they appear in query results/view schema (first query field names are used). How to filter groups when you use union using SQL (you would create separate query or use common table expression (CTE) or use unions in from with () or what happens when you have a combination of UNION and UNION ALL
SQL Questions For Interview:How to display top 5 employees with the higest number of sales (total) and display position as a field. Note that if both of employees have the same total sales values they should receive the same position, in other words Top 5 employees might return more than 5 employees?
Answer: Microsoft introduced in SQL Server 2005 ranking function and it is ideal to solve this query. RANK() function can be used to do that, DENSE_Rank() can also be used. Actually the question is ambiguous because if your two top employees have the same total sales which position should the third employee get 2 (Dense_Rank() function) or 3 (Rank() Function)? In order to filter the query Common Table Expression (CTE) can be used or query can be put inside FROM using brackets ().
SQL Questions For Interview:How to get accurate age of an employee using SQL?
Answer: The word accurate is crucial here. The short answer is you have to play with several functions. For more comprehensive answer see the following link SQL Age Function. Calculate accurate age using SQL Server
SQL Questions For Interview:How to return truly random data from a table? Let say top 100 random rows?
Answer: Again this is more SQL Server answer and you can do that using new_id() function in order by clause and using top 100 in select. There is also table sample function but it is not truly random as it operates on pages not rows and it might not also return the number of rows you wanted.
SQL Questions For Interview:What is the difference between UNION and UNION ALL?