Venky DevMastery

Click here for the more information: Clik Here

PSUEDO Column: Level and Connect By Clause Q: How to Implement Loop in Oracle SQL? > "LEVEL" is a System Variable (PSUEDO Column) > By default LEVEL initialized with 1 > By default LEVEL incremented by 1 Ex: Initially 1 1+1=2 2+1=3 ....etc. SELECT LEVEL FROM DUAL CONNECT BY LEVEL

ROWNUM and ROWID Psuedo Columns usage in Oracle SQL Importance of Psuedo Columns Frequently used Psuedo Columns in Oracle SQL: 1.ROWID 2.ROWNUM 3.CURRVAL 4.NEXTVAL 5.LEVEL CONNECT BY Q:How to display rowids for any table in Oracle SQL? SELECT ROWID,EMPNO,ENAME,SAL FROM EMP; ROWID is a physical address in DB. It is used to delete duplicate records. It contains 18-Digits(alpha-numeric.)-Base-64 String Example :- CREATE TABLE EMP77 ( ENO NUMBER(4), ENAME VARCHAR2(50), SAL NUMBER(10, 2) ); INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (101, 'ARJUN', 55000); INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (102, 'BEATRICE', 62000); INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (103, 'CHENG', 48500); INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (104, 'DAHLIA', 71000); INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (105, 'ELIAS', 53000); INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (101, 'ARJUN', 55000); E...

ABOUT SEQUENCES in Oracle SQL Definitions:- A Sequence is a DB object created to generate sequence numbers(integers) automatically. It's primary use to create auto-increment values for Surrogate Keys or Pimary Key Columns without manual intervention. Syntax: CREATE SEQUENCE [START WITH ] [INCREMENT BY ] [MAXVALUE ] [MINVALUE ] [CYCLE/NOCYCLE] [CACHE ]; OR CREATE SEQUENCE [START WITH n] [INCREMENT BY n] [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE] [ORDER | NOORDER]; Example 1 :- CREATE SEQUENCE S1 START WITH 1 INCREMENT BY 1 MAXVALUE 5; using sequence :- -------------------------- CREATE TABLE STUDENT ( SID NUMBER(2), SNAME VARCHAR2(10) ); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'A'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'B'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'C'); INSERT INTO STUDENT VALUES...

1. Which column serves as the primary 'Golden Thread' to link a person's identity (Names/Phones) to their employment records (Assignments) in Oracle HCM Cloud? A.PERSON_ID(correct:The PERSON_ID is the universal unique identifier for an individual across all HCM modules.) B.PERIOD_OF_SERVICE_ID C.ASSIGNMENT_ID D.PERSON_NUMBER 2.When querying the table PER_ALL_PEOPLE_F, why is it critical to include the condition: TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date? A.To automatically join the Assignment table B.To filter out terminated employees C.To improve query performance via indexing D.To prevent duplicate rows for the same person(correct: Because the table is date-tracked, every historical change creates a new row; this clause ensures you only get the current version.) 3.You need to pull a list of Department names for a report. Which table stores the 'User-Friendly' name of the department in a specific language? A.PER_DEPARTMENTS B.FND_LOOKUP_VA...

Employee Table Creation and Insertion Scripts in Oracle SQL =========================================================== CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2)); INSERT ALL INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20) INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30) INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30) INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20) INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, T...