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:
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.
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.
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.
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.
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.
Data definition language(DDL) allows you to CREATE, ALTER and DELETE database objects such as schema, tables, view, sequence etc.
Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.
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.
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.
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.
Constraints are representators of a column to enforce data entity and consistency. There are two levels :
SELECT * FROM Employees WHERE EmpName like 'A%'
SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC ) AS emp ORDER BY salary ASC;
The main differences between SQL DELETE and TRUNCATE statements are given below:
|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 table||TRUNCATE 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.|