create view roysched_protect_view as select title_id,lorange,hirange from roysched select * from roysched_protect_view --执行视图 with chech option 过滤不合理的记录
create view title_view with encryption as select * from titles ---对视图加密
select distinct country from publishers --返回没有重复结果的过滤信息
select au_lname,ta.title_id,lorange,hirange from authors au,titleauthor ta, roysched_protect_view rpv where au.au_id=ta.au_id and ta.title_id=rpv.title_id
select products.productname,products.unitprice from products,[order details] where products.productid=[order details].productid and products.unitprice=[order details].unitprice
select titles.* from titles,authors
select coll from example where coll like 't[x[xyz]z]' escape 't'
--申明't'为转义字符
--( % ):表示从0到N个任意字符
--( _ ):表示单个任意字符
--( [] ):表示一个没有在括号里列出的任意一个字符
--( [^] ):任意一个没有在括号里列出的字符
select products.productname,suppliers.companyname from products join suppliers on products.supplierid=suppliers.supplierid
select pub_id from publishers union select title from titles where price>20 union select au_lname from authors where au_lname like 'asdfsadsadasda%'
select avg(qty) 'avg_qyp',sum(qty) 'sum_qty',count(distinct stor_id) from sales
select Count(*) from products
select type,pub_id,sum(price) 'sum_price',avg(price) 'avg_price',count(*) from titles where type in ('business','mod_cook','trad_cook') group by type,pub_id
select avg(unitprice),products.productname from products join suppliers on products.supplierid=suppliers.supplierid group by suppliers.supplierid,products.productname having avg(unitprice)>=30
select type,pub_id,avg(price) 'avg_price',sum(price) 'sum_price' from titles where type in('business','trad_cook') group by all type,pub_id
select type,price,advance from titles order by type compute sum(price),sum(advance)
select type,price,advance from titles order by type compute sum(price),sum(advance) by type
select title_id,au_id from titleauthor where au_id in(select au_id from authors where state='ca') order by title_id
select title_id,au_id from titleauthor where exists (select * from authors where authors.au_id=titleauthor.au_id and state='ca') order by title_id
select a.au_lname,t.title_id,'quantity sold'=(select sum(qty) from sales where title_id=t.title_id) from authors a,titles t,titleauthor ta where a.state='ca' and a.au_id=ta.au_id and ta.title_id=t.title_id
select type,title_id,title from titles where titles.title_id in (select title_id from sales where qty>(select avg(qty) from sales))
create view title_view with encryption as select * from titles ---对视图加密
select distinct country from publishers --返回没有重复结果的过滤信息
select au_lname,ta.title_id,lorange,hirange from authors au,titleauthor ta, roysched_protect_view rpv where au.au_id=ta.au_id and ta.title_id=rpv.title_id
select products.productname,products.unitprice from products,[order details] where products.productid=[order details].productid and products.unitprice=[order details].unitprice
select titles.* from titles,authors
select coll from example where coll like 't[x[xyz]z]' escape 't'
--申明't'为转义字符
--( % ):表示从0到N个任意字符
--( _ ):表示单个任意字符
--( [] ):表示一个没有在括号里列出的任意一个字符
--( [^] ):任意一个没有在括号里列出的字符
select products.productname,suppliers.companyname from products join suppliers on products.supplierid=suppliers.supplierid
select pub_id from publishers union select title from titles where price>20 union select au_lname from authors where au_lname like 'asdfsadsadasda%'
select avg(qty) 'avg_qyp',sum(qty) 'sum_qty',count(distinct stor_id) from sales
select Count(*) from products
select type,pub_id,sum(price) 'sum_price',avg(price) 'avg_price',count(*) from titles where type in ('business','mod_cook','trad_cook') group by type,pub_id
select avg(unitprice),products.productname from products join suppliers on products.supplierid=suppliers.supplierid group by suppliers.supplierid,products.productname having avg(unitprice)>=30
select type,pub_id,avg(price) 'avg_price',sum(price) 'sum_price' from titles where type in('business','trad_cook') group by all type,pub_id
select type,price,advance from titles order by type compute sum(price),sum(advance)
select type,price,advance from titles order by type compute sum(price),sum(advance) by type
select title_id,au_id from titleauthor where au_id in(select au_id from authors where state='ca') order by title_id
select title_id,au_id from titleauthor where exists (select * from authors where authors.au_id=titleauthor.au_id and state='ca') order by title_id
select a.au_lname,t.title_id,'quantity sold'=(select sum(qty) from sales where title_id=t.title_id) from authors a,titles t,titleauthor ta where a.state='ca' and a.au_id=ta.au_id and ta.title_id=t.title_id
select type,title_id,title from titles where titles.title_id in (select title_id from sales where qty>(select avg(qty) from sales))
