Pblem 1
Goal: Create a Referential Integrity constraint
Note: Each student may choose either the Oracle or Microsoft Access databases.
Using the class example database (SQLFUN):
Create a Referential Integrity constraint between the valid_phone_numbers table and the L_employees table. (This validates the phone_number value whenever a new row is added to the L_employees table.)
Add one more value to this table: 4707.
Create a new table, valid_phone_numbers, containing the phone numbers for all the employees. (Get this list of valid values from the phone_number column of the L_employees table.)
Submit your homework by copying and pasting the SQL statements and contents to a text file (.txt).
The necessary files to complete your assignments are labeled as reference materials located in DocSharing. To complete your assignment, you have the choice between using either Oracle (utilizing the two sets of SQL scripts) or Microsoft Access (utilizing the two sets of databases). Choose which ever system you feel more comfortable using, but choose only Oracle or Microsoft Access.
Pblem 2
Goal: Use the date functions. Construct and use a table of constants.
Note: Each student may choose either the Oracle or Microsoft Access databases.
Find the number of days between December 21, 2020 (winter solstice), and March 21, 2021 (spring equinox).
Put both dates in a table of constants, so they both have a datatype of date.
Starting points:
Oracle SQL
drop table ex0904a;
create table ex0904a
(beg_date date,
end_date date);
insert into ex0904a
values ('21-dec-2020', '21-mar-2021');
commit;
select end_date - beg_date as number_of_days from ex0904a;
drop table ex0904a;
create table ex0904a
(beg_date datetime,
end_date datetime);
insert into ex0904a
values (#21-dec-2020#, #21-mar-2021#);
select end_date - beg_date as number_of_days from ex0904a;
Submit your homework by copying and pasting the SQL statements and results to a text file (.txt).
The necessary files to complete your assignments are labeled as reference materials located in DocSharing. To complete your assignment, you have the choice between using either Oracle (utilizing the two sets of SQL scripts) or Microsoft Access (utilizing the two sets of databases). Choose which ever system you feel more comfortable using, but choose only Oracle or Microsoft Access.
Pblem 3
Goal: Use the MIN function.
Note: Each student may choose either the Oracle or Microsoft Access databases.
Using the class example database (SQLFUN):
Find the price of the least expensive food in the L_foods table.
Find all the foods with that minimum price.
Submit your homework by copying and pasting the SQL statements and results to a text file (.txt).
The necessary files to complete your assignments are labeled as reference materials located in DocSharing. To complete your assignment, you have the choice between using either Oracle (utilizing the two sets of SQL scripts) or Microsoft Access (utilizing the two sets of databases). Choose which ever system you feel more comfortable using, but choose only Oracle or Microsoft Access.
Pblem 4
Goal: Find rows that are not matched. (They are dropped from an inner join.)
Note: Each student may choose either the Oracle or Microsoft Access databases.
Using the class example database (SQLFUN):
Are there any employees who are not attending any of the lunches? If so, who are they?
List the Employee ID, first name, and last name of these employees.
Sort them on the employee_id.
Submit your homework by copying and pasting the SQL statements and results to a text file (.txt).
The necessary files to complete your assignments are labeled as reference materials located in DocSharing. To complete your assignment, you have the choice between using either Oracle (utilizing the two sets of SQL scripts) or Microsoft Access (utilizing the two sets of databases). Choose which ever system you feel more comfortable using, but choose only Oracle or Microsoft Access.
