PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Queries Using Joins

Using Oracle Pre Defined Functions

Lower Function:


  SELECT 'ORACLE' String, 
		 LOWER('ORACLE') Lower
	FROM DUAL; 			  

  SELECT LOWER(Ename) Ename, 
		 LOWER(Job) Job, 
		 Sal, 
		 HireDate 
	FROM Emp; 			  

SELECT LOWER(Ename)||' is Working As '||Job Employee
  FROM Emp 
 WHERE LOWER(Job) = 'manager'; 

UPPER Function:


SELECT 'oracle' String, 
	   UPPER('oracle') Upper
  FROM DUAL; 

SELECT Ename||' is Designated As '||LOWER(Job) Ename 
  FROM Emp 
 WHERE Job = UPPER('manager');

INITCAP Function:


SELECT 'oracle corporation' String, 
	   INITCAP('oracle corporation') InitCap
  FROM DUAl; 	

SELECT 'sample text for demonstration purpose' String, 
	   INITCAP('sample text for demonstration purpose') InitCap
  FROM DUAL   

	
SELECT 'sample1text@for4demonstration%purpose' String, 
	   INITCAP('sample1text@for4demonstration%purpose') InitCap 
  FROM DUAL; 

Concat Function:


	
 SELECT 'Oracle' Strimg1, 
		'Corporation' String2, 
		CONCAT('Oracle', 'Corporation') Concat 
   FROM DUAL;

				
SELECT CONCAT(CONCAT(INITCAP(Ename),' is a '), Job) Job
  FROM Emp 
 WHERE Deptno IN(10, 20); 

SUBSTR Function:

String, N'thPosition, howmanycharctersforward' --> Returns a Character


				
SELECT 'ABCDEFGHIJ' String, 
	   SUBSTR ('ABCDEFGHIJ', 0, 5) SUBSTR 
  FROM DUAL; 

	
SELECT 'ABCDEFGHIJ' String, 
	   SUBSTR ('ABCDEFGHIJ', 4) SUBSTR 
  FROM DUAL ;

	
SELECT 'ABCDEFGHIJ' String, 
SUBSTR ('ABCDEFGHIJ', -5, 4) SUBSTR   
  FROM DUAL;

	
SELECT 'ABCDEFGHIJ' String, 
SUBSTR ('ABCDEFGHIJ', 5.25, 4.65) SUBSTR 
  FROM DUAL; 

SELECT 'ABCDEFGHIJ' String, 
SUBSTR ('ABCDEFGHIJ', 5, -4) SUBSTR  
  FROM DUAL;

SELECT Ename, Job, Deptno 
  FROM Emp 
 WHERE SUBSTR (Job, 4, 3) = 'AGE';

Length Function:


SELECT 'ORACLE' String, 
LENGTH('ORACLE') Length
  FROM DUAL; 

SELECT INITCAP(Ename), 
	   Job 
  FROM Emp 
 WHERE SUBSTR (Job, 4, LENGTH (SUBSTR (Job, 4, 3))) = 'AGE';

INSTR Function:

String, StringToFind, SearchFromWhichPosition, Nth Occourence --> Returns a Number


SELECT 'STRING' Original, 
INSTR ('STRING' , 'R') InString
FROM DUAL; 

SELECT 
'STRING' Original, 
INSTR('STRING' , 'RIN', 2) InString
FROM DUAL;

SELECT 'CORPORATE FLOOR' Original, 
 INSTR('CORPORATE FLOOR', 'OR', 3, 1) InString 
FROM DUAL; 

SELECT Ename, 
Job Original, 
SUBSTR (Job,INSTR(Job, 'A', 1, 2), 3) SUBSTR ing 
FROM Emp 
WHERE SUBSTR (Job, INSTR(Job, 'A', 1, 2), 1) = 'A';

SELECT 'Sample_Text' OrgString, 
SUBSTR ('Sample_Text', 1, INSTR('Sample_Text', '_', 1, 1) - 1) Sample,
SUBSTR ('Sample_Text', INSTR ('Sample_Text', '_', 1, 1) + 1) Text 
FROM DUAL; 

Padding Functions LPAD, RPAD


SELECT 'Page 1' MyString, 
  LPAD('Page 1', 15, '*.') LPadded
FROM DUAL; 

SELECT Ename, 
  LPAD(Ename, 10, '-') LPad 
FROM Emp WHERE Sal >= 2500;

SELECT 'Page 1' String1, 
  RPAD('Page 1' , 15, '*.') RPad
FROM DUAL; 

SELECT Ename, 
LPAD(RPAD(Ename, 10, '-'), 15, '-') Center
FROM Emp; 

SELECT Ename, 
LPAD(RPAD(Ename, 10), 15) CenterJustified
FROM Emp; 

Trim Functions LTRIM, RTRIM, TRIM:


SELECT 'xyzXxyLAST WORD' OrgStr, 
 LTRIM('xyzXxyLAST WORD', 'xy')  Ltrim 
FROM DUAL; 

SELECT '         LAST WORD' OrgStr, 
 LTRIM('         LAST WORD')  Ltrim
FROM DUAL;

SELECT 'BROWNINGyxXxy' String, 
 RTRIM('BROWNINGyxXxy', 'xy') Rtrim
FROM DUAL;

SELECT Ename, 
	   Job, 
	   Sal 
  FROM Emp 
 WHERE LTRIM(Job, 'MAN') LIKE 'GER';

SELECT 'MITHSS' String, 
TRIM('S' FROM 'MITHSS') Trimmed
FROM DUAL; 

SELECT 'SSMITHSS' String, 
TRIM('S' FROM 'SSMITHSS') Trimmed
FROM DUAL;

SELECT 'SSMITHSS' String, 
TRIM(LEADING 'S' FROM 'SSMITHSS') Trimmed
FROM DUAL;

SELECT 'SSMITHSS' String, 
TRIM(TRAILING 'S' FROM 'SSMITHSS') Trimmed 
FROM DUAL;

SELECT 'SSMITHSS' String, 
TRIM(BOTH 'S' FROM 'SSMITHSS') Trimmed
FROM DUAL;

Replace Function:

 SELECT 'JACK AND JUE' OrgStr, 			
REPLACE('JACK AND JUE' , 'J', 'BL') Replace			
FROM DUAL;

SELECT 'JACK AND JUE' OrgStr, 
LENGTH('JACK AND JUE') - LENGTH(REPLACE('JACK AND JUE' , 'J')) "N J's Found"
FROM DUAL;

SELECT Job, 
REPLACE (Job, 'MAN', 'EXECUTIVE') Replace
FROM Emp 
WHERE Job = 'SALESMAN'; 

Translate Function:

SELECT   Ename, Job, 
TRANSLATE(Job, 'P', ' ') Translate
FROM Emp 
WHERE Job = 'PRESIDENT'; 

SELECT Job, 
TRANSLATE(Job, 'MN', 'DM') Translate
FROM Emp 
WHERE Job = 'MANAGER'; 

ASCII Functions:

SELECT 'Ascii Code '||CHR(67)||CHR(65)||CHR(84)||CHR(10) Sample
FROM DUAL; 

SELECT 
'Employee Number : '||Empno||CHR(10)|| 
'Employee Name   : '||Ename||CHR(10)|| 
'Employee Job    : '||Job||CHR(10)|| 
'Employee Salary : '||Sal||CHR(10)|| 
'Employee Comm   : '||Comm||CHR(10)|| 
'Employee HireDT : '||HireDate||CHR(10)|| 
'Employee Deptno : '||Deptno||CHR(10)|| 
'Employee MGR    : '||MGR||CHR(10) "Employees Report" 
FROM Emp 
WHERE Empno = 7839; 

SELECT ASCII('TYPEYOURNAME') 
  FROM DUAL;

SIGN Function:

SELECT Ename, Sal, Comm 
FROM Emp 
WHERE SIGN(Sal - Comm) = -1;  
SELECT 'The Current time on the Server is : '|| SYSDATE Today 
FROM DUAL; 
SELECT SYSDATE Today, SYSDATE + 5 "5 Days"
FROM DUAL; 
SELECT 
SYSDATE Today, 
SYSDATE + 450 "450 Days"
FROM DUAL;
SELECT Ename, SYSDATE Today, HireDate, SYSDATE - HireDate DateDiff 
FROM Emp; 
SELECT Ename, SYSDATE Today, HireDate, 
       TRUNC(SYSDATE - HireDate) DateDiff 
FROM Emp;
SELECT Ename, SYSDATE Today, HireDate,  
TRUNC(SYSDATE - HireDate) Days,  
TRUNC((SYSDATE - HireDate) / 365) Years 
FROM Emp 
WHERE TRUNC((SYSDATE - HireDate) / 365) > 28;
SELECT 
SYSDATE Today, 
SYSDATE + 90 "3 Months"
FROM DUAL; 
SELECT 
SYSDATE Today, 
SYSDATE - '01-MAY-10' Diff 
FROM DUAL; 
SELECT 
SYSDATE Today, 
SYSDATE + 90 "3 Months" 
FROM DUAL; 

ADD MONTHS Function:

SELECT 
SYSDATE ToDay, 
ADD_MONTHS(SYSDATE, 3) "3 Months"
ADD_MONTHS(SYSDATE, -3) "-3 Months"
FROM DUAL; 

MONTHS BETWEEN Function:

SELECT Ename, Sal, SYSDATE Today, HireDate, 
MONTHS_BETWEEN(SYSDATE, HireDate) "Months" 
FROM Emp; 
SELECT Ename, Sal, SYSDATE Today, HireDate, 
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months", 
TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) "Years" 
FROM Emp 
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) > 28

NEXT DAY Function:

SELECT 
SYSDATE Today, 
NEXT_DAY(SYSDATE, 'FRI') "Friday",
NEXT_DAY(SYSDATE, 6) "Friday" ,
NEXT_DAY(SYSDATE, 'FRIDAY') "Friday" ,
NEXT_DAY(SYSDATE, 'WED') "Wednesday" 
FROM DUAL; 

LAST DAY Function:

SELECT 
SYSDATE Today, 
LAST_DAY(SYSDATE) LastDay 
FROM DUAL; 

ROUND and TRUNC Functions:

SELECT 
SYSDATE Today, 
ROUND(SYSDATE) Round, 
TRUNC(SYSDATE) Trunc 
FROM DUAL;
SELECT 
SYSDATE Today, 
ROUND(SYSDATE, 'DAY') Round,
TRUNC(SYSDATE, 'DAY') Trunc 
FROM DUAL;
SELECT 
SYSDATE Today, 
ROUND(SYSDATE, 'MONTH') Round,
TRUNC(SYSDATE, 'MONTH') Trunc 
FROM DUAL;
SELECT 
SYSDATE Today, 
ROUND(SYSDATE, 'YEAR') Round,
TRUNC(SYSDATE, 'YEAR') Trunc 
FROM DUAL;

TO CHAR Function:

SELECT Ename, Sal, Comm,  
TO_CHAR(Sal - Comm, '9999MI') Diff
FROM Emp 
WHERE Comm IS NOT NULL 
SELECT Ename, Sal, Comm,  
TO_CHAR(Sal - Comm, '9999PR') Diff
FROM Emp 
WHERE Comm IS NOT NULL 
SELECT Ename, Sal, Comm,  
TO_CHAR(Sal - Comm, '9999PT') Diff 
FROM Emp 
WHERE Comm IS NOT NULL 
SELECT TO_CHAR(5, 'RN') CAPS_ROMAN_NUMBER,
       LTRIM(TO_CHAR(5, 'rn')) SMALL_ROMAN_NUMBER, 
  FROM DUAL;
SELECT Ename, Sal, Comm, TO_CHAR(Sal - Comm, '9G999D99S') Diff 
FROM Emp 
WHERE Comm IS NOT NULL; 
SELECT Ename, Sal, Comm, TO_CHAR(Sal - Comm, 'S9G999D99') Diff 
FROM Emp 
WHERE Comm IS NOT NULL;
SELECT TO_CHAR(12, 'X') "Dece to Hexa"
  FROM DUAL;
SELECT Ename,  
TO_CHAR(Sal, '0999') Sal, 
TO_CHAR(Comm, '0999') Comm
FROM Emp;
SELECT Ename,  
TO_CHAR(Sal, '0G999D99') Sal, 
TO_CHAR(Comm, '0G999D99') Comm,
TO_CHAR(Sal, '0G999D99') Salary, 
TO_CHAR(NVL(Comm, 0), '0G999D99') Commision, 
TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotalSalary
FROM Emp;

SYSDATE using different Parameters:

SELECT Ename, Sal, 
       HireDate||TO_CHAR(HireDate, ' B.C.') HireDate,
	   TO_CHAR(SYSDATE, 'P.M.') Meridian,
	   TO_CHAR(SYSDATE, ' B.C.') 	   
FROM Emp; 
SELECT SYSDATE||' '||TO_CHAR(SYSDATE, 'SCC, B.C.') Calendar ,
       HireDate||' '||TO_CHAR(HireDate, 'CC, B.C.') HireDate,
	   TO_CHAR(SYSDATE, 'D') WeekDay ,
	   TO_CHAR(HireDate, 'D') WeekDay,
	   TO_CHAR(HireDate, 'D') WeekDay ,
	   ENAME
  FROM EMP;
SELECT SYSDATE||TO_CHAR(SYSDATE, ' Day') Today
  FROM DUAL;
SELECT 
Ename||' You Are Recruited on '||HireDate|| 
TO_CHAR(HireDate, ' Day') Hiredate 
FROM Emp 
WHERE RTRIM(TO_CHAR(HireDate, 'Day')) = 'Friday';
SELECT TO_CHAR(SYSDATE, 'DD') Day,
       TO_CHAR(SYSDATE, 'DDD') Yearday,
	   TO_CHAR(SYSDATE, 'DY') Today,
	   TO_CHAR(SYSDATE, 'IW') YearWeek ,
	   TO_CHAR(SYSDATE, 'IYYY, B.C.') ThisYear,
	   TO_CHAR(SYSDATE, 'IYYY, B.C. "Century : "CC'),
	   TO_CHAR(SYSDATE, 'YYYY "=>" Year, B.C.') Year,
	   TO_CHAR(SYSDATE, 'W') MonthWeek,
	   TO_CHAR(SYSDATE, 'Q') Quater,
	   TO_CHAR(SYSDATE, 'MM') Month,
	   TO_CHAR(SYSDATE, 'DD-MM-YYYY') Date
  FROM DUAL;
SELECT 
'The Number of Days Completed '||CHR(10)|| 
'Since January 1ST 4712 B.C To '||CHR(10)|| 
'July 12TH 2010 A.D. Are : '||(TO_CHAR(SYSDATE,  'J') - 1) "Julian Days" 
FROm DUAL;
SELECT 
'The Different Days Are : '|| 
TO_CHAR(SYSDATE, 'J, DDD, DD, D') Days
FROM DUAL; 
SELECT Ename, TO_CHAR(HireDate, 'Day, DD-MM-YYYY B.C.') HireDate,  Sal
FROM Emp 
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND 
TO_CHAR(HireDate, 'MM') = 9;
SELECT Ename,  
TO_CHAR(HireDate, 'Day, DD-MM, Year B.C.') HireDate
FROM Emp 
WHERE TO_CHAR(HireDate, 'YYYY') = 1981 AND 	   
TO_CHAR(HireDate, 'MM') = 9;
SELECT Ename,  
TO_CHAR(HireDate, 'Day, DD, Month, Year B.C.') HireDate,
TO_CHAR(HireDate, 'Day, DD, MM, Year B.C.') HireDate 
FROM Emp;
SELECT 'The Current Hour in My Server is : '|| TO_CHAR(SYSDATE, 'HH, P.M.') Hour ,
       'The Current Time in My Server is : '|| TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') MyTime ,
	   'The Current Time in My Server is : '|| TO_CHAR(SYSDATE, 'HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyTimeDetail 
FROM DUAL;
SELECT 'The Current Calender : '|| 
		TO_CHAR(SYSDATE, 'Day, DD, Month, Year B.C.')|| 
		TO_CHAR(SYSDATE, ', HH" Hours" MI" Minutes" SS" Seconds" P.M.') MyCalender
FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD-rm-YYYY') Today,
       'The Seconds Since 12.00 A.M. : '|| TO_CHAR(SYSDATE, 'SSSSS') Seconds 
FROM DUAL;
SELECT 'The Current Date is : '|| TO_CHAR(SYSDATE, 'DDSP, Month Year B.C.') MyDate,
       'The Current Date is : '|| TO_CHAR(SYSDATE, 'DDSPTH, Month Year B.C.') MyDate1,
	   'The Current Date is : '|| TO_CHAR(SYSDATE, '"Day "DDSPTH "of" MMSPTH" Month" " Year "YYYYSP B.C.') MyDate2
FROM DUAL;

TO NUMBER Function:

SELECT 
'1,234.00' Num1, 
'2,345.00' Num2, 
'1,234.00' + '2,345.00' Result
FROM DUAL; 
SELECT 
	'1,234.00' Num1, 
	'2,345.00' Num2, 
	TO_CHAR(1234 + 2345, '9G999D99') Result
	TO_NUMBER('1,234.00', '9G999D99') +  TO_NUMBER('2,345.00', '9G999D99') Result1,
	TO_CHAR((TO_NUMBER('1,234.00', '9G999D99') +  TO_NUMBER('2,345.00', '9G999D99')), '9G999D99') Result2
FROM DUAL;
SELECT  
'12-JUL-10' Today, 
TO_DATE('12-JUL-10', 'DD-MON-YY') + 3 "3 Days",
TO_DATE('Monday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.') + 3 "3 Days1",
TO_CHAR((TO_DATE('Monday, July 12, 2010 A.D.', 'Day, Month DD, YYYY B.C.') + 3), 'Day, Month DD, YYYY B.C.') "3 Days2"
FROM DUAL;
SELECT 
Ename, 
HireDate, 
TO_CHAR(HireDate, 'MONTH DD, YYYY')
FROM Emp 
WHERE Ename = 'BLAKE';
SELECT 
Ename, 
HireDate, 
TO_CHAR(HireDate, 'FMMONTH DD, YYYY')
FROM Emp 
WHERE Ename = 'BLAKE';
SELECT '27-SEP-98' Date1, 
TO_CHAR(TO_DATE('27-SEP-98', 'DD-MON-YY'), 'DD-MON-RRRR') YEAR,
TO_CHAR(TO_DATE('27-SEP-98', 'DD-MON-RR'), 'DD-MON-RRRR') YEAR2
FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDDTH')||' of '||TO_CHAR( SYSDATE, 'Month')||' , '||TO_CHAR (SYSDATE, 'YYYY') DateFormat 
FROM DUAL; 
SELECT TO_CHAR(TO_DATE('1234', 'J'), 'JSP') "Spelled Number" 
FROM DUAL; 
SELECT 'ALL ORACLE APPS' OrgName, 
TRANSLATE('ALL ORACLE APPS', 
       'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
       '1234567890!@#$%^&*()-=_+;,.') EncryptedName
FROM DUAL; 
SELECT 
'1@@ %*13@5 1^^(' EncryptedName, 
TRANSLATE('1@@ %*13@5 1^^(', 
      '1234567890!@#$%^&*()-=_+;,.', 
      'ABCDEFGHIJKLMNOPQRSTUVWXYZ') DecryptedName
FROM DUAL;

Group Functions:

Average, Sum, Distinct, Maximum, Minium, Count

SELECT AVG(Sal) FROM Emp; 
SELECT TRUNC(AVG(Sal), 2) AvgSal FROM Emp;
SELECT AVG(Comm), AVG(Sal) FROM EMp;
SELECT AVG(Comm), AVG(NVL(Comm, 0)) FROM EMP;
SELECT SUM(Sal) FROM Emp; 
SELECT SUM(Sal), SUM(Comm) FROM Emp; 
SELECT SUM(Sal), AVG(Sal) FROM Emp;
SELECT MAX(Sal), MIN(Sal) FROM Emp;
SELECT MAX(Ename), MIN(Ename) FROM Emp;
SELECT MAX(HireDate), MIN(HireDate) FROM Emp;
SELECT MAX(Comm), MIN(Comm) FROM Emp; 
SELECT COUNT(*) FROM Emp; 
SELECT COUNT(Empno) FROM Emp;
SELECT COUNT(MGR) FROM Emp; 
SELECT COUNT(Comm) FROM Emp;

Group By; Group By with Having; Group By with Where, Having and Order By:

SELECT Deptno FROM Emp GROUP BY Deptno; 
SELECT DISTINCT Deptno FROM Emp; 
SELECT Job FROM Emp GROUP BY Job;
SELECT Deptno, SUM(Sal) FROM Emp GROUP BY Deptno;
SELECT Deptno, SUM(Sal)
  FROM Emp 
 WHERE Deptno <> 10 
 GROUP BY Deptno;
SELECT SUM(Sal) FROM Emp
 GROUP BY Deptno;
SELECT TO_CHAR(HireDate, 'YYYY') Year 
FROM Emp 
GROUP BY TO_CHAR(HireDate, 'YYYY');
SELECT TO_CHAR(HireDate, 'Month') Year
FROM Emp 
GROUP BY TO_CHAR(HireDate, 'Month');
SELECT TO_CHAR(HireDate, 'Month') Year 
FROM Emp 
GROUP BY TO_CHAR(HireDate, 'Month') 
ORDER BY TO_CHAR(HireDate, 'Month');
SELECT Deptno, Job 
FROM Emp 
GROUP BY Deptno, Job;

SELECT DISTINCT Deptno, Job FROM Emp;
SELECT Deptno, AVG(Sal) 
FROM Emp 
GROUP BY Deptno; 
SELECT 
Deptno, 
AVG(Sal) 
FROM Emp 
GROUP BY Deptno 
HAVING MAX(Sal) > 2900; 
SELECT 
Job, 
SUM(Sal) Payroll 
FROM Emp 
WHERE Job NOT LIKE 'SALES%'
GROUP BY Job 
HAVING SUM(Sal) > 5000 
ORDER BY SUM(Sal); 	
SELECT 
Deptno, 
MIN(Sal), 
MAX(Sal) 
FROM Emp 
WHERE Job = 'CLERK' 
GROUP BY Deptno 
HAVING MIN(Sal) < 1000;
SELECT 
Deptno, 
SUM(Sal) 
FROM Emp 
GROUP BY Deptno 
HAVING COUNT(Deptno) > 3;
SELECT 
	Deptno, 
	Job, 
	SUM(Sal), 
	AVG(Sal) 
FROM Emp 
GROUP BY Deptno, Job 
HAVING AVG(Sal) > 2500 AND SUM(Sal) < 6000 AND COUNT(*) > 2;
**
SELECT Deptno, MAX(AVG(Sal))
FROM Emp 
GROUP BY Deptno;

Greatest, Least Functions:

SELECT GREATEST('HARRY', 'HARRIOT'),
       LEAST('HARRY', 'HARRIOT') 
FROM DUAL; 
SELECT LEAST(1000, 2000, 200),
	    GREATEST(1000, 2000, 200)
FROM DUAL;
SELECT LEAST('10-JUL-05', '20-JUL-05'),
       GREATEST('10-JUL-05', '20-JUL-05')
FROM DUAL; 

User Functions:

SELECT USER, USERENV('ISDBA') UserEnv FROM DUAL;
SELECT USER, USERENV('LANGUAGE') UserEnv FROM DUAL; 
SELECT USER, USERENV('TERMINAL') UserEnv FROM DUAL;
SELECT USER, USERENV('SESSIONID') UserEnv FROM DUAL;
SELECT VSIZE('SIMPLE') MemorySize FROM DUAL;

VSIZE:

SELECT 
SUM(VSIZE(Empno)) Empno, 
SUM(VSIZE(Ename)) Ename, 
SUM(VSIZE(Deptno)) Deptno, 
SUM(VSIZE(Job)) Job, 
SUM(VSIZE(MGR)) MGR, 
SUM(VSIZE(Sal)) Sal, 
SUM(VSIZE(Comm)) Comm, 
SUM(VSIZE(HireDate)) HireDate,
SUM(VSIZE(Empno)) + 
SUM(VSIZE(Ename)) + 
SUM(VSIZE(Deptno)) + 
SUM(VSIZE(Job)) + 
SUM(VSIZE(MGR)) + 
SUM(VSIZE(Sal)) + 
SUM(VSIZE(Comm)) + 
SUM(VSIZE(HireDate))  Total 
FROM Emp;

Soundex Function:

SELECT Ename, Sal, Deptno, Job 
FROM Emp 
WHERE SOUNDEX(Job) = SOUNDEX('manger');
SELECT Ename, Sal, Deptno, Job 
  FROM Emp 
 WHERE SOUNDEX(Job) = SOUNDEX('clrk');

 
ENAME        SAL DEPTNO JOB                                                      
---------- ----- ------ ---------                                                
JAMES        950     30 CLERK                                                    
SMITH        800     20 CLERK                                                    
ADAMS       1100     20 CLERK                                                    
MILLER      1300     10 CLERK                                                    

 
SELECT Ename, Sal, Deptno, Job 
FROM Emp 
WHERE SOUNDEX(Ename) = SOUNDEX('smythe');
  
ENAME        SAL DEPTNO JOB                                                      
---------- ----- ------ ---------                                                
SMITH        800     20 CLERK                                                    
 

 SELECT Ename, Sal, Deptno, Job 
 FROM Emp 
 WHERE SOUNDEX(Ename) = SOUNDEX('Kng') 
 
ENAME        SAL DEPTNO JOB                                                      
---------- ----- ------ ---------                                                
KING        5000     10 PRESIDENT   
 
SELECT Ename, Sal, Deptno, Job, SOUNDEX('Kng'), SOUNDEX(Ename) 
FROM Emp 
WHERE SOUNDEX(Ename) = SOUNDEX('Kng') 
 
ENAME        SAL DEPTNO JOB       SOUN SOUN                                      
---------- ----- ------ --------- ---- ----                                      
KING        5000     10 PRESIDENT K520 K520    

Go Back