Oracle SQL: Projection

1. Introduction to SQL Projection

In SQL, projection means selecting specific columns from a table.

Instead of retrieving all columns, projection allows you to display only

the columns you need.

In SQL, projection refers to the process of choosing which columns

(attributes) you want to display from a table.

👉 Projection focuses only on columns, not rows.

👉 Projection is done using the SELECT statement.

Why is Projection Important?

Projection is important because:

1. Reduces unnecessary data

o You don’t always need all columns
o Improves performance
2. Improves readability
o Shows only relevant information
3. Used in real applications
o Example: A report might only need name and marks, not age
Basic Syntax

This query retrieves only the specified columns from the table.

Explanation:

 SELECT → tells Oracle what columns to retrieve

 column1, column2 → list of required columns
 FROM → specifies the table

2. Example Table

Assume we have a table called STUDENTS

STUDENT_ID NAME AGE COURSE MARKS

101 Ali 20 IT 85

102 Sara 21 CS 90

103 Omar 19 IT 78
 Types of Projection

1 Selecting All Columns

To retrieve all columns, use *.

Result:

STUDENT_ID NAME AGE COURSE MARKS

101 Ali 20 IT 85

102 Sara 21 CS 90

103 Omar 19 IT 78

Explanation:

 * means ALL columns

 Oracle retrieves every column in the table

When to Use It?

✔ Useful for:
  Testing queries
 Viewing full table

❌ Not recommended for:

 Real applications
 Large tables (performance issue)

2 Selecting Specific Columns

Projection usually means selecting only some columns.

Result:

NAME COURSE

Ali IT

Sara CS

Omar IT

Explanation:

 Only name and course are retrieved

 Other columns are ignored

Why This is Better

  Faster query execution
 Cleaner output
 Professional practice

3. Column Aliases
Aliases allow you to rename columns in the result.

What is an Alias?

An alias is a temporary name given to a column in the output.

Why Use Aliases?

1. Improve readability
2. Rename technical column names
3. Useful in reports

Syntax

Example:

Result:
 STUDENT_NAME SCORE
Ali 85
Sara 90
Omar 78

AS is optional.

 Aliases do NOT change the table structure

 Only affects the result

4. Removing Duplicate Values

Use DISTINCT to remove duplicate values.

Example:

Result:
 COURSE

IT

CS

Without DISTINCT:

COU
RSE
IT

CS

IT

What DISTINCT Does

 Removes duplicate values

 Shows only unique entries

Important Notes

 Works on entire row combination

 Can be applied to multiple columns:
5. Projection with Expressions
What is an Expression?

An expression is a calculation performed on columns.

Explanation

 Oracle calculates marks + 5

 Displays result as a new column

Important Concepts

1. Original data is NOT changed

2. Result is temporary
3. Can include:
o Addition (+)
o Subtraction (-)
o Multiplication (*)
o Division (/)

Projection can include calculations.

Example:

Result:
 NAME MARKS+5

Ali 90

Sara 95

Omar 83

Using alias:

6. Concatenation in Projection
Concatenation means combining multiple values into one column.

Combine columns using ||.

Real-World Use

 Generating messages
 Creating reports
 Formatting output

Example:
Result:

STUDENT_INFO

Ali studies IT

Sara studies CS

Omar studies IT

Summary
Projection in Oracle SQL means:

 Selecting specific columns from a table

 Using SELECT statement
 Removing duplicates with DISTINCT
 Renaming columns with ALIAS
 Using expressions and concatenation

✔ Key Commands

 SELECT
 DISTINCT
 AS
 ||
Class Activities

Activity Instructions
 Work individually or in pairs
 Write each query before running it
 Predict the output before execution
 Compare results with classmates
Tasks
1) Display all data from the employee table.

2) Display only employee name and salary.

3) Display name and department.

4) Display emp_id, name, and age.

5) Display name and salary, rename:

 name → employee_name
 salary → monthly_salary

6) Display department as dept_name.

7) Display unique departments only.

8) Display all departments.

8) Display name and salary + 500.

9) Display name and salary * 2, rename as double_salary.

10) Display: Ali works in IT

11) Display unique departments with alias dept.

Practical Demonstration (Live SQL Practice)

Step 1: Create Table

Step 2: Insert Data

Commit changes:

COMMIT;
Practical Queries for Students

Exercise 1

Display all columns.

Exercise 2

Display only name and marks.

SELECT name, marks

FROM students;

Exercise 3

Display course names without duplicates.

SELECT DISTINCT course

FROM students;

Exercise 4

Display name and marks + 10.

SELECT name, marks + 10 AS improved_marks

FROM students;

Exercise 5
Display student information in one column.

SELECT name || ' - ' || course AS student_details

FROM students;

Common Mistakes to Explain in Class

1. Forgetting FROM clause

Wrong

SELECT name, marks;

Correct

SELECT name, marks FROM students;

2. Misspelling column names

Wrong

SELECT names FROM students;

Correct

SELECT name FROM students;

Oracle SQL: Selection (Theory + Practical Lecture)

1. Introduction to SQL Selection

In SQL, selection means retrieving rows that satisfy a specific

condition.

Selection is performed using the WHERE clause.

While projection selects columns, selection selects rows.

Basic Syntax

The WHERE clause filters rows based on the specified condition.

2. Example Table

Assume the following STUDENTS table.

STUDENT_ID NAME AGE COURSE MARKS

101 Ali 20 IT 85
102 Sara 21 CS 90
103 Omar 19 IT 78
104 Lina 22 SE 88
105 Ahmed 20 CS 92
3. Selection Using Comparison Operators

These operators are used to filter rows.

Operator Meaning
= Equal
> Greater than
< Less than
>= Greater or equal
<= Less or equal
<> Not equal

Example 1: Marks Greater Than 85

Result:

NAME MARKS
Sara 90
Lina 88
Ahmed 92

Example 2: Students Age 20

4. Selection with Logical Operators

Logical operators combine conditions.

Operator Meaning
AND Both conditions must be
true
OR At least one condition
true
NOT Opposite condition

Example 3: Course IT AND Marks > 80

Result:

NAME COURSE MARKS

Ali IT 85
Example 4: Course CS OR Course IT

Example 5: NOT Operator

5. Selection Using BETWEEN

Used for selecting values within a range.

Syntax

Example

Students with marks between 80 and 90

6. Selection Using IN

Used when checking multiple values.

Example

This is equivalent to:

WHERE course='IT' OR course='CS'

7. Selection Using LIKE

Used for pattern matching.

Symbol Meaning
% Any number of characters
_ One character

Example 1: Names Starting with A

Result:

NAME
Ahmed

Example 2: Names Ending with a

Result:

NAME
Sara
Lina

8. Selection Using IS NULL

Used to check missing values.

Practical Demonstration

Step 1: Create Table

CREATE TABLE students (

student_id NUMBER,
name VARCHAR2(50),
age NUMBER,
course VARCHAR2(20),
marks NUMBER
);

Step 2: Insert Data

INSERT INTO students VALUES (101,'Ali',20,'IT',85);

INSERT INTO students VALUES (102,'Sara',21,'CS',90);
INSERT INTO students VALUES (103,'Omar',19,'IT',78);
INSERT INTO students VALUES (104,'Lina',22,'SE',88);
INSERT INTO students VALUES (105,'Ahmed',20,'CS',92);
COMMIT;

Practical Queries

Example 1

Display students with marks greater than 80.

SELECT *
FROM students
WHERE marks > 80;

Example 2

Display students in course IT.

SELECT *
FROM students
WHERE course = 'IT';

Example 3

Display students with marks between 80 and 90.

SELECT *
FROM students
WHERE marks BETWEEN 80 AND 90;

Example 4

Display students in CS or SE.

SELECT *
FROM students
WHERE course IN ('CS','SE');

Example 5

Display students whose names start with S.

SELECT *
FROM students
WHERE name LIKE 'S%';

Common Errors to Explain

Missing quotes for text values

Wrong:

WHERE course = IT

Correct:

WHERE course = 'IT'

Using = instead of LIKE for patterns

Wrong:

WHERE name = 'A%'

Correct:

WHERE name LIKE 'A%'

Summary

Selection in Oracle SQL means filtering rows using conditions.

Key elements:

 WHERE clause
 Comparison operators
 Logical operators
 BETWEEN
 IN
 LIKE
 IS NULL