-- ==============================================================================
-- PART 1
-- ==============================================================================

-- @BEGIN: PART_1_A
-- a) Create Sailors table with the following fields and describe the table (sid: integer, sname:  string, rating: integer, age: real) (Note: Underlined attribute represents the primary key)
CREATE TABLE Sailors (
    sid INT PRIMARY KEY,
    sname VARCHAR2(20),
    rating INT,
    age NUMBER(3,1)
);
DESCRIBE Sailors;
-- @END: PART_1_A

-- @BEGIN: PART_1_B
-- b) Insert records in to the Sailors table
INSERT INTO Sailors VALUES (22, 'Dustin', 7, 45.0);
INSERT INTO Sailors VALUES (29, 'Brutus', 1, 33.0);
INSERT INTO Sailors VALUES (31, 'Lubber', 8, 55.5);
INSERT INTO Sailors VALUES (32, 'Andy', 8, 25.5);
INSERT INTO Sailors VALUES (58, 'Rusty', 10, 35.0);
INSERT INTO Sailors VALUES (64, 'Horatio', 7, 35.0);
INSERT INTO Sailors VALUES (71, 'Zorba', 10, 16.0);
INSERT INTO Sailors VALUES (74, 'Horatio', 9, 35.0);
INSERT INTO Sailors VALUES (85, 'Art', 3, 25.5);
INSERT INTO Sailors VALUES (95, 'Bob', 3, 63.5);
COMMIT;
-- @END: PART_1_B

-- @BEGIN: PART_1_C
-- c) Create Reserves table with the following fields and describe the table (sid: integer, bid: integer, day: date)
CREATE TABLE Reserves (
    sid INT,
    bid INT,
    day DATE,
    PRIMARY KEY(sid, bid, day) -- Using composite primary key for realism
);
DESCRIBE Reserves;
-- @END: PART_1_C

-- @BEGIN: PART_1_D
-- d) Insert records in to the Reserves table
INSERT INTO Reserves VALUES (22, 101, TO_DATE('10-OCT-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (22, 102, TO_DATE('10-OCT-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (22, 103, TO_DATE('10-AUG-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (22, 104, TO_DATE('10-JUL-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (31, 102, TO_DATE('11-OCT-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (31, 103, TO_DATE('11-JUN-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (31, 104, TO_DATE('11-DEC-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (64, 101, TO_DATE('09-MAY-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (64, 102, TO_DATE('09-AUG-1998', 'DD-MON-YYYY'));
INSERT INTO Reserves VALUES (74, 103, TO_DATE('09-AUG-1998', 'DD-MON-YYYY'));
COMMIT;
-- @END: PART_1_D

-- @BEGIN: PART_1_E
-- e) Find all sailors whose first letter and third letter in the names are ‘A’ and ‘d’
SELECT * FROM Sailors WHERE sname LIKE 'A_d%';
-- @END: PART_1_E

-- @BEGIN: PART_1_F
-- f) Find sailors whose rating is better than some sailor called Horatio (using ANY)
SELECT * FROM Sailors 
WHERE rating > ANY(SELECT rating FROM Sailors WHERE sname = 'Horatio');
-- @END: PART_1_F

-- @BEGIN: PART_1_G
-- g) Find the number of reservations made for each boat
SELECT bid, count(bid) AS num_reservations 
FROM Reserves 
GROUP BY bid;
-- @END: PART_1_G

-- @BEGIN: PART_1_H
-- h) Give example which includes all Arithmetic functions
SELECT ABS(-4.55) AS abs_val, CEIL(4.55) AS ceil_val, FLOOR(4.55) AS floor_val, 
       MOD(5,4) AS mod_val, POWER(2,3) AS pow_val, SIGN(-10) AS sign_val, 
       SQRT(9) AS sqrt_val, ROUND(4.333,2) AS round_val, TRUNC(4.333,2) AS trunc_val 
FROM dual;
-- @END: PART_1_H

-- @BEGIN: PART_1_I
-- i) Write a PL/SQL program to find largest and smallest of three numbers
DECLARE
    a NUMBER := 10;
    b NUMBER := 5;
    c NUMBER := 15;
    large NUMBER;
    small NUMBER;
BEGIN
    -- Find Largest
    IF a > b AND a > c THEN large := a;
    ELSIF b > a AND b > c THEN large := b;
    ELSE large := c;
    END IF;
    
    -- Find Smallest
    IF a < b AND a < c THEN small := a;
    ELSIF b < a AND b < c THEN small := b;
    ELSE small := c;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Largest: ' || large);
    DBMS_OUTPUT.PUT_LINE('Smallest: ' || small);
END;
/
-- @END: PART_1_I

-- @BEGIN: PART_1_J
-- j) Create a trigger to display message after insert or update or delete record in a sailors table
CREATE OR REPLACE TRIGGER trg_sailors_after_dml
AFTER INSERT OR UPDATE OR DELETE ON Sailors
BEGIN
    DBMS_OUTPUT.PUT_LINE('Action performed on Sailors table.');
END;
/
-- @END: PART_1_J


-- ==============================================================================
-- PART 2
-- ==============================================================================

-- @BEGIN: PART_2_Q1
-- Find the names of sailors who have reserved boat 103 (using nested query)
SELECT sname FROM Sailors 
WHERE sid IN (SELECT sid FROM Reserves WHERE bid = 103);
-- @END: PART_2_Q1

-- @BEGIN: PART_2_Q2
-- Find the sailors with the highest rating
SELECT * FROM Sailors 
WHERE rating = (SELECT MAX(rating) FROM Sailors);
-- @END: PART_2_Q2

-- @BEGIN: PART_2_Q3
-- Find the number of boat reservations made by each sailor
SELECT s.sname, COUNT(*) AS num_reservations
FROM Sailors s, Reserves r 
WHERE s.sid = r.sid 
GROUP BY s.sname;
-- @END: PART_2_Q3

-- @BEGIN: PART_2_Q4
-- Give example which includes all General functions
SELECT sid, sname, 
       CAST(sid AS VARCHAR2(10)) AS cast_example, 
       NVL(NULL, 88) AS nvl_example, 
       NULLIF(sid, 22) AS nullif_example, 
       DECODE(rating, 10, 'Excellent', 'Other') AS decode_example 
FROM Sailors;
-- @END: PART_2_Q4

-- @BEGIN: PART_2_Q5
-- i) Write a PL/SQL program to perform the following: Insert <99,'John',9,25.6> into the Sailors table, Update sname 'John' to 'Joe' in the Sailors table, Delete tuple from the Sailors table whose sname is 'Joe'.
DECLARE
BEGIN
    INSERT INTO Sailors VALUES(99, 'John', 9, 25.6);
    DBMS_OUTPUT.PUT_LINE('Inserted John.');
    
    UPDATE Sailors SET sname = 'Joe' WHERE sname = 'John';
    DBMS_OUTPUT.PUT_LINE('Updated John to Joe.');
    
    DELETE FROM Sailors WHERE sname = 'Joe';
    DBMS_OUTPUT.PUT_LINE('Deleted Joe.');
END;
/
-- @END: PART_2_Q5

-- @BEGIN: PART_2_Q6
-- Explain Packages with PL/SQL program (Providing Sample Specification and Body)
CREATE OR REPLACE PACKAGE DEMO AS
    FUNCTION fact(n IN NUMBER) RETURN NUMBER;
END DEMO;
/

CREATE OR REPLACE PACKAGE BODY DEMO AS
    FUNCTION fact(n IN NUMBER) RETURN NUMBER IS
        f NUMBER := 1;
        i NUMBER := n;
    BEGIN
        WHILE i >= 1 LOOP
            f := f * i;
            i := i - 1;
        END LOOP;
        RETURN f;
    END fact;
END DEMO;
/
-- @END: PART_2_Q6


-- ==============================================================================
-- PART 3
-- ==============================================================================

-- @BEGIN: PART_3_Q1
-- Create Boats table with the following fields and describe the table (bid: integer, bname: string, color: string).        
CREATE TABLE Boats (
    bid INT PRIMARY KEY,
    bname VARCHAR2(15),
    color VARCHAR2(10)
);
DESCRIBE Boats;
-- @END: PART_3_Q1

-- @BEGIN: PART_3_Q2
-- Insert records in to the Boats table
INSERT INTO Boats VALUES (101, 'Interlake', 'blue');
INSERT INTO Boats VALUES (102, 'Interlake', 'red');
INSERT INTO Boats VALUES (103, 'Clipper', 'green');
INSERT INTO Boats VALUES (104, 'Marine', 'red');
COMMIT;
-- @END: PART_3_Q2

-- @BEGIN: PART_3_Q3
-- Find the number of boats in each color
SELECT color, COUNT(bid) AS num_boats 
FROM Boats 
GROUP BY color;
-- @END: PART_3_Q3

-- @BEGIN: PART_3_Q4
-- f) Find the days on which blue color boat is reserved
SELECT day FROM Reserves 
WHERE bid IN (SELECT bid FROM Boats WHERE color = 'blue');
-- @END: PART_3_Q4

-- @BEGIN: PART_3_Q5
-- g) Find the days on which boat 103 is reserved
SELECT day FROM Reserves WHERE bid = 103;
-- @END: PART_3_Q5

-- @BEGIN: PART_3_Q6
-- Give example which includes all String functions
SELECT ASCII('A') AS ascii_val, CHR(65) AS chr_val, 
       CONCAT('abc', 'def') AS concat_val, INSTR('abaabcab', 'ab', 1, 2) AS instr_val, 
       LENGTH('abcd') AS length_val, LOWER('ABCD') AS lower_val, 
       UPPER('abcd') AS upper_val, REPLACE('abcd', 'a', 'ef') AS replace_val,
       SUBSTR('abcdef', 2, 3) AS substr_val
FROM dual;
-- @END: PART_3_Q6

-- @BEGIN: PART_3_Q7
-- Write a PL/SQL program to check whether a number is palindrome or not?
DECLARE
    a NUMBER := 121;
    c NUMBER := a;
    r NUMBER := 0;
    s NUMBER := 0;
BEGIN
    WHILE c > 0 LOOP
        r := MOD(c, 10);
        s := s * 10 + r;
        c := FLOOR(c / 10);
    END LOOP;
    IF s = a THEN
        DBMS_OUTPUT.PUT_LINE(a || ' is palindrome');
    ELSE
        DBMS_OUTPUT.PUT_LINE(a || ' is not palindrome');
    END IF;
END;
/
-- @END: PART_3_Q7

-- @BEGIN: PART_3_Q8
-- Write a PL/SQL program to show handling of User-defined exception
DECLARE
    b NUMBER := 0;
    ex EXCEPTION;
BEGIN
    IF b = 0 THEN
        RAISE ex;
    END IF;
EXCEPTION
    WHEN ex THEN
        DBMS_OUTPUT.PUT_LINE('Custom Exception: Cannot divide by zero');
END;
/
-- @END: PART_3_Q8


-- ==============================================================================
-- PART 4
-- ==============================================================================

-- @BEGIN: PART_4_Q1
-- Create Sailors1 table with the following fields and describe the table    (sid: integer, sname: string, rating: integer, age: real)
CREATE TABLE Sailors1 (
    sid INT,
    sname VARCHAR2(20),
    rating INT,
    age NUMBER(3,1)
);
DESCRIBE Sailors1;
-- @END: PART_4_Q1

-- @BEGIN: PART_4_Q2
-- Insert tuples of Sailors table in to the Sailors1 table
INSERT INTO Sailors1 SELECT * FROM Sailors;
COMMIT;
-- @END: PART_4_Q2

-- @BEGIN: PART_4_Q3
-- Find all sailors from sailors and sailors1 table by using MINUS operator
-- (Will return no rows since data is identical, but query syntax is accurate)
SELECT * FROM Sailors
MINUS 
SELECT * FROM Sailors1;
-- @END: PART_4_Q3

-- @BEGIN: PART_4_Q4
-- f) Find the age of the youngest sailor who is eligible to vote (i.e., is at least 18 years old) for each rating level with at least two such sailors
SELECT rating, MIN(age) 
FROM Sailors 
WHERE age >= 18 
GROUP BY rating 
HAVING COUNT(rating) >= 2;
-- @END: PART_4_Q4

-- @BEGIN: PART_4_Q5
-- Find the name and age of the oldest sailor
SELECT sname, age FROM Sailors 
WHERE age = (SELECT MAX(age) FROM Sailors);
-- @END: PART_4_Q5

-- @BEGIN: PART_4_Q6
-- Give example which includes all Date and Time functions
SELECT ADD_MONTHS(SYSDATE, 5) AS add_months, 
       CURRENT_DATE,
       EXTRACT(YEAR FROM CURRENT_DATE) AS extract_year,
       MONTHS_BETWEEN(SYSDATE, SYSDATE - 365) AS m_between,
       NEXT_DAY(SYSDATE, 'MONDAY') AS n_day,
       LAST_DAY(SYSDATE) AS l_day
FROM dual;
-- @END: PART_4_Q6

-- @BEGIN: PART_4_Q7
-- Write a PL/SQL program to check whether a number is prime or not?
DECLARE
    a NUMBER := 7;
    c NUMBER := 0;
BEGIN
    FOR i IN 1..a LOOP
        IF MOD(a, i) = 0 THEN
            c := c + 1;
        END IF;
    END LOOP;
    
    IF c = 2 THEN
        DBMS_OUTPUT.PUT_LINE(a || ' is prime');
    ELSE
        DBMS_OUTPUT.PUT_LINE(a || ' is not prime');
    END IF;
END;
/
-- @END: PART_4_Q7

-- @BEGIN: PART_4_Q8
-- Create a trigger to display message before insert or update or delete record in a sailors table
CREATE OR REPLACE TRIGGER trg_sailors_before_dml
BEFORE INSERT OR UPDATE OR DELETE ON Sailors
BEGIN
    DBMS_OUTPUT.PUT_LINE('Preparing to perform action on Sailors table.');
END;
/
-- @END: PART_4_Q8


-- ==============================================================================
-- PART 5
-- ==============================================================================

-- @BEGIN: PART_5_Q1
-- Find all sailors from sailors and sailors1 table by using INTERSECT operator
SELECT * FROM Sailors 
INTERSECT 
SELECT * FROM Sailors1;
-- @END: PART_5_Q1

-- @BEGIN: PART_5_Q2
-- Find the average age of sailors for each rating level that has at least two sailors
SELECT rating, AVG(age) 
FROM Sailors 
GROUP BY rating 
HAVING COUNT(rating) >= 2;
-- @END: PART_5_Q2

-- @BEGIN: PART_5_Q3
-- Find the second maximum age of the sailor
SELECT MAX(age) FROM Sailors 
WHERE age != (SELECT MAX(age) FROM Sailors);
-- @END: PART_5_Q3

-- @BEGIN: PART_5_Q4
-- Retrieve tuples from the sailors table, in which trim 'io' from right side on ‘sname’
SELECT RTRIM(sname, 'io') AS trimmed_name FROM Sailors;
-- @END: PART_5_Q4

-- @BEGIN: PART_5_Q5
-- Write a PL/SQL program to print the following values
-- 0 if a=0         1 if a>0        -1if a<0
DECLARE
    a NUMBER := 5;
BEGIN
    IF a = 0 THEN
        DBMS_OUTPUT.PUT_LINE('0');
    ELSIF a > 0 THEN
        DBMS_OUTPUT.PUT_LINE('1');
    ELSE
        DBMS_OUTPUT.PUT_LINE('-1');
    END IF;
END;
/
-- @END: PART_5_Q5

-- @BEGIN: PART_5_Q6
-- Write a PL/SQL procedure to find factorial of a given number
CREATE OR REPLACE PROCEDURE fact_proc(n IN NUMBER, r OUT NUMBER) IS
    i NUMBER := n;
    f NUMBER := 1;
BEGIN
    WHILE (i > 1) LOOP
        f := f * i;
        i := i - 1;
    END LOOP;
    r := f;
END fact_proc;
/
-- Test Procedure:
DECLARE 
    result NUMBER; 
BEGIN 
    fact_proc(6, result); 
    DBMS_OUTPUT.PUT_LINE('Factorial is: ' || result); 
END;
/
-- @END: PART_5_Q6


-- ==============================================================================
-- PART 6
-- ==============================================================================

-- @BEGIN: PART_6_Q1
-- Find the names of sailors who have reserved boat 103 (using nested query)
-- (Repeated from Part 2)
SELECT sname FROM Sailors 
WHERE sid IN (SELECT sid FROM Reserves WHERE bid = 103);
-- @END: PART_6_Q1

-- @BEGIN: PART_6_Q2
-- Count the number of different sailor names
SELECT COUNT(DISTINCT sname) AS distinct_names FROM Sailors;
-- @END: PART_6_Q2

-- @BEGIN: PART_6_Q3
-- Find the number of reservations (with at least three) made for each boat
SELECT bid, COUNT(bid) AS res_count 
FROM Reserves 
GROUP BY bid 
HAVING COUNT(bid) >= 3;
-- @END: PART_6_Q3

-- @BEGIN: PART_6_Q4
-- Convert ‘2024  11 Aug’ into date
SELECT TO_DATE('2024 11 Aug', 'YYYY DD MON') AS formatted_date FROM dual;
-- @END: PART_6_Q4

-- @BEGIN: PART_6_Q5
-- Write a PL/SQL program to find Armstrong numbers in a given range m to n
DECLARE
    m NUMBER := 150;
    n NUMBER := 400;
    c NUMBER;
    r NUMBER;
    s NUMBER := 0;
BEGIN
    FOR i IN m..n LOOP
        c := i;
        s := 0;
        WHILE c > 0 LOOP
            r := MOD(c, 10);
            s := s + (r * r * r);
            c := FLOOR(c / 10);
        END LOOP;
        IF s = i THEN
            DBMS_OUTPUT.PUT_LINE(i || ' is Armstrong');
        END IF;
    END LOOP;
END;
/
-- @END: PART_6_Q5

-- @BEGIN: PART_6_Q6
-- Write a PL/SQL recursive function to find factorial of a number
CREATE OR REPLACE FUNCTION rfact(n IN NUMBER) RETURN NUMBER IS
    f NUMBER := 1;
BEGIN
    IF n = 1 THEN
        RETURN 1;
    ELSE
        f := n * rfact(n - 1);
        RETURN f;
    END IF;
END rfact;
/
-- Call test: 
BEGIN DBMS_OUTPUT.PUT_LINE('Factorial of 5: ' || rfact(5)); END;
/
-- @END: PART_6_Q6


-- ==============================================================================
-- PART 7
-- ==============================================================================

-- @BEGIN: PART_7_Q1
-- For each red boat, find the number of reservations for this boat.
SELECT bid, COUNT(bid) AS reservations_count 
FROM Reserves 
WHERE bid IN (SELECT bid FROM Boats WHERE color = 'red') 
GROUP BY bid;
-- @END: PART_7_Q1

-- @BEGIN: PART_7_Q2
-- Count the number of sailors who have reserved boats between '9/5/98' and '10/10/98'  
SELECT COUNT(sid) AS sailor_count FROM Reserves 
WHERE day BETWEEN TO_DATE('05-SEP-1998', 'DD-MON-YYYY') AND TO_DATE('10-OCT-1998', 'DD-MON-YYYY');
-- @END: PART_7_Q2

-- @BEGIN: PART_7_Q3
-- Find the color of the boat with bid 102.
SELECT color FROM Boats WHERE bid = 102;
-- @END: PART_7_Q3

-- @BEGIN: PART_7_Q4
-- Retrieve tuples from the reserves table (display ‘day’ as dd-mon-yyyy)
SELECT sid, bid, TO_CHAR(day, 'dd-mon-yyyy') AS day_formatted FROM Reserves;
-- @END: PART_7_Q4

-- @BEGIN: PART_7_Q5
-- Write a PL/SQL program to find even numbers and odd numbers in a given range m to       n.
DECLARE
    m NUMBER := 25;
    n NUMBER := 30;
BEGIN
    FOR i IN m..n LOOP
        IF MOD(i, 2) = 1 THEN
            DBMS_OUTPUT.PUT_LINE(i || ' is odd number');
        ELSE
            DBMS_OUTPUT.PUT_LINE(i || ' is even number');
        END IF;
    END LOOP;
END;
/
-- @END: PART_7_Q5

-- @BEGIN: PART_7_Q6
-- Write a PL/SQL procedure to print first n Fibonacci numbers
CREATE OR REPLACE PROCEDURE fibonacci(n IN NUMBER) IS
    f1 NUMBER := 0;
    f2 NUMBER := 1;
    f3 NUMBER;
    i NUMBER := 1;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Fibonacci Series:');
    DBMS_OUTPUT.PUT_LINE(f1);
    DBMS_OUTPUT.PUT_LINE(f2);
    WHILE (i < n - 1) LOOP
        f3 := f1 + f2;
        f1 := f2;
        f2 := f3;
        i := i + 1;
        DBMS_OUTPUT.PUT_LINE(f3);
    END LOOP;
END fibonacci;
/
-- Call test: 
BEGIN fibonacci(5); END;
/
-- @END: PART_7_Q6


-- ==============================================================================
-- PART 8
-- ==============================================================================

-- @BEGIN: PART_8_SETUP
-- Create Sailors table with the following fields and describe the table (sid: integer, sname:  string, rating: integer) (Note: Underlined attribute represents the primary key)
-- Add field (age: real) to the Sailors table and describe the table
CREATE TABLE Sailors_Part8 (
    sid INT PRIMARY KEY,
    sname VARCHAR2(20),
    rating INT
);
DESCRIBE Sailors_Part8;

ALTER TABLE Sailors_Part8 ADD (age NUMBER(3,1));
DESCRIBE Sailors_Part8;
-- @END: PART_8_SETUP

-- @BEGIN: PART_8_Q1
-- Find all sailors from sailors and sailors1 table by using UNION operator
SELECT * FROM Sailors 
UNION 
SELECT * FROM Sailors1;
-- @END: PART_8_Q1

-- @BEGIN: PART_8_Q2
-- Find all sailors whose age is in the list of values (15.0, 33.2, 45.7, and 63.5) (using IN)
SELECT * FROM Sailors WHERE age IN (15.0, 33.2, 45.7, 63.5);
-- @END: PART_8_Q2

-- @BEGIN: PART_8_Q3
-- Find sailors whose rating is better than some sailor called Horatio (using ALL operators)
SELECT * FROM Sailors 
WHERE rating > ALL(SELECT rating FROM Sailors WHERE sname = 'Horatio');
-- @END: PART_8_Q3

-- @BEGIN: PART_8_Q4
-- Retrieve tuples from the sailors table, in which trim ‘Du' from left side on ‘sname’
SELECT LTRIM(sname, 'Du') AS ltrimmed_name FROM Sailors;
-- @END: PART_8_Q4

-- @BEGIN: PART_8_Q5
-- Retrieve tuple from the Sailors table whose sname is 'Lubber' using PL/SQL program
DECLARE
    s_id Sailors.sid%TYPE;
    s_sname Sailors.sname%TYPE;
    s_rating Sailors.rating%TYPE;
    s_age Sailors.age%TYPE;
BEGIN
    SELECT sid, sname, rating, age INTO s_id, s_sname, s_rating, s_age 
    FROM Sailors WHERE sname = 'Lubber' AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE('sid: ' || s_id || ' sname: ' || s_sname || ' rating: ' || s_rating || ' age: ' || s_age);
END;
/
-- @END: PART_8_Q5

-- @BEGIN: PART_8_Q6
-- Write a PL/SQL function to find sum of digits of a number
CREATE OR REPLACE FUNCTION sumofn(n IN NUMBER) RETURN NUMBER IS
    s NUMBER := 0;
    i NUMBER := n;
BEGIN
    WHILE (i != 0) LOOP
        s := s + MOD(i, 10);
        i := FLOOR(i / 10);
    END LOOP;
    RETURN s;
END sumofn;
/
-- Call test:
BEGIN DBMS_OUTPUT.PUT_LINE('Sum of digits of 456 is: ' || sumofn(456)); END;
/
-- @END: PART_8_Q6


-- ==============================================================================
-- PART 9
-- ==============================================================================

-- @BEGIN: PART_9_Q1
-- Find the names of sailors who have reserved boat number 103 (using AND operator)
SELECT S.sname 
FROM Sailors S, Reserves R 
WHERE S.sid = R.sid AND R.bid = 103;
-- @END: PART_9_Q1

-- @BEGIN: PART_9_Q2
-- Count the number of sailors
SELECT COUNT(*) AS total_sailors FROM Sailors;
-- @END: PART_9_Q2

-- @BEGIN: PART_9_Q3
-- Find the age of the youngest sailor for each rating level, which is greater than 7
SELECT rating, MIN(age) 
FROM Sailors 
GROUP BY rating 
HAVING rating > 7;
-- @END: PART_9_Q3

-- @BEGIN: PART_9_Q4
-- Write a PL/SQL program to show handling of Pre-defined exception
DECLARE
    a NUMBER := 4;
    c NUMBER;
BEGIN
    c := a / 0;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Exception: Divide by zero');
END;
/
-- @END: PART_9_Q4


-- ==============================================================================
-- PART 10
-- ==============================================================================

-- @BEGIN: PART_10_Q1
-- Find the number of sailors belongs to each rating level
SELECT rating, COUNT(rating) AS num_sailors 
FROM Sailors 
GROUP BY rating;
-- @END: PART_10_Q1

-- @BEGIN: PART_10_Q2
-- Find the number of reservations made for each boat during '9/5/98' and '10/10/98'
SELECT bid, COUNT(bid) AS num_res 
FROM Reserves 
WHERE day BETWEEN TO_DATE('05-SEP-1998', 'DD-MON-YYYY') AND TO_DATE('10-OCT-1998', 'DD-MON-YYYY') 
GROUP BY bid;
-- @END: PART_10_Q2

-- @BEGIN: PART_10_Q3
-- Find the days on which 'Horatio' reserved the boats
SELECT day FROM Reserves 
WHERE sid IN (SELECT sid FROM Sailors WHERE sname = 'Horatio');
-- @END: PART_10_Q3

-- @BEGIN: PART_10_Q4
-- Write a PL/SQL program to retrieve records from the sailors table using cursor
DECLARE
    s_id Sailors.sid%TYPE;
    s_name Sailors.sname%TYPE;
    s_rating Sailors.rating%TYPE;
    s_age Sailors.age%TYPE;
    CURSOR c_sailors IS SELECT sid, sname, rating, age FROM Sailors;
BEGIN
    OPEN c_sailors;
    LOOP
        FETCH c_sailors INTO s_id, s_name, s_rating, s_age;
        EXIT WHEN c_sailors%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(s_id || ' ' || s_name || ' ' || s_rating || ' ' || s_age);
    END LOOP;
    CLOSE c_sailors;
END;
/
-- @END: PART_10_Q4