Query Basic SQL database employee

--------------------------------------------------
--------  01. Add Record                  --------
--------------------------------------------------

-- Recommended
-- Tell the Field Name
INSERT INTO Department (DepartmentNo, DepartmentName)
VALUES (1, 'Restaurant')

INSERT INTO Department (DepartmentNo, DepartmentName)
VALUES (2, 'Bar')

INSERT INTO Department (DepartmentNo, DepartmentName)
VALUES (3, 'Lounge')


-- Not recommended
-- Not telling the Field Name
-- This will be considered as you tell all the Field Name
INSERT INTO ShiftDetail
VALUES (1, 'Lunch', '08:00:00', '15:59:59')

INSERT INTO ShiftDetail
VALUES (2, 'Dinner', '16:00:00', '22:00:00')

INSERT INTO ShiftDetail
VALUES (3, 'Midnite', '00:00:00', '03:59:59')


-- Another way, using Query Select
-- When you want to put some condition
-- Example: Increase number by 1 for EmployeeId when adding
-- Syntax 'Max' is to get the maximum value
-- Syntax 'Coalesce' is to handle null (no value) and you put the replacement for it
-- Query Select below only get the EmployeeId value from Table Employee, the rest value is manually written
INSERT INTO Employee (EmployeeId, FirstName, LastName, DOB, Sex, Address, Phone, JoinDate, HourlyWage, Active, JobTitle, DepartmentNo, ShiftNo)
Select Coalesce(Max(EmployeeId), 0)+1, 'Agatha', 'Christie',  '1976-01-12', 'F', 'New Jersey 50891', '5554961', '2010-01-01', 62500, '1', 'Manager', 1, 1
From Employee

INSERT INTO Employee (EmployeeId, FirstName, LastName, DOB, Sex, Address, Phone, JoinDate, HourlyWage, Active, JobTitle, DepartmentNo, ShiftNo)
Select Coalesce(Max(EmployeeId), 0)+1, 'Ben', 'Hecht',  '1984-02-28', 'M', 'New York 50793', '5557373', '2015-10-01', 31250, '1', 'Staff', 1, 1
From Employee

INSERT INTO Employee (EmployeeId, FirstName, LastName, DOB, Sex, Address, Phone, JoinDate, HourlyWage, Active, JobTitle, DepartmentNo, ShiftNo)
Select Coalesce(Max(EmployeeId), 0)+1, 'Clive', 'Lewis',  '1983-11-22', 'M', 'Los Angeles 50246', '5559449', '2013-06-01', 50000, '1', 'Supervisor', 2, 2
From Employee

INSERT INTO Employee (EmployeeId, FirstName, LastName, DOB, Sex, Address, Phone, JoinDate, HourlyWage, Active, JobTitle, DepartmentNo, ShiftNo)
Select Coalesce(Max(EmployeeId), 0)+1, 'Debra', 'Granik',  '1985-05-05', 'F', 'Las Vegas 50246', '5551234', '2015-06-01', 31250, '1', 'Staff', 2, 2
From Employee


-- When meet field with IDENTITY type, do not tell the Field Name
-- That IDENTITY field will be automatically increased by SQL
-- Example: IDENTITY field for this Table is LogId, so do not tell Field LogId
INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (1, '2020-05-11 08:00:00', '2020-05-11 16:00:00', '')

INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (2, '2020-05-11 08:45:00', '2020-05-11 16:30:00', '')

INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (3, '2020-05-11 16:15:00', '2020-05-12 00:15:00', '')

INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (1, '2020-05-12 08:00:00', '2020-05-12 16:00:00', '')

INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (2, '2020-05-12 07:45:00', '2020-05-12 16:00:00', '')

INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (3, '2020-05-12 16:15:00', '', '')

INSERT INTO EmployeeAttendance (EmployeeId, SignInDate, SignOutDate, Remark)
VALUES (4, '2020-05-12 16:00:00', '', '')




--------------------------------------------------
--------  02. Update Record               --------
--------------------------------------------------

UPDATE ShiftDetail
SET EndTime='23:59:59'
WHERE ShiftNo=2




--------------------------------------------------
--------  03. Delete Record               --------
--------------------------------------------------

DELETE FROM ShiftDetail
WHERE ShiftNo=3




--------------------------------------------------
--------  04. Get Data                    --------
--------------------------------------------------

Select * From ShiftDetail
Select * From Department
Select * From Employee
Select * From EmployeeAttendance


-- Distinct record
Select Distinct Sex From Employee

-- Sort by a field
Select LastName, FirstName From Employee Order By LastName

-- Sort descending, get 1 biggest record
Select Top 1 * From Employee Order By HourlyWage desc


-- Condition '>=' (Greater Than Or Equal)
-- Condition 'And'
-- Condition 'Or'
Select * From Employee Where HourlyWage>=50000 And (DepartmentNo=1 Or ShiftNo=2)


-- Condition '<>' (Not Equal)
-- Condition 'Like'
Select * From Employee Where JobTitle<>'Staff' And FirstName Like '%Aga%'


-- Condition 'Between x And y' (Value Range)
-- Condition 'In' specified values
Select * From Employee Where HourlyWage Between 10000 And 50000 And FirstName In ('Ben', 'Clive')


-- Condition 'Exists' is to check the existence from another table
-- Inside the condition 'Exists', on Query Select, the Field DepartmentName is only a formality, you can tell whatever Field Name you want, it will not be used later
-- Inside the condition 'Exists', on Query Select, at condition 'Where', must have a 'bridge' between Query Select inside the 'Exists' and the main Query Select, the 'bridge' is Field DepartmentNo
-- Syntax 'Case' is like an 'IF-THEN' logic
Select
EmployeeId, FirstName,
Case
When Sex='F' Then 'Female'
When Sex='M' Then 'Male'
Else 'Transgender'
End as Gender
From Employee
Where Exists (Select DepartmentName From Department Where Department.DepartmentNo=Employee.DepartmentNo And DepartmentName='Bar')