DBMS Practical Slips

DBMS 20 Slips

Q.20) Account_Customer

create table ac

(

a_no number(10)primary key,

b_name varchar(20),

balance number(10)check(balance>500)

);

create table cus

(

cust_no number(10)primary key,

cust_name varchar(20),

street varchar2(20),

city varchar2(20)

);

 

create table ac_cus

(

a_no number(10) references ac(a_no),

cust_no number(10) references cus(cust_no)

);

insert into ac values(10154,'chinchwad',100000);

insert into ac values(98654,'wadgaon',200000);

insert into ac values(23654,'chinchwad',500000);

insert into ac  values(11258,'narhe',300000);

insert into cus values(101,'laxman','abc chowk','pune');

insert into cus values(102,'ankit','abc chowk','mumbai');

insert into cus values(103,'rohit','abc chowk','pune');

insert into cus values(104,'hitesh','abc chowk','mumbai');

insert into ac_cus values(10154,101);

insert into ac_cus values(98654,102);

insert into ac_cus values(23654,103);

insert into ac_cus values(11258,104);

1. select cus.* from ac,cus,ac_cus

where ac.a_no=ac_cus.a_no AND

cus.cust_no=ac_cus.cust_no AND

balance<200000 or

balance>500000;

   CUST_NO CUST_NAME            STREET               CITY

---------- -------------------- -------------------- -------------------

       101 laxman               abc chowk            pune

----------------------------------------------------------------------------------------------------------------------------------

2. select cust_name from ac,cus,ac_cus

where ac.a_no=ac_cus.a_no AND

cus.cust_no=ac_cus.cust_no AND

ac.a_no>2;

CUST_NAME

--------------------

laxman

ankit

rohit

hitesh

---------------------------------------------------------------------------------------------------------------------------------

3. DELETE from ac

where balance<=200000;

4. select cust_name from ac,cus,ac_cus

where ac.a_no=ac_cus.a_no AND

cus.cust_no=ac_cus.cust_no AND

cus.street='abc chowk' AND

cus.city='mumbai';

CUST_NAME

--------------------

ankit

hitesh

-----------------------------------------------------------------------------------------------------------------------------------

5. select cust_name from ac,cus,ac_cus group by b_name;