Basic Examples in sql

1. 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.

2. 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.


3. 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



4. 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.


5. 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


6. 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.

7. To find max salary from each department.

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


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


9. How to view table comments

SELECT comments
FROM user_tab_comments
WHERE table_name='DEPARTMENTS'


10. How to add a comment to a Table.

COMMENT ON VIEW V3
IS 'Employee Information data '



11. 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


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

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



13. What is DCL in  SQL?


Data Control Language is used to manage user access to an oracle.
This  category is  for operations that include REVOKE, ALTER USER and GRANT ?

For  example:-
Commit
Rollback
Savepoint

Grant
Revoke