Exercise 1

Many of the exercises in the course are based on the Company database. It contains five tables:
1.
Employees includes the first name, middle initial and last name of every worker of the company, as well as their CPR no., date of birth, address, sex, salary and the number of the department they are associated with.
2.
Departments indicates the name of each department of the company (Consumer Products, Industrial Products and Research), their respective numbers, the CPR no. of their managers and the date in which the current manager took up their post.
3.
Projects includes the project name, its number, the department number it is currently allocated to, and the offices where it is located.
4.
Locations lists the departments and the locations of all their offices.
5.
Allocations indicates the number of hours each employee has to spend per week on a particular project. Employees are represented by their CPR no. Employees may work on projects that belong to departments other than their own.
The contents of the five tables are shown below.
 
FNAME MINIT LNAME CPR BDATE ADDRESS SEX SALARY DNO
Lars T Andersen 123 1955-12-10 Klarup M 15,000 12
Kristian C Bohr 456 1965-10-05 Tylstrup M 18,000 11
Charlotte F Kierkegaard 789 1975-08-06 Vejgaard F 14,000 11
Uffe J Bajers 111 1960-09-07 Gistrup M 30,000 12
Hans U Brahe 222 1970-04-02 Svenstrup M 20,000 10
Helle O Dreyer 333 1950-01-08 Uttrup F 35,000 10
Peter P Nielsen 987 1973-05-30 Lundby M 23,000 12
Niels A Thorvaldsen 654 1953-02-27 Vodskov M 32,000 11
Tina C Jacobsen 321 1963-11-16 Nytorv F 26,000 12
Table 1: Employees

 
DNAME DNUMBER MGRCPR MGRSTARTDATE
ConsProd 10 333 1994-10-01
InduProd 11 654 1995-05-01
Research 12 111 1990-06-15
Table 2: Departments

 
PNAME PNUMBER PLOCATION DNUM
MobilePhone 1 Nørresundby 10
InteractiveTV 2 Nørresundby 12
MmedMonitor 3 Aarhus 11
PalmTop 4 Aalborg 10
MobileOffice 5 Aarhus 11
Table 3: Projects

 
DNBR DLOCATION
10 Aalborg
10 Nørresundby
11 Aarhus
12 Nørresundby
12 Frederikshavn
Table 4: Locations

 
ECPR PNO HOURS
123 3 27
456 3 12
789 4 35
111 1 15
111 2 5
222 5 30
333 3 20
333 5 20
987 4 25
654 2 30
321 1 15
321 2 10
Table 5: Allocations

We will in the course use the Oracle database system. You have all gotten an account on Oracle. Your Oracle user name is the same as your Unix user name and your default  Oracle password is your user name.
Please change your Oracle password right away it is done with the following command

SQL>ALTER USER <ORALCE USER NAME> IDENTIFIED BY <NEW PASSWORD>;

Note that the <ORALCE USER NAME> must be in UPPER CASE. Further, note that all statements in Oracle must be terminated by a semicolon ';'.

The purpose of this exercise is to get you all connected to Oracle and start working with the SQL query language.

  1. Set up your Oracle environment. A note on this can be found in the environment.sql file.
  2. Start an Oracle session by entering sql (or sqlplus if you have not set up your enviroment) on the command line in a Unix shell.
  3. Create the tables by executing the CREATE TABLE statements in the create_table.sql file.
  4. Insert data into the Employees table by executing the INSERT statements in the insert_employees.sql file.
  5. Load the data for the remaining table by copying the files in the data directory to a local directory and execute the commands load_departments, load_projects, load_locations, and load_allocations. Before executing any of the load files change their permissions, e.g., "chmod 755 load_*".
  6. Experiment with the SQL language, see, e.g., the file queries.sql.

Best regards,
Kristian Torp