SQL Interview Questions (Set-1) – User friendly Tech help
This is our first set of SQL interview Questions.
n
Prepare well and crack your dream job. It is said,“Fall seven times, stand up eight.”(Japanese proverb), so work hard to achieve heights.
n
 |
| Work Hard |
n
nFollow us on Facebook for more updates and learning!!!
n
n
- n
- What are various types of SQL statements?
- What is the difference between Truncate, Drop and Delete?
- Difference between Local and Global Temp tables?
- What are indexes and types of indexes?
- How to copy only structure of Table A to Table B?
- Meaning of BCP in SQL?
n
n
n
n
n
n
n
Hints:-
n
1.
n
a. Data definition language
n
- n
- Create
- Alter
- Drop
- Truncate
n
n
n
n
n
b. Data Manipulation language
n
- n
- Select
- Insert
- Delete
- Update
n
n
n
n
n
c. Data Control Language
n
d. Transaction control statements
n
- n
- Commit
- Rollback
- Save point
n
n
n
n
2. Delete, is used to remove rows from a table, we can use where clause to limit the rows to be deleted. After delete we need to perform Commit or Rollback operation to make the changes permanent, thus we can say we have chance to undo the delete operation .It is a DML command. Delete will cause delete triggers on the table to be fired.
n
Truncate, it is used to remove all rows from a table. The operation cannot be rolled back and no triggers are fired. It is a DDL command and faster as It does not use the concept of undo operation thus even logs are not maintained for the deleted data.
n
Drop, is more dangerous as it removes table from the database unlike above commands which were only removing rows from the table. Like truncate it cannot be rolled back and no triggers are fired in this case, further this also a DDL command.
n
3. Temporary tables (temdb) are automatically deleted when they are not in use.
n
- n
- Local, It is prefixed with single ‘#’ when while creation. Like create table #Study(ID int not null). Further they are available only to the connection that creates it and are deleted when the connection is closed. We can create a same local temp table with the same name but in a different connection.
- Global, It is prefixed by ‘##’ values, like create table ##Study (ID int not null).They are visible to all the connections of SQL server and are destroyed only when the last connection referencing the table is closed.
n
n
n
4. Index: – It is an structure associated with a table or view which speed up the query retrieval time. An index contains keys which are combination of one or more columns of table or view. These keys are stored in B-Tree structure for efficient search of data from a table.
n
Types:-
n
Clustered Index:- Example like phone directory, where each entry in the directory represents one row of the table. A table can have only one clustered index.
n
Non Clustered Index: – Example , index at the back of the book, where indexed values contains pointers to the actual rows(page number in a book).
nSyntax:-Create index (index name) ON table_Name (Column name)n
5. Select * into TableB from TableA where 0=1
n
6. BCP (Bulk copy Program) is a command line utility to bulk copy data between an SQL server and a data file (both in or out).We can use table or query to export/import data. Example we need to move data from database A to file B , so we would write bcp database A .TABLE A out file B.
n
