SQL server 2012

Products databse column names associated with this:SELECT TOP 1000 [ProductID] ,[ProductName] ,[SupplierID] ,[CategoryID] ,[QuantityPerUnit] ,[UnitPrice] ,[UnitsInStock] ,[UnitsOnOrder] ,[ReorderLevel] ,[Discontinued] FROM [ClearWater].[dbo].[Products]

Created database from 1-3 thats used for other queries:

SELECT TOP 1000 [CategoryID] ,[CategoryName] ,[Description] FROM [ClearWater].[Ass7].[Categories]

/* Query 1 - Creating a new schema

First, drag over the following three lines as a block and execute them as

a single batch to create a new schema called Ass7 in your ClearWater

database.

USE ClearWater

go

CREATE Schema Ass7;

Then, execute the SELECT-INTO statement below to copy Categories table from

the default 'dbo' schema into Categories in Ass7.

select * into Ass7.Categories from Categories;

Now you should have two identical Categories tables, one in each schema, dbo

and Ass7. You may refresh Tables folder of ClearWater or execute the next SELECT

statement to see if Ass7.Categories exists.

select * from Ass7.Categories;

To complete Query 1, write another SELECT-INTO statement to copy certain categories

from Ass7 back to dbo and name this new table 'Categories_limited'. The statement

should create Categories_limited with only two columns, [CategoryID] and [Description]

with categories whose Description contains two or more 'N's.

*/

select CategoryID, Description

into Categories_limited

from Ass7.Categories

where Description like '%n%n%';

--to check and verify:

select * from Categories_limited;

/* Query 2 (Chapter 7)

Write an INSERT statement with a subquery to add categories of Categories_limited to

Categories in Ass7 schema if its CategoryID is an odd number.

Because [CategoryName] of Categories does not allow NULLs and Categories_limited has

no such column, when data is copied from Categories_limited to Categories in Ass7,

'Name not exist' is inserted for each CategoryName. This means the subquery must

prepare 'Name not exist' for CategoryName of each copied row.

Do not use hard coded values like 1, 3, 5, etc., for CategoryID in the statement.

Hint 1: an odd number divided by two should get a remainder 1.

Hint 2: test the subquery first before using it to insert.

*/

insert into Ass7.Categories

select CategoryID, 'Name not exist', Description

from Categories_limited

where CategoryID % 2 = 1;

--to check and verify:

select * from Ass7.Categories ;

/* Query 3 (Chapter 7)

Execute again the same INSERT of Query 2 to copy more rows from Categories_limited

into Categories in Ass7 schema, but this time, instead of 'Name not exist', insert

'N/A' in the CategoryName.

Onec the above INSERT is done, there must be some categories appear three times in

Categories of Ass7.

Write an UPDATE statement to change Description of Categories in Ass7 to the concatenation

of the first and last characters of each category's description. This UPDATE should not

apply to categories with 'N/A' in CategoryName.

For example, category 4 has CategoryName of 'Dairy Products', which is not 'N/A', so

its Description 'Cheeses' should be changed to 'Cs'.

*/

update Ass7.Categories

set Description = LEFT(Description, 1) + RIGHT(Description, 1)

where CategoryName <> 'N/A';

--to check and verify:

select * from Ass7.Categories ;

/* Query 4 (Chapter 7, 20 points)

It's your turn to write an UPDATE statement to change category name of Categories in

Ass7 by removing its last two characters if the category's products in dbo schema has

an average unit price above $30. For example, if the average unit price of products of

category 4 is $33.6, then, its category name 'Dairy Products' should be updated to

'Dairy Produc' after removing its last two characters 'ts'.

Hint 1: if you have done Query 1~3 correctly, this UPDATE should only update the name of

category 6 and 7.

Hint 2: use a subquery or join.

Hint 3: do not forget to add the prefix of the schema name to access the right Categories.

*/

begin tran

update Ass7.Categories

set CategoryName = LEFT(CategoryName, len(CategoryName)-2)

Where CategoryID in (select CategoryID

from Products

where UnitPrice > 28.86636

group by CategoryID, UnitPrice)

rollback tran

--to check and verify:

select * from Ass7.Categories;

/* Query 5 (Chapter 7, 20 points)

Write a SELECT statement first to find categories of products that are supplied by

suppliers in Denmark and Finland. Then, use the SELECT statement as a subquery to

write a DELETE statement to remove the categories of the SELECT statement from

Categories in Ass7.

Hint: You need to join two tables in the SELECT statement.

*/

--to check and verify:

select * from Ass7.Categories ;

/* Query 6 (Chapter 9)

Suppose the formula below defines for the total inventory of each product in

the Clearwater database.

TotalInventory = UnitPrice * UnitsInStock

The manager wants to divide products into three groups, X, Y, and Z based on

the TotalInventory using the following rules:

X products with TotalInventory higher than $1000

Y products with TotalInventory between $500 and $1000

Z products TotalInventory below $500

Write a SELECT statement with a CASE function to return product data in five

columns, including ProductID, UnitPrice, UnitsInStock, TotalInventory, and

InventoryGroup, where InventoryGroup contains only three possible values:

'X', 'Y', or 'Z'.

The output must be sorted first by InventoryGroup followed by TotalInventory,

both in ascending order.

Hint: Correct output should have the first three rows look like below.

ProductID UnitPrice UnitsInStock TotalInventory InventoryGroup

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

76 18.00 57 1026.00 X

63 43.90 24 1053.60 X

50 16.25 65 1056.25 X

*/

select ProductID, UnitPrice, UnitsInStock, TotalInventory = UnitPrice*UnitsInStock,

case

when UnitPrice*UnitsInStock > 1000 then 'X'

when UnitPrice*UnitsInStock between 500 and 1000 then 'Y'

else 'Z'

end as 'InventoryGroup'

from Products

order by 'InventoryGroup', TotalInventory

/* Query 7 (Chapter 9, 20 points)

Write a SELECT statement with CASE, CHARINDEX, LEFT, and LEN functions to return

three columns: ProductName, FirstWord, and FirstWordLength of every product. Your

output should print the first five products with the longest FirstWord.

Here FirstWord means the first word of ProductName from its first character to

the last character before the first space. If ProductName has no space, then the

whole word is returned. FirstWordLength measures the number of characters of

FirstWord.

Hint 1: You use CHARINDEX to test if ProductName contains spaces.

Hint 2: You may need CHARINDEX twice, once to extract the first word with the help

of LEFT, and the other to measure its length by using LEN.

Hint 3: Correct output should have the first 5 rows like below:

ProductName FirstWord FirstWordLength

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

Gudbrandsdalsost Gudbrandsdalsost 16

Lakkalikri Lakkalikri 12

Vegie-spread Vegie-spread 12

Fltemysost Fltemysost 11

Singaporean Hokkien Fried Mee Singaporean 11

*/

/* Query 8 (Chapter 9)

Write a SELECT statement with a ranking function to return product data in five

columns: ProductID, SupplierID, UnitPrice, CategoryID, and 'Price Rank by Supplier'.

We want to rank products by assigning a continuous unique rank value from 1, 2, 3,

....etc. and display them in the 5th column. Ranking should base on each product's

UnitPrice, plus, products of different suppliers must be the ranked independently.

That means supplier 1 has its products ranked from 1, 2, 3, .... based on their

UnitPrice and supplier 2 also ranks its products from i, 2, 3, .... using the same

ranking criteria.

In case two products of the same supplier have identical UnitPrice such as products

34 and 67, both have a supplier 16 and have the same price $14.00, the one with larger

ProductID should be printed first.

Hint: Correct output should contain products of supplier 15 and 16 ranked as below.

They are only a portion of the output. There are many other rows before and

after these six rows.

ProductID SupplierID UnitPrice CategoryID Price Rank by Supplier

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

69 15 36.00 4 1

71 15 21.50 4 2

33 15 2.50 4 3

35 16 18.00 1 1

67 16 14.00 1 2

34 16 14.00 1 3

*/

select ProductID, SupplierID, UnitPrice, CategoryID,

row_number() over(partition by SupplierID

order by UnitPrice desc, ProductID desc) as 'Price Rank by Supplier'

from Products;

--or, using RANK() for the same output

select ProductID, SupplierID, UnitPrice, CategoryID,

RANK() over(partition by SupplierID

order by UnitPrice desc, ProductID desc) as 'Price Rank by Supplier'

from Products;

--------------END--------------