Oracle SQL

Connecting to Oracle Database - SQL*Plus

To connect to Oracle database you need a user name and password.
• Connecting to Oracle Database from SQL*Plus
SQL*Plus is a client program from which you can access Oracle Database.
Display a Windows command prompt.

At the command prompt, type sqlplus and then press the key Enter.
At the user name prompt, type your user name and then press the key Enter.
At the password prompt, type your password and then press the key Enter.

The system connects you to an Oracle Database [Link] are in the SQL*Plus environment.
At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL
statements, and operating system commands.

To exit SQL*Plus, type exit and press the key Enter.

Connecting to Oracle Database – SQL Developer
• Connecting to Oracle Database from SQL Developer
SQL Developer is a client program with which you can access Oracle Database.
1. Start SQL Developer.
2. In the Connections frame, click the icon New Connection.
3. In the New/Select Database Connection window:
a. Type the appropriate values in the fields Connection Name, Username, and
Password. Near the Password field is the check box Save Password. By
default, it is deselected.
b. If the Oracle pane is not showing, click the tab Oracle.
c. In the Oracle pane, accept the default values.
(The default values are: Connection Type, Basic; Role, default, Hostname,
localhost; Port, 1521; SID option, selected; SID field, xe.)
Connecting to Oracle Database – SQL Developer
d. Click the button Test.
The connection is tested. If the connection succeeds, the Status indicator changes
from blank to Success.

e. If the test succeeded, click the button Connect.

The New/Select Database Connection window closes. The Connections frame
shows the connection whose name you entered in the Connection Name field in
step 3.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

 What is a schema
• A schema is a collection of logical database objects owned by a database user. It
is a logical container
• The schema has the same name as its owner, which is a specific database user
account.
• When a user account is created in Oracle, a corresponding schema with the
same name is automatically generated. The user "owns" all the objects within
their schema.
• A schema can contain database objects like tables, views, constraints,….
• Schema vs. User
• While the terms user and schema are often used interchangeably in an Oracle
context because they share the same name, they represent different concepts.
The user is the account used to connect to the database and manage privileges,
while the schema is the collection of objects owned by that user.
 Human Resources (HR) Schema
This is the schema that will be used in this course
 Databases Objects
A database object is any defined object in a database that is used to store or
reference data. Anything which we make from create command is known
as Database Object. It can be used to hold and manipulate the data. Some
of the examples of database objects are: Tables, view, sequence, indexes,
etc.
• Table - Basic unit of storage; composed rows and columns

• View - Logically represents subsets of data from one or more tables

• Sequence - Generates primary key values

• Index - Improves the performance of some queries

• Synonym - Alternative name for an object

 What is SQL
• Structured Query Language (SQL) is the standard language used to
interact with relational databases.
• It allows users to store, retrieve, update and manage data efficiently
through simple commands.
• It is known for its user-friendly syntax and powerful capabilities, SQL is
widely used across industries.
 How SQL works
• Input – The user sends a SQL query (like SELECT or INSERT).
• Parsing – The system checks if the query is written correctly.
• Optimization – It chooses the fastest way to run the query.
• Execution – The database runs the query.
• Output – The result or confirmation is sent back to the user.
 Oracle Datatypes
• Number Data Types:
number(p,s) variable length numeric data
float(p) : float data type (subtytpe of number
• Character Data Types:
char(size) fixed-length character data
varchar2(size) variable-length character data
• Date/Time Datatypes:
date : store valid date-time format with fixed length
timestamp: store valid date (year, month, day) with time (hour, minute, second)
• Large Object Datatypes (LOB types):
CLOB store large blocks of character data into Database.
BLOB store unstructured binary object
 What is NULL value
• NULL is a special data type that represents a missing or unknown value.
• It is not equivalent to zero or an empty string,
• In a database, if a column has no value, its value can be set as NULL, indicating
that the value of the column is missing or unknown.
• In some cases, it is important to distinguish between NULL values and empty
strings. For example, when handling customer information, if a customer’s
address is an empty string, it means the customer has no address, but if the
address column is NULL, it means the customer’s address is unknown or
missing.
• Unlike other data types, NULL cannot be compared, calculated, or concatenated
with values of other types.
 Describe commnad
• The DESCRIBE command in Oracle is used to display the structure of a database
object, such as a table, view, or synonym. It provides details about each column,
including the column name, data type, and whether null values are allowed.

• Can be used with DESC or DESCRIBE

Syntax DESC[RIBE] schema_name.object_name
Example:
desc [Link]
 Rules for Writing SQL statements
• SQL statements are not case-sensitive
• Can be separated in multiple lines
• Keywords cannot be abbreviated or split
• Each SQL statement is terminated by a semicolon “;”
• In sqldeveloper it can also be terminated by a forward slash sign “/”
• Avoid using SQL keywords as names. If needed, wrap them in quotes (" ")
or backticks (`).

• Comments
• Single-line: -- comment
• Multi-line: /* comment */
 Basic SQL SELECT Statement
The SQL SELECT statement is used to retrieve data from one or more tables
and display it in a structured format of rows and columns.

1- Retrieve all the data from the table

SELECT * FROM table_name;

* means all the columns

table_name: name of the table you're querying.

Example: select * from employees ; retrieve all the data from employees
 Basic SQL SELECT Statement

2- Retrieve specific columns from the table

SELECT column1, column2, ... FROM table_name;
column1, column2: columns you want to retrieve.
table_name: name of the table you're querying.

Example: select first_name,last_name from employees ;

retrieve first_name and last_name from employees
 Using column aliases
• A column alias is a temporary, alternate name for a column.
• Aliases are specified in the SELECT clause to name or rename columns so that the result
table is clearer or easier to read.
• Alias Syntax for columns:
SELECT column_name AS alias_name
FROM table_name;
Example:
select FIRST_NAME as FN, LAST_NAME as LN
from EMPLOYEE;
Aliases are often used to name a column that is the result of an arithmetic expression or
summary function.
Example:
SELECT salary * 1.2 AS "Proposed Salary"
FROM employees;
 Operations on columns:
Arithmetic operators

• Arithmetic operators in SQL are used to perform mathematical operations on table

data. These operators help in calculating totals, differences, percentages, and other
numeric transformations directly in queries.
• These operations can be applied to:
• A single column
• Two or more columns
• Constant values with column data

SELECT employee_id,salary,salary*12 as “Annual Salary”

FROM employees;
 Arithmetic expressions and NULL values
• Any arithmetic expression that contains a NULL value will evaluate to NULL.
• This is due to the fact that a calculation’s outcome is also unknown if one of its
components is unknown.

SELECT 3 * NULL + 5, 'Hello ' || NULL || 'world' FROM DUAL;

SELECT last_name, salary, commission_pct ,

salary * commission_pct
FROM employees
 Operations on columns:
Concatenation

• Concatenation joins two or more strings together into a single string.

• The string concatenation operator is: || (two vertical bars)
• Using alias increase readability
• Concatening with NULL value does not return NULL, it returns the other character
strings

SELECT first_name || ' ' || last_name AS “FULL_NAME”

FROM employees
 DISTINCT operator
• The SQL DISTINCT keyword is used to retrieve unique values from a specified
column or set of columns in a database table.
• It is used when a column contains many duplicate values, and you only want to
list the different (distinct) values.

• The basic syntax of the SQL DISTINCT keyword is as follows:

SELECT DISTINCT column1, column2,.....columnN
FROM table_name;

• DISTINCT must be placed immediately after the SELECT keyword.

• You specify the columns for which you want unique results.
 DISTINCT operator
• Using DISTINCT on a single column:
The DISTINCT clause is used on a single column to retrieve all unique
values in that column
• Example
SELECT DISTINCT department
FROM employees

• Using DISTINCT on multiple columns :

The DISTINCT clause is used on multiple columns to retrieve all unique
combinations of values across those columns
• Example
SELECT DISTINCT customername, city, product
FROM orders
 The DUAL table
• DUAL is a table automatically created by Oracle Database along with the data
dictionary.
• DUAL is in the schema of the user SYS but is accessible by the name DUAL to all
users.
• It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row
with a value X.
• The DUAL table is used primarily to execute SELECT statements that do not
need to retrieve data from an actual database table.
• The main reason for the DUAL table is a syntactical requirement in Oracle SQL
that all SELECT statements must have a FROM clause.
 Using DUAL table - examples
• Evaluating Expressions: Perform mathematical calculations or string operations.

SELECT 5 * 3 FROM DUAL;

SELECT 'Hello' || ' World' FROM DUAL;

• Calling System Functions and Pseudocolumns: Retrieve system-generated

values like the current date, time, or user information.

SELECT SYSDATE FROM DUAL;

SELECT USER FROM DUAL;