JOINS(DISPLAYING DATA FROM MULTIPLE TABLES)

–Types of JOINS and its syntax

–Natural join:

•USING clause•ON clause

–Self-join

–Nonequijoins

–OUTER join:

•LEFT OUTER join•RIGHT OUTER join•FULL OUTER join

–Cartesian product

Cross join

Creating Natural Joins:

–The NATURAL JOIN clause is based on all columns in the two tables that have the same name.

–It selects rows from the two tables that have equal values in all matched columns.

If the columns having the same names have different data types, an error is returned.

RETRIEVING THE RECORDS WITH NATURAL JOIN

SELECT department_id, department_name,

       location_id, city

FROM   departments

NATURAL JOIN locations ;

Creating Joins with the USING Clause

–If several columns have the same names but the data types do not match, natural join can be applied using the USING clause to specify the columns that should be used for an equijoin.–Use the USING clause to match only one column when more than one column matches.–The NATURAL JOIN and USING clauses are mutually exclusive.

SELECT employee_id, last_name,

       location_id, department_id

FROM   employees JOIN departments

USING (department_id) 

Creating Joins with the ON Clause

SQL INTERVIEW QUESTIONS

Write SQL Query to display current date.

Ans: SQL has built in function called GetDate () which returns current timestamp.

SELECT SYSDATE FROM DUAL;

Mention the difference between clustered and a non-clustered index?

1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
2. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Write an SQL Query to find names of employee start with ‘S’.

Ans: SELECT * FROM Employees WHERE EmpName like ‘S%’.

How will you find the 3rd max salary in the employment table?

Ans: Select distinct salary from employment e1 where 3= (select count (distinct salary) from employment e2 where e1.salary<=e2.salary)

SOME BASIC QUERIES ASKED IN INTERVIEWS

1. I need all lastname,salary from the employees ,salary must be greater than
   the salary of abel  

sol: select last_name,salary from employees where salary>(select salary from employees 
where last_name='abel';

2.I need all last_name,job_id such that all the job id's must be 
whose employee id is 14

sol: select last_name,salary from employees where job_id=(select job_id from 
employees where employee_id=14;

3.Write a query to find the salary of employees whose salary is greater 
  than the salary of employee whose id is 100?

sol: select last_name,salary from employees where salary>(select salary from 
     employees where employee_id=100);

4.Write a query to find the employees who all are earning the highest salary?

sol: select last_name from employees where salary=(select max(salary) 
     from employees);

5.Write a query to find the departments in which the least salary is 
  greater than the highest salary in the department of id 200?

sol: select department_id,min(salary) from employees group by department_id having min(salary)>
     (select max(salary) from employees where employee_id=200)

6.Write a query to find the employees whose salary is equal to the 
  salary of at least one employee in department of id 300?

sol: select employee_id,last_name from employees where salary IN 
     (select salary from employees where department_id=300)

7.Write a query to find the highest earning employee in each department?

sol: select employee_id,last_name from employees where salary 
     IN( select max(salary)
     from employees group by department_id);

8.Write a query to list the department names which have at lease one employee?

sol: select d.department_name,department_id from departments d where exists 
     (select 1 from employees e where  e.department_id=d.department_id)

9.Write a query to get the department name of an employee?

sol: select e.last_name,(select department_name d from departments d) from employees e;

10.Write a query to find the employees whose manager and department should 
   match with the employee of id 20 or 30? 

sol:select employee_id,manager_id from employees where (employee_id,manager_id) 
    IN(select manager_id,department_id from employee_id IN(20,30); 

11.Write a query to find the employees whose salary is greater than 
   at least on employee in department of id 500?

sol: select employee_id,last_name from employees where salary
     >=any(select salary from employees where department_id=500);

12.Write a query to find the employees whose salary is less than 
   the salary of all employees in department of id 100?

sol: select employee_id,last_name from employees where salary
     <all(select salary from employees where employee_id=100);

EXISTS-EXISTS is a Comparison operator, which is used to check 
====== and match records between two queries on correlation basis and 
       returns a BOOLEAN output (TRUE or FALSE).

example:

1. select e.emoloyee_id,e.last_name from employees e where exists
   (select * from departmets d where d.demartment_id=e.department_id);

2. select e.emoloyee_id,e.last_name from employees e where not exists
   (select * from departmets d where d.demartment_id=e.department_id);

3. update employees e set e.employee_id=999 where not exists

   (select * from department d where d.department_id=e.department_id);

FRESHERS INTERVIEW QUESTIONS

hi guys i want to share some Frequently Oracle Database Interview Questions and Answers on Database theory  for Freshers.

1) What is oracle database ?
Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other.

2) What is schema? 
A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema's. We can create a new Schema/User. But we can't drop default database schema's.

3) What is a Tablespace? 
Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema's and a schema can have multiple tablespace's. Database creates "SYSTEM tablespace" by default during database creation. It contains read only data dictionary tables which contains the information about the database. 

4) What is a Control File ? 
Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.

5) Define data blocks ? 
Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk

6) What is an Extent ? 
Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.

7) What is a Segment ? 
A segment is a collection of extends which is used for storing a specific data structure and resides in the same tablespace.

8) What is Rollback Segment ? 
Database contain one or more Rollback Segments to roll back transactions and data recovery.

9) What are the different type of Segments ? 
Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.

10) What is a Redo Log ? 
Redo Log files is a collection of 2 or more pre-allocated files, which is used in data recovery. When ever a change is made to the database, change info gets stored in redo files. In case of a database crash, we can used redo files for data recovery.

11) What is a table Cluster ? 
Table Cluster is a group of related tables that share common columns are store related data in the same block.