Oracle-Sql-Basic_Queries

1.Get the first_day of the Month

SQL> SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"

  2      FROM DUAL;

First day

---------

01-AUG-14

2.Get the Last_day of  the Current Month

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"

    FROM DUAL;

3.Write a query that check if a table exists in the current database schema.

 SELECT table_name

   FROM user_tables

  WHERE table_name = 'EMPLOYEES'

4. Write a query that shows a Table structure.

Desc Employees;

5.Write a query to display Current User.

Show User;

6.Write a query to find last_record from a table.

 SELECT employee_id,last_name,job_id

   FROM employees

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

 /

7. To Check table has any data

 select count(*) from employees

Or select 1 from employees

where Rownum=1


8. How to find out table name for which this constraint  applied

sql> select owner_table-name from user_constraints
where constraints_name =<<your constraint_name>>

if you have access to the dba_constraints
sql> select owner_table-name from dba_constraints
where constraints_name =<<your constraint_name>>

9. How to get list of all tables in Oracle

sql> select owner,table_name from all_tables
NOte:- If you are only concerned with tablesthat you own,not those that you have access to, you could use user_table.
/
sql> select owner,table_name from user_tables
 

10.1. How can you convert a number into words using Oracle Sql Query?

Please see the query below:-

SELECT TO_CHAR (TO_DATE (523, 'j'), 'jsp') from dual


Output:
TO_CHAR(TO_DATE(523,'J'),
-------------------------
five hundred twenty-three

11. Display the details of employees salary in round value.

select last_name ,salary,round(salary,3) from employees
/

12.Explain Various Types of Objects In Oracle?

1. View
2. Tables
3.Synonyms
4.Indexes
5. Tablespaces

13.Display Employees details with Department name  & manager name .
SELECT e.employee_id Worker_name ,DEPARTMENT_NAME, FIRST_NAME Manager_name  FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)
/

14.How to check if table contains any data

select count(*) from table name  to know number of rows.
select count(*) from employees;

15.How to find the last record from the table

SELECT *

  FROM employees

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

/

16. write a query to show table names from  current database schema
select table_name from user_tables
SQL> SELECT table_name
      FROM user_tables;


17.How is the primary key different from a  unique key ?

Both these keys are uniquely identifying a record in a database table.

But one difference is that you can have more than one unique per table,

But only one primary key.  Also ,the primary key does not any null value while

the unique key allows null value.


18: Get all employee  details from the employees table
   
Sql> select * from employees


19: Get the position of 'g' in the  last_name 'King' from employees table.
Sql>  select (instr(last_name,'g') ) from employees
       where last_name='King';

(INSTR(LAST_NAME,'G'))
----------------------
                     4
                     4
20.  select first three characters from last_name from employees table

SQL> select substr(last_name,0,3) from employees
  2  where department_id=30;