-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path09-Logical-Operators.sql
More file actions
148 lines (111 loc) · 4.78 KB
/
Copy path09-Logical-Operators.sql
File metadata and controls
148 lines (111 loc) · 4.78 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
/*
===============================================================================
Repository : 08-Oracle-SQL
Folder : Source-Code/09-Logical-Operators
File Name : 01-Logical-Operators.sql
Topic : Logical Operators
Description : Practical examples demonstrating the AND, OR, and combined
logical operators in Oracle SQL.
Author : Shaik Mahaboob Basha
License : MIT License
===============================================================================
*/
-- =============================================================================
-- NOTE
-- =============================================================================
-- This script assumes the following tables already exist:
--
-- 1. EMP
-- 2. J_GRADE
--
-- Execute the database setup script before running these examples.
-- =============================================================================
-- =============================================================================
-- SECTION 1 : AND Operator
-- =============================================================================
-- -----------------------------------------------------------------------------
-- Query 1
-- Display employee details whose salary is 12000 and job ID is ST_CLERK.
-- -----------------------------------------------------------------------------
PROMPT
PROMPT ============================================================
PROMPT Query 1 : Salary = 12000 AND Job ID = ST_CLERK
PROMPT ============================================================
SELECT *
FROM EMP
WHERE SALARY = 12000
AND JOB_ID = 'ST_CLERK';
-- -----------------------------------------------------------------------------
-- Query 2
-- Display employee details whose department ID is 22 and manager ID is 100.
-- -----------------------------------------------------------------------------
PROMPT
PROMPT ============================================================
PROMPT Query 2 : Department ID = 22 AND Manager ID = 100
PROMPT ============================================================
SELECT *
FROM EMP
WHERE DEPT_ID = 22
AND MANAGER_ID = 100;
-- =============================================================================
-- SECTION 2 : OR Operator
-- =============================================================================
-- -----------------------------------------------------------------------------
-- Query 3
-- Display grades where grade is 'A' or high salary is greater than 80000.
-- -----------------------------------------------------------------------------
PROMPT
PROMPT ============================================================
PROMPT Query 3 : Grade = 'A' OR High Salary > 80000
PROMPT ============================================================
SELECT GRADE
FROM J_GRADE
WHERE GRADE = 'A'
OR HIGH_SAL > 80000;
-- -----------------------------------------------------------------------------
-- Query 4
-- Display employee details whose job ID is ST_CLERK or salary is less than
-- 19000.
-- -----------------------------------------------------------------------------
PROMPT
PROMPT ============================================================
PROMPT Query 4 : ST_CLERK OR Salary < 19000
PROMPT ============================================================
SELECT *
FROM EMP
WHERE JOB_ID = 'ST_CLERK'
OR SALARY < 19000;
-- -----------------------------------------------------------------------------
-- Query 5
-- Display employee details who are President, Sales Representative,
-- or employees earning more than 35000.
-- -----------------------------------------------------------------------------
PROMPT
PROMPT ============================================================
PROMPT Query 5 : AD_PRES OR SA_REP OR Salary > 35000
PROMPT ============================================================
SELECT *
FROM EMP
WHERE JOB_ID = 'AD_PRES'
OR JOB_ID = 'SA_REP'
OR SALARY > 35000;
-- =============================================================================
-- SECTION 3 : Combination of AND and OR Operators
-- =============================================================================
-- -----------------------------------------------------------------------------
-- Query 6
-- Display employee details who are President or Sales Representative
-- and whose salary is greater than 25000.
-- -----------------------------------------------------------------------------
PROMPT
PROMPT ============================================================
PROMPT Query 6 : (AD_PRES OR SA_REP) AND Salary > 25000
PROMPT ============================================================
SELECT *
FROM EMP
WHERE (JOB_ID = 'AD_PRES'
OR JOB_ID = 'SA_REP')
AND SALARY > 25000;
-- =============================================================================
-- End of Script
-- =============================================================================