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';

SELECT Statement with 'IN' , 'LIKE' operators

SQL > Select * from emp where sal in 800;

SELECT statement with BETWEEN Operator

SQL> Select * from emp where sal between 100 and 3000;

2nd Example With WHERE Clause.

SQL> select * from emp where 1=1;

Explanation: 14 Rows are Selected and Displayed Because Condition 1=1 is True so all the rows are selected from emp table.

1st Example.

SQL> select * from emp;

First We have to Set the Page size and Line size to 100.

SQL > set linesize 100;

SQL> set pagesize 100;

If we are not Setting page and Linesize then Columns and Rows will not appear Properly.

Before Example for Page and Line Size.

After Page and linesize set to 100. (Below Screen Shot)

First We Need to Familiar with  SELECT Statement  before going into other Statements.

Remember:   SELECT is a Data Manipulation Langauage.

First, We Should Install Oracle 9i,10G or 11G in your System.

Remember: 9i (i means Internet) and 10g (g means GRID)

Second, Open SQLPLUS  and Enter Username:scott and Password: tiger.

 Your SQL PLUS is Now Ready to Type the Queries

 

What is SQL ?
structured query language, and pronounced either see-kwell or as separate letters. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation.