Exe-14

1. Remove employee table from your database. Create table employee( eno, ename, sal). Add
designation column in the employee table with values restricted to a set of values.

Ans.

drop table employee;

create table employee(
eno int,
ename text,
sal int
);

alter table employee add column desg varchar(30) check(desg in ('worker','maneger','staff'));
****************************************

 2. Remove student table from your database. Create table student( student_no, sname,
date_of_birth). Add new column into student relation named address as a text data type with NOT
NULL integrity constraint and a column phone of data type integer.

ans.

drop table student;

create table student(
no int,
sname text,
dob date
);

alter table student add column address text not null;
alter table student add column phone integer;
****************************************
3. Consider the project relation created in the assignment 12. Add a constraint that the project
name should always start with the letter ‘R’

ans.
create table project (
pid int primary key
);

alter table project add column rname text check(substring(rname,1,1)='R');

insert into  project values(1,'R_project1');

****************************************
4.Consider the relation hospital created in assignment 12. Add a column hbudget of type int ,
with the constraint that budget of any hospital should always > 50000.

ans.

ALTER TABLE hospital add column hbudget int check (hbudget>50000);