DISPLAY ALL TABLES IN ORACLE

SQL> Select * from tab;

Oracle 11g – How to login "scott/tiger" accounts after installment

After I have successfully installed the Oracle 11g, I wanted to use SQL*plus. However, when I tried to connect database as  login name: sys, I found the account is locked.  Later I tried to login with "system" account, then alter and active scott/tiger. Here are steps for these actions.

1. SQL> conn user/password;

SQL> conn sys/private password;

Error message is displayed: the account is locked.

2. Try to login as "system"   - pass

SQL> conn  system/password;

3. Unlock "scott"

SQL> alter user scott account unlock;

4. Can change "tiger":

SQL> alter user scott identified by tiger;

5. SQL> conn scott/tiger;    -Success

SQL ORDER BY

The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts query results in ascending order by default.

SQL> Select ename,sal, from emp where sal > 100 order by ename desc; (DESCENDING ORDER)
 

SQL> Select ename,sal from emp where sal > 100 order by ename (BY DEFAULT ASCENDING ORDER)

 SQL> Select ename,sal,comm from emp order by 1;
            (In the Above Query ORDER BY 1 is Taking Column Name from Select Option)
             Ename-> Column 1.
             Sal-> Column 2.

SELECT STATEMENT WITH DIFFERENT EXAMPLES

SQL> Select * from emp where sal > (1-1);

SQL Comparison Operator With Examples ( >,<,=)

SQL > select * from emp where sal! = 3000;

 
SQL > select * from emp where sal  = 3000;

 SQL > select * from emp where Sal < 2000;

Using 'ALIAS' in SQL

SQL > Select e.ename from emp e; (This Alias for Tables in SQL)

 SQL > Select ename as Name from emp; (This Alias for Columns in Tables)

SELECT Statement with LIKE Operator

SQL > Select * from emp where sal like 800;

SQL > Select * from emp where ename like 'SMITH';