Experiment 1 solution

Q. 1 Write a sql query to create and display students table.

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));

Q.3 Write a sql query to create and display courses table.
 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));

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 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.  
(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.)

Share this

vjk