In this section you will begin the process of working with the Oracle Chinook database Task – Open the Chinook_Oracle.sql file and execute the scripts within.
In this section you will be performing various queries against the Oracle Chinook database.
Task – Select all records from the Employee table. Task – Select all records from the Employee table where last name is King. Task – Select all records from the Employee table where first name is Andrew and REPORTSTO is NULL.
Task – Select all albums in Album table and sort result set in descending order by title. Task – Select first name from Customer and sort result set in ascending order by city
Task – Insert two new records into Genre table Task – Insert two new records into Employee table Task – Insert two new records into Customer table
Task – Update Aaron Mitchell in Customer table to Robert Walter Task – Update name of artist in the Artist table “Creedence Clearwater Revival” to “CCR”
Task – Select all invoices with a billing address like “T%”
Task – Select all invoices that have a total between 15 and 50 Task – Select all employees hired between 1st of June 2003 and 1st of March 2004
Task – Delete a record in Customer table where the name is Robert Walter (There may be constraints that rely on this, find out how to resolve them).
In this section you will be using the Oracle system functions, as well as your own functions, to perform various actions against the database
Task – Create a function that returns the current time. Task – create a function that returns the length of a mediatype from the mediatype table
Task – Create a function that returns the average total of all invoices Task – Create a function that returns the most expensive track
Task – Create a function that returns the average price of invoiceline items in the invoiceline table
Task – Create a function that returns all employees who are born after 1968.
In this section you will be creating and executing stored procedures. You will be creating various types of stored procedures that take input and output parameters.
Task – Create a stored procedure that selects the first and last names of all the employees.
Task – Create a stored procedure that updates the personal information of an employee. Task – Create a stored procedure that returns the managers of an employee.
Task – Create a stored procedure that returns the name and company of a customer.
In this section you will be working with transactions. Transactions are usually nested within a stored procedure. Task – Create a transaction that given a invoiceId will delete that invoice (There may be constraints that rely on this, find out how to resolve them). Task – Create a transaction nested within a stored procedure that inserts a new record in the Customer table
In this section you will create various kinds of triggers that work when certain DML statements are executed on a table.
Task - Create an after insert trigger on the employee table fired after a new record is inserted into the table. Task – Create an after update trigger on the album table that fires after a row is inserted in the table Task – Create an after delete trigger on the customer table that fires after a row is deleted from the table.
In this section you will be working with combining various tables through the use of joins. You will work with outer, inner, right, left, cross, and self joins.
Task – Create an inner join that joins customers and orders and specifies the name of the customer and the invoiceId.
Task – Create an outer join that joins the customer and invoice table, specifying the CustomerId, firstname, lastname, invoiceId, and total.
Task – Create a right join that joins album and artist specifying artist name and title.
Task – Create a cross join that joins album and artist and sorts by artist name in ascending order.
Task – Perform a self-join on the employee table, joining on the reportsto column.
In this section you will be creating backup files of your database. After you create the backup file you will also restore the database. Task – Create a .bak file for the Chinook database.