Help needed in sql queries ! :)

/*

Question #1 -

The marketing team wants a list of all "Computing Novelties" products for an

email campaign they are putting together. Write a query to return the 83 Stock

Items that fall in this category. Include the Stock Item ID, the name of the item,

the supplier name, recommended price, color, stock group name, and supplier name. If the Color is null

then replace the value with an empty string ('').

This query will return 83 rows. 13 items will not have a color

*/

/*

Question #2 -

Write a query that returns all the sales people from the Application.People

table. The follow rules apply to this list:

- A salesperson is someone with the isSalesperson flag set

- Include the PersonID

- Fix the full name field to be last name then a comma and a space then the first name.

Assume that the current name only has a first and last name separated by a space

- Do not show @wideworldimporters on the network account (only the part before it).

- Remove all the parenthesis, dashes, and spaces from the phone number

Kayla Woodcock's record would look as follows:

PersonID FullName NetworkAccount PhoneNumber EmailAddress

2 Woodcock, Kayla kaylaw 4155550102 kaylaw@wideworldimporters.com

*/

/*

Question #3 -

Its time for the annual trade show and you want to make sure all

your suppliers make it to the party. The marketing team needs

a list of all your suppliers, their full delivery address, and

the name of both the primary and alternate contacts for the

supplier. Order the results by supplier name

You'll be using a number of tables - Purchasing.Suppliers, and

the People, Cities, and StateProvinces tables in the Application

schema. The columns to join things up are a bit more tricky -

- Suppliers to people on the PersonID and the PrimaryContactPersonID

or AlternateContactPersonID.

- Cities on CityID to Suppliers on DeliveryCityID

- StateProvices on StateProvinceID (on both sides)

Something to keep in mind... you can join to the same table multiple times

as long as you give them different aliases.

This query will return 13 rows and 9 columns. An example to compare to -

SupplierName PrimaryContactName AlternateContactName DeliveryAddressLine1 DeliveryAddressLine2 DeliveryCityID CityName StateProvinceCode DeliveryPostalCode

A Datum Corporation Reio Kabin Oliver Kivi Suite 10 183838 Southwest Boulevard 38171 Zionsville IN 46077

*/

/*

Question #4 -

Archer Lamble is preparing to send invites to the annual

Novelty Shop Expo for his high achieving customers.

To recieve an invitation, a novelty shop must have purchased

more than 32k in 2013. Archer only has 10 invitations - if more

than 10 people have reached the 32k goal, the 10 highest sales

get the invitation.

Utilizing Sales.Invoices, InvoiceLines, Sales.Customers, and

Sales.CustomerCategories tables, write a query that provides

Archer his list. You need to calulate the value of each invoice line by

multiplying quanity and unit price and then multiple this value by the

tax rate. Do not use the precalculated fields - do the calculation yourself

(you can check extendedPrice to see if you got it correct for each invoice line).

Return the customer name and how much the sold for 2013

If you run your query for 2014, you will get 10 records with the highest seller being

Tailspin Toys (Cherry Grove Beach, SC) with 43372.020000 in revenue. For 2013, you'll

only get 9 records with the highest being Wingtip Toys (Lake Ronkonkoma, NY) at 43522.900000

*/