SQL Lab 2

Group 1 JK001
Date : 27-08-12  Lab No : 2

drop database movies

create database movies

create database hospital_management_sys

create table doctor(doctor_Id int, doctor_name varchar(20), doctor_salery int)

sp_help doctor

sp_tables

alter table doctor add hire_date datetime

alter table doctor add doctor_specialization varchar

alter table doctor drop column doctor_specialization

sp_help doctor

create table test(Id int, name varchar)

sp_help test

drop table test

sp_tables

insert into doctor values (1, 'prab', 3000, '12-02-2011')

select * from doctor

insert into doctor (doctor_id , doctor_salery) values(3,5000)

update doctor set doctor_Id = 10 where doctor_Id = 5

update doctor set doctor_name = 'kaji' where doctor_Id = 10

update doctor set hire_date = '11-04-2011' where doctor_Id = 10

insert into doctor values(2,'guri',10000,'09-01-2010')

------------------------------------------- XXXXXXXXXXXXXX-----------------------------------


create database movies
create table booking(bid int,custname varchar(20),bdate datetime)
sp_help booking
alter table booking add rating int
alter table booking drop column bdate
sp_help booking
create table show(sid int,sname varchar(20))
alter table show add showtime datetime
drop table show
insert into booking values(101,'xyz',5)
insert into booking values(102,'pqr',6)
insert into booking values(103,'lmn',3)
insert into booking values(104,'abc',4)
select * from booking
insert into booking(bid,custname)values(105,'bbb')
select * from booking
insert into booking(bid,rating)values(106,8)
select * from booking
update booking set custname='aaa' where bid=106
select * from booking
update booking set rating=5 where custname='bbb'
select * from booking
delete from booking where rating=5
select * from booking
delete from booking
select * from booking
truncate table booking
select * from booking
drop table booking
select * from booking
create database foodcourt
create table shop1(shopbid int,shopname varchar(20))
insert into shop1 values(20,'indian')
insert into shop1 values(30,'chinese')
insert into shop1 values(40,'thai')
insert into shop1 values(50,'continentel')
select * from shop1
alter table shop1 add area int
select * from shop1
update shop1 set area=5000 where  area=null
select * from shop1

------------------------------------------- XXXXXXXXXXXXXX-----------------------------------
Execution Question


create database building_info
create table cityland (cityland_no int, cityland_area int , cityland_type varchar)
sp_help cityland
alter table cityland add cityland_price float
alter table cityland drop column cityland_type
insert into cityland values (1001,5000,4678.76)
select * from cityland
insert into cityland (cityland_price) values (8695.56)
insert into cityland (cityland_no, cityland_price) values (1003,5643.68)
insert into cityland (cityland_no, cityland_area) values (1004,7000)
update cityland set cityland_no = 1002 where cityland_price = 8695.56
update cityland set cityland_area = 4000 where cityland_price = 8695.56
delete from cityland where cityland_area = 7000
select * from cityland