SQL Server:

Introduction:
 SQL Server is a software, developed by Microsoft, which is an RDBMS Product.
 It is a Platform Dependent.
 It is both GUI and command-based software.
 It supports SQL (SEQUEL) language which is an IBM product.
Usage of SQL Server:
 It is used to create and Maintain the Database.
 It is used to Integrate the Data using SSRS.
 It used to analyse the data through analysis services (SSAS).
 It is used to generate the reports through Reporting services (SSRS)
SQL Server Instance
 An instance is an installation of SQL Server.
 An instance is an exact copy of the same software.
 If we install 'n' times, then 'n' instances will be created.
 There are two types of instances in SQL Server a) Default b) Named.
 Only one default instance will be supported in one Server.
 Multiple named instances will be supported in one Server.
 Default instance will take the server name as Instance name.
 Default instance service name is MSSQLSERVER.
 16 instances will be supported in 2000 version.
 50 instances will be supported in 2005 and later versions.
SQL Datatype:
 SQL Datatype will define the type of value can be stored into the Table column.
Suppose If we want to store only integer values then we can define the datatype as Int.
 SQL data types can be broadly divided into following categories.
 Numeric data types such as int, tinyint, bigint, float, real etc.
 Date and Time data types such as Date, Time, Datetime etc.
 Character and String data types such as char, varchar, text etc.
 Unicode character string data types, for example nchar, nvarchar, ntext etc.
 Binary data types such as binary, varbinary etc.
Point to remember in case of assigning the Datatypes:
 Not all data types are supported by every relational database vendor. For example,
Oracle database doesn’t support DATETIME and MySQL doesn’t support CLOB
data type. So while designing database schema and writing sql queries, make sure to
check if the data types are supported or not.
 Every relational database vendor has it’s own maximum size limit for different data
types, you don’t need to remember the limit. Idea is to have the knowledge of what
data type to be used in a specific scenario.
SQL Numeric Data Types:

DATATY FROM TO
PE
bit 0 1
tinyint 0 255
smallint -32,768 32,767
int -2,14,74,83,648 2,14,74,83,647
bigint - 92,23,37,20,36,85,47,70,000
92,23,37,20,36,85,47,70,000
decimal 1E+38 10^38 -1
numeric 1E+38 10^38 -1
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

SQL Date and Time Data Types:

DATATYP DESCRIPTION
E
DATE Stores date in the format YYYY-MM-DD
TIME Stores time in the format HH:MI:SS
DATETIM Stores date and time information in the format YYYY-MM-DD HH:MI:SS
E
TIMESTA Stores number of seconds passed since the Unix epoch (‘1970-01-01
MP 00:00:00’ UTC)
YEAR Stores year in 2 digit or 4 digit format. Range 1901 to 2155 in 4-digit
format. Range 70 to 69, representing 1970 to 2069.
 SQL Character and String Data Types:

DATATYPE DESCRIPTION
CHAR Fixed length with maximum length of 8,000 characters
VARCHAR Variable length storage with maximum length of 8,000 characters
VARCHAR(m Variable length storage with provided max characters, not supported in
ax) MySQL
TEXT Variable length storage with maximum size of 2GB data

SQL Unicode Character and String Data Types:

DATATYPE DESCRIPTION
NCHAR Fixed length with maximum length of 4,000 characters
NVARCHAR Variable length storage with maximum length of 4,000 characters
NVARCHAR(m Variable length storage with provided max characters
ax)
NTEXT Variable length storage with maximum size of 1GB data

Exercises:
1) Which Version of the SQL server We are using?

 GUI: From the sql server Configuration Manger

 Command Line:select@@version

2) What are the SQL Server Authentication Modes?

 SQL Server Supports two authentication modes.

 Windows authentication mode and SQL server Authentication Mode.
 By default it ‘s windows authentication mode.
 With Windows authentication, users are already logged onto Windows and do
not have to log on separately to SQL Server.
 Windows authentication is more secure than Sql Server Authentication.
Windows uses a series encrypted messages to authenticate users in sql server.
When sql server logins are user, sql login names and encrypted passwords are
passed across the network, Which Makes them less secure.

3) What is the difference between Char and Varchar?

  Char is a Fixed Length Datatype. That me means if you create a char of 10
length, it always consumes 10 bytes, irrespective of you store 1 or 10
characters.
 When we Know The length of the String then we will go for the char.
 Varchar is a Variable length Datatype. That means if you create a varchar of
10 length, it will consume length equivalent to the number of Characters. So,
if we store 3 characters it will consume 3 bytes only.
 When we Don’t know the length of the string then we will go for the Varchar.

4) What is the Difference between Char, Varchar and Nchar, Nvarchar Datatypes?

a. Char and Varchar cannot store Unicode characters

b. Nchar and Nvarchar Can store Unicode Charaters.

5) What is the Default Value in Sql server?

By default SQL server stores null values. If we are not providing any value to
the Column, then by default it will store the null value into it.
6) What are the types instances we have in sql server?
SQL server supports two types of instances.
 Default Instance. Only One Default instance Can be create throughout the
server.
 Named Instance. We can create 16 named instances till 2005 and 50 instances
in the later versions.

7) What is the difference between Float and Numeric Decimal in Sql server?

 Float and Real Datatypes does not store exact values for many numbers.The
storing Values can be extremely closed.
 Numeric/Decimal are fixed precision data types. It will store the values with
exact precision and scale.
 If you need to store the data where small difference does not matter, then we
can use Float or Real. But if you need have an exact data then we go for
Numeric decimal.
SQL Statements:
 SQL is Structured Query Language, which is a computer language for storing,
manipulating and retrieving data stored in a relational database.
 Allows users to access data in the relational database management systems.
 Allows users to describe the data.
 Allows users to define the data in a database and manipulate that data.
 Allows to embed within other languages using SQL modules, libraries & pre-
compilers.
 Allows users to create and drop databases and tables.
 Allows users to create view, stored procedure, functions in a database.
  Allows users to set permissions on tables, procedures and views.
SQL Commands:
The Following are the SQL Commands Used to Interact with the Database.
1) DDL (Data Definition Language)
2) DML (Data Manipulation Language)
3) DRL (Data Retrieve Language)
4) DCL (Data Control Language)
5) TCL (Transaction Control Language)
DDL Commands:
1) Create: It is used Creates a New Table in the Database.
2) Alter: It is used to modify the existing object such as Table Structure.
3) Drop: it is used to Drop the existing object such as table, view etc.
4) Truncate: It is used to delete the data from table permanently.
DML Commands:
1) Insert: It is used to insert the records into the existing Table.
2) Update: It is used update the Whole data or selected data in a table.
3) Delete: it used to delete the completed data or selected Data from a table.
4) Merge: It is used perform any of the above operation based on the condition.
DRL Commands:
1) Select: It is used to retrieve the data from the table.
DCL Commands:
1) Grant: it gives the privileges to the user
2) Revoke: it will take back the privileges granted to the User.
TCL Commands:
1) Commit: It saves the Work done.
2) Rollback: it restores the database into original state till the last committed
transactions.
3) Save Transaction: Set the Save point within a transaction.
Table Creation:
 Tables are used to store the Data in the Database.
 Tables are uniquely named in the Database.
 Each table contains one more Column
Emp Table Creation Structure:
CREATE TABLE emp
(
empno INT,
enameVARCHAR(10),
job VARCHAR(9),
mgr INT NULL,
hiredate DATETIME,
salNUMERIC(7,2),
comm NUMERIC(7,2) NULL,
dept INT
)
Dept Table Structure:
CREATE TABLE dept
(
deptno INT NOT NULL,
dnameVARCHAR(14),
locVARCHAR(13)
)

Syntax to add the column to an existing empTable:

Alter Table emp add Email Varchar(100);
Syntax to Drop the column from an emp table:
Alter Table emp drop Column Email
Syntax to modify the size of the Datatype of Job column in emp table:
Alter Table emp modify Job Varchar(100)
Syntax to change the Datatype of Job Column from Varchar to Nvarchar
Alter table emp modify Job Nvarchar(200)
Note: Before altering the definition of the table like changing the the size of the Datatype or
Datatype.we have to check the data in the column and alter it accordingly. No issue in case of
column drop,rename and add.
Syntax to Drop The table:
Drop Table Emp;
Syntax to truncate the data in the Table:
Truncate table Emp;

Syntax to rename the Table:

Exec Sp_Rename EMP to Employee
Examples on DML Operations:
Insert:
Syntax to insert the Data into all columns of a table
insert into emp(Empno,Ename,Job,mgr,Hiredate,Sal,Comm,Deptno)
values (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);

insert into emp values

    (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4);

Syntax to insert the Data into Specific columns of a table:

insert into emp(Empno,Ename,Job,mgr,Hiredate)
values (1,'JOHNSON','ADMIN',6,'12-17-1990');
Sample data from emp:
insert into emp values
    (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)
insert into emp values
    (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)
insert into emp values
    (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)
insert into emp values
    (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)
insert into emp values
    (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)
insert into emp values
    (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)
insert into emp values
    (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)
insert into emp values
    (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)
insert into emp values
    (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)
insert into emp values
    (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2)
insert into emp values
    (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)
insert into emp values
    (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)
insert into emp values
    (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)
insert into emp values
    (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)

Sample Data for Dept:

insert into dept values (1,'ACCOUNTING','ST LOUIS')

insert into dept values (2,'RESEARCH','NEW YORK')
insert into dept values (3,'SALES','ATLANTA')
insert into dept values (4, 'OPERATIONS','SEATTLE')

Update:
Syntax to update the all rows of a table
Update emp Set Comm=0
Syntax to update the specific rows of a table:
Update emp set comm=0 where sal>5000
Delete:
Syntax to delete all the from a table:
Delete from emp;
Syntax to delete selected data from a table:
Delete from emp where comm Is null;
Exercises:
1) How to rename the Table in sql server

Sql server does not have any direct statement to rename the table. However we
have the stored procedure named ‘SP_Rename’ is used to rename the table.

Sp_Rename ‘old_Table_Name’ to ‘New Table Name’

2) What is the Difference between Drop,Delete and Truncate?

Truncate:
 Truncate is a DDL command
 Truncate is executed using a table lock and whole table is locked for remove
all records.
 We cannot use WHERE clause with TRUNCATE.
 TRUNCATE removes all rows from a table.
 Minimal logging in transaction log, so it is faster performance wise.
 TRUNCATE TABLE removes the data by deallocating the data pages used to
store the table data and records only the page deallocations in the transaction
log.
 Identify column is reset to its seed value if table contains any identity column.
 To use Truncate on a table you need at least ALTER permission on the table.
 Truncate uses less transaction space than the Delete statement.
 Truncate is faster than DELETE.
Delete:
 Delete is a DML command.
  Delete is executed using a row lock, each row in the table is locked for
deletion.
 We can use where clause with Delete to filter & delete specific records.
 The Delete command is used to remove rows from a table based on WHERE
condition.
 It maintains the log, so it slower than TRUNCATE.
 The Delete statement removes rows one at a time and records an entry in the
transaction log for each deleted row.
 Identity of column keep Delete retains the identity.
 To use Delete you need Delete permission on the table.
 Delete uses the more transaction space than Truncate statement.
Drop:
The DROP command removes a table from the database.
All the tables' rows, indexes and privileges will also be removed.
No DML triggers will be fired.
The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML
command.
 DELETE operations can be rolled back (undone), while DROP and
TRUNCATE operations cannot be rolled back
3) How to create a new table by copying the data from another table?
4) How to copy the structure of the Table from another table?
Note: Important link for creating table structures.
 HTTPS://WWW.IBM.COM/SUPPORT/KNOWLEDGECENTER/EN/SSC6CA_7.2.0/COM.IB
M.NEX.OPTIMZ.COMMON.DOC/SAMPLEDBAPPNDX/OPZCOMMON-C-
SAMPLE_DATABASE_TABLES_AND_STRUCTURE.HTML
 http://www.databaseanswers.org/data_models/customers_and_orders/index.htm

Multiple Records inserting in Oracle:

Insert all statement is used to insert multiple rows with a single insert Statement.
Inserting the multiple records into a same table syntax:

INSERT ALL  
 INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
 INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
 INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
SELECT * FROM dual; 
Inserting the Data into Multiple Tables:

INSERT ALL  
  INTO suppliers (supplier_id, supplier_name) VALUES (30, 'Google')  
  INTO suppliers (supplier_id, supplier_name) VALUES (31, 'Microsoft')  
  INTO customers (age, name, address) VALUES (29, 'Luca Warsi', 'New York')  
SELECT * FROM dual;  
 
Multiple Records inserting in SQL Server:

Insert into Table_Name(Column1,Column2,Columnsn)

Values(Column1,Column2,Columnn),
Values(Column1,Column2,Columnn),
Values(Column1,Column2,Columnn);
Ex:

Insert into emp (Emono,Ename,Sal) Values(1,’A’,2000),

Values(2,’B’,3000)

Exercises on DDL:
1) Write a Query to add the column to an existing table.
2) Write a query to drop column from an existing table.
3) Write a query to rename the column from sal to salary.
4) Change the size of the datatype of the deptno column to 2.
5) Change the Datatype of the Dname to varchar
6) Increase the size of the empno to 4 from emp table.
7) Reduce the location size to 15 from Dept table.
8) Reduce the size of the job to 10 from Emp table
9) Change the datatype of the MGR to int.
10) Salary Should accept 5 integers and 2 decimals
11) Comm should accept 5 integers and 2 decimals.

SQL Operators:
An operator is a reserved word, or a character used primarily in an SQL statement's
WHERE clause to perform operation(s), such as comparisons and arithmetic operations.
These Operators are used to specify conditions in an SQL statement. The following are the
types of operators we have in sql Server.
1) Arithmetic Operator
2) Comparison Operators
3) Relational Operators
4) Logical Operators
Arithmetic Operators:
Addition(+): Adds values on the either side of the operator
Subtraction(-): Subtract Right hand Operand from left hand Operand
Multiplication(*): Multiples the Values on either side of the Operator
Division(/):Division the Left hand Operand by Right Hand operand.
%(Modulus): Divides Left hand operand by right hand operand on returns remainder.
Exercises on Arithmetic Operators:
1) Write a Query to get the Total Salary of the Employees.
2) Write a query to get the total Salary of the Employees without Commission
3) Write a query to get the Annual Salary of an employee.
4) Write a query to get the Experience of the Employee.
5) Write a query to get the experience of the employee in years.
6) List the employees whose comm is more than their salary.
7) Write a query to print the Even numbers.
8) Write a query to print the Odd Numbers.
9) I have a table with n number of records and want to load Half of the records into one
table and remaining half into another table. How could we achieve it?

Logical operators:

Logical operators combine the result of two component conditions to produce a single
result. The logical operators are

A) And b) or c) not

And operator:

1) It returns true if both the conditions are true.

2) It returns false if either condition false, else unknown

Eg: select empno, ename, sal from emp where sal>=1000 and deptno=10;

Or operator:

1) It returns true if either component condition is true.

2) It returns false if both component conditions false

Eg: select empno, sal, deptno from emp where sal>=1000 or deptno=10;

Not operator:

1) It returns true if the following condition is false

2) It returns false if the following condition is true.

Eg: select ename,sal,job from emp where not job=’manager’;

Comparison Operators or Relational Operators:
Comparison operators are used in the WHERE clause to determine which records to select.
1) = (Equal to):Syntax: Expression=expression
Compares the Equality of two expressions. If the expressions are not of the same
datatype, the datatype of the one expression must be implicitly convertible to the
datatype of the other.

2) <> or != (Not Equal to):Syntax: Expression<>Expression

Compares two expressions. If the left operand is not equal to the right operand then it
results true else false. Both expressions should be of the same data type.
3) > (Greater Than):Synatx: Expression>Expression
Compares Two expressions, results true if the left operand value is greater than the
right Operand else False. Both the expressions should have the same datatype.

4) < (Less Than):Synatx: Expression<Expression

Compares Two expressions, results true if the left operand value is Less than the right
Operand else False. Both the expressions should have the same datatype.

5) >= (Greater than or Equal to): Synatx: Expression>=Expression

Compares Two expressions, results true if the left operand value is greater than or
equal to the right Operand else False. Both the expressions should have the same
datatype.

6) <= (Less than or Equal to): Synatx: Expression<=Expression

Compares Two expressions, results true if the left operand value is less than or equal
to the right Operand else False. Both the expressions should have the same datatype.

7) IN Operator: It matches the values in the list.IN condition is used to help reduce the
need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE
statement. The Operator can be used on any datatype.

8) NOT: It Negates the Condition.

9) Between: Between is used to display the rows based on the range of values. The
lower limit should be declared first.

10) Like:

a. It is used for Pattern matching.

b. Use the like condition to perform wildcard.
c. The available wild cards are % and _.
d. % Represents any sequence of zero or More Character.
e. _Represents any single character, Only at that Position.
 f. The wild card symbols can be used in any of the combination with literal
Character.
11) Is Null: It returns the Null values result Set. This is only the operator that can be used
for testing the nulls.
12) IsNotNull: It returns the Nonnull value result set.
Examples on Comparison operators:
1) List the empno, ename, deptno, Hiredate and exp of all managers.
2) List the employees who are working in dept 20.
3) List the employees who is salary is 1000.
4) List the employees who are not managers.
5) List the employees who are not working in dept no 10.
6) List the Employees who joined before 1982.
7) List the employees whose salary is less than 1000.
8) List the employees whose salary is 1000 or Less than 1000.
9) List the employees who are having salary more than 1000.
10) List the employees whose salary is 1000 or more than 1000.
11) List the employees who joined after 1982.
12) List the employees who are receiving commission
13) List the employees who are not receiving the commission
14) List the employees along with their experience and whose daily salary is more than
200.
15) List the employees whose salary is more than 10000
16) Display the details of the employees who are working in dept 10.
17) Display all the Details of the Saleman.
18) Display the details of employees who are working as salesman and Clerk.
Select * from emp where Job=’SalesMan’ or Job=’Clerk’ or Job=’Manger’ or Job=’Tech’
Select * from emp where Job in (’SalesMan’,‘Clerk’,’Manager’,’TECH’)

19) Display the details of the employees who are belongs to dept 10 and 20.
20) Display the details of employees who are not belongs to dept 10 and 20.
21) Display the Employees whose salary is more than 2000 after giving the 20%
increment.
Select * from emp where sal+(Sal*20/100)>2000
22) Display the details of the employees and their salaries and whose salary is not in
range on 1500 and 3000
23) Display the details of employees whose commission amount is greater than the salary
increased by 10%.
24) Display Empno,Ename from emp whose ename starts with M.
25) Display Empno,Ename from emp whose ename starts with M.
26) Display Empno,Ename from emp whose ename is having second letter O.
27) Display Empno,Ename from emp whose ename is not having second letter O.
28) Display the list of employees whose name start with SM.
 29) Display the list of employees whose name ends with S.
30) Display the list of employees whose name not ends with S.
31) Display the list of employees whose name having length in 4 characters.
32) Display the details of the employees who joined in the feb 1981.
33) Display the details of employees who joined in the 1981
34) Display the details of employees who joined in the Jan month of every year.
35) Display the details of employees who joined in 12th of every month.
Handling Null Values:
1) The NULL is the term used to represent a missing value. A NULL value in a table is a
value in a field that appears to be blank.
2) A field with a NULL value is a field with no value. It is very important to understand
that a NULL value is different than a zero value or a field that contains spaces
3) One value is Not equal to another null
4) Null+anything is null
The following are the Null Handling functions in SQL server:
1) IsNull
2) Coalesce
3) Nullif
IsNull:
The ISNULL() function is used to replace NULL with the specified replacement
value. This function contains only two arguments.
Syntax: IsNull(Expr1,Expr2)
If Expr1 is null then Expr2 Else Expr1 end.
Coalesce:
The Coalesce() function returns the first non-null value among its arguments. This
function doesn’t limit the number of arguments, But they must all be of the same datatype.
Coalesce () function is equivalent to the following case expression.
Synatax:Coalesce(Expr1,Expr2..Exprn)
Case when Expr1 is not null then Exp1
When Expr2 is not null Then Exp2
Else Expn
NullIf:
Returns a null value if the two specified expressions are equal. NULLIF returns the
first expression if the two expressions are not equal. If the expressions are equal, NULLIF
returns a null value of the type of the first expression.
Syantax: NullIf(Expr1,expr2)
 e.g.
SELECT NULLIF(‘MakeFlag’, ‘MakeFlag’)AS ‘Null if Equal’
Output : NULL
SELECT NULLIF(‘FinishedGoodsFlag’, ‘MakeFlag’)AS ‘Null if Equal’
Output:FinishedGoodsFlag
Difference between ISNULL () and Coalesce() Functions?
1) The coalesce () function is based on the ANSI SQL Standard Whereas ISNULL is a
T-SQL Function.
2) The ISNULL function contains only two arguments whereas Coalesce () function
contains multiple arguments.
3) ISNULL is a Function where as Coalesce is an Expression.
Difference between ISNULLand NULLIF:
4) ISNULL is used to replace value of expression, if it comes to NULL.
5) NULLIF returns NULL if both of the strings are equal else returns first string.
Difference between count(*) and count(column Name
Count(*) will give you the total record count irrespective of the null values whereas
Count(ColumnName) will give you the count by excluding Nulls.
Difference between count(*) and Count(1)
Count(*) will perform on all the column and rows of a table
Count(1) will perform on a single column of a table
Examples:
1) Write a query to get the total salary of the employee
2) Write a query to get percentage of commission in stake
3) Display the list of employees who are not having managers
4) Display the list of the employees who are having managers
5) Display the list of employees who is not receiving commission.
6) Display the list of employees who is receiving commission.
Set Operators:
Set operators are used to join the results of two (or more) SELECT statements. The
SET operators available in Oracle 11g are UNION, UNION ALL, INTERSECT and MINUS.
UNION:
UNION is used to combine the results of two or more Select statements. However it will
eliminate duplicate rows from its result set. In case of union, number of columns and datatype
must be same in both the tables.
First Table:
ID Name
 1 krishna
2 kishore
3 murali
Second Table:
ID Name
3 murali
4 kishore
5 ramu
Union SQL Query:
Select ID,Name,Cast(Mobile as Varchar(100)) from first;
Union
Select ID,Name,Mobile from second;
Result:
ID NAME
1 Krishna
2 Kishore
3 Murali
4 Kishore
5 Ramu

UNION ALL:
This operation is similar to Union. But it also shows the duplicate rows.
First Table:
ID Name
1 Krishna
2 Kishore
3 Murali

Second Table:
ID Name
3 Murali
4 Kishore
5 Ramu

Union all SQL Query:

Select * from first;
Union all
Select * from second;

Result:
 ID NAME
1 Krishna
2 Kishore
3 Murali
3 Murali
4 Kishore
5 Ramu

Intersect:
Intersect operation is used to combine two SELECT statements, but it only returns the
records which are common from both SELECT statements. In case of Intersect the number
of columns and data type must be same.

First Table:
ID Name
1 Krishna
2 Kishore
3 Murali

Second Table:
ID Name
3 Murali
4 Kishore
5 Ramu

Intersect SQL Query:

Select * from first;
Intersect
Select * from second;

Result:
ID NAME
3 Murali

Minus:
Minus operator displays the rows which are present in the first query but absent in the
second query, with no duplicates and data arranged in ascending order by default.
First Table:
 ID Name
1 Krishna
2 Kishore
3 Murali
Second Table:
ID Name
3 Murali
4 Kishore
5 Ramu

Minus SQL Query:

Select * from first;
Minus
Select * from second;
Result:
ID Name
1 krishna
2 kishore

Note:
 Same number of columns must be selected by all participating SELECT statements.
Column names used in the display are taken from the first query.
 Performance wise, UNION ALL shows better performance as compared to UNION
because resources are not wasted in filtering duplicates and sorting the result set.

Functions in SQL server:

Exercises:
1) Display the Emp details from emp Table.
2) Display the Dept details from Dept table,
3) Display the Empno, Ename, Job, Sal for all employees from employee table.
4) Display the Department Name,Loacation of all the Departments from dept table.
5) Display the Unique departments in Dept table.
6) Display the unique dept with Jobs
7) Display the Details of employees and salary increased by 15% and expressed as
Whole number and label them as a Newcolumn.in addition to this display the increase
of salary over the Previous year.
8) Display the Names of employees working as a manager and drawing salary more than
2000.
9) Display the Names of employees who are working as CLERK, Salesman or Analyst
and Drawing salary more than 2000.
10) Display the names of the employees who are working in the Company for past five
years.
11) Display the List of employees who have joined the company before 31st Jan 1981 or
30th NOV 1981.
12) Display the details of employees whose salary is between 1500 and 5000 and are
Managers.
13) Display the details of employees whose salary is more than 2000 or deptno=20.
14) Display the details of the employees belongs to either deptno 10 or 20 or Managers.
15)