Polaris Frequently Asked Oracle SQL Queries In Written Test Interview Questions
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);
How to add column in a table
Syntax:
ALTER TABLE table_name
ADD column_name column-definition;
Example:
Consider that already existing table customers. Now, add a new column customer_age into the table customers.
ALTER TABLE customers
ADD customer_age varchar2(50);
Now, a new column "customer_age" will be added in customers table.
How to add multiple columns in the existing table
Syntax:
ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
...
column_n column_definition);
Example
ALTER TABLE customers
ADD (customer_type varchar2(50),
customer_address varchar2(50));
Now, two columns customer_type and customer_address will be added in the table customers.
How to modify column of a table
Syntax:
ALTER TABLE table_name
MODIFY column_name column_type;
Example:
ALTER TABLE customers
MODIFY customer_name varchar2(100) not null;
Now the column column_name in the customers table is modified
to varchar2 (100) and forced the column to not allow null values.
How to modify multiple columns of a table
Syntax:
ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type);
Example:
ALTER TABLE customers
MODIFY (customer_name varchar2(100) not null,
city varchar2(100));
This will modify both the customer_name and city columns in the table.
How to drop column of a table
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
ALTER TABLE customers
DROP COLUMN customer_name;
This will drop the customer_name column from the table.
How to rename column of a table
Syntax:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
Example:
ALTER TABLE customers
RENAME COLUMN customer_name to cname;
This will rename the column customer_name into cname.
Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value.
SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
FROM DUAL;
Get the last day of the month?
This query is similar to above but returns last day of current month. One thing worth noting is that it automatically takes care of leap year. So if you have 29 days in Feb, it will return 29/2. Also similar to above query replace SYSDATE with any other date column/value to find last day of that particular month.
SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;
Get the first day of the Year
First day of year is always 1-Jan. This query can be use in stored procedure where you quickly want first day of year for some calculation.
SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;
Get the last day of the year
Similar to above query. Instead of first day this query returns last day of current year.
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL
Get number of days in current month
Now this is useful. This query returns number of days in current month. You can change SYSDATE with any date/value to know number of days in that month.
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
FROM DUAL;
Get number of days left in current month
Below query calculates number of days left in current month.
SELECT SYSDATE,
LAST_DAY (SYSDATE) "Last",
LAST_DAY (SYSDATE) - SYSDATE "Days left"
FROM DUAL;
Get number of days between two dates
Use this query to get difference between two dates in number of days.
SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
num_of_days
FROM DUAL;
OR
SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;
Use second query if you need to find number of days since some specific date. In this example number of days since any employee is hired.
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”
 |
| Polaris Frequently Asked Oracle SQL Queries In Written Test Interview Questions |
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);
How to add column in a table
Syntax:
ALTER TABLE table_name
ADD column_name column-definition;
Example:
Consider that already existing table customers. Now, add a new column customer_age into the table customers.
ALTER TABLE customers
ADD customer_age varchar2(50);
Now, a new column "customer_age" will be added in customers table.
How to add multiple columns in the existing table
Syntax:
ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
...
column_n column_definition);
Example
ALTER TABLE customers
ADD (customer_type varchar2(50),
customer_address varchar2(50));
Now, two columns customer_type and customer_address will be added in the table customers.
How to modify column of a table
Syntax:
ALTER TABLE table_name
MODIFY column_name column_type;
Example:
ALTER TABLE customers
MODIFY customer_name varchar2(100) not null;
Now the column column_name in the customers table is modified
to varchar2 (100) and forced the column to not allow null values.
How to modify multiple columns of a table
Syntax:
ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
...
column_n column_type);
Example:
ALTER TABLE customers
MODIFY (customer_name varchar2(100) not null,
city varchar2(100));
This will modify both the customer_name and city columns in the table.
How to drop column of a table
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
ALTER TABLE customers
DROP COLUMN customer_name;
This will drop the customer_name column from the table.
How to rename column of a table
Syntax:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
Example:
ALTER TABLE customers
RENAME COLUMN customer_name to cname;
This will rename the column customer_name into cname.
How to retrieve 2nd highest sal in each departement from emp and dept tables using GROUP BY?
SELECT e.DeptNo, MAX(e.Sal),d.DeptName Salary
FROM Emp e left outer join dept d ON e.DeptNo=d.DeptNo
WHERE e.Sal <
(SELECT MAX(Sal)
FROM Emp
WHERE DeptNo = e.DeptNo)
GROUP BY e.DeptNo,d.DeptName
Find the 3rd MAX and MIN salary in the emp table?
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the records but common records only once?
(Select * from emp) Union (Select * from emp1)
How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
Get the first day of the month?SELECT e.DeptNo, MAX(e.Sal),d.DeptName Salary
FROM Emp e left outer join dept d ON e.DeptNo=d.DeptNo
WHERE e.Sal <
(SELECT MAX(Sal)
FROM Emp
WHERE DeptNo = e.DeptNo)
GROUP BY e.DeptNo,d.DeptName
Find the 3rd MAX and MIN salary in the emp table?
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
If there are two tables emp1 and emp2, and both have common record. How can I fetch all the records but common records only once?
(Select * from emp) Union (Select * from emp1)
How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value.
SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
FROM DUAL;
Get the last day of the month?
This query is similar to above but returns last day of current month. One thing worth noting is that it automatically takes care of leap year. So if you have 29 days in Feb, it will return 29/2. Also similar to above query replace SYSDATE with any other date column/value to find last day of that particular month.
SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;
Get the first day of the Year
First day of year is always 1-Jan. This query can be use in stored procedure where you quickly want first day of year for some calculation.
SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;
Get the last day of the year
Similar to above query. Instead of first day this query returns last day of current year.
SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL
Get number of days in current month
Now this is useful. This query returns number of days in current month. You can change SYSDATE with any date/value to know number of days in that month.
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
FROM DUAL;
Get number of days left in current month
Below query calculates number of days left in current month.
SELECT SYSDATE,
LAST_DAY (SYSDATE) "Last",
LAST_DAY (SYSDATE) - SYSDATE "Days left"
FROM DUAL;
Get number of days between two dates
Use this query to get difference between two dates in number of days.
SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
num_of_days
FROM DUAL;
OR
SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;
Use second query if you need to find number of days since some specific date. In this example number of days since any employee is hired.
Post a Comment