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, […]
Rowid vs Rownum vs Row_Number() in oracle.
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 Below is the example query to distinguish rowid address. […]
Pseudo columns in Oracle
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 […]
Identify columns with NULL values
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 […]
Remove duplicate records using dynamic SQL
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 […]
Row limiting clause TOP ROWNUM LIMIT FETCH
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 […]
SQL practice test queries on Employee data in relational table
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 […]
Extract function with DATE TIME data type values
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 […]
Oracle TIMESTAMP data type
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 […]
Oracle DATE 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 […]