Skip to main content

Posts

Identify current and prev details of a customer

  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 ) ;
Post a Comment
Read more
Recent posts

Design and Operations ( Employee- Model )

-- 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_...
Post a Comment
Read more

Read Live Logs from SQL

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
Post a Comment
Read more

SQL - Binary Search Tree

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 ...
Post a Comment
Read more

Asian Population

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...
Post a Comment
Read more

Minimum / Maximum from the given list

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; } } ...
Post a Comment
Read more

Simple SQLPLUS formatting

> 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. >
Post a Comment
Read more