Question

3. Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints,
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
The relationship between Customer and Loan is Many to Many
Constraint: Primary key, loan_amt should be > 0.
Consider the above tables and execute the following queries:
1. Add Phone_No column in customer table with data type int.
2. Delete the details of customer whose loan_amt<1000.
Consider the above tables and execute the following queries:
1. Find details of all customers whose loan_amt is greater than 10 lakh.
2. List all customers whose name starts with 'D' character.
3. List the names of customer in descending order who has taken a loan from
Pimpri city.​

Answer 1

Answer:

Query1. Display the details of plan who has taken by “Mr. Patil”.

Ans –

select plan.p_no, cname, pname, no_of_freecalls, free_call_time,

fix_amt from plan, customer where plan.p_no=customer.p_no and

cname='Mr. Patil';

Query.2 Update the mobile No of customer to 7020079536 whose

name is “Mr Roy” and plan is “Go Max”.

Ansupdate customer set mobile_no=7020079537 where cname='Mr. Roy'

and p_no='Go Max';

select * from customer where cname='Mr. Roy';

Answer 2

Here is the RDB in 3NF with appropriate data types and constraints for the given entities and their relationships:

Customer:

cust_no (primary key, int)

cust_name (varchar)

address (varchar)

city (varchar)

phone_no (int)

Loan:

loan_no (primary key, int)

loan_amt (int, check (loan_amt > 0))

Customer_Loan:

cust_no (foreign key referencing Customer(cust_no))

loan_no (foreign key referencing Loan(loan_no))

To execute the following queries:

Add Phone_No column in customer table with data type int:

ALTER TABLE Customer ADD Phone_No INT;

• Delete the details of customer whose loan_amt<1000:

DELETE FROM Customer

WHERE cust_no IN (

SELECT cust_no FROM Customer_Loan

INNER JOIN Loan ON Customer_Loan.loan_no = Loan.loan_no

WHERE loan_amt < 1000

);

To execute the following queries:

• Find details of all customers whose loan_amt is greater than 10 lakh:

SELECT * FROM Customer

WHERE cust_no IN (

SELECT cust_no FROM Customer_Loan

INNER JOIN Loan ON Customer_Loan.loan_no = Loan.loan_no

WHERE loan_amt > 1000000

);

• List all customers whose name starts with 'D' character:

SELECT * FROM Customer

WHERE cust_name LIKE 'D%';

• List the names of customer in descending order who has taken a loan from Pimpri city:

SELECT cust_name FROM Customer

WHERE cust_no IN (

SELECT cust_no FROM Customer_Loan

INNER JOIN Loan ON Customer_Loan.loan_no = Loan.loan_no

) AND city = 'Pimpri'

ORDER BY cust_name DESC;

To learn more about SQL from the given link.

https://brainly.in/question/10453

#SPJ2