SQL

SELECT * FROM Nugget
SELECT * FROM Employee
SELECT * FROM [dbo].[Transaction]
SELECT * FROM TransactionDetail
SELECT * FROM LoginLog

SELECT UserName FROM LoginLog WHERE [EmployeeID] = 'EMP004'
SELECT EmployeeID FROM Employee WHERE UserName = 'adit'

SELECT * FROM [dbo].[TransactionDetail]
INSERT INTO  [dbo].[Transaction] VALUES('T003','EMP001','2015-09-17','17000')

drop table [dbo].[Transaction]

truncate table dbo.TransactionDetail
SELECT Nugget.name FROM Nugget WHERE ID = 'N001'

SELECT UserName FROM LoginLog WHERE [EmployeeID] = 'EMP001'

SELECT * FROM Employee WHERE Employee.Position = 'Admin'

UPDATE Nugget SET Name = 'Chicken Nugget', Description = 'Chicken', Stock = '30', Price = '20000' WHERE ID = 'N001'
UPDATE Employee SET Employee.Status = 'Active' WHERE EmployeeID = 'EMP006'
UPDATE Employee SET Employee.Status = 'Non-active' WHERE EmployeeID = 'EMP002'
UPDATE Employee SET UserName = 'rico', Password = 'rico123', FullName = 'ricooo', Gender = 'Male', Position = 'Cashier', Status = 'Active' WHERE EmployeeID = 'EMP002'

SELECT TOP 1 ID FROM NUGGET ORDER BY ID DESC
SELECT TOP 1 TransactionID FROM [dbo].[Transaction] ORDER BY TransactionID DESC
SELECT TOP 1 EmployeeID FROM Employee ORDER BY EmployeeID DESC

SELECT TOP 1 dbo.LoginLog.EmployeeID FROM dbo.LoginLog ORDER BY dbo.LoginLog.EmployeeID DESC

DELETE FROM Nugget WHERE ID = 'N001'
DELETE FROM Employee WHERE EmployeeID = 'EMP002'
DELETE FROM [dbo].[Transaction] WHERE TransactionID = 'T003'
DELETE FROM [dbo].[TransactionDetail] WHERE TransactionID = 'T003'
DELETE FROM LoginLog WHERE UserName = 'asd'

INSERT Nugget VALUES ('N004', 'Tofu Nugget', 'Tofu', '47', '17000')
INSERT Employee VALUES ('EMP001', 'adit', 'adit123', 'adityaa', 'Male', 'Admin', 'Active')
INSERT Employee VALUES ('EMP002', 'rico', 'rico123', 'ricooo', 'Male', 'Cashier', 'Non-active')
INSERT Employee VALUES ('EMP003', 'ica', 'ica123', 'icaaaa', 'Female', 'Cashier', 'Active')
INSERT Employee VALUES ('EMP004', 'asd', 'asd123', 'asdasd', 'Male', 'Cashier', 'Active')
INSERT [dbo].[Transaction] VALUES ('T002', 'EMP001', '2015/09/17', '17000')
INSERT TransactionDetail VALUES ('T002', 'N001', '1')
INSERT LoginLog VALUES ('EMP001', 'adit', '17-7-2017')

SELECT * FROM [dbo].[Transaction] INNER JOIN TransactionDetail ON [dbo].[Transaction].TransactionID = TransactionDetail.TransactionID

SELECT
[dbo].[Transaction].TransactionID AS TransactionID,
[dbo].[Transaction].GrandTotal as GrandTotal,
TransactionDetail.NuggetID as NuggetID,
TransactionDetail.Quantity as Qty
FROM
  [dbo].[Transaction]
  INNER JOIN TransactionDetail ON [dbo].[Transaction].TransactionID=TransactionDetail.TransactionID

SELECT
[dbo].[Transaction].TransactionID AS TransactionID,
Nugget.ID as NuggetID,
Nugget.name AS NuggetName,
Nugget.price AS UnitPrice,
TransactionDetail.Quantity as Qty,
[dbo].[Transaction].GrandTotal as GrandTotal
FROM [dbo].[Transaction]
INNER JOIN TransactionDetail ON [dbo].[Transaction].TransactionID=TransactionDetail.TransactionID
INNER JOIN Nugget ON TransactionDetail.NuggetID = Nugget.ID WHERE ID = 'N001'

create trigger Kurang_Stock
on [dbo].[TransactionDetail]
after insert
as
begin
declare @Qty int
select @Qty = Quantity from inserted
update [dbo].[Nugget] set stock = stock - @Qty
where ID in (select NuggetID from inserted)
end

select * from [dbo].[Nugget]