--Tao co so du lieu
CREATE DATABASE Company ON PRIMARY
    (NAME = 'Company',
    FILENAME = 'D:\Documents\Sen\CSDL\BT\Company.mdf',
    SIZE = 3072KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB)
LOG ON
    (NAME = 'Company_log',
    FILENAME = 'D:\Documents\Sen\CSDL\BT\Company.Ldf',
    SIZE = 1024KB,
    MAXSIZE = 2048KB,
    FILEGROWTH = 10%)
GO
DROP DATABASE Company
GO

USE Company
GO
--Chon bang
SELECT * FROM Employee;
SELECT * FROM Department;
SELECT * FROM DepartmentLocation;
SELECT * FROM Dependent;
SELECT * FROM Project;
SELECT * FROM WorksOn;

--Company Query
--1. List the names of employees whose last name begin N character.
SELECT FName, MInit, LName
    FROM Employee
    WHERE LName LIKE 'N%';
/*
RameshKNarayan
*/

SELECT FName+ ' ' + MInit + ' ' + LName
    FROM Employee
    WHERE LName LIKE 'N%';
/*
Ramesh K Narayan
*/

--2. List employees whose sex is male and has address in Spring or Humble.
SELECT FName, MInit, LName, Sex, Address
    FROM Employee
    WHERE Sex = 'M' AND Address IN ('Sping, TX','Humble, TX');
/*
Ramesh  K   Narayan M   Humble, TX
*/

--5. List the names of employees whose birth date is from 1940 to 1960, sex is female and salary is from 30000 to 50000.
SELECT FName, MInit, LName,BDate, Sex, Salary
    FROM Employee
    WHERE Sex = 'M' AND (BDate BETWEEN '1940-01-01' AND '1960-12-31') AND (Salary>=30000 AND Salary<=50000);

---nnn
SELECT FName+ ' ' + MInit + ' ' + LName
    FROM Employee E, WorksOn W, Project P
    WHERE E.SSN = W.ESSN AND W.PNo = P.PNumber AND PName = 'Computerization';
/*
Franklin T Wong
Ahmad V Jabbar
Alicia J Zelaya
*/
SELECT FName+ ' ' + MInit + ' ' + LName
    FROM Employee E JOIN WorksOn W ON E.SSN = W.ESSN
        JOIN Project P ON W.PNo = P.PNumber
    WHERE PName = 'Computerization';

--3. Retrieve the names of project which does in Houston and managed by department 5.
SELECT PName
    FROM Project P
    WHERE P.PLocation='Houston' AND P.DNum=5

--4. List dependents whose relationship is son and birth date is 1988 year
SELECT  *
    FROM Dependent
    WHERE Relationship='Son' AND BDate LIKE '%1978%'

--7. Find the names of employees that are directly supervised by 'Franklin Wong'