First round
-------------------
- Procedure
- Packages
- Views
- Virtual tables
- Can we use dcl with in function?
- Joins and few scenarios
- Triggers and its type
- Pragma, type and its functionality
- How to create db link in oracle
- Materialized view
- How to find duplicate values from table?
- Cursor and its functionality
- Write a script to display friday and its date from a entire year.
- Exception Handling
Second round
------------------------
Gave a scenario like. Need to write a function to perform. When user try to change a password. It must not be last five password and a given password can be combination of characters, symbols, upper and lower case.
- 1 Answers
- 1677 Views
- Prodapt, I also Faced
- E-Mail Answers
Answer Posted / Rupendra Bahadur Singh
[First Round]-n- Procedure: A procedure is a collection of PL/SQL statements that can be called by name.n- Packages: A package is a collection of related procedures, functions, and variables.n- Views: A view is a virtual table based on the result set of a SELECT statement.n- Virtual tables: A virtual table is a temporary table created in memory.n- Can we use dcl with in function? Yes, DCL (Data Control Language) statements can be used within PL/SQL functions to control access to database objects.n- Joins and few scenarios:n - INNER JOIN: Combines rows from two tables where the join condition is true.n - LEFT JOIN: Returns all rows from the left table and matching rows from the right table, if any.n - RIGHT JOIN: Returns all rows from the right table and matching rows from the left table, if any.n- Triggers and its type:n - DDL triggers: Activate when changes to database objects occurn - DML triggers: Activate when changes to data in a table occurn- Pragma, type and its functionality: PRAGMA is a directive that can be used to set various options for the Oracle Database compiler.n- How to create db link in oracle: To create a database link, use the CREATE DATABASE LINK statement.n- Materialized view:n - A materialized view is a precomputed and stored version of a query result that can be used to improve query performance.n- How to find duplicate values from table?: Use the DISTINCT keyword in a SELECT statement to eliminate duplicates, or use aggregate functions such as COUNT(*) or GROUP BY to count the number of occurrences of each value.n- Cursor and its functionality: A cursor is a server-side pointer to a set of rows in a database table.n- Write a script to display friday and its date from a entire year.: Here's a sample SQL script that displays Friday dates for an entire year:nn```sqlnSET SERVEROUTPUT ON;nDECLAREn my_date DATE; nBEGINn FOR i IN 1..365 LOOPn my_date := TO_DATE('01-JAN-' || TO_CHAR(i) || '+' || ((MOD(i,7)-1) * 7) || ' DAY');n IF EXTRACT(DAYOFWEEK FROM my_date) = 6 THENn DBMS_OUTPUT.PUT_LINE('Friday: ' || TO_CHAR(my_date, 'DD-MON-YYYY'));n END IF;n END LOOP;nEND;```
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
what is bcp? When does it used? : Sql dba
Is primary key always clustered index?
what is collation? : Sql dba
Hi am new to PLSQL & facing problems in writing code like in SP, Functions, so any one having some SP coding with in depth explanation please share with me my Email ID suvarnaatsuvarna@rediffmail.com Or taking tanning on this please do contact me
If a cursor is open, how can we find in a pl/sql block?
how to use regular expression in pattern match conditions? : Sql dba
What is the current version of postgresql?
Can delete statement be rollbacked?
Can we use distinct and group by together?
Is inner join faster than left join?
what are aggregate and scalar functions? : Sql dba
Can we rollback truncate?
Do we need to rebuild index after truncate?
What is the best sql course?
how to start mysql server? : Sql dba
