This is an exercise.
You don't have to show or hand in anything,
but if you have any problems or questions,
you are welcome to ask your teaching assistant.
There are some solutions to the problems.
But try to write the questions yourself first, and try them out in Mimer!
Objectives
-
To understand and practice the basics of SQL.
Preparations
We suggest that you read about SQL in the course book:
- Introduktion till frågespråket SQL (chapter 7 in the course book)
You should also have created the demo database,
as described in exercise 3.
Scenario
You are the DBA for the demo database,
and now you have to design some SQL queries to search the database.
Task
How do you write the following queries in SQL?
-
List all employees,
that is,
all the rows in the table employee.
-
List all the department names,
that is,
the column name for all the rows in the table dept.
-
Which parts are not available in our store,
that is,
which parts have a qoh ("quantity on hand") of 0?
-
Which employees have a salary between 9000 and 10000?
-
How old was each employee when he or she started working here?
-
Which employees have a last name that ends with "son"?
-
Which items have been supplied by a supplier named "Playskool"?
-
Which names, and what colour, have the parts that are heavier than a black tape drive?
Write this query with a sub-query in the where clause.
The query mustn't contain the weight as a constant.
-
Write the same query, but without a sub-query.
The query still mustn't contain the weight as a constant.
-
What is the average weight of black parts?
In this exercise, it is not allowed to create new tables or views to store
intermediate results.
Thomas Padron-McCarthy
(Thomas.Padron-McCarthy@oru.se),
October 31, 2007