Top Ten Sql Queries

1.Q. How is the primary key different from a unique key?

A. Both the primary and unique keys uniquely identify a record in a database table. main  difference is that you can have

more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, where

as  the unique key allows null value.

2.Q. What is the SQL syntax for sorting, and which is the default order?

A. Syntax:
The basic syntax of ORDER BY clause which would be used to sort result in ascending or descending order is as follows:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

 The default sorting order is ascending.

select from  Employees order by Last_name

select  from employees order by hire_date Desc

For descending order simply desc

Following is an example, which would sort the result in ascending order by NAME and SALARY:
You can use More than one  column in order by clause.

SQL> SELECT * FROM Employees
     ORDER BY last_name, SALARY;



3. How to find third highest or second maximum salary of an Employee

Rownum is defined for ORACLE, while Top is defined for MS SQL  Both rownum and top functions the same,
i.e of selecting the top N tuples according to the query, where N is the number specified when both rownum .

 Select Rownum as rank, last_name, salary  from ( Select last_name, salary from employees order by sal desc) where rownum<=3;
So the above query is made for finding the three highest earners.

4. 1.What is the differance between Having & Where ?

Where Clause Is used For filtering Rows & Having Clause is for Filter Groups.
For Example to view the details of Employees who is working in department_id=50

select employee_id,salary,department_id
from employees
where department_id=50

Output :- EMPLOYEE_ID     SALARY DEPARTMENT_ID
----------- ---------- -------------
        198       2600            50
        199       2600            50
        120       8000            50
        121       8200            50
        122       7900            50
        123       6500            50
        124       5800            50
        125       3200            50
        126       2700            50
        127       2400            50
        128       2200            50


Group By Clause Example

select department_id,sum(salary)
from employees group by department_id

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       51600
           30       24900
                     7000
           20       19000
           70       10000
           90       58000
          110       20300
           50      156400
           40        6500
           80      304500
           10        4400

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           60       28800

12 rows selected.


5. Describe all aggregate Functions with example.

Aggregate functions return a single result row based on groups of rows, rather than on single rows.

select sum(salary) from employees
where department_id=40

select avg(salary) from employees
where department_id=40

select min(salary) from employees
where department_id=40

select max(salary) from employees
where department_id=40



6. What is the differance between truncate & delete command ?

 The delete command is used to remove rows from a table. a  where clause can be used to only remove some rows.

Truncate removes all records from a table and we can not be rolled back and no triggers will fired on truncate command. delete keeps

record into recycle bin(Buffer).

 SQL> delete from employee where employee_id=124;

1 row deleted.


SQL> truncate table employee;

Table truncated.


7. How to convert salary in words.

SQL> select to_char(to_date(salary,'j'),'jsp') from employees;


Output :-

TO_CHAR(TO_DATE(SALARY,'J'),'JSP')
------------------------------------------------
two thousand six hundred
two thousand six hundred
four thousand four hundred
thirteen thousand
six thousand
six thousand five hundred
ten thousand
twelve thousand
eight thousand three hundred
twenty-four thousand
seventeen thousand


8. What is the differance between Uniq key and primary key.?

You can have more than one unique key  per table but you can have onle one primary key per table.it creates an index for primary key automatically.

Sql does not allows null value for primary key but unique key has null value in a table.

9. To find max salary from each department.

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


10.  Write a sql query to display current date & time.
select sysdate,systimestamp from dual.


11. How to view table comments

SELECT comments
FROM user_tab_comments
WHERE table_name='DEPARTMENTS'


12. How to add a comment to a Table.

COMMENT ON VIEW  V3
IS 'Employee Information data '



13. Write a sql query that list the names employees whose first_name starts with s or k

select employee_id,last_name,first_name from employees
where regexp_like(first_name,'^s','i');

EMPLOYEE_ID LAST_NAME                 FIRST_NAME
----------- ------------------------- ------------------
        203 Mavris                    Susan
        205 Higgins                   Shelley
        100 King                      Steven
        116 Baida                     Shelli
        117 Tobias                    Sigal
        123 Vollman                   Shanta
        128 Markle                    Steven
        138 Stiles                    Stephen
        161 Sewall                    Sarath
        166 Ande                      Sundar
        173 Kumar                     Sundita

EMPLOYEE_ID LAST_NAME                 FIRST_NAME
----------- ------------------------- ------------------
        192 Bell                      Sarah
        194 McCain                    Samuel


14.  How can i create en  empty table with same structure of employees?

create table temp as select * from employees where 1=2;



15. What is a Self Join In Oracle ?

A self join  a join  in which a table is joined with itself .Specially when the

table has a foreign key which references its own primary key.To join a table itself

means that each row of the table is combind with itself and with every other row of

the table.the self join can be viewied as a join of two coppies of  the some table

the table is not actually copied but sql performs the command as though it were.

the syntax of the command for joining a table to itself is almost same as that for

joining two different tables . to distinguish the column names from one anther

aliases are for the actual the table name are used since both the tables have same

name.

Table name aliases are defined in the FROM clause of the SELECT statement see

the syntax:

select a column_name, b. column_name...FROM tablel a, tablel a, tablel b where a

common_filed =b.common_fileld;

Manager_id in the worker table is equal to Employee_id in the Manager table.



SELECT e.last_name emp, m.last_name mgr
FROM   employees e JOIN employees m
ON    (e.manager_id = m.employee_id);




16 How to check if table contains any data

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

17.How to find the last record from the table

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


18. Get number of days between two dates.
select round((months_between('01-feb-2014','01-mar-2012') *30,0) num_of_days
from dual;

19. How to get 3 maximum salaries  from employees table.

select distinct salary from employees a where 3>=(select count(distinct sal) from employees b where

a.salary<=b.salary) order by a.salary desc.

20. . How  to fetch alternate  records from a table ?

Records can be fetched for both  odd and even row numbers.

To display even numbers.
select studentid from( select rowno,studentid from students ) where mod(rowno,2)=0