MS SQL Server Database: Apartments Rental Management
MS SQL Server Database: Apartments Rental Management
Introduction
Microsoft SQL Server is an application used to create enterprise databases. In this exercise, we will use that application to create a project used to manage the rental contract of an apartment building. The application is named Apartments Rental Management.
Practical Learning: Introducing the Application
USE master;
GO
IF DB_ID (N'ApartmentsRentalManagement1') IS NOT NULL
DROP DATABASE ApartmentsRentalManagement1;
GO
CREATE DATABASE ApartmentsRentalManagement1;
GO
USE ApartmentsRentalManagement1;
GO
CREATE TABLE Employees
(
EmployeeId INT IDENTITY(1, 1),
EmployeeNumber NVARCHAR(10),
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
EmploymentTitle NVARCHAR(50),
CONSTRAINT PK_Employees PRIMARY KEY(EmployeeId)
);
GO
CREATE TABLE OccupanciesStatus
(
OccupancyStatusId INT IDENTITY(1, 1),
OccupancyStatus NVARCHAR(25),
CONSTRAINT PK_OccupanciesStatus PRIMARY KEY(OccupancyStatusId)
);
GO
CREATE TABLE Apartments
(
ApartmentId INT IDENTITY(1, 1),
UnitNumber NVARCHAR(5),
Bedrooms INT,
Bathrooms INT,
MonthlyRate INT,
SecurityDeposit INT,
OccupancyStatusId INT,
CONSTRAINT FK_OccupanciesStatus FOREIGN KEY(OccupancyStatusId) REFERENCES OccupanciesStatus(OccupancyStatusId),
CONSTRAINT PK_Apartments PRIMARY KEY(ApartmentId)
);
GO
CREATE TABLE RentalContracts
(
RentalContractId INT IDENTITY(1, 1),
ContractNumber NVARCHAR(10),
EmployeeId INT,
ContractDate NVARCHAR(50),
FirstName NVARCHAR(20),
LastName NVARCHAR(20),
MaritalStatus NVARCHAR(25),
NumberOfChildren INT,
ApartmentId INT,
RentStartDate NVARCHAR(50),
CONSTRAINT FK_ContractsProcessors FOREIGN KEY(EmployeeId) REFERENCES Employees(EmployeeId),
CONSTRAINT FK_Apartments FOREIGN KEY(ApartmentId) REFERENCES Apartments(ApartmentId),
CONSTRAINT PK_RentalContracts PRIMARY KEY(RentalContractId)
);
GO
CREATE TABLE Payments
(
PaymentId INT IDENTITY(1, 1),
ReceiptNumber INT,
EmployeeId INT,
RentalContractId INT,
PaymentDate DATE,
Amount INT,
PaymentForMonth NVARCHAR(20),
PaymentForYear INT,
CONSTRAINT FK_PaymentsProcessors FOREIGN KEY(EmployeeId) REFERENCES Employees(EmployeeId),
CONSTRAINT FK_RentalContracts FOREIGN KEY(RentalContractId) REFERENCES RentalContracts(RentalContractId),
CONSTRAINT PK_Payments PRIMARY KEY(PaymentId)
);
GO
-- ==========================================================================================================
CREATE PROCEDURE LocateResidence
@apartCode int = 101
AS
BEGIN
SET NOCOUNT ON;
SELECT Bedrooms, Bathrooms, MonthlyRate
FROM Apartments
WHERE UnitNumber = @apartCode;
END
GO
CREATE PROCEDURE SummarizeContract
@contracNbr int
AS
BEGIN
SET NOCOUNT ON;
SELECT contracts.ContractNumber,
staff.EmployeeNumber + N' - ' + staff.FirstName + N' ' + staff.LastName + N' (' + staff.EmploymentTitle + N')' AS Processor,
ContractDate,
contracts.FirstName + N' ' + contracts.LastName + N' (' + contracts.MaritalStatus + N' with ' + STR(contracts.NumberOfChildren, 1) + N' child(ren); started on ' + contracts.RentStartDate AS Tenant,
ApartmentId
FROM RentalContracts contracts
INNER JOIN Employees staff ON staff.EmployeeId = contracts.EmployeeId
WHERE contracts.ContractNumber = @contracNbr;
END
GO
-- ================================================================================================================
-- This section is used to create records for the tables.
-- Obviously data entry is performed using the INSERT statement.
-- There are errors made on purpose in some records. Examples are bad names (such as 'Willlkinson'),
-- bad titles (such as 'International' instead of 'Intern'), wrong amounts of payments such as 4850 instead of 850,
-- wrong year such as 3118 instead of 2018, etc.
-- You will have to create UPDATE statements to make the corrections
-------------------------------------------------------------------------------------------------------------------
INSERT INTO Employees(EmployeeNumber, FirstName, LastName, EmploymentTitle)
VALUES(N'93947', N'Catherine', N'Watts', N'Owner - General Manager'),
(N'40685', N'Justine', N'Sandt', N'Rent Manager'),
(N'17205', N'Laura', N'Garner', N'Rent Associate'),
(N'73048', N'Raymond', N'Willlkinson', N'International'), -- The last name of the employee is Wilkinson. Change it.
(N'60949', N'Mark', N'Reason', N'Maintenance Technician'),
(N'38408', N'Marc', N'Knights', N'Rent Associate'),
(N'20448', N'Yanciette', N'Longhorn', N'Rent Associate'), -- The first name of the employee is Nancy. Change it.
(N'70007', N'Laura', N'Vine', N'Unknown'), -- Delete this record
(N'57359', N'Victor', N'Greens', N'Technical Associate');
GO
INSERT INTO OccupanciesStatus(OccupancyStatus)
VALUES(N'Unknown'), (N'Available'), (N'Not Ready'), (N'Needs Maintenance');
GO
INSERT INTO Apartments(UnitNumber, Bedrooms, Bathrooms, MonthlyRate, SecurityDeposit, OccupancyStatusId)
VALUES(N'101', 2, 2, 1350, 1100, 2),
(N'102', 1, 1, 1150, 850, 4),
(N'103', 1, 1, 1150, 850, 2),
(N'104', 3, 2, 1500, 1250, 2),
(N'105', 2, 1, 1250, 1000, 3),
(N'106', 3, 2, 1550, 1250, 2),
(N'107', 3, 2, 1450, 1250, 4),
(N'108', 1, 1, 1100, 4850, 2), -- Check the amount of the security deposit of the apartment of this rental contract
(N'109', 2, 2, 1350, 1100, 2),
(N'110', 1, 1, 1050, 850, 1),
(N'111', 2, 2, 1350, 1100, 4),
(N'112', 2, 1, 1285, 1000, 2),
(N'201', 2, 1, 1185, 1000, 1),
(N'202', 1, 1, 1150, 850, 2),
(N'203', 1, 1, 1150, 850, 2),
(N'204', 3, 2, 1600, 1250, 2),
(N'205', 2, 1, 1100, 1000, 4),
(N'206', 3, 2, 1500, 5250, 2), -- Check the value of the payment that is supposed to be made for this record
(N'207', 3, 2, 1550, 1250, 2),
(N'208', 1, 1, 985, 850, 2),
(N'209', 2, 2, 1350, 1100, 2),
(N'210', 1, 1, 1150, 850, 1),
(N'211', 2, 2, 1350, 1100, 2),
(N'212', 2, 1, 1075, 1000, 2),
(N'301', 2, 2, 1175, 1000, 2),
(N'302', 1, 1, 1150, 850, 4),
(N'303', 1, 1, 1100, 850, 2),
(N'304', 3, 2, 1250, 1100, 2),
(N'305', 2, 1, 1100, 1000, 4),
(N'306', 3, 2, 1250, 1100, 2),
(N'307', 3, 2, 1100, 1250, 2),
(N'308', 1, 1, 1100, 850, 2),
(N'309', 2, 2, 1100, 950, 2),
(N'310', 1, 1, 1100, 850, 2),
(N'311', 2, 2, 1100, 32000, 1), -- Check the monthly payment of the apartment of this rental contract
(N'312', 2, 1, 1100, 1000, 2),
(N'110', 1, 1, 1100, 850, 2); -- Delete this record
GO
INSERT INTO RentalContracts(ContractNumber, EmployeeId, ContractDate, FirstName, LastName, MaritalStatus, NumberOfChildren, ApartmentId, RentStartDate)
VALUES(1001, 6, N'6/12/2018', N'Ann', N'Sanders', N'Married', 1, 9, N'7/1/2018'),
(1002, 7, N'6/15/2018', N'Mahty', N'Shaoul', N'Unknown', 2, 4, N'9/1/2018'),
(1003, 3, N'6/22/2018', N'Frank', N'Ulm', N'Single', 0, 34, N'7/1/2018'),
(1004, 1, N'6/22/2018', N'Elise', N'Provowski', N'Separated', 1, 29, N'8/1/2018');
GO
INSERT INTO RentalContracts(ContractNumber, EmployeeId, ContractDate, FirstName, LastName, NumberOfChildren, ApartmentId, RentStartDate)
VALUES
(1005, 1, N'7/23/3118', N'Grace', N'Curryan', 1, 4, N'9/1/2018');
GO
INSERT INTO RentalContracts(ContractNumber, EmployeeId, ContractDate, FirstName, LastName, MaritalStatus, NumberOfChildren, ApartmentId, RentStartDate)
VALUES(1006, 6, N'7/25/2018', N'Tracy', N'Warrens', N'Divorced', 2, 19, N'8/1/2018'),
(1007, 6, N'8/01/2018', N'Paul', N'Yaomoik', N'Married', 3, 16, N'10/1/2118'), -- The last name of the future tenant is Yamo
(1008, 2, N'8/10/2018', N'Nancy', N'Shermann', N'Single', 1, 8, N'9/1/2022'),
(1009, 7, N'9/12/2019', N'Michael', N'Tiernan', N'Unknown', 0, 21, N'11/1/2019'),
(1010, 6, N'10/05/2019', N'Phillippe', N'Anderson', N'Single', 0, 14, N'11/1/2019'),
(1011, 7, N'9/12/2012', N'Michael', N'Tiernan', N'Married', 8, 8, N'12/1/2020'), -- Delete this record
(1012, 7, N'9/12/2014', N'Roger', N'Robertson', N'Single', 2, 11, N'11/1/2020');
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount, PaymentForMonth, PaymentForYear)
VALUES(418881, 7, N'1/25/2018', 5, 1435, N'January', 2021),
(604158, 4, N'2/24/2018', 5, 1435, N'February', 2021),
(824635, 6, N'3/23/2018', 5, 1435, N'March', 2041), -- This payment was for the year 2021. Update the record
(479557, 6, N'4/22/2018', 5, 1435, N'April', 2021),
(504796, 2, N'5/26/2018', 5, 1435, N'May', 2021);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount)
VALUES(158071, 7, N'6/15/2018', 2, 1250),
(138869, 6, N'6/17/2018', 1, 1100),
(807937, 2, N'6/22/2018', 4, 1000);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount, PaymentForMonth, PaymentForYear)
VALUES(427391, 1, N'6/25/2018', 5, 12435, N'June', 2021); -- The payment was in the amount of 1435. Correct it.
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount)
VALUES(790342, 1, N'6/25/2018', 3, 850),
(219495, 4, N'7/23/2018', 5, 1100);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount, PaymentForMonth, PaymentForYear)
VALUES(361499, 7, N'07/24/2018', 5, 1435, N'July' , 2021),
(988006, 2, N'07/26/2018', 3, 1100, N'July', 2018),
(431426, 1, N'07/27/2218', 6, 1250, N'December', 2128),
(253088, 6, N'07/31/2018', 1, 1350, N'July', 2018), -- This payment was actually made on the 21st
(288181, 1, N'08/01/2018', 6, 1735, N'August', 2022);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount)
VALUES(537824, 7, N'8/1/2018', 7, 1240);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount, PaymentForMonth, PaymentForYear)
VALUES(993583, 1, N'08/29/2018', 5, 1435, N'August', 2021),
(404837, 7, N'08/30/2018', 3, 1100, N'August', 2018),
(752808, 7, N'08/31/2018', 1, 1350, N'August', 2018),
(313504, 1, N'09/26/2018', 2, 1500, N'September', 2018),
(274668, 2, N'09/27/2018', 5, 1435, N'September', 2021), -- This payment was the year 2018. Correct it.
(537299, 6, N'09/27/2008', 3, 1100, N'September', 2018), -- This payment was made in the year 2018. Correct it.
(837405, 1, N'09/28/2018', 1, 1350, N'September', 2018),
(456722, 4, N'10/26/2018', 5, 1435, N'October', 2018),
(969921, 1, N'10/28/2018', 3, 1100, N'October', 2018),
(295751, 7, N'10/29/2018', 2, 1500, N'October', 2018),
(942592, 1, N'10/30/2018', 1, 1350, N'October', 2018),
(518414, 7, N'11/25/2018', 5, 1435, N'November', 2018),
(815386, 2, N'11/25/2018', 4, 1100, N'November', 2018),
(118374, 7, N'11/26/2018', 1, 1350, N'November', 2018),
(723049, 2, N'11/29/2018', 3, 1100, N'November', 2018),
(680538, 7, N'11/30/2018', 2, 1500, N'November', 2018),
(308337, 4, N'12/21/2018', 5, 1435, N'December', 2018),
(772997, 1, N'12/27/2018', 2, 1500, N'December', 2018),
(255738, 7, N'12/29/2018', 4, 1100, N'December', 2018),
(499231, 6, N'12/31/2018', 3, 1100, N'December', 2018),
(830426, 6, N'01/04/2009', 1, 1350, N'December', 2018), -- This payment was made in the year 2018. Correct it.
(152367, 6, N'01/22/2019', 5, 1490, N'January', 2019),
(726837, 6, N'01/26/2019', 3, 1100, N'January', 2019),
(960358, 6, N'01/27/2019', 4, 1155, N'January', 2019),
(139633, 6, N'01/28/2019', 2, 1500, N'January', 2019),
(841914, 2, N'01/31/2019', 7, 1450, N'January', 2019),
(681159, 7, N'02/02/2019', 1, 1350, N'January', 2019),
(604859, 2, N'02/23/2019', 4, 1155, N'February', 2019),
(100726, 2, N'02/26/2019', 5, 1490, N'February', 2022), -- This payment was the year 2019. Correct it.
(306318, 7, N'02/26/2019', 3, 1100, N'February', 2019),
(493631, 7, N'02/27/2019', 2, 1500, N'February', 2019),
(326524, 4, N'02/28/2019', 7, 1450, N'February', 2019),
(140668, 6, N'03/01/2019', 1, 1350, N'February', 2019),
(219682, 1, N'03/22/2019', 7, 1450, N'March', 2019),
(888268, 1, N'03/23/2019', 5, 1490, N'March', 2022), -- This payment was the year 2019. Correct it.
(963608, 7, N'03/24/2019', 4, 1155, N'March', 2019),
(462296, 2, N'03/27/2019', 3, 1100, N'March', 2019),
(174973, 6, N'03/30/2019', 2, 1500, N'March', 2019),
(801418, 6, N'03/31/2019', 1, 1350, N'March', 2019),
(608535, 2, N'04/20/2019', 7, 1450, N'April', 2019),
(403006, 1, N'04/23/2019', 3, 1100, N'April', 2019),
(966283, 2, N'04/24/2119', 4, 1155, N'April', 2219),
(129972, 4, N'04/25/2019', 5, 1490, N'April', 2022), -- This payment was the year 2019. Correct it.
(946440, 7, N'04/27/2019', 2, 1500, N'April', 2019),
(973515, 2, N'04/28/2019', 1, 1350, N'April', 2119),
(158100, 4, N'05/20/2019', 5, 1490, N'May', 2019),
(857058, 7, N'05/25/2019', 1, 1350, N'May', 2019),
(280829, 1, N'05/27/2019', 7, 1450, N'May', 2019),
(136955, 6, N'05/29/2019', 2, 1500, N'May', 2019),
(944443, 6, N'05/30/2019', 3, 1100, N'May', 2019),
(814635, 6, N'05/31/2019', 4, 1155, N'May', 2019),
(513421, 6, N'06/21/2019', 7, 1450, N'June', 2026), -- This payment was the year 2019. Correct it.
(488909, 7, N'06/22/2019', 5, 1490, N'June', 2019),
(953022, 1, N'06/23/2019', 2, 1500, N'June', 2019),
(935368, 7, N'06/29/2019', 3, 1100, N'June', 2019),
(602415, 7, N'06/29/2019', 4, 1155, N'June', 2019),
(661840, 4, N'06/30/2019', 1, 1350, N'June', 2019),
(704135, 2, N'07/21/2019', 5, 1490, N'July', 2022), -- This payment was the year 2019. Correct it.
(796225, 2, N'07/24/2019', 1, 1415, N'July', 2019),
(246041, 4, N'07/25/2019', 2, 1500, N'July', 2029), -- This payment was the year 2019. Correct it.
(630575, 4, N'07/30/2019', 3, 1150, N'July', 2019),
(460707, 1, N'07/31/2019', 4, 1155, N'July', 2019),
(482391, 2, N'08/03/2019', 7, 1450, N'July', 2019),
(693971, 4, N'08/23/2019', 5, 1490, N'August', 2022), -- This payment was the year 2019. Correct it.
(140662, 2, N'08/26/2019', 2, 1500, N'August', 2019),
(529968, 1, N'08/29/2019', 1, 1415, N'August', 2019),
(685860, 2, N'08/30/2019', 4, 1155, N'August', 2019),
(727132, 4, N'08/31/2019', 7, 1450, N'August', 2019),
(158065, 1, N'08/31/2019', 3, 1150, N'August', 2019),
(880551, 4, N'09/25/2019', 5, 1490, N'September', 2022), -- This payment was the year 2019. Correct it.
(705049, 7, N'09/29/2019', 4, 1155, N'September', 2019),
(970473, 6, N'09/30/2019', 1, 1415, N'September', 2019),
(761864, 4, N'09/30/2019', 2, 1565, N'September', 2019),
(392029, 4, N'09/30/2019', 3, 1150, N'September', 2019),
(269136, 1, N'10/02/2019', 7, 1450, N'September', 2019),
(503131, 4, N'10/20/2019', 1, 1415, N'October', 2019),
(627053, 2, N'10/27/2019', 5, 1490, N'October', 2022), -- This payment was the year 2019. Correct it.
(852152, 4, N'10/29/2019', 2, 1565, N'October', 2019),
(135880, 6, N'10/31/2019', 3, 1150, N'October', 2019),
(355188, 2, N'10/31/2009', 4, 1155, N'October', 2019), -- This payment was made in the year 2019. Correct it.
(369335, 6, N'11/04/2019', 7, 1450, N'October', 2019);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount)
VALUES(485506, 6, N'11/21/2019', 5, 1490);
GO
INSERT Payments(ReceiptNumber, EmployeeId, PaymentDate, RentalContractId, Amount, PaymentForMonth, PaymentForYear)
VALUES(596883, 2, N'11/27/2019', 2, 1565, N'November', 2019),
(959997, 4, N'11/28/2019', 1, 1415, N'November', 2019),
(480751, 1, N'11/28/2019', 4, 1155, N'November', 2019),
(275961, 2, N'11/30/2019', 3, 1150, N'November', 2019),
(258268, 4, N'12/01/2019', 7, 1450, N'November', 2019),
(952797, 6, N'12/26/2019', 2, 1565, N'December', 2019),
(951797, 6, N'12/26/2019', 2, 1565, N'December', 2019), --
(260394, 6, N'12/26/2019', 4, 1155, N'December', 2019), -- This and the previous records are for the same payment; delete this one
(914057, 1, N'12/27/2019', 1, 1415, N'December', 2019),
(458724, 2, N'12/27/2019', 5, 1490, N'December', 2021),
(539352, 2, N'12/31/2019', 7, 1450, N'December', 2019),
(279425, 4, N'12/31/2019', 3, 1150, N'December', 2019),
(138529, 7, N'01/19/2020', 7, 1555, N'January', 2020),
(469685, 2, N'01/20/2020', 1, 1415, N'January', 2020),
(202415, 1, N'01/26/2020', 2, 1565, N'January', 2020),
(949402, 7, N'01/29/2020', 4, 1205, N'January', 2020),
(699975, 7, N'01/31/2020', 3, 1150, N'January', 2020),
(702197, 1, N'02/22/2020', 7, 1555, N'February', 2020),
(526147, 4, N'02/26/2020', 2, 1565, N'February', 2020),
(286148, 6, N'02/28/2020', 1, 1415, N'February', 2020),
(588395, 4, N'02/28/2020', 4, 1205, N'February', 2020),
(158075, 6, N'02/29/2020', 3, 1150, N'February', 2020),
(313745, 2, N'03/21/2010', 7, 1555, N'March', 2020), -- This payment was made in the year 2020. Correct it.
(425531, 4, N'03/25/2020', 2, 1565, N'March', 2020),
(283768, 1, N'03/29/2020', 4, 1205, N'March', 2020),
(977902, 7, N'03/31/2020', 1, 1415, N'March', 2020),
(401473, 2, N'03/31/2020', 3, 1150, N'March', 2020),
(327277, 4, N'04/21/2020', 1, 1415, N'April', 2020),
(740029, 2, N'04/24/2020', 2, 1565, N'April', 2020),
(122996, 6, N'04/30/2020', 7, 1555, N'April', 2020),
(447531, 1, N'04/30/2020', 3, 1150, N'April', 2020),
(595037, 2, N'04/30/2020', 4, 1205, N'April', 2020),
(411293, 4, N'05/27/2020', 7, 1555, N'May', 2020),
(693539, 4, N'05/27/2020', 4, 1205, N'May', 2020),
(446355, 6, N'05/29/2020', 2, 1565, N'May', 2020),
(388533, 4, N'05/30/2020', 1, 1415, N'May', 2020),
(682073, 4, N'05/31/2020', 3, 1150, N'May', 2020),
(422275, 2, N'06/27/2020', 2, 1565, N'June', 2020),
(584121, 1, N'06/28/2020', 7, 1555, N'June', 2020),
(528693, 1, N'06/28/2020', 4, 1205, N'June', 2020),
(147179, 4, N'06/30/2020', 3, 1150, N'June', 2020),
(248131, 6, N'07/04/2020', 1, 1415, N'June', 2020),
(976049, 7, N'07/28/2020', 2, 1565, N'July', 2020),
(419192, 7, N'07/31/2020', 7, 1555, N'July', 2020),
(962251, 1, N'07/31/2020', 3, 1205, N'July', 2020),
(465715, 2, N'08/02/2020', 1, 1480, N'July', 2020),
(755369, 7, N'08/02/2020', 4, 1205, N'July', 2020),
(579503, 1, N'08/29/2020', 2, 1565, N'August', 2020),
(944794, 4, N'08/31/2020', 1, 1480, N'August', 2020),
(163842, 2, N'08/31/2020', 7, 1555, N'August', 2020),
(933597, 6, N'08/31/2020', 3, 1205, N'August', 2020),
(530573, 6, N'08/31/2020', 4, 1205, N'August', 2020),
(461882, 2, N'09/20/2020', 2, 1630, N'September', 2020),
(536224, 4, N'09/25/2040', 7, 1555, N'September', 2020), -- This payment was made in the year 2020. Correct it.
(518396, 4, N'09/30/2020', 1, 1480, N'September', 2020),
(333077, 2, N'09/30/2020', 3, 1205, N'September', 2020),
(603885, 2, N'10/01/2020', 4, 1205, N'September', 2020),
(499727, 7, N'10/31/2020', 3, 1205, N'October', 2020),
(311829, 2, N'11/01/2020', 1, 1480, N'October', 2020),
(940035, 4, N'11/05/2020', 4, 1205, N'October', 2020),
(168051, 7, N'11/30/2020', 1, 1480, N'November', 2020),
(926469, 4, N'11/30/2020', 3, 1205, N'November', 2020),
(739597, 1, N'11/30/2020', 4, 1205, N'November', 2020),
(440700, 2, N'12/31/2020', 1, 1480, N'December', 2020),
(992725, 2, N'12/31/2020', 3, 1205, N'December', 2020),
(511188, 4, N'01/03/2021', 4, 1205, N'December', 2020),
(107222, 4, N'01/24/2021', 6, 1735, N'September', 2023), -- This payment was the year 2021. Correct it.
(731844, 1, N'01/25/2021', 1, 1480, N'January', 2021),
(296079, 1, N'01/30/2021', 3, 1205, N'January', 2021),
(616470, 2, N'01/30/2021', 4, 1260, N'January', 2021),
(702285, 7, N'02/26/2021', 3, 1205, N'February', 2021),
(335058, 7, N'02/27/2021', 6, 1735, N'October', 2021),
(411752, 6, N'02/28/2021', 1, 1480, N'February', 2021),
(929739, 7, N'03/01/2021', 4, 1260, N'February', 2021),
(973155, 4, N'03/26/2021', 1, 1480, N'March', 2021),
(158548, 4, N'03/30/2021', 6, 1735, N'November', 2021),
(629271, 2, N'03/30/2021', 3, 1205, N'March', 2021),
(740799, 4, N'03/31/2021', 4, 1260, N'March', 2021),
(703620, 2, N'04/21/2021', 6, 1735, N'December', 2223), -- This payment was the year 2019. Correct it.
(692475, 1, N'04/28/2021', 3, 1205, N'April', 2021),
(406951, 2, N'04/30/2021', 1, 1480, N'April', 2021),
(505068, 1, N'04/30/2021', 4, 1260, N'April', 2021),
(963941, 2, N'05/22/2021', 4, 1260, N'May', 2021),
(635244, 6, N'05/23/2021', 6, 1735, N'January', 2021),
(617050, 2, N'05/27/2001', 1, 1480, N'May', 2011), -- This payment was made in the year 2021 for a month in 2021. Correct it.
(843975, 4, N'05/27/2021', 3, 1205, N'May', 2021),
(747468, 4, N'06/29/2021', 3, 1205, N'June', 2021),
(269266, 7, N'06/30/2021', 1, 1480, N'June', 2021),
(205188, 7, N'06/30/2021', 4, 1260, N'June', 2021),
(402795, 2, N'07/27/2021', 4, 1260, N'July', 2021),
(261597, 2, N'07/31/2021', 1, 1480, N'July', 2021),
(851782, 7, N'07/31/2021', 3, 1255, N'July', 2021),
(216973, 6, N'08/27/2021', 3, 1255, N'August', 2021),
(835506, 1, N'08/31/2021', 1, 1555, N'August', 2021),
(859727, 4, N'08/31/2021', 4, 1260, N'August', 2021),
(993614, 1, N'09/28/2021', 3, 1255, N'September', 2021),
(551614, 6, N'09/29/2021', 4, 1260, N'September', 2021),
(374185, 2, N'10/01/2021', 1, 1555, N'September', 2021),
(111393, 4, N'10/20/2021', 4, 1260, N'October', 2021),
(714151, 4, N'10/26/2021', 3, 1255, N'October', 2021),
(622997, 7, N'10/30/2021', 1, 1555, N'October', 2021),
(286385, 2, N'11/30/2021', 3, 1255, N'November', 2021),
(572427, 2, N'11/30/2021', 4, 1260, N'November', 2021),
(300083, 4, N'12/05/2021', 1, 1555, N'November', 2021),
(418410, 2, N'12/24/2021', 4, 1260, N'December', 2021),
(705536, 1, N'12/28/2021', 3, 1255, N'December', 2021),
(976696, 6, N'01/05/2022', 1, 1555, N'December', 2021),
(259942, 1, N'01/29/2022', 4, 1305, N'January', 2022),
(473305, 1, N'01/31/2022', 1, 1555, N'January', 2022),
(464493, 2, N'01/31/2022', 3, 1255, N'January', 2022),
(944885, 7, N'02/26/2022', 3, 1255, N'February', 2022),
(884606, 4, N'02/27/2022', 4, 1305, N'February', 2022),
(229949, 4, N'03/03/2022', 1, 1555, N'February', 2022),
(952533, 7, N'03/30/2022', 4, 1305, N'March', 2022),
(746063, 2, N'03/31/2022', 1, 1555, N'March', 2022),
(796602, 4, N'03/31/2022', 3, 1255, N'March', 2022),
(595880, 4, N'04/20/2022', 1, 1555, N'April', 2022),
(462697, 2, N'04/21/2022', 4, 1305, N'April', 2022),
(935008, 4, N'04/28/2022', 3, 1255, N'April', 2022),
(853692, 2, N'05/29/2022', 1, 1555, N'May', 2022),
(178607, 1, N'05/29/2022', 3, 1255, N'May', 2022),
(583966, 1, N'05/31/2026', 4, 1305, N'May', 2022), -- This payment was made in the year 2022. Correct it.
(226405, 4, N'06/21/2022', 4, 1305, N'June', 2022),
(229944, 6, N'06/27/2022', 3, 1255, N'June', 2022),
(959953, 4, N'06/28/2022', 1, 1555, N'June', 2022),
(844807, 4, N'07/25/2022', 3, 1305, N'July', 2022),
(455033, 4, N'07/26/2022', 1, 1555, N'July', 2022),
(964673, 4, N'07/31/2022', 4, 1305, N'July', 2022),
(244205, 6, N'08/29/2022', 4, 1305, N'August', 2022),
(222929, 4, N'08/31/2022', 1, 1625, N'August', 2022),
(635582, 4, N'08/31/2022', 3, 1305, N'August', 2022),
(258533, 2, N'09/28/2022', 3, 1305, N'September', 2022),
(440350, 7, N'09/28/2022', 4, 1305, N'September', 2022),
(841492, 6, N'09/29/2022', 1, 1625, N'September', 2022),
(463931, 1, N'10/20/2022', 4, 1305, N'October', 2022),
(960499, 7, N'10/26/2022', 3, 1305, N'October', 2022),
(693713, 4, N'11/03/2022', 1, 1625, N'October', 2022),
(483777, 4, N'11/30/2022', 3, 1305, N'November', 2022),
(662026, 2, N'11/30/2022', 4, 1305, N'November', 2022),
(755511, 1, N'12/05/2022', 1, 1625, N'November', 2022),
(774826, 2, N'12/30/2022', 3, 1305, N'December', 2022),
(153947, 2, N'12/30/2022', 4, 1305, N'December', 2022),
(470605, 2, N'10/03/2023', 1, 1625, N'October', 2022), -- This payment was made in January 2023 for the rent payment of the month of December (of the previous year). Correct it.
(979186, 2, N'01/31/2023', 1, 1625, N'January', 2023),
(474157, 6, N'01/31/2023', 3, 1305, N'January', 2023),
(961629, 4, N'02/27/2023', 1, 1625, N'February', 2023),
(669705, 1, N'02/27/2023', 3, 1305, N'February', 2023),
(300484, 4, N'03/28/2023', 3, 1305, N'March', 2023),
(682497, 4, N'03/31/2023', 1, 1625, N'March', 2023),
(620607, 4, N'04/30/2023', 1, 1625, N'April', 2023),
(842995, 4, N'04/30/2023', 3, 1305, N'April', 2023),
(338044, 6, N'05/29/2023', 3, 1305, N'May', 2023),
(261607, 6, N'05/30/2023', 1, 1625, N'May', 2023),
(429240, 6, N'06/27/2023', 1, 1625, N'June', 2023),
(587944, 2, N'06/27/2023', 3, 1305, N'June', 2023),
(397240, 4, N'07/25/2023', 3, 1360, N'July', 2023),
(407229, 2, N'07/31/2023', 1, 1625, N'July', 2023),
(516481, 1, N'08/26/2023', 3, 1360, N'August', 2023),
(974942, 1, N'08/31/2023', 1, 1705, N'August', 2023),
(226414, 2, N'09/29/2026', 1, 1705, N'September', 2023),
(148226, 2, N'09/30/2023', 3, 1360, N'September', 2023),
(418318, 7, N'10/29/2023', 3, 1360, N'October', 2023),
(596197, 7, N'11/03/2023', 1, 1705, N'October', 2023),
(746479, 4, N'11/19/2023', 1, 1705, N'November', 2023),
(453520, 4, N'11/29/2023', 3, 1360, N'November', 2023),
(983177, 4, N'11/30/2023', 1, 1705, N'December', 2023),
(259959, 4, N'12/29/2023', 1, 1360, N'December', 2023);
GO
-- ================================================================
UPDATE Employees
SET LastName = N'Wilkinson'
WHERE EmployeeNumber = N'73048';
GO
---------------------------------
UPDATE Employees
SET FirstName = N'Nancy'
WHERE EmployeeNumber = N'20448';
GO
------------------------------------------
DELETE FROM Employees
WHERE EmployeeNumber = N'70007';
GO
-- ================================================================
SELECT ALL * FROM Employees;
GO
------------------------------------------|
|
|||
| Home | Copyright © 2007-2022, FunctionX | Wednesday 04 May 2022 | Home |
|
|
|||