SQL ,PLSQL , Unix

Identify current and prev details of a customer

2021-08-05T05:57:00.002-07:00

create table customer_rating(

id char,

name char,

irrating char,

jcrrating char,

procDate date,

flag char

);

insert into customer_rating values('A','D','X','Y','28-Jul-21','M');

insert into customer_rating values('A','D','M','L','27-Jul-21','M');

Select cr.id,

cr.name,

cr_prev.irrating pcob_rating,

cr.irrating cob_rating,

cr_prev.jcrrating pcob_jcr_rating,

cr.jcrrating cob_jcr_rating

From Customer_Rating cr join

Customer_Rating cr_Prev ON

cr.procdate = '28-Jul-21' AND

cr_prev.id = cr.id AND

cr_prev.procdate = (

Select max(procdate) from Customer_Rating

Where id=cr.id and procdate < cr.procdate

) ;

Design and Operations ( Employee- Model )

2016-04-26T22:05:00.001-07:00

-- DB design
drop table emp_Salary;
drop table emp_Designation;
drop table emp;
drop table Designations;
drop table Departments;
drop table Salary_Bands;
drop table Addresses;

Create table emp(
        emp_id                  number,
        emp_Name                varchar2(20),
        emp_address_id          varchar2(10),
        emp_created_date        date,
        emp_created_by          varchar2(20),
        constraint e_pk primary key(emp_id)
);

Create table Departments(
        dept_id                 number,
        dept_name               Varchar2(20),
        constraints dept_pk primary key(dept_id)
);

Create table Designations(
        dept_id number references Departments(dept_id),
        desg_id number,
        desg_description varchar2(20),
        constraints desg_pk primary key(dept_id,desg_id)        
);

Create table emp_Designation(
        emp_id                  number references emp(emp_id),
        emp_des_dept_id         number references departments(dept_id),
        emp_desg_id             number
);

Create table Salary_Bands(
        band_id         number,
        band_amount     float(20),
        constraints sal_band_pk primary key(band_id)
);

Create table emp_Salary(
        emp_id                  number  references emp(emp_id),
        emp_sal_band_id         number  references Salary_Bands(band_id),
        emp_sal_percentage      number,
        constraints emp_sal_pk primary key(emp_id,emp_sal_band_id)
);

Create table Addresses(
        address_id number,
        address_zip varchar2(10),
        address_country varchar2(100),
        address_city varchar2(100),
        address_town varchar2(100),
        address_street varchar2(100),
        address_desc varchar2(100),
        constraint addr_pk primary key(address_id)                
);


--Populate master details that do not have dependency on EMP data 
--Addresses
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1001,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-749');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1002,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-750');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1003,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-751');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1004,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-752');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1005,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-753');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1006,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-754');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1007,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-755');

--Departments
insert into departments(dept_id,dept_name) values(100,'IT Solutions');
insert into departments(dept_id,dept_name) values(200,'Marketing');
insert into departments(dept_id,dept_name) values(300,'Customer Desk');
insert into departments(dept_id,dept_name) values(400,'Reception');
insert into departments(dept_id,dept_name) values(500,'Business Department');
insert into departments(dept_id,dept_name) values(1,'Office of CEO');

-- Designations
insert into Designations(dept_id,desg_id,desg_description) values(100,1,'Developer');
insert into Designations(dept_id,desg_id,desg_description) values(100,2,'Tester');
insert into Designations(dept_id,desg_id,desg_description) values(100,3,'Senior Developer');
insert into Designations(dept_id,desg_id,desg_description) values(100,4,'Technical Lead');
insert into Designations(dept_id,desg_id,desg_description) values(100,5,'Sr.Staff');
insert into Designations(dept_id,desg_id,desg_description) values(200,1,'Executive');
insert into Designations(dept_id,desg_id,desg_description) values(200,2,'Junior Executive');
insert into Designations(dept_id,desg_id,desg_description) values(300,1,'Desktop Engineer');
insert into Designations(dept_id,desg_id,desg_description) values(300,2,'Customer Person');
insert into Designations(dept_id,desg_id,desg_description) values(400,1,'Receptionist');
insert into Designations(dept_id,desg_id,desg_description) values(500,1,'Biz User');
insert into Designations(dept_id,desg_id,desg_description) values(500,2,'BA Architect');
insert into Designations(dept_id,desg_id,desg_description) values(1,1,'CEO');

--- Salary_Bands
insert into salary_bands(band_id,band_amount) values(1,100000);
insert into salary_bands(band_id,band_amount) values(2,200000);
insert into salary_bands(band_id,band_amount) values(3,300000);
insert into salary_bands(band_id,band_amount) values(4,400000);
insert into salary_bands(band_id,band_amount) values(5,500000);
insert into salary_bands(band_id,band_amount) values(6,600000);
insert into salary_bands(band_id,band_amount) values(7,700000);
insert into salary_bands(band_id,band_amount) values(8,800000);
insert into salary_bands(band_id,band_amount) values(9,900000);
insert into salary_bands(band_id,band_amount) values(10,1000000);
insert into salary_bands(band_id,band_amount) values(11,1100000);
insert into salary_bands(band_id,band_amount) values(12,1200000);
insert into salary_bands(band_id,band_amount) values(13,1300000);
insert into salary_bands(band_id,band_amount) values(14,1400000);
insert into salary_bands(band_id,band_amount) values(15,1500000);
insert into salary_bands(band_id,band_amount) values(16,1600000);
insert into salary_bands(band_id,band_amount) values(17,1700000);
insert into salary_bands(band_id,band_amount) values(18,1800000);
insert into salary_bands(band_id,band_amount) values(19,1900000);
insert into salary_bands(band_id,band_amount) values(20,2000000);
insert into salary_bands(band_id,band_amount) values(21,2100000);
insert into salary_bands(band_id,band_amount) values(22,2200000);
insert into salary_bands(band_id,band_amount) values(23,2300000);
insert into salary_bands(band_id,band_amount) values(24,2400000);
insert into salary_bands(band_id,band_amount) values(25,2500000);
insert into salary_bands(band_id,band_amount) values(26,2600000);
insert into salary_bands(band_id,band_amount) values(27,2700000);
insert into salary_bands(band_id,band_amount) values(28,2800000);
insert into salary_bands(band_id,band_amount) values(29,2900000);
insert into salary_bands(band_id,band_amount) values(30,3000000);
insert into salary_bands(band_id,band_amount) values(35,3500000);
insert into salary_bands(band_id,band_amount) values(40,4000000);
insert into salary_bands(band_id,band_amount) values(45,4500000);
insert into salary_bands(band_id,band_amount) values(50,5000000);

--Employee data 
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10001,'Name 1',1003,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10002,'Name 2',1001,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10003,'Name 3',1002,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10004,'Name 4',1004,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10005,'Name 5',1005,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10006,'Name 6',1006,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10007,'Name 7',1003,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10008,'Name 8',1004,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10009,'Name 9',1007,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10010,'Name 10',1005,sysdate,user);

--Emp department and Designation
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10001,100,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10002,100,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10003,200,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10004,200,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10005,300,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10006,300,2);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10007,100,2);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10008,100,2);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10009,500,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10010,500,2);

--Emp Salary
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10001,1,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10002,2,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10003,3,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10004,4,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10005,1,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10006,2,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10007,4,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10008,5,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10009,2,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10010,6,100);

commit;


--Get All Employees Details ( Department, Salary , Designation , Address ) order by Department
SELECT e.emp_id, e.emp_name, d.desg_description, sb.band_amount,
          a.address_country
       || ' '
       || a.address_city
       || ' '
       || a.address_town
       || ' '
       || a.address_street
       || ' '
       || a.address_desc address
  FROM emp e,
       emp_designation ed,
       emp_salary es,
       addresses a,
       designations d,
       salary_bands sb
 WHERE 1 = 1
   AND es.emp_sal_band_id = sb.band_id
   AND ed.emp_desg_id = d.desg_id
   AND ed.emp_des_dept_id = d.dept_id
   AND e.emp_address_id = a.address_id
   AND e.emp_id = es.emp_id
   AND e.emp_id = ed.emp_id
Order by 3;


--Find out employees who share the same address
Select  e1.emp_id,
        e1.emp_name,
        a.* 
from    emp e1,
        emp e2,
        addresses a
Where 1=1
and e1.EMP_ADDRESS_ID = a.ADDRESS_ID
and e1.EMP_ID != e2.EMP_ID
and e1.EMP_ADDRESS_ID = e2.EMP_ADDRESS_ID
order by e1.emp_address_id,e1.emp_id;

--Find out employees who get max salary in the same department 
SELECT all_details.dept_name, all_details.emp_id, all_details.emp_name,
       all_details.band_amount amount
  FROM (SELECT   ed.emp_des_dept_id dept_id, MAX (sb.band_amount) max_salary
            FROM emp e, emp_designation ed, emp_salary es, salary_bands sb
           WHERE emp_sal_band_id = band_id
             AND e.emp_id = es.emp_id
             AND e.emp_id = ed.emp_id
        GROUP BY ed.emp_des_dept_id) maxsalary,
       (SELECT e.emp_id, e.emp_name, ed.emp_des_dept_id, d.dept_name,
               sb.band_amount
          FROM emp e,
               emp_designation ed,
               emp_salary es,
               salary_bands sb,
               departments d
         WHERE d.dept_id = ed.emp_des_dept_id
           AND emp_sal_band_id = band_id
           AND e.emp_id = es.emp_id
           AND e.emp_id = ed.emp_id) all_details
 WHERE all_details.band_amount = maxsalary.max_salary
   AND all_details.emp_des_dept_id = maxsalary.dept_id;

Read Live Logs from SQL

2016-04-09T22:26:00.000-07:00

How to read live logs from SQL?

Consider that you want to read the following application log from SQL.
/var/log/appDaily.log

1. Create a directory
CREATE OR REPLACE DIRECTORY live_Log_Directory AS '/var/log';

2. Create external table
CREATE TABLE Live_APP_Log(
    log_text CHAR(250)
)
ORGANIZATION external(
    Type Oracle_Loader
    Default Directory live_Log_Directory
    records delimited by newline
    LOCATION('appDaily.log')
    Reject limit unlimited;

3. Create a view on Live_APP_Log
    Create or Replace view App_Log_Information
    AS
    Select * from Live_APP_Log;

4. Grant necessary permissions to users
5. Now Query Live_APP_Log whenever you want to fetch log information from SQL

SQL - Binary Search Tree

2015-11-04T03:02:00.000-08:00

Problem Statement

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in BST, and P is the parent of N.

Write a query to find the node type of BST ordered by the value of the node. Output one of the following for each node:

· Root: If node is root node.

· Leaf: If node is leaf node.

· Inner: If node is neither root nor leaf node.

Sample Input

	N P
1	2
3	2
6	8
9	8
2	5
8	5
5	null

Sample Output

1 Leaf

2 Inner

3 Leaf

5 Root

6 Leaf

8 Inner

9 Leaf

Explanation

The BST below illustrates the sample:

Query

select N,

case

when P is null then 'Root'

else

decode((Select count(1)

from BST

Where N!=b.N

connect by prior N=P

start with N=b.N),0,'Leaf','Inner')

end "Leaf V"

from BST b order by 1;

Your Output (stdout)

1 Leaf 
2 Inner 
3 Leaf 
4 Inner 
5 Leaf 
6 Inner 
7 Leaf 
8 Leaf 
9 Inner 
10 Leaf 
11 Inner 
12 Leaf 
13 Inner 
14 Leaf 
15 Root

Asian Population

2015-10-17T05:24:00.002-07:00

Problem Statement

Given two tables, City and Country whose descriptions are given below. Find the sum of population of all the Cities that lies in "Asia" continent.

City

+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |
+-------------+----------+

Country

+----------------+-------------+
| Field          | Type        |
+----------------+-------------+
| Code           | char(3)     |
| Name           | char(52)    |
| Continent      | char(50)    |
| Region         | char(26)    |
| SurfaceArea    | float(10,2) |
| IndepYear      | smallint(6) |
| Population     | int(11)     |
| LifeExpectancy | float(3,1)  |
| GNP            | float(10,2) |
| GNPOld         | float(10,2) |
| LocalName      | char(45)    |
| GovernmentForm | char(45)    |
| HeadOfState    | char(60)    |
| Capital        | int(11)     |
| Code2          | char(2)     |
+----------------+-------------+

PS: City.CountryCode and Country.Code is same key.

Select "Population"

from (Select country.continent "City",sum(city.population) "Population"

from city,country

where countrycode=code

group by country.continent)

Where "City" = 'Asia';

Minimum / Maximum from the given list

2015-09-28T19:59:00.001-07:00

File: Execute.java

--------------------------------------------------------------------------------------

package org.developersbrain.Solutions;

public class Execute {

public static int min(int[] arrList){

int minV1=0;

int minV2=0;

int aLen=arrList.length;

minV1=arrList[0];

minV2=arrList[aLen-1];

for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){

if(minV1 > arrList[i]){

minV1=arrList[i];

}

if(minV2 > arrList[j]){

minV2=arrList[j];

}

if(minV2<=minV1){

return minV2;

}else{

return minV1;

}

public static int max(int[] arrList){

int minV1=0;

int minV2=0;

int aLen=arrList.length;

minV1=arrList[0];

minV2=arrList[aLen-1];

for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){

if(minV1 < arrList[i]){

minV1=arrList[i];

}

if(minV2 < arrList[j]){

minV2=arrList[j];

}

if(minV2>=minV1){

return minV2;

}else{

return minV1;

}

public static void main(String[] args) {

int[] a={15,16,1,2,3,9,16,13,22,200,34,56,3,0,999,89,-1,-2,-99};

for(int l:a) System.out.print(l+" ");

System.out.println();

System.out.println("Minimum:"+min(a));

System.out.println("Maximum:"+max(a));

}

Output:

-------------------------

15 16 1 2 3 9 16 13 22 200 34 56 3 0 999 89 -1 -2 -99

Minimum:-99

Maximum:999

Simple SQLPLUS formatting

2015-05-12T20:03:00.001-07:00

> SQL Prompt>SET SQLPROMPT "My SQL Prompt>"
> My SQL Prompt>SET COLSEP "|"
> My SQL Prompt>SET UNDERLINE "-"
> My SQL Prompt>SET LINESIZE 2000
> My SQL Prompt>SET PAGESIZE 1000
> My SQL Prompt>Select tbl2.*
> 2 From (Select *
> 3 From (Select 1 a,
> 4 2 b,
> 5 3 c,
> 6 4 d,
> 7 5 e
> 8 From DUAL) tbl1) tbl2
> 9 Where a=b-1;
>
> A| B| C| D| E
> ---------|---------|---------|---------|---------
> 1| 2| 3| 4| 5
>
> 1 row selected.
>

DUAL Table

2015-05-11T06:02:00.002-07:00

The DUAL table was created by Charles Weiss of Oracle Corporation to provide a table for joining in internal views:

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, apt for the process of creating a pair of rows from just one.^[1]

The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.

Optimization

Beginning with 10g Release 1 Oracle Database have beenOPTIMIZED^[2] and the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists.

DUAL is readily available for all the users in database.

Courtesy: Wikipedia

10 Million records manipulation in Oracle DB

2015-04-17T07:55:00.001-07:00

Say you are given a task to manipulate a set of 10 million records from a couple of tables.
What would you do? Sit and think???!!!!

See if the following helps you..

Replicate the tables data into temporary tables
Create indexes on the columns you want to manipulate the data
Write your query..

Dont ask me what language you are talking!!!!
Look at the examples

Select count(1) from hugeTable1;--7999900 records
Select count(1) from hugeTable2; --9893990

Create table tmp1
as
Select *
from hugeTable1
where 1=2;

insert /*+append*/ into tmp1
select * from hugeTable1;

Create table tmp2
as
Select *
from hugeTable2
Where 1=2;

insert /*+append*/ into tmp2
select * from hugeTable2;

create index indTmp1 on tmp1(column1,column2);
create index indTmp2 on tmp2(column1,column2);

Select *
from tmp1,tmp2
where tmp1.column1=tmp2.column1 and
tmp1.column2=tmp2.column2;

Should you face any issues, please write to me ( susilaarya@gmail.com )

command to list files and its records count

2015-04-16T06:42:00.001-07:00

wc - command is used to list all files in a directory with its records count

Prompt>$ wc -l *.txt

10 file1.txt

93 sampleFile.txt

199333 billingData.txt

65 loadData.txt

Prompt>$

database Search possible on Oracle?

2015-04-16T06:36:00.001-07:00

This was asked in a meeting of following people and the answers are amazing...

An Analyst replied
- I need a month time to prepare analysis whether it is possible or the way possible

Technical architect replied
-It is not impossible but involves lots of work.

Team Lead interrupted
- Google it , you should be able to find it

One in the team suggested
- It will cause performance issues

Developer maintained a silence until everyone calmed down and said..
- Lets make use of data dictionaries
- Find out Varchar fields and construct a query
- Lets list objects that has the data searched
- We will then narrow down our search
- It is not time-consuming job yet not a quick one

Analyst quickly replied - We know that, but it is not right to do as it will cause unnecessary issues
Team Lead - Were you not supposed to complete the other assignment? You concentrate on that!!!
Technical Architect - Do you know the system issues?
Developer - ??!!!?????!!!!!!!!

unusual LINUX commands

2015-04-07T07:39:00.003-07:00

1. file
- Will list the file encoding details
- Read man page for more details

2. pdftohtml
- Utility to convert pdf file to html format

3. iconv -f -t
- to change the file encoding from one format to another
Ex:
iconv -f UTF-16 -t ASCII filename.txt

- Read man page for more supported encoding formats

4. ls -R | grep -i **
- List files in subdirectories

5. last
- To see the list of users who were connected to the system
- It gives you the list with the details logon time, logon ip, user id etc.,

Interview point of View - Technical - PLSQL

2013-08-29T07:19:00.000-07:00

1. Write an SQL statement to delete all duplicate rows in a table.
Give all possible SQL statements that can be used to delete duplicate rows effectively.

2. Consider the following table

ID Name Role ManagerId
MGR6002 RajeshKumar Manager MGR1001
TM1001 Sridevi Associate MGR6002
TM1002 John Sr.Associate MGR6002
TM1008 Rexan Associate MGR6002
MGR6003 Amruta Manager MGR1001
MGR6004 Justin Manager MGR1001
MGR1001 Ravikumar GPM VCH1001
TM1003 Linton Associate MGR6004
TM1003 Kavitha Sr.Associate MGR6004
TM1005 Sailu Analyst MGR6002
TM1006 Sami Sr.Associate MGR6001

Write an SQL to print employee list hierarchically
Ex output.
Rajeshkumar
John
Rexan
Sailu
Amruta
Justin
Linton
Kavitha

3. Write the SQL statement to obtain nth salary of employees.
4. What are triggers? Breifly explain trigger types.
5. Do you know about RBO, CBO? What is your understanding on them?
6. Explain FILE handling in PlSQL. Tell me the commonly used methods for file operations.
> You may be asked to write a program to compare two files using file handling.
7. What are indexes? Explain its types.
8. What is your understanding on Oracle memory structure?
9. What are cursors? Explain its types with examples.
10. Differentiate Views, Inline views , materailized views.
11. Differentiate procedure, function, package.
12. What is the output of the following?

a.create table tabletmp(msg varchar(100));
b.insert into tabletmp values('Message 1');
c.insert into tabletmp values('Message 1');
d.insert into tabletmp values('Message 1');
e.insert into tabletmp values('Message 1');
f.insert into tabletmp values('Message 1');
g.create table tabletmp(msg varchar(100));
h.rollback;
i.Select * from tabletmp;

The execution sequence is : a,b,c,d,e,f,g,h,i
What is the output at 'g'?
What is the output at 'i'?

13. What is the maximum number of columns allowed in a table?
14. Each record has a unique key in oracle, even if the table structure has no primary.What is that?
15. Distinguish primary key, indexes.

USER related commands in UNIX

2013-07-03T04:47:00.001-07:00

whoami

The command lets you know the user name of the current shell you are at.

Example:

root@Microknoppix:/home/knoppix# whoami

root

root@Microknoppix:/home/knoppix# su knoppix

knoppix@Microknoppix:~$ whoami

knoppix

The su command is used to become another user during a login session. Invoked without a username, su defaults to becoming the superuser. The optional argument - may be used to provide an environment similar to what the user would expect had the user logged in directly.

Example :

root@Microknoppix:/home/knoppix# su knoppix

password:

knoppix@Microknoppix:~$

knoppix@Microknoppix:/root$ su -

password:

root@Microknoppix:~#

knoppix@Microknoppix:/root$ su

password:

root@Microknoppix:~#

finger

The finger displays information about the system users.

Example:

root@Microknoppix:~# finger knoppix

Directory: /home/knoppix Shell: /bin/bash

On since Sun Jun 30 01:43 (UTC) on console from :0

2 hours 8 minutes idle

(messages off)

On since Sun Jun 30 01:43 (UTC) on :0 (messages off)

On since Sun Jun 30 03:31 (UTC) on pts/0 from :0

No mail.

No Plan.

root@Microknoppix:~# finger root

Directory: /root Shell: /bin/bash

Never logged in.

No mail.

No Plan.

who

Print information about users who are currently logged in.

who -a, who --all

knoppix@Microknoppix:/root$ who -a

2013-06-30 01:43 1858 id=si term=0 exit=0

system boot 2013-06-30 01:43

run-level 5 2013-06-30 01:43 last=S

2013-06-30 01:43 2482 id=1

2013-06-30 01:43 2486 id=x1

2013-06-30 01:43 2485 id=4

2013-06-30 01:43 2484 id=3

2013-06-30 01:43 2483 id=2

knoppix - console 2013-06-30 01:43 old 2486 (:0)

knoppix ? :0 2013-06-30 01:43 ? 2486

knoppix + pts/0 2013-06-30 03:31 . 12227 (:0)

who -d, who --dead

To print dead processes

knoppix@Microknoppix:/root$ who -d

2013-06-30 01:43 1858 id=si term=0 exit=0

who -p, who --process

To print active processes spawned by init.

knoppix@Microknoppix:/root$ who -p

2013-06-30 01:43 2482 id=1

2013-06-30 01:43 2486 id=x1

2013-06-30 01:43 2485 id=4

2013-06-30 01:43 2484 id=3

2013-06-30 01:43 2483 id=2

who -q, who--count

To list all login names and number of users logged on

knoppix@Microknoppix:/root$ who -q

knoppix susilaarya susil

# users=3

Reader's message

2013-06-27T05:14:00.001-07:00

Dear readers,

Apologies! I have been away so long since my last post.

Here you go!

I will try to continue my blogging at least frequently if not everyday.

Thank you! Enjoy learning!!!

4 Qs 4 Answers

2012-07-29T08:49:00.000-07:00

1. How can I get recent 10 records out of 100 records?
SELECT *
FROM (Select *
from employee
order by rowid desc)
WHERE ROWNUM < 11

2.When would you use inline view in sub query?

You may use inline view when you want to cut short multi-join of tables.

3. How to select the recently updated records from the table?

There is no provision you can use to find out the records from the table. You may use auditing tables with the help of Triggers.

4. Display the number value in words?

Use the date function to achieve the results.

select to_char(to_date(999999,'j'),'jsp') from dual;

But it is better you write your own translate function because it doesnt support beyond 999999.

Higher range might work in 64-bit OS.

Objects becoming INVALID !!!!

2012-04-18T09:40:00.000-07:00

Consider you are working on a front-end change (.net packages or Java libraries) and you are compiling the objects. Say, the code you newly introduced makes call to DB packages which have not been touched.

When rebuilding the front-end changes, you will see all the Called-packages becoming INVALID.

If this is the case, you need to check the REMOTE_DEPENDENCIES_MODE parameter's value.

If it is TIMESTAMP, then change it to SIGNATURE to avoid such issues.

You can do this in the following way if you have permission for Alter System .

ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = SIGNATURE;

Happy learning!

PLSQL Programming Part 5 - CURSOR AND ITS ATTRIBUTES

2012-02-20T08:02:00.000-08:00

/**
What is a cursor?
A cursor is a pointer that points a set of records for processing. Private SQL area is where cursor results are placed.
Implicit cursors and Explicit cursors are two types of cursors.

There are a number of ways a cursor can be declared and used based on the requirement. They are as follows:
1. Simple FOR loop cursor
2. Explicit declaration and invoking it

***/
clear screen
set serveroutput on size 100000
declare
--Cursor Declaration
cursor cur_Customer_Data
is
Select *
from cust_table;

cur_Variable cur_Customer_Data%rowtype; -- Cursor variable to hold recordset
var1 cust_table%rowtype; -- variable to hold data on FOR loop cursor
begin

dbms_output.put_line('-------------------------------------------------------------------');
dbms_output.put_line('FOR loop started at => '||to_char(sysdate,'hh:mi:ss'));
dbms_output.put_line('-------------------------------------------------------------------');
for var1 in (Select * from cust_table)
loop
dbms_output.put_line(var1.cust_id||','||var1.cust_name);
end loop;
dbms_output.put_line('-------------------------------------------------------------------');
dbms_output.put_line('FOR loop ended at => '||to_char(sysdate,'hh:mi:ss'));
dbms_output.put_line('-------------------------------------------------------------------');

dbms_output.put_line('-------------------------------------------------------------------');
dbms_output.put_line('Fetch loop started at => '||to_char(sysdate,'hh:mi:ss'));
dbms_output.put_line('-------------------------------------------------------------------');
open cur_Customer_Data;
loop
fetch cur_Customer_Data into cur_Variable;
exit when cur_Customer_Data%NOTFOUND;
dbms_output.put_line(cur_Variable.cust_id||','||cur_Variable.cust_name);
end loop;
dbms_output.put_line('-------------------------------------------------------------------');
dbms_output.put_line('Fetch loop ended at => '||to_char(sysdate,'hh:mi:ss'));
dbms_output.put_line('-------------------------------------------------------------------');

if (cur_Customer_Data%isopen)
then
dbms_output.put_line('Cursor is still open and now I am closing it!');
close cur_Customer_Data;
else
dbms_output.put_line('Cursor already closed');
end if;

end;
/

PLSQL Programming part 4 - SQL joins

2012-02-16T08:39:00.001-08:00

/**
Do the following exercises for learning SQL joins
Pre-requisites: You must have CREATE privilege and some space allocated for you
**/

create table temp1
(
cust_id number,
cust_name varchar2(100),
addr1 varchar2(40),
addr2 varchar2(40),
addr3 varchar2(40),
addr4 varchar2(40),
pincode varchar2(40),
constraint cust_pk primary key ( cust_id )
);
/

create table service_tbl
(
cust_id number,
service_no number,
start_date date,
end_date date,
plan_type varchar2(10),
foreign key (cust_id) references temp1(cust_id)
);
/

alter table temp1 rename to cust_table;

Select * from cust_table;

insert into cust_table
values(100,'CUSTOMER 1','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(200,'CUSTOMER 2','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(300,'CUSTOMER 3','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert Into cust_table
values(400,'CUSTOMER 4','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(500,'CUSTOMER 5','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(600,'CUSTOMER 6','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(700,'CUSTOMER 6','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);

commit;

Select *
from service_tbl;

insert into service_tbl
values(100,1032427,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032428,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032429,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032430,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032431,sysdate,null,'PrePaid');
insert into service_tbl
values(100,1032432,sysdate,null,'PrePaid');
insert into service_tbl
values(100,1032433,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(400,4032427,sysdate,null,'Post Paid');
insert into service_tbl
values(500,5032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');

commit;

/***
CARTESIAN PRODUCT
***/

Select *
from cust_table,service_tbl;

--It returns 140 rows

select count(*) from cust_table; -- Returns 7
select count(*) from service_tbl; --Returns 20 rows

/***
SELF JOIN
***/

Select *
from cust_table A,CUST_TABLE B;

--It returns 49 rows -- 7x7 ROWS

/***
EQUI JOINS
***/

Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID = SERVICE_TBL.CUST_ID;

--It returns 20 rows
--Returns only the records those satisy the condition
--Driving table or the leading table is duplicated for the no of records fetched in the joining table

/***
OUTER JOIN - LEFT

***/
--Modern syntax
Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID = SERVICE_TBL.CUST_ID(+);

--Old syntax
Select *
from cust_table left outer join SERVICE_TBL
on cust_table.CUST_ID = SERVICE_TBL.CUST_ID;
--It returns 21 rows
--Returns the records those satisfy the condition in serivce_tbl and those dont in cust_table
--For the cust_id 700 in cust_table, there are no child records in service_tbl
--So , in left outer join, it finds no matching records and adds a null set

/***
OUTER JOIN - RIGHT
***/

--Modern syntax
Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID(+) = SERVICE_TBL.CUST_ID;

--Old syntax
Select *
from cust_table right outer join SERVICE_TBL
on cust_table.CUST_ID = SERVICE_TBL.CUST_ID;
--It returns 20 rows
--Returns the records those satisy the condition in cust_table and those dont in service_tbl

PLSQL programming ( Learn by examples - Part 3 )

2012-02-14T09:29:00.000-08:00

/**
In this section , lets learn the following.
How to use a for loop to process multiple records...
**/
set serveroutput on size 100000
begin
--Select objects of table type that are valid under the schema Susil
for var in (Select * from all_objects where object_name not like '%$%' and object_name in ('EMPLOYEE','EMP') and status='VALID' and owner='SUSIL')
loop
if var.object_type='TABLE'
then
/*
Dbms_metadata package provides us a function get_ddl to get the data definition on object.
For your understanding , i have just chosen two tables in this example
If you remove Object_name condition in the above SQL , it will print DDL definitions for all tables under the specified schema
**/
dbms_output.put_line('=====================================================================');
dbms_output.put_line('Table name =>'||var.object_name);
dbms_output.put_line('=====================================================================');
dbms_output.put_line(' Table definition is below ');
dbms_output.put_line('=====================================================================');
dbms_output.put_line(dbms_metadata.get_ddl(var.object_type,var.object_name,var.owner));
dbms_output.put_line('=====================================================================');
end if;
end loop;

end;
/

Output:

=====================================================================
Table name =>EMP
=====================================================================
Table definition is below
=====================================================================

CREATE TABLE "SUSIL"."EMP"
( "ENAME" VARCHAR2(10),
"SAL" NUMBER(7,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS"

=====================================================================
=====================================================================
Table name =>EMPLOYEE
=====================================================================
Table definition is below
=====================================================================
CREATE TABLE "SUSIL"."EMPLOYEE"
( "EMP_ID" VARCHAR2(6),
"EMP_NAME" VARCHAR2(40),
"DEPT_NO" NUMBER,
CONSTRAINT "E_PK" PRIMARY KEY ("EMP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_TS"

=====================================================================

PLSQL programming ( Learn by examples - Part 2 )

2012-02-08T07:28:00.000-08:00

/***
Learn by examples:
Write a PLSQL block to use Select , insert , delete , update statements in it

Purpose : Use of DML statements in Oracle PLSQL
SQL is the cursor placed in memory during DML operation in PLSQL block.
rowcount is SQL's attribute to know how many rows affected of that particular DML query.....

***/
clear screen
set serveroutput on
DECLARE
ENAME VARCHAR2(20);
BEGIN
SELECT EMP_NAME INTO ENAME FROM EMPLOYEE WHERE EMP_ID='E1'; --Syntax for Select statement in PLSQL is different. It has INTO clause to save values in variable
DBMS_OUTPUT.PUT_LINE('EMP NAME '||ENAME);
DBMS_OUTPUT.PUT_LINE('No of rows selected out of SELECT statement '||SQL%rowcount);

delete from employee
where dept_no >25;

DBMS_OUTPUT.PUT_LINE('No of rows deleted out of DELETE statement '||SQL%rowcount);

update employee
set dept_no = 10
where dept_no <=5;

DBMS_OUTPUT.PUT_LINE('No of rows updated out of UPDATE statement '||SQL%rowcount);

insert into employee values('E1141','XXXXX',6);

DBMS_OUTPUT.PUT_LINE('No of rows inserted out of INSERT statement '||SQL%rowcount);

commit;
END;

Output:

EMP NAME Susil Kumar1
No of rows selected out of SELECT statement 1
No of rows deleted out of DELETE statement 0
No of rows updated out of UPDATE statement 0
No of rows inserted out of INSERT statement 1

PLSQL programming - Part 1

2012-02-06T07:01:00.001-08:00

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language

Browse my blog to read the overview of PLSQL if you need it.

Anonymous block

.............
........... This is where you declare Variables that you are going to use it in PLSQL block
............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number.
Begins .....
.........
.........
.......... This is where you implement programming logic and get the desired outcome.
..........
..........
Ends.....

We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects)

Learn from examples

Example 1:

declare
var1_num number; --Declaring number type variable
var2_str varchar2;
begin
var1_num := 10; --Assigning 'Number' Value to the variable
var2_str := 'I am a string value';
end;

Example 2: Write anonymous block code in oracle to print your name, age, profession. Assign your DOB as Date type and find your age.

First, write down the logic :
1. Assign name to Char variable
2. Assign DOB to date variable
3. Find age
4 . Print name, age , profession

SET serveroutput ON
DECLARE
name VARCHAR2(40); --To assign name
profession VARCHAR2(40); --To assign profession
dob DATE; --To assign Date of birth as date
salary FLOAT; --To assign salary
age NUMBER; --to find age and assign it to number variable
BEGIN --Begin implementation part
--Implemenation section
name := 'Susil Kumar';
profession :='Software Professional';
dob := '13/mar/1986';
age := sysdate - dob;
salary := 10000.00;
--Printing the details
--To print , we make use of the Oracle supplied package DBMS_OUTPUT
dbms_output.put_line('My name is '||name||'.
I am a '||profession||'. I was born on '||dob||' and i am '||ROUND(age/365,0) ||' now.'||'My profession earns me '||salary||'.');
END;
/

Exercise:
1. Write PLSQL anonymous block to print the following message.
Today is _____(Today's date). The day is ____(Day).

What is PLSQL ?

2012-02-06T06:28:00.000-08:00

PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure. The PL/SQL compiler and interpreter are embedded in Oracle Database, providing developers with a consistent and leveraged development model on both the client and the server side. In addition, PL/SQL stored subprograms can be called from Oracle clients. PL/SQL enables you to mix SQL statements with procedural constructs.
With PL/SQL, you can create and run PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks, stored subprograms, and packages.

The basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program can be nested inside one another.

You can place declarations close to where they are used, such as inside a large subprogram. The declarations are local to the block and cease to exist when the block completes, helping to avoid cluttered namespaces for variables and procedures.

You can nest blocks in the executable and exception-handling parts of a PL/SQL block or subprogram, but not in the declarative part. You can define local subprograms in the declarative part of any block. You can call local subprograms only from the block in which they are defined.

Anonymous block
An anonymous block is a PL/SQL block that appears in your application and is not named or stored in the database. In many applications, PL/SQL blocks can appear wherever SQL statements can appear. A PL/SQL block groups related declarations and statements.

Stored or standalone subprogram
A stored or standalone subprogram is a PL/SQL block that Oracle stores in the database and can be called by name from an application. Subprograms can be procedures or functions; the difference is that functions return a value when executed. When you create a stored subprogram, Oracle parses the subprogram and stores its parsed representation in the database. See Subprograms and Packages: Usage Information.

Package
A package is a group of subprograms and variable definitions that Oracle stores in the database. Subprograms and variables in packages can be called from other packages or subprograms.

--Extract from Oracle documentation

Editor's Column - Traditional DBMS

2012-02-05T06:42:00.000-08:00

Editor's column
There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ).

Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source.

For instance,

Consider the following:

file1.txt:
1 I am line number 1
2 I am line number 2
3 I am line number 3
4 I am line number 4
5 I am line number 5
6 I am line number 6
7 I am line number 7
8 I am line number 8
9 I am line number 9
10 I am line number 10

To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language.

1. Open file1.txt, and a temporary file temp_f.txt
2. Read lines one by one
3. Match the 8th line records
4. If matches, Dont write in file temp_f.txt
5. If match not found, write record in temp_f.txt
6. Repeat step 2,3,4,5 until the file1.txt reaches it end of line
7. Close file1.txt and delete file1.txt
8. Now rename the file temp_f.txt to file1.txt

Applies same for other DML operations!

User would never know file1.txt has just been removed of only 8 th records, but what actually happens is the file is completely read and except for one record everything is written into an another file and renamed to source.

As we are in 'RAPID-Technolgoical-Environment', we even ignore to read so much and look for what is intended.

Thanks to RDBMS which now makes everyone's task easy.

PLSQL Exception behaviour with INNER BLOCKS

2012-02-02T08:52:00.000-08:00

/**
If exception is unhandled at inner blocks, the control is given to outside blocks... see below code
**/