Restriction and sorting [ WHERE clause ]

SELECT * 
FROM employees;

Number

SELECT * 
FROM   employees
WHERE  department_id = 90;

String:

SELECT * 
FROM   employees
WHERE  First_name = 'steven';

no date fount

*data is case sensitive in oracle

SELECT * 
FROM   employees
WHERE  First_name = 'Steven';

Date:

Date Format  :  DD-MON-YY     e.g. 14-JUN-12

SELECT first_name, Hire_date FROM employees
WHERE hire_date = '08-MAR-00';

Operators :

       =   ,    >  ,  >=   ,   <   ,    <=   ,   <>    (or)    !=       (or)      ^=
     
=

SELECT * FROM employees
WHERE salary = 24000;

>

SELECT * FROM employees
WHERE salary > 17000;

<

SELECT * FROM employees
WHERE salary >= 17000;


!=   [or]  < >  [or]  ^=


SELECT * FROM employees
WHERE first_name != 'Steven';

Other operators

      BETWEEN ...  AND  ....     
      NOT BETWEEN ...  AND  ....     
      IN  
      NOT IN  
      LIKE  
      NOT LIKE
      IS NULL 
      IS NOT NULL     

BETWEEN ...  AND  ....     


SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 10000;


NOT  BETWEEN ...  AND  ....


SELECT * FROM employees
WHERE salary not between 5000 and 10000;


IN


SELECT * FROM employees
WHERE salary IN (24000, 10000, 17000);


NOT IN


SELECT * FROM employees
WHERE salary NOT IN (24000, 10000, 17000);


LIKE


     Pattern matching operator

     % -> something or zero  
     _  -> position


Query to fetch all the first name which starts with S


SELECT * FROM employees
WHERE first_name LIKE 'S%';


Query to fetch all the first name which starts with S and end with n


SELECT * FROM employees
WHERE first_name LIKE 'S%n';


Query to fetch all the first name which contain u as second character


SELECT * FROM employees
WHERE first_name LIKE '_u%';


Query to fetch all the first name which contain u as second character and i as fourth character


SELECT * FROM employees
WHERE first_name LIKE '_u_i%';


Query to fetch all the mail_id which contain _ at third position 


SELECT * FROM employees
WHERE mail_id LIKE '__\_%' ESCAPE '\';

                                  [ or ]

SELECT * FROM employees
WHERE mail_id LIKE '__$_%' ESCAPE '$';


NULL


SELECT * FROM employees
WHERE commission_pct IS NULL;


IS NOT NULL


SELECT * FROM employees
WHERE commission_pct IS NOT NULL;


AND and OR logical operator

AND operator

SELECT * FROM employees
WHERE  First_name = 'Steven'  AND 
       Salary = 24000;


OR operator


SELECT * FROM employees
WHERE  First_name = 'Steven'  OR
       Salary = 24000;


AND and OR operator


SELECT * FROM employees
WHERE  (First_name = 'Steven' OR First_name='Lex') AND 
       Salary > 15000;

ASC and DESC

          Ascending      ASC     [ Default ]
          Descending    DESC


SELECT * FROM employees;

Sorting based on ASC

SELECT * FROM employees
ORDER BY department_id ASC;

Sorting based on DESC

SELECT * FROM employees
ORDER BY department_id DESC;

Sorting based on ASC (default)

SELECT * FROM employees
ORDER BY by department_id;

Sorting based on alias name

SELECT first_name, hire_date, salary as a
FROM employees
ORDER BY a DESC;

Sorting based on multiple columns

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, salary ASC;


Thanks for reading!!!!

Retrieving Data Using the SQL SELECT Statement

Selecting All Columns :

You can display all columns of data in a table by following the SELECT keyword with an asterisk (*). 

In the example in the slide, the department table contains four columns: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. 

The table contains seven rows, one for each department. 

SELECT * FROM departments;

You can also display all columns in the table by listing all thecolumns after the SELECT keyword. 

For example, the following SQL statement (like the example in the slide) displays all columns and all rows of the DEPARTMENTS table:

SELECT Department_id, Department_name, Manager_id, Location_id 

FROM departments;

Selecting Specific Columns :

SELECT Department_id, Department_name

FROM departments;

Writing Sql statement :

• SQL statements are not case sensitive.

     SELECT Employee_id, first_NAMe, salary, department_id 

     FROM empLOYEes;

• SQL statements can be on one or more lines.

     SELECT Employee_id, first_name, salary, department_id

     FROM employees;

                           [ OR ]

     SELECT 

     Employee_id    , 

     First_name     , 

     Salary         , 

     department_id

     FROM 

     employees;

• Keywords cannot be abbreviated or split across lines.

     SEL

     ECT 

     Employee_id    , 

     First_name..      

• Clauses are usually placed on separate lines.

             SELECT 

             Employee_id    , 

             First_name     , 

             Salary         , 

             department_id

             FROM 

             employees;

• Indents are used to enhance readability.

             SELECT 

                 Employee_id    , 

                 First_name     , 

                 Salary         , 

                 department_id

             FROM 

                 employees;

• In iSQL*Plus, SQL statements can optionally be terminated by a semicolon (;). Semicolons are required if you execute multiple SQL statements.

    Tool : iSQL Plus

     SELECT 

        Employee_id    , 

        First_name     , 

        Salary         , 

        department_id

     FROM 

        employees;         -- Here ; is a optional one

• In SQL*Plus, you are required to end each SQL statement with a semicolon (;).

    Tool : SQL Plus

     SELECT 

        Employee_id    , 

        First_name     , 

        Salary         , 

        department_id

     FROM 

        employees;

Arithmetic Expression :

    +  ,  -  ,   /  ,  *


SELECT First_name, salary , salary * 12 FROM employees;

sal = 1000

12*sal  =  12000

12*sal+100 = 12100     (Expected result is 13200)

Because * has more priority than +

12*(sal+100) = 13200   [ ( ) has more priority than * ]

Operator Priority :

BODMAS Rule

BO       D       M       A      S
 |           |          |         |        |_________     -
 |           |          |         |
 |           |          |         |______________    +

 |           |          |  
 |           |          |___________________    *
 |           |   

 |           |_________________________    /

 |
 |_______________________________  (  )



Defining Null Value:

•      Null is a value that is unavailable, unassigned, unknown, or inapplicable.

•      Null is not the same as zero or a blank space.

SELECT first_name, commission_pct

FROM employees;

Arithmetic expressions containing a null value evaluate to null.

SELECT first_name, commission_pct , commission_pct + 500

FROM employees;

\

Alias:

Renames a column heading

Is useful with calculation

SELECT first_name,  last_name,  salary, salary*12  FROM employees;

SELECT first_name,  last_name,  salary, salary*12 AS "Ann_salary"  FROM employees;

Immediately follows the column name (there can be a optional AS keyword between the column and Alias Name)

SELECT first_name,  last_name,  salary, salary*12 "Ann_salary"  FROM employees;

Requires double quotation marks if it contains spaces or special characters or if it is case sensitive

SELECT first_name,  last_name,  salary, salary*12 Ann_salary  FROM employees;

SELECT first_name,  last_name,  salary, salary*12 Ann salary  FROM employees;

ORA-00923: FROM keyword not found where expected

00923. 00000 -  "FROM keyword not found where expected"

*Cause:   

*Action:

Error at Line: 71 Column: 55

SELECT first_name,  last_name,  salary, salary*12 "Ann salary"  FROM employees;

Concatenation:

SELECT First_name || salary FROM employees;

SELECT First_name ||' '|| salary FROM employees;

SELECT First_name ||' salary is '|| salary FROM employees;

SELECT First_name ||''s salary is '|| salary FROM employees;

ORA-00923: FROM keyword not found where expected

00923. 00000 -  "FROM keyword not found where expected"

*Cause:   

*Action:

Error at Line: 87 Column: 25

--10g

SELECT First_name ||q'['s salary is ]'|| salary FROM employees;

Default DATE format in oracle:

      21-JAN-13  (If you want to change you can)

     

      DD-MON-YY

     

SELECT * FROM employees;

Distinct:

SELECT DISTINCT Department_id FROM employees;

SELECT department_id, department_name,  ? 

DESC [RIBE]:

DESCRIBE departments;

DESC employees;

Single Line comment:   

--

Multiple Line comment:

/*

   your comment 

*/

Example:

SELECT

   First_name,

   Last_name,

   --salary,

   hire_date,   /*  emp table

                    info  */

   department_id

FROM

   employees;

    Thanks For Reading!!!

Steps to Install Oracle 10g Express edition

Hi all,

            Please download the setup file from the following link

https://www.dropbox.com/s/suo5br29hze7e13/Oracle%2010g%20Express%20Edition.zip?dl=0

After download is complete:

Step 1: Run the setup file and continue the installation process.

Step 2: Before completing the installation it will ask you for a password

Example:

Password   :  sys

Re-enter password: sys

Step 3: Click next and finish the installation process.

Step 4: Click start button -> goto Oracle Database 10g express edition -> go to-> database home page

Step 5: It will open in your default browser

              User name  : system

              Password   : sys

Menu

Step 6: Go to Administration -> Database user -> select the user HR

1.       Provide the new password

2.       Change the status from locked to unlocked

3.       Check the dba option

4.       And click check all option at the bottom of the form.

5.       Then select Alter user tab at the top right of the form

Log out the session

Step 7: Now click the login button and type user name as HR and Password: (your password )

Choose sql -> sql commands.

Execute your query by using ctrl + Enter shortcut key  or use Run button at the right side top corner.

Thanks For reading!!

User Management in Oracle

Data Dictionary table which is used to view the list of users available in database.

SQL> SELECT username FROM dba_users;

USERNAME

------------------------------

SYS

SYSTEM

SCOTT

TSMSYS

4 rows selected.

Creating User

SQL> CREATE USER murugappan

  2  IDENTIFIED BY murugappan;

User created.

SQL> SELECT username FROM dba_users;

USERNAME

------------------------------

SYS

SYSTEM

MURUGAPPAN

SCOTT

TSMSYS

5 rows selected.

SQL> conn murugappan/murugappan

ERROR:

ORA-01045: user MURUGAPPAN lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

SQL>

Oracle System Privileges

System privileges allow the user to perform system level activities. This might include such things as being able to actually connect to the system, or it might include the ability to do things like create objects in schemas other than your own schema. In the next sections we will discuss the grant command, which you use to grant system privileges. Also the revoke command is used to revoke privileges from users.

Granting Oracle System Level Privileges

The grant command is used to grant system level privileges. System level privileges are those privileges that you need to actually do something on the system. For example you grant system level privileges to a user so that they can:

* Connect to the database (create session)

* Create objects (create table, create index)

* Perform DBA activities, like backup the database (SYSDBA, SYSOPER)

                                                  

                           

                         

SQL> conn / as sysdba;

Connected.

SQL> show user;

USER is "SYS"

SQL> GRANT create session, create table TO murugappan;

Grant succeeded.

SQL> conn murugappan/murugappan

Connected.

SQL> SHOW user;

USER is "MURUGAPPAN"

SQL> CREATE TABLE stud_rec ( sid NUMBER(3), sname VARCHAR2(30));

CREATE TABLE stud_rec ( sid NUMBER(3), sname VARCHAR2(30))

*

ERROR at line 1:

ORA-01950: no privileges on tablespace 'USERS'

Updating Password with Assigning Quota on Users tablespace

SQL> conn / as sysdba;

Connected.

SQL> ALTER USER murugappan

  2  IDENTIFIED BY murugan123   --Reset Password

  3  DEFAULT TABLESPACE users

  4  TEMPORARY TABLESPACE TEMP

  5  QUOTA 3M ON users;

User altered.

SQL> conn murugappan/murugan123

Connected.

SQL>  CREATE TABLE stud_rec ( sid NUMBER(3), sname VARCHAR2(30));

Table created.

SQL> DESC stud_rec;

 Name                                      Null?    Type

----------------------------------------- -------- -------------------

 SID                                                NUMBER(3)

 SNAME                                              VARCHAR2(30)

If you create any object automatically it will get created in the default tablespace that assigned to the particular user.

i.e. Above table stud_rec get created in the users tablespace.

If you want to create object in some other tablespace you have to use optional parameter TABLESPACE in the table creation code as follows.

To view list of tablespace available in the database

SQL> SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

Assigning Quota on SYSAUX tablespace.

SQL> conn / as sysdba

Connected.

SQL> ALTER USER murugappan

  2  QUOTA 3M ON SYSAUX;

User altered.

SQL> conn murugappan/murugan123

Connected.

SQL> CREATE TABLE log_info(emp_id NUMBER(3), total_hours NUMBER(2)) TABLESPACE SYSAUX;

Table created.

stud_rec table has been created in default tablespace (USERS) & log_info table has been create in SYSAUX tablespace.

To view all the information about the tables you can use USER_TABLES data dictionary.

SQL> SELECT table_name, Tablespace_name FROM user_tables;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

STUD_REC                       USERS

LOG_INFO                       SYSAUX

You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.

SQL> ALTER USER murugappan

  2  QUOTA UNLIMITED ON SYSAUX;

User altered.

To view quota assigned for all the tablespace and users:

SQL> SELECT tablespace_name, username, bytes, max_bytes, blocks

2   from dba_ts_quotas;

*If MAX_BYTES column value is -1 means that user have unlimited access on that particular tablespace.

Change Oracle passwords, expire, and lock unnecessary users

SQL> show user

USER is "MURUGAPPAN"

SQL> conn / as sysdba

Connected.

If you create or alter the user with PASSWORD EXPIRE option means it will allow the user to change their password.

  

SQL> ALTER USER murugappan

  2  IDENTIFIED BY new_pass

  3  PASSWORD EXPIRE;

User altered.

SQL> conn murugappan/new_pass

ERROR:

ORA-28001: the password has expired

Changing password for murugappan

New password: ora123

Retype new password: ora123

Password changed

Connected.

SQL> disc

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> conn murugappan/new_pass

ERROR:

ORA-01017: invalid username/password; logon denied

SQL> conn murugappan/ora123

Connected.

Lock unnecessary users

SQL> conn / as sysdba

Connected.

SQL> ALTER USER murugappan ACCOUNT LOCK;

User altered.

SQL> SELECT username, account_status FROM dba_users

  2  WHERE username = 'MURUGAPPAN';

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

MURUGAPPAN                     LOCKED

SQL> conn murugappan/ora123;

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba;

Connected.

SQL> ALTER USER murugappan ACCOUNT UNLOCK;

User altered.

SQL> SELECT username, account_status FROM dba_users

  2  WHERE username = 'MURUGAPPAN';

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

MURUGAPPAN                     OPEN

SQL> conn murugappan/ora123;

Connected.

Unlock the users with new password

SQL> ALTER USER murugappan ACCOUNT UNLOCK

   2 IDENTIFIED BY oracle123;

User altered.

Privileges

·         SYSTEM Privileges

·         OBJECT Privileges       

·        ROLE

System Privileges: (already discussed)

                        Create Session

                        Create Table

Object Privileges:

                        A schema object privilege is a privilege or right to perform a particular action on a specific schema object:

Table	Sequence	View
Procedure	Function	package

SQL> show user;

USER is "SYS"

SQL>

SQL> CREATE TABLE obj_priv ( col1  NUMBER(2));

Table created.

SQL> BEGIN

  2  insert into obj_priv values(1);

  3  insert into obj_priv values(2);

  4  insert into obj_priv values(3);

  5  end;

  6  /

PL/SQL procedure successfully completed.

SQL> select * from obj_priv;

      COL1

----------

         1

         2

         3

SQL> conn murugappan/ora123;

Connected.

SQL> select * from sys.obj_priv;

select * from sys.obj_priv

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn / as sysdba;

Connected.

SQL>  GRANT SELECT, INSERT, UPDATE ON obj_priv TO murugappan;

Grant succeeded.

SQL> conn murugappan/ora123;

Connected.

SQL> select * from sys.obj_priv;

      COL1

----------

         1

         2

         3

Role:

                       

 

SQL> conn / as sysdba

Connected.

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> create user u3 identified by u3;

User created.

SQL> GRANT create session, create table TO u1;

Grant succeeded.

SQL> CREATE ROLE basic_prive;

Role created.

SQL> GRANT create session, create table TO basic_prive;

Grant succeeded.

SQL> GRANT basic_prive TO u2,u3;

Grant succeeded.

Default role

·         DBA

·         connect

·         imp_full_database

·         exp_full_database

--Thanks for reading