DbLab3 (create,insert,select,where)

CREATE DATABASE BANK;

USE BANK;

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)
);

CREATE TABLE Customer
(
    Cus_id INT  NOT NULL PRIMARY KEY,
    Emp_id INT FOREIGN KEY REFERENCES employe(Emp_id),
    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');

INSERT INTO Customer(Cus_id,Emp_id,Branch,City)
VALUES (1001,1,'uttara','dhaka');
INSERT INTO Customer(Cus_id,Emp_id,Branch,City)
VALUES (1701,2,'uttara','dhaka');
INSERT INTO Customer(Cus_id,Emp_id,Branch,City)
VALUES (1781,3,'uttara','dhaka');
INSERT INTO Customer(Cus_id,Emp_id,Branch,City)
VALUES (1101,4,'lalbag','barishal');
INSERT INTO Customer(Cus_id,Emp_id,Branch,City)
VALUES (9001,5,'lalbag','barishal');

SELECT *
FROM employe
ORDER BY Emp_name ASC;

SELECT *
FROM employe
ORDER BY Emp_name DESC;

SELECT TOP 2 *
FROM employe
WHERE City LIKE 'd%';

SELECT *
FROM employe
WHERE  Branch LIKE '%tt%';

SELECT *
FROM employe
WHERE Branch IN('uttara','lalbag');

SELECT *
FROM employe
WHERE Salary BETWEEN 10000 AND 20000;

SELECT *
FROM employe
WHERE Salary BETWEEN 100 AND 200;