DROP TABLE clienti;
DROP TABLE facturi;

CREATE TABLE clienti(
    Codcl NUMBER(4) CONSTRAINT pk_clienti PRIMARY KEY
        CONSTRAINT ck_codcl CHECK (codcl>1000),
    dencl VARCHAR2(20) CONSTRAINT ck_dencl
        CHECK(SUBSTR(dencl,1,1)=UPPER(SUBSTR(dencl,1,1))),
    localitatea VARCHAR2(30) CONSTRAINT nn_localit NOT NULL);

CREATE TABLE facturi(
    nrfact NUMBER(8) CONSTRAINT pk_facturi PRIMARY KEY
        CONSTRAINT ck_nrfact CHECK (nrfact>=10000000 AND nrfact<=99999999),
    datfact DATE DEFAULT sysdate,
    codcl NUMBER(4) CONSTRAINT ck_codcl2 CHECK(codcl>1000)
        CONSTRAINT fk_facturi_clienti REFERENCES clienti(codcl),
    observatii VARCHAR2(50));




INSERT INTO clienti(Codcl, dencl, localitatea) VALUES (1001, 'Popescu', 'Craiova');
INSERT INTO clienti(Codcl, dencl, localitatea) VALUES (1002, 'Andrei', 'Bucuresti');
INSERT INTO clienti(Codcl, dencl, localitatea) VALUES (1003, 'Mihai', 'Otopeni');
INSERT INTO clienti(Codcl, dencl, localitatea) VALUES (1004, 'Ovidiu', 'Constanta');

SELECT * FROM clienti;

INSERT INTO facturi(nrfact, datfact, codcl,observatii) VALUES (10000001, '10/Nov/2018', 1001, 'Aviz de plata');
INSERT INTO facturi(nrfact, datfact, codcl,observatii) VALUES (10000002, '25/Feb/2019', 1002, 'Aviz de plata');
INSERT INTO facturi(nrfact, datfact, codcl,observatii) VALUES (10000003, '25/Jun/2019', 1003, 'Aviz de plata');
INSERT INTO facturi(nrfact, datfact, codcl,observatii) VALUES (10000004, '12/Dec/2019', 1004, 'Aviz de plata');
SELECT * FROM facturi;

   /* JOIN  */
SELECT ENAME, JOB, DNAME
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

SELECT DEPT.DEPTNO, ENAME, JOB, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
ORDER BY DEPT.DEPTNO