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 = VALUEUsing 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 >=24In 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 >=24In 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.78Result
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_nameThis 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_nameThis 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
tablenameThis 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
VendorResult
-----------------------------------------------------------------
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
tablenameCombine 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)