Lunes, Sql Server - rextester

Lunes

create table emple(
   emp_no INTEGER PRIMARY KEY,
  apellido VARCHAR(50) NOT NULL,
  oficio VARCHAR(30),
  dir INTEGER, 
  fecha_alt DATE, 
  salario INTEGER,
  comision INTEGER,
  dept_no INTEGER
);

INSERT INTO emple VALUES (7369,'SÁNCHEZ', 'EMPLEADO', 7902, '1990/12/17', 1040, NULL, 20);
INSERT INTO emple VALUES (7499,'ARROYO', 'VENDEDOR', 7698, '1990/02/20', 1500, 390, 30);
INSERT INTO emple VALUES (7521,'SALA', 'VENDEDOR', 7698, '1991/02/22', 1625, 650, 30);
INSERT INTO emple VALUES (7566,'JIMÉNEZ', 'DIRECTOR', 7839, '1991/04/02', 2900, NULL, 20);
INSERT INTO emple VALUES (7654,'MARTÍN', 'VENDEDOR', 7698, '1991/09/29', 1600, 1020, 30);
INSERT INTO emple VALUES (7698,'NEGRO', 'DIRECTOR', 7839, '1991/05/01', 3005, NULL, 30);
INSERT INTO emple VALUES (7782,'CEREZO', 'DIRECTOR', 7839, '1991/06/09', 2885, NULL, 10);
INSERT INTO emple VALUES (7788,'GIL', 'ANALISTA', 7566, '1991/11/09', 3000, NULL, 20);
INSERT INTO emple VALUES (7839,'REY', 'PRESIDENTE', NULL, '1991/11/17', 4100, NULL, 10);
INSERT INTO emple VALUES (7844,'TOVAR', 'VENDEDOR', 7698, '1991/09/08', 1350, 0, 30);
INSERT INTO emple VALUES (7876,'ALONSO', 'EMPLEADO', 7788, '1991/09/23', 1430, NULL, 20);
INSERT INTO emple VALUES (7900,'JIMENO', 'EMPLEADO', 7698, '1991/12/03', 1335, NULL, 30);
INSERT INTO emple VALUES (7902,'FERNÁNDEZ','ANALISTA', 7566, '1991/12/03', 3000, NULL, 20);
INSERT INTO emple VALUES (7934,'MUÑOZ', 'EMPLEADO', 7782, '1992/01/23', 1690, NULL, 10);

create table depart(
  dept_no INTEGER,
  dnombre VARCHAR(30),
  loc VARCHAR(30)
);

INSERT INTO depart VALUES (10, 'CONTABILIDAD', 'SEVILLA');
INSERT INTO depart VALUES (20, 'INVESTIGACIÓN', 'MADRID');
INSERT INTO depart VALUES (30, 'VENTAS', 'BARCELONA');
INSERT INTO depart VALUES (40, 'PRODUCCIÓN', 'BILBAO');

--SELECT * FROM emple

--1--SELECT apellido, oficio, dept_no FROM emple
--2--SELECT dept_no, dnombre, loc FROM depart
--3--SELECT * FROM emple
--4--SELECT * FROM emple ORDER BY apellido 
--5--SELECT apellido, oficio, dept_no FROM emple ORDER BY dept_no DESC
--6--SELECT apellido, oficio, dept_no FROM emple ORDER BY dept_no DESC, apellido ASC
/*7--SELECT dnombre as 'Departamento'  ,  apellido as 'apellido empleados'  FROM depart  
inner join emple
on  depart.dept_no = emple.dept_no
 ORDER BY dnombre desc, apellido asc*/
 
 /*8--SELECT * FROM emple
 WHERE salario > 2000*/
 
 /*9--SELECT * FROM emple
 WHERE Oficio = 'VENDEDOR'*/
 
/*10--SELECT Apellido, Oficio 
FROM  emple
WHERE dept_no = 20*/

/*11--SELECT * FROM emple
order by Apellido*/

/*12--SELECT * FROM emple 
where Oficio = 'VENDEDOR'
ORDER BY Apellido*/

/*13SELECT * FROM emple
WHERE dept_no = 20 AND Oficio = 'EMPLEADO' 
order by Apellido */

/*14--SELECT * FROM emple
WHERE Salario > 2000 or dept_no = 20*/

/*15--SELECT * FROM emple
ORDER BY Oficio, Apellido*/

/*16--SELECT * FROM emple
where Apellido like 'A%'*/

/*17--SELECT * FROM emple
where Apellido like '%Z'*/

/*18--SELECT * FROM emple
where Apellido like 'A%'
AND Oficio like '%E%'*/

/*19--SELECT * FROM  emple
WHERE Salario BETWEEN 1000 AND 2000*/

/*20--SELECT * FROM emple
WHERE Oficio = 'VENDEDOR'  AND comision > 1000*/

/*21--SELECT * FROM emple
order by dept_no ,Apellido desc*/

/*22--SELECT emp_no, apellido FROM emple
WHERE Apellido like '%Z'  AND salario > 2000*/

/*23--SELECT * FROM depart
WHERE loc like 'B%'*/

/*24--SELECT * FROM emple 
where Salario>1100 and Oficio='EMPLEADO' AND dept_no=10*/

/*25--SELECT Apellido FROM emple
where comision is null or comision=0*/

/*26--SELECT apellido FROM emple
where comision is null or comision=0 
group by apellido
having apellido like 'j%'*/

/*27--SELECT apellido, oficio from emple
where oficio='VENDEDOR' or oficio='ANALISTA' or oficio='EMPLEADO'
order by oficio asc*/

/*28--SELECT apellido, oficio, salario  FROM emple 
where  salario>2000 and ( not    oficio='ANALISTA' and not  oficio='EMPLEADO') */

/*29--select apellido, Salario from emple
where salario between 2000 and 3000*/

/*30--SELECT apellido, salario, dept_no FROM emple
 where salario > 2000 and( dept_no=10 or dept_no=30)*/
 
 /*31--SELECT apellido, emp_no, salario FROM emple
 WHERE  salario  not between 1000 and  2000*/
 
 
 /*32--SELECT LOWER (apellido)
 FROM emple*/
 
 /*33--select (apellido + ' ' + oficio) as 'Concatena' from emple*/
 
 /*34--select  len (apellido) from emple
 order by len (apellido) desc*/
 
 
 --select year (fecha_alt),apellido from emple 
 
 /*SELECT DISTINCT year(fecha_alt) año 
FROM emple;*/
 
 
/*36--select * from emple
 where  fecha_alt like '1992%'*/
 
 
 
/*37--select * FROM emple
where fecha_alt like '%%-02%-%%'*/

--elect datediff (month,'2019/01/01','2019/05/08')
 
 /*39--SELECT * FROM emple 
 WHERE apellido like 'A%' OR fecha_alt='1992'*/
 
 select * from emple
 where dept_no=10 and (comision is null or comision=0)
 
 --select apellido, max(salario)from emple


run \| edit \| history \| help	0