Query advance SQL database employee

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

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

SQL Create Table Database Employee

CREATE TABLE Department
(
DepartmentNo int not null default 0,
DepartmentName varchar(20) not null default ''
)

CREATE TABLE ShiftDetail
(
ShiftNo int not null default 0,
ShiftName varchar(20) not null default '',
StartTime varchar(8) not null default '08:00:00',
EndTime varchar(8) not null default '16:00:00'
)

CREATE TABLE Employee
(
EmployeeId int not null default 0,
FirstName varchar(20) not null default '',
LastName varchar(20) not null default '',
DOB datetime not null default '1900-01-01',
Sex varchar(1) not null default 'M',
Address varchar(50) not null default '',
Phone varchar(20) not null default '',
JoinDate datetime not null default '1900-01-01',
HourlyWage money not null default 0,
Active bit not null default '1',
JobTitle varchar(20) not null default '',
DepartmentNo int not null default 0
)

CREATE TABLE EmployeeAttendance
(
LogId int identity(1,1) not null,
EmployeeId int not null default 0,
SignInDate datetime not null default '1900-01-01',
SignOutDate datetime not null default '1900-01-01',
Remark varchar(50) not null default ''
)

CREATE TABLE EmployeeReport
(
ReportNo int not null default 0,
ReportDate datetime not null default '1900-01-01',
EmployeeId int not null default 0,
StartDate datetime not null default '1900-01-01',
EndDate datetime not null default '1900-01-01',
WorkingHour varchar(20) not null default '',
LateMinute int not null default 0
)




--------------------------------------------------
--------  03. Add Field On Table          --------
--------------------------------------------------

ALTER TABLE Employee
ADD ShiftNo int not null default 0




--------------------------------------------------
--------  04. Change Data Type On Field   --------
--------------------------------------------------

ALTER TABLE EmployeeAttendance
ALTER COLUMN Remark varchar(100)

oracle database work

Oracle database is a collection of physical operating system files or disks (in the simplest terms). Oracle instance is set of Oracle background processes or threads and a shared memory area, which is memory that is shared across those threads or processes running on a single computer. There are basically 9 Processes but in a general system we need to mention the first five background processes.They do the housekeeping activities for the Oracle and are common in any system.

The various background processes in oracle.

1) Database Writer(DBWR) :

Database Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is committed.
2)LogWriter(LGWR) :

Log Writer writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
3) System Monitor(SMON) :

The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure
4)Process Monitor(PMON) :

The Process Monitor performs process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
5) CheckPoint(CKPT) :

At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint
6)Archives(ARCH) :

The Archiver copies online redo log files to archival storage when they are busy.
7) Recoverer(RECO) :

The Recoverer is used to resolve the distributed transaction in network
8) Dispatcher (Dnnn) :

The Dispatcher is useful in Multi Threaded Architecture
9) Lckn :

We can have up to 10 lock processes for inter instance locking in parallel sql

How dangerous is the coronavirus 2020

The coronavirus is extremely dangerous, and I suspect that the danger is currently being underplayed by the world medical community to minimize panic as we prepare for the worst.
Covid-19 is deadly, although fatality rates skyrocket for the elderly and those with compromised immune systems.

First and foremost, it is really important to know that as of today (February 17, 2020), there is still much we do not know about SARS-CoV-2 (i.e. the official name of the novel coronavirus) and COVID-19 (i.e. the official name of the disease caused by SARS-CoV-2). This uncertainty makes this question difficult to answer with a short or simple response.

However, epidemiologists and scientists from around the world are making rapid and significant real-time progress in understanding the nature of this novel coronavirus. Earlier today, a comprehensive report from the China CDC was released on over 70,000 individual cases in China, which included data for 99% of confirmed cases globally as of February 11th, 2020.

As I stated at the very beginning, there are still many unknowns out there. That is why there is no simple answer to the question of how dangerous this thing really is. Among others:

• There is no vaccine against COVID-19 (one that can be deployed safely at scale)
• We are still unsure about how contagious this is in people that are infected but asymptomatic
• We do not know how many people are infected, because (very likely) many people are infected and not yet identified for a variety of reasons
• We do not know exactly why infection and/or mortality rates for young people are so much lower
• We do not know how countries with less-developed healthcare systems will be able to cope with potential outbreaks
• SARS-CoV-2 will continue to mutate and evolve (although scientists aren’t that worried about this)
• It’s hard to predict the second-order effects (e.g. policy under/over-reaction, economic disruption, etc.)

What is the difference between DBMS and SQL

DBMS is a system and SQL is a language

DBMS (Database Management System)
A DBMS is a software for creating and managing databases. The DBMS provides users with a systematic way to create, retrieve, update and manage data. It is a middleware between the database which store all the data and the users or applications which need to interact with that stored database. A DBMS can limit what data the end user sees, as well as how that end user can view the data, providing many views of a single database schema

SQL (Structured Query Language)
SQL is the programming language which is used to access this DBMS and the database. It is the language in which you write the command regarding how and what to access in the database. In the figure above, API is what is SQL. The APP or the USER writes SQL commands which are processed by the DBMS

We use the DBMS system along with SQL for querying data in the database.

Now, for knowing the difference, you must understand one thing that is - in SQL we can see the data we want by asking a query, while DBMS optimizes the query and show us the data.

Let’s understand it with the example -

Suppose, you have a table that contains details of different persons such as name, age, gender, mobile number, address etc.

Now, if you want to see the mobile number of a person, you simply gave a query to DBMS, DBMS here starts working, catches query and finally, it will show you the mobile number of that person.

A DBMS (database management system) is a software system that allows you to store and retrieve data in an efficient and organized manner.

SQL is a formal language that allows you to communicate with many DBMS to insert, query, update, delete the content.

A large subset of the DBMS uses SQL, most notably almost all so-called "relational" DBMS. However, there are non-relational DBMS that use SQL (e.g. IDMS), and relational DBMS that don't use SQL (in particular the early versions of Ingres and Postgres)