[go: up one dir, main page]
Scribd
Upload
52 views5 pages

( (,), Not Null, ) : 'Scrummaster ' 'Productower' 'Team' 'Steakholders'

The document creates a database called scrum and defines tables for roles, countries, and personnel. It inserts data into these tables, including roles, countries, and personnel records with names, salaries, countries, roles, and other details. It then performs various SELECT, INSERT, UPDATE and DELETE queries on the personnel table to retrieve, add, modify and remove records.

Uploaded by

abel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
52 views5 pages

( (,), Not Null, ) : 'Scrummaster ' 'Productower' 'Team' 'Steakholders'

The document creates a database called scrum and defines tables for roles, countries, and personnel. It inserts data into these tables, including roles, countries, and personnel records with names, salaries, countries, roles, and other details. It then performs various SELECT, INSERT, UPDATE and DELETE queries on the personnel table to retrieve, add, modify and remove records.

Uploaded by

abel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

create database scrum

go

use scrum
go

create table roles


(
id_rol int identity (1,1),
rol varchar (20)not null,
constraint pk_roles primary key (id_rol)
)
go

insert into roles values ('ScrumMaster ')


insert into roles values('ProductOwer')
insert into roles values ('Team')
insert into roles values ('Steakholders')

create table pais


(
id_pais int identity (1,1),
pais varchar (25),
constraint pk_pais primary key (id_pais)
)
go

insert into pais values ('El Salvador')


insert into pais values ('Guatemala')
insert into pais values ('Costa Rica')
insert into pais values ('Japon')
insert into pais values ('Esatdos Unidos ')
insert into pais values ('Argentina')
insert into pais values ('Mexico')

create table personas (


id_persona int identity (1,1),
apellido varchar(40) not null,
nombre varchar (40) not null,
sueldo smallmoney ,
direccion varchar (100) ,
celular varchar (8),
sexo varchar(1),
nacimiento datetime ,
id_rol int not null,
id_pais int not null,
constraint pk_persona primary key (id_persona),
constraint fk_rol foreign key (id_rol)
references roles(id_rol),
constraint fk2_pais foreign key (id_pais)
references pais(id_pais)
)
go

insert into personas values ('Romero Peña','Sandra


Y',1200,'','34567564','F','1972/3/3',3,1)

insert into personas values ('Arevalo','Maria


Johana',950,'','32356787','F','1976/2/16',3,3)

insert into personas values ('Hernandez


Mancia','Marielos',958,'','67567555','F','1978/7/4',3,4)

insert into personas values ('Perez


Juarez','Joaquin',850,'','34567564','M','1969/9/2',3,1)

insert into personas values ('Solis


Catro','Eduardo',600,'','12327552','M','1972/3/11',3,1)

insert into personas values ('Ramos Nieto','Jorge


',550,'','34567174','M','1972/10/4',4,1)

insert into personas values ('Hernandez


Carias','Johanna',1800,'','34234809','F','1952/3/4',1,1)

insert into personas values ('Romero Peña','Manuel


Alberto',0,'','34567342','M','1969/12/4',2,1)

insert into personas values ('Garcia


Alvarado','Roxana',1600,'','34345564','F','1970/4/4',1,1)

insert into personas values ('Torres


Castro','Alfredo',450,'','34565674','M','1980/2/9',3,5)

insert into personas values ('Romero


Peña','Verenice',400,'','34556764','F','1978/3/17',3,1)

insert into personas values ('Martinez','Evelyn


Roxana',340,'','34567564','F','1972/8/14',3,5)

insert into personas values


('Siguenza','Eduardo',1120,'','21467564','M','1975/10/9',3,1)

insert into personas values ('Alas


Duran','Martin',1200,'','34562364','M','1972/3/4',3,1)

insert into personas values ('Jerez M','Jose


Carlos',589,'','34563352','M','1972/3/6',4,1)

insert into personas values ('Bolaños


Cea','Sandra',244,'','67557564','F','1968/3/4',3,1)

insert into personas values ('Cuestas D.','Jenny


Maria',1089,'','34567564','F','1972/9/14',3,4)
insert into personas values ('Romero
Peña','Alexandra',1000,'','34565455','F','1972/7/24',3,1)

insert into personas values ('Molina


Nuñez','Mario',578,'','34567564','M','1962/3/7',3,3)

insert into personas values ('Romero


Peña','Jennifer',989,'','34657564','F','1977/10/21',3,3)

insert into personas values ('Melendez A.','karla


Maria',875,'','45656463','F','1956/12/1',4,1)

insert into personas values ('Romero Peña','Andrea


Lissette',0,'','34545343','F','1990/3/4',3,2)

insert into personas values ('Melendez


Torres','Wenceslado',270,'','34564563','M','1977/8/4',4,1)

insert into personas values ('Vivas Rosales','Tere


Maria',786,'','56345674','F','1964/2/7',3,1)

SELECT * FROM personas

SELECT pais FROM pais

SELECT nombre,apellido,sueldo
FROM personas

SELECT nombre,apellido
FROM personas
WHERE sexo='F'

SELECT nombre,apellido,id_pais,sexo
FROM personas
WHERE sexo= 'F' AND id_pais = 1

SELECT nombre,apellido,id_pais,sexo
FROM personas
WHERE sexo= 'F' OR id_pais = 1

SELECT pais FROM pais

SELECT nombre,apellido,id_pais,sexo
FROM personas
WHERE sexo
= 'F' OR id_pais = 1

SELECT apellido,sueldo FROM personas WHERE sueldo> 500


SELECT nombre,apellido, sueldo
FROM personas
WHERE sueldo > 500 AND sueldo< 700 AND sexo='M'

SELECT nombre AS NOMBRE, apellido AS APELLIDO, nacimiento AS CUMPLEAÑOS


FROM personas

SELECT apellido,nacimiento FROM personas ORDER BY nacimiento

//Ahora probaremos las sentencias INSERT:

INSERT INTO personas


(apellido,nombre,sueldo,direccion,celular,sexo,nacimiento,id_rol, id_pais)
VALUES
('Molina Vasquez', 'Alejandro',897, '10 Avenida Sur y 12
Calle Poniente', '45633423','M', '1970/4/21',2,3)

INSERT INTO personas VALUES


('Rivas Alba','Yesenia A',234,'4 Calle Oriten y parque Los Arboles',
'54534243', 'F','11979/11/1' ,2, 4)

INSERT INTO personas (apellido,nombre,sueldo,id_rol,id_pais) VALUES ('Marquez',


'Roxana Elizabeth',434,4,1)

//Sigamos realizando pruebas con las sentencia UPDATE

UPDATE personas SET nacimiento= '1985/11/2' WHERE id_persona


= 14

UPDATE personas SET sexo= 'f' WHERE sexo 'F' UPDATE personas SET celular= ''

//Por ultimo probaremos la sentencia DELETE

DELETE personas WHERE sueldo< 500

DELETE personas WHERE id_persona 5

DELETE personas WHERE sexo 'M'


select sueldo
from personas
order by sueldo asc;

select sueldo
from personas
order by sueldo desc;

select nombre ,sueldo

You might also like