DbLab3Online (check,identity,primaryKey,foreignKey,add)

CREATE DATABASE BANK1;

USE BANK1;

CREATE TABLE employe
(
    Emp_id INT NOT NULL PRIMARY KEY,
    Emp_name VARCHAR(50),
    Age INT,
    Address VARCHAR(100),
    Salary INT,
    Branch VARCHAR(50),
    City VARCHAR(25)
);

INSERT INTO employe(Emp_id,Emp_name,Age,Address,Salary,Branch,City)
VALUES (1,'a',10,'banani',10000,'uttara','dhaka');
INSERT INTO employe(Emp_id,Emp_name,Age,Address,Salary,Branch,City)
VALUES (2,'b',10,'baridhara',10000,'uttara','dhaka');
INSERT INTO employe(Emp_id,Emp_name,Age,Address,Salary,Branch,City)
VALUES (3,'c',10,'gulshan',10000,'uttara','dhaka');
INSERT INTO employe(Emp_id,Emp_name,Age,Address,Salary,Branch,City)
VALUES (4,'d',10,'banani',10000,'lalbag','barishal');
INSERT INTO employe(Emp_id,Emp_name,Age,Address,Salary,Branch,City)
VALUES (5,'e',10,'banani',10000,'lalbag','barishal');

SELECT *
FROM employe;

ALTER TABLE employe
ADD Emp_rank VARCHAR(100);

CREATE TABLE branch
(
    branch_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    branch_name VARCHAR(100) DEFAULT 'DHAKA',
    Emp_id INT FOREIGN KEY REFERENCES employe(Emp_id) CHECK (Emp_id>0),
    City VARCHAR(25)
);

INSERT INTO branch(branch_name,Emp_id,City)
VALUES ('nobinagar',1,'Dhaka');
INSERT INTO branch(branch_name,Emp_id,City)
VALUES ('nobinagar',5,'Dhaka');
INSERT INTO branch(branch_name,Emp_id,City)
VALUES ('nobinagar',4,'Chittagong');

SELECT *
FROM branch
WHERE branch_name='nobinagar' AND City='Dhaka';