MS Excel

Not able to understand the attached quiz
Quiz.docx
Ritwik MukerjeeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Homework?
Ritwik MukerjeeAuthor Commented:
ya stuck badly!
Ritwik MukerjeeAuthor Commented:
give some hint as to how to proceed..
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Martin LissOlder than dirtCommented:
[ fanpages ]IT Services ConsultantCommented:
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:

Image within "Quiz.docx"
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.    
---
Ritwik MukerjeeAuthor Commented:
That is fine. I am not asking anyone to do something on my behalf. But they can always guide me.
[ fanpages ]IT Services ConsultantCommented:
Ritwik Mukerjee:

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

[ http://www.experts-exchange.com/questions/28711681/Need-help-with-function.html#a40965066 ]
Ritwik MukerjeeAuthor Commented:
@[fanpages] I could not understand what is the point in copying and pasting the question.
[ fanpages ]IT Services ConsultantCommented:
"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?
Martin LissOlder than dirtCommented:
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 ]IT Services ConsultantCommented:
"@[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".
Ritwik MukerjeeAuthor Commented:
[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.
Ritwik MukerjeeAuthor Commented:
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.
Martin LissOlder than dirtCommented:
I'll let [ fanpages ] take over here. He's very good at this type of thing.
Ritwik MukerjeeAuthor Commented:
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.
[ fanpages ]IT Services ConsultantCommented:
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)
[ fanpages ]IT Services ConsultantCommented:
...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)
Ritwik MukerjeeAuthor Commented:
Yes,  3rd; 4th and 5th.
Ritwik MukerjeeAuthor Commented:
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.
[ fanpages ]IT Services ConsultantCommented:
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?
Ritwik MukerjeeAuthor Commented:
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?
Martin LissOlder than dirtCommented:
Thanks Martin... unless you meant I am very good at taking over.  I'll assume not.
Your assumption is correct.
[ fanpages ]IT Services ConsultantCommented:
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?
Ritwik MukerjeeAuthor Commented:
No sir how can we do that?
Ritwik MukerjeeAuthor Commented:
will this work?

SELECT Users.FirstName, User.LastName
FROM Users
INNER JOIN UserRoles
ON UserRoles.RoleID = 7;
[ fanpages ]IT Services ConsultantCommented:
Sorry for my delayed response.

I was dealing with another issue (related to "academic"-themed responses elsewhere).

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

Open in new window


That is almost correct.  Not bad if that was your first attempt! :)

When you use an INNER JOIN (or any JOIN for that matter) you need to join a column (field) from one table, with a column from another, thus:

INNER JOIN [Users] ON [UserRoles].[UserID] = [Users].[UserID]

You then need to establish what to retrieve from the [Users] table:

SELECT [Users].[FirstName], [Users].[LastName]

Finally, restrict the [RoleID] appropriately:

WHERE [UserRoles].[RoleID]=7


Putting that all together...

SELECT
[Users].[FirstName],
[Users].[LastName]

INNER JOIN [Users] ON
[UserRoles].[UserID] = [Users].[UserID]

WHERE
[UserRoles].[RoleID]=7

Open in new window



However, the data in your image above will not produce any records from this statement as there are no [UserRoles] records with a [RoleID]=7.

Perhaps the full question that we have not had sight of (but you have) does include some records that will return at least one result from this SQL statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ritwik MukerjeeAuthor Commented:
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.
[ fanpages ]IT Services ConsultantCommented:
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?
Ritwik MukerjeeAuthor Commented:
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.
[ fanpages ]IT Services ConsultantCommented:
OK, I will see if I can find something suitable for you to read on that external site...
Ritwik MukerjeeAuthor Commented:
SELECT CONCAT_WS(`firstname`, `lastname`) AS `FullName` FROM Users
where Roles.RoleName = NULL'

will this work?
[ fanpages ]IT Services ConsultantCommented:
^ 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.
[ fanpages ]IT Services ConsultantCommented:
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")
Ritwik MukerjeeAuthor Commented:
SELECT CONCAT (`firstname`, `lastname`) AS `FullName` FROM Users
where Roles.RoleName = NULL

Open in new window


Is it okay?
[ fanpages ]IT Services ConsultantCommented:
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.
Ritwik MukerjeeAuthor Commented:
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?
[ fanpages ]IT Services ConsultantCommented:
" (select RoleName from Role where RoleName = 'Lumo Advantage') "

That will just give you "Lumo Advantage" (again).
[ fanpages ]IT Services ConsultantCommented:
"...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! :)
Ritwik MukerjeeAuthor Commented:
I have corrected my solution for 4th Question -

SELECT CONCAT (FirstName, ' ', LastName) AS `FullName` FROM Users
where Roles.RoleName is NULL
Ritwik MukerjeeAuthor Commented:
hmmm.. okay let me think about joining the table.
Ritwik MukerjeeAuthor Commented:
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.
Ritwik MukerjeeAuthor Commented:
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?
[ fanpages ]IT Services ConsultantCommented:
I am not planning to be here all night(!); so, no, your most recent suggestion is invalid.

SELECT FirstName & ' ' & LastName AS `FullName`
FROM Users
LEFT JOIN UserRoles ON
UserRoles.UserId = Users.UserId
WHERE UserRoles.UserId Is NULL

Open in new window


Alternatively,

SELECT CONCAT(FirstName, ' ' , LastName) AS `FullName`
FROM Users
LEFT JOIN UserRoles ON
UserRoles.UserId = Users.UserId
WHERE UserRoles.UserId Is NULL

Open in new window


Or, even...

SELECT CONCAT_WS(' ', FirstName, LastName) AS `FullName`
FROM Users
LEFT JOIN UserRoles ON
UserRoles.UserId = Users.UserId
WHERE UserRoles.UserId Is NULL

Open in new window


(Depending on which variant of SQL you are using)

If you cannot attempt question #5 on your own, then I suggest you speak with your course instructor about additional help.
Ritwik MukerjeeAuthor Commented:
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.
[ fanpages ]IT Services ConsultantCommented:
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.
Ritwik MukerjeeAuthor Commented:
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!
Ritwik MukerjeeAuthor Commented:
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.
[ fanpages ]IT Services ConsultantCommented:
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?
Ritwik MukerjeeAuthor Commented:
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.
Ritwik MukerjeeAuthor Commented:
Do you mean the 5th question is wrong?
[ fanpages ]IT Services ConsultantCommented:
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
Ritwik MukerjeeAuthor Commented:
Select RoleName, Count (Distinct RoleName) from Roles where Count (RoleName) < 20;

Will it work?
Ritwik MukerjeeAuthor Commented:
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??
[ fanpages ]IT Services ConsultantCommented:
How about...

SELECT
*
FROM
(
    SELECT
    [Roles].[RoleName],
    (
        SELECT
        COUNT(*)
        FROM
        [UserRoles]
        WHERE
        [UserRoles].[RoleID]=[Roles].[RoleID]
    ) AS [User_Count]
    FROM
    [Roles]
)
WHERE
[User_Count]<20

Open in new window


?
Ritwik MukerjeeAuthor Commented:
ya that's a great thinking! :)

I wish I could do it myself but my logic seems to fail.. :(
Ritwik MukerjeeAuthor Commented:
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?
[ fanpages ]IT Services ConsultantCommented:
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.
Ritwik MukerjeeAuthor Commented:
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??
[ fanpages ]IT Services ConsultantCommented:
"So where is this one wrong??"

It isn't; see comment ID: 40965187.
Ritwik MukerjeeAuthor Commented:
I've requested that this question be deleted for the following reason:

I know I have done wrong thing.
[ fanpages ]IT Services ConsultantCommented:
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!
Ritwik MukerjeeAuthor Commented:
I have no problem in letting it here. I was trying to follow the rules. That's it.
[ fanpages ]IT Services ConsultantCommented:
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.
[ fanpages ]IT Services ConsultantCommented:
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 [ http://www.experts-exchange.com/questions/28711765/MS-Excel.html#a40965187 ]

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

Point 5 was answered within ID: 40965324 [ http://www.experts-exchange.com/questions/28711765/MS-Excel.html#a40965324 ]
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.