----------------------------------------------------
sql
----------------------------------------------------
website:http://sqlzoo.net/
◎-DDL
CREATE TABLESPACE/DATABASE/ROLE/PROFILE/USER
CREATE TABLE/VIEW/INDEX
CREATE PROCEDURE/FUNCTION/TRIGGER/PACKAGE(包头,包体)
DROP TABLE tablename
执行以上语句都是一个transaction
◎SELECT * FROM tab1 WHERE (...);/JOIN + SUBQUERY
DELETE ...
UPDATE ...
INSERT
◎groupby 后面可以添上having,因为聚集的使用
◎For each region show the region and number of countries:
select count(*) from cia group by region
For each region show the region and number of countries with populations of at least 10 million.
select count(name),region from cia where population>= 10000000 group by region
Which country has a population that is more than Kenya but less than Canada?
select * from cia where population>(select population from cia where name ='Kenya') and population<(select population from cia where name ='Canada')
Which countries have a GDP greater than any European country? [Give the name only.]
select * from cia where gdp> (select max(gdp) from cia where region like 'Europe')
select * from cia where gdp> all(select gdp from cia where region like 'Europe')
Find the region for which all countries have a population of 0
select distinct region from cia x where (select sum(population) from cia y where x.region=y.region)=0
select sum(population),region from cia group by region having sum(population)=0
Some countries have populations more than three times that of any of their nieghbours (in the same region). Give the countries and regions.
select region from cia x where population> (select max(3*population) from cia y where x.region=y.region and x.name<>y.name )
◎
procedure
function
trigger
(declare)
cursor c_stu IS
query;
var:
type;
...
begin
open c_stu ;
loop
fetch c_stu into v1,v2,...
exit when c_stu%notfound;
....
end loop;
close c_stu;
exception
when ???
..
others
....
end;
create or replace procedure proc()
as
begin
type;
var;
end proc;
----
create or replace function func() return type
is
type;
var;
begin
...
return v;
exception
...
end func;
----
create or replace trigger trg
before update or delete or insert or tb_name
for eachrow
declare
type;
var;
begin
end trg;
----------
create or replace package pkg
is|as
procedure proc1(...);
procedure proc2(...);
function func1(...);
...
end pkg;
create or replace package body pkg
is|as
--define procdure
procudre proc1()
is|as
begin
end proc1;
function func1(...) return type
is|as
begin
...
end func1;
begin
var:=...;
end pkg;
◎pro*c所指定的sys_include是proc会去查找的路径
cpp_suffix=cc or cxx or cpp
-code=cpp
-parse=none or partial
◎EXEC SQL BEGIN DECLARE SECTION
◎show errors procedure myproc可以显示当前编译的错误,该语句存在数据库中
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
◎
----------------------------------------------------
pl/sql
----------------------------------------------------
----------------------------------------------------
pro*c
----------------------------------------------------