What are different types of query supported by SQL?
- DDL (Data Definition Language): Query that are used to define a database structure such as tables, view etc. Create, Alter, and Drop
- DML (Data Manipulation Language): Query that are used to manipulate the data in records, Insert, Update, and Delete
- DCL (Data Control Language): query used to set privileges such as Grant and Revoke database access permission to the specific user
Which constraints we use while creating sql database?
- 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
What are the different type of JOINS in SQL?
There are 4 major types of join
- INNER JOIN
- LEFT JOIN (LEFT OUTER JOIN)
- RIGHT JOIN (RIGHT OUTER JOIN)
- FULL JOIN (FULL OUTER JOIN):
This joins returns all when there is a match either in the RIGHT table or in the LEFT table.
What are the transaction controls?
There are 3 transaction controls
- SET TRANSACTION: Set the name of transaction
- COMMIT: save all changes made through the transaction
- ROLLBACK: roll back the transaction, all changes made by the transaction are reverted back
How many Aggregate Functions are available in SQL ?
There are 7 aggregate functions 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
Differences between truncate and delete in sql server?
The TRUNCATE command is like a DELETE command without the WHERE clause. TRUNCATE statement executes very fast;
TRUNCATE command reset the identity column value, DELETE does not do that.
TRUNCATE is considered a DDL command; whereas, DELETE is DML
We use TRUNCATE command to remove all data from any table (consider testing scenario), rarely used.
TRUNCATE is executed using a table lock and whole table is locked for remove all records.
Note: DELETE and TRUNCATE both can be rolled back if Specified within TRANSACTION and if the current session is not closed
What is Trigger? How many types of triggers are there in SQL?
A trigger (is a database object) is a special kind of a stored procedure that executes in response to certain action on the table like insertion, deletion or updation of data
There are three types of triggers
What are the difference between Trigger and Stored procedure in SQL?
- Stored procedures can return values.
But Triggers can’t return a value.
- A Stored procedure is called directly by a user code.
Whereas, a Trigger is fired when some event occurs.
- We can pass parameters to a Stored procedure.
But we can’t pass parameters to a Trigger.
- We can call stored procedure within a trigger but we can not call trigger within a stored procedure
What is isolation level? what are the different isolation levels in sql server ?
The isolation level of any transaction determines how sensitive your application is to changes other users' transactions make, and consequently, how transaction must hold locks to protect data against these changes.
- Committed Read
- Uncommitted Read
- Repeatable Read