Lista+de+Exercicios+SQL+Server

Lista de exercícios de SQL SERVER 2005/2008

Considere o seguinte modelo de Banco de Dados:

Usar o script abaixo para inserir dados no banco de Dados

IMIH, sabendo que os campos sublinhados são chaves.

Não esquecer de declarar as chaves estrangeiras.

Create database IMIH

Use IMIH

create table Depto

(codDepto char(5),

nomeDepto varchar(20),

primary key (codDepto));

insert into Depto

values ('INF01', 'Informática');

insert into Depto

values ('MAT01', 'Matemática');

insert into Depto

values ('ELE01', 'Eletrônica');

create table Discipl ina

(codDepto char(5),

numDisc char(5),

nomeDisc varchar(20),

credi tosDisc integer,

primary key (codDepto, numDisc),

foreign key (codDepto) references Depto );

insert into Discipl ina

values ('INF01', 'DIS01', 'Ling Formais', 4);


values ('INF01', 'DIS02', 'Teoria da Comp', 4);


values ('INF01', 'DIS03', 'Programacao I ', 8);


values ('MAT01', 'DIS04', 'Cálcu lo 1', 4);


values ('MAT01', 'DIS01', 'Cálcu lo 2', 6);

create table PreReq

(codDepto char(5),

numDisc char(5),

codDeptoPreReq char(5),

numDiscPreReq char(5),

primary key (codDepto, numDisc, codDeptoPreReq, numDiscPreReq),

foreign key (codDepto, numDisc) references discipl ina,

foreign key (codDeptoPreReq, numDiscPreReq) references discipl ina);

insert into PreReq

values ('INF01', 'DIS02', 'INF01', 'DIS01');

insert into PreReq

values ('MAT01', 'DIS04', 'MAT01', 'DIS01');

insert into PreReq

values ('INF01', 'DIS03', 'INF01', 'DIS02');

create table Turma

(anoSem integer,

codDepto char(5),

numDisc char(5),

siglaTur char(5),

capacTur integer,

primary key (anoSem, codDepto, numDisc, siglaTur),

foreign key (codDepto, numDisc) references Discipl ina);

insert into turma

values (20021, 'INF01', 'DIS01', 'TUR01', 30);

insert into turma


insert into turma


insert into turma


insert into turma


insert into turma

values (20021, 'MAT01', 'DIS01', 'TUR01', 15);

insert into turma


create table Predio

(codPred integer ,

nomePred varchar(30),

primary key (codPred));

insert into predio

values (43423, 'Informática-Aulas');

insert into predio

values (43421, 'Administração');

insert into predio

values (43424, 'Laboratórios');

create table Sala

(codPred integer ,

numSala integer,

capacSala integer,

primary key (codPred, numSala),

foreign key(codPred)references Predio);

insert into sala

values (43423, 101, 30);

insert into sala

values (43421, 102, 50);

insert into sala

values (43424, 215, 40);

create table Horario

(anoSem integer,

codDepto char(5),

numDisc char(5),

siglaTur char(5),

diaSem integer,

horaInicio char(5),

numHoras integer,

codPredio integer ,

numSala integer,

primary key (anoSem, codDepto, numDisc, siglaTur, diaSem,

horaInicio),

foreign key (anoSem, codDepto, numDisc, siglaTur) references turma,

foreign key (codPredio, numSala) references sala);

insert into horario

values (20021, 'INF01', 'DIS01', 'TUR01', 2, '10:30', 60, 43423, 101);

insert into horario


insert into horario


insert into horario


create table Ti tulacao

(codTit integer,

nomeTi t varchar(20),

primary key (codTit));

insert into Ti tulacao

values (1, 'Doutor');


values (2, 'Mestre');


values (3, 'Especial i sta');


values (4, 'Graduado');

create table Professor

(codProf char(5),

nomeProf varchar(50),

codTi t integer,

codDepto char(5),

primary key (codProf),

foreign key (codTit) references Ti tulacao,

foreign key (codDepto) references Depto );

insert into Professor

values ('Pro01', 'Antunes', 1, 'INF01');


values ('Pro02', 'Maria dos Santos', 2, 'INF01');


values ('Pro03', 'Paulo', 3, 'MAT01');


values ('Pro04', 'Gabriel ' , 2, 'MAT01');

create table ProfTurma

(anoSem integer,

codDepto char(5),

numDisc char(5),

siglaTur char(5),

codProf char(5),

primary key (anoSem, codDepto, numDisc, siglaTur, codProf),

foreign key (anoSem, codDepto, numDisc, siglaTur) references Turma,

foreign key (codProf) references Professor);

insert into ProfTurma

values (20021, 'INF01', 'DIS01', 'TUR01', 'Pro01');






values (20021, 'MAT01', 'DIS01', 'TUR01', 'Pro03');


values (20021, 'MAT01', 'DIS01', 'TUR01', 'Pro02');

Lista 01

1. Obter todos os dados dos professores.

2. Obter o código e o nome dos professores.

3. Obter a capacidade das turmas.

4. Obter os di ferentes valores de capacidades de turmas.

5. Obter o nome das discipl inas do depto INF01, desde que tenham

mais de 5 crédi tos.

6. Obter o código do prédio chamado Laboratórios.

7. Obter o código da sala e o código do prédio, desde que a sal a

tenha capacidade superior a 35 lugares.

8. Obter o nome dos professores que têm t i tulação 1 e que trabalham

no depto INF01.

9. Obter o nome dos professores que têm ti tulação 2 ou que

trabalham no depto INF01.

10. Retorne o código e o nome dos professores que possuem o

sobrenome Santos.

11. Sabendo que cada crédi to de discipl ina corresponde a 15hs-

aula, retorne o nome da discipl ina e o seu número de horas-aula.

Lista 02

1. Obtenha os nomes das di scipl inas seguidas do nome de seu

departamento.

2. Obtenha os nomes dos professores que possuem ti tulação de ‘Dr‘.

3. Obtenha os nomes dos professores que ministraram aulas em

1999/2.

4. Obtenha os números das salas do prédio de nome ’Laboratórios’

cuja capacidade seja maior que 30.

5. Obtenha os nomes das discipl inas que foram oferecidas em 2000/1.

6. Obtenha os números das salas do prédio "Laboratórios".

7. Obtenha os nomes dos professores seguidos do nome de seu

departamento.

8. Obtenha os códigos dos professores que não possuem turma em

1999/2.

9. Nomes dos departamentos que possuem discipl inas que não

apresentam pré-requisi to.

10. Obtenha os códigos dos professores que ministraram aulas em

1999/2 e 2000/1.

11. Obtenha os nomes dos departamentos em que há pelo menos uma

discipl ina com mais que três crédi tos.

Lista 03

1. Obter os códigos dos diferentes departamentos que tem turmas no

ano-semestre 2002/1

2. Obter os códigos dos professores que são do departamento de

código 'INF01' e que ministraram ao menos uma turma em 2002/1.

3. Obter os horários de aula (dia da semana,hora inicial e número de

horas ministradas) do professor "Antunes" em 2002/1.

4. Obter os nomes dos departamentos que têm turmas que, em

2002/1, têm aulas na sala 101 do prédio denominado 'Informática-

Aulas'.

5. Obter os códigos dos professores com tí tulo denominado 'Doutor'

que não ministraram aulas em 2002/1.

6. Obter os identi fi cadores das salas (código do prédio e número da

sala) que, em 2002/1:

a. nas segundas-fei ras (dia da semana = 2), tiveram ao menos uma

turma do departamento 'Informática',

e b. nas quartas-fei ras (dia da semana = 4), tiveram ao menos uma

turma ministrada pelo professor denominado 'Antunes'.

7. Obter o dia da semana, a hora de início e o número de horas de

cada horário de cada turma ministrada por um professor de nome

`Antunes', em 2002/1, na sala número 101 do prédio de código

43423.

8. Para cada discipl ina que possui pré-requisi to, obter o nome da

discipl ina seguido do nome da discipl ina que é seu pré-requisi to.

9. Obter os nomes das discipl inas que não são pré-requisi to.

10. Obter os nomes docentes cuja ti tulação tem código diferente de 3.

11. Obter os nomes dos departamentos que têm turmas que, em

2002/1, têm aulas na sala 101 do prédio denominado 'Informática-

Aulas'.

12. Obter o nome de cada departamento seguido do nome de cada

uma de suas discipl inas que possui mais que três crédi tos (caso o

departamento não tenha discipl inas ou caso o departamento não

tenha discipl inas com mais que três crédi tos, seu nome deve aparecer

seguido de vazio).

13. Obter o os nomes dos professores que são do departamento

denominado 'Informática', sejam doutores, e que, em 2002/2,

ministraram alguma turma de discipl ina do departamento

'Informática' que tenha mais que três créd i tos.

14. Obter o número de discipl inas do departamento denominado

‘Informática'.

15. Obter os nomes das discipl inas do departamento denominado

‘Informática’ que têm o maior número de crédi tos dentre as

discipl inas deste departamento.

16. Para cada departamento, obter seu nome e o número de

discipl inas do departamento.

Obter o resul tado em ordem descendente de número de discipl inas

Documents

Lista+de+Exercicios+SQL+Server