Q) Clauses in SQL Server
A) Following are some of clauses in SQL server:
- Where clause: Used to filter database on a condition
- Order by clause: used for sorting the data in ascending, descending,etc.
- Distinct: used to remove duplicates rows
- Group by: For grouping on given condition
- Having clause: Used to filter on grouping
Q) What is ‘Between’ Operator in SQL Server?
A)Between operator is used to compare column values with range of values. It works with lower limit and upper limit.
Example: To display the employee records whose salaries are between 2000 and 5000
Select * from Employee where sal between 2000 and 5000
Q) What is ‘In’ operator in SQL Server?
A) ‘In’ operator is used to compare column values with multiple values
Example: To display employee records whose name is mak and king, we use it as follows
Select * from Employee where EmpName in ('mak','king')
Q) What is ‘Is’ operator in SQL Server?
A) This operator is used to compare columns value with null or not null
Example: Display employee records who got no commissions
select * from employee where comm is null or comm==0
Q) What is ‘Like’ operator in SQL Server?
A) It is used to compare a column value with string patterns
Example: Get all employees whose name has alphabet ‘a’
Select * from employees where empname like '%a%'
Note:Here % represents anything before or after alphabet a.
Q) What are DML commands?
A)DML stands for Data Manipulation Language. The following are its commands:
1.Insert: Used to insert data into tables
2.Update: Used to update data into tables
3.Delete: Used to delete records from tables
4.Merge: Used to Merge the data of tables
Q) What are DDL commands?
A) DDL stands for Data Definition Language. The following are its commands:
1.Create: Used to create a new database or table
2.Alter: Used to alter the table structure
3.Drop: Used to drop the table
4.Truncate: Used to truncate the table
Q) What is Identity ?
A) Identity keyword is used to generate serial number for a column in a table. Identity is used mostly for primary key columns
Identity(Start with,Increment by)
Example: Create table Customer (CustomerId int Identity(100,1), CustName varchar(50))
Note: Here when inserting we don’t have to give identity values, It will take by default starting from 100(in above case) and increasing 1 each time
Q) What is Unique Key?
A) Unique key doesn’t allow duplicates. Its always unique. (Even though it accepts null values. Null value can be present only once)
Q) What is Primary Key?
A) Primary key is used to uniquely identify the records in a table.
A table allows only one primary key. The only difference between primary and Unique key is primary key doesnt allow null where as unique key allows.
Q) What is Foreign key?
A) It is used to establish relationship. It is an attribute in one table that refers to primary key or unique key of another table.
Foreign key should match with primary key value or it can be null.
Q) Built in functions in SQL server?
A) Build in functions can be broadly categorized into two categories:
Single Row Functions: The functions which process only one row at a time. Few examples of single row functions are:
Upper(): Converts a string to upper case
Lower(): Converts a string to lower case
Len(): Returns the length of string
Left(): Returns specific number of characters from left side
Right(): Returns specific number of characters from right side
Multi Row Functions: The functions which process multiple rows at same time. Few examples of Multi row functions are:
Max(): Returns max value of given expression
Min():Returns Min value of given expression
Sum(): Returns sum of given expression
Avg(): Returns Average of given expression