DBS311 Lab 1

Review

1. Create the following tables in your Oracle account using the lab handout Create Patient and Insurance:

PATIENT

INSURANCE

2. Be prepared to produce the following output for your professor in the lab: (1 Mark)

3. Find the Oracle equivalent for the following DB2 statement: (1 Mark)

4. Adjust your tables so you can demonstrate enforcing referential integrity. (2 marks)
5. Find the Oracle equivalent for the following DB2 statement: (1 Mark)

If the current date was Sept 10,2022 this would produce:

Oracle uses ToChar, SYSDATE, ‘fmMonth, fmdd, ‘YYYY’ and dual

6. Make a copy of your patient table with data in PATIENT3, add a birthdate column with appropriate data and put back John Howard.

The birthdates data shows and the John Howard record now uses a PINSURNUM of 444

7. Find the Oracle equivalent for the following DB2 statement discussed in class: (2 Marks)

It can look like this – but maybe you can get closer to the DB2 example

(note the age is slightly different with the two result sets)

Some character functions:

8. Show the insurance company names that end in E (1 mark)

9. Provide this output using PATIENT3 AND INSURANCE (string handling on patient name and numeric phone) (1 mark)

10 Demonstrate the use of a variable in a select statement where clause.  (1 mark)