Sure, here are the SQL statements for each of the tasks:

1) To create the tables with Primary Key, foreign key constraints in given schemas:

```sql
CREATE TABLE Insurance_Company (
c_id INT PRIMARY KEY,
c_name VARCHAR(255),
city VARCHAR(255),
state VARCHAR(255),
policy_no INT,
policy_name VARCHAR(255),
premium DECIMAL(10, 2),
cust_id INT,
a_id INT
);

CREATE TABLE Agent (
a_id INT PRIMARY KEY,
a_name VARCHAR(255),
address VARCHAR(255),
a_city VARCHAR(255),
a_state VARCHAR(255),
c_name VARCHAR(255),
salary DECIMAL(10, 2),
incentive DECIMAL(10, 2)
);

CREATE TABLE Customer (
cust_id INT PRIMARY KEY,
name VARCHAR(255),
age INT,
cust_address VARCHAR(255),
cust_city VARCHAR(255),
cust_state VARCHAR(255),
a_id INT,
policy_no INT,
FOREIGN KEY (a_id) REFERENCES Agent(a_id),
FOREIGN KEY (policy_no) REFERENCES Insurance_Company(policy_no)
);
```

2) To display the details of customer who have taken policy from ‘Ahmedabad Insurance Company’:

```sql
SELECT *
FROM Customer
JOIN Insurance_Company ON Customer.policy_no = Insurance_Company.policy_no
WHERE Insurance_Company.c_name = 'Ahmedabad Insurance Company';
```

3) To display agent name who have not sold even a single policy:

```sql
SELECT a_name
FROM Agent
LEFT JOIN Insurance_Company ON Agent.a_id = Insurance_Company.a_id
WHERE Insurance_Company.policy_no IS NULL;
```

4) To display the name of Insurance company who has maximum number of customers:

```sql
SELECT c_name
FROM Insurance_Company
JOIN Customer ON Insurance_Company.cust_id = Customer.cust_id
GROUP BY c_name
ORDER BY COUNT(*) DESC
LIMIT 1;
```

Please note that these SQL statements are written assuming that the database is following the schema provided in the question. If the schema is different, these statements may need to be adjusted.

Question

Sure, here are the SQL statements for each of the tasks:

1) To create the tables with Primary Key, foreign key constraints in given schemas:

```sql
CREATE TABLE Insurance_Company (
    c_id INT PRIMARY KEY,
    c_name VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    policy_no INT,
    policy_name VARCHAR(255),
    premium DECIMAL(10, 2),
    cust_id INT,
    a_id INT
);

CREATE TABLE Agent (
    a_id INT PRIMARY KEY,
    a_name VARCHAR(255),
    address VARCHAR(255),
    a_city VARCHAR(255),
    a_state VARCHAR(255),
    c_name VARCHAR(255),
    salary DECIMAL(10, 2),
    incentive DECIMAL(10, 2)
);

CREATE TABLE Customer (
    cust_id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    cust_address VARCHAR(255),
    cust_city VARCHAR(255),
    cust_state VARCHAR(255),
    a_id INT,
    policy_no INT,
    FOREIGN KEY (a_id) REFERENCES Agent(a_id),
    FOREIGN KEY (policy_no) REFERENCES Insurance_Company(policy_no)
);
```

2) To display the details of customer who have taken policy from ‘Ahmedabad Insurance Company’:

```sql
SELECT * 
FROM Customer 
JOIN Insurance_Company ON Customer.policy_no = Insurance_Company.policy_no 
WHERE Insurance_Company.c_name = 'Ahmedabad Insurance Company';
```

3) To display agent name who have not sold even a single policy:

```sql
SELECT a_name 
FROM Agent 
LEFT JOIN Insurance_Company ON Agent.a_id = Insurance_Company.a_id 
WHERE Insurance_Company.policy_no IS NULL;
```

4) To display the name of Insurance company who has maximum number of customers:

```sql
SELECT c_name 
FROM Insurance_Company 
JOIN Customer ON Insurance_Company.cust_id = Customer.cust_id 
GROUP BY c_name 
ORDER BY COUNT(*) DESC 
LIMIT 1;
```

Please note that these SQL statements are written assuming that the database is following the schema provided in the question. If the schema is different, these statements may need to be adjusted.

Knowee AI · Accepted Answer