SQL ,PLSQL , Unix

  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 ) ;

-- 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_...

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

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 ...

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) | | C...

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&lt;=aLen/2;i++,j--){ if(minV1 &gt; arrList[i]){ minV1=arrList[i]; } if(minV2 &gt; arrList[j]){ minV2=arrList[j]; } } if(minV2&lt;=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&lt;=aLen/2;i++,j--){ if(minV1 &lt; arrList[i]){ minV1=arrList[i]; } if(minV2 &lt; arrList[j]){ minV2=arrList[j]; } } if(minV2&gt;=minV1){ return minV2; }else{ return minV1; } } ...

> 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. >

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 been OPTIMIZED [2]  and the database no longer performs physical or logical IO on the DUAL table even though the DUAL table still actually exists...

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 ( s...

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>$

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 Archi...

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.,

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. Wha...

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 su 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 Login: knoppix Name: Knoppix User Directory: /home/knoppix Shell: /bin...

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!!!

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.

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!

/** 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...

/** 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...

/** 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.objec...