SQL Interview Question And Answers
Question #1) What is SQL?
Structured Query Language is a database tool which is used to create and access database to support software application.
Question #2) What are tables in SQL?
The table is a collection of record and its information at a single view.
Question #3) What are different types of statements supported by SQL?
There are 3 types of SQL statements
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.
ALTER: The ALTER table is used for modifying the existing table object in the database.
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.
Question #4) How do we use DISTINCT statement? What is its use?
The DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.
Syntax: SELECT DISTINCT column_name(s)
FROM table_name;
Question #5) What are different Clauses used in SQL?
WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfil the given condition
Syntax: SELECT column_name(s)
FROM table_name
WHERE condition;
GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.
Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name;
HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of result or the entire result as a single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause
Syntax: SELECT column_name(s)
FROM table_name
GROUP BY column_name
HAVING condition;
ORDER BY clause: This clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.
Syntax: SELECT column_name(s)
FROM table_name
WHERE condition
ORDER BY column_name ASC|DESC;
USING clause: USING clause comes in use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.
Syntax: SELECT column_name(s)
FROM table_name
JOIN table_name
USING (column_name);
Question #6) Why do we use SQL constraints? Which constraints we can use while creating database in SQL?
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 columns 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
Question #7) What are different JOINS used in SQL?
There are 4 major types of joins made to use while working on multiple tables in SQL databases
INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one column matched
Syntax: SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON column_name1=column_name2;
LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from a LEFT table and its matched
rows from a RIGHT table.
Syntax: SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON column_name1=column_name2;
RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from a LEFT table.
Syntax: SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON column_name1=column_name2;
FULL JOIN (FULL OUTER JOIN): This joins returns all when there is a match either in the RIGHT table or in the LEFT table.
Syntax: SELECT column_name(s)
FROM table_name1
FULL OUTER JOIN table_name2
ON column_name1=column_name2;
Question #8) What are transaction and its controls?
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 comes from transactions.
In simple word, 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
Question #9) What are properties of the transaction?
Properties of 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 database with committed transactions persist as it is even after system failure
Question #10) How many Aggregate Functions are available there in SQL?
SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.
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
For more questions visit : Read More
