Step by step learn SQL (SQL for Non Technical)

Structured Query Language is standard set of commands, provides the means for

     • Data Definition Language (DDL)
     • Data Manipulation Language (DML)

In Data Definition Language (DDL) we will cover.

     1. Create Table
     2. Create View
     3. Create Sequence

Create Table

Create Table table_name
(
Column1 datatype,
Column2 datatype,
...
);

CREATE TABLE table_name
AS select_statement;

Example1:

CREATE TABLE
Legacy_Vendors
(Vendor_Name VARCHAR2(50),
Vendor_Number VARCHAR2(10),
Address1 VARCHAR2(50),
Address2 VARCHAR2(50),
City VARCHAR2(25),
State VARCHAR2(2),
Zip_Code VARCHAR2(10)
);

Example2:

CREATE TABLE
Payroll_Code_Combinations
AS
SELECT
Segment1 as Company,
Segment2 as Division,
Segment3 as Account,
Segment4 as SubAccount
FROM
GL_Code_Combinations
WHERE
Segment3 LIKE ‘20%’;

Create View

CREATE OR REPLACE VIEW
view_name
AS
select_statement;

CREATE OR REPLACE VIEW
Payroll_Code_Combinations_V
AS
SELECT
Segment1 as Company,
Segment2 as Division,
Segment3 as Account,
Segment4 as SubAccount
FROM
GL_Code_Combinations
WHERE
Segment3 LIKE ‘20%’;

Create Sequence

CREATE SEQUENCE
sequence_name;
CREATE SEQUENCE
sequence_name
START WITH
starting_point
MAXVALUE
max_value
MINVALUE
min_value

Example:

Create sequence
tar_number
Start with 1
Maxvalue 9999999999999999
Minvalue 1;

In Data Manipulation Language (DML) we will cover.

     1. Select Statements
     2. Insert Statements
     3. Update Statements
     4. Delete Statements
     5. Data Conversion

Select Statements

SELECT
column1, column2, … column x
FROM
table, ...
view, …
WHERE condition
GROUP BY
group condition
ORDER BY
ordering criteria

Example:

SELECT
Segment1 ‘Company’,
Segment2 ‘Division’,
Segment3 ‘Account’,
Segment4 ‘Sub-Account’
FROM
GL_Code_Combinations
WHERE
Enabled_Flag = ‘N’
ORDER BY
Segment1, Segment2, Segment3, Segment4;

Data Types

     1. Character
     2. Numbers
     3. Dates
     4. Binary Objects
     5. File Pointers

Insert Statements
INSERT INTO table_name
VALUES
(list of values);
INSERT INTO table_name
(list of columns)
VALUES
(list of values);
INSERT INTO table_name kc.cmdassignedto
select_statement;
INSERT INTO table_name
(list of columns)
select_statement;

Example:

INSERT INTO FND_USER
(user_name, end_date)
VALUES
(‘WALSHJ’, SYSDATE + 365);
Update Statements
UPDATE table_name
SET column = value,
column = select_statement
column, column = value …
WHERE condition;

Example:

UPDATE
AP_Selected_Invoice_Checks
SET Vendor_Name = ‘Charles Keating’
WHERE Payment_Amount > 5000;
Delete Statement
DELETE FROM
table_name
WHERE
where_condition;

Example:

DELETE FROM FND_User
WHERE User_Name = ‘SHAHANI’;

Data Conversion

1. Character to Numbers
     • to_number(‘string’)
      Example:
       Select to_number(‘123’) from dual;
2. Character to Dates
     • to_date(‘string’, ‘format’)
      Example:
       Select to_date('01-jan-2015','dd/mm/yyyy') from dual;
3. Dates to Characters
     • to_char(date, ‘format’)
      Example:
       Select to_char(sysdate,'dd/mm/yyyy') from dual;
4. Numbers to Characters
     • to_char(number, ‘format’)
      Example:
       Select to_char(010115) from dual;

SQL versus SQL*Plus

SQL is a Standard
SQL*Plus extends standard SQL

     • Editing
     • DECODE
     • COLUMN
     • SET

PL/SQL

     • Procedural Language
     • Provides Structures
     • Sequence
     • Iteration
     • Alternation
     • Create Procedures, Functions, Packages
     • Store Code in Database

Database Objects

     • Tables
     • Views
     • Sequences
     • Procedures
     • Functions
     • Packages
     • Package Bodies

Dropping Objects

     • Drop Table table_name;
     • Drop View view_name;
     • Drop Sequence sequence_name;
     • Drop Package package_name;
     • Drop Procedure procedure_name;
     • Drop Function function_name;

Database Security

     • Privileged Users
     • SYS
     • SYSTEM
     • APPLSYS
     • APPS
     • Users with Critical Data
     • GL, AP, AR, HR, etc.

Database Security

     • Oracle Applications Tables Columns
     • Creation_Date
     • Created_By
     • Last_Update_Date
     • Last_Updated_By