Compose a 500 words assignment on advanced sql queries for the pine valley furniture company database. Needs to be plagiarism free!

Compose a 500 words assignment on advanced sql queries for the pine valley furniture company database. Needs to be plagiarism free! Advanced SQL Queries for the Pine Valley Furniture Company Database

Question 1: What are the IDs for all orders that include furniture produced by the product line 1?

SELECT ORDER_t.Order_ID, PRODUCT_LINE_t.Product_Line_ID

FROM ORDER_t, PRODUCT_LINE_t

WHERE (((PRODUCT_LINE_t.Product_Line_ID)=1)).

In order to make this query, we need to know which entities or tables we will query. We need the IDs of all the orders that were made on all the furniture processed by product line one.

The most important table in this case is the ORDER table, which we know as ORDER_t. The field from this table that identifies the orders for us is the Order_ID. Given, we need the other table, which has information on product lines. This is the Product_Line table. From this table, we need to query the field that identifies all the product lines for us. It is the Product_Line_ID. We then specify which product line we need to use. This is the product line one. Hence, we have the SQL query above.

Question 2: List the details about the product with the lowest standard price.

SELECT TOP 1PRODUCT_t.Product_ID,PRODUCT_t.Product_Description, PRODUCT_t.Product_Finish, PRODUCT_t.Standard_Price, PRODUCT_t.Product_Line_ID

FROM PRODUCT_t

GROUP BY PRODUCT_t.Product_ID, PRODUCT_t.Product_Description, PRODUCT_t.Product_Finish, PRODUCT_t.Standard_Price, PRODUCT_t.Product_Line_ID

ORDER BY Min(PRODUCT_t.Standard_Price).

In this query, we need to come up with the list of the products that has the lowest price. To get the result, we need to sort the prices of the products and choose the one that is the least among them all. In the parameters for the queries, we indicate that we would like to pick the top item from the field whose values have been sorted in ascending order. From then on, we display the top 1 item to show us the least item in terms of the price.

Question 3: List the employee ID, name, and his/her work center ID for all employees

SELECT EMPLOYEE_t.Employee_ID, EMPLOYEE_t.Employee_Name, WORK_CENTER.Work_Center_ID

FROM EMPLOYEE_t, WORK_CENTER.

This query is obtained from the employee table and the work center table. From the employee table we get the employee identifications and the names of the employees. The work center table is only meant to give us the work center identifications for the employees. Once we get the values from both of the tables, we make the query.

Question 4: How many different items were ordered on order number 1006?

SELECT Order_line_t.Order_ID, Order_line_t.Product_ID

FROM Order_line_t

WHERE (((Order_line_t.Order_ID)=1006)).

The above query is made on the order line table of the database. This table contains both the order ids as well as the product identification numbers. In order to know which different products are contained in the order number 1006, we design the query in such a way that we display the order ids for all the product lines while indicating the product line number 1006 as the only restriction on the query.

Question 5: Give 10% discount to all the products manufactured by the product line 3

SELECT PRODUCT_LINE_t.Product_Line_ID, PRODUCT_t.Standard_Price, PRODUCT_t.Product_Description, PRODUCT_t.Product_ID, PRODUCT_t.Standard_Price*0.9 AS Discounted_Price

FROM PRODUCT_LINE_t INNER JOIN PRODUCT_t ON PRODUCT_LINE_t.Product_Line_ID = PRODUCT_t.Product_Line_ID

WHERE (((PRODUCT_LINE_t.Product_Line_ID)=3)).

In order to know the discounted product prices for the products manufactured by the product line 3, we need to calculate the discounted prices and display them on a different column. The operands for the calculation are the Standard_Price for all the products, and 0.9. Once the calculation has been done, we display the product details of the products manufactured by line 3.

Works Cited

"Chapter 5 Logical Database Design and the Relational Model." e-Portfolio TurgutTezir.N.p., 2006. Web. 2 Mar. 2012 .