Assignemnt 1

CREATE TABLE EMP (
    empid NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    date_of_birth DATE,
    city VARCHAR2(50),
    salary NUMBER
);

-- Inserting Input 1
INSERT INTO EMP (empid, name, date_of_birth, city, salary)
VALUES (101, 'John Smith', TO_DATE('1990-05-15', 'YYYY-MM-DD'), 'New York', 60000);

-- Inserting Input 2
INSERT INTO EMP (empid, name, date_of_birth, city, salary)
VALUES (102, 'Alice Johnson', TO_DATE('1985-11-28', 'YYYY-MM-DD'), 'Los Angeles', 75000);

-- Inserting Input 3
INSERT INTO EMP (empid, name, date_of_birth, city, salary)
VALUES (103, 'Michael Davis', TO_DATE('1988-07-10', 'YYYY-MM-DD'), 'Chicago', 55000);


-- Create table
CREATE TABLE EMP_Constraint1 (
    id VARCHAR2(10) PRIMARY KEY,
    name VARCHAR2(50),
    city VARCHAR2(20) CHECK (city IN ('kolkata', 'delhi', 'mumbai')),
    sal NUMBER(5, 0) CHECK (sal >= 10000 AND sal <= 99999),
    comm NUMBER(4, 0) CHECK (comm >= 0 AND comm <= 9999),
    dob DATE,
    designation VARCHAR2(50) CHECK (designation = LOWER(designation)),
    dependence NUMBER DEFAULT 1,
    CONSTRAINT name_unique UNIQUE (name)
);

-- Insert data
INSERT ALL
    INTO EMP_Constraint (id, name, city, sal, comm, dob, designation, dependence)
    VALUES ('A102', 'John', 'kolkata', 15000, 500, TO_DATE('1999-05-20', 'YYYY-MM-DD'), 'clerk', 1)

    INTO EMP_Constraint (id, name, city, sal, comm, dob, designation, dependence)
    VALUES ('A103', 'Alice', 'mumbai', 28000, 200, TO_DATE('1995-09-12', 'YYYY-MM-DD'), 'manager', 2)

    INTO EMP_Constraint (id, name, city, sal, comm, dob, designation, dependence)
    VALUES ('A104', 'Bob', 'delhi', 21000, 0, TO_DATE('1998-03-05', 'YYYY-MM-DD'), 'analyst', 3)

SELECT 1 FROM DUAL;