MS Excel

Homework?

ya stuck badly!

give some hint as to how to proceed..

OK, please see this EE article on the homework policy.

Taken from the attachment within the opening comment of this question:
---
We have a database with 3 tables Users, Roles and UserRoles that will be referenced throughout the below questions:
An example of the data in each table is below:


1.      Describe in non-technical words the relationship between each table.

2.      What is the purpose of the UserRoles table, what is this kind of table called?

3.      Write a query which displays the First and Last Name for all users who belong to the Role “Lumo Advantage”.

4.      Write a query which displays the First and Last Name in a single column, for all users that don’t have a Role.

5.      Write a query which lists all the Roles and how many users below to each role where the Role has less than 20 users in it.    
---

That is fine. I am not asking anyone to do something on my behalf. But they can always guide me.

Ritwik Mukerjee:

A comment from a Moderator on the subject of academic "homework" in a question posted within a different Topic Area today:

[ https://www.experts-exchange.com/questions/28711681/Need-help-with-function.html?anchorAnswerId=40965066#a40965066 ]

@[fanpages] I could not understand what is the point in copying and pasting the question.

"That is fine. I am not asking anyone to do something on my behalf. But they can always guide me."

OK... where are you stuck & not able to progress?

Have you made any attempt to answer the questions? If so please don't be embarrassed and show us what you have so far.

"@[fanpages] I could not understand what is the point in copying and pasting the question."

Not everybody reading this thread across many devices will have the ability to open an MS-Word 2007-2013 format document.

I posted the content of the document to maximise your chance of input from contributing "Experts".

[fanpages] Ok I understand

See I tried to proceed as follows:

1) Seeing the image, it seems to me as MS Excel table but will require me to run SQL query.

2) If you see the 3rd table, it has roleid field which is also present in 2nd table. The 3rd table also has userid field which is there in 1st table. So with the help of 3rd table, 2nd and 3rd are connected; 1st and 3rd are connected.

3) Third table is called metadata as it contains skeletal info of all the three tables.

In SQL, I have read that if you need firstname and lastname from EMP table then you have to enter

select firstname, lastname from emp where role name = 'lumo advantage'

But this is excel here so I am getting confused all the more.

I'll let [ fanpages ] take over here. He's very good at this type of thing.

I have spent 2 night straight to understand all this. Now I am willing to stay straight even today but atleast give me some hint to invest my energy. I think I am not able to understand the question.

Thanks Martin... unless you meant I am very good at taking over.  I'll assume not.

Feel free to continue contributing.  A problem shared is a problem halved.

...
"1) Seeing the image, it seems to me as MS Excel table but will require me to run SQL query."

The image, to me, looks like the results of three Structured Query Language [SQL] queries; three SELECT statements that produce three results sets.

"2) If you see the 3rd table, it has roleid field which is also present in 2nd table. The 3rd table also has userid field which is there in 1st table. So with the help of 3rd table, 2nd and 3rd are connected; 1st and 3rd are connected."

Yes, I concur.

"3) Third table is called metadata as it contains skeletal info of all the three tables."

OK.  It could also be a primary table, or any other number of descriptions :)

I would refer to it as a de-normalised table, & the other two are normalised tables.

(Replace the "s" in "normalised" with "z" to suit your preference)

...So, is it just questions #4 & #5 you need guidance with?

(This is not specifically an MS-Excel Topic-based question, but we can proceed as you wish)

Yes,  3rd; 4th and 5th.

3.      Write a query which displays the First and Last Name for all users who belong to the Role “Lumo Advantage”.

4.      Write a query which displays the First and Last Name in a single column, for all users that don’t have a Role.

5.      Write a query which lists all the Roles and how many users below to each role where the Role has less than 20 users in it.

3.      Write a query which displays the First and Last Name for all users who belong to the Role “Lumo Advantage”.

Do you think that you will be required (or, rather, is the question asking) to refer to the [Roles] table, or simply use the [RoleID] (7) of "Lumo Advantage" within the SQL statement joining the other two tables. [Users] & [UserRoles], together?

I will share honestly what I think should be the answer

select userid from userroles where roleid=7;

This will give us the userid of lumo advantage [say it is 10]

Then using that we will again write

select firstname, lastname from users where userid = 10;

This will display the firstname, lastname..

But sir that will be a 2 line formula.. Can't we do it in just 1 line?

Thanks Martin... unless you meant I am very good at taking over.  I'll assume not.

Your assumption is correct.

Ritwik':

Yes, you are correct that the two statements can be used together to form "1 line" (or one statement).

Have you learned/researched/been instructed on anything about the JOIN construct within SQL statement syntax?

No sir how can we do that?

will this work?

SELECT Users.FirstName, User.LastName
FROM Users
INNER JOIN UserRoles
ON UserRoles.RoleID = 7;

hmmm.. I understood.

Let's come to the 4th question.. I can't understand even 1%.. How can we write in single column?

Very difficult for a beginner like me.

4.      Write a query which displays the First and Last Name in a single column, for all users that don’t have a Role.

I am mindful that I am now doing this task for you.

Producing a single column from two (or more) other columns may be influenced by the underlying database (&/or version of SQL that the database uses).

Do you have any idea what database you are using?  I suspect not given that you originally thought your were producing MS-Excel tables! :)

Also, it does sound like you either have not covered within your course what you need to have covered in order to attempt these (five) questions, or you are missing some background reading.

Do you have any notes or additional text to refer to?

Honestly sir I thought it was MS Excel. But just as you're hinting me, I am refering to W3Schools tutorials. It from there I got the idea to join the table but I couldn't do it correctly.

I request for your help.

OK, I will see if I can find something suitable for you to read on that external site...

SELECT CONCAT_WS(`firstname`, `lastname`) AS `FullName` FROM Users
where Roles.RoleName = NULL'

will this work?

^ That approach will work will a little modification, yes.

So far we have looked at:

INNER JOIN:
[ http://www.w3schools.com/sql/sql_join_inner.asp ]

combined with WHERE:
[ http://www.w3schools.com/sql/sql_where.asp ]

To combine two (or more) columns together into a single column, some databases allow the CONCAT function:
[ http://www.tutorialspoint.com/sql/pdf/sql-concat-function.pdf ]

(I could not find any mention of this within the W3Schools.com site)

However, that is not the only method available, as CONCAT is not supported by every database that implements a SQL statement syntax.

SELECT CONCAT_WS(`firstname`, `lastname`) AS `FullName` FROM Users
where Roles.RoleName = NULL'

will this work?

CONCAT_WS is seen in mySQL databases, & has at least three parameters:

CONCAT_WS (<separator>, <string1>, <string2>,…)

("_WS" stands for "with separator")

SELECT CONCAT (`firstname`, `lastname`) AS `FullName` FROM Users
where Roles.RoleName = NULL

Select allOpen in new window

Is it okay?

Things to consider...

a) The single quote (') character is not used to enclose column names, but to delimit explicit text values.
b) Why are you looking for a RoleName with a NULL value?
c) Do you want the [FirstName] and [LastName] columns to run together without something between them?

As discussed above, I am trying to guide you, not give you the complete answer.

Fair enough. Just guide me, I am confident that I will reach to the answers in the remaining 6 hours! :)

The question says: "Write a query which displays the First and Last Name in a single column, for all users that don’t have a Role" so wouldn't that mean users whose RoleName is NULL??

SELECT CONCAT (FirstName, '', LastName) AS `FullName` FROM Users
where Roles.RoleName = NULL

Now is it correct?


For question no. 3, I thought of this one -

SELECT FirstName, LastName FROM Users
where RoleName in (select RoleName from Role where RoleName = 'Lumo Advantage');

Is it correct?

" (select RoleName from Role where RoleName = 'Lumo Advantage') "

That will just give you "Lumo Advantage" (again).

"...for all users that don’t have a Role..."

OK, look at this requirement differently.

You need a list of users that do not have a role defined.

Where are the roles in the database?  Is there a table where roles for each user are stored?

Why not look for any user that does not exist in that table?

Clue: You may need to JOIN two tables together! :)

I have corrected my solution for 4th Question -

SELECT CONCAT (FirstName, ' ', LastName) AS `FullName` FROM Users
where Roles.RoleName is NULL

hmmm.. okay let me think about joining the table.

Meanwhile please see this solution for question 3:

SELECT Users.FirstName, Users.LastName
INNER JOIN Users
ON UserRoles.UserID = Users.UserID
WHERE UserRoles.RoleID =7;

In the 2nd line, shouldn't we specify names of two tables? We have just mentioned name of 1 table.

A possible solution for Question 4:


SELECT CONCAT (FirstName, ' ', LastName) AS `FullName`
FROM Users Inner Join UserRoles
ON User.UserID = UserRoles.UserID
Inner Join Roles
On UserRoles.RoleID = Roles.RoleID
where Roles.RoleName is NULL

will it work?

sir,

my professor is on a maternity leave. What she is doing is only sending us questions through email and asking to submit our response to her PhD student by monday.

Without any instructions, we have been left to wander. This is utter nonsense but I can't change the system.

Hence, I request you to help with question no. 5. I beg of you.

Have you considered installing mySQL or a similar database on your PC & attempting to replicate (locally) the environment in which the questions are set?

Alternatively, Microsoft Access, if you have this available to you.

Yes sir,

I am implementing them on SQL Plus but unfortunately they are giving error. So I ask them to you so that you could point out what is getting wrong - syntax or the entire logic!

Sir,

Please help with question no. 5. I earnestly request you. I swear I have worked for than 2 days straight to solve these 5 questions. I didn't lie to anyone.

So,... you did know these where related to a database (an ORACLE variant of SQL), not to MS-Excel?

(Sigh)

5. Write a query which lists all the Roles and how many users below to each role where the Role has less than 20 users in it.

Did it take you two days to notice the problem with the fifth question?

Again want to be honest, my SQL plus looks like Command line interface. These screenshots look like GUI.

So I thought it might be Access or Excel. When I asked my great professor over email; she simply didn't reply.

Do you mean the 5th question is wrong?

OK... Write a query that [I corrected the English here] lists all the Roles & how many users belong [spelling corrected here] to each Role, where the Role has less than 20 Users.

How do you think you are going to approach this question?

Split it into three parts:

a) List all the Roles
b) Determine the Quantity of Users in each Role listed
c) Restrict the result to Roles with less than 20 Users

Select RoleName, Count (Distinct RoleName) from Roles where Count (RoleName) < 20;

Will it work?

a) For listing all roles - Select RoleName from Roles;

correct?

b) For getting number of number of people attached to each role, count function has to be used.

Select RoleNames. Count (UserID)
from Roles, Users
where Roles.RoleID = UserRoles.RoleID and Users.UserID = UserRoles.UserID

Are these 2 correct??

ya that's a great thinking! :)

I wish I could do it myself but my logic seems to fail.. :(

But I want to ask one more question -

The 3rd question was - Write a query which displays the First and Last Name for all users who belong to the Role “Lumo Advantage”.

This we did by making use of JOIN. But for JOIN we use the name of two tables.

SELECT Users.FirstName, Users.LastName
INNER JOIN Users
ON UserRoles.UserID = Users.UserID
WHERE UserRoles.RoleID =7;

But in this code, we have not named the 2nd table [the one we are joining with]

General Syntax - SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

We used only one table USERS. wouldn't that be incorrect?

No, not correct.  Sorry.

"INNER JOIN Users ON UserRoles.UserID = Users.UserID"

We are joining records from [Users] to records within [UserRoles] where the [UserID] is the same between the two tables.

We (to be honest you) arrived at the same solution earlier also..

SELECT Users.FirstName, Users.LastName
INNER JOIN Users
ON UserRoles.UserID = Users.UserID
WHERE UserRoles.RoleID =7;

So where is this one wrong??

"So where is this one wrong??"

It isn't; see comment ID: 40965187.

I've requested that this question be deleted for the following reason:

I know I have done wrong thing.

Delete Request Pending

ritwik28 requested that this question be deleted for the following reason:

I know I have done wrong thing.

To cancel this request and generate a request for Moderator review, state your reason for objecting in the standard comment box and click the 'Object' button. This question will be closed on 9/10/2015 if there are no objections.

"I object" because this makes no sense!

I have no problem in letting it here. I was trying to follow the rules. That's it.

I have no problem in letting it here. I was trying to follow the rules. That's it.

You mean after spending over five hours gaining answers to all your queries, you then decide that you wish to delete the thread?

No, I don't think so.

Hi eenookami,

Thank you for following this to conclusion.

1) Delete
2) Accept one or more comments as the solution (including the Asker's)

In the case of #2, please be specific and include specific comment ID(s), using the format http:#axxxxxxxx 

Option 2, in my (biased!) opinion, please:

(It was confirmed within ID: 40965134 that just the latter three points of the question required 'assistance')

Point 3 was answered within ID: 40965187 [ https://www.experts-exchange.com/questions/28711765/MS-Excel.html?anchorAnswerId=40965187#a40965187 ]

Point 4 was answered within ID: 40965286 [ https://www.experts-exchange.com/questions/28711765/MS-Excel.html?anchorAnswerId=40965286#a40965286 ]

Point 5 was answered within ID: 40965324 [ https://www.experts-exchange.com/questions/28711765/MS-Excel.html?anchorAnswerId=40965324#a40965324 ]