EXERCICIO DE BANCO DE DADOS

create database estacio;

use estacio;

drop table turma;

CREATE TABLE turma(

 nome varchar(50) not null,

 celular varchar(20) not null,

 email varchar(100) not null,

 nota decimal(2,2),

 frequencia int,

 situação         varchar(20),

 status CHAR);

 

 

 select * from turma;

 

 

 

ALTER TABLE turma ADD COLUMN matricula INT PRIMARY KEY AUTO_INCREMENT FIRST;

ALTER TABLE turma CHANGE COLUMN frequencia freq INT;

ALTER TABLE turma CHANGE COLUMN email arroba varchar(100);

ALTER TABLE turma DROP COLUMN status;

INSERT INTO turma (matricula,nome,celular,arroba,nota,freq,situação)

                                                values(0001,'Beth Frígida','98899','@betefrig',NULL,NULL,'Matriculado'),

                                                          (0002,'Laura Palmer','98841','@laurap',NULL,NULL,'Matriculado'),

                                                          (0003,'Rita Grita','99012','@ritagrita',NULL,NULL,'Matriculado'),

                                                      (0004,'Maria Debê','98832','@mariadb',NULL,NULL,'Abandonante');

                       

INSERT INTO turma values(7,'Marta Marte','98899','@betefrig',NULL,NULL,'Matriculado');

INSERT INTO turma (nome,celular,arroba,nota,freq,situação)

                                                values('João Junta','97799','@joaojunta',NULL,NULL,'M'),

                                                          ('John Lennon','98091','@98091',NULL,NULL,'M'),

                                                          ('Paulo Paulada','99772','@paulop',NULL,NULL,'M');

                             

SET SQL_SAFE_UPDATES = 0;

UPDATE turma SET  situação = 'Inscrito' WHERE situação='Matriculado';

UPDATE turma SET celular = '98801' WHERE  nome='Marta Marte';

UPDATE turma SET arroba = '@lennonjohn' WHERE  nome='John Lennon';

UPDATE turma SET freq = 5;

delete from turma where nome = 'Rita Grita';

select * from turma;