UNIVERSIDAD TECNOLOGICA DE SANTIAGO

(UTESA)

ASIGNATURA:
Base de Datos II

TEMA:
Metodología para el desarrollo de Sistemas de Bases de Datos
seguros

NOMBRES
Antonio Reinoso Vasquez – 3-12-3889
Eric Pinales – 2-12-3721
Jonathan Liado – 2-13-4325
Miguel Ángel Heredia – 1-12-6086
Stephany Delirio Paniagua Rojas – 1-14-4661
Alexander Frías – 2-13-5606
Stefany Maria Abreu Vasquez – 1-13-5662

PRESENTADO A:
Ricardo Soriano

Santo Domingo, Rep. Dom. (Recinto Máximo Gómez)

Fecha: 14-12-2017
Indicaciones para realizar Backup

BACKUP DATABASE [PRACTICA01]

TO DISK = N'F:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\[Link]'
WITH NOFORMAT, NOINIT, NAME = N'PRACTICA01-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

_____________________________________________________________________________
PRACTICA 01 QUERY FACTURA CON SUFICIENTE DETALLE PARA CLIENTE

Query:

USE PRACTICA01

SELECT Nombre AS Cliente, Direccion, Telefono,

Nombre_usuario as Facturador,imp_pesos as valor, cantidad as
Cantidad_articulos,
valor_arti*cantidad+itbis as valor_total, tipo_venta,fecha, detalle as
MPSolutions

FROM TBL_Cliente, TBL_factura, TBL_Detalle_factura, tblusuario, tbl_articulo

WHERE tbl_CLIENTE.id_cliente=2023 and tblusuario.id_usuario=101;

_____________________________________________________________________________

Base de datos con restricciones:

Créate database DbRestricciones

Use DbRestricciones
Créate table tblcliente (
nombreColumna1 INT Null,
nombreColumna2 varchar(100) Not null,
nombreColumna3 varchar(100) Not null,
)
Select * from tblcliente
Alter table tblcliente
Add nombrecolumna4 varchar(100)
Constraint restrinccion4 default ‘N/A’ not null
Select * from tblcliente
Insert into tblcliente (nombrecolumna1, nombrecolumna2, nombrecolumna3)
Values (‘1’,’Ricardo’,’Almonte’)
Select * from tblcliente

_____________________________________________________________________________

Create database rockie

Go
Use roockie
Go
Create table [Link]
(Noencrip varchar (50),
Encrip varbinary (50),
Go

Create máster ley encryption by password = ‘llavemaestra'

Go
Create certificate certificado
With subject = ‘rockie blog',
Expiry_date =’20171231’,
Go
Select name pvt_ley_encryption_type_desc, subject, expiry_date from [Link].
Inserta into [Link] (noencrip, encrip)
Values (‘se muestra', encryptbycert(cert_id ( ‘05216vc215a1’), ‘no se muestra'))
Go
Selecto noencrip
Convertido (varchar (13) decryptbycert (cert_id (05216vc215a1’) encrip)
As encriptado
From [Link]

Backup certificaré certificado

To filé “’c:\temp\[Link]'
With private ley (encryption by password = ‘123456’,
Filé = ‘c:\temp\[Link]');
Go

_____________________________________________________________________________

1. SELECT * FROM EMP

2. SELECT * FROM DEPT

3. SELECT * FROM EMP WHERE JOB = 'CLERK'

4. SELECT * FROM EMP WHERE JOB = 'CLERK' ORDER BY ENAME ASC

5. SELECT * FROM EMP WHERE JOB = 'CLERK' ORDER BY ENAME DESC

6. SELECT EMPN0,ENAME,SAL FROM EMP

7. SELECT DNAME FROM DEPT

8. SELECT DNAME FROM DEPT ORDER BY DNAME ASC

9. SELECT DNAME FROM DEPT ORDER BY LOC ASC

10. SELECT DNAME FROM DEPT ORDER BY LOC DESC

11. SELECT ENAME,JOB FROM EMP ORDER BY SAL ASC

12. SELECT ENAME,JOB FROM EMP ORDER BY JOB ASC,SAL DESC

13. SELECT ENAME,JOB FROM EMP ORDER BY JOB DESC,SAL ASC

14. SELECT SAL,COMM FROM EMP WHERE DEPTN0=30

15. SELECT SAL,COMM FROM EMP WHERE DEPTN0=30 ORDER BY COMM ASC

16. SELECT DISTINCT COMM FROM EMP

17. SELECT DISTINCT ENAME,COMM FROM EMP

18. SELECT DISTINCT COMM,DEPTN0 FROM EMP

19. SELECT DISTINCT ENAME,SAL FROM EMP

20. SELECT ENAME, SAL+1000 FROM EMP

21. SELECT ENAME,SAL,SAL+1000 AS 'NUEVO SALARIO' FROM EMP

22. SELECT * FROM EMP WHERE COMM > SAL/2

23. SELECT * FROM EMP WHERE COMM <= SAL*0.25

24. SELECT 'NOMBRE:'ENAME,'PUESTO:'JOB FROM EMP

25. SELECT EMPN0,SAL,COMM FROM EMP WHERE EMPN0>7500

26. SELECT ENAME,JOB FROM EMP WHERE SUBSTRING(ENAME,1,1)>='J' ORDER BY

ENAME ASC

27. SELECT SAL,COMM,SAL+COMM AS 'SALARIO TOTAL' FROM EMP WHERE COMM IS NOT
NULL

28. SELECT SAL,COMM,SAL+COMM AS 'SALARIO TOTAL' FROM EMP WHERE COMM IS

NULL

29. SELECT * FROM EMP WHERE SAL> 1000 AND MGR=7698

30. SELECT * FROM EMP WHERE SAL> 1000 AND MGR<>7698

31. SELECT ENAME,SAL+COMM AS 'SALARIO TOTAL',COMM,ROUND

((100*COMM/(SAL+COMM)),0) AS'PORCENTAJE'FROM EMP WHERE COMM IS NOT NULL
ORDER BY ENAME

32. SELECT * FROM EMP WHERE DEPTN0=10 AND ENAME NOT LIKE '%LA%'

33. SELECT * FROM EMP WHERE MGR IS NULL

34. SELECT DNAME FROM DEPT WHERE NOT DNAME='SALES' AND NOT
DNAME='RESEARCH'

35. SELECT ENAME,EMPN0,SAL,HIREDATE FROM EMP WHERE JOB ='CLERK' AND DEPTN0
<>10 AND SAL>800 ORDER BY HIREDATE ASC

36. SELECT ENAME,COMM,SAL=COMM FROM EMP WHERE COMM IS NOT NULL ORDER
BY ENAME
37. SELECT ENAME FROM EMP WHERE LEN (ENAME)=5

38. SELECT ENAME FROM EMP WHERE LEN (ENAME)>=5

39. SELECT * FROM EMP WHERE ENAME LIKE 'A%' AND SAL > 1000 AND COMM IS NOT
NULL AND DEPTN0=30

40. SELECT ENAME,SAL+COMM AS 'SALARIO TOTAL',SAL FROM EMP WHERE COMM IS

NOT NULL ORDER BY SAL+COMM,SAL

41. SELECT ENAME,SAL,COMM FROM EMP WHERE SAL BETWEEN COMM/2 AND COMM
ORDER BY SAL ASC

42. SELECT ENAME,SAL,COMM FROM EMP WHERE SAL NOT BETWEEN COMM/2 AND
COMM ORDER BY SAL ASC

43. SELECT ENAME,JOB FROM EMP WHERE JOB LIKE '%MAN%' AND ENAME LIKE 'A%'

44. SELECT ENAME,JOB FROM EMP WHERE LEN(JOB)>5 ORDER BY ENAME

45. SELECT * FROM EMP WHERE LEN (ENAME)>=5

46. SELECT ENAME,COMM,SAL+COMM AS 'SALARIO

TOTAL',(SAL+COMM)+(0.06*(SAL+COMM)) AS '6%',(SAL+COMM)+(0.07(SAL+COMM)) AS '7%'
FROM EMP WHERE COMM IS NOT NULL UNION SELECT
ENAME,COMM,SAL,SAL+(0.06*SAL),SAL+(0.07*SAL) FROM EMP WHERE COMM IS NULL

47.

48. SELECT ENAME,HIREDATE FROM EMP WHERE JOB <> 'SALESMAN'

49. SELECT * FROM EMP WHERE EMPN0 IN (7844,7900,7521,7782,7934,7698,7639) AND

EMPN0 NOT IN (7902,7839,7499,7878)

50. SELECT * FROM EMP ORDER BY DEPTN0 ASC,EMPN0 DESC

51. SELECT * FROM EMP WHERE MGR> EMPN0 AND SAL BETWEEN 1000 AND 2000 AND
DEPTN0=30
52. SELECT MAX(SAL) AS 'MAXIMO SALARIO', SUM (COMM) AS 'TOTAL DE
COMISIONES',COUNT (EMPN0) AS 'TOTAL DE EMPLEADOS' FROM EMP

53. SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE
EMPN0=7934)ORDER BY SAL ASC

54. SELECT * FROM EMP WHERE SAL> (SELECT SAL FROM EMP WHERE ENAME= 'ALLEN')

55. SELECT ENAME FROM EMP WHERE ENAME>= ALL(SELECT ENAME FROM EMP)

56. SELECT MAX (SAL) AS 'SALARIO MAS ALTO',MIN (SAL) AS 'SALARIO MAS
BAJO',(MAX(SAL)-MIN(SAL)) AS 'DIFERENCIA' FROM EMP

57. SELECT ENAME,SAL FROM EMP WHERE SAL>(SELECT SAL FROM EMP WHERE
EMPN0=7930)

58. SELECT E.DEPTN0,[Link],AVG(SAL) AS 'SALARIO PROMEDIO' FROM EMP E,DEPT D

WHERE E.DEPTN0=D.DEPTN0 AND E.DEPTN0 NOT IN (SELECT DEPTN0 FROM EMP WHERE
SAL<= 900)GROUP BY [Link],E.DEPTN0

59. SELECT [Link],[Link] FROM EMP E,DEPT D WHERE E.DEPTN0=D.DEPTN0 AND

LEN([Link])>5 ORDER BY [Link] ASC,[Link]

60. SELECT ENAME,SAL FROM EMP WHERE SAL>=ALL(SELECT AVG(SAL)FROM EMP)

61. SELECT [Link],[Link],E.DEPTN0 FROM EMP E WHERE [Link]>=ALL(SELECT SAL FROM

EMP WHERE DEPTN0=E.DEPTN0)

62. SELECT COUNT(DISTINCT JOB) AS 'EMPLEOS',COUNT(ENAME) AS 'EMPLEADOS',COUNT

(DISTINCT SAL) AS 'SALARIOS',SUM(SAL) AS 'TOTAL SALARIOS' FROM EMP WHERE DEPTN0=30

63. SELECT COUNT(COMM) AS 'CANTIDAD DE EMPLEADOS CON COMISION' FROM EMP

64. SELECT COUNT(E.EMPN0) AS 'CANT. DE EMP DEL DEPT 20' FROM EMP E ,DEPT D
WHERE E.DEPTN0=D.DEPTN0 AND D.DEPTN0=20
65. SELECT E.DEPTN0,DNAME,COUNT([Link]) AS 'CANT. DE EMPLEADOS' FROM EMP
E,DEPT D WHERE D.DEPTN0=E.DEPTN0 AND 3<(SELECT COUNT(ENAME)FROM EMP WHERE
DEPTN0=D.DEPTN0) GROUP BY DNAME,E.DEPTN0

66. SELECT * FROM EMP WHERE DEPTN0=10

67. SELECT ENAME FROM EMP WHERE DEPTN0=10 AND JOB IN (SELECT JOB FROM EMP
INNER JOIN DEPT ON(EMP.DEPTN0=DEPT.DEPTN0)WHERE [Link]='SALES')

68. SELECT ename from emp where EMPN0 in (Select mgr from emp where mgr is not null)
order by ename desc

69. SELECT * from emp where job in (Select job from emp inner join dept on (emp.
DEPTN0=dept. DEPTN0) where [Link]='CHICAGO')

70. SELECT distinct(job), count(ename) from emp group by job

71. SELECT sum(sal) from emp group by deptn0

72. SELECT DEPTN0 from dept where DEPTN0 not in (Select DEPTN0 from emp)

73. SELECT ename from emp where ename Not in (SELECT ename from emp where
DEPTN0 in (Select mgr from emp where mgr is not null))

74. SELECT dname, count(ename), avg(sal*12) from emp inner join dept on (emp.
DEPTN0=dept. DEPTN0) group by dname

75. SELECT ename from emp where DEPTN0=30 order by comm desc

76. SELECT ename from emp inner join dept on (emp.DEPTN0=dept.DEPTN0) where
loc='DALLAS' or loc='NEW YORK'

77. select [Link],[Link] as jefe from emp a right join emp b on a.DEPTN0=[Link]