EA IT DATA DEN TRAINING        mail - eadatadenatenviroapps.com

In this information technology era,  data is very important. Even though we are in big data time, we still need streamlined relational databases (RDBMS) to perform secure, clean and authentic data analyses. Also it is very important to understand data through RDBMS and then further explore data in other data world. We have successfully utilized it in our many products and training. One such product is EAPHARMICS eSTAR - Electronic Stability Testing, Analysis and Reporting for Pharmaceutical industries. Now, assuming that you have a computer with satisfying system requirements for Oracle 11g XE and TOAD,

 


Step: 1 - Download Oracle11g Express Edition - It is free version, easy to install and learn RDBMS
(Click the Oracle link  under External links. - Right hand side)
                Follow installation instructions (It should be available under documentation tab).
Note: Install oraclexe in it's own directory such as C;/oraclexe,
Make sure you put in easy password that you can remember for SYSTEM user.
Also, you may want to unlock HR account, give a password and dba privileges.

Step. 2 - Download Tool for Oracle Development - TOAD - It is 30 day free trial.
(Click the TOAD  under External links. - Right hand side)
 Now Connect to HR schema from toad using the password. Once you connect you can do the following.
Note: Install TOAD in it's own directory such as C;/TOAD.
If toad does not work after trial period, ORACLE SQL DEVELOPER

Language used in Databases:
Structured Query Language
* DDL (Data Definition Language) - CREATE TABLE
* DML (Data Manipulation Language) - INSERT
* DCL (Data Control Language) - GRANT
* QUERY - SELECT

Step 3 - In databases data is in stored in tables. Tables have columns and data is stored in rows.
We need to know how to perform CRUD functionality to manipulate data in the tables.
C- Create Record, R - Retrieve, U - Update and D - Delete Rows.

Before we do the CRUD, we need to know Data Types.
Let us use preparing and serving TEA to explain data types.
Tea preparing starts by boiling water in a Kettle, once boiling water with tea leaves is done,
filtering is done and may be served in cups and glasses (for ICE TEA).
Here various utensils are used such as Kettle, Tea filter, Cups and glasses.
Different type of data types for holding data is similar to the utensils we used for tea preparing.
Let us see 3 fundamental and simple data types.

Character column defined as VARCHAR2(length of the field).
Number defined as NUMBER,
Date defined as DATE.

With this let us create our first TABLE (FAMILY) in oracle database after logging into HR schema using TOAD.
In the editor window type the following:

CREATE TABLE FAMILY ( DAD VARCHAR2(100),
                                               MOM VARCHAR2(100),
                                               CHILD VARCHAR2(100),
                                               CHILD_AGE NUMBER,
                                               EXAM_DATE  DATE);

Execute above statement and you will see Table Created. If you notice we have used all
3 data types - VARCHAR2, NUMBER and DATE.

In editor window you can do DESCRIBE FAMILY or a short form DESC FAMILY,
it will show the table with all columns and data types.
Now let us do the CRUD functionality on this table:

CREATE Record - how do we create a record - by using INSERT statement.
  
INSERT INTO family (
dad,
mom,
child,
child_age,
exam_date)
VALUES
('John',
 'Mary',
 'Francis',
 9,
 TO_DATE ('11/09/2015', 'MM/DD/YYYY'));

Execute the above statement and you will see 1 record inserted.
For permanently saving the record you have to do COMMIT and
to permanently delete record you need to do ROLLBACK. For now let us do COMMIT;
Now create your own insert statement using the above statement by changing VALUES.
Run it and commit. Now you will have 2 rows in the Family table.

RETRIEVE Record - how do we retrieve records - by using SELECT Statement.

SELECT
dad,
mom,
child,
child_age,
exam_date
FROM
family;

Execute above statement and two rows will be displayed. You can add one more clause
called WHERE clause to filter the specific data you want:

SELECT
dad,
mom,
child,
child_age,
exam_date
FROM
family
WHERE
dad = 'John';

Execute the above statement and you will get one row. You can also do

SELECT
*
FROM
family
WHERE
dad = 'John';  

This will also yield result. Symbol Star - '*' replaces the necessity to type in all column names
It selects all the columns from the table.

Update Record - how do we update records - by using UPDATE Statement.
Let us try to update the age of the child to 10 in the family table where dad is John.

UPDATE
family
SET
child_age = 10
WHERE
dad = 'John';

Execute this and COMMIT. That record where dad is John will be updated with child's age to 10.

Now, Final step of the CRUD functionalities - the DELETE.

how do we delete records - by using DELETE Statement.

DELETE FROM
family
WHERE
child_age = 10;

Execute and COMMIT, The row with dad - John will be deleted since that row had child_age as 10.

Hope you understood the CRUD functionalities - the fundamentals of any database.
Practice as many CRUD as you can. Error and Trial will make you expert in SQL.

Step 4 - Now, Click  the SQL/PLSQL BLOG link in the right hand to learn more SQL and PL/SQL step by step.
Learn alias of tables, views, joins ( JOIN CONDITION = Number of Tables Joined -1, So if you are joining 3 tables ( 3 -1 = 2) you would have 2 join conditions),
group by, having, decode and other functions to retrieve data. Also learn what is CASCADE, COMMIT, ROLLBACK, SAVEPOINT and
ORACLE BUILT IN FUNCTIONS
There are links on that blog on the right hand side for each topic in SQL and then PL/SQL.

Step 5 -  After learning SQL send us an email for 50 questions to answer. Workout these 50 questions
and let us know how fast you can write queries for  these 50 questions. Practice these questions again and again
you can reduce the time you take to complete meanwhile becoming a SQL Query Expert.
Learn what is views, synonyms and sequences, ROWID, ROWNUM, USERS AND PRIVILEGES and GRANT.

Step 6 - After learning PLSQL  Procedural Language - Structured Query Language -  CONTROL STRUCTURES (IF, WHILE, FOR LOOP), triggers, stored procedures - functions, procedures and packages email us to get Things to be remembered (It will help you prepare for interviews) and we will send you that information. Error handling (Exceptions, SHOW ERRORS), finding where the actual error happened, and fixing the problem in PL/SQL logic is where you get your money. Always create a table called ERROR_LOG and log that  table with more information. That way the problems can be efficiently caught and fixed easily.

Other areas, Programming Skills and experience to have as Software Programmer:
ER Diagram - Data base diagram representing Entity Relationships
Learn about normalizations and relationship between tables:
1 to one
1 to many
what is crow's foot?

More Databases and ETL:
MYSQL, MSSQL - Microsoft, Informatica Express, T-SQL, TERADATA, NOSQL, SQL LOADER
Learn about Date Warehouses

More Programming skills:
Create an account in code academy to learn - HTML, CSS, JAVASCRIPT, JQUERY, PHP, JAVA  (JDeveloper,  Eclipse tool) and other courses to improve your Skills.

Add skills by learning UNIX OR LINUX
Learn some basic unix commands such as
creating directory - mkdir directory_name
Move file - mv file_name
Copy file - cp file name
Listing file - ls -l
Giving privileges to file - chmod 777 file_name (read write and execute to user, group and others)
Delete file  - rm file_name

Learn about File Transfer Protocols - FTP and SFTP

If you have windows environment you can learn these commands by using online unix accounts
or downloading CYGWIN for practicing Unix commands.

SOFTWARE DEVELOPMENT LIFE CYCLE:
Design Phase - gathering requirement and designing
Perform Development - Coding and development - Follow Team Coding Standards
Unit Testing - Peer review
Quality Assurance - QA testing
User Acceptance Testing - Testing by end users and sign off
Production - Publishing and deploying

Source Control (Version Control):
Subversion
for windows environment and for learning subversion source control in windows environment use Tortoise subversion.
GIT
Git is a free and open source distributed version control system and is easy to learn.
Repository, Clone, Branch, Switch, Add, Commit, Pull, Fetch, Push and Merge are some key steps of GIT.
You can use TortoiseGit for a GUI environment. Tutorials are available.

Project Management:
MS Project,  - You can also google open source project management tools and learn
Learn how to Start a project until how to complete a project - sometimes UDEMY gives $10 to $15 PMP courses.
You can also buy Project Management for Dummies book.
Learn Agile, Scrum and development methodologies
.
BIG DATA:
Hadoop, HDFS, Map Reduce, HBase,  Hive, Pig, Avro, Mahout - Apache open sources.

Analysis of Data:
Four types of Analysis is usually demanded by the business
a) Descriptive Analysis:
    The world of reports - Analysis is mostly canned data and user will get the insights immediately with out further queries
b) Prescriptive:
    Come up with simulation, models etc. Learn user behavior, employ data optimizations and
    give the business prescriptions to boost the insights or business expectations
c) Predictive:
    The most challenging but most useful analysis - Come up with prediction about the business user from the current trends,
    current user engagement supported by real data that is collected - Recommendations, Linear graphs for future etc.
    Gaps identification etc are features of predictive analysis
d) Decisive Analysis: This is one level down before business and Business application(s) takes certain decision -
    For example: A dependent application continuously received the results of certain analysis and
    based on the analytic condition, it chooses to switch on a new service for a particular demography or
    not. Based on Certain conditions, business will be requested to expand the infrastructure well ahead in time
    before the critical condition is reached - Growth Vs Investment etc.

Analytics Result Data Preparation for Consumption
This is where data is fully structured and cataloged. Data Steward is responsible for Data Catalog.
A properly structured, well thought data will help the BI tools to visualize the insights in
graphs, visual widgets etc. Also, the structured data can be used to connect multiple
application that makes up the business.

Data Analysis Tools - Using R, Python
if more interested and focusing on only statistics - you can go and learn high level SAS or SPSS.

Business Intelligence:
Oracle BI, Qlik, Pentaho, Tableau,

Mobile and Web Development:
 ios, android, phonegap, AngularJS , Bootstrap

Virtual Environments - VMWARE

Cloud:
Oracle, Google, Amazon AWS,  Microsoft, Adobe

ORACLE APPLICATIONS, Oracle Apex, Database Administration (DBA), Siebel*, Peoplesoft* (* Oracle products) and SAP
are some of other areas to explore after you learn databases.

DATA SCIENCE:
With these expertise, if you brush up on probability, statistics and apply that to data to train your machines, preprocess, understand the logic of the data, apply to model and
get the output converting the data into TENSOR data - then you can enter the field of Data Science.

SOME REAL WORLD TIPS;
Always make a back up before making any changes especially in production environment.
When you are working locally, after considerable progress save your work in Network and if it is working code
check it to source control.
Slowly build in your linkedin profile also - which will help you in your professional career.


These are the fundamentals and you can shine in any domain or field. SKY is limit for your learning and you would be doing great if you focus and practice at least some of the above topics.
Good Luck for your IT Career.