Databasteknik: Assignment 5 - Advanced SQL

The table of assignments lists when you are supposed to do, and hand in, this assignment.

Objectives

• To understand and practice some more advanced SQL, including views, outer joins, and aggregate functions with group by and having.
• To practice report writing.

Preparations

• Introduktion till frågespråket SQL (chapter 7 in the course book)
• Mer om SQL (chapter 8 in the course book)
• Index och prestanda (chapter 22 in the course book, or chapter 21 in the first edition)
• Transaktioner (chapter 24 in the course book, or chapter 23 in the first edition)
• Lecture notes: A transaction example from Mimer

(Please be advised that some database management systems, including at least some versions of Mimer, give incorrect results for some complicated SQL queries that combine several explicit joins.)

Scenario

Task

1. Which part is heaviest, and what does it weigh?
2. Write a query that finds out which items cost more than the average.
3. Create a view that contains all items that cost more than the average.
4. Create a new table that contains all items that cost more than the average.
5. Explain the difference between the previous three SQL statements.
6. Which parts have we received shipments (in the table supply) of? We need the part number (pnum) and the name (pname). Write the query with a subquery in the where clause.
7. Write the same query, but this time without a subquery. Don't use an explicit join.
8. Write the same query, but this time with an explicit join.
9. Which parts have we not received any shipments of? Use a subquery in the where clause.
10. Write the same query, but this time with an outer join.
11. How many items have been sold by each department? It is enough to just show the department number and the number of items. (Hint: Use the columns dept and and quantity in the the table sale.) Departments that haven't sold any items don't have to be in the result. (You'll have to add the items using an aggregate function: You must report that department 26 sold 6 items, not just that they sold 5 of item 118 and 1 of item 120.)
12. The same query, but now we also want the department name in the result. Write the query without an explicit join.
13. Write the same query, but this time with an explicit join.
14. Write the same query once again, with the difference that departments that haven't sold any items should be in the result, with null as the number of items they have sold.
15. The same query as above, but now departments that haven't sold any items should have zero as the number of items they have sold. (Hint: coalesce)
16. What is the name and the number of the department that has sold the greatest number of items? (Hint: Define a view, and use it in the query.)
17. Earthquake! California sinks into the ocean, and all our suppliers in California disappear under the water. Write a query to delete them from the database. What happens when you run the query, if you have declared a foreign key? What happens if you have not declared a foreign key?
18. Assume that you didn't have any foreign key declarations, and deleted the suppliers in California. Comment on the results of these two queries:

    select item.number, item.name
    from item
    order by item.number;
    

    select item.number, item.name, supplier.name
    from item, supplier
    where item.supplier = supplier.number
    order by item.number;
    

19. The queries 1-16 above are used frequently in the database. We expect the database to grow to a more realistic size, with many thousands of items and many millions of sales. Which indexes should be created? Show the create index commands that should be used! (Assume that the database manager doesn't automatically create indexes on declared primary keys, so you'll have to explicitly create indexes for them too.)
20. Start two BSQL instances beside each other, to login twice in the same database and run two concurrent transactions. Show the effect of commit and rollback, and what happens if the two transactions try to commit conflicting changes. (Some hints can be found in the transaction example from the lecture.) Remember that by default, each SQL statement is considered its own transaction in BSQL, and you have to give the command start transaction to start a multi-statement transaction.

Examination

You can copy and paste the queries from the window and into the report. In Batch SQL, you can right-click on the title bar, and then choose Mark in the menu, and then Copy.

One of the objectives with this assignment is to practice report writing, so please make an effort to make your report understandable, easy to read, and if possible enjoyable.