-------------------------------------------------- --SQL ---------------------------------------------------------------------------------------------------------------------------------------------------- ----- Website: http://sqlzoo.net/ ◎-ddlcreate tablespace / database / role / profile / usercreate Table / View / Indexcreate Procedure / Function / Trigger / Package DROP TABLE TABLENAME Perform more Statements are all transaction
◎ Select * from tab1 where (...); / join subsuerydelete ... Update ... INSERT ◎ GrouPY can be added to add HAVING, because of the use of aggregation
◎ 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 Country with Population 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) = 0select 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.Name) ◎ ProcedureFunctionTrigger (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 ??? .. tah ....
Create Or Replace Procedure PROC () ASBEGIN TYPE; VAR; End Proc; ---- Create Or Replace Function Func () Return Typeis Type; Var; Begin ... Return V; Exception ... End func; Create Or Replace Trigger Trgbefore Update or Delete or Insert Or TB_Namefor EachrowDeclare Type; Var; Begin
End TRG; ---------- Create or Replace Package Pkgis | As Procedure Proc1 (...); Function Func1 (...); ... END PKG;
Create Or Replace Package Body Pkgis | As --define Procute ProCudre Proc1 () IS | AS Begin
End proc1; function func1 (...) Return Type is | as begin ... End func1; begin var: = ...; end pkg; ◎ Pro * c specified SYS_INCLUDE is PROC to find the path CPP_SUFFIX = CC OR CXX or CPP-CODE = CPP-PARSE = None or Partial ◎ EXEC SQL Begin Declare Section
◎ Show Errors Procedure MyProc can display the current compilation error, the statement exists in the database ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ◎ ------------------------------------------------------------------------------------------------------------------------- ---- PL / SQL ------------------------------------------- ---------
-------------------------------------------------- --Pro * C ------------------------------------------------------------------------------------------------------------------------------------ -------
