Official Certification Track

Preparation video

OCA 11g Preparation 4

Give name of manager and salary of employee SUNIL.

SQL>select emp_company.salary,manager.mname
from emp_company,manager
where emp_company.ename = 'SUNIL'
and emp_company.ename = manager.ename;

List the employee living in city 'BOMBAY' and those having company located in city 'DELHI'

SQL>select ename from emp_company
where ename In
(select ename from employee where city='BOMBAY') and
cname In (select cname from company where city ='DELHI');

OCA 11g Preparation 3

Basic SQL Commands

List name of companies located in BOMBAY.
SQL>select cname from company where city =`BOMBAY';

list name of employees living in city `NAGPUR'
SQL>select ename from employees where city = `Nagpur';

List the names of employee who are not employee with the company 'ACC'.
SQL>select ename from emp_company where not (cname='ACC');
-OR-
SQL>select ename from emp_company where cname<>'ACC';

Suppose you want the names of employee from company 'ACC' or 'TATA'
SQL>select ename from emp_company where cname='ACC' or cname='TATA';

LAB:-
Create your own commands to find out the required data

OCA 11g Preparation 2

Create Tables and data or Install example database for Learning SQL Basics.

$sqlplus
Enter user-name: shinoj/shinoj

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> select * from tab; (List all the Tables)

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COMPANY TABLE
EMPLOYEE TABLE
EMP_COMPANY TABLE
EMP_SHIFT TABLE
MANAGER TABLE
SALES_DATA TABLE

6 rows selected.

Details of Each Tables.........

SQL> desc company;
Name Null? Type
----------------------------------------- -------- ----------------------------
CNAME VARCHAR2(30)
CITY VARCHAR2(30)

SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
CITY VARCHAR2(30)

SQL> desc emp_company;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
CNAME VARCHAR2(30)
SALARY NUMBER(7,2)
JDATE DATE

SQL> desc emp_shift;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
SHIFT CHAR(3)

SQL> desc manager;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
MNAME VARCHAR2(30)

SQL> desc sales_data;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODID NOT NULL NUMBER(6)
CUSTID NOT NULL NUMBER
TIMEID NOT NULL DATE
CHANNELID NOT NULL CHAR(1)
PROMOID NOT NULL NUMBER(6)
QUANTITY NOT NULL NUMBER(3)
AMOUNT NOT NULL NUMBER(10,2)


I have just imported two dumps to create the above tables and data
1. company.dmp
2. sales.dmp

Creating tables and inserting data manually will help to improve SQL commands..Hi hi

OCA 11g Preparation 1

Certification Path:-
OCA
SQL - 125$ - (1Z0-051)
Admin1 - 195$ - (1Z0-052)
============================

OCP
Admin2 - 195$ - (1Z0-053)
============================

We have to create an enviornment first to start Preparation.....

Operating System - Red Hat Enterprise Linux 5
Oracle - Oracle 11g Ver 11.2.0.1.0
-----------------------------------------------------------------------------
Create a User for all R&D

SQL>

create user shinoj identified by shinoj
Default tablespace users
Quota unlimited on users;

grant connect, resource,DBA to shinoj;

grant create role to shinoj;

SQL>

Start Enterprise Manager

$emctl start dbconsole

Now you can access enterprise Manager by

https://serverIP:1158/em