SQL



1. What is SQL?  

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

SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language.

Also, they are using different dialects, such as:

  • MS SQL Server using T-SQL
  • Oracle using PL/SQL
  • MS Access version of SQL is called JET SQL (native format) etc.


2. Why you should use SQL?  

  • Allows users to access data in relational database management systems.
  • Allows users to describe the data.
  • Allows users to define the data in database and manipulate that data.
  • Allows to embed within other languages using SQL modules, libraries & pre-compilers.
  • Allows users to create and drop databases and tables.
  • Allows users to create view, stored procedure, functions in a database.
  • Allows users to set permissions on tables, procedures, and views


3. What are the different type of SQL's statements ?  

This is one of the frequently asked SQL Interview Questions to freshers. SQL statements are broadly classified into three. They are

1. DDL – Data Definition Language DDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.

2. DML– Data Manipulation Language DML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. Select statement is considered as a limited version of DML, since it can't change data in the database. But it can perform operations on data retrieved from DBMS, before the results are returned to the calling function.

3. DCL– Data Control Language
DCL is used to control the visibility of data like granting database access and set privileges to create tables etc. Example - Grant, Revoke access permission to the user to access data in database.



4. What are the Advantages of SQL ?  

1. SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL ,MYSQL etc.

2. SQL is easy to learn. The statements are all made up of descriptive English words, and there aren't that many of them.

3. SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.




5. What is a database transaction?  

Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.



6. What are properties of a transaction?  

Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience. Properties of the transaction can be summarized as ACID Properties.

1. Atomicity A transaction consists of many steps. When all the steps in a transaction gets completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.

2. Consistency

The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.

3. Isolation

Every transaction should operate as if it is the only transaction in the system.

4. Durability

Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.



7. 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.



8. 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


9. 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.


10. What are the type of operators available in SQL?  

  1. Arithmetic operators
  2. Logical operators
  3. Comparison operator


11. 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.



12. 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.


13. 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.


14. 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.



15. 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.


16. What are set operators in SQL?  

Union, intersect or minus operators are called set operators.


17. 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 :

  • column level constraint
  • table level constraint


18. Write an SQL query to find names of employee start with 'A'?  

SELECT * FROM Employees WHERE EmpName like 'A%'


19. Write an SQL query to get third maximum salary of an employee from a table named employee_table.  

    SELECT TOP 1 salary   
    FROM (  
    SELECT TOP 3 salary  
    FROM employee_table  
    ORDER BY salary DESC ) AS emp  
    ORDER BY salary ASC;      


20. What is the difference between DELETE and TRUNCATE statement in SQL?  

The main differences between SQL DELETE and TRUNCATE statements are given below:

No.DELETETRUNCATE
1)DELETE is a DML command.TRUNCATE is a DDL command.
2)We can use WHERE clause in DELETE command.We cannot use WHERE clause with TRUNCATE
3)DELETE statement is used to delete a row from a tableTRUNCATE statement is used to remove all the rows from a table.
4)DELETE is slower than TRUNCATE statement.TRUNCATE statement is faster than DELETE statement.
5)You can rollback data after using DELETE statement.It is not possible to rollback after using TRUNCATE statement.


Java Interview Question

.Net Interview Question

PHP Interview Question

AngularJS Interview Questions