-- M2106
-- Correction du TD1

-- ex1
CREATE TABLE etat
a
  code        CHAR(2 CHAR)      CONSTRAINT pk_etat          PRIMARY KEY,
  nom         VARCHAR2(15 CHAR) CONSTRAINT nn_etat_nom      NOT NULL,
  capitale    VARCHAR2(15 CHAR) CONSTRAINT nn_etat_capitale NOT NULL,
  nbHab       NUMBER(8)         CONSTRAINT nn_etat_nbHab    NOT NULL,
  CONSTRAINT  un_etat_nom       UNIQUE(nom),
  CONSTRAINT  un_etat_capitale  UNIQUE(capitale),
  CONSTRAINT  ck_etat_nbHab     CHECK(nbHab >= 500000)
) ;

-- ex2
CREATE TABLE prenom
(
  idP         NUMBER                CONSTRAINT pk_prenom          PRIMARY KEY,
  libelle     VARCHAR2(15 CHAR)     CONSTRAINT nn_prenom_libelle  NOT NULL,
  estCelebre  CHAR(1 CHAR),
  CONSTRAINT  un_prenom_libelle     UNIQUE(libelle),
  CONSTRAINT  ck_prenom_libelle     CHECK(INITCAP(libelle) = libelle),
  CONSTRAINT  ck_prenom_estCelebre  CHECK(estCelebre IN ('O', 'N'))
) ;

-- ex3
CREATE TABLE naissance
(
  idP         NUMBER,
  sexe        CHAR(1 CHAR),
  code        CHAR(2 CHAR),
  annee       NUMBER(4),
  nb          NUMBER              DEFAULT 5 CONSTRAINT nn_naissance_nb NOT NULL,
  CONSTRAINT  pk_naissance        PRIMARY KEY(idP, sexe, code, annee),
  CONSTRAINT  fk_naissance_idP    foreign KEY(idP) references prenom,
  CONSTRAINT  fk_naissance_code   foreign KEY(code) references etat,
  CONSTRAINT  ck_naissance_sexe   CHECK(sexe IN ('M', 'F')),
  CONSTRAINT  ck_naissance_annee  CHECK(annee BETWEEN 1910 AND 2012),
  CONSTRAINT  ck_naissance_nb     CHECK(nb >= 5)
) ;

-- ex4
DESC etat
DESC prenom
DESC naissance

-- ex5
SELECT table_name
FROM user_tables ;

-- ex6
SELECT table_name, constraint_name, constraint_type, status
FROM user_constraints
ORDER BY table_name, constraint_name ;

-- ex7
SELECT t.*, bytes/1024/1024 MO_occupes, (max_bytes - bytes)/1024/1024 MO_dispos
FROM user_ts_quotas t ;

-- ex8
INSERT INTO etat VALUES('CA', 'California', 'Sacramento', 38041430) ;
INSERT INTO etat(code, nom, capitale, nbHab) VALUES('TX', 'Texas', 'Austin', 26059203) ;
INSERT INTO etat(capitale, nbHab, code, nom) VALUES('Tallahasee', 19317568, 'FL', 'Florida') ;
INSERT INTO etat VALUES('MA', 'Massachussets', 'Boston', 6646144) ;
INSERT INTO etat VALUES('CO', 'Colorado', 'Denver', 5187582) ;
INSERT INTO etat VALUES('LA', 'Louisiana', 'Baton Rouge', 4601893) ;
INSERT INTO etat VALUES('UT', 'Utah', 'Salt Lake City', 2855287) ;
COMMIT ;

-- ex9
INSERT INTO prenom(idP, libelle) SELECT idP, libelle FROM cabanac.prenom ;

-- ex10
INSERT INTO naissance
  SELECT idP, sexe, code, annee, nb
  FROM cabanac.naissance
  WHERE code IN ('CA', 'CO', 'FL', 'LA', 'MA', 'TX', 'UT') -- ou in (select code from etat)
    AND annee > 1979 ;

COMMIT ;


--Ex11
UPDATE PRENOM
SET ESTCELEBRE = 'N'
WHERE LIBELLE = 'Clorissa' OR LIBELLE='Elyn';

SELECT * FROM PRENOM WHERE libelle = 'Clorissa' OR libelle = 'Elyn';

COMMIT;

UPDATE PRENOM
SET ESTCELEBRE = 'O'
WHERE LIBELLE = 'Barack' OR LIBELLE='Monica' OR libelle = 'Marylin' OR libelle ='Mickael' OR libelle = 'Indiana' OR libelle = 'Hussain';

--ex14
INSERT INTO etat (CODE, NOM, CAPITALE, NBHAB) VALUES ( 'FR', 'France', 'Paris', 50);

COMMIT;

--ex15
CREATE TABLE naissancevip AS
  SELECT *
  FROM naissance
  WHERE idp IN (SELECT idp FROM prenom WHERE libelle = 'Clorissa' OR libelle = 'Elyn');


SELECT constraint_name, status, constraint_type, search_condition FROM USER_CONSTRAINTS WHERE table_name = 'NAISSANCEVIP';
SELECT constraint_name, status, constraint_type, search_condition FROM USER_CONSTRAINTS WHERE table_name = 'NAISSANCE';

DELETE FROM naissancevip WHERE annee < 2000;

DROP TABLE naissancevip;

--ex16
ALTER TABLE etat ADD
prixPort NUMBER(3,2) DEFAULT 3.14 CONSTRAINT ck_etat_prixPort CHECK(prixPort <= 8.50);

--EX 17

UPDATE ETAT
SET prixPort = 6.90
WHERE nom = 'Utah' OR NBHAB > 10000000;

COMMIT;