Advanced SQL Queries for the Pine Valley Furniture Company Database - Term Paper Example

Advanced SQL Queries for the Pine Valley Furniture Company DatabaseQuestion 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_IDFROM ORDER_t, PRODUCT_LINE_tWHERE (((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_IDFROM PRODUCT_tGROUP BY PRODUCT_t.Product_ID, PRODUCT_t.Product_Description, PRODUCT_t.Product_Finish, PRODUCT_t.Standard_Price, PRODUCT_t.Product_Line_IDORDER 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 employeesSELECT EMPLOYEE_t.Employee_ID, EMPLOYEE_t.Employee_Name, WORK_CENTER.Work_Center_IDFROM 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_IDFROM Order_line_tWHERE (((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 3SELECT 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_PriceFROM PRODUCT_LINE_t INNER JOIN PRODUCT_t ON PRODUCT_LINE_t.Product_Line_ID = PRODUCT_t.Product_Line_IDWHERE (((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 .