23)Consider the following Entities and Relationships [30 Marks]
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.
Create a Database in 3NF & write queries for following.
• Display the plan having minimum response.
• Display customer details starting their mobile number with 98.
• Display the customer dfetails that are getting less number of free calls than that of the plan ‘Let’s Rock’.
• Delete the details of ‘John’ who has stopped ‘Go Max’ plan.
• Find the plan whose fixed amount is greater than 5000.
-->
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='goamax';
Find the plan whose fixed amount is greater than 5000.
sql>select pname from plan where fix_amt>5000;
/
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='goamax';
Find the plan whose fixed amount is greater than 5000. sql>select pname from plan where fix_amt>5000;
Search
Recent Posts
Mail Management System
Posted on 2019-07-18Online food ordering system
Posted on 2019-07-18Library Management System
Posted on 2019-07-17Health center system project
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17furniture management system
Posted on 2019-07-17Electronic shop management system
Posted on 2019-07-17Automobile Workshop Management
Posted on 2019-07-17Online Visa Processing System
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17petrol-management system
Posted on 2019-07-17Cloths management system
Posted on 2019-07-17Society Management system
Posted on 2019-07-17Mall management system
Posted on 2019-07-17school management system
Posted on 2019-07-17Sales Order Processing System
Posted on 2019-07-17Retail sales management
Posted on 2019-07-17Raw Materials Management
Posted on 2019-07-17railway reservation system
Posted on 2019-07-17purchase and sales management system
Posted on 2019-07-17Placement Management System
Posted on 2019-07-17Pet Shop Management System
Posted on 2019-07-17petrol pump management system
Posted on 2019-07-17Patient Information System
Posted on 2019-07-17news agency system
Posted on 2019-07-17Cinema Booking System
Posted on 2019-07-17Medical Store System
Posted on 2019-07-17leave management System
Posted on 2019-07-17Laboratory Information Management System
Posted on 2019-07-17content management system
Posted on 2019-07-17Inventory management System
Posted on 2019-07-17Institute Management System
Posted on 2019-07-17Hotel management System
Posted on 2019-07-17Gym Management System
Posted on 2019-07-17Garage Management System
Posted on 2019-07-17Furniture shop management system
Posted on 2019-07-17Fisheries management System
Posted on 2019-07-17Fertilizer scheduling system
Posted on 2019-07-17online eye care system
Posted on 2019-07-17Dental Clinic Management System
Posted on 2019-07-17Cyber Café Management
Posted on 2019-07-17Milk Billing System
Posted on 2019-07-17Colddrink management system
Posted on 2019-07-17Cable management System
Posted on 2019-07-17Beauty parlor management system
Posted on 2019-07-17Facebook Clone
Posted on 2019-05-28Dance Class Management System
Posted on 2019-05-24Library Management System
Posted on 2019-05-24Cab Management System
Posted on 2019-05-23Blood Bank Management system
Posted on 2019-05-23Beauty Parlour Management System
Posted on 2019-05-23vissa proccesing system
Posted on 2019-05-23Toll Plazza
Posted on 2019-05-23BILLING APPLICATION
Posted on 2019-05-23FLORICULTURE MANAGEMENT SYSTEM
Posted on 2019-05-23Car On Rent
Posted on 2019-05-23E-commers Shop
Posted on 2019-05-23