realcode4you
- Jun 4, 2021
- 6 min read

SQL Server Practice Exercise | Select, Order By, Where Clause and Filtering In SQL Server

In this blog we will cover sql server basic queries and with help of some examples. In this blog we will learn Select, Order By, Where Clause and Filtering

The SQL SELECT Statement

The SELECT statement is used to SELECT data from a table. The tabular result is stored in a result table (called the result-set).

Syntax:

Select 
   ColumnName 
From 
   TableName

SELECT Single Column

To SELECT the content of column named "ProductNumber", from the table called "Product", write a SELECT statement like this:

Select 
   ProductNumber 
From 
    Product

In more common terms, this request could be similar to. “Can you help me get a list of all product numbers from our Product table?” or even “Can you help me get a list of all product numbers for our products?” Because you have the context that all product information is located in the Products table, the person requesting the information may not know the details of the database.

Results

ProductNumber

-------------------------

AR-5381

BA-8327

BB-7421

BB-8107

BB-9108

(Not all records displayed

SELECT Multiple Columns

To SELECT multiple columns from the "Product" table, type in column names. Remember to add comma after each column name:

Select 
    ProductNumber, Name 
From 
    Product

Results In more common terms, this request could be similar to. “Can you help me get a list of all product names, and their numbers for our products?”

ProductNumber Name

------------------------- --------------------------------------------------

AR-5381 Adjustable Race

BA-8327 Bearing Ball

BE-2349 BB Ball Bearing

BE-2908 Headset Ball Bearings

BL-2036 Blade

CA-5965 LL Crankarm

(Not all records displayed)

SELECT All Columns

To SELECT all columns from the "Product" table, use a * symbol instead of column names, like this:

Select 
  * 
From 
  Product

In more common terms, this request could be similar to. “Can you help me get a list of all product information?”

Results

ProductID Name ProductNumber MakeFlag FinishedGoodsFlag….

----------- ------------------------------------------- ---------------

1 Adjustable Race AR-5381 … ...

2 Bearing Ball BA-8327 … ...

3 BB Ball Bearing BE-2349 … ...

(Not all records and columns displayed)

The SELECT DISTINCT Statement

What if we only want to SELECT distinct values? The DISTINCT keyword is used to return only distinct (unique) VALUES. The SELECT statement returns information from a table.

With SQL, all we need to do is to add a DISTINCT keyword to the SELECT statement:

Sample Basic Syntax

Select Distinct 
ColumnName 
FROM 
TableName

Using the DISTINCT keyword

To SELECT ALL VALUES from the column named "Title" in the Employee table we use a SELECT statement like this.

Notice in this result set you get duplicates titles.

Select 
Title 
FROM 
Employee

Results

Title

--------------------------------------------------

Production Technician - WC10

Production Supervisor - WC50

Production Technician - WC10

Production Supervisor - WC60

Production Technician - WC10

Production Supervisor - WC60

(Not all records displayed)

To SELECT only distinct VALUES from the column named "Title" we use a SELECT statement like this:

Select 
   Distinct Title 
FROM 
   Employee

In more common terms, this request could be similar to: “Can you help me get a list of employee titles in our system?” Note the assumption is that the person requesting the data, doesn’t want duplicate titles.

Results

Title

--------------------------------------------------

Production Supervisor - WC10

Production Supervisor - WC20

Production Supervisor - WC30

Production Supervisor - WC40

Production Supervisor - WC45

Production Supervisor - WC50

Production Supervisor - WC60

Production Technician - WC10

Production Technician - WC20

(Not all records displayed)

Limiting Results Statement

The following sample shows how to limit the result set to one or more rows based on percent.

Sample Basic Syntax

Select 
   Top (number) PERCENT Columnname 
FROM 
   TableName

Write Exercise Code:

This SELECT statement returns all records or a percentage of records in table. Select statement can also specify fields to be returned. This statement only returns the top 30 percent of the rows in the table

Select 
  TOP (30) PERCENT Title 
FROM 
  Employee

In more common terms, this request could be similar to: “I’m trying to get a small sample of employee titles that we have at the company. Can you help me get a sample (say 30%) list of employee titles in our system? I’m fine if there are duplicates because that way I know how many there are with the same title”

Results

Title

--------------------------------------------------

Production Technician - WC60

Marketing Assistant

Engineering Manager

Senior Tool Designer

Tool Designer

Marketing Manager

(Not all records displayed)

Write Exercise Code:

SELECT statement that limits records by specified number of records. In this case, this statement will return the top 5 records:

Select 
  Top 5 Title 
FROM 
  Employee

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.

Sample Basic Syntax

Here is the example statement:

SELECT 
  column_name 
FROM 
  tablename 
Order By 
  column_name

Write Exercise Code:

To display the vendors names in default order. This will display records as entered into the table.

SELECT 
  Name 
FROM 
  Vendor

Result:

Name

--------------------------------------------------

International

Electronic Bike Repair & Supplies

Premier Sport, Inc.

Comfort Road Bicycles

Metro Sport Equipment

Green Lake Bike Company

Mountain Works

(Not all records displayed)

Write Exercise Code:

To display the products names in alphabetical order. This will display records ORDERED BY Name

SELECT 
    Name 
FROM 
    Vendor 
Order By 
    Name

Result:

Name

--------------------------------------------------

A. Datum Corporation

Advanced Bicycles

Allenson Cycles

American Bicycles and Wheels

American Bikes

Anderson's Custom Bikes

Aurora Bike Center

(Not all records displayed)

Sort in ascending order

To display the products names in alphabetical order specifying ascending sort direction. This statement changes the sort order to ascending order.

SELECT 
   Name 
FROM 
   Vendor 
Order By 
   Name asc

In more common terms, this request could be similar to: “Can you help me get an alphabetized list of all of our vendors?”

Note: Alphabetized = Ordered, and likely ordered in Ascending

Result:

Name

--------------------------------------------------

A. Datum Corporation

Advanced Bicycles

Allenson Cycles

American Bicycles and Wheels

American Bikes

Anderson's Custom Bikes

(Not all records displayed)

Where Clause and Filtering by logical expressions

Filtering Data - The WHERE Clause

The “WHERE” clause is used to specify a SELECT criterion. To conditionally SELECT data from a table, a WHERE clause can be added to the SELECT statement. Think of a WHERE clause as a filter. It can limit the number of rows returned based on your filter.

Sample Basic Syntax

Here is the example statement:

SELECT 
   column_name 
FROM
   tablename 
WHERE 
   column_name  <operator> VALUE

With the WHERE clause, the following operators can be used:

Write Exercise Code:

Checking for Equal To VALUE To SELECT only the persons living in the city ‘San Francisco’, we add a WHERE clause to the SELECT statement using = operator. When using a string value you surround it single quotes.

Syntax

SELECT 
   City 
FROM 
   Address 
WHERE 
   City = 'San Francisco

Result

City

------------------------------

San Francisco

(Not all records displayed)

Checking for Not Equal To VALUE

To SELECT only the persons not living in the city ‘San Francisco’, we add a WHERE clause to the SELECT statement with the <> operator:

Syntax

SELECT 
   City 
FROM 
   Address 
WHERE 
   City <> 'San Francisco'

Result

In more common terms, this request could be similar to: “Can you help me a list of cities customers live in that is outside of San Francisco?”

Note: Alphabetized = Ordered, and likely ordered in Ascending

City

------------------------------

Ottawa

Burnaby

Dunkerque

Verrieres Le Buisson

Saint-Denis

(Not all records displayed)

Checking for Less Then VALUE

To SELECT two columns from products table but only return rows with list price VALUE less then 147.1400, we add a WHERE clause to the SELECT statement with the < operator:

Syntax

SELECT 
   Name, ListPrice 
FROM 
    Product 
WHERE ListPrice < 147

In more common terms, this request could be similar to: “Can you also help me get a list of products that have a price point lower than $147.00. I’m just interested in the product names, and the price”

Result

Name ListPrice

-------------------------------------------------- ---------------------

Adjustable Race 0.00

Bearing Ball 0.00

BB Ball Bearing 0.00

Headset Ball Bearings 0.00

(Not all records displayed)

Checking for Less or Equal to

To SELECT two columns from products table but only return rows with list price VALUE less or equal to 147.1400, we add a WHERE clause to the SELECT statement with the <= operator. If you noticed it also returned any values with decimals less or equal to 147.

Syntax

SELECT 
    Name, ListPrice 
FROM 
    Product 
WHERE ListPrice <= 147

Result

Name ListPrice

-------------------------------------------------- ---------------------

HL Touring Handlebars 91.57

Front Brakes 106.50

Chain 20.24

LL Bottom Bracket 53.99

ML Bottom Bracket 101.24

HL Bottom Bracket 121.49

(Not all records displayed)

Checking for Range VALUE

To SELECT two columns from products table but only return rows with list price VALUE between a range of VALUES, we add a WHERE clause to the SELECT statement with the “between” operator:

Syntax

SELECT 
    Name, ListPrice 
FROM 
    Product 
WHERE ListPrice between 147 and 175

Result

In more common terms, this request could be similar to: “Can you help me get list of products that have a list price between $147 and $175?

Again, the product name, and price is all that’s needed”

Name ListPrice

-------------------------------------------------- ---------------------

ML Mountain Seat Assembly 147.14

ML Road Seat Assembly 147.14

ML Touring Seat Assembly 147.14

LL Fork 148.22

All-Purpose Bike Stand 159.00

Contact Us at realcode4you@gmail.com and get any database related project assignment help with an affordable prices.

RealCode4You

SQL Server Practice Exercise | Select, Order By, Where Clause and Filtering In SQL Server

The SQL SELECT Statement

Order By

Where Clause and Filtering by logical expressions

Recent Posts

Comments

SQL Server Practice Exercise | Select, Order By, Where Clause and Filtering In SQL Server

The​ ​SQL​ ​SELECT​ ​Statement

Order​ ​By

Where​ ​Clause​ ​and​ ​Filtering​ ​by​ ​logical​ ​expressions

Recent Posts

Comments

The SQL SELECT Statement

Order By

Where Clause and Filtering by logical expressions