LAB 3

LAB 3
03-09-12,

create database library
create table book(book_isbn_no int , title varchar(40), author varchar(35), price float)
select * from book
sp_help book
insert into book values(101, NULL, 'Abc', 550.64)
insert into book values(102, 'Database Management', 'XYZ', 456.76)
insert into book values(103, 'Programming in C', NULL, 750.47)
alter table book add quantity int
insert into book (quantity) values(45)
update book set quantity = 45 where author = 'Abc'
delete from book where book_isbn_no is NULL
update book set quantity = 50 where author = 'XYZ'
update book set quantity = 25 where author is NULL
update book set author = 'QRS' where quantity = 25
insert into book values(NULL, NULL, NULL, NULL, NULL)
update book set book_isbn_no = 104 where book_isbn_no is NULL
update book set title = 'Programming in C' where book_isbn_no = 104
update book set author = 'RST' where book_isbn_no = 104
update book set price = 550.67 where book_isbn_no = 104
update book set quantity = 35 where book_isbn_no = 104
select distinct title from book
select book_isbn_no , title from book
select title as BookTitle ,  author "Book Author" from book
select title+author "Book Info" , price from book
update book set title = 'MCP' where quantity = 45
select 'Book having ISBN no ' + cast(book_isbn_no as varchar(10)) + ' with Title '+ title "Book info" from book

---------------------------------------------------------------------------------------
Execution Question

create table staff(staff_id int ,staff_name varchar(20), no_of_books int , salary float)
insert into staff values (1001, 'Abc', 12 , 5000.789)
select * from staff
insert into staff values (1002, 'XYZ', 10 , 6000.769)
insert into staff values (1003, 'QRS', 20 , 7000.989)
insert into staff values (NULL, NULL , NULL , NULL)
update staff set staff_id = 1004 where staff_id is NULL
update staff set salary = 4000.896 where staff_id = 1004
select staff_name " Staff Name" , salary "Salary"  from staff
select 'Staff Id No ' + cast(staff_id as varchar(10)) + ' with name " '+ staff_name + ' " and having salary = ' + cast(salary as varchar(10)) "Salary Info" from staff