User(UserCode int, first_name varchar(256), last_name varchar(256), last_login_date (date), password varchar(256));
UserHistLogin (UserCode int,


CREATE TABLE Users(
        UserCode int PRIMARY KEY,
        first_name varchar(255),
        last_name varchar(255),
        last_login_date DATETIME,
        password varchar(255)
    );

CREATE TABLE UserHistLogin(
    UserCode int,
    LoginDate DATETIME,
    LoginStatus int,
    PRIMARY KEY (UserCode, LoginDate),
    FOREIGN KEY (UserCode) REFERENCES Users(UserCode)
);

-- Insert dummy data into Users table
INSERT INTO Users (UserCode, first_name, last_name, last_login_date, password)
VALUES
(1, 'John', 'Doe', '2023-11-15 10:00:00', 'password123'),
(2, 'Jane', 'Smith', '2023-11-14 15:30:00', 'securepass'),
(3, 'Alice', 'Johnson', '2023-11-13 08:45:00', 'letmein');

-- Insert dummy data into UserHistLogin table
INSERT INTO UserHistLogin (UserCode, LoginDate, LoginStatus)
VALUES
(1, '2023-11-15 10:00:00', 1), -- Successful login
(1, '2023-11-14 09:30:00', 0), -- Failed login
(2, '2023-11-14 15:30:00', 1), -- Successful login
(3, '2023-11-13 08:45:00', 1); -- Successful login


Assumption: UserCode is int and not string:


SELECT *
FROM Users
JOIN UserHistLogin
ON Users.UserCode
WHERE LoginStatus = 1 AND Users.UserCode = "SHAIJA"
ORDER BY LoginDate DESC
LIMIT 1;


SELECT first_name, last_name
FROM Users
WHERE UserCode IN (
    SELECT UserCode
    FROM UserHistLogin
    WHERE login_status = 0
        AND DATE(login_date) = CURRENT_DATE
    GROUP BY UserCode
    HAVING COUNT(*) >= 3
);