Showing posts with label SQL database employee. Show all posts
Showing posts with label SQL database employee. Show all posts

Tuesday, May 12, 2020

Query advance SQL database employee

0 komentar
--------------------------------------------------
--------  01. Calculation                 --------
--------------------------------------------------

/*
Select * From Employee
Select * From EmployeeAttendance
*/

-- Maximum, Minimum, Total
-- You can put an Alias Name for the Field
-- Example: Field Sum(HourlyWage) will be Field Total_HourlyWage
Select
Max(HourlyWage) as Max_HourlyWage,
Min(HourlyWage) as Min_HourlyWage,
Sum(HourlyWage) as Total_HourlyWage
From Employee


-- Count record
-- Syntax 'Group By' is used for grouping
Select JobTitle, Count(EmployeeId) as Count_Record From Employee Group By JobTitle


----------------------------------------------------------------------------------------------------
-- Declare Variable
Declare @a int, @b int, @x money, @y money

-- Set Value
Set @a = 5
Set @b = 3
Set @x = 5
Set @y = 3

-- Add, Substract, Multiply
Select
@a + @b as Result_Add,
@a - @b as Result_Substract,
@a * @b as Result_Multiply

-- Divide
-- Data type Integer will have no decimal point
-- Data type Money will have decimal point
Select
@a / @b as Divide_Integer,
@x / @y as Divide_Money

-- Modulo adalah sisa bagi
-- 5 dibagi 3 menghasilkan bilangan bulat 1
-- Kemudian sisa baginya adalah 5 - 3 = 2
Select @a % @b as Result_Modulo

-- Round some digit after comma
Select Round(@x / @y, 2) as Round_Money

-- Floor is to round down until no decimal point
Select Floor(@x / @y) as Round_Money
----------------------------------------------------------------------------------------------------




--------------------------------------------------
--------  02. String                      --------
--------------------------------------------------

-- Left take some character from the left string
-- Example: 3 characters from the left string
-- Right take some character from the right string
-- Example: 5 characters from the right string
Select Left(Address, 3) + ' ' + Right(Address, 5) as Alamat From Employee


-- Substring take some character from the starting point to the ending point
-- Len is to count the length of a string
-- Example: The starting point is 5, the ending point is depending on the length of Field Address
Select Substring(Address, 5, Len(Address)) as Alamat From Employee


-- Replace character
Select Replace(JobTitle, 'g', 'j') as Jabatan From Employee


-- Lower & Upper case
Select Lower(FirstName) as FirstName, Upper(LastName) as LastName From Employee




--------------------------------------------------
--------  03. Date                        --------
--------------------------------------------------

-- Convert data type Datetime to a specific format string
-- Format 103 = dd/MM/yyyy
-- Format 108 = HH:mm:ss
-- For complete format string:  www.sqlusa.com/bestpractices2005/centurydateformat
Select FirstName, Convert(varchar(10), DOB, 103) as DOB From Employee
Select EmployeeId, Convert(varchar(8), SignInDate, 108) as SignInTime From EmployeeAttendance Where Convert(varchar(10), SignInDate, 103) = '12/05/2020'


-- Date Basic Syntax
Select
GetDate() as Current_DateTime,
DatePart(year, GetDate()) as Current_Year,
DatePart(month, GetDate()) as Current_Month,
DatePart(day, GetDate()) as Current_Day,
DatePart(weekday, GetDate()) as Current_DayOfTheWeek,
DateName(month, GetDate()) as Current_MonthName,
DateName(weekday, GetDate()) as Current_DayName,
DateAdd(day, 7, GetDate()) as Next_Week


-- DateDiff is to count the difference between starting date and ending date
Select FirstName, DateDiff(year, DOB, GetDate()) as Age From Employee

Query Basic SQL database employee

0 komentar
--------------------------------------------------
--------  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')

 
Trends K N A Copyright © 2009
Fresh Girly Blogger Template Designed by Herro | Powered By Blogger