MULTIPLE TABLES

ORACLE MULTIPLE TABLES

1. MULTIPLE TABLES

2. The required data are in more than one table , related tables are using a join condition. The join condition combines a row in one table with a row in another table based on the same values in the common columns. Different types of joins based on the join condition used. JOIN

3. TYPES OF JOIN

4. The equijoin is a join with a join condition involving common columns from two tables. Syntax: SELECT column names FROM table names WHERE join condition(s ); 1.EQUIJOIN

5. There is no matching column in the EMPLEVEL tables for the salary column in the EMPLOYEE table. The join condition for these tables can be written using any operator other than the =operator that is why it is called non-equijoin. 2.NONEQUIJOIN

6. If a row in one table does not have a matching value in the other table it is not joined. you would change your query’s join condition and create a join known as an outer join. The outer join uses the(+)operator in the join condition on the deficient side . 3.OUTER JOIN

7. SELECT1.columnname,tablename2 .column name FROM tablename1,tablename2 WHERE tablename1.columnname(+) = tablename2.columnname; syntax

8. A Self join is joining a table to itself. one join that is not so easy to understand. a tables is joined to itself two copies of the same table are loaded or used. they are treated like any two different tables and a join is produced from those two copies. 4.SELF JOIN

9. In set operators learned about union, intersection and difference operations if you recall these operations are possible on “union-compatible” tables. The implementation of these operations is through the use of set operators. SET OPERATORS

10. SELECT-Query 1 set operator SELECT-Query 2; where set operator is one of the four set operators described. Syntax

11. UNION - It returns all rows from both queries but duplicate rows are not repeated. UNION ALL – It returns all from both queries and it displays all duplicate rows. INTERSECT – It returns all rows that appear in both queries results. MINUS – It returns rows that are returned by the first query minus rows that are returned by the second query. SET OPERATOR AND USE

12. sub query are also known as nested query .a sub query is usually a SELECT query within one of the clauses in another SELECT query. 1. Single –row query 2. multiple –row query. SUBQUERY

13. A sub query that returns only one row of data it is also known as a scalar sub query. The sub query must be enclosed within a pair of parentheses .The ORDER BY clause cannot be used in a sub query. SYNTEX SELECT column list FROM table name WHERE column operator (SELECT column names FROM table name WHERE condition SINGLE ROW SUBQUERY

14. You can create a table by using a nested SELECT query .the query will create a new table and populate it with the rows selected from the other table . syntax : CREATE TABLE table name AS SELECT query; CREATING A TABLE USING A SUBQUERY

15. An existing table can be populated with a sub query .the table must already exist to insert rows into it . syntax INSERT INTO table name [(column list)] SELECT columns names FROM table name WHERE conditions; INSERT USING A SUBQUERY

16. In most cases you uses the INSERT statement to add a row into a table . 1. INSERT ALL(conditional &unconditional) 2. INSERT FIRST INSERTING INTO MULTIPLE TABLES

17. The rows are selected from the EMPLOYEE table and inserted into two existing tables , EMPLOYEE_SALARY and EMPLOYEE _DEPT. syntax INSERT ALL INTO employee _salary VALUES(Employee id ,L name , F name salary commission UNCONDITIONAL INSERT ALL

18. INTO employee _dept VALUES(Employee id ,L name , F name, Dept id, supervisor) SELECT Employee id ,L name , F name, salary, commission , dept id , supervisor FROM employee WHERE salary > 50000 OR dept id <>40;

19. The rows are inserted into tables based on their individual conditions . The WHEN…..THEN clause is used with different conditions for inserting rows into different tables. SYNTAX INSERT ALL WHEN Salary >50000 THEN INTO employee _salary VALUES (employee id, L name ,F name , salary ,commission ) WHEN dept id<>40 THEN INTO employee _dept VALUES(employee id ,L name , F name , Dept id ,supervisor) SELECT employee id , L name , F name , salary , commission dept id , supervisor FROM employee WHERE salary>50000 OR dept id <>40; CONDITIONAL INSERT ALL

20. A row or rows from a table can be deleted based on a value returned by a sub query. SYNTAX : DELETE FROM table name WHERE column name = ( SELECT sub query) ; DELETE USING A SUBQUERY

21. A multiple –row sub query returns more than one row . the operator used in single row sub queries (=,<>,>,>=,< and <=) cannot be used with multiple –row sub query. MULTIPLE ROW SUBQUERY

22. MERGE STATEMENT You can use the MERGE statement to perform INSERT and UPDATE operations together . This operation is very useful in data warehousing . CORRELATED SUBQUERY In a Correlated sub query the inner (nested )query can reference columns from the outer query .The inner query is executed once for each row in the outer query . In other sub quries the inner query was excueted only once.

23. The EXISTS and NOT EXISTS operators are used with correlated queries . The EXISTS operator checks if the inner query returns at least one row. EXISTS AND NOT EXISTS OPERATORS

24. A view is an oracle object that gives the user a logical view of data from an underlying table or tables. When a view is created from than one tables ,the user can view data from the without using join condition and complex conditions. VIEW

25. A view is created with a SELECT sub query. The sub query cannot use an ORDER by clause but a view can. syntax: CREATE [OR REPLACE] VIEW view name [column aliases] AS SELECT – sub query [WITH CHECK OPTION [CONSTRAINT constraint name]] [WITH READ ONLY]; CREATING VIEW

26. A user who owns a view or who has the privilege to remove it – can remove a view. The removal of a view does not affect data in the underlying table. when a view is removed a “view dropped” message is displayed. DROP VIEW view name; REMOVING A VIEW

27. When you alter an underlying table the view becomes invalid. You need to recompile that view to make it valid again. the ALTER VIEW statement is used for the re –compilation of a view. ALTER VIEW deptsalvu COMPILE; ALTERTING A VIEW

28. A sequence is an oracle object that is used to generate a sequence of number sequencing is a perfect solution for generating values for such numeric columns. A sequence is a not primary key columns but can be used on any numeric column. SEQUENCES

29. CREATE SEQUENCE sequencename [INCREMENT BY n] [START WITH s] [MAXVALUE x NOMAXVALUE] [MINVALUE m NOMINVALUE] [CYCLE \NOCYCLE] [CACHE c\NOCACHE] [ORDER\NOORDER] syntax

30. Modify a sequence if you own it or have ALTER SEQUENCE . Modify sequence does not affect past numbers only generated in the future. Modification of a sequence does not allow you to change the starting value. The maximum value cannot be set to a number less than the current number. MODIFYING A SEQUENCE

31. ALTER SEQUENCE sequencename [INCREMENT BY n] [MAXVALUE x NOMAXVALUE] [MINVALUE m NOMINVALUE] [CYCLE\NOCYCLE] [CACHE c\NOCACHE] [ORDER\NOORDER]; syntax

32. You can drop a sequence with the DROP SEQUENCE statement. A removed sequence cannot be used anymore. DROPSEQUENCE major Majorid seq ; DROPPING A SEQUENCE

33. synonyms are oracle objects that are used to create alternative names for tables ,views , sequnces and other objects. you can create a synonyms for username .table name a synonym name must be different than all than the other objects owned by the user. A synonym can be removed by using the DROP SYNONYM statement. A user can get information about synonyms and their table names by using oracle data dictionary table , USER SYNONYMS SYNONYMS

34. An index is another oracle object that is used for faster retrieval of rows from a table. an index can be created explicitly by using the CREATE INDEX statement or implicitly by oracle. INDEX

35. An index based on a combined of column is called composite index or CONCATENATED INDEX. The general syntax is : CREATE INDEX index name ON tablename(columnname1,[columnname2],…); The TABLESPACE and STORAGE clause can be used with the CREATE INDEX statement.

36. When a table goes through many changes (insertions, delections,and updates), it is advisable to rebuild indexes based on that table. You can drop an index and recreate it, but it is faster to just rebuild an existing index. Rebuild compacts index data and improves performance. For example, ALTER INDEX student_ index REBUILD; REBUILDING AN INDEX

37. A user’s access needs to be controlled in a shared, multiuser oracle environment. A user’s access to the database can be restricted, and a user may or may not be allowed to use certain objects in the database. Security is classified into two types: SYSTEM SECURITY DATABASE SECURITY CONTROLLING ACCESSS

38. Defines access to the database at the system level. It is implemented by assigning a username and password, allocating disk space, and providing a user with the ability to perform system operations . SYSTEM SECURITY

39. Defines a user’s access to various objects' and the tasks a user can perform on them. The database administrator[DBA]is the most trusted user ,and a DBA is the most trusted user, and a DBA has all the privileges. A DBA can create users, assign them. Database security

40. An object privileges specifies what a user can do with a database object, such as a table, sequence, or a view. There are 11 basic object privileges, and each object has a set of privileges out of the total of 11 privileges. OBJECT PRIVILEGES