diseño de base de datos
si llevas con delira aqui esta el primer trabajo
- Obtener employee_id y el first_name de los empleados, en donde el nombre del empleado contenga el carácter “R” en cualquier posición
Select employee_id, first_name
From employees
From employees
Where upper(first_name)like ‘%r%’
EMPLOYEE_ID FIRST_NAME
----------- ------------
103 Alexander
104 Bruce
115 Alexander
119 Karen
126 Irene
129 Laura
137 Renske
141 Trenna
142 Curtis
143 Randall
144 Peter
EMPLOYEE_ID FIRST_NAME
----------- ------------
146 Karen
147 Alberto
148 Gerald
150 Peter
152 Peter
153 Christopher
155 Oliver
157 Patrick
161 Sarath
162 Clara
166 Sundar
EMPLOYEE_ID FIRST_NAME
----------- ------------
169 Harrison
170 Tayler
178 Kimberely
179 Charles
182 Martha
183 Girard
189 Jennifer
191 Randall
192 Sarah
193 Britney
200 Jennifer
EMPLOYEE_ID FIRST_NAME
----------- ------------
204 Hermann
34 rows selected.
- Obtener employee_id, first_name, job_id, department_id de los empleados ordenados por puesto y departamento
1 select employee_id, first_name, job_id, department_id
2 from employees
2 from employees
3* order by department_id, job_id
EMPLOYEE_ID FIRST_NAME JOB_ID DEPARTMENT_ID
----------- -------------------- ---------- -------------
111 Ismael FI_ACCOUNT 100
112 Jose Manuel FI_ACCOUNT 100
113 Luis FI_ACCOUNT 100
110 John FI_ACCOUNT 100
108 Nancy FI_MGR 100
206 William AC_ACCOUNT 110
205 Shelley AC_MGR 110
178 Kimberely SA_REP
107 rows selected.
3. Obtener employee_id, first_name, comisión_pct de los empleados que no tienen asignada un porcentaje de comisión
select first_name, employee_id, commission_p
2 from employees
2 from employees
3 where commission_pct is null;
FIRST_NAME EMPLOYEE_ID COMMISSION_PCT
-------------------- ----------- --------------
Michael 201
Pat 202
Susan 203
Hermann 204
Shelley 205
William 206
72 rows selected.
4. Obtener el first_name de los empleados que su nombre contenga una letra L en la segunda posición
Select first_name
from employees
from employees
where first_name like ‘_l%’;
LAST_NAME
-----------
Olson
Zlotkey
Olsen
Bloom
Fleaur
- Obtener un listado de los puestos ocupados en la organización (no repetición –Job-ID)
Select distinct jod_id
From employees;
JOB_ID
----------
AC_ACCOUNT
AC_MGR
AD_ASST
AD_PRES
AD_VP
FI_ACCOUNT
FI_MGR
HR_REP
IT_PROG
MK_MAN
MK_REP
JOB_ID
----------
PR_REP
PU_CLERK
PU_MAN
SA_MAN
SA_REP
SH_CLERK
ST_CLERK
ST_MAN
19 rows selected.
6. Obtener de los departamentos 10 y 60, el nombre y el puesto de los empleados
1 select first_name, job_id, department_id
2 from employees
3 where department_id in (10,60)
FIRST_NAME JOB_ID
-------------------- --------
Alexander IT_PROG
Bruce IT_PROG
David IT_PROG
Valli IT_PROG
Diana IT_PROG
Jennifer AD_ASST
6 rows selected.
7. Obtener los nombre de los vendedores que tienen un porcentaje de comisión mayor 0.3
SQL> select first_name, commission_pct
2 from employees
3 where commission_pct > .3;
FIRST_NAME COMMISSION_PCT
-------------------- --------------
John ,4
Janette ,35
Patrick ,35
Allan ,35
8. Obtener los nombre y sueldos de los empleados del departamento 50, ordenado descendentemente por sueldo
1 select first_name, salary, department_id
2 from employees
3 where department_id = 50
4 order by salary desc;
FIRST_NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Adam 8200 50
Matthew 8000 50
Payam 7900 50
Shanta 6500 50
Kevin 5800 50
Nandita 4200 50
Alexis 4100 50
Sarah 4000 50
Britney 3900 50
Kelly 3800 50
Renske 3600 50
FIRST_NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Jennifer 3600 50
Trenna 3500 50
Julia 3400 50
Laura 3300 50
Jason 3300 50
Julia 3200 50
Winston 3200 50
Samuel 3200 50
Stephen 3200 50
Curtis 3100 50
Alana 3100 50
FIRST_NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Jean 3100 50
Anthony 3000 50
Kevin 3000 50
Michael 2900 50
Timothy 2900 50
Mozhe 2800 50
Vance 2800 50
Girard 2800 50
Irene 2700 50
John 2700 50
Randall 2600 50
FIRST_NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
Donald 2600 50
Douglas 2600 50
James 2500 50
Joshua 2500 50
Randall 2500 50
Martha 2500 50
Peter 2500 50
James 2400 50
Ki 2400 50
Steven 2200 50
Hazel 2200 50
FIRST_NAME SALARY DEPARTMENT_ID
-------------------- ---------- -------------
TJ 2100 50
45 rows selected.
9. Obtener los datos de los empleados, donde el nombre del empleado contenga solo 4 caracteres de longitud
1 select first_name, employee_id
2 from employees
2 from employees
3* where upper(first_name) like '____';
SQL>
FIRST_NAME EMPLOYEE_ID
-------------------- -----------
John 110
Luis 113
Adam 121
John 139
John 145
Amit 167
Lisa 168
Jack 177
Jean 181
9 rows selected.
10. Obtener los nombres de los empleados de los administradores 148 y 149, ordenando el resultado por el nombre de los empleados
1 select first_name, manager_id
2 from employees
2 from employees
3 where manager_id in (148,149)
4* order by first_name;
4* order by first_name;
FIRST_NAME MANAGER_ID
-------------------- ----------
Alyssa 149
Charles 149
Elizabeth 148
Ellen 149
Harrison 148
Jack 149
Jonathon 149
Kimberely 149
Lisa 148
Sundita 148
Tayler 148
FIRST_NAME MANAGER_ID
-------------------- ----------
William 148
12 rows selected.
11. Obtener los datos de los empleados, donde el nombre del empleado contenga el carácter “L” en cualquier posición, el puesto sea SA_REP y su sueldo sea mayor a 9600
1 select employee_id, first_name,job_id,salary
2 from employees
3 where upper(first_name) like '%L%'
4 and job_id= 'SA_REP'
5* and salary > 9600
SQL> /
EMPLOYEE_ID FIRST_NAME JOB_ID SALARY
----------- -------------------- ---------- ----------
162 Clara SA_REP 10500
168 Lisa SA_REP 11500
174 Ellen SA_REP 11000
SQL>
12 Obtener los nombres de los empleados, los cuales el no. de empleado este en el rango de 170 y 177
SQL> select first_name, employee_id
2 from employees
3 where employee_id between 170 and 17
FIRST_NAME EMPLOYEE_ID
-------------------- -----------
Tayler 170
William 171
Elizabeth 172
Sundita 173
Ellen 174
Alyssa 175
Jonathon 176
Jack 177
8 rows selected.
13 Obtener los nombre de los empleados que no laboran en el departamento de 50
1 select first_name, department
2 from employees
3 where department_id
4* not in (50);
FIRST_NAME DEPARTMENT_ID
-------------------- -------------
Michael 20
Pat 20
Susan 40
Hermann 70
Shelley 110
William 110
61 rows selected.
14. Obtener los datos de los empleados que ocupan el puesto de SA_MAN, (no incluir a John).
1 select first_name, job_id
2 from employees
3 where first_name not like 'john'
4* and job_id = 'SA_MAN'
FIRST_NAME JOB_ID
-------------------- ----------
John SA_MAN
Karen SA_MAN
Alberto SA_MAN
Gerald SA_MAN
Eleni SA_MAN
SQL>
15. Obtener el nombre, sueldo y el sueldo con un aumento del 1% para los empleados que ocupan el puesto SA_REP y laboran en el departamento 80
1 select first_name, salary, salary * .01, job_id, department_id
2 from employees
3 where job_id = 'SA_REP'
4* and department_id = 80
FIRST_NAME SALARY SALARY*.01 JOB_ID DEPARTMENT_ID
-------------------- ---------- ---------- ---------- -------------
Peter 10000 100 SA_REP 80
David 9500 95 SA_REP 80
Peter 9000 90 SA_REP 80
Christopher 8000 80 SA_REP 80
Nanette 7500 75 SA_REP 80
Oliver 7000 70 SA_REP 80
Janette 10000 100 SA_REP 80
Patrick 9500 95 SA_REP 80
Allan 9000 90 SA_REP 80
Lindsey 8000 80 SA_REP 80
Louise 7500 75 SA_REP 80
FIRST_NAME SALARY SALARY*.01 JOB_ID DEPARTMENT_ID
-------------------- ---------- ---------- ---------- -------------
Sarath 7000 70 SA_REP 80
Clara 10500 105 SA_REP 80
Danielle 9500 95 SA_REP 80
Mattea 7200 72 SA_REP 80
David 6800 68 SA_REP 80
Sundar 6400 64 SA_REP 80
Amit 6200 62 SA_REP 80
Lisa 11500 115 SA_REP 80
Harrison 10000 100 SA_REP 80
Tayler 9600 96 SA_REP 80
William 7400 74 SA_REP 80
FIRST_NAME SALARY SALARY*.01 JOB_ID DEPARTMENT_ID
-------------------- ---------- ---------- ---------- -------------
Elizabeth 7300 73 SA_REP 80
Sundita 6100 61 SA_REP 80
Ellen 11000 110 SA_REP 80
Alyssa 8800 88 SA_REP 80
Jonathon 8600 86 SA_REP 80
Jack 8400 84 SA_REP 80
Charles 6200 62 SA_REP 80
29 rows selected.
16. Obtener la estructura de las siguientes tablas:
a. Department
b. Employees
b)
; desc employees;
Name Null? Type
----------------------------------------- -------- ----------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
a)
1* select departments
SQL> desc departments;
Name Null? Type
----------------------------------------- -------- --------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
17. Obtener los números y nombre de los empleados que no ocupan el puesto de SA_MAN, ordenar el resultado por nombre de empleado.
select first_name, employee_id, job_id
select first_name, employee_id, job_id
2 from employees
3 where job_id not like 'SA_MAN'
4 order by first_name;
FIRST_NAME EMPLOYEE_ID JOB_ID
-------------------- ----------- ----------
William 171 SA_REP
William 206 AC_ACCOUNT
Winston 180 SH_CLERK
102 rows selected.
18. Obtener los datos de los empleados que no son subordinados del administrador 146.
SQL> select first_name, employee_id, department_id, manager_id
2 from employees
3 where manager_id not like 146;
FIRST_NAME EMPLOYEE_ID DEPARTMENT_ID MANAGER_ID
-------------------- ----------- ------------- ----------
Vance 195 50 123
Alana 196 50 124
Kevin 197 50 124
Donald 198 50 124
Douglas 199 50 124
Jennifer 200 10 101
Michael 201 20 100
Pat 202 20 201
Susan 203 40 101
Hermann 204 70 101
Shelley 205 110 101
FIRST_NAME EMPLOYEE_ID DEPARTMENT_ID MANAGER_ID
-------------------- ----------- ------------- ----------
William 206 110 205
100 rows selected.
19. Obtener los datos
select first_name, department_id, salary, job_id
2 from employees
3 where salary > 2500
4 and department_id = 50;
FIRST_NAME DEPARTMENT_ID SALARY JOB_ID
-------------------- ------------- ---------- ----------
Matthew 50 8000 ST_MAN
Adam 50 8200 ST_MAN
Payam 50 7900 ST_MAN
Shanta 50 6500 ST_MAN
Kevin 50 5800 ST_MAN
Julia 50 3200 ST_CLERK
Irene 50 2700 ST_CLERK
Laura 50 3300 ST_CLERK
Mozhe 50 2800 ST_CLERK
Jason 50 3300 ST_CLERK
Michael 50 2900 ST_CLERK
FIRST_NAME DEPARTMENT_ID SALARY JOB_ID
-------------------- ------------- ---------- ----------
Renske 50 3600 ST_CLERK
Stephen 50 3200 ST_CLERK
John 50 2700 ST_CLERK
Trenna 50 3500 ST_CLERK
Curtis 50 3100 ST_CLERK
Randall 50 2600 ST_CLERK
Winston 50 3200 SH_CLERK
Jean 50 3100 SH_CLERK
Girard 50 2800 SH_CLERK
Nandita 50 4200 SH_CLERK
Alexis 50 4100 SH_CLERK
FIRST_NAME DEPARTMENT_ID SALARY JOB_ID
-------------------- ------------- ---------- ----------
Julia 50 3400 SH_CLERK
Anthony 50 3000 SH_CLERK
Kelly 50 3800 SH_CLERK
Jennifer 50 3600 SH_CLERK
Timothy 50 2900 SH_CLERK
Sarah 50 4000 SH_CLERK
Britney 50 3900 SH_CLERK
Samuel 50 3200 SH_CLERK
Vance 50 2800 SH_CLERK
Alana 50 3100 SH_CLERK
Kevin 50 3000 SH_CLERK
FIRST_NAME DEPARTMENT_ID SALARY JOB_ID
-------------------- ------------- ---------- ----------
Donald 50 2600 SH_CLERK
Douglas 50 2600 SH_CLERK
35 rows selected.
