dbms lab 7

CREATE DATABASE lab7;
USE lab7;

/* credating table */

CREATE TABLE bank(
bank_name VARCHAR(5),
state VARCHAR(20)
);

/* creating account holder table */

CREATE TABLE account_holder(
acc_name VARCHAR(10),
bank_name VARCHAR(5),
state_name VARCHAR(20)
);

/* inserting values into rfespective tables */

INSERT INTO bank VALUES ('SBI','ANDHRA PRADESH');
INSERT INTO bank VALUES ('SBI','TAMIL NADU');
INSERT INTO bank VALUES ('SBI','KARNATAKA');
INSERT INTO bank VALUES ('ICICI','TAMIL NADU');
INSERT INTO bank VALUES ('ICICI','KARNATAKA');

INSERT INTO account_holder VALUES ('RAMESH','ICICI','TAMIL NADU');
INSERT INTO account_holder VALUES ('DINESH','SBI','ANDHRA PRADESH');
INSERT INTO account_holder VALUES ('ROBERT','SBI','TAMIL NADU');
INSERT INTO account_holder VALUES ('ROBERT','ICICI','KARNATAKA');
INSERT INTO account_holder VALUES ('ROBERT','SBI','ANDHRA PRADESH');
INSERT INTO account_holder VALUES ('KARTHIK','SBI','ANDHRA PRADESH');

-- 1st question

/* step 1 : getting all distinct bank names */

CREATE TABLE all_banks AS
SELECT DISTINCT bank_name FROM bank;

SELECT * FROM all_banks;
/* step 1 : getting all distinct account names */
CREATE TABLE all_accounts AS
SELECT DISTINCT acc_name FROM account_holder;

SELECT * FROM all_accounts;

/* natural join btwn account name and bank */

CREATE TABLE all_banks_all_accounts AS
SELECT bank_name,acc_name FROM all_banks,all_accounts;

SELECT * FROM all_banks_all_accounts;

/* removing the common tuples and getting different tuples to remove from our main account holder distinct names */

CREATE TABLE not_needed AS
(SELECT * FROM all_banks_all_accounts WHERE NOT EXISTS (SELECT bank_name,acc_name FROM account_holder
WHERE all_banks_all_accounts.bank_name=account_holder.bank_name AND all_banks_all_accounts.acc_name=account_holder.acc_name));

SELECT * FROM not_needed;

/* this gives the final names who have accounts in all banks */

SELECT acc_name FROM all_accounts
WHERE acc_name NOT IN (SELECT acc_name FROM not_needed);

-- 2nd question

/* creating table and making distinct names */

CREATE TABLE all_states AS
SELECT DISTINCT state_name FROM account_holder;

SELECT * FROM all_states;

/* making natural joins between states and bank names */

CREATE TABLE all_banks_all_states AS
SELECT bank_name,state_name FROM all_banks,all_states;

SELECT * FROM all_banks_all_states;

DROP TABLE not_needed_2;

/* removing the intersection part from the natural join table */

CREATE TABLE not_needed_2 AS
(SELECT * FROM all_banks_all_states WHERE NOT EXISTS (SELECT bank_name,state_name FROM bank
WHERE all_banks_all_states.bank_name=bank.bank_name AND all_banks_all_states.state_name=bank.state));

SELECT * FROM not_needed_2;

SELECT bank_name FROM all_banks
WHERE bank_name NOT IN (SELECT bank_name FROM not_needed_2);

-- 3rd question
/* this question is just negation of the 2nd one */

SELECT bank_name FROM all_banks
WHERE bank_name IN (SELECT bank_name FROM not_needed_2);

-- 4th question

CREATE TABLE all_accounts_all_states AS
SELECT acc_name,state_name FROM all_accounts,all_states;

SELECT * FROM all_accounts_all_states;

CREATE TABLE not_needed_3 AS
(SELECT * FROM all_accounts_all_states WHERE NOT EXISTS (SELECT acc_name,state_name FROM account_holder
WHERE all_accounts_all_states.acc_name=account_holder.acc_name AND all_accounts_all_states.state_name=account_holder.state_name));

SELECT * FROM not_needed_3;

SELECT acc_name FROM all_accounts
WHERE acc_name NOT IN (SELECT acc_name FROM not_needed_3);