Top 40 SQL Interview Questions and Answers - Follow us for More
1.Is SQL-supported programming?
Ans. Basically, SQL is a command-based programming language but it doesn’t contain any control flow statements.
2.State the difference between char and varchar.
Ans. Both char and varchar are datatypes only, used for character strings. The only difference between them is the length. Varchar is used for a variable length of strings and char is used for a fixed length of strings. In char data type, if the length of the string is less than the fixed length then it is padded with blank spaces to attain the fixed length. While varchar, padding won’t be done.
3.Write the Syntaxes for joins.
Ans.
INNER JOIN: select column_name From table1 INNER JOIN table2 on table1.column_name = table2.column_name;
OUTER JOIN: select column_name From table1 OUTER JOIN table2 on table1.column_name = table2.column_name;
LEFT JOIN: select column_name From table1 LEFT JOIN table2 on table1.column_name = table2.column_name;
RIGHT JOIN: select column_name From table1 RIGHT JOIN table2 on table1.column_name = table2.column_name;
FULL JOIN: select column_name From table1 INNER JOIN table2 on table1.column_name = table2.column_name WHERE condition;
4.Write the syntaxes for set operators.
Ans. Consider two tables T1 and T2 having attributes like id, name.
UNION: select name from T1 UNION select name from T2;
UNION ALL: select name from T1 UNION ALL select name from T2;
INTERSECT: select name from T1 INTERSECT select name from T2;
MINUS: select name from T1 MINUS select name from T2;
5.What is DBMS? Explain types of DBMS.
Ans. DBMS is software that interacts with users, applications, and the database itself to capture and analyze the data. It is a structured collection of data. There are two types of DBMS. They are:
Relational DBMS: Data is stored in tables(relations). This involves the concept of tuples, attributes.
Non – Relational DBMS: In this, there won’t be any concept of tables, tuples, attributes.
6.State the difference between OLTP and OLAP.
Ans.
OLTP: OLTP means Online Transaction Processing, it is an online database modification system. When data in the database is changed, this database transaction occurs.
OLAP: OLAP means Online Analytical Processing, it is an online query response system.
7.What are joins in SQL?
Ans. As the name suggests a JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:
Inner Join
Right Join
Left Join
Self Join
8.What are tables and Fields?
Ans. Data organized in a model with Rows and Columns is called a table. Rows are horizontal and Columns can be categorized as vertical. A table has a specified number of columns called fields but can have an infinite number of rows which is called a record.
9.What is Foreign Key?
Ans.
FOREIGN KEY: This is a key used for linking 2 tables.
A FOREIGN KEY is a field or maybe a collection of fields in a table that corresponds to the PRIMARY KEY of another table.
The table containing the FOREIGN KEY is called the CHILD TABLE and the table containing the CANDIDATE KEY is called the PARENT TABLE.
10.What is the difference between DELETE and TRUNCATE statements?
Ans: DELETE:
The DELETE command helps to delete a row of a table.
The rollback of data is possible after using the delete statement.
It is a DML command.
It is slower than a TRUNCATE statement.
TRUNCATE:
The TRUNCATE command helps to delete a row of a table.
Rollback of data is not possible.
It is a DDL command.
As compared to DELETE the TRUNCATE command is faster.
11.What is Data Integrity?
Ans: The assurance of consistency and accuracy of the data over the entire life cycle is Data Integrity. It is a critical aspect of the implementation, design, and usage of any system that processes, stores 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.
12.What is the SELECT statement?
Ans: SELECT is a SQL command that is used for selecting data from a database. The data which is returned is saved in a result table, called the result-set.
Example: select * from customers;
13.What is normalization?
Ans. Normalization is a way of organizing fields and tables of the database in a way that reduces redundancy and dependency. The main objective is to create a single table where operations like add, delete or modify can be performed on the field.
14.What is the ACID property in a database?
Ans.
To ensure that the data transactions are processed reliably in a database system we use the ACID property.
Atomicity: It states that each transaction is all or nothing. It states that the entire transaction fails if one part of the transaction fails and the database state is left unchanged.
Consistency: It ensures that the data must follow all validation rules. According to this a transaction never leaves your database without its state being completed.
Isolation: The main goal of providing isolation is concurrency control. This property ensures that the concurrent property of execution should not be met.
Durability: this property states that once a transaction has been committed, it remains committed, whatever the situation be, even power loss, crashes, or errors.
15.Explain different types of indexes.
Ans.
There are three major types of index:
Unique Index: It does not allow the field to possess redundant values if the column is indexed uniquely. A unique index can be applied automatically if a primary key is defined.
Clustered Index: It rearranges the order of the table and searches based on key values. Every table has only one clustered index.
Non-Clustered Index: It causes no change in the order of the table and keeps a logical order of the data intact. There can be many non-clustered indexes for a table.
16.What are the types of joins and explain each?
Ans. There are 4 types of Join:-
Inner Join: Inner join returns rows when there is at least one match of rows between the tables.
Right Join: Right join return rows which are common between the tables and all rows of the Right-hand side table. Simply, it returns all the rows from the right-hand side table even though there are no matches in the left-hand side table.
Left Join: Left join return rows which are common between the tables and all rows of the Left-hand side table. Simply, it returns all the rows from the Left-hand side table even though there are no matches in the Right-hand side table.
Full Join: Full join return rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.
17.What do you mean by “Trigger” in SQL?
Ans. A trigger is a special type of stored procedure that executes automatically in place or after data changes. It allows executing a batch of code when an insert, update, or any other query is executed against a specific table.
18.What is a constraint?
Ans.
Constraints are the limitations on the data type of a given table. A constraint can be specified while creating or altering the table statement.
NOT NULL.
CHECK.
DEFAULT.
UNIQUE.
PRIMARY KEY.
FOREIGN KEY.
19.What are the set operators in SQL?
Ans.
SQL supports a set of operations that can be performed on the table data. These are used to get meaningful results from data stored in the table, under different special conditions. The set operators in SQL are :
UNION.
UNION ALL
INTERSECT
MINUS
20.What is the usage of the DISTINCT keyword?
Ans. The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetch unique records.
21.What is a primary key?
Ans. A primary key also called a primary keyword, is a key in a relational database that is unique for each record. This is a special kind of unique key, and it has an implicit, NOT NULL constraint. It means Primary key values cannot be NULL. A relational database must always have one and only one primary key.
22.What is a unique key?
Ans. A Unique key constraint uniquely identified each record in the database. This provides uniqueness for the column or set of columns. A unique key is a set of one or more than one field/column of a table that uniquely identifies a record in a database table. You can say that it is a little like a primary key but it can accept only one null value and it cannot have duplicate values.
23.What are all the different normalizations?
Ans.
The normal forms can be divided into 4 forms, and they are explained below -.
First Normal Form (1NF): According to this, remove all the redundant columns from a table. Creation of tables for the related data and identification of unique columns.
Second Normal Form (2NF): Should follow all requirements of the first normal form. Arranging the data subsets in separate tables and Creation of relationships between the tables using a primary key.
Third Normal Form (3NF): Should follow all requirements of the 2NF. Removing the columns which are not dependent on primary key constraints.
BCNF (Boyce-Codd Normal Form): Meeting all the requirements of the third normal form and it should not have multi-valued dependencies.
24.What is a query?
Ans. A query is a request for data. A DB query is a code written to get the information back from the database. You ask the database for something and it answers in the best way it knows with data as a result of a query.
25.What is a stored procedure? State the Advantages and Disadvantages of Stored Procedure?
Ans. A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. Stored Procedure is a function that consists of many SQL statements to access the database system. Stored procedure supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data. The disadvantage is that it can be executed only in the Database and uses more storage memory.
26.Write a SQL query to find the names of employees that begin with ‘A’?
Ans. To display the name of the employees that begin with ‘A’, type in the below command:
SELECT * FROM Table_name WHERE EmpName like ‘A%’
27.What is the need for group functions in SQL?
Ans. Group functions work on the set of rows and return one result per group. Some of the commonly used group functions are AVG, COUNT, MAX, MIN, SUM, VARIANCE.
28.What is Alias in SQL?
Ans. An alias is a SQL function that most, if not all, RDBMSs support. It’s a fictitious name given to a table or table column for the purposes of a SQL query. Furthermore, aliasing can be used as an obfuscation strategy to protect the true names of database fields. A correlation name is another name for a table alias. The “AS” keyword represents an alias.
29.What is a User-defined function? What are its various types?
Ans. The user-defined functions in SQL are like functions in any other programming language that accept parameters, perform complex calculations, and return a value. They are written to use the logic repetitively whenever required. There are two types of SQL user-defined functions:
Scalar Function: As explained earlier, user-defined scalar functions return a single scalar value.
Table-Valued Functions: User-defined table-valued functions return a table as output.
Inline: returns a table data type based on a single SELECT statement.
Multi-statement: returns a tabular result-set but, unlike inline, multiple SELECT statements can be used inside the function body.
30.What is CLAUSE in SQL?
Ans. SQL clause helps to limit the result set by providing a condition to the query. A clause helps to filter the rows from the entire set of records.
For example – WHERE, HAVING clause.
31.What is the difference between ‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
Ans. 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.
32.How to create empty tables with the same structure as another table?
Ans. Creating empty tables with the same structure can be done smartly by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.
SELECT * INTO Students_copy FROM Students WHERE 1 = 2;
33.What is AUTO_INCREMENT?
Ans. AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table. Since the primary key is unique for each record, we add this primary field as the AUTO_INCREMENT field so that it is incremented when a new record is inserted. The AUTO-INCREMENT value is by default starts from 1 and is incremented by 1 whenever a new record is inserted.
34.How to create empty tables with the same structure as another table?
Ans. Creating empty tables with the same structure can be done smartly by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. Hence, SQL prepares the new table with a duplicate structure to accept the fetched records but since no records get fetched due to the WHERE clause in action, nothing is inserted into the new table.
35.What is the main difference between SQL and PL/SQL?
Ans. SQL is a query language that allows you to issue a single query or execute a single insert/update/delete whereas PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a full program (loops, variables, etc.) to accomplish multiple operations such as selects/inserts/updates/deletes.
36.What are Local and Global variables?
Ans.
Local variables: These variables can be used or exist only inside the function. These variables are not used or referred to by any other function.
Global variables: These variables are the variables that can be accessed throughout the program. Global variables cannot be created whenever that function is called.
37.How can you fetch the first 5 characters of the string?
Ans. There are a lot of ways to fetch characters from a string.
For example: Select SUBSTRING(StudentName,1,5) as studentname from student;
38.What is a View?
Ans. A view is a virtual table that consists of a subset of data contained in a table. Since views are not present, it takes less space to store. A view can have data of one or more tables combined and it depends on the relationship.
39.How to change the column data type in SQL?
Ans.We can change the data type of the column using the alter table. This will be the command: ALTER TABLE table_name MODIFY COLUMN column_name datatype; We start off by giving the keywords ALTER TABLE, then we will give in the name of the table. After that, we will give in the keywords MODIFY COLUMN. Going ahead, we will give in the name of the column for which we would want to change the datatype and finally we will give in the data type to which we would want to change.
40.What is the difference between SQL and NoSQL databases?
Ans. SQL stands for structured query language and is majorly used to query data from relational databases. When we talk about a SQL database, it will be a relational database. But when it comes to the NoSQL database, we will be working with non-relational databases.