Oracle PLSQL ORACLE TRAINER videos

Top-Ten-Queries

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

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. Display the last name, salary, and commission for all employees who earn commissions. Sort

data in descending order of salary and commissions.

SELECT last_name, salary, commission_pct

FROM employees

WHERE commission_pct IS NOT NULL

ORDER BY salary DESC, commission_pct DESC;





Post a Comment

Links to this post