download DBMS practical 6
answers for the dbms1 practical 6

--Q1
create table Student(
sno varchar(30) primary key,
stName varchar(30),
address varchar(30),
phone int,
course varchar(30)
);


--Q2
insert into Student values('ST001','Nilusha','Galle',0992333333,'IT')
insert into Student values('ST002','Shan','Colombo',0112345345,'IT')
insert into Student values('ST003','Janani','Kandy',0812222222,'IS')


--Q4
select *
from Student


--Q5
alter table Student
add age int


--Q6
alter table Student
add last_name varchar(30)


update Student
set last_name='Perera'
where sno='ST001'


update Student
set last_name='Fernando'
where sno='ST002'


update Student
set last_name='Alwita'
where sno='ST003'


--Q7
alter table Student
add constraint chk_crs  check (course in ('IT','IS','CSN'))


--Q8
insert into Student values ('ST004','Heshan','Colombo-03',0112789658,'BM','De Silva')


--Q11
alter table Student
drop column age


--Q12


update Student
set phone=0112456789
where sno='ST002'


update Student
set address='Karapitiya'
where sno='ST001'




--Q13
delete
from Student
where sno='ST003'


--Exercise 2
drop table dept
create table dept(
deptno char(3) primary key,
deptname varchar(36),
location varchar(36)
);


create table emp_1(
empno char(6),
firstname varchar(50),
lastname varchar(50),
workdept char(3),
phoneno int,
sex char(1),
birthdate datetime,
salary float,
constraint FK_dno foreign key(workdept)
references dept(deptno)


);






--Q1
insert into dept values('D01','Acount','Malabe')


--Q2
insert into emp_1 values('E001','Nilu','Perera','D01',011345678,'F',1987-10-12,10000)




--Q3
insert into emp_1 values('E002','Saman','De Silva','D02',033226007,'F',1974-05-17,17000)


--Q5
alter table emp_1
drop constraint FK_dno


--Q6
insert into emp_1 values('E002','Saman','De Silva','D02',033226007,'F',1974-05-17,17000)