TOP SQL Interview Questions and Answers
What is SQL?
SQL stands for structured query language. It is a database language used for database creation, deletion, fetching rows and modifying rows etc. sometimes it is pronounced as se-qwell.
When SQL appeared?
SQL is appeared in 1974.
What are the usages of SQL?
• To execute queries against a database
• To retrieve data from a database
• To inserts records in a database
• To updates records in a database
• To delete records from a database
• To create new databases
• To create new tables in a database
• To create views in a database.
Does SQL support programming?
No, SQL doesn’t have loop or Conditional statement. It is used like commanding language to access databases.
What are the subsets of SQL?
1. Data definition language (DDL)
2. Data manipulation language (DML)
3. Data control language (DCL)
What is data definition language?
Data definition language(DDL) allows you to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.
What is data manipulation language?
Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.
• Insert data into database
• Retrieve data from the database
• Update data in the database
• Delete data from the database.
What is data control language?
Data control language allows you to control access to the database. It includes two commands
GRANT and REVOKE.
GRANT: to grant specific user to perform specific task.
REVOKE: to cancel previously denied or granted permissions.
What are the type of operators available in SQL?
1. Arithmetic operators
2. Logical operators
3. Comparison operator
What is the difference between clustered and non clustered index in SQL?
There are mainly two type of indexes in SQL, Clustered index and non clustered index. The differences between these two indexes is very important from SQL performance perspective.
1) One table can have only one clustered index but it can have many non clustered index.(approximately 250).
2) clustered index determines how data is stored physically in table. Actually clustered index stores data in cluster, related data is stored together so it makes simple to retrieve data.
3) reading from a clustered index is much faster than reading from non clustered index from the same table.
4) clustered index sort and store data rows in the table or view based on their key value, while non cluster have a structure separate from the data row.
What is the SQL query to display current date?
There is a built in function in SQL called GetDate() which is used to return current timestamp.
Which types of join is used in SQL widely?
The knowledge of JOIN is very necessary for an interviewee. Mostly used join is INNER JOIN and (left/right) OUTER JOIN.
What is “TRIGGER” in SQL?
Trigger allows you to execute a batch of SQL code when an insert, update or delete command is executed against a specific table.
Actually triggers are special type of stored procedures that are defined to execute automatically in place or after data modifications.
What is self join and what is the requirement of self join?
Self join is often very useful to convert a hierarchical structure to a flat structure. It is used to join a table to itself as like if that is the second table.
What are set operators in SQL?
Union, intersect or minus operators are called set operators.
What is a constraint? Tell me about its various levels.
Constraints are representators of a column to enforce data entity and consistency. There are two levels :
1. column level constraint
2. table level constraint
Write an SQL query to find names of employee start with ‘A’?
SELECT * FROM Employees WHERE EmpName like ‘A%’
Write an SQL query to get third maximum salary of an employee from a table named employee_table.
1. SELECT TOP 1 salary
2. FROM (
3. SELECT TOP 3 salary
4. FROM employee_table
5. ORDER BY salary DESC ) AS emp
6. ORDER BY salary ASC;
What is ACID property in database?
ACID property is used to ensure that the data transactions are processed reliably in a database system.
A single logical operation of a data is called transaction.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability.
Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.
Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.
Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.
The most important DDL statements in SQL are:
CREATE TABLE – creates a new database table
ALTER TABLE – alters (changes) a database table
DROP TABLE – deletes a database table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index
Operators used in SELECT statements.
<> or != Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range LIKE Search for a pattern
SELECT column_name(s) FROM table_name
SELECT DISTINCT column_name(s) FROM table_name
SELECT column FROM table WHERE column operator value
SELECT column FROM table WHERE column LIKE pattern
SELECT column,SUM(column) FROM table GROUP BY column
SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value
Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.
The SELECT INTO Statement is most often used to create backup copies of tables or for archiving records.
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source
SELECT column_name(s) INTO newtable [IN externaldatabase] FROM source WHERE column_name operator value
The INSERT INTO Statements:
INSERT INTO table_name VALUES (value1, value2,….)
INSERT INTO table_name (column1, column2,…) VALUES (value1, value2,….)
The Update Statement:
UPDATE table_name SET column_name = new_value WHERE column_name = some_value
The Delete Statements:
DELETE FROM table_name WHERE column_name = some_value
Delete All Rows:
DELETE FROM table_name or DELETE * FROM table_name
BETWEEN … AND
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2 The values can be numbers, text, or dates.
What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Which system tables contain information on privileges granted and privileges obtained?
Which system table contains information on constraints on all the tables created?obtained?
What does the following query do? SELECT SAL + NVL(COMM,0) FROM EMP;?
This displays the total salary of all employees. The null values in the commission column will be replaced by 0 and added to salary.
What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.