Joining Multiple Tables in SQL: Best Practices and Techniques

SQL JOINS :

•  Sometimes we have to combine the data from so many tables.
•  A join combines the data spread across tables.
•  A join is performed by the 'where' clause which combines the specified rows of tables.
•  A common column provides the join condition.

Types of joins :

 There are three types of joins. They are

  1. Simple join or inner join

  2. Self join

  3. Outer Join

1. Inner join

•   The most important and frequently used of the joins is the inner join.
•   They are also referred to as EQUIJOIN.
•   Inner join retrieve only the 'MATCHED RECORDS'.

Example :

  select e.employee_id,e.first_name,e.department_name,d.department_id

  from employees e, departments d

  where e.department_id = d.department_id;

  

 Non-equijoin

•   A join that is based on relational operators other than = is called a non-Equi join.

Example :

  select a.item_id,item_name,stock,quantity

  from item_master a,sales_details b

  where(a.item_id=b.item_id)

  and (a.stock<b.quantity);

  This statement lists all items for which the stock in hand is less than the quantity ordered.

2. Outer Join

•    The outer join extends the result of a simple join.
•    An outer join returns all the rows from one table that do not match any row from the other table.
•    The symbol + is used to represent the outer join.

 Left Outer Join

•    All the records from the left side table and only the matched rows join from the right side table.

 Example :

   select e.employee_id,e.first_name,e.department_name,d.department_id

   from employees e, departments d

   where e.department_id = d.department_id(+);

 Right Outer Join

•    All the records are from the right side table and only the matched rows left side table.

 Example :

   select e.employee_id,e.first_name,e.department_name,d.department_id

   from employees e, departments d

   where e.department_id(+) = d.department_id;

3. Self Join 

•   Self-join is a regular join, but the table is joined with itself.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

The Complete Guide to Oracle Application R12 VIEWS

Database Objects in Oracle :

1. TABLE:  Used to store information & allows manipulation, retrieval & share of information.

 ( user_tables, user_tab_columns ,TAB )

2. VIEWS & SYNONYMS: Used to manipulate, retrieve &  share information. They will not hold data.

 ( user_views, user_synonyms, CAT )

3. SEQUENCES: Used to generate the numbers automatically. 

( user_sequences )

4. INDEX & CLUSTER: Used to improve the performance of Oracle while retrieving or manipulating data.

 ( user_indexes , user_clusters )

Views: 

 It is a stored select statement.

 It is a virtual or Logical component.

 It allows Desc, DML, and Select on it.

 It will not hold data.

 It is stored permanently in the "User_views" system table.

 It can be shared with other users.

 DML on view are reflected in Table and DML on   Table are reflected in the view.

 * It is used to share "Selected Rows and Columns"   with other users. It is used for reporting purposes.

 *It will Improve performance while manipulating or retrieving data from the Views.

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Mastering RDBMS: Understanding the Types of Languages

RDBMS:

• RDBMS is the abbreviation for ‘Relational DataBase Management System’.

      Any database system consists of two parts. They are
         a) Database Management system
         b) Database Application

• The Database management system is required to organize and maintain the data.
• The Database Application is a program to retrieve, insert and update the data in the system.

SERVICES OF DBMS
      A DBMS provides the following services to do the activities mentioned previously. Let us see some of the services.
   1) Data Definition
           This is a method of defining data and its storage.
   2) Data Maintenance
          This method verifies whether each record has fields containing all information as defined using data definition.
   3) Data Manipulation
          Data Manipulation allows to insert, update, delete and sorting of data in the database.
   4) Data Display
         Data Display allows viewing the data in the database.
  5) Data Integrity
        This method verifies the integrity or accuracy of data stored in the database.

DATA TYPES

•  Data are stored in a table which is a collection of fields. Oracle supports the following data types.

A. Character data types
   1. CHAR
       Char data type is used when a fixed-length character string is required. Up to 2000 bytes can be stored in this data type. The default value is one byte.
    Syntax
        CHAR (size)
    Example: CHAR (20)

  2. VARCHAR2
       This data type is used to store variable-length character strings. It can also store alphanumerical values. The size of these data types varies from 1 to 4000 bytes.
Syntax
     VARCHAR2 (size)

 3. LONG
      Cells of this data type can store variable character strings up to 2GB. Binary data in ASCII format can also be used in such data types. Based on the memory space available on the computer, its length would be restricted.
NOTE:
   a) Only one column in a table can have this data type.
   b) This column should not contain a unique or primary key constraint.
   c) This column can’t be indexed
   d) This data type can’t be used as an argument in any procedure.

B. Number Data types
  1. NUMBER
       A number data type is used to declare a column to store only numbers. This type supports the storage of integers, real numbers, floating-point numbers, etc.
Example:
      Salary Number
      Age Number

C. Date Data Type
        All types of time-related information, including date and associated time, can be stored in a column declared with the date data type.
Syntax
        DATE
Example:
        Date_of_birth DATE

DATA DEFINITION LANGUAGE
      The Data Definition Language is used for the following purposes.
         1. To create an object
         2. To alter the structure of an object
         3. To drop an object
Note: Some of the database objects are Table, View, Sequence, Cursor, etc.

DDL used for a table
       The DDL used for table definition is classified as
        1. Create table command
        2. Alter table command
       3. Truncate table command
       4. Drop table command

1. CREATING A TABLE
Syntax
        CREATE TABLE <table name> (column1 datatype1,…, column data type n);

Example
         CREATE TABLE STUDENT_MASTER (Roll_No NUMBER (4), Name VARCHAR2 (15), Sex VARCHAR2 (6), DOB DATE, DOAD DATE, Class VARCHAR2 (6), Guardian VARCHAR2 (30), Address VARCHAR2 (100));

      If the above command is executed successfully, the message “table created” is displayed.

Important points:
     a) The first letter should be an alphabet
     b) Oracle reserve words cannot be used as table name
     c) The length of a table should not exceed 30 characters
    d) Different tables should have different names.
    e) Underscore, numerals and letters are allowed. Blank space and single quotes are not allowed.
    f) Double quotes can be used for naming a table.

2. ALTERING A TABLE
     The structure of a table can be changed using this command.
   a) Add a new column
   b) Change the width of a data type
   c) Change the data type of a column
   d) Include an integrity constraint
   e) Drop an integrity constraint

Syntax
    ALTER TABLE <table name>MODIFY (column datatype,…);
    ALTER TABLE <table name>ADD (column datatype,….);

Restrictions in altering a table
      The following are the major restrictions in altering a table
a) Cannot change the name of a column
b) Cannot drop a column from the table
c) Cannot change the name of a table
d) Cannot decrease the size of a column if the table is non-empty.
e) Cannot change the data type if the column contains data that conflicts with the data type.
f) Cannot add constraints if the data in the table violates the constraint.

Example:
     ALTER TABLE student_master MODIFY (name VARCHAR2 (25));
     ALTER TABLE student_master ADD (tel_no NUMBER(10));

3. TRUNCATING A TABLE
Syntax
     TRUNCATE TABLE < table_name>;

This command is used to delete all records stored in a table. If we use this command, the structure of the table is retained.

Example:
       TRUNCATE TABLE student_master;

4. DROPPING A TABLE
   This command is used to remove a table from the database.
Syntax
    DROP TABLE <table name>;

Note:
   We can’t drop a table if it is referenced by an existing table through a foreign key.

Data Manipulation Language (DML)
        Data manipulation languages are used in the query and to manipulate the existing objects in the database like tables. The DML commands are
      1. Insert command
      2. Select command
      3. Update command
      4. Delete command

1. Insert command
   The insert command is used to add one or more rows to a table.

Syntax
     INSERT INTO <table name> VALUES (list of values);

Note:
  The values are separated by commas
  Values of data type char, varchar2, raw, long and date must be enclosed in single quotes.
 The values must be entered in the same order as they appear in the table.

a) Inserting a row by entering all values for the columns defined in a table
Syntax
      INSERT INTO <table name> VALUES (value1,value2,…);
Example:
      INSERT INTO student_master VALUES (1001,’ R.BALU’,’ MALE’,’01-JAN-00’,
       ’11-JAN-03’,’CIVIL’, ’M.RAGU’,’28, Main Road, Nagercoil’);

b) Inserting values only for some particular columns
Syntax
     INSERT INTO <table name> (column1, column2…) VALUES (value1, value2,..);

Example:
     INSERT INTO student_master (roll_no, name) VALUES (2001,’ J.ARUN’);

c) Entering a large number of rows
     INSERT INTO staff_master
     VALUES (&staff_id,’&subject’,’&name’,’&doap’,’&street’,’&city’,’&pin’);

2. Select command
    The select command is used to retrieve the stored data from a table.
Syntax
    SELECT * FROM <table_name>;
                    (or)
    SELECT column1,column2,.. FROM <table_name>;

Selecting distinct rows
     To avoid the selection of duplicate rows, the distinct clause is used in the select statement.
Syntax
      SELECT DISTINCT field_name FROM <table_name>;

Selecting specific rows
    To select specific rows from a table, where clause is used in the select statement after the from clause.

Syntax
     SELECT column1,column2,.. FROM <table_name> WHERE <condition> ORDER BY column DESC

3. Update command
    The update command is used to update (changing values in one or two columns of a row) rows in a table. Specific rows can also be updated based on some conditions.
Syntax
      UPDATE <table_name> SET column1=expression,
      Column2=expression…WHERE <search_condition>

4. Delete command
     The delete command is used to delete rows from a table.
Syntax
     DELETE FROM <table_name> WHERE <searchcondition>;

Deletion of the specific row
     DELETE FROM staff_master WHERE name=’E. Suriya’;

Deletion of all rows
     DELETE FROM staff_master;

Transaction Control Language (TCL)
         All changes made to the database are defined as a transaction. It is a logical unit of work. The transaction can be made permanent in a database only if they are committed.
         The two transaction control commands are
             1. Commit
             2. Rollback

1. Commit
   The commit command is used to make all transaction changes permanent to the database.
   This command erases all savepoints and releases the transaction lock.
Syntax
    Commit;

2. Rollback
     The rollback command is used to undo all the changes made in the current transaction. We can either roll back the entire transaction or roll back a transaction to a savepoint.
Syntax
    Rollback;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Query to Find On Hand Quantity in Oracle Apps R12

SELECT  segment1,
  it.inventory_item_id,
  it.organization_id,
  transaction_quantity,
  internal_ORDER_FLAG
FROM mtl_system_items_b it,--MATERIAL TABLE
            mtl_onhand_quantities onhand -- ONHAND QTY TABLE
WHERE it.inventory_item_id  = onhand.inventory_item_id
AND it.organization_id      = onhand.organization_id
AND it.organization_id      = 4
AND IT.INVENTORY_ITEM_ID= 28934;

OUTPUT :
SEGMENT1            ITEM_ID     ORG_ID    TRANSACTION QTY    INTERNAL_ORDER_FLAG
M0800RCSM004 28934            4                     42                                   Y
M0800RCSM004 28934            4                     90                                   Y

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

Query to Find Pricing Table List in Oracle Apps R12

SELECT    qlhb.list_header_id,qlhb.list_type_code,qlht.name,qlht.description

FROM qp_list_headers_b qlhb,
             qp_list_headers_tl qlht
WHERE qlhb.list_header_id = qlht.list_header_id
 AND qlht.name = 'COMM_CONT';

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.