-- find the table names of all existing tables created by users

select table_name from user_tables;

 

-- find the schema of the customer table

desc customer

 


-- direct the output of a query to a file named queryResult.lst

spool queryResult;

select * from employees;

spool off;


 

-- Save the contents of the SQL buffer (previous SQL statement) into a file with name mySQLFile.sql

save mySQLFile


 

-- Run the SQL*Plus statements in the file mySQLFile
@mySQLFile


-- Quit Oracle
quit;


--set output line width to 500 characters
set line 500

-- set pagesize to 10 lines
set pagesize 10


--_ alter table
alter table branch add (county char(20));
alter table branch add primary key (branch_name);
alter table branch add constraint countyUnique unique (county);
alter table branch modify (branch_city char(30) default 'Denton'
                                     constraint cityNotnull not null);
alter table branch add constraint ACheck check (assets>0);
alter table branch drop constraint countyUnique;
alter table branch drop constraint Acheck;
alter table branch drop constraint citynotnull;
alter table branch drop primary key cascade;




--___SQL does not eliminate duplications automatically

-- List the branch names of branches what have a loan
select branch_name
from loan;

select distinct branch_name
from loan;

-- List all loans
select *
from loan;

-- Find loan number, branch name, and the loan amount multiplied by 100 of each loan
select loan_number, branch_name, amount * 100
from loan;


--Find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200.
select loan_number
from loan
where branch_name = 'Perryridge' and amount > 1200;

--Find the loan number of those loans with loan amounts between $90,000 and $100,000 
select loan_number
from loan
where amount between 90000 and 100000;

-- Try the following statement and say the result as a Cartisian product
select *
from borrower, loan;


-- Find the name, loan number and loan amount of all customers  having a loan at the Perryridge branch.
select customer_name, borrower.loan_number, amount
from borrower, loan
where   borrower.loan_number = loan.loan_number  and branch_name = 'Perryridge';


-- Rename an attribute in the result using 'as'
select customer_name, borrower.loan_number as loan_id, amount
from borrower, loan
where borrower.loan_number = loan.loan_number;


-- Rename a relation 
-- Note: in oracle 'as' is omitted
select customer_name, T.loan_number, S.amount
from borrower  T, loan  S
where T.loan_number = S.loan_number;


--Find the names of all branches that have greater assets than some branch located in Brooklyn.
select distinct T.branch_name
from branch T, branch S
where T.assets > S.assets and S.branch_city = 'Brooklyn';

_ Or
select branch_name
from branch
where assets > some
 ( select assets
   from branch
   where branch_city = 'Brooklyn'
 );

--Find the names of all customers whose street includes the substring Main.
select customer_name
from customer
where customer_street like '%Main%';

--List in alphabetic order the names of all customers having a loan in Perryridge branch
select distinct customer_name	
from    borrower, loan	
where borrower.loan_number = loan.loan_number                          
      and branch_name = 'Perryridge'
order by customer_name desc;

-- Find all customers who have a loan, an account, or both:
(select customer_name from depositor)	
union	
(select customer_name from borrower);

-- Find all customers who have both a loan and an account.
(select customer_name from depositor)	
intersect	
(select customer_name from borrower);

-- Find all customers who have an account but no loan.	
(select customer_name from depositor)	
minus
(select customer_name from borrower);


-- Find the average account balance at the Perryridge branch.
select avg (balance)	
from account	
where branch_name = 'Perryridge';

--Find the number of tuples in the customer relation.
select count (*)	
from customer;

-- Find the number of depositors in the bank.
select count (distinct customer_name)	
from depositor;

-- Find the number of depositors for each branch.
select branch_name, count (distinct customer_name)	
from depositor, account	
where depositor.account_number = account.account_number	
group by branch_name;

-- Find the names of all branches where the average account balance is more than $500.
select branch_name, avg (balance)	
from account	
group by branch_name	
having avg (balance) > 500;

--Find all loan number which appear in the loan relation with null values for amount.
select loan_number	
from loan	
where amount is null;

-- Find all customers who have both an account and a loan at the bank.
select distinct customer_name	
from borrower	
where customer_name in 
(select customer_name
from depositor);

-- Find all customers who have a loan at the bank but do not have      an account at the bank
select distinct customer_name	
from borrower	
where customer_name not in 
(select customer_name
from depositor);

-- Find the names of all branches that have greater assets than all branches located in Brooklyn.
select branch_name	
from branch	
where assets > all
(select assets
from branch
where branch_city = 'Brooklyn');

-- Find all customers who have an account at all branches located in Brooklyn.
select distinct S.customer_name	
from depositor S	
where not exists (
(select branch_name
from branch
where branch_city = 'Brooklyn') 
minus
(select R.branch_name
from depositor T, account R
where T.account_number = R.account_number and S.customer_name = T.customer_name));

-- Find all customers who have at most one account at the Perryridge branch.
select T.customer_name
from depositor  T
where 1 = (
select count(R.customer_name)   
from account, depositor  R
where T.customer_name = R.customer_name                      
      and R.account_number = account.account_number 
      and account.branch_name = 'Perryridge');


-- set the assets of Perryridge branch to 100,000
update branch
set assets = 100000
where branch_name= 'Perryridge';

-- increase the balances of the accounts in Perryridge branch by 10%, in Brooklyn by 5%, and others by 1%
update account
set balance =  case
   when branch_name = 'Perryridge'
   then balance *1.10
   when branch_name = 'Brooklyn'
   then balance *1.05
   else balance *1.01
end;