From the table below I need to write an SQL query, such that each user that has ever created a role will return 4 values:
-
User name in the UserName column;
-
No of created roles in NoOfCreated column;
-
No of roles enabled in NoOFEnabledRoles column
-
No of updated roles in theNoO
fUpdatedRole column.
Sorting: by username in descending order. also username should return no leading or trailing white spaces and should be in uppercase.
Query shouldn't return any null value for numerical columns, null should be replaced by -1(incase no role are updated by user,or no roles are created by a given user that are enabled).
Note: values in created at updated at are not consistent
CREATE TABLE UserRole(
Id bigint NOT NULL AUTO_INCREMENT,
Name varchar(100) NOT NULL,
Description varchar(200) NULL,
IsEnabled bit NOT NULL, -- 1 if a role is enabled, 0 otherwise
Created date NOT NULL, -- When a role was created
CreatedBy varchar(200) NOT NULL, -- Who created a role
Updated date NULL, - When a role was updated (if at all)
UpdatedBy varchar(200) NULL, - Who updated a role (if at all)
CONSTRAINT PK_UserRole PRIMARY KEY ( Id ASC )
)
Can anyone please help me with the query thanks in advance!