Q. 1 Write a sql query to create and display students table.
create table meet_in(cid varchar(10),rno numeric(4),time varchar(8));
Alter table add/modify/drop columns:
(EXPLANATION-A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created this way.)
alter table students ADD CONSTRAINT stud_login_nn CHECK (login IS NOT NULL); OR
alter table students alter column login set not null;OR
alter table students alter login set not null;
alter table faculty alter fname set not null , alter sal set not null; OR
alter table faculty ADD CONSTRAINT faculty_fname_nn CHECK (fname IS NOT NULL),ADD CONSTRAINT faculty_sal_nn cHECK (sal IS NOT NULL);
alter table courses ADD CONSTRAINT course_cname_nn cHECK (cname IS NOT NULL),ADD CONSTRAINT course_credits_nn cHECK (credits IS NOT NULL); OR
alter table courses alter cname set not null , alter credits set not null;
alter table rooms ADD CONSTRAINT crooms_address_nn CHECK (address IS NOT NULL),ADD CONSTRAINT courses_capacity_nn cHECK (capacity IS NOT NULL); OR
alter table rooms alter address set not null , alter capacity set not null;
Q.11 write a sql query to add column and table level constraints
alter table students add constraint student_sid_pk primary key(sid), add constraint stud_login_uk unique(login);
alter table faculty add constraint faculty_fid_pk primary key(fid);
alter table courses add constraint courses_cid_pk primary key(cid);
alter table rooms add constraint rooms_rno_pk primary key(rno);
alter table enrolled add constraint enroll_grade_ck check(grade in( 'A','B','C','D'));
alter table enrolled add constraint enroll_pk primary key(sid,cid), add constraint enroll_sid_fk foreign key(sid) references students(sid) on delete cascade,add constraint enroll_cid_fk foreign key(cid) references courses(cid);
alter table teaches add constraint teach_pk primary key(fid,cid), add constraint teach_fid_fk foreign key(fid) references faculty(fid) on delete cascade ,add constraint teach_cid_fk foreign key(cid) references courses(cid);
alter table meet_in add constraint meet_in_pk primary key(cid,rno,time), add constraint meet_in_cid_fk foreign key(cid) references courses(cid) on delete cascade,add constraint meet_in_rno_fk foreign key(rno) references rooms(rno) on delete cascade;
create table students (sid varchar(10),name varchar(20),login varchar(10),age numeric(2),gpa numeric(4,2));
Q.2Write a sql query to create and display faculty table.
create table faculty(fid varchar(10),fname varchar(20),sal numeric(8,2));
create table faculty(fid varchar(10),fname varchar(20),sal numeric(8,2));
Q.3 Write a sql query to create and display courses table.
create table courses(cid varchar(10),cname varchar(20),credits numeric(1));
create table courses(cid varchar(10),cname varchar(20),credits numeric(1));
Q.4 Write a sql query to create and display rooms table.
create table rooms(rno numeric(4),address varchar(20),capacity numeric(3));
create table rooms(rno numeric(4),address varchar(20),capacity numeric(3));
Q.5 Write a sql query to create and display enrolled table.
create table enrolled(sid varchar(10),cid varchar(10),grade varchar(1) );
Q.6 Write a sql query to create and display teaches table.
create table teaches(fid varchar(10),cid varchar(10) );
Q.7 Write a sql query to create and display meet_in table.create table teaches(fid varchar(10),cid varchar(10) );
create table meet_in(cid varchar(10),rno numeric(4),time varchar(8));
Alter table add/modify/drop columns:
Q.8 write a sql query to drop a column of a table
alter table faculty drop column fname;
Q.9 write a sql query to add a column to a table
alter table faculty add fname varchar(20);
Q.10 Write a sql table to modify the columns to add null constraint. alter table faculty add fname varchar(20);
(EXPLANATION-A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created this way.)
alter table students ADD CONSTRAINT stud_login_nn CHECK (login IS NOT NULL); OR
alter table students alter column login set not null;OR
alter table students alter login set not null;
alter table faculty alter fname set not null , alter sal set not null; OR
alter table faculty ADD CONSTRAINT faculty_fname_nn CHECK (fname IS NOT NULL),ADD CONSTRAINT faculty_sal_nn cHECK (sal IS NOT NULL);
alter table courses ADD CONSTRAINT course_cname_nn cHECK (cname IS NOT NULL),ADD CONSTRAINT course_credits_nn cHECK (credits IS NOT NULL); OR
alter table courses alter cname set not null , alter credits set not null;
alter table rooms ADD CONSTRAINT crooms_address_nn CHECK (address IS NOT NULL),ADD CONSTRAINT courses_capacity_nn cHECK (capacity IS NOT NULL); OR
alter table rooms alter address set not null , alter capacity set not null;
Q.11 write a sql query to add column and table level constraints
alter table students add constraint student_sid_pk primary key(sid), add constraint stud_login_uk unique(login);
alter table faculty add constraint faculty_fid_pk primary key(fid);
alter table courses add constraint courses_cid_pk primary key(cid);
alter table rooms add constraint rooms_rno_pk primary key(rno);
alter table enrolled add constraint enroll_grade_ck check(grade in( 'A','B','C','D'));
alter table enrolled add constraint enroll_pk primary key(sid,cid), add constraint enroll_sid_fk foreign key(sid) references students(sid) on delete cascade,add constraint enroll_cid_fk foreign key(cid) references courses(cid);
alter table teaches add constraint teach_pk primary key(fid,cid), add constraint teach_fid_fk foreign key(fid) references faculty(fid) on delete cascade ,add constraint teach_cid_fk foreign key(cid) references courses(cid);
alter table meet_in add constraint meet_in_pk primary key(cid,rno,time), add constraint meet_in_cid_fk foreign key(cid) references courses(cid) on delete cascade,add constraint meet_in_rno_fk foreign key(rno) references rooms(rno) on delete cascade;
(EXPLANATION-A foreign key with a cascade delete means that if a record in the parent
table is deleted, then the corresponding records in the child table
with automatically be deleted. This is called a cascade delete.)
