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