Answer to Question #214284 in Databases | SQL | Oracle | MS Access for Hassam

Question

Write SQL queries for the given statements. Schema given below

 

Projects (CNIC, Name, Start_Date, End_Date, Type)

 

-         Write a trigger which does not allow user to insert data in Projects table unless following constraints are met

a.      CNIC must contain 13 digits

b.     Name must contain only alphabets

c.      Start_Date must be lesser than End_Date

d.     Duration of Project must not be more than 5 months

e.      Type can be either ‘Dev’, ‘Construction’, ‘Management’ or ‘Labor’.

f.       Not more than 3 projects of same type must be inserted.

Answer 1

CREATE TABLE IF NOT EXISTS tasks (
    CNIC INT(13) PRIMARY KEY,
    Name TEXT NOT NULL,
    start_date DATE,
    due_date DATE,
    status TINYINT NOT NULL,
    priority TINYINT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);