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)