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)
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
SalesOrderHeaderSql 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) = SubTotalResult
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
tablenameThe 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
TablenameThe 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
ContactResult
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
ContactResult
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
ContactResult
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
TablenameThe 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
ContactResult
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
TablenameWrite 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
PurchaseOrderDetailIn 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
PurchaseOrderDetailResult
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
EmployeeResult
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
tablenameThe 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
PurchaseOrderDetailResult
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