SQL Server
Kaplan University
IT350
May 18 2014

1.. Write a query using the Production.ProductReview table. Use CONTAINS to find all the rows that have the word socks in the Comments column. Return the ProductID and Comments columns
Answer;
Free text is used to look for columns that contain character-based data types for values that match the meaning and not the words in the search condition. Meanwhile the like operator is used to perform wildcard pattern matches. And also in the where clause of a select, insert, update and delete statements. SELECT ProductID, Comments FROM Production.ProductReview WHERE CONTAINS (Comments,'socks');

2 Select all the columns and rows from the production.productlistprice, display output in order of lowest to highest price.
Answer
SELECT * FROM production.ProductListPriceHistory order by ListPrice;

Q3. Write a query that returns the business entity ID and name columns (concatenated to show as 1 formatted column called Full Name) from the Person.Person table. Sort the results by Full Name . Make sure to account for Nulls.
Answer;
SELECT BusinessEntityID, FirstName + ' ' + MiddleName + ' ' + LastName AS "Full Name"
FROM Person.Person;

Q4. Write a query using the Sales.SpecialOffer table that multiplies the MaxQty column by the DiscountPct column. If the MaxQty value is NULL, replace it with the value 10. Include the SpecialOfferID and Description columns in the results.
SELECT SpecialOfferID,Description, DiscountPct * ISNULL(MaxQty,10) AS MaxDiscount
FROM Sales.SpecialOffer;

Q5. Write a query that displays characters 10 to 15 of the AddressLine1 column in the Person.Address table.
Answer.
SELECT AddressLine1, SUBSTRING(AddressLine1,10,6) as "Characters 10 - 15" FROM Person.Address

Q6. All the data in the ProductNumber in the Production.Product table contains a hyphen (-). Write a query that uses the