easy sql practice
TRANSCRIPT
![Page 1: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/1.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 1/173
SQL – Structured Query Language
Data Definition Language (DDL)1. Create2. Alter
3. Drop4. Truncate5. Rename
To create a TableEx:
Create Table cig_emp(empno number,name varchar2(25),doj date,job varchar2(25),salnumber,deptno number);
To View the Structure of the Table.SQL>Describe cig_emp;
Or SQL>Desc cig_emp;
To Alter the Table Structure?SQL> Alter Table Cig_Emp modify(job varchar2(20));
Note: To resize the column width of Job
To add one more column in the existing table.SQL> Alter Table Cig_emp Add(Blood_Group varchar2(10));
To Delete the unwanted Column.
SQL> Alter Table Cig_emp Drop(Blood_Group);To Rename the Column NameSQL> ALter Table Dept1 Rename column Deptno To Dno;
To Delete the Table.SQL>Drop Table CIG_EMP;
To remove the data from the table.SQL> Truncate Table CIG_Emp;
To rename the Table NameSQL>Rename CIG_EMP To CIG_EMP1;
2. Data Manipulation LanguageINSERTUPDATEDELETE
![Page 2: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/2.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 2/173
MERGE
Ex:SQL>Insert into CIG_EMP Values(1000, ‘Raghu’, ’02-Apr-2008’, ‘Tech_Consult’,20000, 40);
For Continuous InputSQL> insert into cig_emp values(&empno,'&name','&doj','&job',&sal,&dno);
Note: Use /
For insert in desired field onlySQL > Insert into CIG_EMP(empno,name) Values (100,’Seenu’);
For insert all rows to a new tableSql> Insert into Dept1 ( Select * from dept )
5 rows created.Data Retrieval - SelectTo View the DataSQL> Select * From CIG_EMP;
To view Selected ColumnsSQL> Select Empno, Name from CIG_EMP;
How to view the Desired Record?To view the Record of ‘Raghu’SQL> select * from CIg_EMP Where empno=1001;or SQL> select * from CIg_EMP Where name='Raghu';
To view the records of name starts with ‘r’?SQL> select * from cig_emp where name like 'r%';
Note: % - For Multicharacters. _ For Single Character.SQL> select * from cig_emp where name like '_a%';
To view the records Last character as ‘r’?SQL> select * from cig_emp where name like '%r';
To view the records : empno >=1002SQL> select * from cig_emp where empno>=1002;
Using In to extract Data?SQL> select * from cig_emp where empno in (1000,1003);SQL> select * from cig_emp where name in ('Raghu','raja');
![Page 3: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/3.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 3/173
Using ANDSQL> Select * from emp where job='CLERK' AND SAL > 1000;SQL> SELECT * FROM EMP WHERE SAL >=1000 AND SAL <=2000;
Using OR SQL> select * from emp where job = 'CLERK' OR job = 'MANAGER';SQL> SELECT * FROM EMP WHERE JOB IN ('MANAGER','CLERK');
Between andSQL> select * from emp where sal between 1000 and 2000;
Not (except 1000 to 2000)SQL> select * from emp where sal not between 1000 and 2000;SQL> select * from emp where not job='MANAGER';
SQL> SELECT * FROM EMP WHERE JOB NOT IN ('MANAGER','CLERK');SQL> Select name from trial where name like '%\_%' ESCAPE '\';
NAME--------------------st_thomassri_ramajayamlathika_saranabi_nayaaa_rvinthan
Note: Search for special characters : By using ESCAPE
24.09.2008To Insert Data in particular fields.SQL> Insert into cig_emp (Empno,Name) values(2000,'Vignesh');
Update : To made Changes in Existing Records.Ex:
SQL> Update Cig_Emp1 Set DOJ='02-May-2008' where empno=2000; Note: Update in a Single Field.
SQL> Update Cig_Emp1 Set Job='DBA',sal=5000,deptno=30 where name='Vignesh'; Note: To update in multiple Fields.
SQL> Update CIG_EMP1 set sal=sal*1.20; Note: To increase 20% of sal to all employees.
![Page 4: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/4.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 4/173
CONSTRAINTS1. NOT NULL2. UNIQUE3. CHECK
4. PRIMARY KEY (UNIQUE + NOT NULL)5. FOREIGN KEY
To create the table with Primary Key & Not NullSQL> CREATE TABLE CIG_EMP2(ENO NUMBER PRIMARY KEY,NAMEVARCHAR2(30) NOT NULL,DEPTNO NUMBER,SAL NUMBER);
Note: We can’t enter duplicate values in ENO & with out leaving the NAME Field.
How to set Default Value .SQL> CREATE TABLE CIG_EMP2(ENO NUMBER PRIMARY KEY,NAMEVARCHAR2(30) NOT NULL,DOJ DATE DEFAULT SYSDATE,DEPTNO
NUMBER);Ex:SQL> INSERT INTO CIG_EMP2(ENO,NAME,DEPTNO) VALUES(&ENO,'&NAME',&DNO);Or SQL> INSERT INTO CIG_EMP2 (ENO,NAME,DEPTNO) VALUES(1000,'ASHOK',40);
SQL> Insert into CIG_EMP2 values (1004,'Raghu','02-May-2008',40); Note: If we give value then take that value otherwise take the default value.
CHECK SQL> Create table cig_emp3 (eno number primary key,name varchar2(30) notnull,deptno number check(deptno<50));
Note: Allows deptno is less than 50
How to Add Not Null in existing Column?SQL> Alter table cig modify(name varchar2(20) NOT NULL);
How To add constraint?SQL> Alter table cig add constraint cons1 PRIMARY KEY(no);
Add Primary Key for Column SetsSQL> CREATE TABLE TR _ TAB 1(ID NUMBER , NAME VARCHAR 2(20), SAL NUMBER , CONSTRAINT TR _ CONS PRIMARY KEY(ID, NAME ))
Note: If we create as like above then it check for 2 columns not a single column.
![Page 5: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/5.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 5/173
Ex: SQL> select * from tr_tab1;
ID NAME SAL---------- -------------------- ----------
1000 Saran 2500
1000 Seenu 25002000 Seenu 2500
How to Handle More than one Table?1. List EmpNo, Name, DepartmentNo, Department Name,JOBSQL> select cig_emp2.eno,cig_emp2.name,cig_emp2.deptno,cig_dept.dname fromcig_emp2,cig_dept where cig_emp2.deptno = cig_dept.deptno;
By using ALIAS Name to the TableSQL> Select e.eno,e.name,d.dname from cig_emp2 e,cig_dept d where
e.deptno=d.deptno;
SortingSQL> select * from cig_emp2 order by name;
Note: In Default : Ascending
DescendingSQL> select * from cig_emp2 order by name desc;
FunctionsSingle Row Function
• General functions:– NVL– NVL2– NULLIF– COALESCE– CASE
– DECODE
Function DescriptionNVL Converts a null value to an actual value
NVL2 If expr1 is not null, NVL2 returns expr2 . If expr1 is null, NVL2 returns expr3 .The argument expr1 can have any data type.
NULLIF Compares two expressions and returns null if they are equal, or the firstexpression if they are not equal
COALESCE Returns the first non-null expression in the expression list
![Page 6: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/6.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 6/173
Ex: NVLSQL> select empno,ename,nvl(comm,100) from emp;
Note: Substitute the value 100 for null value.
SQL> select ename,sal*nvl(comm,1) from emp;
SQL> select empno,ename,nvl(hiredate,sysdate) from emp; Note: Show system date for null of hiredate
SQL> select empno,nvl(ename,'Prasanth') from emp;
NVL2SQL> Select empno,ename,comm,nvl2(comm,'YES','NO') "Commision Status" fromemp;
Ex:EMPNO ENAME COMM Com
---------- ---------- ---------- ---7369 SMITH NO7499 ALLEN 300 YES7521 WARD 500 YES7566 JONES NO
NULLIFSyntaxNULLIF ( expr1 , expr2 )In the syntax:expr1 is the source value compared to expr2expr2 is the source value compared with expr1 . (If it is not equal to expr1 , expr1is returned.)
Note: The NULLIF function is logically equivalent to the following CASE expression. TheCASEexpression is discussed in a subsequent page:CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END
Ex:SQL> select ename,job,NULLIF(EMPNO,MGR) "NULLIF" FROM EMP;
ENAME JOB NULLIF---------- --------- ----------SMITH CLERK 7369ALLEN SALESMAN 7499WARD SALESMAN 7521JONES MANAGER 7566XX Sales 1321the manager 1121Saran 100ASHOK TECH
The COALESCE FunctionThe COALESCE function returns the first non-null expression in the list.
![Page 7: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/7.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 7/173
SyntaxCOALESCE ( expr1 , expr2, ... exprn )In the syntax:expr1 returns this expression if it is not nullexpr2 returns this expression if the first expression is null and this expression is notnullexprn returns this expression if the preceding expressions are null
SQL> Select ename,coalesce(comm,sal,1000) "COALESCE" From emp;
ENAME COALESCE---------- ----------SMITH 800ALLEN 300WARD 500JONES 2975MARTIN 1400the 5000Saran 1000ASHOK 25
Conditional Expressions• Provide the use of IF-THEN-ELSE logic within aSQL statement• Use two methods:– CASE expression
– DECODE function
SQL> Select ename,job,sal "Current Salary", CASE JOBWhen 'CLERK' THEN 1.10 * SAL
2 When 'SALESMAN' Then 1.15 * sal3 When 'MANAGER' Then 1.20 * Sal4 ELSE5 Sal6 End "Revised Salary" From Emp;
ENAME JOB Current Salary Revised Salary
---------- --------- -------------- --------------SMITH CLERK 800 880ALLEN SALESMAN 1600 1840WARD SALESMAN 1250 1437.5JONES MANAGER 2975 3570MARTIN SALESMAN 1250 1437.5BLAKE MANAGER 2850 3420kannan SALESMAN 850 977.5
![Page 8: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/8.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 8/173
SCOTT ANALYST 300 300
SQL> Select Ename,Job,Sal "Current Salary", DECODE(Job, 'CLERK', 1.10 * Sal,'SALESMAN',1.15 * Sal, 'MANAGER', 1.20 * Sal, sal) "Revised Sal" From Emp;
ENAME JOB Current Salary Revised Sal---------- --------- -------------- -----------SMITH CLERK 800 880ALLEN SALESMAN 1600 1840WARD SALESMAN 1250 1437.5JONES MANAGER 2975 3570MARTIN SALESMAN 1250 1437.5BLAKE MANAGER 2850 3420
Try it Yourself
1. Find a Grade for the Employees Depends upon theSalary:
‘A’ > 3000‘B’ > 2000 & <=3000‘C’ Others
27-09-2008
Character-Manipulation FunctionsThese functions manipulate character strings:
CONCAT – To Add more than one text.Ex:1SQL> select concat('Hello',' World') from Dual;
CONCAT('HEL-----------Hello World
SQL> Select Concat(Ename,Job) from Emp;CONCAT(ENAME,JOB)-------------------SMITHCLERK
ALLENSALESMAN
WARDSALESMANJONESMANAGER
In Real Time Example.SQL> Select ename || ' is a ' || job "Ename with Job" From Emp;
Ename with Job
![Page 9: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/9.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 9/173
-------------------------SMITH is a CLERKALLEN is a SALESMANWARD is a SALESMANJONES is a MANAGER
SUBSTR – To extract a particular character(s) from theexisting text.Syntax: SubStr(text,startpos,no.of Char)SQL> Select SubStr('Murshidh',4,5) from dual;
SUBST-----Shidh
SQL> Select SubStr('Ashok',3) from Dual;
SUB---Hok
To extract from Right of the Text.SQL> Select Substr(ename,-3) from emp;
SUB---ITHLEN
LENGTH
SQL> Select Length('Ashok') from Dual;LENGTH('ASHOK')---------------
5
SQL> Select Ename, Length(Ename) "Length" from Emp;
ENAME Length---------- ----------SMITH 5ALLEN 5
![Page 10: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/10.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 10/173
SQL> Select * from Emp where Length(Ename)=5;
EMPNO ENAME JOB MGR HIREDATE---------- ---------- --------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-80
7499 ALLEN SALESMAN 7698 20-FEB-817566 JONES MANAGER 7839 02-APR-81
OrSQL> Select * from emp Where Ename Like '_____';
EMPNO ENAME JOB MGR HIREDATE---------- ---------- --------- ---------- ---------
7369 SMITH CLERK 7902 17-DEC-807499 ALLEN SALESMAN 7698 20-FEB-817566 JONES MANAGER 7839 02-APR-81
INSTR – To find the Position of the Character in the Text.
SQL> Select Instr('Ashok','o') From Dual;
INSTR('ASHOK','O')------------------
4
SQL> Select Instr('Murshidh','o') From Dual;
INSTR('MURSHIDH','O')---------------------
0
SQL> Select Instr('amirtha','a') From Dual;
INSTR('AMIRTHA','A')--------------------
1
SQL> Select Instr('amirtha','a',2) From Dual;
INSTR('AMIRTHA','A',2)----------------------
7
Exercise:Extract the Characters upto second ‘-‘ in a columnSQL> select * from ex1;
![Page 11: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/11.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 11/173
COL1------------------------------5676-2345-6572-567256-57-565
567-676-752-1105-7865-656-756
ResultSelect Substr(col1,1,Instr(col1,'-',instr(col1,'-')+1)-1)from ex1/SUBSTR(COL1,1,INSTR(COL1,'-',I------------------------------5676-234556-57567-6765-7865
LPAD – To Add Given Character in Left Side (For filling theEmpty Space)SQL> Select LPAD('Saranya',20,'#') From Dual;
LPAD('SARANYA',20,'#--------------------#############Saranya
SQL> Select Lpad(Ename,25,'*') From Emp;
LPAD(ENAME,25,'*')-------------------------********************SMITH********************ALLEN*********************WARD
RPAD – To Add Given Character in Right Side (For fillingthe Empty Space)
SQL> Select Rpad('Kiran',20,'$') From Dual;
RPAD('KIRAN',20,'$')--------------------Kiran$$$$$$$$$$$$$$$
SQL> Select Rpad(sal,15,'%') From emp;
![Page 12: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/12.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 12/173
RPAD(SAL,15,'%'---------------800%%%%%%%%%%%%1600%%%%%%%%%%%
1250%%%%%%%%%%%TRIM – To remove the blank space or first character of atext.SQL> Select Trim('a' From 'aaaalagi') from dual;TRIM----LagiSQL> select trim(ename) from emp;
TRIM(ENAME----------SMITHALLEN
29.09.2008Replace :Syntax: Replace(Text,Searching Text, Replacing Text);
Ex:SQL> Select Replace('saravanan','a','z') from dual;
REPLACE('---------szrzvznznSQL> Select Replace ('This is sample','is','was') from dual;
REPLACE('THISISS----------------Thwas was sample
SQL> select ename,replace(ename,'a','x') from emp;
ENAME REPLACE(EN---------- ----------kannan kxnnxnSCOTT SCOTTkannan kxnnxnthe thekumar kumxrnithya1 nithyx1
![Page 13: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/13.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 13/173
To remove all ‘a’ in a Given TextSQL>select replace('aaaalagi','a') from dual
REP
---lgi
Case Manipulation FunctionsThese functions convert case for character strings.LOWER(’SQL Course’)UPPER(’SQL Course’)INITCAP(’SQL Course’)
SQL> Select Lower('AShok') From dual;
LOWER-----ashokSQL> SelectLower(ename),Upper(Ename),Initcap(Ename) From emp;
LOWER(ENAM UPPER(ENAM INITCAP(EN---------- ---------- ----------smith SMITH Smith
allen ALLEN Allenward WARD Wardjones JONES Jonesmartin MARTIN Martin
Number Functions• ROUND: Rounds value to specified decimalROUND(45.926, 2) 45.93SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),
2 ROUND(45.923,-1)3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)-------------- --------------- ----------------
45.92 46 50
• TRUNC: Truncates value to specified decimal
![Page 14: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/14.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 14/173
TRUNC(45.926, 2) 45.92
SQL> select round(2000.3589,2),Trunc(2000.3589,2)From Dual;
ROUND(2000.3589,2) TRUNC(2000.3589,2)------------------ ------------------
2000.36 2000.35
SQL> Select Round(3564.25,-2) from Dual;
ROUND(3564.25,-2)-----------------
3600
TRUNC(3554.25,-3)-----------------
3000
• MOD: Returns remainder of division MOD(1600, 300) 100
SQL> Select Mod(26,5) from dual;
MOD(26,5)
---------- 1Calculate the remainder of a salary after it is dividedby 5000 for all employees whose job title is salesrepresentative.
SELECT last_name, salary, MOD(salary, 5000)FROM employees WHERE job_id = 'SA_REP'/
LAST_NAME SALARY MOD(SALARY,5000)
-------------------- ---------- ----------------Abel 11000 1000Grant 7000 2000
Date FunctionsTo view the System DateSQL> select sysdate from dual;
![Page 15: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/15.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 15/173
SYSDATE---------29-SEP-08
Arithmetic with Dates• Add or subtract a number to or from a date for aresultant date value.• Subtract two dates to find the number of days
between those dates.• Add hours to a date by dividing the number ofhours by 24.
SQL> SELECT last_name, (SYSDATE-hire_date)/7 ASWEEKS FROM employees WHERE department_id = 90;
LAST_NAME WEEKS-------------------- ----------King 1110.78507Kochhar 992.642216De Haan 818.92793
For example, display the employee number, hire date, number of months employed, six-month review date, first Friday after hire date, and last day of the hire month for allemployees employed for fewer than 36 months.
SQL> SELECT employee_id, hire_date,2 MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,3 ADD_MONTHS (hire_date, 6) REVIEW,4 NEXT_DAY (hire_date, 'FRIDAY'),
LAST_DAY(hire_date)5* FROM employees
EMPLOYEE_ID HIRE_DATE TENURE REVIEW NEXT_DAY( LAST_DAY(
---------- --------- ---------- --------- --------- ---------100 17-JUN-87 255.403214 17-DEC-87 19-JUN-87 30-JUN-87101 21-SEP-89 228.274182 21-MAR-90 22-SEP-89 30-SEP-89102 19-JAN-93 188.338698 19-JUL-93 22-JAN-93 31-JAN-93103 03-JAN-90 224.854827 03-JUL-90 05-JAN-90 31-JAN-90104 21-MAY-91 208.274182 21-NOV-91 24-MAY-91 31-MAY-91
Using Date Functions
![Page 16: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/16.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 16/173
• MONTHS_BETWEEN (’01-SEP-95’,’11-JAN-94’)SQL> Select Round(Months_Between(Sysdate,To_Date('15-Jan-1975'))/12) From Dual;
•
ADD_MONTHS (’11-JAN-94’,6)SQL> Select Sysdate,ADD_MONTHS(SYSDATE,6) FromDual;
SYSDATE ADD_MONTH--------- ---------29-SEP-08 29-MAR-09
• NEXT_DAY (’01-SEP-95’,’FRIDAY’)SQL> Select Sysdate,Next_Day(SYSDATE,'WEDNESDAY')
From Dual;
SYSDATE NEXT_DAY(--------- ---------29-SEP-08 01-OCT-08
• LAST_DAY(’01-FEB-95’)SQL> Select Last_Day(Sysdate) From Dual;
LAST_DAY(---------30-SEP-08
Explicit Data Type Conversion
To_CharSQL> Select sysdate,To_Char(sysdate,'MM/YY') FromDual;
SYSDATE TO_CH--------- -----29-SEP-08 09/08
AS Like We use the Below Terms YYYY YEARMM
![Page 17: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/17.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 17/173
MONTHDYDAYMONDD
To Spelled the YearSQL> Select To_Char(sysdate,'YEAR') From Dual;
TO_CHAR(SYSDATE,'YEAR')------------------------------------------TWO THOUSAND EIGHT
To Spelled the day
SQL> Select To_Char(To_Date('15-Jan-75'),'ddspth')from dual;
TO_CHAR(TO_DAT--------------Fifteenth
To Spelled the Day of the DateSQL> Select To_Char(sysdate,'DAY') From Dual;
TO_CHAR(S---------MONDAYNote: For ‘MON’ – Use ‘DY’
To Spelled the Month of the DateSQL> Select To_Char(sysdate,'MONTH') From Dual;
TO_CHAR(S---------
SEPTEMBERNote: For ‘JAN’ – Use ‘MON’
To Show in Roman LetterSQL> Select To_Char(sysdate,'RM') From Dual;
TO_C
![Page 18: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/18.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 18/173
----IX
SQL> Select To_Char(sysdate,'DD "of" Month') From Dual;
TO_CHAR(SYSDATE---------------29 of September
SQL> Select To_char(sysdate,'DD') || ' of ' ||To_Char(sysdate,'Month') from dual;
TO_CHAR(SYSDATE
---------------29 of September
SQL> SELECT last_name,2 TO_CHAR(hire_date, 'fmDD Month YYYY')3 AS HIREDATE4* FROM employees;
LAST_NAME HIREDATE-------------------- -----------------
King 17 June 1987Kochhar 21 September 1989De Haan 19 January 1993Hunold 3 January 1990Ernst 21 May 1991
9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
![Page 19: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/19.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 19/173
, Prints a thousand indicator
SQL> Select To_Char(sal,'9,99,999.99') from emp;
TO_CHAR(SAL,------------
800.001,600.001,250.002,975.001,250.002,850.00
SQL>Select To_Char(sal,'0,99,999.99') from empTO_CHAR(SAL,------------
0,00,800.000,01,600.000,01,250.00
SQL > Select To_Char(sal,'$9,99,999.99') from emp;
TO_CHAR(SAL,'-------------$800.00
$1,600.00$1,250.00$2,975.00
SQL > Select To_Char(sal,'L9,99,999.99') from emp;TO_CHAR(SAL,'L9,99,999----------------------
£800.00£1,600.00£1,250.00£2,975.00
For Negative values onlySQL > select To_Char(-4500,'999999MI') from dual;
![Page 20: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/20.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 20/173
TO_CHAR-------
4500-
SQL>select To_Char(-4500,'999999PR') from dual;
TO_CHAR(--------
<4500>
SQL> Select To_Char(4578456874,'9999EEEE') from dual;
TO_CHAR
-------5E+09
V Multiply by 10 n times ( n = number of 9s after V) 9999V99 123400SQL> Select To_Char(1234,'9999V99') from Dual;
TO_CHAR-------
123400
B - Display zero values as blank, not 0 B9999.99 1234.00SQL> select To_Char(0,'9B') from dual;
TO--
To_NumberSQL> Select To_Number('145') from dual;
TO_NUMBER('145')----------------
145
To_DateSQL> Select To_Date('19-Sep-2008') from dual;
![Page 21: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/21.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 21/173
TO_DATE('---------19-SEP-08
SQL> Select To_Date('25-Jul-1973') + 7 From dual;
TO_DATE('---------01-AUG-73
30-09-2008Displaying Data From Multiple Tables
Cartesian Products
When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. All rows in the firsttable are joined to all rows in the second table.A Cartesian product tends to generate a large number of rows, and the result israrely useful. You should always include a valid join condition in a WHEREclause, unless you have a specific need to combine all rows from all tables.Cartesian products are useful for some tests when you need to generate a largenumber of rows to simulate a reasonable amount of data.
Ex:
Sql>Select Ename,Dname From Emp,Dept;135 Rows Selected.. (27 x 5 Records)
Note: Cartesian Product occursSQL> Set Pause On;SQL> Set Pause “Press any key to Continue..”(Wait for each page)
To Avoid Cartesian Product..
![Page 22: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/22.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 22/173
SQL > Select Ename,emp.deptno,Dname from emp,deptwhere emp.deptno=dept.deptno;
ENAME DEPTNO DNAME---------- ---------- --------------SMITH 20 RESEARCHALLEN 30 SALESWARD 30 SALESJONES 20 RESEARCHMARTIN 30 SALESBLAKE 30 SALES
Note: If we use the common named columns then weuse the table name also as prefix. (emp.deptno)Note: Equijoins are also called simple joins or inner joins .
Using AND condition.SQL>Select Ename,emp.deptno,Dname from emp,deptwhere emp.deptno=dept.deptno AND dept.deptno=20;
ENAME DEPTNO DNAME---------- ---------- --------------SMITH 20 RESEARCH
![Page 23: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/23.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 23/173
JONES 20 RESEARCHSCOTT 20 RESEARCHADAMS 20 RESEARCH
Using Table AliasQualifying Ambiguous Column NamesYou need to qualify the names of the columns in the WHERE clause with the table name to avoidambiguity. Without the table prefixes, the DEPARTMENT_ID column could be from either theDEPARTMENTS table or the EMPLOYEES table. It is necessary to add the table prefix toexecute your query.
SQL> Select e.ename,e.job,e.sal,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;ENAME JOB SAL DEPTNO DNAME LOC---------- --------- ---------- ---------- -------------- -------------SMITH CLERK 800 20 RESEARCH DALLASALLEN SALESMAN 1600 30 SALES CHICAGOWARD SALESMAN 1250 30 SALES CHICAGOJONES MANAGER 2975 20 RESEARCH DALLASMARTIN SALESMAN 1250 30 SALES CHICAGO
SQL > Select e.ename,d.dname,l.name From emp e,deptd,location l where e.deptno=d.deptno andd.loc_id=l.loc_id
![Page 24: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/24.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 24/173
ENAME DNAME NAME---------- -------------- --------------------kannan ACCOUNTING EASTkannan ACCOUNTING EASTRaman ACCOUNTING EASTXX ACCOUNTING EASTSMITH RESEARCH WESTADAMS RESEARCH WESTFORD RESEARCH WEST
SQL >Select Distinct e.ename,d.dname,l.name From emp e,dept d,location l where e.deptno=d.deptno andd.loc_id=l.loc_id;
ENAME DNAME NAME---------- -------------- --------------------ADAMS RESEARCH WESTALLEN SALES SOUTHASHOK RESEARCH WESTBLAKE SALES SOUTHFORD RESEARCH WESTJAMES SALES SOUTHJONES RESEARCH WEST
Non-EquijoinsA non-equijoin is a join condition containing something other than an equality operator.The relationship between the EMPLOYEES table and the JOB_GRADES table has anexample of a non-equijoin. A relationship between the two tables is that the SALARYcolumn in the EMPLOYEES table must be between the values in the LOWEST_SALARYand HIGHEST_SALARY columns of the JOB_GRADES table. The relationship isobtained using an operator other than equals (=).
SQL> SELECT e.last_name, e.salary, j.grade_level2 FROM employees e, job_grades j3 WHERE e.salary4 BETWEEN j.lowest_sal AND j.highest_sal;
LAST_NAME SALARY GRA-------------------- ---------- ---Matos 2600 AVargas 2500 A
![Page 25: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/25.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 25/173
Lorentz 4200 BRajs 3500 BDavies 3100 BWhalen 4400 BHunold 9000 C
The slide example creates a non-equijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.It is important to note that all employees appear exactly once when this query is executed. Noemployee is repeated in the list. There are two reasons for this:• None of the rows in the job grade table contain grades that overlap. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in thesalary grade table.• All of the employees’ salaries lie within the limits provided by the job grade table. That is, noemployee earns less than the lowest value contained in the LOWEST_SAL column or more thanthe highest value contained in the HIGHEST_SAL column.Note: Other conditions, such as <= and >= can be used, but BETWEEN is the simplest.Remember to specify the low value first and the high value last when using BETWEEN.Table aliases have been specified in the slide example for performance reasons, not because of
possible ambiguity.SQL> SELECT e.last_name, e.salary, j.grade_level FROM employeese, job_grades j WHERE e.salary >= j.lowest_sal AND e.salary <=j.highest_sal
LAST_NAME SALARY GRA-------------------- ---------- ---Matos 2600 AVargas 2500 ALorentz 4200 BRajs 3500 B
![Page 26: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/26.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 26/173
SQL > select ename,dept.deptno,dept.dname from emp,dept whereemp.deptno(+)=dept.deptno
ENAME DEPTNO DNAME---------- ---------- --------------kannan 10 ACCOUNTINGkannan 10 ACCOUNTINGBLAKE 30 SALESMARTIN 30 SALESJAMES 30 SALESTURNER 30 SALESWARD 30 SALESPRIYA 40 OPERATIONS
65 ADMINSQL>select ename,dept.deptno,dept.dname from emp,dept where
emp.deptno=dept.deptno(+)
ENAME DEPTNO DNAME---------- ---------- --------------
SMITH 20 RESEARCHADAMS 20 RESEARCHJAMES 30 SALESFORD 20 RESEARCH
10 ACCOUNTINGthekumarnithya1
![Page 27: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/27.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 27/173
Raman 20 RESEARCHRaman 10 ACCOUNTINGPRIYA 40 OPERATIONS
ENAME DEPTNO DNAME---------- ---------- --------------XX 10 ACCOUNTINGthe 20 RESEARCHSaranASHOK 20 RESEARCH
Ashok
SQL> edWrote file afiedt.buf
SQL > SELECT worker.last_name || ' works for '|| manager.last_name
FROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME
---------------------------------------------------Kochhar works for KingDe Haan works for KingHaritstein works for KingZlotkey works for KingWhalen works for Kochhar
![Page 28: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/28.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 28/173
Higgins works for KochharHunold works for De HaanLorentz works for HunoldAbel works for ZlotkeyGrant works for ZlotkeyFay works for HaritsteinGietz works for Higgins
12 rows selected.03-Oct-2008
CROSS JOINSQL> Select Empno,Dept.deptno,Dname From Emp CROSS JOIN Dept;
![Page 29: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/29.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 29/173
EMPNO DEPTNO DNAME---------- ---------- --------------
7369 10 ACCOUNTING7499 10 ACCOUNTING
---- --100 65 ADMIN
5000 65 ADMIN145 65 ADMIN
135 rows selected.
ORSQL> Select Empno,Dept.Deptno,Dname From Emp,Dept;
NATURAL JOINSQL>Select Empno,Deptno,Ename,Dname From Emp NATURAL JOIN Dept
EMPNO DEPTNO ENAME DNAME
---------- ---------- ------------------------- --------------7369 20 SMITH RESEARCH7499 30 ALLEN SALES7521 30 WARD SALES7566 20 JONES RESEARCH7654 30 MARTIN SALES7698 30 BLAKE SALES4567 10 kannan ACCOUNTING7788 20 SCOTT RESEARCH4568 10 kannan ACCOUNTING7844 30 TURNER SALES7876 20 ADAMS RESEARCH7900 30 JAMES SALES7902 20 FORD RESEARCH
Using Where Clause in NATURAL JOINSQL>Select Empno,Deptno,Ename,Dname From Emp NATURAL JOIN DeptWhere Deptno in (20,40)
EMPNO DEPTNO ENAME DNAME--------- ---------- ------------------------------ ----------
7512 40 PRIYA OPERATIONS7369 20 SMITH RESEARCH7566 20 JONES RESEARCH7788 20 SCOTT RESEARCH
JOIN & USING (As like EQUI JOIN)SQL> Select e.ename,d.dname from emp e join dept d using(deptno);
ENAME DNAME------------------------------ --------------SMITH RESEARCHALLEN SALES
![Page 30: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/30.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 30/173
WARD SALESJONES RESEARCHMARTIN SALESBLAKE SALESkannan ACCOUNTINGSCOTT RESEARCH
![Page 31: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/31.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 31/173
LEFT OUTER JOIN
![Page 32: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/32.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 32/173
Instructor NoteIt was not possible to complete this in earlier releases using outer joins. However, you couldaccomplish the same results using the UNION operator.SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id (+) = d.department_idUNIONSELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id (+);
![Page 33: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/33.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 33/173
Aggregating DataUsing Group Functions
Group FunctionsUnlike single-row functions, group functions operate on sets of rows to give one result per group.These sets may be the whole table or the table split into groups.
![Page 34: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/34.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 34/173
![Page 35: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/35.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 35/173
![Page 36: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/36.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 36/173
![Page 37: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/37.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 37/173
Groups of DataUntil now, all group functions have treated the table as one large group of information. At times,you need to divide the table of information into smaller groups. This can be done by using theGROUP BY clause.
![Page 38: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/38.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 38/173
![Page 39: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/39.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 39/173
![Page 40: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/40.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 40/173
![Page 41: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/41.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 41/173
![Page 42: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/42.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 42/173
Standard Deviation
SQL> select stddev(sal) from emp;
STDDEV(SAL)-----------
14044.1804
1 row selected.
SQL> select variance(sal) from emp;
VARIANCE(SAL)-------------
197239002
1 row selected.
04-Oct-2008
SubqueriesIn this lesson, you learn about more advanced features of the SELECT statement. You can writesubqueries in the WHERE clause of another SQL statement to obtain values based on an unknown
conditional value. This lesson covers single-row subqueries and multiple-row subqueries.
![Page 43: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/43.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 43/173
![Page 44: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/44.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 44/173
![Page 45: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/45.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 45/173
Note: The outer and inner queries can get data from different tables.
![Page 46: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/46.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 46/173
![Page 47: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/47.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 47/173
![Page 48: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/48.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 48/173
![Page 49: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/49.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 49/173
![Page 50: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/50.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 50/173
Multiple-Row Subqueries (continued)The ANY operator (and its synonym, the SOME operator) compares a value to each valuereturned by a subquery. The slide example displays employees who are not IT programmers andwhose salary is less than that of any IT programmer. The maximum salary that a programmer earns is $9,000.<ANY means less than the maximum. > ANY means more than the minimum. = ANY isequivalent to
IN .<ALL means less than the maximum. > ALL means more than the minimum
![Page 51: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/51.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 51/173
Multiple-Row Subqueries (continued)The ALL operator compares a value to every value returned by a subquery. The slide exampledisplaysemployees whose salary is less than the salary of all employees with a job ID of IT_PROG andwhose job is not IT_PROG.>ALL means more than the maximum, and <ALL means less than the minimum.The NOT operator can be used with IN , ANY, and ALL operators.
![Page 52: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/52.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 52/173
Manipulating Data
![Page 53: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/53.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 53/173
![Page 54: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/54.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 54/173
![Page 55: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/55.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 55/173
To create a Table from existing with record SQL> Create Table Dept_Dupe As Select * from Dept;
Table created.
To create a Table from existing with out record SQL> Create Table Dept_Dupe1 As Select * from Dept Where 0=1;
To insert RecordsSQL> Insert into Dept_Dupe1 Select * From Dept;
To a Particular ColumnSQL> Insert into Dept_Dupe1(Dname) Select Loc_Id from Dept;
![Page 56: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/56.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 56/173
6-Oct-2008
To change a name in the existing record?SQL> Update emp SET ENAME = 'ABISHEIK' where ename='kannan';
To Change in 2 columnsSQL> UPDATE EMP SET ENAME='THIRU',JOB='TECH' WHERE EMPNO=4569;
Update for 2 Emp numbersSQL> UPDATE EMP SET ENAME='PRASANTH',JOB='CONSULT' WHEREEMPNO=1254 OR EMPNO=1245;
To Change the salary as 1000 those who get Less than 1000SQL> Update emp set sal=1000 Where SAL < 1000;
![Page 57: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/57.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 57/173
Ex:SQL> Update Emp set Job = (Select Job from emp whereename='PRIYA'),Sal = (Select Sal from Emp WhereEname='PRIYA') Where Ename='ABISHEIK';
![Page 58: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/58.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 58/173
Ex:SQL> UPDATE EMP2 SET ENAME=(Select Ename From Emp WhereEmpno=4569) Where empno=4569;
![Page 59: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/59.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 59/173
EX:SQL> Truncate Table Departments;Truncate Table Departments
*ERROR at line 1:
![Page 60: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/60.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 60/173
ORA-02266: unique/primary keys in table referenced byenabled foreign keys
Ex:To delete a particular rowSQL>Delete From Emp2 Where Empno=1000;
To Delete all RowsSQL>Delete From Emp2;
Note: Rollback the Deleted Rows
![Page 61: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/61.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 61/173
Using INSQL>Delete From Emp Where Empno IN (145,100,1321,11111)
Ex:SQL> Delete From Emp Where Deptno = (Select deptno from dept where dname='OPERATIONS');
![Page 62: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/62.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 62/173
![Page 63: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/63.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 63/173
![Page 64: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/64.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 64/173
ID NAME DEPTNO---------- ------------------------- ----------
1000 PRASANTH 202000 ASHOK 20
3000 20SQL> UPDATE CIG_STAFF SET NAME=DEFAULT WHERE ID=1000;
1 row updated.
SQL> SELECT * FROM CIG_STAFF;
ID NAME DEPTNO---------- ------------------------- ----------
1000 202000 ASHOK 203000 20
![Page 65: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/65.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 65/173
Ex:SQL> Merge into copy_staff cs using cig_staff ot
2 on (cs.id = ot.id)3 When Matched then4 Update set cs.name = ot.name5 , cs.deptno=ot.deptno6 When Not Matched Then7 Insert values (ot.id,ot.name,ot.deptno);
5 rows merged.
![Page 66: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/66.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 66/173
![Page 67: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/67.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 67/173
Ex:SQL> commit;
Commit complete.
SQL> insert into copy_staff values(8000,'Saro',65);1 row created.
SQL> SavePoint A;
Savepoint created.
SQL> insert into copy_staff values(9000,'Seenu',75);
1 row created.
SQL> SavePoint B;
Savepoint created.
SQL> insert into copy_staff values(7500,'raja',85);
1 row created.
SQL> rollback to savepoint A;
Rollback complete.
SQL> select * from copy_staff;
ID NAME DEPTNO---------- ------------------------- ----------
1000 Saran 202000 ASHOK 203000 Saran 205000 Abishiek 604000 Thiru 508000 Saro 65
6 rows selected.
SQL> rollback;
Rollback complete.
SQL> select * from copy_staff;
![Page 68: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/68.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 68/173
ID NAME DEPTNO---------- ------------------------- ----------
1000 Saran 202000 ASHOK 20
3000 Saran 205000 Abishiek 604000 Thiru 50
SQL> delete from copy_staff;
5 rows deleted.
SQL> rollback;
Rollback complete.
SQL> select * from copy_staff;
ID NAME DEPTNO---------- ------------------------- ----------
1000 Saran 202000 ASHOK 203000 Saran 205000 Abishiek 604000 Thiru 50
![Page 69: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/69.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 69/173
11-Oct-08
Creating and Managing Tables
![Page 70: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/70.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 70/173
![Page 71: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/71.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 71/173
Ex:SQL>Select * From SCOTT.EMP;
![Page 72: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/72.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 72/173
Ex:SQL>CREATE TABLE STAFF(ENO NUMBER,ENAME VARCHAR2(25),DOJ DATE DEFAULT SYSDATE);
![Page 73: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/73.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 73/173
Ex:SQL> SELECT * FROM USER_TABLES;SQL> SELECT * FROM USER_VIEWS;SQL> SELECT * FROM USER_SYNONYMS;SQL> SELECT * FROM USER_SEQUENCES;
USE ALL_ SQL> SELECT * FROM ALL_TABLES;
SQL> SELECT * FROM ALL_VIEWS;
![Page 74: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/74.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 74/173
![Page 75: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/75.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 75/173
![Page 76: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/76.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 76/173
![Page 77: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/77.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 77/173
![Page 78: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/78.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 78/173
![Page 79: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/79.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 79/173
![Page 80: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/80.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 80/173
![Page 81: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/81.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 81/173
![Page 82: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/82.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 82/173
![Page 83: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/83.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 83/173
![Page 84: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/84.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 84/173
Ex:SQL> SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME ='EMPLOYEES';
Including Constraints
![Page 85: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/85.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 85/173
![Page 86: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/86.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 86/173
![Page 87: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/87.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 87/173
Ex:Sql>CREATE TABLE CIG_CONS1(ID NUMBER,NAME VARCHAR2(15),NICK
VARCHAR2(10), CONSTRAINT CONS_1 UNIQUE(name,nick));
![Page 88: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/88.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 88/173
![Page 89: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/89.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 89/173
![Page 90: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/90.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 90/173
![Page 91: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/91.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 91/173
![Page 92: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/92.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 92/173
![Page 93: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/93.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 93/173
![Page 94: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/94.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 94/173
![Page 95: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/95.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 95/173
![Page 96: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/96.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 96/173
ISQLPLUS
![Page 97: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/97.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 97/173
Note:If we give the value by “Define” then take that valueotherwise it asking for a value.
SQL>DEFINE TN=DEPT;SQL>SELECT * FROM &TN;
![Page 98: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/98.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 98/173
![Page 99: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/99.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 99/173
![Page 100: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/100.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 100/173
![Page 101: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/101.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 101/173
![Page 102: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/102.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 102/173
Note: SQL>SET VERIFY OFF; Now old & new are skipped.
![Page 103: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/103.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 103/173
![Page 104: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/104.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 104/173
![Page 105: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/105.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 105/173
Ex:
![Page 106: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/106.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 106/173
![Page 107: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/107.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 107/173
![Page 108: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/108.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 108/173
'Data in Thousands'
Monthly Analysis 01 Jan 2001 Page: 1Data in Thousands
To suppress the top title display without changing its definition, enter
TTITLE OFF
Ex:SQL>REPHEADER “WELCOME”SQL>REPFOOTER “GOOD BYE”
15-Oct-08
Creating Views
Ex:
![Page 109: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/109.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 109/173
SQL>create or replace view emp_view AS Select employee_number,ename,sal from emp;
![Page 110: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/110.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 110/173
![Page 111: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/111.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 111/173
SQL> SELECT * FROM CVIEW;
DEPTNO MINSAL MAXSAL AVGSAL---------- ---------- ---------- ----------
10 1000 3740 229020 1000 71500 11315.312530 1045 3135 1723.3333340 2200 2200 2200
![Page 112: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/112.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 112/173
![Page 113: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/113.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 113/173
![Page 114: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/114.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 114/173
![Page 115: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/115.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 115/173
•
![Page 116: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/116.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 116/173
![Page 117: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/117.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 117/173
![Page 118: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/118.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 118/173
![Page 119: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/119.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 119/173
![Page 120: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/120.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 120/173
![Page 121: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/121.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 121/173
16-Oct-08
Ex:SQL>ALTER SEQUENCE CIG_SEQ1 INCREMENT BY 5;
![Page 122: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/122.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 122/173
Ex:SQL> ALTER SEQUENCE CIG_SEQ1 MAXVALUE 50;ALTER SEQUENCE CIG_SEQ1 MAXVALUE 50*ERROR at line 1:ORA-04009: MAXVALUE cannot be made to be less thanthe current value
Ex:SQL> DROP SEQUENCE CIG_SEQ1;
Sequence dropped.
![Page 123: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/123.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 123/173
![Page 124: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/124.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 124/173
![Page 125: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/125.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 125/173
![Page 126: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/126.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 126/173
Let students know that to create a function-based index in your own schema on your own table,you must have the CREATE INDEX and QUERY REWRITE system privileges. To create theindex in another schema or on another schema’s table, you must have the CREATE ANY INDEX
and GLOBAL QUERY REWRITE privileges. The table owner must also have the EXECUTEobject privilege on the functions used in the function-based index.
![Page 127: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/127.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 127/173
![Page 128: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/128.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 128/173
Controlling User Access
![Page 129: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/129.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 129/173
![Page 130: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/130.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 130/173
![Page 131: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/131.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 131/173
![Page 132: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/132.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 132/173
![Page 133: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/133.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 133/173
![Page 134: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/134.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 134/173
![Page 135: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/135.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 135/173
![Page 136: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/136.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 136/173
![Page 137: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/137.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 137/173
![Page 138: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/138.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 138/173
![Page 139: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/139.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 139/173
NOPRINTTo hide a column upto the current sessionSQL>COLUMN ENAME NOPRINT;
Note: The column Ename is hidden;
17-Oct-08Oracle9 i Datetime Functions
![Page 140: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/140.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 140/173
To view all zonesSQL>Select * From V$TIMEZONE_NAMES;
![Page 141: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/141.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 141/173
![Page 142: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/142.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 142/173
![Page 143: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/143.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 143/173
![Page 144: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/144.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 144/173
![Page 145: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/145.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 145/173
![Page 146: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/146.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 146/173
![Page 147: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/147.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 147/173
![Page 148: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/148.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 148/173
![Page 149: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/149.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 149/173
![Page 150: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/150.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 150/173
Note:If we use as like above , just eliminate the subtotals ofjobid wise.
![Page 151: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/151.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 151/173
![Page 152: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/152.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 152/173
![Page 153: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/153.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 153/173
![Page 154: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/154.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 154/173
![Page 155: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/155.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 155/173
![Page 156: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/156.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 156/173
![Page 157: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/157.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 157/173
![Page 158: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/158.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 158/173
Ex:SQL> Update cor_emp e set dname = (Select dname from dept
where e.deptno=deptno);
29 rows updated.
![Page 159: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/159.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 159/173
![Page 160: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/160.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 160/173
Hierarchical Retrieval
![Page 161: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/161.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 161/173
![Page 162: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/162.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 162/173
![Page 163: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/163.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 163/173
![Page 164: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/164.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 164/173
![Page 165: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/165.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 165/173
![Page 166: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/166.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 166/173
![Page 167: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/167.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 167/173
![Page 168: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/168.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 168/173
![Page 169: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/169.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 169/173
![Page 170: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/170.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 170/173
![Page 171: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/171.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 171/173
![Page 172: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/172.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 172/173
![Page 173: Easy SQL Practice](https://reader031.vdocuments.us/reader031/viewer/2022021123/577d21121a28ab4e1e946998/html5/thumbnails/173.jpg)
8/3/2019 Easy SQL Practice
http://slidepdf.com/reader/full/easy-sql-practice 173/173