(DBLab6-2-Online)

CREATE DATABASE MYDATABASE

USE MYDATABASE
CREATE TABLE customer
(
   cus_id VARCHAR(5) PRIMARY KEY,
   cus_name VARCHAR(20) UNIQUE,
   city VARCHAR(10) NULL,
   street VARCHAR(20) NULL,
);

USE MYDATABASE
CREATE TABLE account
(
   acc_id VARCHAR(5) PRIMARY KEY,
   branch VARCHAR(10) NOT NULL CHECK(branch='dhanmondi' OR branch='gulshan' OR branch='mohakhali'),
   balance FLOAT DEFAULT 500 NOT NULL,
   cus_id VARCHAR(5) FOREIGN KEY REFERENCES customer(cus_id),



);

USE MYDATABASE
CREATE TABLE dispositor
(

   cus_id VARCHAR(5) FOREIGN KEY REFERENCES customer(cus_id),
   acc_id VARCHAR(5) FOREIGN KEY REFERENCES account(acc_id),


);


USE MYDATABASE

INSERT INTO customer (cus_id,cus_name,city, street) VALUES ('1', 'Rahim', 'Dhaka', 'Big Road');
INSERT INTO customer (cus_id,cus_name,city, street) VALUES ('2', 'Karim', 'Dhaka', 'Small Road');
INSERT INTO customer (cus_id,cus_name,city, street) VALUES ('3', 'Hakim', 'Chittagong', 'Court Road');
INSERT INTO customer (cus_id,cus_name,city, street) VALUES ('4', 'Masum', 'Khulna', 'Bhuiyan Road');
INSERT INTO customer (cus_id,cus_name,city, street) VALUES ('5', 'Rahman', 'Chittagong', 'Sarak Road');
INSERT INTO customer (cus_id,cus_name,city, street) VALUES ('6', 'Kaneki', 'CTokyo', 'Anteiku');

INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10101', 'dhanmondi', 1000.00, '1');
INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10102', 'gulshan', 2000.00, '2');
INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10103', 'mohakhali', 10000.00, '3');
INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10107', 'mohakhali', 10440.00, '3');
INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10104', 'dhanmondi', 20000.00, '4');
INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10105', 'gulshan', 50000.00, '5');
INSERT INTO account(acc_id,branch,balance,cus_id) VALUES ('10106', 'gulshan', 30000.00, '5');

INSERT INTO dispositor(cus_id,acc_id) VALUES ('1', '10101');
INSERT INTO dispositor(cus_id,acc_id) VALUES ('2', '10102');
INSERT INTO dispositor(cus_id,acc_id) VALUES ('3', '10103');
INSERT INTO dispositor(cus_id,acc_id) VALUES ('4', '10104');
INSERT INTO dispositor(cus_id,acc_id) VALUES ('5', '10105');

SELECT acc_id,balance FROM account WHERE branch='dhanmondi';

SELECT customer.cus_name , AVG(account.balance) AS 'avgBalance' FROM account FULL JOIN customer ON account.cus_id=customer.cus_id GROUP BY customer.cus_name;

SELECT account.branch , customer.city FROM account FULL JOIN customer ON account.cus_id=customer.cus_id WHERE customer.cus_name LIKE '%i%';

SELECT * FROM account LEFT JOIN customer ON account.cus_id=customer.cus_id;

SELECT * FROM (dispositor JOIN account ON dispositor.cus_id=account.cus_id) JOIN customer ON (account.cus_id=customer.cus_id);