sql server basic interview questions part 6

SQL ORDER BY

 The ORDER BY keyword is used to sort the result.

 Sort the Rows

 The ORDER BY clause is used to sort the rows.

 Orders:

 Company OrderNumber

 Sega 3412

ABC Shop 5678

W3Schools 2312

W3Schools 6798

 Example

 To display the companies in alphabetical order:

 SELECT Company, OrderNumber FROM Orders ORDER BY Company

Result:

 Company OrderNumber

 ABC Shop5678

 Sega 3412

 W3Schools 6798

 W3Schools 2312

 Example

 To display the companies in alphabetical order AND the ordernumbers in numerical order:

 SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

 Result:

 Company OrderNumber

 ABC Shop 5678

Sega 3412

W3Schools 2312

W3Schools 6798

 Example

 To display the companies in reverse alphabetical order:

 SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

 Result:

 Company OrderNumber

W3Schools 6798

W3Schools 2312

Sega 3412

ABC Shop 5678

Example

To display the companies in reverse alphabetical order AND the ordernumbers in numerical order:

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

Result:

 Company OrderNumber

W3Schools 2312

W3Schools 6798

Sega 3412

ABC Shop 5678

 SQL AND & OR

AND & OR

AND and OR join two or more conditions in a WHERE clause.

 The AND operator displays a row if ALL conditions listed are true. The OR operator displays a row if ANY of the conditions listed are true.

 Original Table (used in the examples)

 LastName FirstName Address City

 Hansen Ola Timoteivn 10 Sandnes

 Svendson Tove Borgvn 23 Sandnes

 Svendson Stephen Kaivn 18 Sandnes

 Example

Use AND to display each person with the first name equal to "Tove", and the last name equal to "Svendson":

SELECT * FROM Persons WHERE FirstName='Tove' AND LastName='Svendson'

 Result:

LastName FirstName Address City

Svendson Tove Borgvn 23 Sandnes

 Example

 Use OR to display each person with the first name equal to "Tove", or the last name equal to "Svendson":

SELECT * FROM Persons

WHERE firstname='Tove'

OR lastname='Svendson'

Result:

LastName FirstName Address City

 svendson Tove Borgvn 23 Sandnes

Svendson Stephen Kaivn 18 Sandnes

 Example

 You can also combine AND and OR (use parentheses to form complex expressions):

 SELECT * FROM Persons WHERE (FirstName='Tove' OR FirstName='Stephen') AND LastName='Svendson'

Result:

LastName FirstName Address City

 Svendson Tove Borgvn 23 Sandnes

 svendson Stephen Kaivn 18 Sandnes