A Gentle Introduction to

SQL


Pick an engine:

Southwind database: hard questions

You may need to create views to complete these questions - but you do not have permission to create tables or views in the default schema.

Your SQL commands are executed by user scott in schema gisq - you may create or drop views and tables in schema scott but not in gisq.

10.5 When creating a view in scott you must specify the schema name of the sources and the destination.

11 Produce an exceptions list. There are three kinds of exception:
  • RETURN - a customer returns a product
  • REFUSED - a delivery is returned to the supplier
  • INCOMPLETE - a customer is sent an incomplete shipment
  • OUTSTANDING - an order has not been shipped
The exceptions list should show the customer/supplier id and name, the date, the exception code, the product involved and any other details that are available - such as the explanation

12 For each item, calculate the total number currently in stock based on the most recent stock check and deliveries/shipments since that date. You should assume that customer returned items are put back in stock

13 List the customers by the degree of alliteration. You may assume that every firm and every address contains a single space - giving four words in all. You are free to calculate the "degree of alliteration" as you see fit. Be sure to include a precise description in your submission.

14 Produce a bar chart showing the total capital value of the stock week by week for the 8 weeks from 11th January to 1st March 2002. The bar chart may have horizontal bars made of characters.

15 Obtain a list of discrepancies in the stock levels