DBMS-Slip23

Slip23                                      

Consider the following Entities and Relationships                                   
Plan (plan_no, plan_name,nooffreecalls,freecalltime,fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many.
Constraint: Primary key.

Solution:-

Create a Database in 3NF & write queries for following.
•Display the plan having minimum response.
SQL>select pno,pname,min(freecalltime) from plan  group by pno,pname;

•Display customer details starting  their mobile number with 98.
SQL>select * from cust   where mbno like'98%'

•Display the customer dfetails that are getting less number of free calls than that of the plan ‘Let’s Rock’.
SQL>select cno,cname,mbno from Cust,Plan where Cust.pno=Plan.pno and nooffreecalls<10 and   pname=’let’;

•Delete the details of ‘John’ who has stopped ‘Go Max’ plan.
SQL>Delete pno from cust where pno=(select pno from plan,cust where cust.pno=plan.pno and pname=’gomax’ and cname=’john’);
SQL>Delete from cust where pno=(select pno from plan where  pname=’gomax‘);
SQL>Delete from plan where pname=’gomax’;

•Find the plan whose fixed amount is greater than 5000.
SQL>select pname from Plan  where fix_amt>5000;