Oracle SQL Table Management and Constraints

Querying Database Metadata

To begin, we search for existing tables and columns within the database schema using the following queries:

SELECT * FROM all_tables WHERE table_name LIKE 'ALUMNO%';
SELECT * FROM ALL_TAB_COLUMNS WHERE table_name = 'ALUMNOS4';

Creating and Describing the Ejemplo Table

We drop the existing table and recreate it with specific data types, default values, and a primary key:

DROP TABLE ejemplo;

CREATE TABLE ejemplo (
  col1 NUMBER(6) DEFAULT '0',
  col2 DATE,
  col3 CHAR(1) CHECK (col3 IN ('H','M')),
  PRIMARY KEY (col1)
);

To verify the structure, we can use the following commands:

SELECT * FROM all_tables WHERE table_name = 'EJEMPLO';
SELECT * FROM All_tab_COLUMNS WHERE table_name = 'EJEMPLO';
DESCRIBE EJEMPLO;
DESCRIBE ejemplo;
SELECT * FROM ejemplo;

Recreating the Table Structure

CREATE TABLE ejemplo (
  col1 NUMBER(6) DEFAULT '0',
  col2 DATE,
  col3 CHAR(1) CHECK (col3 IN ('H','M')),
  PRIMARY KEY (col1)
);

Data Insertion and Date Operations

We perform several INSERT operations, testing constraints and using the SYSDATE and ADD_MONTHS functions:

INSERT INTO ejemplo VALUES (1, SYSDATE, 'P');
INSERT INTO ejemplo VALUES (1, SYSDATE, 'H');
SELECT * FROM ejemplo;

Applying Date Range Constraints

ALTER TABLE ejemplo 
ADD CHECK (col2 BETWEEN '01/JAN/2015' 
AND TO_DATE('31/12/2015 23:59:59','dd/mm/yyyy hh24:mi:ss'));

SELECT SYSDATE, ADD_MONTHS(SYSDATE, -3), SYSDATE + 1 AS tomorrow FROM dual;
INSERT INTO ejemplo VALUES (2, ADD_MONTHS(SYSDATE, -3), 'M');
SELECT * FROM ejemplo;

SELECT SYSDATE, ADD_MONTHS(SYSDATE, -4) FROM dual;
INSERT INTO ejemplo VALUES (3, ADD_MONTHS(SYSDATE, -4), 'M');

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 6) FROM dual;
INSERT INTO ejemplo VALUES (4, ADD_MONTHS(SYSDATE, 6), 'H');
SELECT * FROM ejemplo;

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 12) FROM dual;
INSERT INTO ejemplo VALUES (5, ADD_MONTHS(SYSDATE, 12), 'H');

Managing Table Constraints

Adding and dropping specific constraints to control data integrity:

ALTER TABLE ejemplo 
ADD CONSTRAINT sammy2015 CHECK (col1 < 10); > 10); >

INSERT INTO ejemplo VALUES (11, ADD_MONTHS(SYSDATE, 2), 'M');

ALTER TABLE ejemplo 
DROP CONSTRAINT sammy2015;

INSERT INTO ejemplo VALUES (11, ADD_MONTHS(SYSDATE, 2), 'M');
SELECT * FROM ejemplo;
COMMIT;

Modifying Column Constraints

CREATE TABLE ejemplo (
  col1 NUMBER(6) DEFAULT '0',
  col2 DATE,
  col3 CHAR(1) CHECK (col3 IN ('H','M')),
  PRIMARY KEY (col1)
);

ALTER TABLE ejemplo 
MODIFY col3 CHAR(1) CHECK (col3 IN ('H','J','K','L','M'));

INSERT INTO ejemplo VALUES (3, SYSDATE, 'K');

SELECT * FROM all_constraints WHERE table_name = 'EJEMPLO';

ALTER TABLE ejemplo 
DROP CONSTRAINT SYS_C007439; -- En su sistema, puede tener otro nombre

SELECT * FROM all_constraints WHERE table_name = 'EJEMPLO';

INSERT INTO ejemplo VALUES (10, SYSDATE, 'L');
SELECT * FROM ejemplo;

Modifying Columns and Transaction Control

Final adjustments to column defaults and nullability, followed by transaction management:

-------------------------------
ALTER TABLE ejemplo 
MODIFY col2 DATE DEFAULT SYSDATE;

INSERT INTO ejemplo VALUES (9, 'J');
INSERT INTO ejemplo (col1, col3) VALUES (9, 'J');
SELECT * FROM ejemplo;

INSERT INTO ejemplo VALUES (8, '', 'J');
SELECT * FROM ejemplo;

INSERT INTO ejemplo VALUES (7, NULL, 'J');
SELECT * FROM ejemplo;

ROLLBACK;
SELECT * FROM ejemplo;

ALTER TABLE ejemplo 
MODIFY col2 DATE NOT NULL;

INSERT INTO ejemplo (col1, col3) VALUES (7, 'J');
SELECT * FROM ejemplo;

COMMIT;