Cyient Frequently Asked Oracle SQL Queries In Written Test

Get number of seconds passed since today (since 00:00 hr)

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
  FROM DUAL;

Get number of seconds left today (till 23:59:59 hr)

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
  FROM DUAL;

Check if a table exists in the current database schema

A simple query that can be used to check if a table exists before you create it. This way you can make your create table script rerunnable. Just replace table_name with actual table you want to check. This query will check if table exists for current user (from where the query is executed).

SELECT table_name
  FROM user_tables
 WHERE table_name = 'TABLE_NAME';

Cyient Frequently Asked Oracle SQL Queries In Written Test Interview Questions

Check if a column exists in a table

Simple query to check if a particular column exists in table. Useful when you tries to add new column in table using ALTER TABLE statement, you might wanna check if column already exists before adding one.

SELECT column_name AS FOUND
  FROM user_tab_cols
 WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

Showing the table structure

This query gives you the DDL statement for any table. Notice we have pass ‘TABLE’ as first parameter. This query can be generalized to get DDL statement of any database object. For example to get DDL for a view just replace first argument with ‘VIEW’ and second with your view name and so.

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

Getting current schema

Yet another query to get current schema name.

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

Changing current schema

Yet another query to change the current schema. Useful when your script is expected to run under certain user but is actually executed by other user. It is always safe to set the current user to what your script expects.

Find the last record from a table

This ones straight forward. Use this when your table does not have primary key or you cannot be sure if record having max primary key is the latest one.

SELECT *
  FROM employees
 WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees
MINUS
SELECT *
  FROM employees
 WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

Row Data Multiplication in Oracle

This query use some tricky math functions to multiply values from each row. Read below article for more details.
More info: Row Data Multiplication In Oracle

WITH tbl
     AS (SELECT -2 num FROM DUAL
         UNION
         SELECT -3 num FROM DUAL
         UNION
         SELECT -4 num FROM DUAL),
     sign_val
     AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
           FROM tbl
          WHERE num < 0)
  SELECT EXP (SUM (LN (ABS (num)))) * val
    FROM tbl, sign_val
GROUP BY val;

Generating Random Data In Oracle

You might want to generate some random data to quickly insert in table for testing. Below query help you do that. Read this article for more details.
More info: Random Data in Oracle

SELECT LEVEL empl_id,
           MOD (ROWNUM, 50000) dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,
           TO_DATE (
                 ROUND (DBMS_RANDOM.VALUE (1, 28))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1, 12))
              || '-'
              || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
              'DD-MM-YYYY')
              dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address
      FROM DUAL
CONNECT BY LEVEL < 10000;

Random number generator in Oracle

Plain old random number generator in Oracle. This ones generate a random number between 0 and 100. Change the multiplier to number that you want to set limit for.

--generate random number between 0 and 100
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

Check if table contains any data

This one can be written in multiple ways. You can create count(*) on a table to know number of rows. But this query is more efficient given the fact that we are only interested in knowing if table has any data.

SELECT 1
  FROM TABLE_NAME
 WHERE ROWNUM = 1;

Oracle Select Query

Oracle select query is used to fetch records from database. For example:

SELECT * from customers;

Oracle Insert Query

Oracle insert query is used to insert records into table. For example:

insert into customers values(101,'rahul','delhi');

Oracle Update Query

Oracle update query is used to update records of a table. For example:

update customers set name='bob', city='london' where id=101;

Oracle Delete Query

Oracle update query is used to delete records of a table from database. For example:

delete from customers where id=101;

Get position of 'o' in name 'John' from employee table

Select instr(FIRST_NAME,'o') from employee where first_name = 'John'

Oracle Truncate Query

Oracle update query is used to truncate or remove records of a table. It doesn't remove structure. For example:

truncate table customers;

Get First_Name from employee table after replacing 'o' with '$'

select REPLACE(FIRST_NAME,'o','$') from employee

Oracle Drop Query

Oracle drop query is used to drop a table or view. It doesn't have structure and data. For example:

drop table customers;

Get Joining Date,Time including milliseconds from employee table

select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from
EMPLOYEE . Column Data Type should be “TimeStamp”

Oracle Create Query

Oracle create query is used to create a table, view, sequence, procedure and function. For example:

CREATE TABLE customers
( id number(10) NOT NULL,
  name varchar2(50) NOT NULL,
  city varchar2(50),
CONSTRAINT customers_pk PRIMARY KEY (id) 
);

Oracle Alter Query

Oracle alter query is used to add, modify, delete or drop colums of a table. Let's see a query to add column in customers table:

ALTER TABLE customers
ADD age varchar2(50);