These tasks are also visible in the SQL-Trainer user interface. This page will show the solutions after the deadlines.
Link 'result' shows one valid result for the query.
| K01 | List customer data about all customers. | result |
| K02 | List the identifier, name and address of customers that live in area A03 and are entitled to pay by credit. Order the result by customer name. | result |
| K03 | Make a list of customers that do not have a phone. Order the list by the area and by the name of customer. | result |
| K04 | Make a list of orders, the total price of which is less than 12 euros. Include in your list the date (in dd.mm.yyyy format without time) as column DAY, the identifier, the total price and the way of payment. Arrange the result by the time the order was made. | result |
| K05 | Fetch the Finnish names for the materials ordered as extras. | result |
| K06 | English names, portion types (modelID) and amounts of products included in the order 3012. Do not include extras. | result |
| K07 | Make a listing in Finnish of products that belog to the category Pizza. Give the names in alphabetic order in column PNAME. You may not use the identifier of Pizza category in your query. | result |
| K08 | Make listing in English about the ingredients of a Seafood pizza. List the materials in column Ingredient. | result |
| K09 | Make a report that shows the oder identifier, the delivery time counted from the time the order was made, and thedelivery time counted from the time the delivery was assigned to the delivery boy. Give the times in columns DELAY_ORD and DELAY_ASSN. Include enough identification data for the orders. Arrange the result so that the orders having the biggest overall delay come first. Hint: multiply the time difference by the number of minutes in 24 hours. Use round for the final result. | result |
| K10 | Make a list of orders that have not been assigned a delivery boy, yet. Include the order identifier, time when the order wasmade and the living area or the customer. Arrange the result by how old the orders are. | result |
| K11 | Make a list of orders that have been assigned for some delivery boy but that have not yet been registered as delivered. Include the order identifier, time when the order was made and the living area of the customer. Arrange the orders by when they were made. | result |
| K12 | Find out the ingredients not used in any products. Give the identifier and name of these ingredient. You may select the language. | result |
| K13 | Find out the delivery boys that have made deliveries only on their main delivery area. Give their names and their main delivery area. | result |
| K14 | Find out the states of undeliverd orders. Order should be reported to be in state 'assigned' if it has been assigned to a delivery boy, otherwise it is 'non-assigned'. Report the state in column STATE. | result |
| K15 | Some pizza eaters in area A01 have fallen sick. Tomato and ham pizzas or delivery boy Mike B are suspected to be the cause. Find out the identifiers, names, addresses, and phone numbers of customers that have ordered tomato and ham pizzas or recieved deliveries from Mike B. Product identifier for tomato and ham pizzas is P02. | result |
| K16 | Find out the contents of order 3019 in order to calculate the price for the order. The content listing should include products and extras, how much of them have been ordered, and their unit prices. Extras need not be connected to products. It is enough to give the identifiers of products and materials. Use PORTION as the modelId for extras. | result |
| Y1 | Give the number of customers as column CUSTOMERS | result |
| Y2 | How many customers have an e-mail address? Give the answer as column EM. | result |
| Y3 | Give the percentage (rounded to an integer) of customers
that have an e-mail address and the percentage of customers that have
a phone number as columns, EPROS, and PPROS respectively. Hint: use the round-function for rounding. |
result |
| Y4 | What is the most expensive product? Give the Finnish name, the product and model identifiers, and the price of the product. | result |
| Y5 | How many pizzas (the name of the product type in English is Pizza) have been ordered altogether? Give the result as column PIZZACOUNT. | result |
| Y6 | Count the shortest, longest and average delivery time for
orders. Delivery time is the difference of the time when the order was
made and when it was delivered. Give the results in columns SHORTEST,
LONGEST and MID. Hint: You may transfer the time interval to minutes by multiplying it with 24*60, and then rounding it with the round-function. Compute the average before rounding. |
result |
| Y7 | How many distinct products have been ordered? Give the answer in column ITEMS. Hint: You may use an expression as the argument for aggregate functions. Concatenate the product type identifier and the portion size identifier to obtain product id as a single value. |
result |
| Y8 | List the customers and their order amounts arranged so that the customer with the biggest amount is first. You should include at least the name of the customer and enough other information to identify customers uniquely. Give the number of orders in column ORDS. | result |
| Y9 | List the areas by their turnover (sum of prices paid) from best to worst. Give the turnover as column TOTAL. | result |
| Y10 | List the customers whose total value of orders exceeds 20 euros. Give the total value as column VALUE. | result |
| Y11 | List the number of delivery tasks for each delivery boy. Give the number in column TASKS. Arrange the list so that the boy with the least number of tasks becomes first. |
result |
| Y12 | Which product has been most popular by its selling volume. Give the volume in column VOLUME. You should include the type identifier, model and English name of the product. | result |
| Y13 | Give the identifier, name and delivery area of delivery boys that have had less than 3 deliveries to take care of. Arrange the list by the number of delivery tasks. | result |
| Y14 | Find out how big a proportion of the total value of orders
has been paid by credit. Give the result as column CPROS. Hint: Use a subquery embedded in the from part of the query to find out the total values. |
result |
| Y15 | Find out how many percent of all orders of each product
type have extras added on. Give the English name of the product type and
the percent of its orders with exras. Give the percentage rounded as an
integer in column EPROS. You need not include products that have never
been suplemented with extras. Hint: use subqueries in the from part of the query- Take into account the amouts of products ordered, but ignore the amounts of extras. |
result |
| Y16 | Find out how many customers live in each area and how many customers there are for each delivery boy with primary interest on that area. Give the number of customers in column CUST and the ratio per delivery boy in column C_PER_BOY.. | result |
| U1 | The pizza company has hired a new delivery boy. His name is Pick Upp. His main delivery area is A03. His identifier will be PICK. Register him as a new delivery boy. | result |
| U2 | Patty P becomes a customer. Her address is Rocky Road 5E and her phone number is 234987. She does not have an email address. She may buy only with cash. Rocky Road is located in area A01. Her customer id will be one bigger than the currently biggest customer id. Register her as a customer. | result |
| U3 | Customer Frank S moves to address Market Square 6, 122. This means that his living area changes to A02. Register the changes. | result |
| U4 | The prices of all products will be increased by 10 percent. The new price should, however, be rounded to one decimal (function round(expression,decimals) ). Carry out the increase. | result |
| U5 | There has been an additional delivery fee of 2 euros that has not been included in the total price of an order. From now on this should be included. Change the total price of those orders that have not been assigned a delivery yet. | result |
| U6 | We want to keep the active tables small. All the deliveries taken care of before 2.1.2002 have already been copied into the history tables. Delete the rows from the original table. | result |
| U7 | Delete the products that nobody has ordered. | result |
| U8 | Order 3023 has been altered. Update the total price to correspond
to the new situation (consider only the actual products. Taking care of
extras would require another update operation and this is not possible
with this tool). You need the Oracle special syntax for updates. |
result |