Assignment 1 SIT103 – Introduction to Database Design

Assignment 1 Trimester 1 – 2016 Due Date: Sun 17th Apr, 2016 Background

In this assignment you need to create a small database for ABC company to store employees' information. Requirements You are to prepare SQL query statements that will provide answers to the following 13 requests for information.

Queries 1. Create 3 tables to store Employees' details, Salary and Department details including following fields (select proper data type for fields):

a) Employee: Staff ID, First name, Last name, Gender, Marital status, Date of Birth, Hire date, Address, Postcode, Phone number and any other field/fields you would like to add for joining tables.

b) Salary: Salary, Manager ID, Employment Status (Full Time, Part Time or Casual) and any other field/fields which might be required to create relationship between

tables. c) Department: Department Number, Department Name and any other field/fields which might be required to create relationship between tables. d) Insert 15 records in the tables. Make sure to insert all data using a proper value and

format. For example, do not enter 1234 for the phone number. A proper value would be like like 03 92599617.

2. List Staff ID and Full names of female employees sorted by their last names. 3. Display number of married employees. 4. Display full name of the oldest and youngest employee including his/her age.

5. Display the employees detail who is the most experienced. 6. List all employees who have "b" in their family name and work as full time staff. 7. List all employees who were born on "July" and have between 1 to 3 years experience

in the ABC company. 8. List employees full name, salary, and the department number they are working in. 9. Create your own query. It must include a join query. Submit the following:

• question your query is answering • the SQL query

• The mark for this question will depend on the complexity of the query. • Higher marks will be given for queries that are more complex and/or innovative. • If you do not provide a description of what question the query is answering, you will get

zero for this query. Submission Submit a copy of your queries, electronically on the course portal, on or before the due date. You must submit the syntax (Oracle) of the queries themselves, including the screen shot of

the output. Make sure you include the username and password of your Oracle database.