Sample: SQL Query Assignment

Question

Imagine a client database for a governmental organization, for example Workforce Development Division of your local county. As an exercise, write constraints that will prevent a counselor from entering:

• A death date preceding a birth date
• A pregnancy flag for a male
• Authority for welfare to minors granted to senior citizens
• Authority for family subsistence payments for wealthy residents
• Aid to unmarried females when a boyfriend or father shares expenses

Answer

CREATE TABLE organisation(

ID INT NOT NULL,

Birth_Date DATETIME NOT NULL ,

Death_Date DATETIME NOT NULL,

Pregnancy_Flag VARCHAR(30) NOT NULL CONSTRAINT Check_Pregnancy_Flag

CHECK (Pregnancy_Flag ='Female'),

Senior_Citizen_Age VARCHAR(30) NOT NULL CONSTRAINT Check_Senior_Citizen_Age

CHECK (Senior_Citizen_Age >= '60'),

Family_Subsistence_Income VARCHAR(100) NOT NULL CONSTRAINT Check_Family_Subsistence_Income

CHECK (Family_Subsistence_Income >='100000'),

Unmarried_Female_Shares_Expenses VARCHAR(30) NOT NULL CONSTRAINT Check_Unmarried_Female_Shares_Expenses

CHECK ((Unmarried_Female_Shares_Expenses ='boyfriend') or (Unmarried_Female_Shares_Expenses ='father'))

);

Alter table organisation add CONSTRAINT Check_Birth_Date CHECK (DATEDIFF(dd, Birth_Date, Death_Date)>0)