Oracle PL SQL

Learn about NULL value and an empty string behavior in oracle database with example queries. NULL means no-value(nothing). An empty string(”) treated as NULL value Let’s create a table suppliers_tbl with the following table definition. Next, we’ll insert following records into this table. The first statement inserts a record with a supplier_name that is null, […]

Learn the about the logical columns that are not exist in the table, but how can be used to select the data from the table, with explanation and example queries. Rowid, Rownum are the Pseudo columns in oracle used to select the data from tables. Rowid ROWID is a pseudo column in a table which […]

Learn about the logical columns that exists in oracle database with example queries. Pseudo columns are nothing but logical columns which behaves like a physical columns in database. Means Pseudo columns behaves like a table columns but is not actually stored in the table. You can select from pseudo-columns, but you cannot insert, update, or […]

Learn how to identify the columns present with NULL values in the table with example queries. There can be different ways to identify the columns having NULL values. Below are the few queries to find the NULL value columns in the table. Below is the procedure to find the Columns having all NULL values Below […]

Learn how to remove duplicate records present in a table with standard sql queries and also dynamic sql with example queries. For base table reference please click on the given link. To implement removing duplicate rows using dynamic SQL we just want to have the knowledge about Listagg, Execute Immediate concepts. Below are example queries […]

Learn how to fetch top N rows using SQL TOP, ROWNUM, LIMIT, FETCH clauses with example queries. Row Limiting clause is useful to limit the number of records to return on large tables with thousands of records. TOP, ROWNUM, LIMIT, FETCH are Row Limiting clauses used to specify the number of records to return. TOP […]

Learn how to write SQL queries for questions given below. Queries are based on the Employee and Incentives tables given below. Let’s prepare the both the table first and then start with SQL queries. Table Name: Employee Employee Table with data EMPNO ENAME MGR HIREDATE SAL DEPTNAME 1 SMITH 3 12/17/1980 800.00 BANKING 2 ALLEN […]

Learn how to extract a DAY,MONTH,YEAR,HOUR,MINUTE AND SECOND from a date time data type values. EXTRACT function extracts a date or a timestamp and returns the year, month, day, hour, minute, second, or time zone. EXTRACT extracts a YEAR, MONTH, and DAY from a DATE data type. EXTRACT extracts a TIMEZONE_HOUR and TIMEZONE_MINUTE from TIME_STAMP […]

Learn how to use a Time stamp data type ,how to convert a Time stamp in string format to a string Timestamp format using TO_TIMESTAMP string function and TIMESTAMP literal formats. Also Learn how to get the current operating system time stamp and current session DB time stamp with example queries. The Timestamp data type […]

Learn how to convert a date in string format to a string Date format using TO_DATE function and Date literal formats. Also Learn how to get the current operating system time and current session DB time with example queries. DATE– data type used to store the date(day,month,year) and time(Hours,minutes,seconds) values. Date format of date data […]