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.

How to Place your reusable code into Toolbox in Visual Studio?

This is very important and small tips for all VS users. Sometimes we need a small piece of code in many places of our application or in some different application. What we do? We just copy the code from one page and paste it to the destination page.

 Visual Studio having a great feature, that you can place your code snippet inside the toolbox. For that you need to just select the code snippet and drag the selected snippet on to the general Tab.

 

You can rename the selected snippet, by just right clicking on snippet and click on Rename.

Now, you can use it anywhere in your application or in any VS Application by just drag and drop of the selected snippet.

Bundling in visual studio 2012 for web optimization

As we know now days web applications or site are providing more and more features and due to that we have include lots of JavaScript and CSS files in our web application.So once we load site then we will have all the JavaScript  js files and CSS files loaded in the browsers and If you have lots of JavaScript files then its consumes lots of time when browser request them.
Following images show the same situation over there.



Here you can see total 25 files loaded into the system and it's almost more than 1MB of total size. As we need to have our web application of site very responsive and need to have high performance application/site, this will be a performance bottleneck to our site. In situation like this, the bundling feature of Visual Studio 2012 and ASP.NET 4.5 comes very handy. With the help of this feature we do optimization there and we can increase performance of our application.


To enable this feature in Visual Studio 2012 we just made debug=”false” in web.config of our application like following.



Now once you enable this feature and run this application in the browser to see your traffic it will have less items like following.