johnhonan

-- This shows how to create a procedure
-- which uses EXECUTE IMMEDIATE to first create a timestamped backup of a table
-- before deleting all the rows from that table

CREATE TABLE hr.my_test_table2 AS SELECT * FROM hr.EMPLOYEES;

CREATE OR REPLACE PROCEDURE hr.del_rows
(p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER)
IS
BEGIN

johnhonan

SELECT salary_group, count(*) FROM
    (SELECT
        employee_id,
        last_name,
        CASE
              WHEN salary <= 3000 THEN 'poor'
              WHEN salary >3000 AND salary <= 11000 THEN 'okay'
              WHEN salary > 11000 THEN 'rich'
              ELSE 'Theres a problem'
        END AS salary_group

johnhonan

SELECT DISTINCT
    job_history.employee_id, 
    employees.first_name,
    x.max_start,
    y.min_start
FROM hr.job_history
JOIN hr.employees
    ON job_history.employee_id = employees.employee_id
JOIN
    (SELECT employee_id, max(start_date) as max_start

johnhonan

SELECT DISTINCT
    job_history.employee_id, 
    employees.first_name,
    x.max_start,
    y.min_start
FROM hr.job_history
JOIN hr.employees
    ON job_history.employee_id = employees.employee_id
JOIN
    (SELECT employee_id, max(start_date) as max_start

johnhonan

 CREATE TABLE "HR"."PRODUCTS" 
   (	"PRODUCT_ID" NUMBER, 
	"PRODUCT_DESC" VARCHAR2(30 BYTE)
   );

Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (10,'Oranges');
Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (11,'Apples');
Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (15,'Pineapples');
Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (34,'bananas');
Insert into HR.PRODUCTS (PRODUCT_ID,PRODUCT_DESC) values (10,'Coconuts');

johnhonan

-- 1) Create a table in the hr database called 'log'
-- with two fields; log_msg (text) and time_of_msg (datetime)

-- 2) Modify the procedure below to insert a record into
-- the log table;
CREATE OR REPLACE PROCEDURE hr.log_details (msg IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- run a commit here regardless of any other transaction state
BEGIN
    INSERT INTO hr.log(msg_id,log_msg,time_of_msg)

johnhonan

class Calculator(object): # define a calculator object
    def add(self, x): # define addition function
    # use comprehebnsion to check that all 
    # elements in a list are numeric
        number_types = (int, long, float, complex) # accepted number types
        if all(isinstance(item, number_types) for item in x):
            return reduce(lambda a,b: a+b, x)
        else:
            raise ValueError # if one or both values are not accepted number types return a Value Error
 

johnhonan

import unittest
from calculator import Calculator

# test the calculator functionality
class TestCalculator(unittest.TestCase):
    def setUp(self):
        self.calc = Calculator()

    # this tests the add functionality
    # 2 + 2 = 4

johnhonan

class Calculator(object): # define a calculator object
    def add(self, x, y): # define addition function
        number_types = (int, long, float, complex) # accepted number types

        if isinstance(x, number_types) and isinstance(y, number_types): # if both values are accepted number types
            return x + y # return the sum of the values
        else:
            raise ValueError # if one or both values are not accepted number types return a Value Error

    def subtract(self, x, y):

johnhonan

-- This is a package specification
CREATE OR REPLACE PACKAGE hr.job_pack
AUTHID CURRENT_USER
IS -- Declare package components.
    -- Author: [email protected]
    -- Date: 28-Nov-2017
    -- Procedures to add/update and delete
    -- into the jobs table
    PROCEDURE ADD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2);
    PROCEDURE UPD_JOB (v_job_id VARCHAR2,v_job_title VARCHAR2);