Consider the following tables where keys are underlined sign

Consider the following tables where keys are underlined: signup(userid, day, password, in, out), user(userid, name, salary). day’s domain is {‘Monday’ ‘Tuesday’, ‘Wednesday’, ’Thursday’ ‘Friday’, ‘Sat’, ‘Sunday’}, and the domain of in and out is {‘Yes’, ‘No’}. Formulate a relational algebra expression for each query in the following. a). Return the names of all users who signed in on Monday. b). Return the names of those users who signed in every day except Monday. c). Return the names of those users who signed in but didn’t sign out on Friday. d). Return the names of all users who signed in on Monday and then signed out on Friday. e). Return the names of all users who worked exactly for two days in a row (signed in on one day and then signed out the next day). Based on the above tables, formulate a SQL statement for each query in the following. f). Delete those users who never signed in from Monday to Friday. g). Increase the user’s salary by 10% if she signed in and signed out every day from Monday to Friday. h). Return the names of the users and the days on which the users signed in but didn’t sign out. i). Increase the user’s salary by 20% if the user is the only user who signed in on a particular day (no other people signed in on that day). j). Return the names of a pair of users (u1, u2)who have exactly the same pattern: a user u1 signed in or signed out on a particular day if and only if the other u2 do so as well.

Solution

f)

DELETE * FROM users
INNER JOIN signup
ON user.userid=signup.userid
WHERE signup.in NOT BETWEEN \'Monday\' and \'Friday\';

------------------------------------------------------------------------------------------------
g)
UPDATE users
INNER JOIN signup ON users.userid=signup.userid
SET salary=(salary+(salary*0.1))
WHERE (signup.in BETWEEN \'Monday\' and \'Friday\') AND (signup.out BETWEEN \'Monday\' and \'Friday);

---------------------------------------------------------------------------------------------------------

h)

SELECT users.names,signup.days FROM users
INNERJOIN signup ON users.userid=signup.userid
WHERE signup.in != signup.out;

----------------------------------------------------------------------------------------------------------