oracle 9i

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle 9i Introduction to SQL

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

KALIYAPERUMAL SEKAR

 

kaliyaperumal.sekar@igate.com

 

 

 

Contents

 

1.   BasicSQL SELECTStatements

 

BasicSELECTStatement SelectingAllColumns SelectingSpecificColumns WritingSQLStatements DefiningaColumnAlias

2.   RestrictingandSorting Data

 

LimitingtheRowsSelected UsingtheWHEREClause CharacterStringsandDates ComparisonConditions

OtherComparisonConditions UsingtheBETWEENCondition UsingtheINCondition

UsingtheLIKECondition UsingtheNULLConditions LogicalConditions

UsingtheANDOperator UsingtheOROperator UsingtheNOTOperator ORDERBYClause

Sortingin DescendingOrder SortingbyColumnAlias SortingbyMultipleColumns

3.   Single-RowFunctions Single-RowFunctions CharacterFunctions

CaseManipulationFunctions Character-ManipulationFunctions NumberFunctions

DateFunctions

 

 

 

ConversionFunctions

 

GeneralFunctions

 

4.   DisplayingDatafromMultipleTables

 

ObtainingDatafromMultipleTables JoiningTablesUsingOracleSyntax TypesofJoins

Equijoins Nonequijoins OuterJoins

OuterJoinRestrictions

 

SelfJoins CrossJoins NaturalJoins

LEFTOUTERJOIN RIGHTOUTERJOIN FULLOUTERJOIN

5.   Subqueries

 

SubquerySyntax

 

GuidelinesforUsingSubqueries

 

TypesofSubqueries

 

UsingGroupFunctionsin aSubqueryTheHAVINGClausewithSubqueries Multiple-rowsubqueries

6.   AggregatingDataUsingGroupFunctions

 

GroupFunctions

 

TypesofGroupFunction

 

GroupFunctionsSyntax

 

UsingtheAVGandSUMFunctions UsingtheMINandMAXFunctions UsingtheCOUNTFunction CreatingGroupsofData

UsingtheGROUPBYClauseonMultipleColumns

 

UsingtheHAVINGClause

 

 

 

7.   CreatingandManagingTables

 

DatabaseObjects

 

CreatingTables

 

Tablesin theOracleDatabase

 

Creatinga TablebyUsinga SubquerySyntax

 

TheALTERTABLEStatement

 

AddingaColumn

 

GuidelinesforAddingaColumn

 

Modifyinga Column DroppingaColumn DroppingaTable RenamingaTable TruncatingaTable

8.   ManipulatingDataAdding a NewRowtoaTableInserting RowswithNullValuesCreating aScript

CopyingRowsfromAnotherTable

 

ChangingDatain aTable

 

UpdatingTwoColumnswithaSubquery

 

RemovingaRowfromaTable

 

TheMERGEStatement

 

9.   Constraints

 

DefiningConstraints

 

TheNOTNULLConstraint

 

TheUNIQUEConstraint

 

ThePRIMARYKEYConstraint TheFOREIGNKEYConstraint TheCHECKConstraint

AddingaConstraint

 

DroppingaConstraint

 

 

 

10. OtherDatabaseObjects

 

Views

 

SimpleViewsversusComplexViews RetrievingDatafromaView RemovingaView

Sequence

 

UsingaSequence Modifyinga Sequence RemovingaSequence Index

CreatinganIndex RemovinganIndex Synonyms

Dropasynonym

 

 

 

Overview

 

 

 

Thefoundation forallOracleprofessionalsistheStructuredQueryLanguage (SQL). UsingSQL,youcancommunicatewiththeOracleserver.Theadvantagesofthe SQLare,

 

•  Efficient

 

•  Easytolearnanduse

 

•      Functionallycomplete(WithSQL,youcandefine,retrieve,andmanipulate datainthetables.)

 

 

 

OracleSQLcomplieswithindustry-acceptedstandards.OracleCorporationensures futurecompliancewithevolvingstandardsbyactivelyinvolvingkeypersonnelinSQL standardscommittees. IndustryacceptedcommitteesaretheAmericanNational StandardsInstitute(ANSI)andtheInternational StandardsOrganization (ISO).Both ANSI and ISO have accepted SQL as the standard  language for relational databases.

 

 

 

A detailed considerationof this language  includes topics such as basic select statements, restrictingandsortingthedataretrievedbythequery,varioustypeof singlerowfunctions,tablejoins,subqueries, GroupfunctionandHavingclause, creatingandmaintainingtabledefinitionsin  thedatadictionary,specifyingdata types,manipulating tablesanddevelopingsophisticated queriesquicklyand accurately,constraintsandadvanceddatabaseobjects.

 

 

 

Aftercompletingthesetopics,youshouldbeabletogetadetailedknowledgeabout

StructuredQueryLanguageandthedifferentfunctionalitiesthislanguageoffers.

 

 

 

1.BasicSQL SELECTStatements

 

SQLStatements

 

•  OracleSQLcomplieswithindustry-acceptedstandards.

 

•     ANSIandISOhaveacceptedSQLasthestandardlanguageforrelational databases.

 

 

SQLCommand	Category
SELECT	DataRetrieval
INSERT,UPDATE,DELETE,MERGE	DataManipulationLanguage(DML)
CREATE,ALTER,DROP,RENAME TRUNCATE	Datadefinitionlanguage(DDL)
COMMIT,TROLLBACK,SAVEPOINT	Transactioncontrol
GRANT,REVOKE	Datacontrollanguage(DCL)

 

 

Statement	Description
SELECT	Retrievesdatafromthedatabase
INSERT       UPDATE DELETE      MERGE	Entersnewrows,changes existingrows,and Removes unwanted rowsfromtablesinthe database,respectively. Collectivelyknownas Datamanipulationlanguage(DML).
CREATE         ALTER DROP RENAME        TRUNCATE	Set up, change,  and remove data structures from tablesCollectivelyknownasDataDefinition Language(DDL).
COMMIT          ROLLBACK SAVEPOINT	Manage thechanges madebyDMLstatements. Changestothedatacanbegroupedtogether intologicaltransactions.
GRANT          REVOKE	Giveorremove access rightstoboththeOracle databaseand thestructureswithinit.Collectively knownasDataControlLanguage(DCL).

 

 

WritingSQLStatements

 

•  SQLstatementsarenotcasesensitive.

 

•  SQLstatementscanbeononeormorelines.

 

•  Keywordscannotbeabbreviatedorsplitacrosslines.

 

 

 

 

 

BasicSELECTStatement

 

SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable;

 

 

 

Initssimplestform,aSELECTstatementmustincludethefollowing:

 

•  ASELECTclause,whichspecifiesthecolumnstobedisplayed

 

-SELECTidentifieswhatcolumns

 

•    AFROMclause,whichspecifiesthetablecontainingthecolumnslistedin theSELECTclause

 

-FROMidentifieswhichtable

 

Inthesyntax:

 

SELECT                    isalistofoneormorecolumns

 

*                                 selectsall columns

 

DISTINCT                 suppressesduplicates

 

column|expressionselectsthenamedcolumnortheexpression alias                          givesselectedcolumnsdifferentheadings FROMtable              specifiesthetablecontainingthecolumns

 

 

Note:Throughoutthis course,the wordskeyword,clause,andstatementare usedas follows.

 

•  AkeywordreferstoanindividualSQLelement.

 

Forexample,SELECTandFROMarekeywords.

 

•  AclauseisapartofaSQLstatement.

 

Forexample,SELECTemployee_id,last_name,... isaclause.

 

•  Astatementisacombinationoftwoormoreclauses.

 

Forexample,SELECT* FROMemployeesisaSQLstatement.

 

 

 

Example:

 

The     department     table     contains     four     columns     DEPARTMENT_ID, DEPARTMENT_NAME,MANAGER_IDandLOCATION_ID.

 

 

 

SelectingAllColumns:

 

SELECT*FROMdepartments;

 

 

 

SelectingSpecificColumns:

 

SELECTdepartment_id,location_idFROMdepartments;

 

 

 

DisplayingTableStructure

 

DESC[RIBE]tablename;

 

 

 

Example:

 

DESCRIBEemployees;

 

 

 

DefiningaColumnAlias

 

Acolumnalias:

 

•  Renamesacolumnheading

 

•  Isusefulwithcalculations

 

•  Immediatelyfollowsthecolumnname:therecanalsobetheoptionalAS

keywordbetweenthecolumnnameandalias.

 

•     Requiresdoublequotationmarksifitcontainsspacesorspecialcharactersor iscasesensitive.

 

 

 

Example:

 

SELECTlast_nameASname,commission_pctcomm

 

FROMemployees;

 

 

 

SELECTlast_name"Name",salary*12"AnnualSalary" FROMemployees;

 

 

 

2.RestrictingandSorting Data

 

•  Limittherowsretrievedbyaquery

 

•  Sorttherowsretrievedbyaquery

 

 

 

LimitingtheRowsSelected:

 

RestricttherowsreturnedbyusingtheWHEREclause: SELECT*|{[DISTINCT]column|expression[alias],...} FROMtable[WHEREcondition(s)];

 

 

TheWHEREclausefollowstheFROMclause. Inthesyntax:

WHERE         restrictsthequerytorowsthatmeetacondition

 

condition          iscomposedofcolumnnames,expressions,constants,anda comparisonoperator

 

The WHERE clause can compare values in columns, literal values, arithmetic expressions,orfunctions.It consistsofthreeelements:

 

•  Columnname.

 

•  Comparisoncondition.

 

•  Columnname,constant,orlistof values.

 

 

 

UsingtheWHEREClause:

 

SELECTemployee_id,last_name,job_id,department_idFROMemployees

WHEREdepartment_id=90;

 

 

 

CharacterStringsandDates:

 

•  Characterstringsanddatevaluesareenclosedinsinglequotationmarks.

 

•  Charactervaluesarecasesensitive,anddatevaluesareformatsensitive.

ThedefaultdateformatisDD-MON-RR.

 

SELECTlast_name,job_id,department_idFROMemployees

 

WHERElast_name='Goyal';

 

 

 

ComparisonConditions

 

Comparison  conditions are used inconditions that compare one expression  to anothervalueorexpression. TheyareusedintheWHEREclauseinthefollowing format:

 

... WHEREexproperatorvalue

 

Operators:=, >, >=, <, <=, <> Example:

... WHEREhire_date='01-JAN-95'

 

... WHEREsalary>=6000

 

... WHERElast_name='Smith'

 

AnaliascannotbeusedintheWHEREclause.

 

 

 

Note:Thesymbol!= and^=canalsorepresentthenotequaltocondition.

 

SELECTlast_name,salaryFROMemployeesWHEREsalary<=3000;

 

 

 

OtherComparisonConditions:

 

BETWEEN…AND…          Betweentwovalues(inclusive) IN(Set)                                 Matchanyofalistofvalues. LIKE                                      Matchacharacterpattern.

IS NULL                                Isanullvalues.

 

 

 

UsingtheBETWEENCondition:

 

UsetheBETWEENconditiontodisplayrowsbasedonarangeofvalues.

 

SELECTlast_name,salaryFROMemployeesWHEREsalaryBETWEEN

2500AND3500;

 

 

 

UsingtheINCondition:

 

UsetheINmembershipconditiontotestforvaluesinalist.

 

SELECT employee_id, last_name, salary, manager_idFROM employees

WHEREmanager_idIN(100,101,201);

 

 

 

UsingtheLIKECondition:

 

UsetheLIKEconditiontoperformwildcardsearchesofvalidsearchstringvalues. Searchconditionscancontaineitherliteralcharactersornumbers:

%denoteszeroormanycharacters.

 

_denotesonecharacter.

 

SELECTfirst_nameFROMemployeesWHEREfirst_nameLIKE'S%';

 

 

 

UsingtheNULLConditions:

 

TestfornullswiththeIS NULLoperator.

 

SELECTlast_name,manager_idFROMemployeesWHEREmanager_idIS NULL;

 

 

 

LogicalConditions:

 

Alogicalconditioncombinestheresultoftwocomponentconditionstoproducea singleresultbasedonthemorinvertstheresultofa singlecondition.

 

Arowisreturnedonlyiftheoverallresultoftheconditionistrue.Threelogical operatorsareavailableinSQL:

 

AND   ReturnsTRUEif bothcomponentconditionsaretrue OR     ReturnsTRUEif eithercomponentconditionistrue NOT   ReturnsTRUEif thefollowingconditionisfalse

 

 

UsingtheANDOperator

 

•  ANDoperatorrequiresbothconditionstobetrue.

 

SELECTemployee_id,last_name,job_id,salaryFROMemployees

 

WHEREsalary>=10000ANDjob_idLIKE'%MAN%';

 

 

 

UsingtheOROperator

 

ORoperatorrequireseitherconditiontobetrue.

 

SELECTemployee_id,last_name,job_id,salaryFROMemployees

 

WHEREsalary>=10000ORjob_idLIKE'%MAN%';

 

 

 

UsingtheNOTOperator

 

SELECTlast_name,job_idFROMemployees

 

WHEREjob_idNOTIN ('IT_PROG','ST_CLERK','SA_REP');

 

 

 

ORDERBYClause

 

SortrowswiththeORDERBYclause

 

•  ASC:ascendingorder(thedefaultorder)

 

•  DESC:descendingorder

 

TheORDERBYclausecomeslastintheSELECTstatement.

 

SELECT last_name, job_id, department_id, hire_dateFROM employees

ORDERBYhire_date;

 

 

 

SortinginDescendingOrder:

 

SELECT last_name, job_id, department_id, hire_dateFROM employees

ORDERBYhire_dateDESC;

 

 

 

SortingbyColumnAlias:

 

SELECT employee_id, last_name, salary*12 annsalFROM employees

ORDERBYannsal;

 

 

 

SortingbyMultipleColumns:

 

SELECTlast_name,department_id,salaryFROMemployeesORDERBY

department_id,salaryDESC;

 

 

 

3.Single-RowFunctions

 

•  DescribevarioustypesoffunctionsavailableinSQL.

 

•  Usecharacter,number,anddatefunctionsinSELECTstatements.

 

•  Describetheuseofconversionfunctions.

 

 

 

Single-RowFunctions:

 

Characterfunctions:Acceptcharacterinputandcanreturnbothcharacterand numbervalues.

 

Numberfunctions:Acceptnumericinputandreturnnumericvalues.

 

Datefunctions: OperateonvaluesoftheDATEdatatype(Alldatefunctionsreturna valueofDATEdatatypeexcepttheMONTHS_BETWEEN function, whichreturnsanumber.)

 

Conversionfunctions:Convertavaluefromonedatatypetoanother. Generalfunctions:

•  NVL

 

•  NVL2

 

•  DECODE

 

 

 

CharacterFunctions:

 

Single-rowcharacterfunctionsacceptcharacterdataasinputandcanreturnboth characterandnumericvalues.Characterfunctionscanbedividedintothefollowing:

 

•  Case-manipulationfunctions.

 

•  Character-manipulationfunctions.

 

 

 

CaseManipulationFunctions:

 

Thesefunctionsconvertcaseforcharacterstrings.

 

LOWER('SQLCourse')                     sqlcourse UPPER('SQLCourse')                     SQLCOURSE INITCAP('SQLCourse')                    SqlCourse

 

 

•  LOWER:Convertsmixedcaseoruppercasecharacterstringstolowercase.

 

•  UPPER:Convertsmixedcaseorlowercasecharacterstringstouppercase.

 

 

 

•     INITCAP:Convertsthefirstletterofeachwordtouppercaseandremaining letterstolowercase.

 

 

•    SELECT 'The job id for '||UPPER(last_name)||' is‘ ||LOWER(job_id) AS "EMPLOYEEDETAILS“FROMemployees;

 

 

Result-ThejobidforKINGisad_pres

 

 

Character-ManipulationFunctions:

 

Thesefunctionsmanipulatecharacterstrings:

 

 

CONCAT('Hello','World')                 HelloWorld SUBSTR('HelloWorld',1,5)               Hello LENGTH('HelloWorld')10

INSTR('HelloWorld','W')                  6

 

LPAD(salary,10,'*')                           *****24000

 

RPAD(salary,10,'*')                         24000***** TRIM('H'FROM'HelloWorld')            elloWorld

 

 

•  CONCAT:Joinsvaluestogether

 

•  SUBSTR:Extractsastringofdeterminedlength

 

•  LENGTH:Showsthelengthofastringasanumericvalue

 

•  INSTR:Findsnumericpositionofanamedcharacter

 

•  LPAD:Padsthecharactervalueright-justified

 

•  RPAD:Padsthecharactervalueleft-justified

 

•     TRIM: Trimsheadingortrailing characters(orboth)fromacharacterstring(If trim_characterortrim_sourceisacharacterliteral,youmustenclose

itinsinglequotes.)

 

 

NumberFunctions:

 

 

Numberfunctionsacceptnumericinputandreturnnumericvalues.Thissection describessomeofthenumberfunctions.

 

 

 

Function	Purpose
ROUND(column|expression, n)	Rounds thecolumn, expression,  or value to n decimalplaces,or,if nisomitted,nodecimalplaces. (Ifnisnegative, numbers toleftofthedecimalpoint arerounded.)
TRUNC(column|expression,n)	Truncatesthecolumn,expression, orvalueton decimal places,or,ifnisomitted, thenndefaults to zero
MOD(m,n)	Returnstheremainderofmdividedbyn

 

 

 

Note:Thislistcontainsonlysomeoftheavailablenumberfunctions.

 

 

 

ROUND:Roundsvaluetospecifieddecimal

 

ROUND(45.926,2)-45.93

 

TRUNC:Truncatesvaluetospecifieddecimal

 

TRUNC(45.926,2)-45.92

 

MOD:Returnsremainderofdivision

 

MOD(1600,300)–100

 

 

 

DateFunctions:

 

DatefunctionsoperateonOracledates.AlldatefunctionsreturnavalueofDATE

datatypeexceptMONTHS_BETWEEN,whichreturnsanumericvalue.

 

 

MONTHS_BETWEEN	Numberof monthsbetweentwodates
ADD_MONTHS	Addcalendarmonthstodate
NEXT_DAY	Nextdayof thedatespecified
LAST_DAY	Lastdayofthemonth
ROUND	Rounddate
ROUND	Rounddate

 

 

•     MONTHS_BETWEEN(date1,date2):Findsthenumberofmonthsbetween date1anddate2.Theresultcanbepositiveornegative.Ifdate1islaterthan

 

 

 

date2, the result is positive; if date1 is earlier than date2, the result is negative.Thenonintegerpartoftheresultrepresentsaportionofthemonth.

 

•    ADD_MONTHS(date,n):Addsnnumberofcalendarmonthstodate.The valueofnmustbeanintegerandcanbenegative.

 

•     NEXT_DAY(date,'char'):Findsthedateofthenextspecifieddayoftheweek ('char')followingdate.The valueofchar may beanumberrepresentingaday oracharacterstring.

 

•    LAST_DAY(date):Findsthedateofthelastdayofthemonththatcontains date.

 

•     ROUND(date[,'fmt']):Returnsdateroundedtotheunitspecified bytheformat modelfmt.Iftheformatmodelfmtisomitted, dateisrounded tothenearest day.

 

•     TRUNC(date[,'fmt']):Returnsdatewiththetimeportionofthedaytruncated totheunitspecifiedbytheformatmodelfmt.Iftheformatmodelfmtis omitted,dateistruncatedtothenearestday.

 

 

 

Examples:

 

1.MONTHS_BETWEEN('01-SEP-95','11-JAN-94')-19.6774194

 

2.ADD_MONTHS('11-JAN-94',6)-'11-JUL-94'

 

3.NEXT_DAY('01-SEP-95','FRIDAY')-'08-SEP-95'

 

4.LAST_DAY('01-FEB-95')-'28-FEB-95'

 

 

 

AssumeSYSDATE='25-JUL-95':

 

5.ROUND(SYSDATE,'MONTH')- 01-AUG-95

 

6.ROUND(SYSDATE,'YEAR')-01-JAN-96

 

7.TRUNC(SYSDATE,'MONTH')-01-JUL-95

 

8.TRUNC(SYSDATE,'YEAR')-01-JAN-95

 

 

 

ConversionFunctions:

 

 

Function	Purpose
TO_CHAR(number|date, [ fmt],[nlsparams])	Converts anumberordatevaluetoa VARCHAR2 characterstringwithformatmodel fmt.

 

 

 

TO_NUMBER(char,[fmt], [nlsparams])	Convertsacharacterstringcontainingdigitsto anumberintheformatspecifiedbytheoptional formatmodelfmt.
TO_DATE(char,[fmt],[nlsparams])	Convertsacharacterstringrepresenting adate toadatevalueaccording tothefmtspecified.If fmtisomitted,theformatisDD-MON-YY.

 

 

 

Examples:

 

1.TO_CHAR(hire_date,'MM/YY')-06/07

 

2.TO_CHAR(salary,'$99,999.00')-$10,000.00

 

3.TO_DATE(’27-SEP-2007’,’MM/DD/YY’)-09/27/07

 

4.TO_NUMBER(‘1000’,’09999’)- 01000

 

 

 

GeneralFunctions:

 

•  NVL(expr1,expr2)

 

•  NVL2(expr1,expr2,expr3)–(expr1is)notnullexpr2–nullexrpr3

 

 

 

4.DisplayingDatafrommultipletables

 

•    WriteSELECTstatementstoaccessdatafrommorethanonetableusing equalityandnon-equalityjoins.

 

•  Viewdatathatgenerallydoesnotmeetajoinconditionbyusingouterjoins.

 

•  Joinatabletoitselfbyusingaselfjoin.

 

ObtainingDatafromMultipleTables:

 

Sometimesyouneedtousedatafrommorethanonetable.Intheexample,the reportdisplaysdatafromtwoseparatetables.

 

Forexample:

 

•  EmployeeIDsexistintheEMPLOYEEStable.

 

•  Department IDsexist in both the EMPLOYEES and DEPARTMENTS

tables.

 

•  LocationIDsexistintheDEPARTMENTStable.

 

 

 

Toproducethereport,youneedtolinktheEMPLOYEESandDEPARTMENTS

tablesandaccessdatafrombothofthem.

 

 

JoiningTablesUsingOracleSyntax:

 

Useajointoquerydatafrommorethanonetable.

 

SELECTtable1.column,table2.column

 

FROMtable1,table2

 

WHEREtable1.column1=table2.column2;

 

•  WritethejoinconditionintheWHEREclause.

 

•     Prefixthecolumnnamewiththetablenamewhenthesamecolumnname appearsinmorethanonetable.

 

Guidelines:

 

•     WhenwritingaSELECTstatementthatjoinstables,precedethecolumn namewiththetablenameforclarityandtoenhancedatabaseaccess.

 

•     Ifthesamecolumnnameappearsinmorethanonetable,thecolumnname mustbeprefixedwiththetablename.

 

•    Tojoinntablestogether,youneedaminimumofn-1joinconditions.For example,to joinfourtables,aminimumofthreejoinsisrequired.

 

 

 

TypesofJoins:

 

•  Equijoin

 

•  Nonequijoin

 

•  Outerjoin

 

•  Selfjoin

 

•  Crossjoins

 

•  Naturaljoins

 

•  Fullortwosidedouterjoins

 

 

 

Equijoins:

 

Todetermineanemployee’s department name,youcomparethevalueinthe DEPARTMENT_ID columnintheEMPLOYEEStablewiththeDEPARTMENT_ID valuesin theDEPARTMENTStable.TherelationshipbetweentheEMPLOYEESand DEPARTMENTStables  isan equijoin,that is, values  in the DEPARTMENT_ID columnonbothtablesmustbeequal.Frequently, thistypeofjoininvolvesprimary andforeignkeycomplements.

 

Note:Equijoinsarealsocalledsimplejoinsorinnerjoins

 

SELECTemployees.employee_id,employees.last_name, employees.department_id,departments.department_id, departments.location_idFROMemployees,departments

WHEREemployees.department_id=departments.department_id;

 

 

 

UsingTableAliases:

 

•  Simplifyqueriesbyusingtablealiases

 

•  Improveperformancebyusingtableprefixes

 

SELECTe.employee_id,e.last_name,e.department_id, d.department_id,d.location_id

FROMemployeese,departmentsd

 

WHEREe.department_id=d.department_id;

 

 

 

Nonequijoins:

 

A nonequijoinis a join condition containing something other than an equality operator.TherelationshipbetweentheEMPLOYEEStableandtheJOB_GRADES

 

 

 

tablehasanexampleofanonequijoin. Arelationshipbetweenthetwotablesisthat theSALARYcolumnintheEMPLOYEES tablemustbebetweenthevaluesinthe LOWEST_SALARY andHIGHEST_SALARY columnsoftheJOB_GRADEStable. Therelationshipisobtainedusinganoperatorotherthanequals(=).

 

 

 

SELECTe.last_name,e.salary,j.grade_level

 

FROMemployeese,job_gradesj

 

WHEREe.salaryBETWEENj.lowest_salANDj.highest_sal; Example-      KP     $20,000         A        $10,000$25,000

OuterJoins:

 

Ifarowdoesnotsatisfyajoincondition, therowwillnotappearinthequeryresult. Forexample,intheequijoinconditionofEMPLOYEESandDEPARTMENTStables, employeeGrantdoesnotappearbecausethereisnodepartmentIDrecordedfor her intheEMPLOYEEStable.Insteadofseeing20employeesintheresultset,yousee

19records.

 

SELECTe.last_name,e.department_id,d.department_name

 

FROMemployeese,departmentsd

 

WHEREe.department_id=d.department_id;

 

 

 

•   Youuseanouterjointoalsoseerowsthatdonotmeetthejoincondition.

 

•  Theouterjoinoperatoristheplussign(+).

 

 

 

SELECTtable1.column,table2.column

 

FROMtable1,table2

 

WHEREtable1.column(+)=table2.column;

 

 

 

SELECTtable1.column,table2.column

 

FROMtable1,table2

 

WHEREtable1.column=table2.column(+);

 

 

 

OuterJoinRestrictions:

 

Theouterjoinoperatorcanappearononlyonesideoftheexpression: thesidethat hasinformationmissing.It returnsthoserowsfromonetablethathasnodirectmatch intheothertable.

 

 

 

SelfJoins:

 

Sometimes youneedtojoinatabletoitself.Tofindthenameofeachemployee’s manager,youneedtojointheEMPLOYEEStabletoitself,orperform aselfjoin.For example,to findthenameofWhalen’smanager,youneedto:

 

 

 

•  Find Whalen in the EMPLOYEES table by looking at the LAST_NAME

column.

 

•  FindthemanagernumberforWhalenbylookingattheMANAGER_ID

column.Whalen’smanagernumberis101.

 

•    FindthenameofthemanagerwithEMPLOYEE_ID101bylookingatthe LAST_NAME column.Kochhar’semployeenumberis101,soKochharis Whalen’smanager.

 

Inthisprocess, youlookinthetabletwice.Thefirsttimeyoulookinthetabletofind WhalenintheLAST_NAME columnandMANAGER_ID valueof101.Thesecond timeyoulookintheEMPLOYEE_IDcolumntofind101andtheLAST_NAMEcolumn tofindKochhar.

 

 

 

SELECTworker.last_name|| ' worksfor'

 

||manager.last_name

 

FROMemployeesworker,employeesmanager

 

WHEREworker.manager_id=manager.employee_id;

 

 

 

CrossJoins:

 

TheCROSSJOINclauseproducesthecrossproductoftwotables.

 

SELECTlast_name,department_nameFROMemployees

 

CROSSJOINdepartments;

 

 

 

NaturalJoins:

 

•    TheNATURALJOINclauseisbasedonallcolumnsinthetwotablesthat havethesamename.

 

 

 

•     Itselectsrowsfromthetwotablesthathaveequalvaluesinallmatched columns.

 

•     Ifthecolumnshavingthesamenameshavedifferentdatatypes,thenan errorisreturned.

 

 

 

SELECTdepartment_id,department_name,location_id,city

 

FROMdepartmentsNATURALJOINlocations;

 

 

 

ONClause: CreatingJoinswiththeONClause. Thejoinconditionforthenatural join isbasicallyanequijoinofallcolumnswiththesamename.

 

 

 

LEFTOUTERJOIN:

 

ThisqueryretrievesallrowsintheEMPLOYEEStable,whichisthelefttableevenif thereisnomatchintheDEPARTMENTStable.

 

SELECTe.last_name,e.department_id,d.department_name

 

FROMemployeeseLEFTOUTERJOINdepartmentsd

 

ON(e.department_id=d.department_id);

 

 

 

RIGHTOUTERJOIN:

 

ThisqueryretrievesallrowsintheDEPARTMENTS table,whichistherighttable evenif thereisnomatchintheEMPLOYEEStable.

 

SELECTe.last_name,e.department_id,d.department_name

 

FROMemployeeseRIGHTOUTERJOINdepartmentsd

 

ON(e.department_id=d.department_id);

 

 

 

FULLOUTERJOIN:

 

ThisqueryretrievesallrowsintheEMPLOYEEStable,evenifthereisnomatchin theDEPARTMENTS table.ItalsoretrievesallrowsintheDEPARTMENTS table, evenif thereisnomatchintheEMPLOYEEStable.

 

SELECTe.last_name,e.department_id,d.department_name

 

FROMemployeeseFULLOUTERJOINdepartmentsd

 

ON(e.department_id=d.department_id);

 

 

 

5.Subqueries

 

•  Definesubqueries

 

•  Listthetypesofsubqueries

 

•  Writesingle-rowandmultiple-rowsubqueries

 

SubquerySyntax:

 

SELECTselect_listFROMtable

 

WHEREexproperator(SELECTselect_listFROMtable);

 

 

 

•  Thesubquery(innerquery)executesoncebeforethemainquery.

 

•  Theresultofthesubqueryisusedbythemainquery(outerquery).

 

 

 

YoucanplacethesubqueryinanumberofSQLclauses,including:

 

•  TheWHEREclause

 

•  TheHAVINGclause

 

•  TheFROMclause

 

 

 

Inthesyntax:

 

Operatorincludesacomparisonconditionsuchas>, =, orIN

 

Note:Comparisonconditionsfallintotwoclasses:single-rowoperators(>,=,>=,<,

<>, <=)andmultiplerowoperators(IN,ANY,ALL).

 

Example:

 

SELECTlast_nameFROMemployees

 

WHEREsalary>(SELECTsalaryFROMemployees

 

WHERElast_name='Abel');

 

 

 

GuidelinesforUsingSubqueries:

 

•  Enclosesubqueriesin parentheses.

 

•  Placesubqueriesontherightsideofthecomparisoncondition.

 

•  Usesingle-rowoperatorswithsingle-rowsubqueriesandusemultiple-row

 

•  Operatorswithmultiple-rowsubqueries.

 

 

 

TypesofSubqueries:

 

•  Single-row subqueries:Queries that return only one row from the inner

SELECTstatement. Example:

DisplaytheemployeeswhosejobIDisthesameasthatofemployee141.

 

SELECTlast_name,job_idFROMemployees

 

WHEREjob_id=(SELECTjob_idFROMemployees

 

WHEREemployee_id=141);

 

 

 

-    UsingGroupFunctionsinaSubquery:

 

Youcandisplaydatafromamainquery byusingagroupfunction ina subquerytoreturnasinglerow.Thesubqueryisinparentheses andis placedafterthecomparisoncondition.

 

SELECTlast_name,job_id,salaryFROMemployees

 

WHEREsalary=(SELECTMIN(salary)FROMemployees);

 

 

 

-    TheHAVINGClausewithSubqueries:

 

Youcanusesubqueries notonlyintheWHEREclause,butalsointhe HAVINGclause.TheOracleServerexecutesthesubquery, andthe resultsarereturnedintotheHAVINGclauseofthemainquery.

 

 

 

TheSQLstatementontheslidedisplaysallthedepartmentsthathavea minimumsalarygreaterthanthatofdepartment50.

 

 

 

SELECTdepartment_id,MIN(salary)FROMemployees

 

GROUPBYdepartment_id

 

HAVINGMIN(salary)>(SELECTMIN(salary)FROMemployees

 

WHEREdepartment_id=50);

 

 

 

•     Multiple-rowsubqueries:Queriesthatreturnmorethanonerowfromthe innerSELECTstatement.

 

Findtheemployeeswhoearnthesamesalaryastheminimumsalaryfor eachdepartment.

 

SELECTlast_name,salary,department_id

 

 

 

FROMemployees

 

WHEREsalaryIN(SELECTMIN(salary)FROMemployees

 

GROUPBYdepartment_id);

 

 

 

6.AggregatingDataUsingGroupFunctions

 

 

 

•  Identifytheavailablegroupfunctions.

 

•  Describetheuseofgroupfunctions.

 

•  GroupdatausingtheGROUPBYclause.

 

•  IncludeorexcludegroupedrowsbyusingtheHAVINGclause.

 

GroupFunctions:

 

Groupfunctionsoperateonsetsofrowstogiveoneresultpergroup. Syntax:

SELECT[column,]group_function(column),... FROMtable

[WHEREcondition] [GROUPBYcolumn] [ORDERBYcolumn];

 

 

•  TypesofGroupFunction:

 

-     AVG

 

-     COUNT

 

-     MAX

 

-     MIN

 

-     SUM

 

 

AVG([DISTINCT|ALL]n)	Averagevalueofn,ignoringnullvalues
COUNT({*|[DISTINCT|ALL]e xpr})	Numberofrows,whereexprevaluates to something otherthannull(countall selected  rows using *, including duplicatesandrowswithnulls)
MAX([DISTINCT|ALL]expr)	Maximum value of expr, ignoring null

 

 

 

	values
MIN([DISTINCT|ALL]expr)	Minimum value of expr, ignoring null values
SUM([DISTINCT|ALL]n)	Sumvaluesofn,ignoringnullvalues

 

 

-    UsingtheAVGandSUMFunctions:

 

YoucanuseAVGandSUMfornumericdata.

 

SELECTAVG(salary),SUM(salary) FROMemployees

WHEREjob_idLIKE'%REP%';

 

 

 

-    UsingtheMINandMAXFunctions:

 

YoucanuseMINandMAXforanydatatype.

 

SELECTMIN(hire_date),MAX(hire_date) FROMemployees;

Note:AVG,SUMfunctionscanbeusedonlywithnumericdatatypes.

 

 

-    UsingtheCOUNTFunction:

 

COUNT(*)returnsthenumberofrowsinatable.

 

SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=50;

 

 

 

CreatingGroupsofData:

 

DividerowsinatableintosmallergroupsbyusingtheGROUPBYclause. Syntax:

SELECTcolumn,group_function(column) FROMtable

[WHEREcondition]

 

[GROUPBYgroup_by_expression] [ORDERBYcolumn];

 

 

 

Guidelines:

 

•  YoumustincludethecolumnsintheGROUPBYclause.

 

•  YoucannotuseacolumnaliasintheGROUPBYclause.

 

•  Bydefault,rows aresortedbyascendingorder ofthecolumnsincludedinthe

GROUPBYlist.

 

SELECTdepartment_id,AVG(salary)

 

FROMemployeesGROUPBYdepartment_id;

DEPARTMENT_ID	AVG(SALARY)
50	3500
10	4400

 

Error!

 

WhenusingtheGROUP BYclause,makesurethatallcolumns intheSELECT list thatarenotgroupfunctions areincludedintheGROUPBYclause.Theexample in the slide displays the department  number and the average salary for each department.

 

HereishowthisSELECTstatement,containingaGROUPBYclause,isevaluated:

 

•TheSELECTclausespecifiesthecolumnstoberetrieved:

 

-    ThedepartmentnumbercolumnintheEMPLOYEEStable.

 

-      TheaverageofallthesalariesinthegroupyouspecifiedintheGROUP BYclause.

 

•  TheFROMclausespecifiesthetablesthatthedatabasemustaccess:the

EMPLOYEEStable.

 

•  TheWHEREclausespecifiestherowstoberetrieved.Becausethereisno

WHEREclause,allrowsareretrievedbydefault.

 

•    TheGROUPBYclausespecifies howtherowsshouldbegrouped. Therows arebeinggroupedbydepartment number,sotheAVGfunctionthatisbeing appliedtothesalarycolumnwillcalculate theaveragesalaryforeach department.

 

 

 

UsingtheGROUPBYClauseonMultipleColumns: SELECTdepartment_iddept_id,job_id,SUM(salary) FROMemployees

GROUPBYdepartment_id,job_id;

 

 

 

DEPT_ID	JOB_ID	SUM(SALARY)
10	AD_ASST	4400
20	MK_MAN	13000
20	MK_REP	6000

 

 

 

 

 

•  First,therowsaregroupedbydepartmentnumber.

 

•  Second,withinthedepartmentnumbergroups,therowsaregroupedbyjob

ID.

 

SotheSUMfunctionisbeingappliedtothesalarycolumnforalljobIDswithineach departmentnumbergroup.

 

 

 

UsingtheHAVINGClause:

 

SELECTdepartment_id,MAX(salary) FROMemployees

GROUPBYdepartment_id

 

HAVINGMAX(salary)>10000;

 

 

DEPARTMENT_ID	MAX(SALARY)
20	13000
80	11000

 

 

 

YouusetheHAVINGclausetospecifywhichgroupsaretobedisplayed,andthus, youfurtherrestrictthegroupsonthebasisofaggregateinformation.

 

TheOracleServerperformsthefollowingstepswhenyouusetheHAVINGclause:

 

•  Rowsaregrouped.

 

•  Thegroupfunctionisappliedtothegroup.

 

•  ThegroupsthatmatchthecriteriaintheHAVINGclausearedisplayed.

 

 

 

7.CreatingandManagingTables

 

•  Describethemaindatabaseobjects

 

•  Createtables.

 

•  Altertabledefinitions.

 

•  Drop,rename,andtruncatetables.

 

 

 

DatabaseObjects:

 

AnOracledatabasecancontainmultipledatastructures. Eachstructureshouldbe outlinedinthedatabase designsothatitcanbecreated duringthebuildstageof databasedevelopment.

 

Table            : Storesdata.

 

View             : Subsetofdatafromoneormoretables. Sequence     : Numericvaluegenerator.

Index            : Improvestheperformanceofsomequeries. Synonym      : Givesalternativenamestoobjects.

 

 

CreatingTables:

 

CREATETABLEdept(deptnoNUMBER(2), dnameVARCHAR2(14),

locVARCHAR2(13)); Confirmcreationofthetable.

DESCRIBEdept;

 

 

Name	Null?	Type
DEPTNO		NUMBER(2)
DNAME		VARCHAR2(14)
LOC		VARCHAR2(13)

 

 

Theexample ontheslidecreatestheDEPTtable,withthreecolumns: namely, DEPTNO,DNAME, andLOC.Itfurtherconfirms thecreationofthetablebyissuing the DESCRIBE  command. Because creatinga table is a DDL statement, an automaticcommittakesplacewhenthisstatementisexecuted.

 

 

 

TablesintheOracleDatabase:

 

•  Usertables:

 

-    Areacollectionoftablescreatedandmaintainedbytheuser.

 

-    Containuserinformation.

 

•  Datadictionary:

 

-    IsacollectionoftablescreatedandmaintainedbytheOracleServer.

 

-    Containdatabaseinformation. DataTypes:

VARCHAR2(size)     Variable-lengthcharacterdata. CHAR[(size)]             Fixed-lengthcharacterdata. NUMBER[(p,s)]         Variable-lengthnumericdata. DATE                        Dateandtimevalues.

 

 

Creatinga TablebyUsinga SubquerySyntax:

 

CreateatableandinsertrowsbycombiningtheCREATETABLEstatementandthe

ASsubqueryoption.

 

 

 

CREATETABLEtable

 

[(column,column...)]

 

ASsubquery;

 

 

 

Example:

 

CREATETABLEdept80

 

AS

 

SELECT employee_id, last_name, salary*12 ANNSAL, hire_dateFROM

employeesWHEREdepartment_id=80;

 

Name   Null?   Type   EMPLOYEE_ID     NUMBER(6)   LAST_NAME   NOTNULL   VARCHAR2(25)   ANNSAL     NUMBER   HIRE_DATE   NOTNULL   DATE

Error!

 

 

 

TheALTERTABLEStatement:

 

UsetheALTERTABLEstatementto:

 

•  AddingaColumn

 

-    UsetheADDclausetoaddcolumns.

 

ALTERTABLEdept80ADD(job_idVARCHAR2(9)); GuidelinesforAddingaColumn

-    Youcanaddormodifycolumns.

 

-      Youcannotspecifywherethecolumnistoappear.Thenewcolumn becomesthelastcolumn.

 

 

 

•  ModifyingaColumn

 

-    Youcanchangeacolumn’sdatatype,size,anddefaultvalue.

 

-      Achangetothedefaultvalueaffectsonlysubsequentinsertionstothe table.

 

ALTERTABLEdept80MODIFY(last_nameVARCHAR2(30)); Guidelines:

-    Youcanincreasethewidthofnumericorcharactercolumns.

 

-      Youcandecreasethewidthofacolumnonlyifthecolumncontainsonly nullvaluesorif thetablehasnorows.

 

-    Youcanchangethedatatypeonlyif thecolumncontainsnullvalues.

 

 

 

•  DroppingaColumn

 

UsetheDROPCOLUMNclausetodropcolumnsyounolongerneedfrom thetable.

 

ALTERTABLEdept80DROPCOLUMNjob_id; Guidelines:

-    Thecolumnmayor maynotcontaindata.

 

-      UsingtheALTERTABLEstatement, onlyonecolumncanbedropped at atime.

 

-    Thetablemusthaveatleastonecolumnremaininginit afterit isaltered.

 

-    Onceacolumnisdropped,it cannotberecovered.

 

 

 

•  DroppingaTable

 

-    Alldataandstructureinthetableisdeleted.

 

-    Anypendingtransactionsarecommitted.

 

-Allindexesaredropped.

 

-    YoucannotrollbacktheDROPTABLEstatement.

 

DROPTABLEdept80;

 

 

 

•  RenamingaTable:

 

AdditionalDDLstatementsincludetheRENAMEstatement,whichisusedto renameatable,view,sequence,orasynonym.

 

Syntax:

 

RENAMEold_nameTOnew_name; Inthesyntax:

-    old_nameistheoldnameofthetable,view,sequence,orsynonym.

 

-      new_nameisthenewnameofthetable,view,sequence,orsynonym. Youmustbetheowneroftheobjectthatyourename.

RENAMEdeptTOdetail_dept;

 

 

 

•  TruncatingaTable:

 

-      The TRUNCATETABLEstatement:Removesall rowsfrom a table, Releasesthestoragespaceusedbythattable.

 

-    YoucannotrollbackrowremovalwhenusingTRUNCATE.

 

-    Alternatively,youcanremoverowsbyusingtheDELETEstatement.

 

TRUNCATETABLEdetail_dept;

 

 

 

8.ManipulatingData

 

•  Insertrowsintoatable

 

•  Updaterowsinatable

 

•  Deleterowsfromatable

 

•  Mergerowsinatable

 

 

 

Addinganewrowtoatable:

 

AddnewrowstoatablebyusingtheINSERTstatement

 

INSERTINTOdepartments(department_id,department_name, manager_id,location_id)

VALUES(70,'PublicRelations',100,1700);

 

 

 

InsertingRowswithNullValues

 

Implicitmethod:Omitthecolumnfromthecolumnlist.

 

INSERTINTOdepartments(department_id, department_name)

VALUES(30,'Purchasing');

 

 

 

Explicitmethod:SpecifytheNULLkeywordintheVALUESclause.

 

INSERTINTOdepartments

 

VALUES(100,'Finance',NULL,NULL);

 

 

 

CreatingaScript:

 

•  Use&substitutionina SQLstatementtopromptforvalues.

 

•  &isaplaceholderforthevariablevalue.

 

 

 

INSERTINTOdepartments

 

(department_id,department_name,location_id)

 

VALUES(&department_id,'&department_name',&location);

 

 

 

CopyingRowsfromAnotherTable:

 

•  WriteyourINSERTstatementwithasubquery.

 

•  DonotusetheVALUESclause.

 

•  MatchthenumberofcolumnsintheINSERTclausetothoseinthesubquery

 

 

 

INSERTINTOsales_reps(id,name,salary,commission_pct) SELECTemployee_id,last_name,salary,commission_pct FROMemployees

WHEREjob_idLIKE'%REP%';

 

 

 

YoucanusetheINSERTstatement toaddrowstoatablewherethevalues arederivedfromexistingtables.InplaceoftheVALUES clause,youusea subquery.

 

 

 

ChangingDatain aTable:

 

 

•  ModifyexistingrowswiththeUPDATEstatement.

 

•  Updatemorethanonerowatatime,if required.

 

 

 

UPDATEtable

 

SETcolumn=value[, column=value,...] [WHEREcondition];

 

 

SpecificroworrowsaremodifiedifyouspecifytheWHEREclause.

 

UPDATEemployeesSETdepartment_id=70

 

WHEREemployee_id=113;

 

 

 

AllrowsinthetablearemodifiedifyouomittheWHEREclause

 

UPDATEcopy_empSETdepartment_id=110;

 

 

 

UpdatingTwoColumnswithaSubquery:

 

Updateemployee114’sjobanddepartmenttomatchthatofemployee205.

 

 

 

UPDATEemployeesSET

 

job_id=(SELECTjob_idFROMemployees

 

WHEREemployee_id=205), salary=(SELECTsalaryFROMemployees

WHEREemployee_id=205)

 

WHEREemployee_id=114;

 

 

 

RemovingaRowfromaTable:

 

YoucanremoveexistingrowsfromatablebyusingtheDELETEstatement.

 

DELETE[FROM]table[WHEREcondition];

Note:If norowsaredeleted,amessage“0rowsdeleted.”isreturned: Specificrowsaredeletedif youspecifytheWHEREclause.

 

DELETEFROMdepartmentsWHEREdepartment_name='Finance';

 

 

 

Allrowsinthetablearedeletedif youomittheWHEREclause.

 

DELETEFROMcopy_emp;

 

 

 

DeletingRowsBasedonAnotherTable:

 

Use subqueriesinDELETEstatementstoremoverowsfrom atablebasedonvalues fromanothertable.

 

 

DELETEFROMemployees

 

WHEREdepartment_id=

 

(SELECTdepartment_idFROMdepartments

 

WHEREdepartment_nameLIKE'%Public%');

 

 

 

TheMERGEStatement

 

 

 

•    Providestheabilitytoconditionallyupdateorinsertdataintoadatabase table.

 

 

 

•  PerformsanUPDATEif therowexistsandanINSERTif it isanewrow

 

-        Avoidsseparateupdates

 

-        Increasesperformanceandeaseofuse

 

-        Isusefulindatawarehousingapplications

 

 

 

InsertorupdaterowsintheCOPY_EMPtabletomatchtheEMPLOYEEStable.

 

 

 

MERGEINTOcopy_empASc

 

USINGemployeese

 

ON(c.employee_id=e.employee_id) WHENMATCHEDTHEN

UPDATESET

 

c.first_name=e.first_name, c.last_name=e.last_name,

...

 

c.department_id=e.department_id

 

WHENNOTMATCHEDTHEN

 

INSERTVALUES(e.employee_id,e.first_name,e.last_name, e.email,e.phone_number,e.hire_date,e.job_id,

e.salary,e.commission_pct,e.manager_id, e.department_id);

 

 

 

9.Constraints

 

•  Describeconstraints.

 

•  Createandmaintainconstraints.

 

 

 

Constraints:

 

•  Constraintsenforcerulesatthetablelevel.

 

•  Constraintspreventthedeletionofatableif therearedependencies.

 

 

 

Thefollowingconstrainttypesarevalid:

 

 

NOTNULL	Specifiesthatthecolumncannotcontainanullvalue.
UNIQUE	Specifies acolumn or combination of columns whose valuesmustbeuniqueforallrowsinthetable.
PRIMARYKEY	Uniquelyidentifieseachrowofthetable.
FOREIGNKEY	Foreign keyrelationship between the column and a columnofthereferencedtable.
CHECK	Specifiesaconditionthatmustbetrue.

 

 

Guidelines:

 

•  NameaconstraintortheOracleservergeneratesanamebyusingthe

SYS_Cnformat.

 

•  Createaconstrainteither:

 

-    At thesametimeasthetableiscreated,or

 

-    Afterthetablehasbeencreated.

 

•  Defineaconstraintatthecolumnortablelevel.

 

•  Viewaconstraintinthedatadictionary.

 

 

 

DefiningConstraints

 

CREATETABLEemployees(employee_id NUMBER(6),first_name VARCHAR2(20),

...

 

 

 

job_idVARCHAR2(10)NOTNULL, CONSTRAINTemp_emp_id_pk PRIMARYKEY(EMPLOYEE_ID));

 

 

Constraints areusuallycreatedatthesametimeasthetable.Constraintscanbe addedtoa tableafteritscreationandalsotemporarilydisabled.

 

 

TheNOTNULLConstraint

 

TheNOTNULLconstraintensuresthatthecolumn containsnonullvalues. Columns withouttheNOTNULLconstraintcancontainnullvaluesbydefault.

 

 

 

CREATETABLEemployees(

 

employee_idNUMBER(6),

 

last_nameVARCHAR2(25)NOTNULL,ÆSystemnamed salaryNUMBER(8,2),

commission_pctNUMBER(2,2), hire_dateDATE

CONSTRAINTemp_hire_date_nnÆUserNamed

 

NOTNULL,

 

...

 

TheNOTNULLconstraint canbespecifiedonlyatthecolumnlevel,notatthetable level.

 

 

 

TheUNIQUEConstraint

 

AUNIQUEkeyintegrityconstraint requiresthateveryvalueinacolumnorsetof columns (key)beunique:thatis,notworowsofatablecanhaveduplicatevaluesin aspecifiedcolumnorsetofcolumns.Thecolumn(orsetofcolumns)included inthe definitionoftheUNIQUEkeyconstraint iscalledtheuniquekey.IftheUNIQUE constraintcomprisesmorethanonecolumn,thatgroupof  columnsis  calleda compositeuniquekey.

 

 

 

CREATETABLEemployees(

 

employee_idNUMBER(6),

 

last_nameVARCHAR2(25)NOTNULL,

 

 

 

emailVARCHAR2(25),salary NUMBER(8,2),commission_pct NUMBER(2,2),hire_dateDATE NOTNULL,

...

 

CONSTRAINTemp_email_ukUNIQUE(email));

 

 

 

TheexampleontheslideappliestheUNIQUEconstrainttotheEMAILcolumnofthe

EMPLOYEEStable.ThenameoftheconstraintisEMP_EMAIL_UK.

 

 

 

ThePRIMARYKEYConstraint

 

APRIMARYKEYconstraintcreatesaprimarykeyforthetable.Onlyoneprimary keycanbecreatedforaeachtable.

 

 

 

CREATETABLEdepartments(department_id NUMBER(4),department_name VARCHAR2(30)CONSTRAINT dept_name_nnNOTNULL,manager_id NUMBER(6),

location_idNUMBER(4),

 

CONSTRAINTdept_id_pkPRIMARYKEY(department_id));

 

 

 

PRIMARYKEYconstraints canbedefinedatthecolumnlevelortablelevel.Atable canhaveonlyonePRIMARY KEYconstraint butcanhaveseveralUNIQUE constraints. TheexampleontheslidedefinesaPRIMARYKEYconstraintonthe DEPARTMENT_IDcolumnoftheDEPARTMENTStable.Thenameoftheconstraint isDEPT_ID_PK.

 

 

 

Note:AUNIQUEindexiscreatedautomaticallyforaPRIMARYKEYcolumn.

 

 

 

TheFOREIGNKEYConstraint

 

The FOREIGN KEY,or referential integrity constraint, designates a column or combinationofcolumnsasaforeignkeyandestablishesarelationshipbetweena

 

 

 

primarykeyorauniquekeyinthesametableoradifferent table.Intheexample on the slide, DEPARTMENT_ID has been defined as the foreign key in   the EMPLOYEES table(dependentorchildtable);itreferencestheDEPARTMENT_ID columnoftheDEPARTMENTStable(thereferencedorparenttable).

 

 

 

CREATETABLEemployees(

 

employee_idNUMBER(6),

 

last_nameVARCHAR2(25)NOTNULL, emailVARCHAR2(25),

salaryNUMBER(8,2), commission_pctNUMBER(2,2), hire_dateDATENOTNULL,

...

 

department_idNUMBER(4),

 

CONSTRAINTemp_dept_fkFOREIGNKEY(department_id) REFERENCESdepartments(department_id),

CONSTRAINTemp_email_ukUNIQUE(email));

 

 

 

TheCHECKConstraint

 

TheCHECKconstraintdefinesaconditionthateachrowmustsatisfy.

 

 

 

CREATETABLEemployees

 

(...

 

salaryNUMBER(8,2)CONSTRAINTemp_salary_min

 

CHECK(salary>0),

 

...

 

 

 

UsetheALTERTABLEstatementto:

 

•  Addordropaconstraint,butnotmodifyitsstructure.

 

•  AddaNOTNULLconstraintbyusingtheMODIFYclause.

 

 

 

AddingaConstraint

 

ALTERTABLEemployees

 

ADDCONSTRAINTemp_manager_fk FOREIGNKEY(manager_id) REFERENCESemployees(employee_id);

 

 

ALTERTABLEemployeesMODIFYnameNOTNULL;

 

 

 

DroppingaConstraint

 

ALTERTABLEemployees

 

DROPCONSTRAINTemp_manager_fk;

 

 

 

10.OtherDatabaseObjects

 

 

Table	Basicunitofstorage;composedofrowsandcolumns.
View	Logicallyrepresentssubsetsofdatafromoneormoretables
Sequence	Generatesprimarykeyvalues.
Index	Improvestheperformanceofsomequeries.
Synonym	Alternativenameforanobject.

 

 

 

Views:

 

Youcanpresentlogicalsubsetsorcombinationsofdatabycreatingviewsoftables. Aviewisalogicaltablebasedonatable oranotherview. Aviewcontainsnodataof itsownbutislikeawindowthroughwhichdatafromtablescanbeviewedor changed.

 

 

 

•     Viewsrestrictaccesstothedatabecausetheviewcandisplayselective columnsfromthetable.

 

•     Views can be used to make simple queries to retrieve the results of complicated queries.Forexample,viewscanbeusedtoqueryinformation frommultipletableswithouttheuserknowinghowtowriteajoinstatement.

 

 

 

SimpleViewsversusComplexViews:

 

Therearetwoclassifications forviews:simpleandcomplex.Thebasicdifferenceis relatedtotheDML(INSERT,UPDATE,andDELETE)operations.

 

•  Asimpleviewisonethat:

 

-    Derivesdatafromonlyonetable

 

-    Containsnofunctionsorgroupsofdata

 

•  Acomplexviewisonethat:

 

-    Derivesdatafrommanytables

 

-    Containsfunctionsorgroupsofdata

 

CREATEVIEWempvu80

 

ASSELECTemployee_id,last_name,salary

 

FROMemployees

 

WHEREdepartment_id=80;

 

 

 

CREATEVIEWsalvu50

 

ASSELECTemployee_idID_NUMBER,last_nameNAME, salary*12ANN_SALARY

FROMemployees

 

WHEREdepartment_id=50;

 

 

 

CREATEVIEWdept_sum_vu(name,minsal,maxsal,avgsal) ASSELECTd.department_name,MIN(e.salary), MAX(e.salary),AVG(e.salary)

FROMemployeese,departmentsd

 

WHEREe.department_id=d.department_id

 

GROUPBYd.department_name;

 

 

RetrievingDatafromaView: SELECT*FROMsalvu50;

 

 

RemovingaView:

 

DROPVIEWempvu80;

 

 

 

Sequence

 

•  Automaticallygeneratesuniquenumbers.

 

•  Istypicallyusedtocreateaprimarykeyvalue.

 

•  Speedsuptheefficiencyofaccessingsequencevalues. Defineasequencetogeneratesequentialnumbersautomatically.

CREATESEQUENCEsequence

 

[INCREMENTBYn] [STARTWITHn]

[{MAXVALUEn |NOMAXVALUE}] [{MINVALUEn|NOMINVALUE}]

 

 

 

[{CYCLE| NOCYCLE}];

 

 

•    CreateasequencenamedDEPT_DEPTID_SEQtobeusedfortheprimary keyoftheDEPARTMENTStable.

 

•  DonotusetheCYCLEoption.

 

 

 

CREATESEQUENCEdept_deptid_seq

 

INCREMENTBY10

 

STARTWITH120

 

MAXVALUE9999

 

NOCYCLE;

 

 

 

DonotusetheCYCLEoptionifthesequenceisusedtogenerateprimarykey values.

 

 

 

NEXTVALandCURRVAL:

 

•  NEXTVALreturnsthenextavailablesequencevalue.

 

•  CURRVALobtainsthecurrentsequencevalue.

 

•    NEXTVALmustbeissuedforthatsequencebeforeCURRVALcontainsa value.

 

 

 

UsingaSequence:

 

•  Insertanewdepartmentnamed“Support”inlocationID2500.

 

INSERTINTOdepartments(department_id,department_name,location_id) VALUES(dept_deptid_seq.NEXTVAL,

'Support',2500);

 

 

• ViewthecurrentvaluefortheDEPT_DEPTID_SEQsequence.

 

SELECTdept_deptid_seq.CURRVALFROMdual;

 

 

 

ModifyingaSequence:

 

IfyoureachtheMAXVALUE limitforyoursequence,noadditionalvaluesfromthe sequence areallocated andyouwillreceiveanerrorindicating thatthesequence exceedstheMAXVALUE.Tocontinuetousethesequence,youcanmodifyitby usingtheALTERSEQUENCEstatement.

 

ALTERSEQUENCEdept_deptid_seq

 

INCREMENTBY20

 

MAXVALUE999999

 

NOCYCLE;

 

 

 

Guidelines:

 

•  YoumustbetheownerorhavetheALTERprivilegeforthesequence.

 

•  Onlyfuturesequencenumbersareaffected.

 

 

RemovingaSequence:

 

•    Remove asequence from  the  data  dictionary by  using the  DROP SEQUENCEstatement.

 

•  Onceremoved,thesequencecannolongerbereferenced.

 

DROPSEQUENCEdept_deptid_seq;

 

 

 

Index

 

•  Isaschemaobject.

 

•     IsusedbytheOracleServertospeeduptheretrievalofrowsbyusinga pointer.

 

•  Isindependentofthetableit indexes.

 

•  IsusedandmaintainedautomaticallybytheOracleServer.

 

 

 

Theindexcanbecreated

 

•  Automatically:Auniqueindexiscreatedautomaticallywhenyoudefinea

PRIMARYKEYorUNIQUEconstraintinatabledefinition.

 

•    Manually:Userscancreatenonuniqueindexesoncolumnstospeedup accesstotherows.

 

 

 

CreatinganIndex:

 

CREATEINDEX<indexname>ONtable(column[,column]...);

 

 

 

ImprovethespeedofqueryaccesstotheLAST_NAMEcolumnintheEMPLOYEES

table.

 

CREATEINDEXemp_last_name_idxONemployees(last_name);

 

 

 

WhentoCreateanIndex

 

Youshouldcreateanindexif:

 

•  Acolumncontainsawiderangeofvalues.

 

•  Acolumncontainsalargenumberofnullvalues.

 

•     OneormorecolumnsarefrequentlyusedtogetherinaWHEREclauseora joincondition.

 

 

RemovinganIndex:

 

RemoveanindexfromthedatadictionarybyusingtheDROPINDEXcommand.

 

DROPINDEXindex;

 

DROPINDEXupper_last_name_idx;

 

 

 

Synonyms:

 

•  Easereferringtoatableownedbyanotheruser.

 

•  Shortenlengthyobjectnames.

 

 

 

CreateashortenednamefortheDEPT_SUM_VUview.

 

CREATE[PUBLIC]SYNONYMd_sumFORdept_sum_vu;

 

 

 

Dropasynonym.

 

DROP[PUBLIC]SYNONYMd_sum;