Author Archives: Girish Kirtikumar Mehta.

Few more questions

Q1. What is sysdate? Is it a pseudo column or system variable or a function?Ans. It is a function. Q2. Which one is faster Delete or Truncate?Ans. Truncate is faster. Q3. Can we use Decode function is PL/SQL without SQL statement?Ans. No. Q4. Can a primary key column have a null value?Ans. No. Q5. CanContinue reading “Few more questions”

Solution for Test Your Knowledge 2

Test your knowledge by answering following questions. Note: • COUNTRIES • DEPARTMENTS • EMPLOYEES • JOBS • JOB_HISTORY • LOCATIONS • REGIONS 1. Write a query in SQL to display the first name, last name, department number, and department name for each employee. Solution: 2. Write a query in SQL to display the first andContinue reading “Solution for Test Your Knowledge 2”

Solution for Test Your Knowledge 1

Kindly note that there are various ways to arrive at solution of the questions listed below. The solutions shared below is just to help you in arriving at a solution. Q1. Create Table Students with following fields & Write 2 insert statements for it. First field will be used as a unique identifier for theContinue reading “Solution for Test Your Knowledge 1”

Some Playful, Complex & Amazing Queries

1. Query to get Financial or Yearly Calendar from desired date.
2. Query to print string in X formation.
3. Query to form diamond.
5. Query to form reverse diamond.
6. Query to generate mathematical tables using cartesian product / cross join.

Comparison of Oracle & SQL Server

1. Programming Oracle: PL/SQL is the language used for programming SQL Server: T-SQL is the language used for programming 2. ROWID Oracle: ROWID NumID AAAJWTAAWAAB+BiAAA 10 AAAJWTAAWAAB+BiAAB 20 SQL Server: Output %%physloc%% File:Page:Slot NUM 0x2926020001000000 (1:108786:0) 10 0x2926020001000100 (1:108786:1) 20 3. Dual Oracle: Dual Table is made available by default. SQL Server: Dual table canContinue reading “Comparison of Oracle & SQL Server”

Performance Optimization Tips

Replace IN, NOT IN with Exists & Not Exists. Avoid using “Insert Into … Select …” statements as it can take lot time to execute if select query returns huge number of records. Instead user cursors. Avoid sub queries, instead use joins or subquery factoring (with clause) this will optimize the code. If insert &Continue reading “Performance Optimization Tips”

Coding Standards

All variable names should start with “v”. e.g., vKount Number; All stored procedure names should start with “SP_”.e.g., Create or Replace Procedure SP_HPC_Cashback(OutMsg Out VarChar2) is All stored function names should start with “SF_”.e.g., Create or Replace Procedure SF_CalcInt(Prin Number, Yr Number) return number is All package names should start with “PKG_”.e.g., CREATE PACKAGE PKG_DMR_ProcessingContinue reading “Coding Standards”

Test Your Knowledge 2

Test your knowledge by answering following questions. Note: Kindly use the below mentioned tables only from HR Schema. Don’t create your own tables. • COUNTRIES • DEPARTMENTS • EMPLOYEES • JOBS • JOB_HISTORY • LOCATIONS • REGIONS Write a query in SQL to display the first name, last name, department number, and department name forContinue reading “Test Your Knowledge 2”

Test Your Knowledge 1

You can test to basic SQL & PL/SQL knowledge using the following listed questions. Q1. Create Table Students with following fields & Write 2 insert statements for it. RollNo integer 10 => Auto Increment FName string 50 LName string 50 DOB Date => Date of Birth Add1 string 150 Add2 String 150 City String 100Continue reading “Test Your Knowledge 1”