Database is the collection of data which is stored in an organized way.
RDBMS stands for Relational Database Management System. It is the collection of tables which is related to each other
SQL stands for Structured Query Language. This language is used to interact with Relational Database Management System.
No, sql commands are not case sensitive.
Null Represents undefined value
Distinct is used to supress duplicate row in select query
We use order by clause to sort data either in ascending or descending order
Ascending is the default order
Data integrity ensures that the data which is going to enter in the database is reliable, accurate and consistent. Data integrity maintains the correctness and completeness of data.
Constraints are the rules which we applied on the table’s column to ensure the data integrity.
When we want to make a particular column to be restricted for particular data in that case we use check constraints
We want to restrict gender column to take value only either ‘M’ or ‘F’ then we use check constraints in the following manner
create table t7(id int, gender char (1) check (gender in ('M','F')));
Take another example in which we have a column age in which we want to add age above 36 then we use check constraints.
create table t8(id int, age int check(age>36));
We use identity in sql server to create auto increment column.
create table emp(id int primary key identity(1,1), name varchar(100), salary int)
The stored procedure is a set of SQL Statements. It is precompiled object. Stored procedure increases the performance of the database as they are precompiled.
Triggers can be defined as an area of code Which executes in response of a particular action like insert, update, delete, Create, drop etc. Triggers executes automatically.
We have two types of triggers
a) DDL Triggers b) DML Trigger
DML Triggers further categorized in following two category
We use IS NULL operator to select null value.
We use like operator to match pattern.
Difference between primary key and unique key are as follows: -
a) We cannot enter null in primary key but we can have one null value in unique key column.
b) When we create primary key cluster index created automatically but unique key non clustered index created automatically.
c) Searching will be fast with the help of primary key as compare to unique key.
d) A table can have only one primary key but we can have more than one unique key.
Both commands are used to remove data from tables but they have following differences: -
1. Delete is DML command but Truncate is DDL command.
2. Delete command can be used with where clause if we need to delete specific row but truncate command doesn’t work with where clause so it removes all rows.
3. Truncate set the identity from the beginning but delete not.
4. Delete command delete row one by one and maintains its data in transaction log but truncate de allocates its data pages.
5. Delete is slower than truncate as it has to maintains its each entry in transaction log.
6. Delete can be roll backed but truncate not.
7. Delete command activates all triggers if any associated but truncate not.
Note: - delete and truncate both can be roll backed if used with transaction.
1. Count function require column to count data but count (*) will count all rows
2. Count function do not count NULL values in column but count (*) will count all rows whether any column having null or not.
1. Where clause is used to filter the row but having clause is used to filter groups
2. We can use where clause with select, insert and update statements but having can be used only with select statement.
3. Where clause comes before group and having always comes after the group in select statement.
There are following difference between stored procedure and user defined function
a) We can use all DML command like insert, update and delete and select command in stored procedure but user defined function can use only select statement.
b) Function must have to return a value but in case stored procedure it is not mandatory.
c) We can call function from stored procedure but we can’t call stored procedure from function.
d) We can use function in select statement and can use its output with select query but we can’t call stored procedure from select query. We need to use exec command to execute stored procedure.
e) We can’t use transactions and exception handling in function but stored procedure support both transactions and exception handling.
f) Function support only input parameter not ouput parameter but in stored procedure we can use both.
g) User defined function can return only single value but stored procedure can return multiple value result set.
Let’s assume we have following table
So we will perform following query to find out the 2nd highest salary records.
select top 1 * from (select top 2 * from emp order by salary desc) as temp_table order by salary
it will show the following output
We use alter command to delete column from the table. Let’s we have following emp table in which I want to delete salary column
alter table emp drop column salary
We use drop command to delete the table
Drop table table_name
drop table emp