Solutions on Bank Database - Request to all the user to suggest for the corrections and optimization

create database  bank

create table BRANCH(
  bname varchar(15)primary key,
bcity varchar(15),
assets real
  )

insert into BRANCH values('synd_nitte','karkala',200000)
insert into BRANCH values('Corp_nitte','karkala',300000)
insert into BRANCH values('PNB_nitte','karkala',100000)
insert into BRANCH values('Corp_mang','Mangalore',300000)
insert into BRANCH values('PNB_mang','Mangalore',500000)
insert into BRANCH values('state_udupi','Udupi',500000)
insert into BRANCH values('synd_udupi','Udupi',500000)

select * from BRANCH
create table ACCOUNT(
accno int,
bname varchar(15),
balance real,
primary key(accno),
foreign key(bname) references BRANCH(bname) on delete cascade on update cascade
   )


insert into ACCOUNT values(12345,'synd_nitte',6000)
insert into ACCOUNT values(14345,'Corp_nitte',15000)
insert into ACCOUNT values(13345,'PNB_nitte',11000)
insert into ACCOUNT values(13346,'PNB_nitte',11000)
insert into ACCOUNT values(13347,'PNB_nitte',11000)

insert into ACCOUNT values(12340,'synd_nitte',6000)
insert into ACCOUNT values(14341,'Corp_nitte',15000)

insert into ACCOUNT values(15345,'synd_udupi',11000)

insert into ACCOUNT values(21345,'synd_nitte',10000)
insert into ACCOUNT values(21346,'PNB_mang',10000)

insert into ACCOUNT values(13245,'state_udupi',5000)
insert into ACCOUNT values(13241,'state_udupi',5000)


insert into ACCOUNT values(12375,'state_udupi',12000)
insert into ACCOUNT values(12377,'state_udupi',12000)
insert into ACCOUNT values(12378,'state_udupi',12000)

insert into ACCOUNT values(12453,'PNB_mang',17000)
insert into ACCOUNT values(15342,'state_udupi',19000)

insert into ACCOUNT values(12450,'PNB_mang',17000)
insert into ACCOUNT values(13340,'PNB_nitte',11000)

insert into ACCOUNT values(12451,'state_udupi',17000)
insert into ACCOUNT values(12452,'PNB_mang',17000)

insert into ACCOUNT values(12455,'Corp_nitte',17000)

select * from depositor D , account A where D.accno = A.accno and D.cname = 'rakesh'

select * from ACCOUNT
select * from DEPOSITOR


create table CUSTOMER(
cname varchar(20)primary key,
cstreet varchar(25),
ccity varchar(20)
    )


insert into CUSTOMER values('Rakesh','3rd main','karkala')
insert into CUSTOMER values('Ramesh','4th main','karkala')
insert into CUSTOMER values('Rajesh','4th block','mangalore')
insert into CUSTOMER values('Kareem','456 nagar','mangalore')
insert into CUSTOMER values('John smith','452 street','Uudpi')





create table DEPOSITOR(
cname varchar(20),
accno int,
primary key(cname,accno),
foreign key(cname) references CUSTOMER(cname) on delete cascade on update cascade,
foreign key(accno) references ACCOUNT(accno) on delete cascade on update cascade,
unique(accno)
     )

select * from account
insert into DEPOSITOR values('Rakesh',12345)
insert into DEPOSITOR values('Rakesh',13345)
insert into DEPOSITOR values('Rakesh',14345)
insert into DEPOSITOR values('Rakesh',13346)

delete from DEPOSITOR where accno = 12345

insert into DEPOSITOR values('Ramesh',12375)
insert into DEPOSITOR values('Ramesh',12377)
insert into DEPOSITOR values('Ramesh',12378)
insert into DEPOSITOR values('Ramesh',12450)
insert into DEPOSITOR values('Ramesh',13340)
insert into DEPOSITOR values('Ramesh',12451)
insert into DEPOSITOR values('Ramesh',12452)
insert into DEPOSITOR values('Ramesh',12455)

delete from DEPOSITOR where accno = 12450

insert into DEPOSITOR values('Rakesh',15342)
insert into DEPOSITOR values('Rakesh',12340)
insert into DEPOSITOR values('Rakesh',14341)

insert into DEPOSITOR values('Rajesh',15345)
insert into DEPOSITOR values('Rajesh',13241)



insert into DEPOSITOR values('Kareem',21346)
insert into DEPOSITOR values('Kareem',13245)


insert into DEPOSITOR values('John smith',21345)
insert into DEPOSITOR values('John smith',12453)
insert into DEPOSITOR values('John smith',13347)


select * from depositor D , account A where D.accno = A.accno and D.cname = 'rakesh'

select * from customer


select distinct bcity from branch

select * from DEPOSITOR
select * from ACCOUNT


select * from CUSTOMER


create table LOAN (
loanno int,
bname varchar(15),
amount real,
primary key(loanno),
foreign key(bname) references BRANCH(bname) on delete cascade on update cascade

)


insert into LOAN values(1,'Corp_mang',12000)
insert into LOAN values(2,'Corp_mang',11000)
insert into LOAN values(3,'Corp_mang',10000)

insert into LOAN values(4,'Corp_nitte',16000)
insert into LOAN values(5,'Corp_nitte',13000)


insert into LOAN values(6,'PNB_mang',12000)

insert into LOAN values(7,'state_udupi',20000)
insert into LOAN values(8,'state_udupi',23000)

insert into LOAN values(9,'synd_nitte',32000)

insert into LOAN values(10,'PNB_nitte',12000)





select * from LOAN


create table BORROWER(
cname varchar(20),
loanno int
primary key(cname,loanno),
foreign key(cname) references CUSTOMER(cname) on delete cascade on update cascade,
foreign key(loanno) references LOAN(loanno) on delete cascade on update cascade,
unique(loanno)
)


insert into BORROWER values('John smith',1)

insert into BORROWER values('John smith',2)
insert into BORROWER values('John smith',3)

insert into BORROWER values('Kareem',4)
insert into BORROWER values('Kareem',5)
insert into BORROWER values('Rajesh',6)
insert into BORROWER values('Rajesh',7)
insert into BORROWER values('Rajesh',8)

insert into BORROWER values('Rakesh',9)
insert into BORROWER values('Ramesh',10)



select * from BORROWER

select * from BRANCH


1. Find all the customers who have at least two accounts at the Main branch.

select D.cname  from DEPOSITOR D , ACCOUNT A
where D.accno = A.accno and A.bname = 'state_udupi'  group by D.cname having  count(*) >= 2

2A. Find all the customers who have an account at all the branches
located in a specific city.


select C.cname from CUSTOMER  C
where not exists(
select bname from  BRANCH where bcity  = 'karkala' and bname not in

  (select distinct(A.bname) from ACCOUNT A , BRANCH B,DEPOSITOR D
where A.bname = B.bname
and D.accno = A.accno
and B.bcity  = 'karkala'
and D.cname = C.cname )
)



OR

select C.cname from CUSTOMER  C
where not exists(
select B.bname from  BRANCH B where bcity  = 'karkala' and B.bname not in

  (select distinct(A.bname) from ACCOUNT A,DEPOSITOR D
where D.accno = A.accno
and  A.bname = B.bname
and D.cname = C.cname )
)

OR


select C.cname from CUSTOMER  C
where not exists(
  select B.bname from  BRANCH B where  B.bcity  = 'karkala'
   and not exists(
   (select *  from ACCOUNT A ,  DEPOSITOR D
    where  D.accno = A.accno
        and A.bname  = B.bname
    and D.cname = C.cname ))
)





2B. Find all the customers who have atleast 2  accounts at all the branches
located in a specific city.

select C.cname from CUSTOMER  C
where not exists(
select B.bname from  BRANCH B where B.bcity  = 'karkala' and bname not in

  (select A.bname from ACCOUNT A ,DEPOSITOR D
where D.accno = A.accno
and A.bname = B.bname
and D.cname = C.cname  group by A.bname having count(*) >= 2)
)



or

select * from CUSTOMER

Find all the customers who have accounts in atleast 1 branch located in all the cities
select C.cname from CUSTOMER  C
where not exists(
          select distinct(B.bcity)   from  BRANCH B
 where  not exists
                        (
                         
  select A.bname from ACCOUNT A ,DEPOSITOR D
where  D.accno = A.accno
and D.cname =C.cname  and  A.bname  in (select bname from BRANCH where bcity = B.bcity)

)
)




select C.cname from CUSTOMER  C
where  not  exists(  
     
    select distinct(B1.bcity)   from  BRANCH B1
    where not exists(

     select  count( distinct B.bname) from BRANCH B, ACCOUNT A ,DEPOSITOR D
               where A.bname = B.bname
      and D.accno = A.accno
      and B.bcity  = B1.bcity
      and D.cname = C.cname   group by B.bcity having count(*) >=1))



select * from customer
select * from branch


Find all the customers who have accounts in atleast 2 branches located in a specific city.

select C.cname from CUSTOMER  C
where  exists(  
      select  count( distinct B.bname) from BRANCH B, ACCOUNT A ,DEPOSITOR D
               where A.bname = B.bname
      and D.accno = A.accno
      and B.bcity  = 'karkala'
      and D.cname = C.cname   group by B.bcity having count(*) >=2)


Find all the customers who have accounts in atleast 2 branches located in all the cities



select C.cname from CUSTOMER  C
where  not  exists(  
     
    select distinct(B1.bcity)   from  BRANCH B1
    where not exists(

     select  count( distinct B.bname) from BRANCH B, ACCOUNT A ,DEPOSITOR D
               where A.bname = B.bname
      and D.accno = A.accno
      and B.bcity  = B1.bcity
      and D.cname = C.cname   group by B.bcity having count(*) >=2))



select * from customer
select * from branch

select * from BORROWER

select bname from  BRANCH B where B.bcity  = 'karkala'

select L.bname from  BORROWER B , LOAN L   where L.loanno = B.loanno and B.cname = 'Rajesh'


Find the branch name that has maximum number of customers in a specific city

select D.cname, A.bname, count(*) from ACCOUNT A, DEPOSITOR D
where A.accno = D.accno group by D.cname , A.bname


select   A.bname,count(distinct D.cname)   from ACCOUNT A, DEPOSITOR D
where A.accno = D.accno group by  A.bname
having   count(distinct D.cname) >= all (select   count(distinct D.cname)    from ACCOUNT A, DEPOSITOR D
where A.accno = D.accno group by  A.bname)

select * from ACCOUNT

Solutions on book_shop Database - Request to all the user to suggest for the corrections and optimization

create database bk_shop

create table author
(
authorid int primary key,
aname  varchar(20),
city varchar(20),
country varchar(20)

)

insert into author values(111,'sebesta','mangalore','India')
insert into author values(112,'Elmasri','Houston','Canada')
insert into author values(113,'Bharath K','Bangalore','India')
insert into author values(114,'Willy Z','California','USA')
insert into author values(115,'Salma','Dakha','Bangla')



create table publisher
(
pubid int primary key,
pname  varchar(20),
city varchar(20),
country varchar(20)
)


insert into publisher values(201,'McGRAW','mangalore','India')
insert into publisher values(202,'Pearson','Bangalore','India')
insert into publisher values(203,'GKP','Bangalore','India')
insert into publisher values(204,'MediTech','Delhi','India')
insert into publisher values(205,'Sun','Ahmadbad','India')



create table catagory
(
catid int primary key ,
descript varchar(30),

)


insert into catagory values(1,'All children Books')
insert into catagory values(2,'Cooking Books')
insert into catagory values(3,'Popular Novels')
insert into catagory values(4,'Samll Story Books')
insert into catagory values(5,'Medicla Books')



create table catalog
(
bookid int primary key,
title  varchar(20),
pubid int,
authorid int,
catid int,
yr int,
price int,
foreign key(pubid) references publisher(pubid) on delete cascade on update cascade,
foreign key(authorid) references author(authorid) on delete cascade on update cascade,
foreign key(catid) references catagory(catid) on delete cascade on update cascade,
)


select * from publisher

insert into catalog values(301,'Panchatantra',201,111,1,2000,300)
insert into catalog values(302,'Vegetables',202,111,2,2000,400)
insert into catalog values(303,'Yogasana',203,112,5,2002,600)
insert into catalog values(304,'Stories of Village',204,113,4,2005,100)
insert into catalog values(305,'Triangle',205,114,3,2008,1000)

select * from catalog

create table order_det
(
ordno int ,
bookid int,
qty int,
primary key (ordno,bookid),
foreign key(bookid) references catalog(bookid) on delete cascade on update cascade,
)


insert into order_det values(1,301,10)
insert into order_det values(1,302,6)
insert into order_det values(1,303,23)

insert into order_det values(2,301,15)
insert into order_det values(2,304,11)

insert into order_det values(3,304,15)

insert into order_det values(4,301,3)
insert into order_det values(4,305,8)

insert into order_det values(5,303,20)
insert into order_det values(5,304,6)
insert into order_det values(5,305,7)

select * from order_det

1. Give the details of the authors who have 2 or more books in the
catalog and the price of the books is greater than the average
price of the books in the catalog and the year of publication is
after 2000.

select A.authorid,A.aname,A.city from author A, catalog C
where A.authorid  =  C.authorid group by A.authorid, A.aname,A.city
having sum(C.price) > (select avg(price)   from catalog)
and count(*)>=2


2.  Find the author of the book which has maximum sales.

select A.authorid ,A.aname  ,A.city ,C.bookid,sum(O.qty) as QTY_SUM into tb_auth  from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid  group by A.authorid, A.aname,A.city,C.bookid

select * from  tb_auth   where QTY_SUM in (select max(QTY_SUM) from tb_auth)


select A.authorid ,A.aname  ,A.city ,sum(O.qty) as QTY_SUM   from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid  group by A.authorid, A.aname,A.city,C.bookid
having sum(qty) >= all (select sum(qty)  from order_det group by bookid)

(select A.authorid ,A.aname  ,A.city ,C.bookid,sum(O.qty)  from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid)   group by A.authorid, A.aname,A.city,C.bookid
having sum(qty) = (select max(qty)  from temp1 )

create view temp as
select A.authorid ,A.aname  ,A.city ,C.bookid,sum(O.qty) as QTY_SUM  from author A, catalog C,order_det O
where A.authorid  =  C.authorid
and C.bookid = O.bookid  group by A.authorid, A.aname,A.city,C.bookid

select * from  temp   where QTY_SUM = (select max(QTY_SUM) from temp)


select * from  tb_auth   where QTY_SUM in (select max(QTY_SUM) from tb_auth)


3. Increase the price of the books published by a specific publisher by 10%

update  catalog set price = price * 1.1 where pubid in ( select pubid from publisher where pname ='Pearson')


select count(*) as no_of_orders from order_det
where bookid in (

select bookid  from order_det  group by bookid
having sum(qty) >= all (select sum(qty)  from order_det group by bookid)
    )
group by bookid

select * from order_det

Solutions on student_enroll Database - Request to all the user to suggest for the corrections and optimization

create database st_enroll


create table STUDENT (
regno varchar(10),
fname char(15),
major char (20),
bdate datetime
primary key(regno)
   )



insert into STUDENT values ('111','ravi','academic','1989-11-09')
insert into STUDENT values ('112','sudha','academic','1979-07-04')
insert into STUDENT values ('113','kumar','academic','1979-01-06')
insert into STUDENT values ('114','raju','academic','1999-10-02')
insert into STUDENT values ('115','hemanth','academic','1988-11-04')

create table COURSE (
course int,
cname varchar(15),
dept  char (20),
primary key(course)
   )




insert into COURSE values (1,'DBMS','CS')
insert into COURSE values (2,'COMPILER','CS')
insert into COURSE values (3,'JAVA','CS')
insert into COURSE values (4,'SIG PROCESSING','ENC')
insert into COURSE values (5,'DIGTAL CIRCUITS','ENC')
insert into COURSE values (6,'MACHINE DESIGN','MECH')
insert into COURSE values (7,'THEMODYNAICS','MECH')
insert into COURSE values (8,'AUTOCAD','MECH')

select * from COURSE



create table TEXTBOOK (
bookISBN int,
title varchar(50),
publisher  varchar(20),
author  char(20),
primary key (bookISBN)
    )

drop table TEXTBOOK

insert into TEXTBOOK  values (201,'Fundumentals of DBMS','McGraw','NAVATHE')
insert into TEXTBOOK  values (202,'Database Design','McGraw','Raghu Rama')
insert into TEXTBOOK  values (203,'Compiler design','Pearson','Ulman')
insert into TEXTBOOK  values (204,'JAVA complete Reference','McGraw','BALAGURU')
insert into TEXTBOOK  values (205,'Singals and Fundumentals','McGraw','NITHIN')
insert into TEXTBOOK  values (206,'Machine Theory','McGraw','Ragavan')
insert into TEXTBOOK  values (208,'Circuit design','McGraw','Rajkamal')

insert into TEXTBOOK  values (207,'Thermodynamics','McGraw','Alfred')

insert into TEXTBOOK  values (209,'Electronic Circuits','McGraw','Alfred')
insert into TEXTBOOK  values (210,'Circuits Theory','McGraw','Alfred')



select * from TEXTBOOK

create table BOOK_ADAPTION (
course int,
sem int,
bookISBN int,
primary key(course, sem,bookISBN),
foreign key(course) references COURSE(course) on delete cascade on update cascade,
foreign key(bookISBN) references TEXTBOOK (bookISBN) on delete cascade on update cascade,
   )

insert into BOOK_ADAPTION  values (1,5,201)
insert into BOOK_ADAPTION  values (1,7,202)
insert into BOOK_ADAPTION  values (2,5,203)
insert into BOOK_ADAPTION  values (2,6,203)
insert into BOOK_ADAPTION  values (3,7,204)
insert into BOOK_ADAPTION  values (4,3,205)
insert into BOOK_ADAPTION  values (4,5,209)
insert into BOOK_ADAPTION  values (5,5,205)
insert into BOOK_ADAPTION  values (5,6,208)
insert into BOOK_ADAPTION  values (5,2,210)
insert into BOOK_ADAPTION  values (6,7,206)
insert into BOOK_ADAPTION  values (7,3,207)
insert into BOOK_ADAPTION  values (7,3,206)

delete from BOOK_ADAPTION

select * from BOOK_ADAPTION


create table ENROLL (
regno varchar(10),
course  int,
sem int ,
marks int,
primary key(regno,course,sem),
foreign key(regno) references STUDENT(regno)on delete cascade on update cascade,
foreign key(course) references COURSE(course)on delete cascade on update cascade,
   )


drop table ENROLL

drop table BOOK_ADAPTION



insert into ENROLL  values (111,1,5,59)
insert into ENROLL  values (112,1,5,49)
insert into ENROLL  values (113,2,5,80)
insert into ENROLL  values (114,3,7,79)
insert into ENROLL  values (115,4,3,79)

select * from ENROLL


1. Produce a list of text books (include Course #, Book-ISBN,
   Book-title) in the alphabetical order for courses offered by the
   ‘CS’ department that use more than two books.


select A.bookISBN,A.title,B.course,B.cname  from TEXTBOOK A,COURSE B,BOOK_ADAPTION C
where  A.bookISBN = C.bookISBN and B.course=C.course
and B.dept='CS' and B.course in (select course from BOOK_ADAPTION group by course having count(*)>=2)
order by A.title

2. List any department that has all its adopted books published by
   a specific publisher

 

select distinct(C.dept) from course C
   where not exists (
                     

                       select bookISBN from  BOOK_ADAPTION
                       where  course in
                          (select course from  course where dept = C.dept) and bookISBN not in
                                                               
                                                                                (select bookISBN from TEXTBOOK where publisher='McGraw')  
 )


OR

select distinct(C1.dept) from course C1
   where not exists (
                     

                       select B.bookISBN from  BOOK_ADAPTION B , COURSE C
                       where  B.course = C.course
                       and C.dept = C1.dept   and bookISBN not in
                                                               
                                             (select bookISBN from TEXTBOOK where publisher='McGraw')  
 )


     

using count (*)(will not work for this)
-----------


select distinct(C1.dept) from COURSE C1
where  exists (

                   select  count (distinct BA.bookISBN) from COURSE C, BOOK_ADAPTION BA
  where C.course = BA.course and C.dept = C1.dept
  having  count (distinct BA.bookISBN) =   (select count (distinct BA.bookISBN)  from  COURSE C,TEXTBOOK T, BOOK_ADAPTION BA
                                    where C.course = BA.course and T.bookISBN = BA.bookISBN  and  T.publisher='McGraw'
                                    and C.dept = C1.dept)
                )







select * from TEXTBOOK
select * from COURSE
select * from BOOK_ADAPTION
select * from COURSE
select * from enroll


select C.dept , C.course from course C, enroll E
where C.course = E.course group by  C.dept  , C.course

select C.dept,  count(distinct E.regno) from course C, enroll E
where C.course = E.course group by  C.dept
having count (distinct E.regno) >= all(select   count(distinct E.regno) from course C, enroll E where C.course = E.course group by  C.dept )


select C.dept, C.course ,count(distinct B.bookISBN) from course C, BOOK_ADAPTION B
where C.course = B.course group by  C.dept ,C.course

select C.dept,count(distinct B.bookISBN) from course C, BOOK_ADAPTION B
where C.course = B.course group by  C.dept


select C.dept from course C, BOOK_ADAPTION B
where C.course = B.course group by  C.dept

select C.dept, count(distinct E.regno) , count(distinct B.bookISBN) from COURSE C, BOOK_ADAPTION B, ENROLL E
where C.course = E.course and B.course = C.course
and C.dept in
(select C.dept from course C
group by  C.dept
having count(*) > 2)
group by C.dept


create view temp as

1. for each dept list course that adopts maximum number of books

select C.dept, C.course,count(distinct B.bookISBN)  from course C, BOOK_ADAPTION B
where C.course = B.course group by  C.dept ,C.course
having count(distinct B.bookISBN) > = all
                 (select count(distinct B1.bookISBN)  from course C1, BOOK_ADAPTION B1
where C1.course = B1.course and C1.dept = C.dept group by  C1.dept ,C1.course)


select * from temp
drop view temp


select T.dept,T.course from temp T where
T.no_of_books in ( select max(no_of_books) from temp where dept = T.dept)

Solutions on order processing Database - Request to all the user to suggest for the corrections and optimization

create database ord_proc
use ord_proc
CREATE TABLE CUSTOMER (
custid int,
cname char(15) not null,
city varchar(30),
primary key (custid)
)


insert into CUSTOMER values (111,'John Smith', 'Karkala')
insert into CUSTOMER values (112,'Ramesh N', 'Nitte')
insert into CUSTOMER values (113,'Franklin', 'Karkala')
insert into CUSTOMER values (114,'Alica', 'mangalore')
insert into CUSTOMER values (115,'Raju', 'Udupi')

drop table customer
drop table c_order
drop table item
drop TABLE ORDER_ITEM
drop table shipment
drop table warehouse

CREATE TABLE C_ORDER (
orderid int,
odate datetime,
custid int,
ordamt int,
primary key (orderid)  ,
foreign key(custid) references CUSTOMER(custid)on delete cascade on update cascade,
)


insert into C_ORDER values (201,'2001-08-03', 111,null)
insert into C_ORDER values (202,'2002-08-03', 111,null)
insert into C_ORDER values (203,'2001-08-04', 112,null)
insert into C_ORDER values (204,'2004-02-01', 113,null)
insert into C_ORDER values (205,'2001-04-02', 114,null)
insert into C_ORDER values (206,'2005-02-01', 115,null)



update C_ORDER set ordamt = (select sum(O.qty * T.price) from ORDER_ITEM O, ITEM T
                              where O.itemid = T.itemid and O.orderid = 206)

where orderid = 206

select * from C_ORDER

CREATE TABLE ITEM (
itemid  int,
price int,
primary key (itemid)
 )

insert into ITEM values (301,2000)
insert into ITEM values (302,2000)
insert into ITEM values (303,1000)
insert into ITEM values (304,5000)
insert into ITEM values (305,4000)


CREATE TABLE ORDER_ITEM (
orderid int,
itemid int,
qty int,
primary key (orderid,itemid),
foreign key(orderid) references C_ORDER(orderid) on delete cascade on update cascade,
foreign key(itemid) references ITEM(itemid) on delete cascade on update cascade,
)

insert into ORDER_ITEM values (201,302,4)
insert into ORDER_ITEM values (201,303,4)
insert into ORDER_ITEM values (201,304,4)
insert into ORDER_ITEM values (202,303,2)
insert into ORDER_ITEM values (202,305,4)
insert into ORDER_ITEM values (203,302,1)
insert into ORDER_ITEM values (204,305,2)




CREATE TABLE WAREHOUSE (
warehouseid int,
city varchar(20)not null,
primary key (warehouseid)
  )


insert into WAREHOUSE values (1,'MAGALORE')
insert into WAREHOUSE values (2,'MAGALORE')
insert into WAREHOUSE values (3,'MAGALORE')
insert into WAREHOUSE values (4,'UDUPI')
insert into WAREHOUSE values (5,'UDUPI')
insert into WAREHOUSE values (6,'KARKALA')


select * from WAREHOUSE

CREATE TABLE SHIPMENT (
orderid int,
warehouseid int,
ship_dt datetime,
primary key (orderid,warehouseid)  ,
foreign key(orderid) references C_ORDER(orderid) on delete cascade on update cascade,
foreign key(warehouseid) references WAREHOUSE(warehouseid) on delete cascade on update cascade
  )



SELECT * FROM C_ORDER

insert into SHIPMENT values (201,1,'2001-04-02')
insert into SHIPMENT values (201,2,'2001-04-04')
insert into SHIPMENT values (202,1,'2001-05-02')

insert into SHIPMENT values (202,2,'2002-05-12')
insert into SHIPMENT values (202,3,'2003-06-01')
insert into SHIPMENT values (202,4,'2003-06-01')
insert into SHIPMENT values (203,1,'2004-02-01')
insert into SHIPMENT values (203,2,'2004-02-01')
insert into SHIPMENT values (203,3,'2004-02-01')
insert into SHIPMENT values (204,4,'2004-06-02')



SELECT * FROM WAREHOUSE
SELECT * FROM SHIPMENT

select date(ship_dt) from SHIPMENT

SELECT DATEPART(ship_dt,date ) from SHIPMENT

SELECT  left(10,ship_dt ) from SHIPMENT

select ship_dt from  SHIPMENT

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

SELECT GETDATE()

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

SELECT CONVERT(VARCHAR(10),ship_dt,111) as shipdate from SHIPMENT

select datepart(year,month)




1. Produce a listing: CUSTNAME, #oforders, AVG_ORDER_AMT, where the middle column is the total
numbers of orders by the customer and the last column is the average order amount for that customer.

select C.cname , count(O.orderid) as NO_OF_ORDR, avg(O.ordamt) as AVG_ORD_AMT
from CUSTOMER C, C_ORDER O
where C.custid = O.custid group  by C.cname


2.List the order# for orders that were shipped from all the warehouses that the company has in a specific city.

using not in
------------
select O.orderid from C_ORDER O
where not exists (select warehouseid from WAREHOUSE where city = 'MAGALORE' and warehouseid not in
(select warehouseid from SHIPMENT  where orderid = O.orderid)
                 )



select O.orderid from C_ORDER O
where not exists (
                        (select warehouseid from WAREHOUSE where city = 'MAGALORE' and warehouseid not in
(select warehouseid from SHIPMENT  where orderid = O.orderid))
                                         union

(select warehouseid from SHIPMENT  where orderid = O.orderid and   warehouseid not in
(select warehouseid from WAREHOUSE where city = 'MAGALORE'))
                       
                 )




select O.orderid from C_ORDER O
where not exists (select warehouseid from WAREHOUSE where city = 'MAGALORE' and warehouseid not in
(select warehouseid from SHIPMENT  where orderid = O.orderid)
                           
                 )


using count
-----------

select A.orderid from shipment A,warehouse B
where A.warehouseid = B.warehouseid and B.city='MAGALORE' group by A.orderid
having count(*) = (select count(*) from warehouse where city='MAGALORE')


using left outer join
----------------------

select O.orderid from C_ORDER O
where not exists (select orderid  from (
                    (select warehouseid from WAREHOUSE where city = 'MAGALORE') as R1
                        left outer join
                     (select warehouseid, orderid  from SHIPMENT  where orderid = O.orderid) as R2 on  R1.warehouseid = R2.warehouseid)
                    where orderid is null
                 )






3. Demonstrate the deletion of an item from the ITEM table and
demonstrate a method of handling the rows in the
ORDER_ITEM table that contain this particular item.







                   
                     

Solutions on insurance Database - Request to all the user to suggest for the corrections and optimization

create Database Insurance

use Insurance


CREATE TABLE PERSON (
driverid varchar(10),
fname char(15) not null,
address varchar(30),
primary key (driverid)
)


insert into  PERSON values ('111','John Smith' , 'SP Road, Bangalore-12')
insert into  PERSON values ('112','Ramesh Babu' , 'KP Nagar, Udupi -13')
insert into  PERSON values ('113','Raju SK' , 'KS Circle, Mangalore-12')
insert into  PERSON values ('114','Ramesh Babu' , 'AS Road, Bangalore-14')
insert into  PERSON values ('115','Alica wallace' , 'SS Road, Karkala-16')



select * from PERSON



CREATE TABLE CAR (
regno varchar(10),
model varchar(10)not null,
cyear int,
primary key(regno)
)

insert into  CAR values ('KA-12','FORD' ,1980)
insert into  CAR values ('KA-13','SWIFT' ,1990)
insert into  CAR values ('MH-11','INDIGO' ,1998)
insert into  CAR values ('AP-10','SWIFT' ,1988)
insert into  CAR values ('TN-11','FORD' ,2001)
insert into  CAR values ('TN-12','TOYATA' ,2001)
insert into  CAR values ('MH-14','SWIFT' ,2001)
insert into  CAR values ('KL-15','TOYATA' ,2001)
insert into  CAR values ('KL-4','INDIGO' ,2001)
insert into  CAR values ('AP-05','SANTRO' ,2001)

select * from CAR

CREATE TABLE ACCIDENT  (
reportno int ,
accdate datetime,
location varchar(20),
primary key(reportno)
)





insert into  ACCIDENT values (1,'1998-07-22' ,'Nitte')
insert into  ACCIDENT values (2,'1998-07-22','Karkala')
insert into  ACCIDENT values (3,'1998-07-23','Mangalore')
insert into  ACCIDENT values (4,'1990-09-09','Bhatkal')
insert into  ACCIDENT values (5,'2001-02-22' ,'Udupi')
insert into  ACCIDENT values (6,'1990-09-09','Udupi')

select * from ACCIDENT
delete from ACCIDENT

insert into  ACCIDENT values (7,'1981-09-09','Karkala')
insert into  ACCIDENT values (8,'1990-09-09','Bhatkal')
insert into  ACCIDENT values (9,'2001-02-22' ,'Udupi')
insert into  ACCIDENT values (10,'1998-02-02','Udupi')





delete from ACCIDENT


CREATE TABLE OWNS    (
driverid varchar(10) ,
regno varchar(10)
primary key(driverid,regno)
foreign key(driverid) references PERSON(driverid)on delete cascade on update cascade,
foreign key(regno) references CAR(regno)on delete cascade on update cascade,
unique(regno)
   )


drop table OWNS

select * from PERSON
select * from car

insert into  OWNS values ('111','KA-13')
insert into  OWNS values ('111','KA-12')
insert into  OWNS values ('111','MH-11')

insert into  OWNS values ('112','AP-10')
insert into  OWNS values ('112','TN-11')

insert into  OWNS values ('113','TN-12')
insert into  OWNS values ('113','KL-15')

insert into  OWNS values ('114','AP-05')
insert into  OWNS values ('114','KL-4')

insert into  OWNS values ('115','TN-12')
insert into  OWNS values ('115','MH-14')


select * from OWNS

delete from OWNS

drop table PARTCIPATED
CREATE TABLE PARTCIPATED (
  driverid varchar(10) ,
  regno varchar(10),
  reportno  int,
  dmgamt int,
  primary key(driverid,regno,reportno) ,
  foreign key(driverid) references PERSON(driverid)on delete cascade on update cascade,
  foreign key(regno) references CAR(regno)on delete cascade on update cascade,
  foreign key(reportno) references ACCIDENT(reportno)  on delete cascade on update cascade,
  foreign key(driverid,regno) references OWNS(driverid,regno),
  unique(reportno)
)


drop table  PARTCIPATED

select * from accident

insert into  PARTCIPATED values ('111','KA-12',1,20000)
insert into  PARTCIPATED values ('111','KA-12',2,10000)
insert into  PARTCIPATED values ('111','KA-12',3,60000)
insert into  PARTCIPATED values ('111','KA-12',4,60000)
insert into  PARTCIPATED values ('111','KA-12',5,60000)

delete from PARTCIPATED where reportno=5

insert into  PARTCIPATED values ('111','KA-13',6,10000)


insert into  PARTCIPATED values ('112','AP-10',7,30000)
insert into  PARTCIPATED values ('112','TN-11',8,40000)

insert into  PARTCIPATED values ('113','TN-12',9,40000)
insert into  PARTCIPATED values ('113','KL-15',10,50000)

insert into  PARTCIPATED values ('114','AP-05',4,10000)
insert into  PARTCIPATED values ('114','KL-4',6,10000)
insert into  PARTCIPATED values ('114','KL-4',3,70000)
insert into  PARTCIPATED values ('111','KA-12',2,20000)

insert into  PARTCIPATED values ('111','KA-12',5,20000)




drop table PERSON
drop table CAR
drop table ACCIDENT
drop table OWNS
drop table PARTCIPATED


select * from PERSON
select * from CAR
select * from ACCIDENT
select * from OWNS
select * from PARTCIPATED

select distinct(location) from ACCIDENT



1. Find the total number of people who owned cars that were involved in accidents in 1989.

select count (distinct P.driverid)
from accident A, partcipated P
where A.reportno = P.reportno
and A.accdate between  '1998-01-01' and  '1998-12-31'

select count (distinct P.driverid)
from accident A, partcipated P
where A.reportno = P.reportno
and year(A.accdate) = '1998'


select count (distinct P.driverid)
from  partcipated P where P.reportno  in
                                   (
                                        select reportno  from accident
                                        where reportno = P.reportno and year(accdate)  ='1998')



select count (distinct P.driverid)
from  partcipated P where P.reportno  in
                                   (
                                        select reportno  from accident
                                        where  year(accdate)  ='1998')








2a.  Find the number of accidents in which the cars belonging to “John Smith” were involved.


select  count (P.reportno) as NO_OF_ACC
from   partcipated P,  person PN
where P.driverid =  PN.driverid
and   PN.fname = 'John Smith'  
 
2b. Find the number of accidents in which the cars belonging to specific model were involved.

select  count (P.reportno) as NO_OF_ACC
from   partcipated P,  car C
where P.regno =  C.regno
and   C.model  = 'SWIFT'






3. Add a new accident to the database; assume any values for required attributes.


We assume the driver was “Ramesh Babu,” although it could be someone else.
Also, we assume “Ramesh Babu” owns one Toyota. First we must find the license of
the given car. Then the participated and accident relations must be updated
in order to both record the accident and tie it to the given car. We assume
values “Berkeley” for location, ’2001-09-01’ for date and date, 4007 for reportnumber
and 3000 for damage amount.

insert into accident values (7, '2001-09-01', 'Karkala')

insert into partcipated
select O.driverid, C.regno, 7, 100000
from person P, owns O, car C
where P.fname = 'Ramesh Babu'  
and P.driverid = O.driverid
and O.regno = C.regno
and C.model = 'SWIFT'
 
select * from partcipated
select * from accident

4. Delete the Mazda belonging to “John Smith”.

delete from  car
where model = 'INDIGO' and regno in
(select regno
from person P, owns O
where P.fname = 'John Smith' and P.driverid = O.driverid)



5. Update the damage amount for the car with reg number “KA-12” in
the accident with report number “1” to $3000.

update PARTCIPATED  set dmgamt = 29000
where reportno = 1 and  driverid in
(select driverid
from owns
where regno = 'KA-13')



select * from person
select * from car
select * from accident
select * from PARTCIPATED
select * from OWNS

delete from  PARTCIPATED

create view  veiw1  as
select distinct(driverid)
from (
select driverid,regno from OWNS  as T
where not exists
              (
                      select distinct (location) from accident  A
                      where  location not in
                           (
                                select location from accident A1,PARTCIPATED P1
where A1.reportno = P1.reportno and P1.driverid = T.driverid
                                and P1.regno=T.regno
                               
     )
               )    
                         
      )   as T




select driverid ,fname  from  person P
where  exists
 (
select regno from car  where not exists
              (
                      select distinct (location) from accident
                      where  location not in
                           (
                                select location from accident A,PARTCIPATED PT
where A.reportno = PT.reportno and PT.driverid = P.driverid
                               
                               
     )
               )    
                         
 )  



select P.driverid , P.regno , count(*) as no_of_accidents from PARTCIPATED P
where P.driverid  in ( select driverid from OWNS group by driverid having count(*) >= 2)

                 and
 P.regno  in  ( select regno from  PARTCIPATED where driverid = P.driverid group by regno
                     having sum(dmgamt) >= all ( select  sum(dmgamt) from  PARTCIPATED
 where driverid = P.driverid group by regno))
                 
 group by P.driverid , P.regno


select driverid,count(*) from OWNS  group by driverid
select driverid,regno from OWNS   where driverid =112



 



               
select * from person