Finkelstein methodology, Database Management System

PART MARKING: For this section, we will use part marking. This means we will supply model answers to all questions but we will only mark six questions. We will choose these randomly and each question chosen for marking will be worth three marks. If you do not answer a question that is chosen for marking, you will receive no marks. You should therefore attempt all the questions. These questions are designed to hone your SQL skills before the exam.

Use the relations below to write SQL queries to solve the business problems specified.

CLIENT(clientno#,name, client_referred_by#)
ORDER(orderno#, clientno#, order_date, empid#)
ORDER_LINE(orderno#, order line number#, item_number#, no_of_items, item_ cost, shipping_date)
ITEM(item_number#, item_type, cost)
EMPLOYEE(empid#, emp_type#, deptno, salary, firstname, lastname)


1. Each client may be referred by another client. If so, the client number of the referring client is stored in referred_by.

2. The total cost for a particular order line = no_of_items X item_cost.

1. Display all the rows and columns in the CLIENT table. Sort by client name in reverse alphabetical order.

2. Display the item number and total cost for each order line (total cost = no of items X item cost). Name the calculated column TOTAL COST.

3. Display all the client numbers in the ORDER table. Remove duplicates.

4. Display the order number and client number from the ORDER table. Output the result in the format. Client ordered

5. Display full details from the ORDER_LINE table where the item number is (first condition) between 1 and 200 (no > or < operators) OR the item number is greater than 1000 AND (second condition) the item cost is not in the list 1000, 2000, 3000 OR the order number is not equal to 1000.

6. Display the client name and order date for all orders using the traditional method.

7. Repeat query (7) but also display all clients who have never ordered anything.

8. Display the client name and order date for all orders using the natural join keywords.

9. Display the client name and order date for all orders using the JOIN . . . USING method.

10. Display the client number, order date and shipping date for all orders where the shipping date is between three and six months after the order date.

11. Display the client number and name and the client number and name of the person who referred that client.

12. Display the client name in upper case only and in lower case only.

13. Display the second to fifth characters in each client name.

14. Display the item_cost and then round it to the nearest hundred, ten, unit, tenth and hundredth.

15. Display the item_cost and then truncate it to the nearest hundred, ten, unit, tenth and hundredth.

16. Display the order number, order line number and the shipping date. If the shipping date is null, display the string .

17. Display the order number and average item cost for each order.

18. Display the clientno and total value for all orders placed by that client. Output the result in the following format: Client has placed orders to the value of

19. Display all clients whose name begins with the letter J or contains the letter M anywhere or contains E as the third letter.

20. Using a set operator, display the client number of all clients who have never placed an order.

21. Using a set operator, display the client number of all clients who have ever placed an order and whose name does not contain the string Sm.

22. Display the order number, number of lines in the order, total number of items and total value for all orders that have a total value greater than $100

23. Display the client name for all clients who have placed an order where any order line has more than 3 items. Do not use a table join anywhere in your query.

24. Display the order number for all orders whose average item cost is greater than the overall average item cost across all orders.

25. Display the client number and the value of the highest value order placed by that client. (High challenge question - well above exam standard).

26. Display the earliest shipping date in the format: DD/MON/YYYY

27. Display the order number and the number of months since the order was shipped for all orders that have been shipped in the last year (365 days). (Hint: Unshipped orders will have a null value).

28. Display the item_number, the total number of times that item has been sold and the average cost for that item. Restrict that output to items that have been included in more than 500 orders. (Hint: Assume an item will appear in only one order line of an order).

29. Display the surname for all employees who earn less the average salary of all those employees in the department with the lowest average salary.

30. Display the client number and the total number of orders the client has placed for all clients who have placed at least one order in 2011. (Hint: Use exists).

Posted Date: 3/2/2013 2:14:41 AM | Location : United States

Related Discussions:- Finkelstein methodology, Assignment Help, Ask Question on Finkelstein methodology, Get Answer, Expert's Help, Finkelstein methodology Discussions

Write discussion on Finkelstein methodology
Your posts are moderated
Related Questions
What are the advantages of object ID? The main advantages of Object IDs are as below: i) IDs are not changing  ii) IDs are completely independent of the changes in data v

Provide Examples of different types of Time and Date formats.  It's most common store dates using a dash (-) as a colon (:)and the delimiter as the time delimiter. The given fo

Need a sample of a clinic database relationships

Create a table according to given data:  AmbulanceDriver(Driver_Number#, Name, Address, Birth_Date) AmbulanceDriverTeam(Team_Number#, Driver_Number#, Join_Date, Lea

Before defines the second type of integrity constraint, viz., Entity Integrity, we should be well-known with the concept of NULL. Mostly, NULL is intended as a basis for dealing

What are uncommitted modifications? The immediate-modification technique permits database modifications to be output to the database whereas the transaction is still in the act

You are managing the following database system: • DBMS: Oracle 11g • OS: Sun Solaris • System architecture: Web-based application using ASP.Net • Purpose of Server: HR managem

Events4Fun is an event management company in Europe with branches in South America as well. The company is well-known for its efficiency, good-quality services, and affordable char

Define the terms 1) physical schema 2) logical schema. Physical schema: The physical schema explains the database design at the physical level, which is the lowest level of abs