How to Install Oracle Express Edition for SQL Practice

We have to practice the sql statement to become well versed with syntax, restrictions and expression. Oracle Express Edition for windows is a good environment to do practice on your laptop or Desktop . I am going to provide you the step by step installation guide for that.

Step by Step Oracle Express Edition Installation

  1.  Download the 18c Express edition from below link

https://www.oracle.com/database/technologies/xe-downloads.html

Choose the window one

If you are more interested  in 11g, the  go to below link

https://www.oracle.com/database/technologies/xe-prior-releases.html

Choose the window one as per your computer version

2.  This will download the setup.exe. Double click to start the installation

Click NEXT

3.In the License Agreement window, select I accept the terms in the license agreement and then click Next.

  1. In the Choose Destination Location window, either accept the default or click Browse to select a different installation directory. (Do not select a directory that has spaces in its name.) Then click Next.

5.  if you are prompted for a port number, then specify one.

The following port numbers are the default values:

  • 1521: Oracle database listener
  • 2030: Oracle Services for Microsoft Transaction Server
  • 8080: HTTP port for the Oracle Database XE graphical user interface

If these port numbers are not currently used, then the installation uses them automatically without prompting you. If they are in use, then you will be prompted to enter an available port number.

6.In the Specify Database Passwords window, enter and confirm the password to use for the SYS and SYSTEM database accounts. Then click Next.

Remember these password and write it somewhere

7.In the Summary window, review the installation settings, and if you are satisfied, click Install. Otherwise, click Back and modify the settings as necessary.

  1. Click Finish when prompted

This finishes your Oracle express edition installation

How to Stop /start the Oracle database

If the database is currently stopped, start it as follows: from the Start menu, select Programs (or All Programs), then Oracle Database 11g Express Edition, and then click Start Database.

If you want to stop, use the same procedure

 

How to connect To Oracle database

 

  1. Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.
  2. Type: connect
    Enter user-name: system
    Enter password: <password-for-system> : The same password which we supplied during installation
  3. You will get a prompt. Now you are connected as SYSTEM user.

 

How to create a new user in Oracle database

Now we can use create a  new user and start exploring the sql functionalities

create user test identified by <password-for-test>;

Grant the user the necessary privileges. For example:

SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
to test;

 How to Unlock the HR schema

1.Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.

  1. Connect as the SYSTEM user:

Type: connect

Enter user-name: system

Enter password: <password-for-system>

3.SQL> ALTER USER hr ACCOUNT UNLOCK;

  1. Enter a statement in the following form to specify the password that you want for the HR user:

SQL> ALTER USER hr IDENTIFIED BY <hr-password>;

 

How to connect using SQL Developer

We can connect to this database using sql developer also.

Check out below post for sql developer installation

Things you need to know about Oracle SQL developer

Once you have installed SQL developer, we can connect using sql developer

  1. First create the database connection

Connection Name: An alias for a connection to the database using the information that you enter. Example: xe_test

Username: Name of the database user for the connection. This user must have sufficient privileges to perform the tasks that you want to perform while connected to the database. Example: test

Password: Password associated with the specified database user.

Save Password: Specify this option if you want the password is saved with the connection information; you will not be prompted for the password on subsequent attempts to connect using this connection.

Hostname: Host system for the Oracle database. For your Express Edition database, the hostname is localhost.

Port: Listener port. The default is 1521.

SID: Database name. The default for Express Edition is xe

  1. Now test the connection

When the test is successful, click Save to save the connection.

  1. Now to open the connection after saving it, you can click Connect

Sample EMP table script

You can do creation of below tables to start the practice

CREATE TABLE "DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  )

CREATE TABLE "EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
         REFERENCES "DEPT" ("DEPTNO") ENABLE
);


  

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL>

SQL> desc  dept
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------------

 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL>


insert into DEPT  values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept  values(20, 'RESEARCH', 'DALLAS');
insert into dept  values(30, 'RESEARCH', 'DELHI');
insert into dept  values(40, 'RESEARCH', 'MUMBAI');


insert into emp   values(  7698, 'Blake', 'MANAGER', 7839,  to_date('1-5-2007','dd-mm-yyyy'),  2850, null, 10  );
insert into emp  values(  7782, 'Clark', 'MANAGER', 7839,  to_date('9-6-2008','dd-mm-yyyy'),  2450, null, 10  );
insert into emp  values(   7788, 'Scott', 'ANALYST', 7566,  to_date('9-6-2012','dd-mm-yyyy'),  3000, null, 20  );

insert into emp  values(   7789, 'TPM', 'ANALYST', 7566,  to_date('9-6-2017','dd-mm-yyyy'),  3000, null, null  );
insert into emp  values(   7560, 'T1OM', 'ANALYST', 7567,  to_date('9-7-2017','dd-mm-yyyy'),  4000, null, 20  );
insert into emp  values(   7790, 'TOM', 'ANALYST', 7567,  to_date('9-7-2017','dd-mm-yyyy'),  4000, null, null  );


SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------  ----------
      7698 BLAKE      MANAGER         7839 01-MAY-07       2850               10
      7782 CLARK      MANAGER         7839 09-JUN-08       2450               10
      7788 SCOTT      ANALYST         7566 09-JUN-12       3000               20
      7789 TPM        ANALYST         7566 09-JUN-17       3000
      7790 TOM        ANALYST         7567 09-JUL-17       4000
      7560 T1OM       ANALYST         7567 09-JUL-17       4000               20
        

drop table emp;

drop table dept;

Related Articles

Pre-Built Oracle database for learning and testing using Oracle Developer VM

Sql tutorial for Oracle ,MySQL , SQL server and Postgres

Leave a Reply