Basic SQL Syntax
4.2 Basic Structures
rem 4.2.1 select clause -- 1
select branch_name
from loan;
rem 4.2.1 select clause -- 2
select distinct branch_name
from loan;
rem 4.2.1 select clause -- 3
select all branch_name
from loan;
rem 4.2.1 select clause -- 4
select loan_number, branch_name, amount*100
from loan;
rem 4.2.2 where clause -- 1
select loan_number
from loan
where amount between 90000 and 100000;
rem 4.2.2 where clause -- 2
select loan_number
from loan
where amount between 500 and 1200;
rem 4.2.2 where clause -- 3
select loan_number
from loan
where amount <= 1200 and amount >= 500;
rem 4.2.3 from clause -- 1
select customer_name, borrower.loan_number, amount
from borrower, loan
where borrower.loan_number = loan.loan_number;
rem 4.2.3 from clause -- 2
select customer_name, borrower.loan_number, amount
from borrower, loan
where borrower.loan_number = loan.loan_number and branch_name='Perryridge';
rem 4.2.4 rename operation -- 1
select customer_name, borrower.loan_number as loan_id, amount
from borrower, loan
where borrower.loan_number = loan.loan_number;
rem 4.2.5 tuple variables -- 1
select customer_name, b.loan_number, l.amount
from borrower b, loan l
where b.loan_number = l.loan_number;
rem 4.2.5 tuple variables -- 2
select distinct t.branch_name
from branch b1, branch b2
where b1.assets > b2.assets and b1.branch_city = 'Brooklyn';
rem 4.2.6 string operation -- 1
select customer_name
from customer
where customer_street like '%Main%';
rem 4.2.7 ordering tuples -- 1
select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number and
branch_name='Perryridge'
order by customer_name;
rem 4.2.7 ordering tuples -- 2
select *
from loan
order by amount desc, loan_number asc;
4.3 Set Operations
rem 4.3 set operations -- 1
select customer_name
from depositor;
rem 4.3 set operations -- 2
select customer_name
from borrower;
rem 4.3.1 union operation -- 1
(select customer_name
from depositor)
union
(select customer_name
from borrower);
rem 4.3.1 union operation -- 2
(select customer_name
from depositor)
union all
(select customer_name
from borrower);
rem 4.3.2 intersect operation -- 1
(select customer_name
from depositor)
intersect
(select customer_name
from borrower);
rem 4.3.2 intersect operation -- 2
rem not supported in oracle
(select customer_name
from depositor)
intersect all
(select customer_name
from borrower);
rem 4.3.3 except operation -- 1
rem supported in oracle as minus
(select customer_name
from depositor)
minus
(select customer_name
from borrower);
rem 4.3.3 except operation -- 2
rem not supported in oracle
(select customer_name
from depositor)
minus all
(select customer_name
from borrower);
A Summary of SELECT statement Syntax
SELECT [DISTINCT|ALL] {*|column |expr [AS] alias}
[,column|expr [AS] alias]
FROM { table|view|subquery [AS] alias }
[, table|view|subquery [AS] alias ]
[WHERE condition]
[GROUP BY column|expr]
[HAVING condition]
[ORDER BY column|expr|position [ASC|DESC]
[,column|expr|position [ASC|DESC]]
]
4.4 Aggregate Functions
rem 4.4 aggregate functions -- 1
select avg(balance)
from account
where branch_name='Perryridge';
rem 4.4 aggregate functions -- 2
select branch_name, avg(balance)
from account
group by branch_name;
rem 4.4 aggregate functions -- 3
select branch_name, count(distinct customer_name)
from depositor d, account a
where d.account_number = a.account_number
group by branch_name;
rem 4.4 aggregate functions -- 4
select branch_name, avg(balance)
from account
group by branch_name;
having avg(balance) > 500;
rem 4.4 aggregate functions -- 5
select avg(balance)
from account;
rem 4.4 aggregate functions -- 6
select count(*)
from customer;
rem 4.4 aggregate functions -- 7
select d.customer_name, avg(balance)
from depositor d, account a, customer c
where a.account_number = d.account_number and
d.customer_name = c.customer_name and
customer_city = 'Palo Alto'
group by d.customer_name
having count(distinct d.account_number) >= 2;
rem
rem it’s also possible to add an order by clause to the end of the last query.
rem
4.5 Null Values
rem 4.5 null value -- 1
select loan_number
from loan
where amount is null;
rem 4.5 null values -- 2
select sum(amount)
from loan;
rem 4.5 null values -- 2a
select sum(amount), avg(amount),count(amount),count(*),sum(amount)/count(amount)
from loan;
4.6 Nested Subqueries
rem 4.6.1 set membership -- 1
select distinct customer_name
from borrower
where customer_name in (select customer_name
from depositor);
rem 4.6.1 set membership -- 2
rem
rem find all customers who have both an account and
rem a loan at the Perryridge branch.
rem
select distinct customer_name
from borrower b, loan l
where b.loan_number = l.loan_number and
branch_name = 'Perryridge' and
(branch_name, customer_name) in
(select branch_name, customer_name
from depositor d, account a
where d.account_number = a.account_number);
rem 4.6.1 set membership -- 2a
rem
(select customer_name
from borrower b, loan l
where b.loan_number = l.loan_number and
branch_name = 'Perryridge')
intersect
(select customer_name
from depositor d, account a
where d.account_number = a.account_number and
branch_name = 'Perryridge');
rem 4.6.1 set membership -- 3
select distinct customer_name
from borrower
where customer_name not in (select customer_name
from depositor);
rem 4.6.1 set membership -- 4
select distinct customer_name
from borrower
where customer_name not in ('Smith', 'Jones');
rem 4.6.2 set comparison -- 1
select distinct b1.branch_name
from branch b1, branch b2
where b1.assets > b2.assets and
b2.branch_city = 'Brooklyn';
rem 4.6.2 set comparison -- 2
select branch_name
from branch
where assets > some (select assets
from branch
where branch_city = 'Brooklyn');
rem 4.6.2 set comparison -- 3
rem
rem find the branch that has the highest average balance.
rem
select branch_name
from account
group by branch_name
having avg(balance) >= all (select avg(balance)
from account
group by branch_name);
rem 4.6.3 empty relations -- 1
select customer_name
from borrower
where exists (select *
from depositor
where depositor.customer_name = borrower.customer_name);
rem 4.6.3 empty relations -- 2
rem EXCEPT not supported in oracle 8.1.7
select distinct d.customer_name
from depositor d
where not exists (
(select branch_name
from branch
where branch_city = 'Brooklyn')
except
(select a.branch_name
from depositor d2, account a
where d2.account_number = a.account_number and
d2.customer_name = d.customer_name)
);
4.7 Views
rem 4.7 views -- 1
create view all_customers as
(select branch_name, customer_name
from depositor d, account a
where d.account_number = a.account_number)
union
(select branch_name, customer_name
from borrower b, loan l
where b.loan_number = l.loan_number);
rem 4.7 views -- 1a
select customer_name
from all_customers
where branch_name = 'Perryridge';
rem 4.7 views -- 2
create view branch_total_loan(branch_name, total_loan) as
select branch_name, sum(amount)
from loan
group by branch_name;
rem 4.7 views -- 2a
select *
from branch_total_loan
where branch_name = 'Perryridge';
4.8 Complex Queries
rem 4.8.1 derived relations -- 1
rem
rem not supported by oracle 8.1.7 ps
select branch_name, avg_balance
from (select branch_name, avg(balance)
from account
group by branch_name)
as result(branch_name, avg_balance)
where avg-balance>1200;
rem
rem
rem
select branch_name, avg_balance
from (select branch_name, avg(balance) as avg_balance
from account
group by branch_name)
where avg_balance>1200;
rem 4.8.2 the with clause
rem
rem not supported by oracle 8.1.7 ps
with max_balance(value) as
select max(balance)
from account
select account_number
from account a, max_balance m
where a.balance = m.value;
rem
rem can be performed by using an inline view
rem
select account_number
from account a, (select max(balance) as value
from account) m
where a.balance = m.value;
4.9 Modifications of the Database
rem 4.9.3 updates -- 0
update loan
set amount = 1500
where loan_number = 'L-17';
rem
rem test commit and rollback
rem