Untitled

CREATE TABLE branch (
    branch_name VARCHAR(50),
    branch_city VARCHAR(50),
    assets INT
);
 
INSERT INTO branch VALUES ('Brighton', 'Brooklyn', 7100000);
INSERT INTO branch VALUES ('Downtown', 'Brooklyn', 9000000);
INSERT INTO branch VALUES('Mianus', 'Horseneck', 400000);
INSERT INTO branch VALUES('North Town', 'Rye', 3700000);
INSERT INTO branch VALUES('Perryridge', 'Horseneck', 1700000);
INSERT INTO branch VALUES('Pownal', 'Bennington', 300000);
INSERT INTO branch VALUES('Redwood', 'Palo Alto', 2100000);
INSERT INTO branch VALUES('Round Hill', 'Horseneck', 8000000);
Select * from branch;
 
CREATE TABLE customer (
    customer_name VARCHAR(50),
    customer_street VARCHAR(50),
    customer_city VARCHAR(50)
);
 
 
INSERT INTO customer VALUES ('Adams', 'Spring', 'Pittsfield');
INSERT INTO customer VALUES('Brooks', 'Senator', 'Brooklyn');
INSERT INTO customer VALUES('Curry', 'North', 'Rye');
INSERT INTO customer VALUES('Glenn', 'Sand Hill', 'Woodside');
INSERT INTO customer VALUES('Green', 'Walnut', 'Stamford');
INSERT INTO customer VALUES('Hayes', 'Main', 'Harrison');
INSERT INTO customer VALUES('Johnson', 'Alma', 'Palo Alto');
INSERT INTO customer VALUES('Jones', 'Main', 'Harrison');
INSERT INTO customer VALUES('Lindsay', 'Park', 'Pittsfield');
INSERT INTO customer VALUES('Smith', 'North', 'Rye');
INSERT INTO customer VALUES('Turner', 'Putnam', 'Stamford');
INSERT INTO customer VALUES('Williams', 'Nassau', 'Princeton');
 Select * from customer;
 
CREATE TABLE loan (
    loan_number VARCHAR(10),
    branch_name VARCHAR(50),
    amount DECIMAL(10, 2)
);
 
 
 
INSERT INTO loan VALUES('L-11', 'Round Hill', 900);
INSERT INTO loan VALUES('L-14', 'Downtown', 1500);
INSERT INTO loan VALUES('L-15', 'Perryridge', 1500);
INSERT INTO loan VALUES('L-16', 'Perryridge', 1300);
INSERT INTO loan VALUES('L-17', 'Downtown', 1000);
INSERT INTO loan VALUES('L-23', 'Redwood', 2000);
INSERT INTO loan VALUES('L-93', 'Mianus', 500);
Select * from loan;
 
CREATE TABLE account (
    account_number VARCHAR(10),
    branch_name VARCHAR(50),
    balance DECIMAL(10, 2)
);
 
 
 
INSERT INTO account VALUES('A-101', 'Downtown', 500);
INSERT INTO account VALUES('A-215', 'Mianus', 700);
INSERT INTO account VALUES('A-102', 'Perryridge', 400);
INSERT INTO account VALUES('A-305', 'Round Hill', 350);
INSERT INTO account VALUES('A-201', 'Brighton', 900);
INSERT INTO account VALUES('A-222', 'Redwood', 700);
INSERT INTO account VALUES('A-217', 'Brighton', 750);
Select * from account;
 
 
CREATE TABLE depositor (
    customer_name VARCHAR(50),
    account_number VARCHAR(10)
);
 
INSERT INTO depositor VALUES('Hayes', 'A-102');
INSERT INTO depositor VALUES('Johnson', 'A-101');
INSERT INTO depositor VALUES('Johnson', 'A-201');
INSERT INTO depositor VALUES('Jones', 'A-217');
INSERT INTO depositor VALUES('Lindsay', 'A-222');
INSERT INTO depositor VALUES('Smith', 'A-215');
INSERT INTO depositor VALUES('Turner', 'A-305');
 Select * from depositor;
 
CREATE TABLE borrower (
    customer_name VARCHAR(50),
    loan_number VARCHAR(10)
);
 
INSERT INTO borrower VALUES ('Adams', 'L-16');
INSERT INTO borrower VALUES ('Curry', 'L-93');
INSERT INTO borrower VALUES ('Hayes', 'L-15');
INSERT INTO borrower VALUES ('Jackson', 'L-14');
INSERT INTO borrower VALUES ('Jones', 'L-17');
INSERT INTO borrower VALUES ('Smith', 'L-11');
INSERT INTO borrower VALUES ('Smith', 'L-23');
INSERT INTO borrower VALUES ('Williams', 'L-17');
Select * from borrower;
 
SELECT borrower.customer_name, loan.loan_number, loan.amount
    FROM loan
    JOIN borrower ON loan.loan_number = borrower.loan_number
    WHERE loan.branch_name = 'Perryridge';
 
SELECT customer_name
    FROM customer
    WHERE customer_street LIKE '%Main%';
 
 SELECT loan_number, amount
    FROM loan
    ORDER BY amount DESC;
 
  SELECT DISTINCT customer_name
    FROM depositor
    UNION
    SELECT DISTINCT customer_name
    FROM borrower;
 
SELECT branch_name
    FROM account
    GROUP BY branch_name
    HAVING COUNT(account_number) > 1;
 
SELECT MAX(balance) AS second_highest_balance
    FROM account
    WHERE balance < (SELECT MAX(balance) FROM account);
 
SELECT account.account_number, account.balance, account.branch_name
    FROM account
    JOIN (
        SELECT branch_name, AVG(balance) AS avg_balance
        FROM account
        GROUP BY branch_name
    ) branch_avg ON account.branch_name = branch_avg.branch_name
    WHERE account.balance > branch_avg.avg_balance;
 
   SELECT DISTINCT customer.*
    FROM customer
    JOIN depositor ON customer.customer_name = depositor.customer_name;
 
    SELECT DISTINCT customer.*
    FROM account
    JOIN depositor ON account.account_number = depositor.account_number
    JOIN customer ON depositor.customer_name = customer.customer_name
    WHERE account_opening_year BETWEEN 1950 AND 1975;
 
DECLARE
        num NUMBER;
        i NUMBER := 2;
        is_prime BOOLEAN := TRUE;
    BEGIN
        num := &input_number;
 
        IF num < 2 THEN
            is_prime := FALSE;
        ELSE
            WHILE i <= SQRT(num) LOOP
                IF (num MOD i = 0) THEN
                    is_prime := FALSE;
                    EXIT;
                END IF;
                i := i + 1;
            END LOOP;
        END IF;
 
        IF is_prime THEN
            DBMS_OUTPUT.PUT_LINE(num || ' is a prime number.');
        ELSE
            DBMS_OUTPUT.PUT_LINE(num || ' is not a prime number.');
        END IF;
    END;