Untitled

Find out the customer names, loan numbers, and loan amounts for all loans located at Perryridge branch.

sql
Copy code
SELECT c.customer_name, l.loan_number, l.amount
FROM borrower b
JOIN loan l ON b.loan_number = l.loan_number
JOIN customer c ON b.customer_name = c.customer_name
WHERE l.branch_name = 'Perryridge';
Find out the names of all the customers whose street address includes the substring.

sql
Copy code
SELECT customer_name
FROM customer
WHERE customer_street LIKE '%substring%';
List all the loans by descending order of amount.

sql
Copy code
SELECT *
FROM loan
ORDER BY amount DESC;
Find out all the customers having a loan, an account, or both.

sql
Copy code
SELECT DISTINCT c.customer_name
FROM customer c
LEFT JOIN depositor d ON c.customer_name = d.customer_name
LEFT JOIN borrower b ON c.customer_name = b.customer_name
WHERE d.account_number IS NOT NULL OR b.loan_number IS NOT NULL;
Find out the branch having more than one account.

sql
Copy code
SELECT branch_name
FROM account
GROUP BY branch_name
HAVING COUNT(account_number) > 1;
Find out the second highest balance owned by a customer.

sql
Copy code
SELECT MAX(balance) AS second_highest_balance
FROM account
WHERE balance < (SELECT MAX(balance) FROM account);
List accounts with the current balance and the branch to which it belongs, having a balance more than the average balance of that branch.

sql
Copy code
SELECT a.account_number, a.balance, a.branch_name
FROM account a
JOIN (
    SELECT branch_name, AVG(balance) AS avg_balance
    FROM account
    GROUP BY branch_name
) b_avg ON a.branch_name = b_avg.branch_name
WHERE a.balance > b_avg.avg_balance;
Find the customers who opened their account between the year 1950 and 1975.
(Assuming there is a column opening_date in the account table)

sql
Copy code
SELECT DISTINCT c.customer_name
FROM customer c
JOIN depositor d ON c.customer_name = d.customer_name
JOIN account a ON d.account_number = a.account_number
WHERE a.opening_date BETWEEN '1950-01-01' AND '1975-12-31';
Write a PL/SQL program to check whether a number is prime or not.

sql
Copy code
DECLARE
    num NUMBER := &number;
    i NUMBER := 2;
    is_prime BOOLEAN := TRUE;
BEGIN
    IF num <= 1 THEN
        is_prime := FALSE;
    ELSE
        WHILE i <= SQRT(num) LOOP
            IF MOD(num, 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;
/
branch(branch-name,branch-city,assets) 
account(account-number,branch-name,balance) 
customer(customer-name,customer-city,customer-street) 
depositor(customer-name,account-number) 
loan(loan-number,branch-name,amount) 
borrower(customer-name,loan-number) 
GIVE AN EXPRESSION IN SQL FOR EACH OF THE FOLLOWING QUERIES: 
1. Find out the customer names, account numbers and balance for all the accounts located at 
the Perryridge branch. 
2. List the customers whose names have the second character as a or l . 3. List in alphabetic order all customers who have a loan at the Perryridge branch. 
4. Find out all the customers who have both a loan and account. 
5. List those customers which are associated with only one account. 
6. Find the third highest balance owned by a customer. 
7. List accounts with the current balance and the branch to which it belongs, having a 
balance more than the average balance of that branch, which the account belongs to. 
8. Find the customers who have bank branches at their city. 
9. List all customers who have at least one account. 
10. Write a PLSQL program to find out greatest common divisor of two numbers.
ChatGPT
Here are the SQL expressions for the new set of queries:

Find out the customer names, account numbers, and balance for all the accounts located at the Perryridge branch.

sql
Copy code
SELECT c.customer_name, a.account_number, a.balance
FROM customer c
JOIN depositor d ON c.customer_name = d.customer_name
JOIN account a ON d.account_number = a.account_number
WHERE a.branch_name = 'Perryridge';
List the customers whose names have the second character as 'a' or 'l'.

sql
Copy code
SELECT customer_name
FROM customer
WHERE SUBSTRING(customer_name, 2, 1) IN ('a', 'l');
List in alphabetic order all customers who have a loan at the Perryridge branch.

sql
Copy code
SELECT DISTINCT c.customer_name
FROM customer c
JOIN borrower b ON c.customer_name = b.customer_name
JOIN loan l ON b.loan_number = l.loan_number
WHERE l.branch_name = 'Perryridge'
ORDER BY c.customer_name;
Find out all the customers who have both a loan and an account.

sql
Copy code
SELECT DISTINCT c.customer_name
FROM customer c
JOIN depositor d ON c.customer_name = d.customer_name
JOIN borrower b ON c.customer_name = b.customer_name;
List those customers which are associated with only one account.

sql
Copy code
SELECT d.customer_name
FROM depositor d
GROUP BY d.customer_name
HAVING COUNT(d.account_number) = 1;
Find the third highest balance owned by a customer.

sql
Copy code
SELECT MIN(balance) AS third_highest_balance
FROM (
    SELECT DISTINCT balance
    FROM account
    ORDER BY balance DESC
    LIMIT 3
) AS subquery;
List accounts with the current balance and the branch to which it belongs, having a balance more than the average balance of that branch.

sql
Copy code
SELECT a.account_number, a.balance, a.branch_name
FROM account a
JOIN (
    SELECT branch_name, AVG(balance) AS avg_balance
    FROM account
    GROUP BY branch_name
) AS avg_balances ON a.branch_name = avg_balances.branch_name
WHERE a.balance > avg_balances.avg_balance;
Find the customers who have bank branches in their city.

sql
Copy code
SELECT DISTINCT c.customer_name
FROM customer c
JOIN branch b ON c.customer_city = b.branch_city;
List all customers who have at least one account.

sql
Copy code
SELECT DISTINCT c.customer_name
FROM customer c
JOIN depositor d ON c.customer_name = d.customer_name;
Write a PL/SQL program to find out the greatest common divisor of two numbers.

sql
Copy code
DECLARE
    num1 NUMBER := &number1;
    num2 NUMBER := &number2;
    gcd NUMBER;
BEGIN
    WHILE num2 != 0 LOOP
        gcd := num2;
        num2 := MOD(num1, num2);
        num1 := gcd;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('The GCD is ' || gcd);
END;
/
Create table also
ChatGPT
Sure, here are the SQL CREATE TABLE statements for the given schema:

sql
Copy code
CREATE TABLE branch (
    branch_name VARCHAR(50) PRIMARY KEY,
    branch_city VARCHAR(50),
    assets DECIMAL(15, 2)
);

CREATE TABLE account (
    account_number VARCHAR(20) PRIMARY KEY,
    branch_name VARCHAR(50),
    balance DECIMAL(15, 2),
    opening_date DATE,
    FOREIGN KEY (branch_name) REFERENCES branch(branch_name)
);

CREATE TABLE customer (
    customer_name VARCHAR(50) PRIMARY KEY,
    customer_city VARCHAR(50),
    customer_street VARCHAR(100)
);

CREATE TABLE depositor (
    customer_name VARCHAR(50),
    account_number VARCHAR(20),
    PRIMARY KEY (customer_name, account_number),
    FOREIGN KEY (customer_name) REFERENCES customer(customer_name),
    FOREIGN KEY (account_number) REFERENCES account(account_number)
);

CREATE TABLE loan (
    loan_number VARCHAR(20) PRIMARY KEY,
    branch_name VARCHAR(50),
    amount DECIMAL(15, 2),
    FOREIGN KEY (branch_name) REFERENCES branch(branch_name)
);

CREATE TABLE borrower (
    customer_name VARCHAR(50),
    loan_number VARCHAR(20),
    PRIMARY KEY (customer_name, loan_number),
    FOREIGN KEY (customer_name) REFERENCES customer(customer_name),
    FOREIGN KEY (loan_number) REFERENCES loan(loan_number)
);