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.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
21.Get the Last_day of the Current Month
SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;
22.Write a query that check if a table exists in the current database schema.
SELECT table_name
FROM user_tables
WHERE table_name = 'EMPLOYEES'
23. Write a query that shows a Table structure.
Desc Employees;
24.Write a query to display Current User.
Show User;
25.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)
/
26. To Check table has any data
select count(*) from employees
Or select 1 from employees
where Rownum=1
27. 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>>
28. 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
29.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
30 Display the details of employees salary in round value.
select last_name ,salary,round(salary,3) from employees
/
31.Explain Various Types of Objects In Oracle?
1. View
2. Tables
3.Synonyms
4.Indexes
5. Tablespaces
32 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)
/
33.How to check if table contains any data
select count(*) from table name to know number of rows.
select count(*) from employees;
34 .How to find the last record from the table
SELECT *
FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees)
/
35. write a query to show table names from current database schema
select table_name from user_tables
SQL> SELECT table_name
FROM user_tables;
36.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.
37: Get all employee details from the employees table
Sql> select * from employees
38: 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
39. select first three characters from last_name from employees table
SQL> select substr(last_name,0,3) from employees
2 where department_id=30;
40. Display the last name and salary for any employee whose salary is not in the range of $5,000 to $12,000
42. Identified the query:-
select e.employee_id,e.last_name||' Reporting '||m.last_name||'--' from employees e,employees m
where e.manager_id=m.employee_id
/
Options Are
1. Cartesian Product
2. Self Join
3. outer
4. Equi
Ans:- Self Join
43. 2.You want to delete a record from thae table by prompting the user for an Employee Id Number.
How do you do that?
A.Delete from employees where employee_id=*eno;
b.Delete from employees where &employee_id=*eno;
c.Delete from employees where &employee_id=@eno;
d.Delete from employees where employee_id=&eno;
Ans:-Delete from employees where employee_id=&eno;
44. You have created a Table with the following syntax.
create table teacher(id number primary key,fname varchar2(50),city varchar2(50));
For which column Index will be created automatically.
A.city
b. Id
c.fname
Ans:- ID
45. Changing current schema
ALTER SESSION SET CURRENT_SCHEMA = new_schema;
No comments:
Post a Comment