Get nth Highest or Lowest data from Table

Here, we are considering a table student and we need to find out Nth highest and lowest marks of a student.

Nth Highest

select * from student s1 where N-1 = ( select count(distinct(marks)) from student s2 where s1.marks > s2.marks)

SQL Scenario - 1

You have a table like below :

Name Age Gender
Atul         11 F
Ram 10 F
Disha  6 M
Pavan  8 F
Mani         10 M
Lovely 10 M
Rani    9 M

SQL Assignment08 - Delete Statement

1. Delete from customers.
2. Delete depositor of branches having number of customers between 1 and 3.
3. Delete branches havinng average deposit less than 5000.
4. Delete branches having maximum loan more than 5000.
5. Delete branches having deposit from Nagpur.

SQL Assignment07 - Update Statement

1. Give 10% interest to all depositors.
2. give 10% interest to all depositors having branch VRCE
3. Give 10% interest to all depositors living in city Nagpur.
4. Give 10% interest to all depositors having branch in city BOMBAY and living in Nagpur.
5. Add 100 Rs to deposit of Anil and assign it to Sunil.
6. Change the deposit of VRCE branch to 1000 and change the branch as VRCE_UPT
7. Assign to the deposit of Anil the maximum deposit from VRCE branch

SQL Assignment06 - Group By or Having Clause

1. List the branches having sum of deposit more than 5000.
2. List the branches having a sum of deposit more than 5000 and located in city BOMBAY.
3. List the name of Customers having deposit in the branches where the average deposit is more than 5000.
4. List the names of customers having maximum deposit.
5. List the names of customers having maximum deposit in the table CUSTOMERS living in Nagpur.
6. List the name of branch having highest number of depositors.
7. Count the number of depositors living in Nagpur.

SQL Assignment05 - Aggregate Functions

1. List total loan.
2. List total deposit.
3. List total loan taken from KAROLBAGH branch.
4. List total deposit of customers having account date later than 1-Jan-96.
5. List total deposit of customers living in city Nagpur.
6. List maximum deposit of customers living in BOMBAY.
7. List total deposit of customers having branch city DELHI

SQL Assignment04 - Operations

1. List all the customers who are depositors but not borrowers.
2. List all the customers who are both depositors and borrowers.
3. List all the customers, their amount, who are either depositors or borrowers and living in city Nagpur.
4. List all the depositors having deposit in all the branches where Sunil is having account.
5. List all the customers living in city Nagpur and having branch city BOMBAY or DELHI.

SQL Assignment03 - Join or Cartesian Product

Some more queries on SQL to work on --

1. Give names of customers having living city BOMBAY and branch city Nagpur.
2. Give Names of Customers having the same living city as their branch city.
3. Give names of customers who are borrowers as well as depositors and having living city as Nagpur.
4. Give names of customers who are depositors and having the same branch city as that of Sunil.
5. Give names of depositors having the same living city as that if Anil and having deposit amount greater than 2000.

SQL Assignment02 - Select Statement

Now, As we created some tables with data, lets do some practice with basic SQLs, Try to write queries for below statements -

-- List all data from table DEPOSIT
-- List all data from table BORROW
-- List all data from table CUSTOMERS
-- List all data from table BRANCH

SQL Assignment01 - Creating tables

Hello Guys, As we all know that SQL is "must" known for getting into DWH area, So considering this what I am trying to do here is sharing some basic SQL assignments to complete. Start with me if wanna to test your SQL knowledge :-)

First of all, we will create some tables ( Script may need to change as per DB you are using ) - 

Delete Duplicate Rows in DB2 Database

Many times and Many places, this question is being asked :-) that How to delete the duplicate row from a table in different DBs. Here, we will see How to do this in DB2 DB.

How to UPDATE row based on a row in same table

How you will update the Data of a table based on same table ??


update Table_DUMMY T1 
set COL_UPDT = (select T2.COL_FROM from Table_DUMMY T2 where T1.KEY=T2.KEY )  


Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you
https://twitter.com/datastage4you
For WHATSAPP group , drop a msg to 91-88-00-906098

Oracle table is Partitioned or Not ?

For getting whether Oracle table is Partitioned or not, we can use below query on ALL_TABLES -

Oracle SQL Tuning Tips - 3

• Oracle automatically performs simple column type conversions(or casting) when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using.Use:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_no = ’123′;
  HERE if emp_no indexed numeric, then after implicit conversion query will be:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_no = TO_NUMBER(’123′);
  Thus, index is used in this case.
  Don’t use:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_type = 123;
  HERE if emp_type is indexed varchar2, then after implicit conversion query will be:
  SELECT emp_no, emp_name, sal FROM emp WHERE TO_NUMBER(emp_type) = 123;
  Thus, index will not be used in this case.

Oracle SQL Tuning Tips - 2

• Don’t forget to tune views. Views are SELECT statements and can be tuned in just the same way as any other type of SLECT statement can be. All tuning applicable to any SQL statement are equally applicable to views.
• Avoid including a HAVING clause in SELECT statements. The HAVING clause filters selected rows only after all rows have been fetched. Using a WHERE clause helps reduce overheads in sorting, summing, etc. HAVING clauses should only be used when columns with summary operations applied to them are restricted by the clause.Use:
  SELECT city FROM country WHERE city!= ‘Vancouver’ AND city!= ‘Toronto’; GROUP BY city;
  Rather than:
  SELECT city FROM country GROUP BY city HAVING city!= ‘Vancouver’ AND city!= ‘Toronto’;
  

Oracle SQL Tuning Tips - 1

Consideration when writing an SQL statement is that it returns a correct result. The second is that it be the most efficient for a given situation.  You can use many different SQL statements to achieve the same result. It is often the case that only one statement will be the most efficient choice in a given situation.

Remember that processing SQL is a sequence of Parse (syntax check and object resolution), Execution (required reads and writes), and Fetch (row results retrieved, listed, sorted, and returned). SQL “tuning” consists, quite simply, of reducing one or more of them.

Note: generally Parse is the greatest time and resource hog. Parse overhead can be minimized by the use of Procedures, Functions, Packages, Views, etc.

Some DataStage Job design

Updating the Master Reports List (MRL) in Datastage

Create a Job which has a Row Generator stage linking to an ODBC or DB2 stage. Change the parameter in the Row Generator stage to output zero records. Write the SQL to update the MRL table as 'After SQL' in the output ODBC or DB2 stage.

Some Oracle SQL should known by Developer - 1

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

Performance Tunings in DataStage

JOB LEVEL

• Parametrize all the inputs needed for the jobs; avoid hard coding of parameters like username, password, directory paths etc.
• By using the environment variable ‘APT_CONFIG_FILE’ as a job parameter, user can dynamically change the number of nodes being used for processing a particular job.
• For reading/writing data from large tables/files, make use of the environment variable ‘APT_BUFFER_MAXIMUM_MEMORY’. It can be used to change the memory buffer size being used for each stage.
• It is recommended to set the environment variable $APT_DUMP_SCORE to a value of 1. When this environment variable is set, an entry is placed in the WebSphere DataStage job log showing the actual runtime structure (processes, their associated internal operators, datasets, nodes, etc) used to execute the job flow.

SQL Best Practices - Part1

Never put SQL scalar functions on columns in the WHERE predicates.
For example: WHERE YEAR(HIREDATE) = 2009 should be recoded as WHERE HIREDATE BETWEEN '2009-01-01'and '2009-12-31'.
When placing SQL scalar functions on columns in the SELECT portion of the SQL statement does incur some minimal overhead, but applying it to a column in the WHERE clause causes the predicate to become stage to non indexable.

Same applies for mathematics.
For example: WHERE HIREDATE – 7 DAYS > :HV-DATE should be recoded as WHERE HIREDATE > :HV_DATE + 7 days. Having the mathematics on the host variable is not a problem.