stattriada.blogg.se - Interview questions for oracle sql developer

INTERVIEW QUESTIONS FOR ORACLE SQL DEVELOPER HOW TO

Weekends means the Saturdays and Sundays from the month. The query will give the all the dates from first to last of current date.įrom all the month, we need to calculate the weekends. SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dtĬONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1 Let us Fragment the Query for Understanding, Where TO_CHAR (Current_dt,’dy’) IN (‘sat’,’sun’) (SELECT TRUNC (SYSDATE,’mm’) +LEVEL-1 Current_dtĬONNECT BY LEVEL <= last_day (SYSDATE) – TRUNC (SYSDATE,’mm’) +1 (Select * from Tabs) -Tabs is system table in which user get the different user defined table names.Įxecute immediate (‘Drop Table ‘||i.table_name||’cascade constraints’) ġ1.How to get number of Weekends of current month? To Drop all tables user needs to write simple PLSQL block Tip: In real scenarios, lot of times developer needs to calculate the number of commas in the column then regexp_count function is used.ĩ.How to create the Student_1 table, which is exact replica of Student table?Ĭreate Table Student_1 as select * from Student ġ0.What is Query to drop all user tables from Oracle? Select Student_name, regexp_count (marks,’,’) + As “Marks Count” from Student

Want to display output like : Student Name

INTERVIEW QUESTIONS FOR ORACLE SQL DEVELOPER HOW TO

How to calculate the count of that comma separated values? Student Name Select to_date (Hire_date,’DD-MON-YYYY’) Date_Format from Employee Ĩ.If marks column contain the comma separated values from Student table. SELECT lpad (‘*’, ROWNUM,’*’) FROM Student WHERE ROWNUM <4 ħ.How to display Date in DD-MON-YYYY table? We cannot use dual table to display output given above. Click here to get information about union and union all. Tip : Use the concept of union to show the max and min marks together. Select * from Employee a Where 3 = (Select Count (distinct Salary) from Employee where a.salary<=b.salary ĥ.How to Show the Max marks and min marks together from student table? Select rollno, count (rollno) from StudentĤ.How to find Third highest salary in Employee table using self-join?(90% asked Complex SQL Interview Questions ) (Select max (rowid) from Student b where rollno=b.rollno) 3.How to find count of duplicate rows? (95% asked complex sql interview questions )

These are stored separately from other data items.(Select max (rowid) from Student b where rollno=b.rollno)

Large object data type- These data types hold locators that defines the location of large objects stored out of the line.

Reference data type- it is a data type that holds pointers to other data items.

Composite data type- it is a combination of other data types and internal components that can be easily altered and manipulated.

LONG– it is an Oracle data type for storing character data of variable length.

DATE– it is a data type for storing date and time information.

BOOLEAN– it is a data type with two values, true or false, intended to represent the truth values of logic and Boolean algebra.

VARCHAR2– these data types store variable-length character strings ranging from 1 to 32,767 characters.

Its value ranges from 1 to 32,767 characters.

CHAR– these data types hold the fixed-length character strings.

They are further classified into four categories.

Scalar data type-these are single-valued data types with no internal components.

The following are four data types available in PL/SQL:

20) What is the difference between ROLLBACK and ROLLBACK TO statements?Įach constant, value and parameter has a data type that tells us the type of data used and their associated operations in the program.

19) What is the basic structure of PL/SQL?.

18) What is a mutating table and a constraining table?.

17) What is PL/SQL table? Why is it used?.

16) What is the difference between function, procedure and package in PL/SQL?.

15) How many types of triggers exist in PL/SQL?.

14) What are some predefined exceptions in PL/SQL?.

13) Differentiate between syntax and runtime errors?.

11) Explain the difference between Triggers and Constraints?.

10) What is the difference between execution of triggers and stored procedures?.

5) Explain the purpose of %TYPE AND %ROWTYPE data types?.

4) Enlist the data types that can be used in PL/SQL?.

3) State few characteristics of PL/SQL?.2) Differentiate between PL/SQL and SQL?.