banco de dados 071119

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

mysql> use cenariofaculdade;
Database changed
mysql> create table professor (matp int auto_increment primary key, nome varchar(40), sexo char(1), cpf char(11), cidade varchar(40), estado varchar(40), salario int, formacao char(3));
Query OK, 0 rows affected (0.02 sec)
mysql> create table disciplina (codd int auto_increment primary key, nome varchar(40), ch int, modalidade char(3));
Query OK, 0 rows affected (0.03 sec)
mysql> create table prof_disc (idprof_disc int auto_increment primary key, matriculap int, codigod int);
Query OK, 0 rows affected (0.03 sec)
mysql> alter table prof_disc add foreign key (matriculap) references professor(matp);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc prof_disc;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| idprof_disc | int(11) | NO | PRI | NULL | auto_increment |
| matriculap | int(11) | YES | MUL | NULL | |
| codigod | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table prof_disc add foreign key (codigod) references disciplina(codd);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc prof_disc; +-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| idprof_disc | int(11) | NO | PRI | NULL | auto_increment |
| matriculap | int(11) | YES | MUL | NULL | |
| codigod | int(11) | YES | MUL | NULL | |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
mysql> use cenariofaculdade;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_cenariofaculdade |
+----------------------------+
| disciplina |
| prof_disc |
| professor |
+----------------------------+
3 rows in set (0.00 sec)
mysql>
mysql> desc professor;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| matp | int(11) | NO | PRI | NULL | auto_increment |
| nome | varchar(40) | YES | | NULL | |
| sexo | char(1) | YES | | NULL | |
| cpf | char(11) | YES | | NULL | |
| cidade | varchar(40) | YES | | NULL | |
| estado | varchar(40) | YES | | NULL | |
| salario | int(11) | YES | | NULL | |
| formacao | char(3) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> desc disciplina;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| codd | int(11) | NO | PRI | NULL | auto_increment |
| nome | varchar(40) | YES | | NULL | |
| ch | int(11) | YES | | NULL | |
| modalidade | char(3) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
mysql> desc prof_disc;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| idprof_disc | int(11) | NO | PRI | NULL | auto_increment |
| matriculap | int(11) | YES | MUL | NULL | |
| codigod | int(11) | YES | MUL | NULL | |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc professor;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| matp | int(11) | NO | PRI | NULL | auto_increment |
| nome | varchar(40) | YES | | NULL | |
| sexo | char(1) | YES | | NULL | |
| cpf | char(11) | YES | | NULL | |
| cidade | varchar(40) | YES | | NULL | |
| estado | varchar(40) | YES | | NULL | |
| salario | int(11) | YES | | NULL | |
| formacao | char(3) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> select * from professor;
Empty set (0.00 sec)
mysql>
mysql> insert into professor values (1, 'Cassio', 'M', '11111111111', 'Quixada', 'CE', 10, 'MSC');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into professor values (2, 'Janete', 'F', '22222222222', 'Sao Paulo', 'SP', 500, 'MSC');
Query OK, 1 row affected (0.00 sec)
mysql> insert into professor values (3, 'Amina', 'M', '33333333333', 'Fortaleza', 'CE', 500, 'PHD');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> desc disciplina;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| codd | int(11) | NO | PRI | NULL | auto_increment |
| nome | varchar(40) | YES | | NULL | |
| ch | int(11) | YES | | NULL | |
| modalidade | char(3) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into disciplina values (1, 'Redes', 40, 'PRE');
Query OK, 1 row affected (0.00 sec)
mysql> insert into disciplina values (2, 'Jogos', 80, 'PRE');
Query OK, 1 row affected (0.00 sec)
mysql> insert into disciplina values (3, 'Testes', 80, 'PRE');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into disciplina values (4, 'Poo', 40, 'PRE');
Query OK, 1 row affected (0.01 sec)
mysql> insert into disciplina values (5, 'Banco', 40, 'PRE');
Query OK, 1 row affected (0.01 sec)
mysql> insert into disciplina values (6, 'NoSQL', 40, 'PRE');
Query OK, 1 row affected (0.00 sec)
mysql> insert into disciplina values (7, 'NoSQL', 40, 'EAD');
Query OK, 1 row affected (0.00 sec)
mysql> insert into disciplina values (8, 'Poo', 40, 'EAD');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from professor;
+------+--------+------+-------------+-----------+--------+---------+----------+
| matp | nome | sexo | cpf | cidade | estado | salario | formacao |
+------+--------+------+-------------+-----------+--------+---------+----------+
| 1 | Cassio | M | 11111111111 | Quixada | CE | 10 | MSC |
| 2 | Janete | F | 22222222222 | Sao Paulo | SP | 500 | MSC |
| 3 | Amina | M | 33333333333 | Fortaleza | CE | 500 | PHD |
+------+--------+------+-------------+-----------+--------+---------+----------+
3 rows in set (0.00 sec)
mysql>
mysql> select * from disciplina;
+------+--------+------+------------+
| codd | nome | ch | modalidade |
+------+--------+------+------------+
| 1 | Redes | 40 | PRE |
| 2 | Jogos | 80 | PRE |
| 3 | Testes | 80 | PRE |
| 4 | Poo | 40 | PRE |
| 5 | Banco | 40 | PRE |
| 6 | NoSQL | 40 | PRE |
| 7 | NoSQL | 40 | EAD |
| 8 | Poo | 40 | EAD |
+------+--------+------+------------+
8 rows in set (0.00 sec)
mysql> desc prof_disc;
+-------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| idprof_disc | int(11) | NO | PRI | NULL | auto_increment |
| matriculap | int(11) | YES | MUL | NULL | |
| codigod | int(11) | YES | MUL | NULL | |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
mysql> insert into prof_disc (matriculap, codigod) values (1, 5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into prof_disc (matriculap, codigod) values (1, 6);
Query OK, 1 row affected (0.01 sec)
mysql> insert into prof_disc (matriculap, codigod) values (1, 7);
Query OK, 1 row affected (0.01 sec)
mysql> desc prof_disc;
+-------------+---------+------+-----+---------+----------------+
| Field | Type
| Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+----------------+
| idprof_disc | int(11) | NO | PRI | NULL | auto_increment |
| matriculap | int(11) | YES | MUL | NULL | |
| codigod | int(11) | YES | MUL | NULL | |
+-------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from prof_disc;
+-------------+------------+---------+
| idprof_disc | matriculap | codigod |
+-------------+------------+---------+
| 1 | 1 | 5 |
| 2 | 1 | 6 |
| 3 | 1 | 7 |
+-------------+------------+---------+
3 rows in set (0.00 sec)
mysql>
mysql> insert into prof_disc (matriculap, codigod) values (2, 3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into prof_disc (matriculap, codigod) values (2, 4);
Query OK, 1 row affected (0.01 sec)
mysql> insert into prof_disc (matriculap, codigod) values (2, 8);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into prof_disc (matriculap, codigod) values (3, 1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into prof_disc (matriculap, codigod) values (3, 2);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from prof_disc;
+-------------+------------+---------+
| idprof_disc | matriculap | codigod |
+-------------+------------+---------+
| 1 | 1 | 5 |
| 2 | 1 | 6 |
| 3 | 1 | 7 |
| 4 | 2 | 3 |
| 5 | 2 | 4 |
| 6 | 2 | 8 |
| 7 | 3 | 1 |
| 8 | 3 | 2 |
+-------------+------------+---------+
8 rows in set (0.00 sec)
mysql> select professor.nome, prof_disc.codigod from prof_disc
-> inner join professor on (prof_disc.matriculap = professor.matP);
+--------+---------+
| nome | codigod |
+--------+---------+
| Cassio | 5 |
| Cassio | 6 |
| Cassio | 7 |
| Janete | 3 |
| Janete | 4 |
| Janete | 8 |
| Amina | 1 |
| Amina | 2 |
+--------+---------+
8 rows in set (0.00 sec)
mysql>
mysql> select prof_disc.matriculap, disciplina.nome from prof_disc inner join disciplina on (prof_disc.codigod = disciplina.codd);
+------------+--------+
| matriculap | nome |
+------------+--------+
| 1 | Banco |
| 1 | NoSQL |
| 1 | NoSQL |
| 2 | Testes |
| 2 | Poo |
| 2 | Poo |
| 3 | Redes |
| 3 | Jogos |
+------------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select professor.nome, disciplina.nome from prof_disc inner join professor on (prof_disc.matriculap = professor.matP) inner join disciplina on (prof_disc.codigod = disciplina.codd);
+--------+--------+
| nome | nome |
+--------+--------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select professor.nome, disciplina.nome from prof_disc inner join professor on (prof_disc.matriculap = professor.matP) inner join disciplina on (prof_disc.codigod = disciplina.codd);
+--------+--------+
| nome | nome |
+--------+--------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select p.nome, d.nome from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd);
+--------+--------+
| nome | nome |
+--------+--------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select p.nome, d.nome from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd);
+--------+--------+
| nome | nome |
+--------+--------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, d.nome Disciplina from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd);
+-----------+------------+
| Professor | Disciplina |
+-----------+------------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+-----------+------------+
8 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, d.nome Disciplina from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd);
+-----------+------------+
| Professor | Disciplina |
+-----------+------------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+-----------+------------+
8 rows in set (0.00 sec)
mysql> select p.nome Professor, d.nome Disciplina from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd);
+-----------+------------+
| Professor | Disciplina |
+-----------+------------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+-----------+------------+
8 rows in set (0.00 sec)
mysql> select p.nome Professor, d.nome Disciplina from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd);
+-----------+------------+
| Professor | Disciplina |
+-----------+------------+
| Cassio | Banco |
| Cassio | NoSQL |
| Cassio | NoSQL |
| Janete | Testes |
| Janete | Poo |
| Janete | Poo |
| Amina | Redes |
| Amina | Jogos |
+-----------+------------+
8 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd) group by p.nome;
+-----------+
| Professor |
+-----------+
| Amina |
| Cassio |
| Janete |
+-----------+
3 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, count(d.nome) Disciplina from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd) group by p.nome;
+-----------+------------+
| Professor | Disciplina |
+-----------+------------+
| Amina | 2 |
| Cassio | 3 |
| Janete | 3 |
+-----------+------------+
3 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, sum(d.ch) Disciplina from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd) group by p.nome;
+-----------+------------+
| Professor | Disciplina |
+-----------+------------+
| Amina | 120 |
| Cassio | 120 |
| Janete | 160 |
+-----------+------------+
3 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, sum(d.ch) Total_CH from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd) group by p.nome;
+-----------+----------+
| Professor | Total_CH |
+-----------+----------+
| Amina | 120 |
| Cassio | 120 |
| Janete | 160 |
+-----------+----------+
3 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, sum(d.ch)/20 Total_CH_S from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd) group by p.nome;
+-----------+------------+
| Professor | Total_CH_S |
+-----------+------------+
| Amina | 6.0000 |
| Cassio | 6.0000 |
| Janete | 8.0000 |
+-----------+------------+
3 rows in set (0.00 sec)
mysql>
mysql> select p.nome Professor, sum(d.ch)/20 Total_CH_S from prof_disc pd inner join professor p on (pd.matriculap = p.matP) inner join disciplina d on (pd.codigod = d.codd) where p.sexo
= 'M' group by p.nome;
+-----------+------------+
| Professor | Total_CH_S |
+-----------+------------+
| Amina | 6.0000 |
| Cassio | 6.0000 |
+-----------+------------+
2 rows in set (0.00 sec)
mysql>