table operation
drop
drop table components cascade constraints;
create
create table components (
term varchar2(10),
lineno number(4) check(lineno >= 1000),
compname varchar2(15),
maxpoints number(4) not null check(maxpoints >= 0),
weight number(2) check(weight >= 0),
primary key (term,lineno,compname),
foreign key (term.lineno) references courses
);
modify
alter table customers modify (
street varchar2(50)
);
insert row
insert into components values
('f96',1031,'examl',100,30);
select
select [distinct] <expression> {, <expression>}
from <tablename> [<alias>] {, <tablename> [<alias>]}
[where <search_condition>];
[group by <column> {, <column>}]
[having <condition>]
wildcard, is
select *
from customers
where cname like 'A%' and
weight is null;
-- %对应任意长度
-- _对应 1 长度
between
select sid AS id
from scores
where points between 50 and 70;
rename, calculation
select x.ono, x.pno, p.pname, x.qty,
p.price, (x.qty * p.price) total
from odetails x, parts p
-- from 多个 table 相当于 table1 x table2 x table3 ...
-- 需要在 where 进行限制，变成实质的 join
where x.pno = p.pno
Get all pairs of cno values for customers based in the same zip code.
select c1.cno, c2.cno
from customers c1, customers c2
where c1.zip = c2.zip and c1.cno > c2.cno;
Get pno values for parts that have been ordered by at least two different customers.
select distinct y1.pno
from orders x1, orders x2, odetails y1, odetails y2
where y1.pno = y2.pno and y1.ono = xl.ono and
y2.ono = x2.ono and x1.cno > x2.cno
Sub-Selects
expr [not] in (sub-select)
expr [not] in (value1, value2, ...})
Get cname values of customers who place orders with employees living in Fort Dodge.
select distinct cname from orders,customers where orders.eno = customers.eno and eno in (select eno from employees,zipcodes where employees.zip = zipcodes.zip and city = 'Fort Dodge');Get pname values for parts with the lowest price.
select pname from parts where price <=all (select price from parts);
Union
(sub-select) union (sub-select)
select city
from customers.zipcodes
where customers.zip = zipcodes.zip
UNION
-- or INTERSECT, MINUS
select city
from employees,zipcodes
where employees.zip = zipcodes.zip
Aggregate Functions
Get total quantity of part 10601 that has been ordered.
select sum(qty) TOTAL
from odetails
where pno = 10601;
Get the number of cities in which customers are based.
select count(distinct city)
from customers, zipcodes
where customers.zip = zipcodes.zip;
Get the pname values of parts that cost more than the average cost of all parts
select pname
from parts
where price > (select avg(price)
from parts);
Get pname values of parts ordered by at least two different customers.
select p.pname
from parts p
where 2 <= (select count(distinct cno)
from orders,odetails
where orders.ono = odetails.ono and
pno = p.pno);
Group by
在上面，aggregate function 都是去操作（被where挑选的）全部的记录行，比如“全校的数学平均分”。但是有时候 aggregate function 需要分组去操作记录行，比如“全校每个班的数学平均分”。所以我们需要group by来操作。在结果的 table 中，每个组都会有单独的一个记录行
| 组 | 值 |
|---|---|
| 一班 | 75分 |
| 二班 | 72分 |
| 三班 | 63分 |
having是用来剔除特定的组，比如“平均分小于65的班”
View
CREATE VIEW MyBeers AS
SELECT name, manf
FROM Beers
WHERE manf = 'Carlton';
view可以被认为是relational algebra里面的temp table 如果底层的relation的data变了，那么相关的view的data也会跟着变
EXISTS
Find the beers that are the unique beer by their manufacturer.
SELECT name
FROM Beers b1
WHERE NOT EXISTS
(SELECT *
FROM Beers
WHERE manf = b1.manf
AND name != b1.name
);
思路：把Beers进行self join，然后选出里面所有b1.name != b2.name的部分。如果某个manf只有一个name，那么subquery将不存在这个name；反之，如果某个manf有多个name，那么这个name将存在于subquery内。subquery外部的NOT EXISTS使得全部的unique name被保留下来。
Set Division
Find bars that each sell all of the beers Justin likes.
SELECT DISTINCT a.bar
FROM Sells a
WHERE NOT EXISTS ( (SELECT beer FROM Likes WHERE drinker = 'Justin')
MINUS
(SELECT b.beer FROM Sells b WHERE b.bar = a.bar)
);
思路：第一个select是要选出所有Justin喜欢的酒。第二个select是“给定a.bar，找出a.bar提供的所有酒”。做减法，看看剩下的集合是否为空。如果为空，那么说明第一个select集合内的所有酒都在第二个select集合内。所以这个给定的a.bar符合要求
GROUP-BY
SELECT bar
FROM Sells
WHERE beer = 'New' AND
price <= (SELECT MIN(price)
FROM Sells
WHERE beer = 'New');
