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
 where department_id=30;




21. You can find maximum salary for each department by grouping all records by department_id
& then use max function to calculate maximum salary in each department.

sql> select department_id,max(salary) from employees
group by department_id

22.Write a  query to display procedure name in Oracle 

SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE='PROCEDURE';

SELECT *
FROM USER_PROCEDURES;