OracleSQL_SZIC/Practice 02.sql at master · SyedMuhammadArsalanShah/OracleSQL_SZIC · GitHub

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
-- Question No 01
-- Answer is
select
    last_name,
    salary
FROM
    hr.employees
where
    salary > 12000;

-- Question No 02
-- Answer is
select
    last_name,
    department_id
FROM
    hr.employees
where
    employee_id = 176;

-- Question No 03
-- Answer is
SELECT
    last_name,
    salary
FROM
    hr.employees
WHERE
    salary NOT BETWEEN 5000
    AND 12000;

-- Question No 04
-- Answer is
SELECT
    last_name,
    job_id,
    hire_date
FROM
    hr.employees
WHERE
    hire_date BETWEEN '20-Feb-1998'
    AND '01-May-1998'
ORDER BY
    hire_date;

-- Question No 05
-- Answer is
SELECT
    last_name,
    department_id
FROM
    hr.employees
WHERE
    department_id IN (20, 50)
ORDER BY
    last_name;

-- Question No 06
-- Answer is
SELECT
    last_name,
    salary,
    department_id
FROM
    hr.employees
WHERE
    salary between 5000
    and 12000
    and department_id IN (20, 50);

-- Question No 07
-- Answer is
SELECT
    last_name,
    hire_date
FROM
    hr.employees
WHERE
    hire_date LIKE '%94';

-- Question No 08
-- Answer is
SELECT
    last_name,
    job_id
FROM
    hr.employees
WHERE
    manager_id is null;

-- Question No 09
-- Answer is
SELECT
    last_name,
    salary,
    COMMISSION_PCT
FROM
    hr.employees
WHERE
    COMMISSION_PCT is not null
ORDER BY
    salary desc,
    COMMISSION_PCT desc;

-- Question No 10
-- Answer is
SELECT
    last_name
FROM
    hr.employees
WHERE
    last_name LIKE '__a%';

-- Question No 11
-- Answer is
SELECT
    last_name
FROM
    hr.employees
WHERE
    last_name LIKE '%a%'
    and last_name LIKE '%e%';

-- Question No 12
-- Answer is
SELECT
    last_name,
    job_id,
    salary
FROM
    hr.employees
WHERE
    job_id IN ('SA_REP', 'ST_CLERK')
    AND salary NOT IN (2500, 3500, 7000);

-- Question No 13
-- Answer is
SELECT
    last_name as "Employee",
    salary as "Monthly Salary",
    COMMISSION_PCT
FROM
    hr.employees
WHERE
    COMMISSION_PCT =.2;