These tasks should be solved in the SQL-trainer training environment. The environment will show you the tasks. They deal with a kitchen furniture database.
By clicking the link 'model' you see a result produced by one correct answer query. The result need not be exactly the same. Do not change the column names in the result to others than the default unless it is requested in the task definition.
Results for the tasks U1-U8 are tablelisting after the operations.
| K01 | List all the producttypes that belong to the company's selection. | model |
| K02 | Give the identifiers of products (units) of model EX (exclusive) that cost more than 100 euros. | model |
| K03 | Give the number of the plan, he name of the customer and the date the plan was made for those plans that have not been converted into orders. | model |
| K04 | Prepare a price list that lists the products (identifier is enough, name is not required) in alphabetic order. It the product is avilable in many models they should be listed in decreasing order by the price. | model |
| K05 | What model - color combinations have been selected for the composition bodies of the current plans. List the combinations ordered by the model code. | model |
| K06 | Prepare a list of orders the delivery of which has been delayed. Include the plannumber, the name of the customer, the requested delivery time, and as column DELAY the number of days the delivery was delayd. | model |
| K07 | Find out the witdh and depth combinations of 90 cm high wall cabinet frames (WCF). | model |
| K08 | List the plannumber, the name of the customer, the address of the customer, and the requested delivery date of undelivered orders odered by the requested delivery date. Give the date in Finnish date format (dd.mm.yyyy) as column DDATE. Give the address of the costomer as column ADDRESS so that the city in appended at the end of street address and and separated from it with a comma and a space character. | model |
| K09 | What other elements than HM model handles and legs have been included in plans as separate elements. Give the unit identifier and the model identifier. Order the list primarily by the model identifier and secondarily by the unit identifier. | model |
| K10 | What parts and how many of them belong to the composition BCF40x3D? Include in the answer the identifiers and the names of the composition and the parts. | model |
| K11 | What are the materials available for the 40 cm wide base cabinet frames (BCF) listed in the price list. Do not rely on the structure of the unit identifier in your query. | model |
| K12 | What models provide white doors (FRO)? The doors need not to be included in the price list. Give the name and identifier of the model. | model |
| K13 | A customer is interested in cabinet composition BCF40x3D. List the choises and their prices. The listing should contain in addition to the prices and part identification data also the model, the material and the amount of parts. | model |
| K14 | What standard drawer cabinets are available. A drawer cabinet consists of a base cabinet frame (BCF) and at least one drawer (DRA). List the name of and identifier of the comnposition. You may not use the information embedded in the identifiers. | model |
| K15 | Find out how much more expensive the cabinet frames (BCF,WCF) of model EX are when compared to the corresponding frames of model UN. For each product give the identifier of the frame, its width and height, its price in UN model and the difference of prices (positive) in column DIFF. | model |
| K16 | List the plan number, the name of the custorer and the contents of the plan (number, name and amount) of included compositions or separate parts). Give the plan number in column PL_NO, the identifier of the composition or unit in colum PART_NO and the corresponding name in column PART_NAME. | model |
| Y1 | How many plans has been made? Give the amount in column PLANS. | model |
| Y2 | How many heights there are for wall cabinets (WCF)? Give the answer in column HEIGHTS. | model |
| Y3 | When was the latest plan made? Give the date in Finnish date format (dd.mm.yyyy) in column LATEST. select to_char(max(datemade),'dd.mm.yyyy') LATEST | model |
| Y4 | How many delayd deliveries there has been and what is their average delay in days. Give the amount in column DCOUNT and the delay in column DELAY with one decimal. Count only such orders that have already been delivered. | model |
| Y5 | What is the most expensive unit in the price list and what is its price? Give the identifier, the name and the price of the unit. | model |
| Y6 | Wine rack WSK4070 may be ordered as a separate unit or as a part in compositions. How many plans there are for WSK4070 as a part of a composition. The result of the query should contain the idertifier of the wine rack in column UNITCODE and the ordered amount in column CNT. | model |
| Y7 | Wine rack WSK4070 may be ordered as a separate unit or as a part in compositions. How many planned inclusions there are for WSK4070 altogether. The result of the query should contain the identifier of the wine rack in column UNITCODE and the ordered amount in column CNT. | model |
| Y8 | How many products (different product identifiers) there are for each product type. give the amount in column CNT. Sort the result by product type identifier. | model |
| Y9 | (Note: this tasks has been changed since publishing, EX changed to TR) The prices of standard compositions are figured to be 80% of the sum of the parts rounded as an integer. Find out what would be the price of BCF40x3DM when the doors (FRO) are of model TR and the other elements are of the model UN. Give the result in colum TOTAL. | model |
| Y10 | How many products of each model are listed in the price list.
Give the identifiers and the name of the model and the associated amount in column CNT.
You may omit the models that do not have any products in the price list.
| model |
|
| Y11 | AList the lowest and highest price for each product in column LOW and HIGH. Give also the number of choices in column CHOICES. Name of the element is not required. | model |
| Y12 | Try to find out how the material affects on the price of doors. Sort the materials by the average price of doors made of it, lowest price first. include the average price in column APRICE rounded to 2 decimals. Hint: function round(x,d) rounds. | model |
| Y13 | Prepare a report on how many plans have been made (column PLANS) and how big portion of then have been converted to orders (column ORDER_PERCENTAGE). Give the portion as a procentage with one decimals. | model |
| Y14 | Which composition has the most of parts and how many? Give the amount in column CNT. | model |
| Y15 | How many product of each model are listed in the price list. Give the identifier and the mname of the model. Sort the result in decreasing order by the amount. Include the models that do not have any units listed. Hint: use outer join or union. | model |
| Y16 | Compute the total prices for the plans as the sum of their parts. Give the total price in colum PRICE. sort the report on plan number. | malli |
| U1 | Append 'spice rack' (id SPR) as a new product type into the list of product types. | model |
| U2 | New door models are introduced on model HI. Their price has beed decided to be 20% higher (rounded to integers) that the corresponding doors of model ST. Model ST contains only doors. Register the new doors in the price list. | model |
| U3 | Donald Duck supplements his order with the wine cabinet WCF4070xW (model EX birch body and model UN birch front). Register the supplement. You may assume that Donald Duck has placed only one order. It is possible to count the itemnumber in the query, but you are also allowed to enter it as a constant. insert into planelement. | model |
| U4 | Bunny Easter moves to address Long Street 10 and his phone number changes to 55522. Register the changes. | model |
| U5 | The price of model EX product is increased with 10% (rounded as an integer). Register the increase. | model |
| U6 | Bill Buyer converts his plan into an order on 20.3.2007 and requests 1.6.2007 as the delivery date. He requests for a home delivery. Register the transaction. | model |
| U7 | Remove from the prduct type list the product types that do not have any products listed in price list. | model |
| U8 | Daisy Duck removes from her order all cabinets (compositions) with a grey body). Register the change. Item numbers of the remoining eitems need not be changed. | model |