Database Homework Help | SQL Server Practice Set: 3

Realcode4you is the top rated website where you can get all database related help with an affordable price. In this blog we will provide another practice in which you can learn Filtering Data, Using Wildcard Filtering, Creating Calculated Fields, Text Manipulation Functions, Date/Time Manipulation Functions.

Filtering Data

Combining WHERE Clauses To gain greater control over filtering data you can specify multiple operators in the WHERE clause. In the SELECT statement you can do this but using the “And” operator or the “Or” operator

Basic Syntax

SELECT 
     column_name 
FROM 
     tablename 
WHERE 
    column_name = VALUE logical <operator> column_name = VALUE

Using the “And” Operator

To filter using more than one column you can use the “And” operator to add conditions to the WHERE clause that must be met. In this statement, we are using the AND operator to specify the two conditions that must be met: ProductID and StandardPrice

Write Exercise Code:

SELECT 
     ProductID, VendorID, StandardPrice 
FROM 
     ProductVendor 
WHERE 
     ProductID = 317 AND StandardPrice >=24

In more common terms, this request could be similar to. “Can you help me get a list Product IDs and Vendor IDs for ProductID 37 along with a Price that’s $24 or higher?”

Result

ProductID VendorID StandardPrice

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

317 50 28.17

317 84 25.77

(2 row(s) affected)

Using the “OR” Operator

To filter using more than one column you can use the “or” operator to add conditions to the WHERE clause that match either condition. In this statement, we are using the 2 OR operator to specify the two conditions that can be met: ProductID and StandardPrice

Write Exercise Code:

SELECT 
     ProductID, VendorID, StandardPrice 
FROM 
    ProductVendor 
WHERE 
   ProductID = 317 OR StandardPrice >=24

In more common terms, this request could be similar to. “Can you help me get a list Product IDs and the vendor IDs associated with each product.? I’m looking for a report that has all products with the product ID of 37, and also any products where the prices is $24 or higher”

Note: Notice in the request OR is not explicit. Take into consideration that the requestor doesn’t know your schema or SQL, they are just describing the request in a way they know how to. Because the requestor used “and also”, it translates into an OR in SQL.

Result

ProductID VendorID StandardPrice

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

938 53 29.99

939 35 45.99

939 53 45.99

(349 row(s) affected)

Using both “AND” and “OR” Operator To filter using both operators add conditions to the WHERE clause that match the “AND” operator and either of the two conditions in “OR” operator:

Write Exercise Code:

SELECT 
       ProductID, VendorID, StandardPrice 
FROM 
      ProductVendor 
WHERE 
      ProductID = 317 OR ProductID = 318 AND StandardPrice >=25.78

Result

ProductID VendorID StandardPrice

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

317 50 28.17

317 84 25.77

318 50 34.38

318 84 31.98

(4 row(s) affected)

The above SELECT statement returns one row that have Standard Price less then 25.78. The problem is the order of evaluation. Most languages process “AND” condition first and then the “OR” condition.

To correct this syntax we have to use a parenthesis to group related operators. Parentheses have a higher order of evaluation and thus are executed first. In this statement the code inside parentheses will execute first and then the outer code

Write Exercise Code:

SELECT 
      ProductID, VendorID, StandardPrice 
FROM 
     ProductVendor 
WHERE 
    (ProductID = 317 OR ProductID = 318) AND StandardPrice >=25.78; 

In more common terms, this request could be similar to. “Can you help me get a list Product IDs and the vendor IDs associated with each product.? I’m looking for a report for for just product ID is either 37 or 318. Oh, can you also only limit the report to products that have a $25.78 or higher price?

Result

ProductID VendorID StandardPrice

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

317 50 28.17

318 50 34.38

318 84 31.98

(3 row(s) affected)

Using the IN Operator

Parentheses are also used to group related VALUES specified in the WHERE clause. The IN operator uses a comma-delimited list of VALUES. Any VALUES within the parentheses can be matched.

Basic Syntax

SELECT 
       columnname 
FROM 
       tablename 
WHERE 
       columnname  (value,value) Order BY Columnname

The following returns any rows that match the VALUES within the parentheses. The IN operator does exactly what the “OR” operator does. It just reduces that amount of code.

Write Exercise Code:

SELECT 
       ProductID, VendorID, StandardPrice 
FROM 
      ProductVendor 
WHERE 
      ProductID IN (317,318)

Result

ProductID VendorID StandardPrice

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

317 50 28.17

317 84 25.77

318 50 34.38

318 84 31.98

(4 row(s) affected)

Using the NOT Operator

Parentheses are also used to group related VALUES specified in the WHERE clause. The NOT operator also uses a comma-delimited list of VALUES. Any VALUES within the parentheses can be matched. However, only those rows that are NOT specified within the parentheses are returned.

Basic Syntax

SELECT 
     column_name 
FROM 
     tablename 
WHERE 
      column_name <operator> <operator> (VALUE, VALUE) 
ORDER BY 
     column_name

This SELECT statement returns rows that are NOT IN the parentheses

Write Exercise Code:

SELECT 
     ProductID, VendorID, StandardPrice 
FROM 
     ProductID NOT IN (317,318)
WHERE 
    ProductID NOT IN (317,318)

Result

ProductID VendorID StandardPrice

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

939 53 45.99

940 17 59.99

941 80 59.99

948 94 78.89

952 72 14.99

(Not all rows displayed) (456 row(s) affected)

Using Wildcard Filtering

The % Sign Wildcard The LIKE operator is used for searching patterns that can be compared to your data as opposed to specific know VALUES. Using know VALUES does not always return all data you are searching to retrieve. The following is the SELECT statement syntax used for wildcard operator:

Basic Syntax

SELECT 
   column_name 
FROM 
   tablename 
WHERE 
   column_name operator ‘VALUE%’ 
Order by 
   column_name

This SELECT statement search’s a pattern of values and returns all records that start with “ML Touring” VALUE. The % specifies to return any characters after the VALUE ‘ML Touring’:

Write Exercise Code:

SELECT 
      ProductID, Name, ListPrice 
FROM 
      Product 
WHERE 
      Name LIKE 'ML Touring%'

In more common terms, this request could be similar to. “Can you get me a list of products and prices for all of our ML Touring products?”

Note:

The requestor didn’t specify to use any specific filter. They assume you know the data, and would be able to understand all the product names are part of a category of products where the Name starts with “ML Touring”. If you’re not sure, it’s always great to confirm.

Result

ProductID Name ListPrice

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

521 ML Touring Seat Assembly 147.14

915 ML Touring Seat/Saddle 39.14

(2 row(s) affected)

The wildcard can be used ANYWHERE within the search pattern and multiple wildcards can be used.

This SELECT statement places the wildcard on the left side of the search pattern. The % specifies to return any characters before the VALUE ‘Crankarm’

Write Exercise Code:

SELECT 
        ProductID, Name, ListPrice 
FROM 
       Product 
WHERE 
       Name LIKE '%Crankarm'

In more common terms, this request could be similar to. “Can you get me a list all the Crankarm products?”

Result

ProductID Name ListPrice

319 HL Crankarm 0.00

317 LL Crankarm 0.00

318 ML Crankarm 0.00

(3 row(s) affected)

This SELECT statement uses two wildcards, one wildcard at the beginning and one wildcard at the end of the search pattern. This SELECT statement returns all records that match the text “an” anywhere within it, regardless of any characters before or after it.

Write Exercise Code:

SELECT 
      ProductID, Name, ListPrice 
FROM 
      Product 
WHERE 
      Name LIKE '%Nut%'

In more common terms, this request could be similar to. “Can you get me a list all the Nut products?”

Result

ProductID Name ListPrice

321 Chainring Nut 0.00

359 Thin-Jam Hex Nut 9 0.00

360 Thin-Jam Hex Nut 10 0.00

361 Thin-Jam Hex Nut 1 0.00

362 Thin-Jam Hex Nut 2 0.00

(Not all rows displayed)

The wildcard can be used any WHERE within the search pattern. This SELECT statement uses one wildcard in between the letters “Men” and “, L.” This SELECT statement returns all records that begin with an “Men” and ends with an “, L” This type of statement is rarely used, although valid.

Write Exercise Code:

SELECT 
     ProductID, Name, ListPrice 
FROM 
     Product 
WHERE 
     Name LIKE 'Men%,L'

In more common terms, this request could be similar to. “Can you get me a list all Men’s products in size L?” Note: The requestor made a pretty straight forward request right? The complexity comes into play when you start looking for the right way to structure the wildcard using the base name. All Men’s products start with “Men”, all large sizes are “L”. The trick is you don’t want something like “Men’s Sports, Shorts XL”. Notice the separation with a “, L”.

Result

ProductID Name ListPrice

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

850 Men's Sports Shorts, L 59.99

857 Men's Bib-Shorts, L 89.99

(2 row(s) affected)

The _ Sign Wildcard The _ wildcard matches only one character WHERE as the % matches multiple characters in search pattern. This SELECT statement returns rows that match the search pattern plus one character.

Write Exercise Code:

SELECT 
      ProductID, Name, ListPrice 
FROM 
      Product 
WHERE 
      Name LIKE '_L Touring%'

Result

ProductID Name ListPrice

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

520 LL Touring Seat Assembly 133.34

521 ML Touring Seat Assembly 147.14

522 HL Touring Seat Assembly 196.92

885 HL Touring Frame - Yellow,60 1003.91

886 LL Touring Frame - Yellow, 62 333.42

(Not all rows displayed)

The Brackets [] Sign Wildcard The [] sign in wildcard statements is used to specify a set of characters which must match any one in the specified position. This SELECT statement returns rows whose names start with the letters “H” or “L”

Write Exercise Code:

SELECT 
    ProductID, Name, ListPrice 
FROM 
     ProductID, Name, ListPrice 
WHERE 
     Name LIKE '[HL]%' 

Result

ProductID Name ListPrice

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

4 Headset Ball Bearings 0.00

317 LL Crankarm 0.00

319 HL Crankarm 0.00

356 LL Grip Tape 0.00

358 HL Grip Tape 0.00

375 Hex Nut 5 0.00

376 Hex Nut 6 0.00

377 Hex Nut 16 0.00

(Not all rows displayed)

Creating Calculated Fields

Concatenating Fields To combine two columns in a SELECT statement we need to concatenate them. Concatenating means to join VALUES together by appending them to each other to form one VALUE. To combine vend name and vend account number and add parentheses around account number you use the + operator in the SELECT statement. The following is the SELECT statement syntax used for the + operator:

Basic Syntax

SELECT 
   column_name+‘(‘+ column_name+‘)’ 
FROM 
    tablename

This SELECT statement combines the vend name with the vend account number and places parentheses around account number. The + is used to combine the column:

Write Exercise Code:

SELECT 
     Name+'('+ AccountNumber+')' 
FROM 
     Vendor

Result

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

International(INTERNAT0001)

Electronic Bike Repair & Supplies(ELECTRON0002)

Premier Sport, Inc.(PREMIER0001)

Comfort Road Bicycles(COMFORT0001)

Metro Sport Equipment(METROSP0001)

Green Lake Bike Company(GREENLA0001)

Mountain Works(MOUNTAIN0001)

(Not all rows displayed)

Using Aliases To combine two columns in a SELECT statement and give it a new aliases Name we use the “AS” keyword. To combine two columns, add a parentheses around second column and give it an aliases name you use the + operator in the SELECT statement followed by the AS keyword. The following is the SELECT statement syntax used for the + operator with the AS keyword:

Basic Syntax

SELECT 
    column_name+‘(‘ + column_name+‘)’ AS AliasName 
FROM 
    tablename

Combine two columns in a SELECT statement and give the column a new aliases Name and place parenthesis between the two columns use the following SELECT statement: Write

Exercise Code:

SELECT 
     Name+'('+AccountNumber+')' AS VendorNameNumber 
FROM 
     Vendor 

Result

VendorNameNumber

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

A. Datum Corporation(ADATUM0001)

Advanced Bicycles(ADVANCED0001)

Allenson Cycles(ALLENSON0001)

American Bicycles and Wheels(AMERICAN0001)

American Bikes(AMERICAN0002)

(Not all rows displayed)

Sql plus (+) operator The SQL plus (+) operator is used to add two or more expressions or numbers. The following calculates Total Due by adding SubTotal plus Freight cost:

Write Exercise Code:

SELECT 
   (SubTotal+Freight) AS TotalDUE 
FROM 
   SalesOrderHeader

Sql minus (-) operator

The SQL minus (-) operator is used to subtract one expression or number from another expression or number. The filters result set based on condition which subtracts SubTotal from TaxAmt from Freight that equals SubTotal in WHERE clause.

Write Exercise Code:

SELECT 
  * 
FROM 
    SalesOrderHeader 
WHERE 
    (TotalDue-TaxAmt-Freight) = SubTotal

Result

Sales OrderID RevisionNumber OrderDateTaxAmt ...

43659 1 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 ... 43660 1 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 ... 43661 1 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 ... 43662 1 2001-07-01 00:00:00.000 2001-07-13 00:00:00.000 ... (Not all rows/columns displayed)

Sql multiply (*) operator

The SQL multiply ( * ) operator is used to multiply two or more expressions or numbers. The following calculates the Total Cost by multiplying UnitPrice with OrderQty:

Write Exercise Code:

SELECT 
      (UnitPrice*OrderQty) AS TotalCost 
FROM 
      PurchaseOrderDetail

Result

TotalCost

201.04

135.36

136.7415

8847.30

171.0765

20397.30

14628.075

(Not all rows displayed)

Sql divide (/) operator

The SQL divide ( / ) operator is used to divide one expressions or numbers by another The following calculates 5 percent commission on UnitPrice:

Write Exercise Code:

SELECT 
    UnitPrice, (UnitPrice*5/100) As Commission 
FROM 
     PurchaseOrderDetail 

Result

UnitPrice Commission

50.26 2.513

45.12 2.256

45.5805 2.279

(Not all rows displayed)

Text Manipulation Functions

Upper Case Function Functions are operations that are used to manipulate or change data. The Upper case function is an example of a function that returns data in Upper case. The following is the SELECT statement syntax used for specific functions:

Basic Syntax

SELECT 
     column_name, Function(column_name) 
FROM 
     tablename

The following is the SELECT statement syntax used to change text to Upper case:

Write Exercise Code:

SELECT 
      Name, UPPER(Name) As UpperCaseName 
FROM 
     Product

Result

Name UpperCaseName

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

Adjustable Race ADJUSTABLE RACE

All-Purpose Bike Stand ALL-PURPOSE BIKE STAND

AWC Logo Cap AWC LOGO CAP

BB Ball Bearing BB BALL BEARING

Bearing Ball BEARING BALL

Bike Wash - Dissolver BIKE WASH – DISSOLVER

(Not all rows displayed)

Lower Case Function

Functions are operations that are used to manipulate or change data. The Lower case function is an example of a function that returns data in lower case. The following is the SELECT statement syntax used for specific functions: The following is the SELECT statement syntax used to change text to Upper case:

Write Exercise Code:

SELECT 
     Name, LOWER(Name) As LowerCaseName 
FROM 
     Product 

Result

Name LowerCaseName

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

Adjustable Race adjustable race

All-Purpose Bike Stand all-purpose bike stand

AWC Logo Cap awc logo cap

BB Ball Bearing bb ball bearing

(Not all rows displayed)

RTrim; LTrim Function

Functions are operations that are used to manipulate or change data. This function removes space from either the right or left of a string. Note: SQL Server does NOT use the TRIM() function The following is the SELECT statement syntax used for specific functions:

Basic Syntax

SELECT 
     column_name, Function(column_name) 
FROM 
    Tablename

The following is the SELECT statement syntax used to remove space from the right of a string using the RTRim function:

Write Exercise Code:

SELECT 
      RTRIM(FirstName)+' '+ RTRIM(LastName) AS Name 
FROM 
     Contact

Result

Name

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

Gustavo Achong

Catherine Abel

Kim Abercrombie

Humberto Acevedo

Pilar Ackerman

Frances Adams

Margaret Smith

Carla Adams

Jay Adams

(Not all rows displayed)

The following is the SELECT statement syntax used to remove space from the left of a string using the LTRim function:

Write Exercise Code:

SELECT 
    LTRIM(FirstName)+' '+ LTRIM(LastName) AS Name 
FROM 
    Contact

Result

Name

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

Gustavo Achong

Catherine Abel

Kim Abercrombie

Humberto Acevedo

Pilar Ackerman

Frances Adams

Margaret Smith

Carla Adams

Jay Adams

(Not all rows displayed)

The following is the SELECT statement syntax used to remove space from the right and left of a string using the RTrim and LTrim function:

Write Exercise Code:

SELECT 
     LTRIM(RTRIM(FirstName))+' '+ LTRIM(RTRIM(LastName)) AS Name 
FROM 
    Contact

Result

Name

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

Gustavo Achong

Catherine Abel

Kim Abercrombie

Humberto Acevedo

Pilar Ackerman

Frances Adams

Margaret Smith

Carla Adams

Jay Adams

(Not all rows displayed)

Left & Right Function

Functions are operations that are used to manipulate or change data. The Left/Right case function is an example of a function that returns specified number of characters from either the Left or Right. Basic Syntax

SELECT 
     column_name, Function(column_name) 
FROM 
    Tablename

The following is the SELECT statement syntax used for returning specific number of characters from right or left side of string:

The following is the SELECT statement syntax used to return three characters from left side of string:

Write Exercise Code:

SELECT 
    Left(LastName,3) As LeftThree 
FROM 
    Contact

Result

LeftThree

-----

Ach

Abe

Abe

Ace

(Not all rows displayed)

The following is the SELECT statement syntax used to return three characters from right side of string:

Write Exercise Code:

SELECT 
     Right(LastName,3) As RightThree 
FROM 
     Contact

Result

ong

bel

bie

edo

(Not all rows displayed)

Date/Time Manipulation Functions

DateADD() Function

Functions are operations that are used to manipulate or change data. The DateADD() is an example of a function that returns a new datetime VALUE based on adding an interval to the specified date. The following is the SELECT statement syntax used for specific functions:

Basic Syntax

SELECT 
     Function(Day,21,column_name) AS Alias 
FROM 
    Tablename

Write Exercise Code:

SELECT 
    DueDate 
FROM 
    PurchaseOrderDetail

Result

DueDate

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

2001-05-31 00:00:00.000

2001-05-31 00:00:00.000

(Not all rows displayed)

The following is the SELECT statement syntax used to add 21 days to the DueDate column:

Write Exercise Code:

SELECT 
     ProductID, DueDate,DATEADD(Day,21,DueDate) AS NewDueDate 
FROM 
     PurchaseOrderDetail

In more common terms, this request could be similar to. “Can you help me get a list purchase orders, just the IDs and due dates. Also can you display what the due date would if we extended terms out 21 days?”

Result

PurchaseOrderID DueDate NewDueDate

1 2001-05-31 00:00:00.000 2001-06-21 00:00:00.000

2 2001-05-31 00:00:00.000 2001-06-21 00:00:00.000

2 2001-05-31 00:00:00.000 2001-06-21 00:00:00.000

3 2001-05-31 00:00:00.000 2001-06-21 00:00:00.000 (Not all rows displayed)

DateDiff() Function

Functions are operations that are used to manipulate or change data. The DateDiff() is an example of a function that number of date and time boundaries crossed between two specified dates.

Basic Syntax

SELECT 
     Function(Day,column_name,GETDATE()) AS NewDate 
FROM 
    Tablename

The following is the SELECT statement syntax used count the difference in the number of days form the due date to the current date:

Write Exercise Code:

SELECT 
    DueDate,DATEDIFF(Day,DueDate,GETDATE()) AS NumberOfdays 
FROM 
     PurchaseOrderDetail

Result

DueDate NumberOfdays

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

2001-05-31 00:00:00.000 2604

2001-05-31 00:00:00.000 2604

(Not all rows displayed)

The following is the SELECT statement syntax used to get number of days between current date and when employee was hired:

Write Exercise Code:

SELECT 
      HireDate, GetDate() as TodaysDate, DATEDIFF(Day,HireDate,GETDATE()) AS 
       NumberOfdays 
FROM 
       Employee

Result

HireDate TodaysDate NumberOfdays ----------------------- ----------------------- ------------

1996-07-31 00:00:00.000 2008-07-17 17:31:03.893 4369 1997-02-26 00:00:00.000 2008-07-17 17:31:03.893 4159 1997-12-12 00:00:00.000 2008-07-17 17:31:03.893 3870

(Not all rows displayed)

This example uses the Convert () function to change one data type to another data type.

Write Exercise Code:

SELECT 
     Convert(Char(12), GetDate(), 101) as TodaysDate 
FROM 
     Employee

Now let’s convert a couple of columns:

Write Exercise Code:

SELECT 
      Convert(Char(12), HireDate, 101) as HireDate, Convert(Char(12), GetDate(), 101) as 
     TodaysDate, DATEDIFF(Day,HireDate,GETDATE()) AS NumberOfdays 
FROM 
    Employee 

Result

HireDate TodaysDate NumberOfdays ------------ ------------ ------------ 07/31/1996 07/24/2008 4376 02/26/1997 07/24/2008 4166 12/12/1997 07/24/2008 3877

(Not all rows displayed)

DateName() Function

Functions are operations that are used to manipulate or change data. The DateName() is an example of a function that returns a character string representing the specified datepart of the specified date.

Basic Syntax

SELECT 
    Function(month,GetDate()) AS 'Month Name' 
FROM 
    tablename

The following is the SELECT statement syntax used to get the month name from the specified date:

Write Exercise Code:

SELECT 
     Convert(Char(12),DueDate, 101) AS DueDate, DateName(month,DueDate) AS 'Month Name' 
FROM 
      PurchaseOrderDetail

Result

DueDate Month Name

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

05/31/2001 May

05/31/2001 May

(Not all rows displayed)

Contact Us to get any database project help at realcode4you@gmail.com