Isnull function in SQL SERVER

Isnull function

Any arthimetic operation with null results in null. Hence whenever you have to perform arithmetic operations on a column containing null use the function “ISNull”

DEPT TABLE

EMP TABLE

Display empno,ename, sal, comm along with total and annual salary

select empno,ename,sal,comm ,sal+isnull(comm,0),(sal+isnull(comm,0))*12 from emp

OR BY USING COL ALIAS

select empno,ename,sal,comm ,sal+isnull(comm,0) AS "TOTAL SALARY",(sal+isnull(comm,0))*12 AS "ANNUAL SALARY"from emp

Distict KeyWord

Distict KeyWord

It is used to eliminate duplicate values in the output of select statement.

the role for using distinct keyword is the keyword distinct can be used only once that also immediately next to the keyword select

Tables Dept table

Emp table

Display the list of department no in which employees are working

Select distinct deptno from emp

Display the list of jobs available in every Dept 
select distinct deptno,job from emp order by deptno

When you select multiple columns while using the keyword distinct then duplicates will not be eliminated in individual columns and duplicates will be eliminated in the combination of columns you select.

Eg: select distinct job from emp

select distinct deptno,job from emp

ORDER OF DECLARATION IN SQL SERVER

Tables

ORDER OF DECLARATION

(USER FOLLOWS THIS ONE)

MEANS WHICH CLAUSE

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

ORDER OF EXECUTION (SYSTEM FOLLOWS)

FROM

WHERE

GROUP BY

HAVING

SELECT

Display employee records earning more than 2000

select * from emp where sal >2000

Display employee records earning more than 2000 and less than 5000

select * from emp where sal>2000 and sal<5000

Order by Clause

It is used to sort rows retrieved by the “select statement” in Ascending Order or Descending Order.

Syntax

Select */<collist>  from <tablename> [where condition] order by <colname> [asc,desc],

<colname> [asc,desc]

Default is ascending order

Display the details of employee in the order of highest salary to the lowest salary

select * from emp order by sal desc

     

Display the details of employee working in dept 30 in alphabetical order

select * from emp where deptno=30 order by ename

Display the details of employees in order of highest salary to lowest salary and arrange the employee who are drawing same salary.

select * from emp order by sal desc , ename asc

with in order by clause you can specify multiple columns when you specify the columns in order by then first it will arrange the rows in the specified order of first column and then only the rows that have same value in that first column will be arranged in the specified order of the second column.

Sorting the emp table in ename wise

Select * from emp order by ename

Or 

select * from emp order by 2

Arrange employee records department wise with in the department salary wise

Select deptno,ename,sal,job from emp order by deptno, sal desc

Display the employee records for 10 or 20 deptno and sort the result salary-wise in descending order

Select * from emp where deptno in(10,20) order by sal desc

DATA QUERY LANGUAGE

The commands that are used to retrieve the data from db are collectively called as Data Query Language.

As select statements is used to retrieve data from db, select statement is called as a query.

Select statement that has the following syntax

Syntax

Select */ collist  from  <TableName>

Dept Table.(Deptno-->pk)

Emp Table(Empno-PK    , Deptno--fk)

 Display the details of all the Depts available in the company.

 select * from Dept

 Display Ename, Empno,job and salary of all the Employees.

select empno,ename,job,sal from emp

Where Clause

It is used to specify the conditions based on which the rows retrieved by the select statement will be filtered.

When you write where clause in the select statement then, it will return only the rows that satisfy the given condition.

Syntax:

Select * | <collist> from <table name> where <conditions>

Find the employees working in dept 20

select *from emp where deptno=20

Find the employees whose job is Manager

select *from emp where job='manager'

To get the manager and clerks list

Select *from emp where job in('manager','clerk')

select *from emp where job ='manager' or job='clerk'

Find the employees whose salary is in the range of 2000 and 4000

select *from emp where sal  between 2000 and 4000

select *from emp where sal>2000 and sal <=4000

Find the employees who are working as Manager in dept no 30

select *from emp where job='manager' and deptno=30

Find the employees whose salary is either 800 or 950 or1300

select *from emp where sal=800 or sal=950 or sal=1300

Find the employees who are not working as manager

select *from emp where job!='manager' 

select *from emp where job <> 'manager'

select *from emp where not job='manager'

Find the software for which development cost was not recover.

select * from software where scost*SOLD<DCOST

UNABLE TO SEND TO ASP.NET DEVELOPMENT SERVER ERROR

Right click on the solution File

and select properties

and uncheck the readonly 

2 step

Open the solution file with a text editor like Notepad

 there is a property of the solution called VWDPort, the value of this property is the port used to publish the website, delete the port leaving the property VWDPort = ""; when you start debugging again, .Net will choose a new FREE port. Problem Solved!

in that u will find VWDPort make it as VWDPort=""

then ur problem is solved.