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
- Download the 18c Express edition from below link
Choose the window one
If you are more interested in 11g, the go to below link
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.
4. 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.
- 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
- Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.
- Type: connect
Enter user-name: system
Enter password: <password-for-system> : The same password which we supplied during installation - 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.
2. Connect as the SYSTEM user:
Type: connect
Enter user-name: system
Enter password: <password-for-system>
3.SQL> ALTER USER hr ACCOUNT UNLOCK;
4. 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 about oracle sql developer installation
Once you have installed SQL developer, we can connect using sql developer
- 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
2. Now test the connection
When the test is successful, click Save to save the connection.
3. 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
Oracle Sql Tutorials : Listing of all the Oracle Sql lesson to start learning Oracle Sql
