The assignment must be neatly done and organized by tasks. This is a \"W\" cours

Question

The assignment must be neatly done and organized by tasks. This is a "W" course. Presentation matters. The explanations are as important as the SQL. The explanations should be written in complete, correct sentences. For each query, show the SQL followed by the result, followed by the explanation. Number the pages. Place your name at the top of each page. If you hand in nothing on the day this assignment is due, the grade will be zero. If you hand in a substantial portion of the work on the day it is due you will get partial credit. Significant number of points will be subtracted for disorganized and sloppy submission Please ask me if you have any questions regarding the submission or if you do not understand what is expected. Hand in a listing of the SQL, result and explanation of the logic for the following questions. Task One (Use STOCK and NATION table) List Nation Code, Nation Name, Stock Code, Stock Name, and Stock Quantity for every stock. Produce one row for each stock showing these columns. List the results in order by Nation Name and within Nation order by Stock code, List Nation Name, Stock Name, and Stock Price for all stocks. List these in descending order of Stock Price. Repeat the first query omitting the WHERE clause. Do NOT hand in the query or result, but briefly describe the result and explain what happened. Task Two Think about a database containing data about Customer and orders. Customer data consists of Customer Id Number and Customer Name. Customer id numbers are guaranteed to be unique. Customer names are not. Order data consists of Order Number and Order Date. Obviously, the order numbers are unique and the dates are not. In this case, we know that one order comes from one customer and that one customer might give us multiple orders. Draw a data model similar to figure 4-1 for customers and orders. Hand this in We might attempt to represent the relationship between customers and orders by adding order number to the customer table. Or, we might add customer number to the order table. Briefly state which approach makes more sense and explain why Don't add any other data to this model. Task Three (Use the sale, item, and lineitem tables from chapter 5) (A) List itemcolor, and the total number of (different kinds of) items stocked for each itemcolor. (Clarification - there are 4 brown items, 2 white items, 1 red item etc.) (B) List itemcolor, and the total value of items sold (that's the sum of (lineprice X lineqty)) for each itemcolor. (C) List itemno, itemtype, itemname, number of sales (that's number of lineitem rows), and the total value of items sold (that's the sum of (lineprice X lineqty)) for each item. Do not include items for which no sales have been recorded.

Explanation / Answer

TASK ONE:-

1.

SELECT NATION_CODE,NATION_NAME,STOCK_CODE,STOCK_NAME,STOCK_QUANTITY FROM STOCK S,NATION N WHERE S.STOCK_CODE=N.STOCK_CODE ORDEY BY N.NATION_NAME,S.STOCK_CODE;

In this query we are using select statement to retrieve the required information. In the select query we have written the column names NATION_CODE,NATION_NAME,STOCK_CODE,STOCK_NAME,STOCK_QUANTITY which needs to be retrieved from the given tables stock and nation where it matches the condition that stock id in both the tables. So that it will give the exact results of that particular stock only. Because of order by clause in the query it produces the output in the alphabetical order of nation name and the ascending order of stock code.

2.

SELECT NATION_NAME,STOCK_NAME,STOCK_PRICE FROM STOCK S,NATION N WHERE S.STOCK_CODE=N.STOCK_CODE ORDER BY STOCK_PRICE DESC;

In this query we are just retrieving the required column's from the tables which matches the stock id in both the tables in the descending order of stock price.

3.

SELECT NATION_CODE,NATION_NAME,STOCK_CODE,STOCK_NAME,STOCK_QUANTITY FROM STOCK S INNER JOIN NATION N ON S.STOCK_CODE=N.STOCK_CODE ORDEY BY N.NATION_NAME,S.STOCK_CODE;

In this query we are using inner join rather than the where condition. The inner join will retrieves all the matching tables from both the tables based on the given condition and the order by the name and stock_code.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.