INF3707/101/3/2015

Tutorial letter 101/3/2015 Database Design and Implementation

INF3707 Semesters 1 & 2 School of Computing

IMPORTANT INFORMATION:

This tutorial letter contains important information about your module.

2

CONTENTS

Page

1 INTRODUCTION .......................................................................................................................... 3

2 PURPOSE OF AND OUTCOMES FOR THE MODULE............................................................... 4

2.1 Purpose ........................................................................................................................................ 4

2.2 Outcomes ..................................................................................................................................... 4

3 LECTURER(S) AND CONTACT DETAILS ................................................................................... 5

3.1 Lecturer(s) .................................................................................................................................... 5

3.2 Department ................................................................................................................................... 5

3.3 University ...................................................................................................................................... 5

4 MODULE-RELATED RESOURCES ............................................................................................. 6

4.1 Prescribed books .......................................................................................................................... 6

4.2 Recommended books ................................................................................................................... 6

4.3 Electronic Reserves (e-Reserves) ................................................................................................. 6

5 STUDENT SUPPORT SERVICES FOR THE MODULE ............................................................... 6

6 MODULE-SPECIFIC STUDY PLAN ............................................................................................. 6

7 MODULE PRACTICAL WORK AND WORK-INTEGRATED LEARNING .................................... 7

8 ASSESSMENT ............................................................................................................................. 7

8.1 Assessment plan .......................................................................................................................... 7

8.2 General assignment numbers ....................................................................................................... 8

8.2.1 Unique assignment numbers ........................................................................................................ 8

8.2.2 Due dates for assignments ........................................................................................................... 9

8.3 Submission of assignments .......................................................................................................... 9

8.4 Assignments ................................................................................................................................. 9

9 OTHER ASSESSMENT METHODS ........................................................................................... 20

10 EXAMINATION ........................................................................................................................... 20

11 FREQUENTLY ASKED QUESTIONS ........................................................................................ 20

12 SOURCES CONSULTED ........................................................................................................... 20

13 CONCLUSION ............................................................................................................................ 20

14 ADDENDUM ............................................................................................................................... 21

14.1 23

INF3707/101

3

1 INTRODUCTION Dear Student

Welcome to the School of Computing and the module in Database Design and Implementation. Access to a computer, the internet and myUnisa is essential for this module. The module is presented as a practical course. All the information about the content of the course will be found in this tutorial letter, the manuals and on the virtual campus (myUnisa). Logon to myUnisa as soon as possible and download the additional information for this module. You need to purchase your manuals directly from the publishers. All the information on how to order the prescribed manuals is available on myUnisa under "Announcements". On myUnisa you will find the tool "Home". The information displayed here is very important and will guide you through the different tools that we are going to use specifically for this course. In this module, you will be introduced to Oracle’s implementation of SQL. We will be using Oracle 10g XE for the purpose of this course. Installing Oracle 10g XE: Note that Oracle software is shipped as a CD in your study manual or can be downloaded for free from the Oracle website. The CD is part of the manuals (textbook) that you must purchase. Follow the steps as explained in this tutorial letter when installing Oracle 10g XE. The instructions are given on myUnisa under "Additional Resources". Tutorial Letter 101 contains important information about the scheme of work, resources and assignments for this module. We urge you to read it carefully and to keep it at hand when working through the study material; preparing for assignments; preparing for the examination and addressing questions to your lecturers. Read Tutorial Letter 301 in combination with Tutorial Letter 101, since it gives you an idea of important general information when studying at a distance at a specific college. All follow-up tutorial letters and communication will be published on myUnisa. You will find the assignments in this tutorial letter which also gives you all the information you need with regard to the prescribed manuals and other resources and how to obtain them. Please study the information carefully and make sure that you purchase the textbook as soon as possible. Included in this tutorial letter is certain general and administrative information about this module. Please study this section carefully. Right from the start we would like to point out that you must read the entire tutorial letters carefully during the year as soon as you receive them, because they always contain important and, sometimes, urgent information. The tutorial letters will be available on myUnisa as well. Thus, at this stage, it would be a very good idea to drop everything, run to your computer, logon to myUnisa, and read the information under “HOME”. We hope that you will enjoy this course!

4

2 PURPOSE OF AND OUTCOMES FOR THE MODULE 2.1 Purpose

Students who successfully complete this module will be able to design, implement and use database management systems. This module provides fundamental and required knowledge, skills and values which will support further studies and applications in the sector of Computer Science and Information systems, in the field of Database Management systems as part of either Bachelor of Science degree in Computing or Informatics or the Bachelor of Commerce degree with Informatics as major. These competencies therefore contribute to the development of competitive information technology practitioners who have strong technical skills of designing, implementing and managing database systems.

2.2 Outcomes

For this module, there are several outcomes that we hope you will be able to accomplish by the end of the course:

Specific outcome 1:

Demonstrate knowledge of commercially available DBMS end-user tools

Range:

• Commercially available DBMS end-user tools. • Simple indexes, multi-level indexes, index-sequential files, tree structures Assessment criteria:

• Students critically analyse and synthesise the features and limitations of the various DBMS end-user tools

• Students demonstrate an informed understanding of the interaction between the tools and the database.

• Students can use the tools to create workable solutions to well-defined, but unfamiliar problems.

Specific outcome 2:

Gather and review the requirements for database access for a computer application, using Structured Query Language (SQL)

Range:

• The database access requirements include at least four of the following: data sharing, integration, abstraction, data independence, data models, data definition language, data manipulation language and data control language.

Assessment criteria:

• Students critically analyse the feasibility of the requirement. • Students demonstrate an informed understanding of the database access objectives and

critical performance factors. • Students adopt the procedure to ensure that the outcomes meet the database access

requirements.

INF3707/101

5

Specific outcome 3:

Perform testing of programs for a computer application that accesses a database, using SQL.

Range:

• The test includes access to database and debugging

Assessment criteria:

• Students check program logical paths; correct program code to eliminate errors identified through testing; and access functions in the required design environment

Specific outcome 4:

Create program coding for database access for a computer application, using SQL

Range:

• Types of data access include user-defined types, user-defined routines, reference types, collection types, support for large objects, stored procedures, multiple-joined tables.

Assessment criteria: • Students code and implement a program according to the program design, using the

language constructs to facilitate the understanding of the code, ensuring that the data integrity, accessed by multiple users and processes, is maintained.

3 LECTURER(S) AND CONTACT DETAILS 3.1 Lecturer(s)

Details for the Primary Lecturer of this module are available in Tutorial letter 301.

Course email address: INF3707-15-S2@unisa.ac.za 3.2 Department

Details for the School of Computing are available in Tutorial letter 301.

3.3 University

To contact the University, you should follow the instructions in the myStudies @ Unisa brochure. Remember to have your student number available when you contact the University.

When you contact the Lecturer, please do not forget to always include your student number. This will help the Lecturers to assist you.

6

4 MODULE-RELATED RESOURCES 4.1 Prescribed books

These manuals follow the official Oracle University syllabus. The prescribed textbook / manual(s) for this year: Title: Oracle Database 10g: SQL Fundamentals 1& Fundamentals 11 Publisher: Master Skill Edition: 1 Year published: 2012 (This is an official Oracle study manual and Master Skill has a 2012 copyright). Note: A CD containing the Oracle 10g XE installations and other data is included in the manuals. These manuals cannot be purchased at any of the official bookshops; you need to order them online. The manuals are printed on demand and are specifically designed for Unisa. The online procedure for purchasing the study manuals is available on myUnisa under "Announcements". The prescribed manuals you have purchased consists of two manuals, namely Oracle Database 10g: SQL Fundamentals I & Fundamentals II. Each part is divided into lessons. At the end of each lesson you will find the workshop and exercises. 4.2 Recommended books

There are no recommended books for this module. However, there are numerous websites and web pages available where you will be able to download sample code, online textbooks, and so forth. The links to these websites are available on myUnisa.

4.3 Electronic Reserves (e-Reserves)

There are no e-Reserves for books for this module. 5 STUDENT SUPPORT SERVICES FOR THE MODULE Important information appears in your my Studies @ Unisa brochure. 6 MODULE-SPECIFIC STUDY PLAN Use your my Studies @ Unisa brochure for general time management and planning skills.

INF3707/101

7

Syllabus Assignment Retrieving data using the SQL select statement 01 fundamentals 1–lesson 1

Restricting and sorting data 01 fundamentals 1–lesson 2

Using single raw functions to customise output 01 fundamentals 1–lesson 3

Reporting aggregated data using the group

functions

01 fundamentals 1–lesson 4

Displaying data from multiple tables 01 fundamentals 1–lesson 5

Using subqueries to solve queries 02 Fundamentals 1–lesson 6

Using the set operators 02 Fundamentals 1–lesson 7

Manipulating data 02 Fundamentals 1–lesson 8

Using DDL statements to create and manage

tables

02 Fundamentals 1–lesson 9

Creating other schema objects 02 Fundamentals 1–lesson 10

Managing objects with data dictionary 02 Fundamentals 1–lesson 11

Controlling user access 02 Fundamentals 1–lesson 1

Managing schema objects 02 Fundamentals 11–lesson 2

Manipulating large data sets 02 Fundamentals 11–lesson 3

Generating reports by grouping data 02 Fundamentals 11–lesson 4

Managing data in different time zones 02 Fundamentals 11–lesson 5

Retrieving data using sub queries 02 Fundamentals 11–lesson 6

Hierarchical retrieval 02 Fundamentals 11–lesson 7

Regular expression support 02 Fundamentals 11–lesson 8

7 MODULE PRACTICAL WORK AND WORK-INTEGRATED LEARNING This is a practical course. 8 ASSESSMENT Assignments are regarded as part of the learning material for this module. When doing the assignment; studying the text you have to read; consulting other resources; discussing the work with fellow students or tutors; or doing research, you are actively engaged in learning. Looking at the assessment criteria given for each assignment will help you to get a better idea of what is required of you. Submit the multiple-choice assignments via myUnisa. Do not fax, e-mail or post the assignments directly to the lecturer. For detailed information on and the requirements for the assignments, please refer to the brochure, my Studies @ Unisa.

8.1 Assessment plan

Although you may work together when preparing assignments, you must write and submit your own individually prepared assignment. In other words, you must submit your own ideas in your

8

own coding. It is unacceptable to submit copied (a form of plagiarism) assignments on the basis that you have worked together. None of these assignments will be marked. Furthermore, you may be penalised or subjected to disciplinary proceedings by the university. Both Assignment 1 and 2 are compulsory. CALCULATION OF YOUR YEAR MARK, EXAMINATION MARK AND FINAL MARK

CALCULATION OF YOUR YEAR MARK

Year mark = (Assignment 1 x 50%) + (Assignment 2 x 50%) For general information and requirements as far as assignments are concerned, see the brochure, my Studies @ Unisa. HOW THE EXAMINATION SYSTEM WORKS

For general information on and requirements for the examinations, see the brochure, my Studies @ Unisa. EXAMINATION ADMISSION

You must submit Assignment 1 on or before the applicable due date to gain admission to the examination. If you do not meet this requirement, you will not be granted examination admission for this module. The marks you receive for the two assignments contribute to your year mark, which counts 20% towards the final mark. The year mark constitutes 20% and the examination mark 80% of the final mark. Final mark = Year mark x 0.2 + Examination mark x 0.8 You must obtain a final mark of 50% or more in order to pass this module. 8.2 General assignment numbers

Assignments are numbered consecutively per module, starting from 01. You should submit assignment 01 first.

8.2.1 Unique assignment numbers

Semester 01 assignments

Assignment number Unique number 01 591695 02 591743

Semester 02 assignments

Assignment number Unique number 01 592036 02 592514

INF3707/101

9

8.2.2 Due dates for assignments

In 2015, this module is offered in the form of a semester module. This means that if you are registered for the first semester you are supposed to do the assignments for the first semester and submit them before the respective due dates. If you are registered for the second semester, you are supposed to do the second semester assignments and submit them before the respective due dates.

First Semester Assignment Submission

Assignment number Due date 01 2015-03-10 02 2015-04- 10

Second Semester Assignment Submission

8.3 Submission of assignments

Steps to follow when submitting Assignment 2 online: - Complete the assignment. Save it as indicated in the assignment. - Logon to myUnisa using your student number and password. - Click on "Assignments" in the left-hand menu. - Select this module: INF3707 - Select the assignment number: 2 - Click on submit.

8.4 Assignments

Semester 1 Total marks Due date Assignment 1 10 2015-03-10

Unique number: 591695

Note that this is a COMPULSORY assignment! If you do not complete this assignment and submit it by the due date, you will not gain examination entry!

1. Indicate which of the following queries will list the countries that are in the

region_id = 1 or 4. A. select * from countries where region_id = 4 or region_id = 1;

B. select * from countries where region_id in (1,4); C. select region_id from countries; D. select ‘1’ and ‘4’ from products;

1. A is correct. 2. Both A and B are correct. 3. C is correct.

Assignment number Due date 01 2015-08-05 02 2015-09-21

10

4. D is correct. 2. Indicate which of the following SQL will display the years of employment for each

employee. 1. select last_name, round(years_between(sysdate,hire_date))

YEARS_WORKED from employees order by YEARS_WORKED;

2. select last_name, round(months_between(sysdate,hire_date)/12) YEARS_WORKED from employees order by months_between;

3. select last_name, round((sysdate-hire_date)/12) YEARS_WORKED from employees order by YEARS_WORKED;

4. select last_name, round(months_between(sysdate,hire_date)/12) YEARS_WORKED from employees order by YEARS_WORKED;

3. Indicate which of the following SQL will display .25 as the commission

percentage for an employee if the commission percentage is NULL. 1. select last_name, commission_pct COMM from employees

order by last_name; 2. select last_name, coalesce(commission_pct,.25) COMM from employees

order by last_name; 3. select last_name, commission_pct COMM from employees

where commission_pct is NULL order by last_name;

4. select last_name, commission_pct COMM from employees where commission_pct <> NULL order by last_name;

4. True or False

The following SQL displays for each department the average and maximum salary. select department_id DEPT, avg(salary) AVERAGE, max(salary) MAXIMUM from employees group by deparment_id order by department_id;

1. True 2. False

5. True or False

The following SQL will display the dates in a format that spells out the ordinal number. select last_name, to_char(hire_date, 'fmDd "of" Month YYYY') HIREDATE from employees;

1. True 2. False

INF3707/101

11

6. Indicate which of the following SQL statements will return an error message. 1. select job_id, count(employee_id) TOTAL

from employees group by job_id order by TOTAL desc;

2. select manager_id,count(manager_id) NUM_OF_EMP_PER_MNG from employees group by manager_id order by NUM_OF_EMP_PER_MNG desc;

3. select employee_id,max(salary) from employees group by salary;

4. select count(manager_id), count(department_id) from employees; 7. Indicate which of the following SQL will display the department numbers and

average salaries for those departments with a maximum salary that is greater than 10 000.

1. select department_id, round(avg(salary)) AVG_SALARY from employees group by department_id having salary > 1000;

2. select department_id, round(avg(salary)) AVG_SALARY from employees group by department_id where max(salary) > 10000;

3. select department_id, round(avg(salary)) AVG_SALARY from employees group by department_id having AVG_SALARY > 10000;

4. select department_id, round(avg(salary)) AVG_SALARY from employees group by department_id having max(salary) > 10000;

8. Indicate which of the following SQL will correctly display the department each

employee is allocated to. If the employee is not allocated to a department then that employee must be displayed as well.

1. select e.employee_id, d.department_id from employees e, departments d;

2. select e.employee_id, d.department_id from employees e, departments d where e.department_id = d.department_id;

3. select e.last_name, d.department_name from employees e RIGHT OUTER JOIN departments d on (d.department_id = e.department_id);

4. select e.last_name, d.department_name from employees e FULL OUTER JOIN departments d on (d.department_id = e.department_id);

5. select e.last_name, d.department_name from employees e LEFT OUTER JOIN departments d

12

on (d.department_id = e.department_id);

9. True or False A join between tables can happen on those columns that have the same names only in both tables.

1. True 2. False 10. True or False

A primary key in Oracle / SQL is a type of column that serves as a unique identifier for each row within a table.

1. True 2. False

Semester 1 Total marks Due date Unique Number Assignment 2 100 2015-04- 10 591743

Note:

• This assignment is compulsory • Include the unique number in your assignment • No handwritten assignments will be accepted. • All assignments should be submitted online as a PDF document. • Remember to include your student number in the heading of each page.

Number the pages. • The advantage of submitting the assignment online is that you will have proof

that it is received at UNISA. Lesson 2 1. The HR department wants to run reports based on a manager. Create a query that prompts the userfor a manager ID and generates the employee ID, last name, salary, and department for that manager’s employees. The HR department wants the ability to sort the report on a selected column. You can test the data with the following values:

manager ID = 103, sorted by employee last name manager ID = 201, sorted by salary manager ID = 124, sorted by employee ID (4)

Lesson 4 2. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50, 80, and 90, giving each column an appropriate heading. (7) Lesson 5 3. The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto. (6) Lesson 6 4. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a u. Place your SQL statement in a text file named lab_06_04.sql. Run your query. (6) Lesson 8

INF3707/101

13

5. Run the statements below to build the MY_EMPLOYEE table to be used for the lab. CREATE TABLE my_employee

(id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL, last_name VARCHAR2(25), first_name VARCHAR2(25), userid VARCHAR2(8), salary NUMBER(9,2));

5.1. Describe the structure of the MY_EMPLOYEE table to identify the column names. (1) 5.2. Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the columns in the INSERT clause. (3)

ID LAST_NAME FIRST_NAME USERID SALARY 1 Patel Ralph rpatel 895 2 Dancs Betty bdancs 860 3 Biri Ben bbiri 1100 4 Newman Chad cnewman 750 5 Ropeburn Audrey aropebur 1550

5.3 . Change the salary to $1,000 for all employees with a salary less than $900. (3) 5.4 . Verify your changes to the table. (1) Lesson 10 6. Department 50 needs access to its employee data. Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50. They have requested that you label the view columns EMPNO, EMPLOYEE, and DEPTNO. For security purposes, do not allow an employee to be reassigned to another department through the view. (8) Fundamentals 11 Lesson 1 7. Query the ALL_TABLES data dictionary view to see information about all the tables that you can access. Exclude tables that you own. (5) Lesson 2 8.1 Create the EMPLOYEES2 table based on the structure of the EMPLOYEES table. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns. Name the columns in your new table ID, FIRST_NAME, LAST_NAME, SALARY, and DEPT_ID, respectively. (4) 8.2. Drop the FIRST_NAME column from the EMPLOYEES2 table. Confirm your modification by checking the description of the table. (6) 8.3. In the EMPLOYEES2 table, mark the DEPT_ID column as UNUSED. Confirm your modification by checking the description of the table. (4) Lesson 4 9. Using GROUPING SETS, write a query to display the following groupings:

- department_id, manager_id, job_id - department_id, job_id - manager_id, job_id

14

The query should calculate the sum of the salaries for each of these groups. (7) Lesson 5 10. Create a query to print the last names and the number of years of service for each

employee. If the employee has been employed five or more years, then print 5 years of service. If the employee has been employed 10 or more years, then print 10 years of service. If the employee has been employed 15 or more years, then print 15 years of service. If none of these conditions match, then print maybe next year! Sort the results by the HIRE_DATE column. Use the EMPLOYEES table. Hint: Use CASE expressions and TO_YMINTERVAL. (10)

Lesson 6 11. Display the last name, department name, and salary of any employee whose salary and commission match the salary and commission of any employee located in location ID1700. (10) Lesson 7 12. Produce a company organization chart that shows the management hierarchy. Start with the person at the top level, exclude all people with a job ID of IT_PROG, and exclude De Haan and those employees who report to De Haan. (10) Lesson 8 13. Write a query to search the EMPLOYEES table for all employees whose first names start with “Ne” or “Na.” (5)

INF3707/101

15

Semester 2 Total marks Due date Assignment 1 10 2015-08-05

Unique number: 592036

Note that this is a COMPULSORY assignment! If you do not complete this assignment and submit it by the due date, you will not gain examination entry!

Multiple choice questions: Identify the choice that best completes the statement or answers

the question. Mark the correct option IN PENCIL on the mark reading sheet provided.

1. Examine the structure of the Employees and Jobs tables. Display the names of all employees whose salaries belong to SA_MAN. Which SQL statements give the required output?

1. SELECT first_name, last_name FROM employees JOIN jobs USING (salary BETWEEN min_salary AND max_salary) AND job_id = ‘SA_MAN’;

2. SELECT first_name, last_name FROM employees e JOIN jobs j ON (salary BETWEEN min_salary AND max_salary AND job_id =

'SA_MAN'); 3. SELECT distinct e.first_name, e.last_name, e.job_id, e.salary FROM employees e JOIN jobs j ON (e.salary BETWEEN j.min_salary AND j.max_salary) and e.job_id =

'SA_MAN'; 4. SELECT first_name, last_name FROM employees e JOIN job j WHERE e.salary BETWEEN j.min_salary AND j.max_salary AND j.job_id =

'SA_MAN';

2. Evaluate the following SQL statement: SELECT TO_CHAR(1230,'00,999.99') FROM DUAL; What would be the outcome?

1. 1,230 2. 01,230 3. 1,230.00 4. 01,230.00

3. True or False A primary key in Oracle / SQL is a type of column that serves as a unique identifier for each row within a table.

1. True 2. False

4. Examine the employees table. Write a query that displays the annual commission amount payable to each employee. Which SQL statements ensure that a value is displayed in the calculated column for all the employees?

1. SELECT last_name, NVL(12 * salary * commission_pct) FROM employees; 2. SELECT last_name, NVL2(12 * salary * commission_pct,0)

16

FROM employees; 3. SELECT last_name, COALESCE(12 * salary * commission_pct,0) FROM employees; 4. SELECT last_name, DECODE(12 * salary * commission_pct,0,0) FROM employees;

5. Examine the employee table. Write a query to find the highest average salary being paid in any department. Which SQL statement gives the required result?

1. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; 2. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id

HAVING AVG(salary) >= MAX(salary); 3. SELECT department_id, MAX(AVG(salary)) FROM employees GROUP BY

department_id; 4. SELECT department_id, MAX(AVG(salary)) FROM employees GROUP BY

department_id, salary;

6. Examine the employees table. Write SQL statements to generate a report that fulfils the following requirements:

1. Displays employees' names and commission amounts 2. Excludes employees who do not have a commission 3. Displays a zero for employees whose SALARY does not have a value

You issue the following SQL statement: SELECT first_name, NVL(salary * commission_pct, 0) FROM employees WHERE commission_pct <> NULL;

What is the outcome? 1. It generates an error 2. It executes successfully but displays no result 3. It executes successfully but displays results that fulfil only requirements 1and

3 4. It executes successfully and displays results that fulfil all the requirements

7. Which statement is true regarding single-row functions? 1. They cannot be nested 2. They can accept only one argument 3. They act on each row returned and return only one result per row 4. They act on each row returned and can return multiple results per row

8. Indicate which of the following queries will list the countries that are in the region_id = 1

or 4. a. select * from countries where region_id = 4 or region_id = 1; b. select * from countries where region_id in (1,4); c. select region_id from countries; d. select ‘1’ and ‘4’ from products;

1. A is correct 2. Both A and B are correct 3. C is correct 4. D is correct

INF3707/101

17

9. True or False The following SQL displays for each department the average and maximum salary. select department_id DEPT, avg(salary) AVERAGE, max(salary) MAXIMUM from employees group by department_id order by department_id;

1. True 2. False

10. Indicate which of the following SQL statements will return an error message. 1. select job_id, count(employee_id) TOTAL from employees group by job_id order by TOTAL desc; 2. select manager_id,count(manager_id) NUM_OF_EMP_PER_MNG from employees group by manager_id order by NUM_OF_EMP_PER_MNG desc; 3. select employee_id,max(salary) from employees group by salary; 4. select count(manager_id), count(department_id) from employees;

Semester 2 Total marks Due date Unique Number

Assignment 2 100 2015-09-21 592514

Note:

• This is a compulsory assignment. • No handwritten assignments will be accepted. • All assignments should be submitted online as a PDF document. • Remember to include your student number in the heading of each page.

Number the pages. • The advantage of submitting the assignment online is that you will have proof

at it is received at UNISA. lesson 3 1. Create a query that displays the first eight characters of the employees’ last names and indicates the amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES. (4) Lesson 5 2. The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto. (6) Lesson 6 3. The manager requested you to provide a list of all the employees who earns less than the average salary of all the accountants and reports to Nancy Greenberg.Note: Keep in mind there are two types of jobs that are accountants. (6)

18

Lesson 7 4.1 The job history table keeps record of the employees that filled other positions (job_id’s) within the organization. Use one of the set operators to list the employee information (name and last name) for all those employees that has never filled any other position within the organization The report must list the employee_id and employee last name and first name. Order the list by the employee_id (4)

4.1 Use one of the set operators to list the names of all the employees that are in the Shipping Department and reports to Steven King. Sort the report alphabetically according to the last name of the employee. (4) Lesson 8 5.1 Provide the SQL script to create the following table: (6)

Table name: LOGIN_DETAILS ID Number(4) NOT NULL LOGIN_NAME Varchar2(25) PASSWORD_REMINDER Varchar2(100), PASSWORD Varchar2(30)

5.2 Provide the SQL that will create dynamic reusable SQL to load rows into the LOGIN_DETAILS table. (3)

5.3 Provide the SQL that will update the table LOGIN_DETAILS as follow: All the passwords must be changed to uppercase and all the

A’s must be replaced by 4’s O’s must be replaced by 0’s S’s must be replaced by 5’s I’s must be replaced by 1’s B’s must be replaced by 8’s

Example: DAYS will become D4Y5. (4) Hint: used the TRANSLATE command. Lesson 9 6. Provide the SQL that will create the following table: EMP_JOBCARD

6.1 Provide the SQLto create a sequence CARD_NUM_SEQ. This sequence will be used to insert data into the EMP_JOBCARD table. The sequence should start at 100 and have a maximum value of 10000. Have your sequence increment by 2. (3)

INF3707/101

19

6.2 Provide the SQL to dynamically insert values into the EMP_JOBCARD table. Remember to use the sequence you have created in the previous question. (5) Lesson 11 7. Provide the SQL that will prompt the user for the name of the table. This SQL will then provide a report that will list the column names, data types, data types lengths, data precision, indicate whether NULLS are allowed for this specific table. Provide suitable aliases / headings for the columns. Keep in mind that the table name must be in capital letters – use a suitable converter to convert the text that the user enter into capital letters. (5) Fundamentals 11 8 Login as the user HR. 8.1 Provide the SQL that will create the table EMPDATA based on the structure of the EMPLOYEES and DEPARTMENTS tables. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY and DEPARTMENT_NAME columns. Name the columns in the new table as ID, NAME (concatenate the FIRST_NAME and LAST_NAME), SALARY, DEPTNAME. (5) 8.2 Provide the SQL that will alter the table EMPDATA and add a PRIMARY KEY on the ID column. (2) 8.3 Provide the SQL that will display the object names and types from the USER_OBJECTS data dictionary view for the EMPLOYEES and EMPDATA tables. (4) 8.4 Provide the SQL that will drop EMPDATA in such a way that it cannot be restored. (2) Lesson 4 9 Provide the SQL that will request the user to enter the Manager ID and display the following report for this manager:

o Manager ID o Job and Total Salaries for every job for employees who report to this manager. o Total salaries for this manager o Cross-tabulation values to display the total salary for every job. (7)

Lesson 5 10 Create a query to retrieve last names from the EMPLOYEES table and calculate the review status. If the year hired was 1998, then display Needs Review for the review status; otherwise, display not this year! Name the review status column Review. Sort the results by the HIRE_DATE column. Hint: Use a CASE expression with the EXTRACT function to calculate the review status. (8) Lesson 6 11Create a query to display the last name, hire date, and salary for all employees who have the same salary and commission as Kochhar. Note: Do not display Kochhar in the result set. (10) Lesson 7 12. Produce a company organization chart that shows the management hierarchy. Start with the person at the top level, exclude all people with a job ID of IT_PROG, and exclude De Haan and those employees who report to De Haan. (8)

20

Lesson 8 13. Create a query that displays “St” replaced by “Street” in the STREET_ADDRESS column of the LOCATIONS table. Be careful that you do not affect any rows that already have “Street” in them. Display only those rows, which are affected. (4) 9 OTHER ASSESSMENT METHODS None

10 EXAMINATION Use your my Studies @ Unisa brochure for general examination guidelines and examination preparation guidelines. In 2015, this module is offered in the form of a semester module. This means, if you are registered for the first semester, you will write the examination in May/June 2015. If you are registered for the second semester, you will write the examination in October/November 2015. EXAMINATION PAPER

The examination consists of one two-hour examination paper. The examination paper will be set in English only. Examination guidelines will be made available to all students before the examination. You will be tested on all the work, not only the work covered in Assignments 1 and 2. The examination guidelines will be available on myUnisa. 11 FREQUENTLY ASKED QUESTIONS The my Studies @ Unisa brochure contains an A-Z guide of the most relevant study information. Throughout the manuals you will be referred to the following environments: SQL, iSQL*Plus. For the purpose of this course, we will not use these environments; only the Oracle XE environment will be used. It is, however, necessary to include the SQL and iSQL*Plus environments in the manuals, should you wish to complete the Oracle Certified Examinations. 12 SOURCES CONSULTED This course is based on official Oracle university study guide.

13 CONCLUSION You are required to make use of the available electronic resources that are accessible through myUnisa in their learning. The tools on myUnisa allow you to submit assignments, participate in discussion forums and download study material. You are reminded that courses that have a component of programming can only be mastered through practice.

INF3707/101

21

14 ADDENDUM

Getting Started with Oracle Oracle 10g XE and this course Oracle 10g XE is a smaller version of the full installation of Oracle 10g, yet it is very powerful and will allow you to experience not only SQL, but also the Oracle environment. It is not necessary for you to be connected to the internet, however, the Oracle XE will run in a browser environment. I have tested it in Internet Explorer v7 & v8 as well as Mozilla FireFox v3. Bundled with Oracle XE is a full set of online documentation that you can use. This course is not about teaching you the GUI of Oracle 10g XE, but since the software is bundled with all the necessary documentation, you should be able to develop full-fletched programs in no time. We will focus mainly on the SQL part of Oracle, you are, however, welcome to explore and investigate the Oracle XE environment and you will soon notice that it is very easy to create SQL scripts, using the Query Scripts editor. However, you must be able to create those scripts yourself from scratch as well. Installing the Oracle 10g XE Software I have tested the installation on XP and Windows 7. If you are running LINUX then you can download the LINUX version from www.oracle.com. Select DOWNLOADS. There are some forms and stuff that you need to complete, but the download and installing should not be a problem. I have tested it on Apple Mac with an Intel Processor in the Windows VE. If you have installed and tested it on other operating systems, add your comments on the WIKI on myUnisa. Open the CD that accompanies your prescribed manual. Double click on the file: OracleXEUniv.exe. If you are requested to allow this program to make the necessary changes to your PC, click yes. The Install Shield Wizard will guide you through the program setup process. Step 1:

Click next

STEP 2

22

Select the “I accept the terms in the license agreement”. Select next

STEP 3 Keep the Destination Folder as indicated by the Installer.

Click NEXT STEP 4

VERY IMPORTANT: Enter the following passwords – do not create your own passwords since you will not be able to run and install the necessary SQL scripts: MANAGER1 Confirm the password and click on next.

INF3707/101

23

STEP 5

Click on install.

STEP 6

Sit back and enjoy a cup of coffee or tea while Oracle Database 10g XE installs. (It should not take too long to complete the installation)

STEP 7

Click on finish. Oracle XE is now installed on your PC. The best is to create a shortcut on your desktop. 14.1

24

ORACLE XE HOMEPAGE The first time that you open the Database Homepage it will open with the following database login screen. (Note the URL. Copy or write it down somewhere in case something goes wrong and you need this URL again to connect to the database. ) Hint to copy the URL: create a shortcut on your desktop and copy the URL in the field in which you are requested to type the location of the item.

Login for the username: SYSTEM Password: MANAGER1 (Note: Oracle is not case sensitive) Click on login.

SYSTEM is the system manager. You will need to login as SYSTEM to gain access to the database as the “administrator”.

The MAIN screen:

Unlocking the user HR We need to unlock an additional user for the purpose of this course. Check that you are still logged in as the user SYSTEM. Click on the option Administration Database Users. Click on the icon for the user HR.

The user SYSTEM is logged in.

INF3707/101

25

Notice the yellow lock that indicates that the user is created but it is locked.

As the user SYSTEM you have the privileges to update the user’s profile. Click on the icon and enter the following information into the fields:

For the user password, type in PASSWORD. (Off course you can use something else, but for the purpose of this module, I suggest that we all use the same password.) Change the status from Locked to Unlocked. Do not alter or change the User Privileges.

Now you are ready to login as the user. First you need to logout as SYSTEM.

Click on Logout.

PASSWORD

26

Login as HR:

©2015 Unisa

password