SOFTWARE
LABORATORYRECORD
Name :VENKATA PHANIKRISHNAB
Assistant Professor
DNR CET
Lab : DATABASEMANAGEMENTSYSTEMS
INDEX
S.NO / DATE / TITLEOF EXPERIMENT / Page No.1 / 1. EXPERIMENTSON SQL
1. 1.Simple SQLcommands
1.2.Creation oftables
1.3.Set Operations
1.4.Functions and Procedures
1.5.Triggers / 1
12
20
30
45
1.EXPERIMENTSONSQL
Expno:1.1 DATE:3-7-2012
SIMPLE SQL COMMANDS
Aim:
To practicesimple sql commands for retrieving data contained in tables
Theory:
Questionsincludingdata storedina databasearecalledqueries.Queriesare the primarymechanism for retrieving information from adatabaseand consist ofquestions presentedtothedatabaseinapredefinedformat.Manydatabasemanagement systemsusethe StructuredQueryLanguage(SQL)standardqueryformat.Adatabasequery canbeeithera selectqueryoranactionquery.Aselectqueryissimply adataretrievalquery.Anaction query canaskforadditionaloperationsonthedata,suchasinsertion,updating,ordeletion. StructuredQueryLanguage isa special-purpose programminglanguage designedfor managing datain relational databasemanagement systems (RDBMS).
SQL wasinitially developedatIBM by DonaldD.ChamberlinandRaymondF.Boyceinthe early1970s.Thisversion,initiallycalledSEQUEL(StructuredEnglishQueryLanguage), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database management system, System R, which a group at IBM San Jose Research Laboratory haddevelopedduringthe1970s.Tobringconformity amongvendors,the AmericanNationalStandardsInstitute(ANSI)publisheditsfirstSQL standardin1986anda secondwidelyadoptedstandardin1989.ANSIreleasedupdatesin1992,knownasSQL92 and SQL2,and again in 1999, termed as both SQL99 and SQL3.
In SQL92, SQLstatements aregroupedinto threebroad categories. Theyare
Data ManipulationLanguage (DML), Data DefinitionLanguage (DDL), Data ControlLanguage(DCL)
Clauses, which are constituent components of statements and queries.
TheFROMclausewhichindicatesthetable(s)fromwhichdataistoberetrieved.
TheFROMclausecanincludeoptionalJOINsubclausestospecifytherulesfor joining tables.
The WHERE clause includes a comparison predicate, which restricts the rows returnedby thequery.TheWHEREclauseeliminatesallrowsfromtheresultsetfor which the comparison predicate does not evaluateto True.
The GROUP BYclauseisused toprojectrowshavingcommonvaluesintoa smaller setofrows.GROUPBYisoftenusedinconjunctionwithSQL aggregationfunctions or toeliminate duplicaterowsfromaresultset.The WHEREclause isappliedbefore the GROUPBY clause.
TheHAVINGclauseincludesapredicateusedtofilterrowsresultingfromthe GROUPBYclause.BecauseitactsontheresultsoftheGROUP BYclause, aggregation functions can beusedin theHAVING clausepredicate.
TheORDERBYclauseidentifieswhichcolumnsare usedtosorttheresultingdata, andinwhichdirectionthey shouldbesorted(optionsareascendingordescending). WithoutanORDERBYclause,theorderofrowsreturnedby anSQLqueryis undefined.
Dependingontheoperationsperformedbydatabase,SQLiscategorizedintofollowingsub- languages
1) DDL:-DataDefinitionLanguage
2) DML:-Data ManipulationLanguage
3) DRL:-Data RetrievalLanguage
4) DCL:-Data ControllingLanguage
5) TCL:-Transaction ControlLanguage
DDL:-DataDefinitionLanguage
Operations are
Create:-wecancreate atable ordata definition
Alter:-alreadycreated datadefinitionwewantto addnewentityweusethismeansto modifydata definition weuse alter
Drop:-To drop the datadefinition (Means to delete thetableor datadefinition)
Truncate:-Releasememory allocatedforthetablethatisonetableiscreatedthen some memoryallocatedforthat table. When weusetruncate that memoryis cleared
Rename:-usedtochangename ofthe tables
DML:-DataManipulationLanguage
This is used to implement operations on data Set of instructions to manipulatedataare
Insert:-forinserting thedata in alreadycreated table
Update: -forupdating the content in table
Delete:-fordeleting thecontent in thetable
DRL:-DataRetrieval Language
Select:-To retrievedatafrom database
DCL:-DataControlling Language
Grant:-if oneuserdatacannot be accessed byanother user until 1st user give permissionsto2nduser.Thenonly 2ndusercanaccess1stuserdata.Meansforgiving permissionstouser we use the grantcommand.i.e. The GRANTcommand gives privileges to users
Revoke:-REVOKEcommand takes awayprivileges from user.
TCL:-TransactionControlLanguage
Commit:-Tosavetransaction.Thisissameassaveoptioninnotepadifweusethis
„commit‟commandwesavethedatapermanentlyinsecondarymemoryotherwiseit
is in RAM
Rollback:-To cancel transaction. This is same as undo operation in notepad.
Savepoint:-Tocancelpartofthetransactiontocancelwholetransactionweuse rollback. Fortocancelpart oftransaction weuse savepoint
PROCEDURE:
Forgetting SQLprompt
Click start
Click allprograms
Click Oracle
Click Application development
Click SQLplus
AtLogon windowenterthe following data.
User name : system
Password : manager
Host string : oracle9
Aftergetting SQLprompt typethe following command to createanew user
Creating newuserinDatabase:
SQLcreate user mtech34 identified byphani quota100m on users;
User created.
Forproviding permissions to newly created user SQLgrant connect,create session,resourceto mtech34; Grant succeeded.
For changing existing account to anotheraccount
SQLconnect mtech34
Enter password: Connected.
Forfindouting thepresent account
SQLshow user; USERis "MTECH34"
For copying theoneaccount tables to thepresent account SQLcreate table mtech34.sailors as select * from mt34.sailors; Table created.
SQLcreate table mtech34.Boats as select * frommt34.boats; Table created.
SQLcreate table mtech34.reserves as select * from mt34.reserves; Table created.
QUERIES:
SQLSELECT * FROM EMP;
EMPNO------/ ENAME
------/ JOB
------/ MGR HIREDATE
------/ SAL COMM DEPTNO
------
7369 / SMITH / CLERK / 7902 17-DEC-80 / 800 20
7499 / ALLEN / SALESMAN / 7698 / 20-FEB-81 / 1600 / 300 / 30
7521 / WARD / SALESMAN / 7698 / 22-FEB-81 / 1250 / 500 / 30
7566 / JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7654 / MARTIN / SALESMAN / 7698 / 28-SEP-81 / 1250 / 1400 / 30
7698 / BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782 / CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788 / SCOTT / ANALYST / 7566 / 09-DEC-82 / 3000 / 20
7839 / KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844 / TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 30
7876 / ADAMS / CLERK / 7788 / 12-JAN-83 / 1100 / 20
7900 / JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902 / FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934 / MILLER / CLERK7 / 782 / 23-JAN-82 / 1300 / 10
SQLSELECT * FROM DEPT;
DEPTNO / DNAME / LOC------/ ------/ ------
10 / ACCOUNTING / NEWYORK
20 / RESEARCH / DALLAS
30 / SALES / CHICAGO
40 / OPERATIONS / BOSTON
SQLSELECT * FROM SALGRADE;
GRADELOSALHISAL
------
Display all the employees’ names SQLSELECTENAMEFROMEMP; ENAME
------SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
14rowsselected.
Display all the department names. SQLSELECT DNAME FROM DEPT; DNAME
------ACCOUNTING RESEARCH SALES OPERATIONS
Display all the employees names along with their designations SQLSELECT ENAME,JOB AS DESIGNATION FROM EMP; ENAME DESIGNATION
------SMITH CLERKALLEN SALESMANWARD SALESMANJONES MANAGERMARTIN SALESMANBLAKE MANAGERCLARK MANAGERSCOTT ANALYSTKING PRESIDENTTURNER SALESMANADAMS CLERKJAMES CLERK
FORD ANALYST MILLER CLERK
Display thedetails ofemployees whose salaries aregreater than2000.
SQLSELECTEMPNO,ENAME FROM EMP WHERE SAL2000;
EMPNOENAME
------
7566JONES
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7902FORD
6 rowsselected.
Display thedetails ofemployeewhose empno is 7788
SQLselect * from empwhere empno=7788;
EMPNO / ENAME / JOB / MGR HIREDATE / SAL / COMM / DEPTNO------/ ------/ ------/ ------/ ----- / ------/ ------
7788 / SCOTT / ANALYST / 7566 09-DEC-82 3000 / 20
Display thedesignationofemployeewhose empno is 7788
SQLselect job as designation from emp whereempno=7788;
DESIGNATION
------ANALYST
Displaythename,designationandsalaryofemployeeswhosesalariesaregreaterthan
2000.
SQLselect ename, jobas designation from empwheresal>2000;
ENAME DESIGNATION
------
JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT FORD ANALYST
Displaythename,designationandsalaryofemployeeswhosesalariesarebetween2500 and3000.
SQLselect ename, jobas designation from empwheresal between 2500and 3000;
ENAME DESIGNATION
------
JONES MANAGER BLAKE MANAGER SCOTT ANALYST FORD ANALYST
Display thename, designationandsalary ofemployees whose salaries areless than2500 andthatofemployees whose salaries aregreater than3000.
SQLselect ename, jobas designation,sal from emp wheresal2500 orsal>3000;
ENAME DESIGNATION SAL
------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
10rowsselected.
Display thedetails ofemployeewhose nameisscott.
SQLselect * from empwhere ename='SCOTT';
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
------
7788SCOTT ANALYST 756609-DEC-82 3000 20
Display thedetails ofemployees whose designationis manager.
SQLselect * from empwherejob='MANAGER';
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
7566JONES / MANAGER / 783902-APR-81 / 2975 / 207698BLAKE / MANAGER / 783901-MAY-81 / 2850 / 30
7782CLARK / MANAGER / 783909-JUN-81 / 2450 / 10
Displaythenamesanddesignationofemployeeswhosedesignationismanagerand whose salary is greaterthan2500.
SQLSELECT ename,job from emp wherejob='MANAGER'and sal2500;
ENAME JOB
------
JONES MANAGER BLAKE MANAGER
Display thedetails ofemployees whose namesstart with letters.
SQLselect * from empwhere ename like'S%';
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
------
20
20
Display thedetails ofemployees whose namesendwithletter t.
SQLselect * from empwhere ename like'%T';
EMPNOENAME JOB MGRHIREDATE SAL COMMDEPTNO
------
7788SCOTT ANALYST 756609-DEC-82 3000 20
Display thesalaries ofemployees without repetitions
SQLselect distinct sal from emp;
SAL
------
2450
5000
1300
1250
2850
2975
1100
3000
800
1600
1500
950
12rowsselected.
Display names and designationofemployees after concatenating.
SQLSELECT ENAME||':-'||JOB FROM EMP;
ENAME||':-'||JOB
------SMITH:-CLERK ALLEN:-SALESMAN WARD:-SALESMAN JONES:-MANAGER MARTIN:-SALESMAN BLAKE:-MANAGER CLARK:-MANAGER SCOTT:-ANALYST KING:-PRESIDENT TURNER:-SALESMAN ADAMS:-CLERK JAMES:-CLERK
FORD:-ANALYST MILLER:-CLERK
14rowsselected.
Display names ofemployees without any commission
SQLSELECT * FROM EMP WHERE COMM ISNULL;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
------
7369SMITH / CLERK / 7902 / 17-DEC-80 / 800 / 207566JONES / MANAGER / 7839 / 02-APR-81 / 2975 / 20
7698BLAKE / MANAGER / 7839 / 01-MAY-81 / 2850 / 30
7782CLARK / MANAGER / 7839 / 09-JUN-81 / 2450 / 10
7788SCOTT / ANALYST / 7566 / 09-DEC-82 / 3000 / 20
7839KING / PRESIDENT / 17-NOV-81 / 5000 / 10
7844TURNER / SALESMAN / 7698 / 08-SEP-81 / 1500 / 30
7876ADAMS / CLERK / 7788 / 12-JAN-83 / 1100 / 20
7900JAMES / CLERK / 7698 / 03-DEC-81 / 950 / 30
7902FORD / ANALYST / 7566 / 03-DEC-81 / 3000 / 20
7934MILLER / CLERK / 7782 / 23-JAN-82 / 1300 / 10
11rowsselected.
Display names ofemployees with somecommissionalongwith commission.
SQLSELECT * FROM EMP WHERE COMM ISNOT NULL;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
------
7499ALLEN / SALESMAN / 769820-FEB-81 / 1600 / 300 / 307521WARD / SALESMAN / 769822-FEB-81 / 1250 / 500 / 30
7654MARTIN / SALESMAN / 769828-SEP-81 / 1250 / 1400 / 30
Displayempnoofemployeesandsalariesinorderofsalariessuchthatemployeewith max salary is ontop.
SQLSELECT ENAME,SALFROM EMP ORDER BY SALDESC;
ENAME SAL
------
KING / 5000FORD / 3000
SCOTT / 3000
JONES / 2975
BLAKE / 2850
CLARK / 2450
ALLEN / 1600
TURNER / 1500
MILLER / 1300
WARD / 1250
MARTIN / 1250
ADAMS / 1100
JAMES / 950
SMITH / 800
14rowsselected.
Displaynamesofemployeesandsalariesinorderofsalariessuchthatemployeewith max salary is atthe bottom
SQLSELECT ENAME,SALFROM EMP ORDER BY SAL;
ENAME SAL
------
SMITH / 800JAMES / 950
ADAMS / 1100
WARD / 1250
MARTIN / 1250
MILLER / 1300
TURNER / 1500
ALLEN / 1600
CLARK / 2450
BLAKE / 2850
JONES / 2975
SCOTT / 3000
FORD / 3000
KING / 5000
14rowsselected.
Display names ofemployees andjoining dates intheorderofjoining dates. SQLSELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE; ENAME HIREDATE
------
SMITH 17-DEC-80
ALLEN 20-FEB-81
WARD 22-FEB-81
JONES 02-APR-81
BLAKE 01-MAY-81
CLARK 09-JUN-81
TURNER 08-SEP-81
MARTIN 28-SEP-81
KING 17-NOV-81
JAMES 03-DEC-81
FORD 03-DEC-81
MILLER 23-JAN-82
SCOTT 09-DEC-82
ADAMS 12-JAN-83
14rowsselected.
Display thedetails ofsalesmanandmanagerwhose salary is 1500 ormore.
SQLSELECT * FROM EMP WHERE SAL>=1500 AND JOB='SALESMAN' OR
JOB='MANAGER';
EMPNOENAME JOB MGR HIREDATE SAL COMM DEPTNO
------
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
7566JONES MANAGER 783902-APR-81 2975 20
7698BLAKE MANAGER 783901-MAY-81 2850 / 307782CLARK / MANAGER / 783909-JUN-81 / 2450 / 10
7844TURNER / SALESMAN / 769808-SEP-81 / 1500 / 30
Calculateand display salary ofscott foroneyear.
SQLselect 12*sal as pafrom emp whereename='SCOTT';
PA
------
36000
Display empno, deptno, dnameofall employees
SQLselect e.ename,e.deptno,d.loc,d.dname from dept d,emp e where e.deptno=d.deptno;
ENAME DEPTNOLOC DNAME
------SMITH 20DALLAS RESEARCH ALLEN 30 CHICAGO SALES WARD 30CHICAGO SALES JONES 20DALLAS RESEARCH MARTIN 30 CHICAGO SALES BLAKE 30 CHICAGO SALES
CLARK 10NEWYORK ACCOUNTING SCOTT 20DALLAS RESEARCH
KING 10NEWYORK ACCOUNTING TURNER 30CHICAGO SALES ADAMS 20DALLAS RESEARCH
JAMES 30 CHICAGO SALES FORD 20DALLAS RESEARCH
MILLER 10NEWYORK ACCOUNTING
14 rowsselected.
Display locationofthedepartment in which ‘scott’is working
SQLselect d.locfrom dept d,emp ewheree.deptno=d.deptno and e.ename='SCOTT';
LOC
------DALLAS
RESULT: Thesimple sql commands executed successfullyand outputverified
Expno:1.2 DATE:3-7-2012
CREATING TABLES
Aim:
To create, modifyand update tables and to practicesome aggregate functions
Theory:
Tablesarethebasicstructurewheredataisstoredinthedatabase.Tablesaredefined using the createcommand. Thesimplified form ofcreate command is
create table r
( A1 D1, A2 D2,…, AnDn,
[integrity_constraint1 ],…,[integrity_constraintn])
|as select_statement;
insert :Tablesare const ructed using theinsert command. Thesimplified form of insert command is
insert into r
[(A1, A2, ..,An)]values(v1, v2, . .,vn)
|select statement ;
update: The attributes values areupdated using theupdate command. Thesimplified form of update Command is
update table_name|view_name
setA =v1
where conditions;
droptable: Table can bedropped using drop table. Thesyntax of the command is
droptable [owner. ]table_name;
Delete:Tablecontentscanbedeletedusingdeletecommand.Thesyntaxofdeletecommand is
deletefrom[owner.]table_name [where clause];
altertable:Tablestructurecanbealteredusingaltertablecommand.Thesyntaxofalter table command is
alter table [owner_name. ]table_name
addA D
| modify A D
|dropcolumnA ;
Aggregatefunctions:
Aggregatefunctionsarefunctionsthattakeacollectionofvaluesasinputandreturnasingle value. SQLoffers five built-in aggregate functions.
·MIN (A) :returns minimumof column values
·MAX(A ):returns maximum of column values
·AVG ( [distinct ]A ):returns averageof uniquecolumn values
·SUM( [distinct ]A ):returns total of unique column values
·COUNT( [distinct ]A):returns numberof unique column values
OPERATIONS
Creating theEmpTable
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SALNUMBER(7, 2),
COMM NUMBER(7, 2), DEPTNONUMBER(2));
SQLDESCEMP;
Name Null? Type
------EMPNO NOTNULLNUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE
SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
Forinsertion
INSERTINTO EMPVALUES(7934,'MILLER','CLERK',7782, TO_DATE('23-JAN-1982','DD-MON-YYYY'), 1300, NULL, 10);
Integrity constraints: Areusedto implement business rules
To validate thedata
To maintain dataintegrity
To implement business rules
Integrityconstraintsensurethedatainsertedinatablehasintegrity.Inserteddatawillbe always correct
Generallyconstraints mean „arule‟;
According to RDBMSthe different integrityconstraints are
Entityintegrity: is implemented byUNIQUE, PRIMARY key
Domain integrity: is implemented byNOTNULL,CHECK
Referentialintegrity: is implemented byFORIGNkey
UNIQUE:-this constraintdoesn‟tallow duplicates, but allow null values.
Syntax: empno number unique
NOT NULL:-this constraintdoesn‟tallow null values but allow duplicates
Syntax: enamevarchar2(20) not null;
PRIMARY KEY:-It doesn‟tallows duplicate values andnullvalues
Syntax: eno number(4)primarykey
Check: if validations is based on some condition then weusecheckconstraint
Syntax: sal number(7,2)check(sal>300)
FOREIGN key:-usedto establish relation between two tables
Rules:valueofforeignkeyshouldwatchwithprimarykeyvaluesorforeignkeycan
benull. Butdoesn‟tallow new values other thanprimarykeyvalues andnullvalues
Afterdeclaringforeignkeyarelationshipisestablishedbetweenthetwotablesandthat relationship is called parent-child relation or master-slaverelation.
Referring tableis child-->emp
Referred tableis parent-->dept
Ex
SQLcreate table sailors(sid integer primary key,sname varchar2(13),rating integer,age number);
Table created.
SQLcreate table boats(bid integer primarykey,bname varchar2(13),colorvarchar2(10)); Table created.
SQL> create table reserves(sid integer references sailors(sid),bid integer references boats(bid),daydate);
Table created.
SQLcreate table student(regno varchar2(12), name varchar2(12),addressvarchar2(29), profilevarchar2(23),branch varchar2(6),phno number,password varchar2(34)) ;
Table created. SQLdescstudent;
Name Null? Type
------REGNO VARCHAR2(12) NAME VARCHAR2(12) ADDRESS VARCHAR2(29) PROFILE VARCHAR2(23) BRANCH VARCHAR2(6) PHNO NUMBER PASSWORD VARCHAR2(34)
Update: isusedtomodifythedata.Meanstoupdatetheparticularfieldvalueofarowwe use update
SQLupdate emp set sal=sal+100 wheresal<1000;
2 rows updated.
Delete: is usedto modifythedata. Means to deletethe particularrowweusedelete command
SQLdeletefrom emp whereempno=7902;
1 rowdeleted.
ALTER COMMNAD: Is Used To ModifyTheData definition OfA Table
To renamea column:
Altertabletnamerename column old-nameto <new –nameSQLAltertablestudentrenamecolumn name tosname;
Table altered.
SQLdescstudent;
Name Null? Type
------REGNO VARCHAR2(12) SNAME VARCHAR2(12) ADDRESS VARCHAR2(29) PROFILE VARCHAR2(23) BRANCH VARCHAR2(6) PHNO NUMBER PASSWORD VARCHAR2(34) To modify a column
Ieincrementordecreamentfieldsize,tochangedatatype,tochangenullvaluestonotnull
values, to chanenot nullvalues to values
SQLalter table studentmodifyregno varchar2(10); Table altered
Adding a column:
SQLalter table studentadd(yofad date); Table altered.
SQLdescstudent;
Name Null? Type
------REGNO VARCHAR2(12) SNAME VARCHAR2(12) ADDRESS VARCHAR2(29) PROFILE VARCHAR2(23) BRANCH VARCHAR2(6) PHNO NUMBER PASSWORD VARCHAR2(34) YOFAD DATE Dropping columns:
SQLalter table studentdrop column profile;
Table altered.
To change thedata type:
SQLalter table studentmodify(yofad varchar2(12)); Table altered.
Changing to nullto notnull
SQLalter table studentmodify(sname not null); Table altered.
SQLcreate table sailors(sid integer primary key,sname varchar2(13),rating integer,age number);
Table created.
SQLcreate table boats(bid integer primarykey,bname varchar2(13),colorvarchar2(10)); Table created.
SQL> create table reserves(sid integer references / sailors(sid),bid / integer / referencesboats(bid),daydate);
Table created.
SQLdescsailors;
Name Null? Type
------SID NOTNULLNUMBER(38) SNAME VARCHAR2(13) RATING NUMBER(38)
AGE NUMBER
SQLdescboats;
Name Null? Type
------BID NOTNULLNUMBER(38) BNAME VARCHAR2(13) COLOR VARCHAR2(10)
SQLdescreserves;
Name Null? Type
------SID NUMBER(38)
BID NUMBER(38)
DAY DATE
SAILORS
SIDSNAME RATINGAGE
BOAT
BIDBNAME COLOR
------
101 Interlake blue
102 Interlake red
103 Clipper green
104 Marine red
RESERVES
SID BID DAY
------
22 / 101 / 10-OCT-9822 / 102 / 10-OCT-98
22 / 103 / 10-AUG-98
22 / 104 / 10-JUL-98
31 / 102 / 11-OCT-98
31 / 103 / 11-JUN-98
31 / 104 / 11-DEC-98
64 / 101 / 09-MAY-98
64 / 102 / 09-AUG-98
Findallsailors witha rating above7.
SQLselect * from sailors where rating7;
SIDSNAME RATING AGE
------
31Lubber / 8 / 55.532Andy / 8 / 25.5
58 Rusty / 10 / 35
71Zorba / 10 / 16
74Horatio / 9 / 35
LIKEoperator:
FindtheagesofsailorswhosenamebeginsandendswithBandhasatleasethree characters,
SQLselect s.age from sailors s wheres.sname like 'B_%b';
AGE
-----
63.5
Set-ComparisonOperators:
{,<=,=,,>=,}
Findsailorswhose rating is better thansomesailor calledHoratio
SQLselects.snamefromsailorsswheres.rating> any(selects2.ratingfromsailorss2 wheres2.sname='Horatio');
SNAME
------Lubber Andy Rusty Zorba Horatio
Findthesailorswith thehighest rating
SQLselect s.sid,s.sname from sailors s wheres.rating= all(select s2.rating from sailors s2); SID SNAME
------
58 Rusty
71 Zorba
Findthenames ofsailors who have reservedall boats
SQLselects.snamefromsailorsswherenotexists((selectbidfromboats)except(select r.bid from reserves rwhere r.sid=s.sid));
Aggregatefunctions:
It takea collection ofvalues as input and returnasinglevalue
Min(a):-returns minimumof column values Max(a):-returns maximum of column values Avg(a):-retunes averageof column values Sum(a):-returns sum ofcolumns values Count(a): -returns number ofvalues in acolumn.
Findtheaverageageofallsailors SQLselect avg(s.age) from sailors s; AVG(S.AGE)
------
36.9
Findtheaverageageofsailors with a rating of10
SQLselect avg(s.age) from sailors s wheres.rating=10;
AVG(S.AGE)
------
25.5
Findthenameandageoftheoldest sailor
SQLselect s.sname, s.age from sailors s wheres.age=(select max(s2.age)from sailors s2);
OR
SQLselect s.sname, s.age from sailors s where(select max(s2.age) fromsailors s2)=s.age;
SNAME AGE
------Bob 63.5
Count thenumberofsailors
SQLselect count(*) from sailors;
COUNT(*)
------
10
Count thenumberof different sailors names; SQLselect count(distinct s.sname) from sailors s; COUNT(DISTINCTS.SNAME)
------
9
Findthenames ofsailors who areolder thantheoldest sailorwith a rating of10
SQLselects.snamefromsailorsswheres.age(selectmax(s2.age)fromsailorss2where s2.rating=10);
SNAME
------Dustin Lubber Bob
RESULT:
Thesql commands forcreation oftableis executed successfullyand outputverified
Expno:1.3 DATE:10-7-2012
SET OPERATIONS
Aim :-
To Practiceset operations, nested queriesand joins in SQL
Theory:-
Anestedquery isquerythathasanotherqueryembeddedwithinit,theembeddedquery is called sub query
.
NestedQueries in this inner sub queriesare completelydependent on theouterqueries CorrelatedNestedqueriesinthisinnersubqueryhasbeencompletelyindependentofthe outerquery
There aresomepoints tobe remembered while using nested queries. Theyare
Use single-row operatorswithsingle-row subqueriesanduse multiple-row operators with multiple-rowsub queries.
In Single row sub query,operators usedare, ,=, =,
Inmultiplerowsubquery,operatorsusedareall,someandin.whosemeaningsare every valuereturned bythesubquery,atleastonevaluereturnedby thesubquery , anyvalue in the list respectively.
SQL supportsthesetoperationsunion,intersectionandminuswiththekeywordsunion, intersect,andexcept(minusinoracle).Italsosupportsexistsandnotexists.Exists operator returnstrueifthesetisnotempty andnotexistsreturnstrueifthesetisempty.TheUNION operator returns records fromthe resultof bothqueriesafter eliminatingthe duplicate records whicharecommoninboth.Thereisanotheroptionofunion,theUNIONALLoperator, whichreturnsrecordsfromthe resultof bothqueries,includingduplicate records whichare commoninboth.TheINTERSECToperatorreturnstherecordswhichare commoninthe resultof bothqueries.The EXCEPToperatorreturnstherecordswhichareinthe resultof the first querybut notin the resultof thesecond one
Wecanusethejoincapability yinSQLtobringtogetherdatathatisstoredindifferenttables by creatingalinkbetweenthem.Whendatafrommorethanonetableinthedatabaseis required,a joinconditionisused.Rowsinone table canbe joinedtorowsinanother table accordingtocommonvaluesexistingincorrespondingcolumns,thatis,usually primary and foreignkey columns.Ajoinwithajoinconditionisknownasconditionaljoin.Whenajoin conditionisinvalidor omittedcompletely,the resultisaCartesianproduct,inwhichall combinationsofrowsaredisplayed.Allrowsinthefirsttableare joinedtoallrowsinthe second table.
Equiv.-Joinis a special caseofcondition join wherethecondition contains onlyequalities.
AnaturaljoinisanEquiv.-Joinon allcommon fieldsoftherelatedtables.Ifthereareany valuesinonetablethatdonothavecorrespondingvaluesintheother,inanEqui-jointhat rowwillnotbeselected.Suchrowscanbeforcefully selectedby usingouterjoin.Theleft outer jointakesalltherowsinthelefttable (firsttable inthejoin) andpadstherowswhich don‟thaveamatchingrowwithnullvaluesforallotherattributesintherighttable.Theright outer jointakesallthe rowsinthe righttable( firsttable inthe join) andpadsthe rowswhich doesn‟thaveamatchingrowwithnullvaluesforallotherattributesinthelefttable.Thefull outerjoinincludesalltherowsfrombothtablesandpadstherowswhichdoesn‟t havea matching row with nullvalues for allother attributes in theothertable.
InSQLtherearefourjointypesnamelyinnerjoin,leftouterjoin,right outerjoinandfull outerjoin. Andtherearethree joinconditionsnatural,on<condition,using(field1, field2,..).Hereoncondition>isforconditionaljoinandusing(field1,field2,..)islike natural join butthe join fields are fields specifiedin the using class.
Conditionaljoins: ThefollowingstatementappliesCartesianproductondeptandemp tablesanddisplaystherecordswhichsatisfytheconditioni.e.thedetailsofdepartmentno10 employees‟details who doesn‟tbelong to the department.
Select * fromdept,empwhere dept.deptnoemp.deptno anddept.deptno=10;
Equi-join:ThefollowingstatementappliesCartesianproductondeptandemp tables and displaystherecordswhichsatisfy theconditioni.e.thedetailsofdepartmentswhichhave someemployeesalong withemployees‟details whobelongtothedepartment.The condition hereis equalitycondition. So, itis also known asEqui-join.
select * fromdept,empwhere dept.deptno=emp.deptno;
inner join: An alternativewayof using theabovequeryis
select* fromdeptinner joinemp ondept.deptno=emp.deptno;
left outer join: The following statements display all the dept records which have the matchingemp records alongwiththe matchingemprecordsand inadditiondisplaysdept recordswhich does not have anyemployees.
select* fromdeptleft outer joinemp ondept.deptno=emp.deptno; OR
select* fromdept, empwhere dept.deptno =emp.deptno(+);
rightouterjoin:The followingstatementsdisplay allthedeptrecordswhichhavethe matchingemprecordsalongwiththematching emprecordsandinaddition displaysemp recordswhich does not have anydepartment.