Development SQL Server Count and group by question

I have yet another project to due thats due in a few days and out of 20 queries there are 2 I am stumped on. I can think of them logically in my head but I'm unable to really translate it into a SQL server query.

The two questions are:

q7 List the details for the branches that have at least a 1 DVD copy in a minimum of 3 different categories

--q8 List details for the members that have the most currently rented copies. Sequence the output alphabetically by member name

The database is a DVD rental company and there are 5 different movie category / genres across 7 different stores. There are several copies of each dvd available and there are something like 37 dvd titles though many copies throughout the stores. I am unsure of how to go about the question. I know I need to do a count per each movie category and group them by their different locations / stores. It's really got me stumped.

As for question 8, I think it's asking who was the last person to rent a title, regardless of which copy number it is. Again, this would be some sort of min or max as the database has a Date rented DATETIME variable in one of the tables, but I am unsure of how to go about writing the code. Any help would be greatly appreciated.

The two tables that would be used for question 7 are:

Branch table
Primary key = BranchNumber
Variables: Street, City, Province, Advisor

DVDCopy table
Primary Key CopyNumber
Foreign Key DVDNumber (irrelevant I believe)
Foreign Key BranchNumber
Status (If status is null, its fine, if status is M its missing)
LastDateRented
LastDateReturned
MemberNumber foreign key

For question 8 you'd need the member table and DVDCopy table above.

The member table
Primary key = MemberNumber
Name
RegistrationDate
OutstandingFees
BranchNumber Foreign key

 

Old git comment, actual knowledge is far more useful than the ability to derive it.

 

Alrighty than...

Well I am in school for "IT" though I've never been exposed to any sort of programming prior to Java which I was first introduced to only a year ago, and I disliked it. Our textbook is next to useless with VERY basic things. I came here for some help and support, and if that's how you feel than so be it. I don't really want to start any arguments so I'll just leave this be. Also, only 2 years of "IT" Education and my program consists of more business than IT at the moment.

 

so your boss S*IT on you today, and you are now talking it out on people online.

Awesome you have a great job, your real mature.

and on another point

"Sorry, i know I annoy some people with my attitude but you are so taking the piss it beggars belief. if, after 3 years of IT education, you cannot manage such a basic challenge, you should really give up now, be honest with yourself and any future employer, and go get a 20k job doing some HR function."

who the fk said the amount of education he has? just wondering?

really get off that step your on. come back to earth.

 

Here's a table... Does this help?
Yes I realize it is not the database and it's from a different program but the database has the exact same tables with the exact same relationships.

 

Old git comment, actual knowledge is far more useful than the ability to derive it.

 

really step off that step ladder your on and leave

 

Old git comment, actual knowledge is far more useful than the ability to derive it.

 

I realize I'm getting no help from you. As I said, I've gone through our textbook and it only has basic things, I've gone through the weekly labs and I've gone through the in class stuff our teacher did. This is a bit trickier. I can figure it out in my head, but can't get it out on paper. I don't play to continue with SQL, the course was mandatory. I don't plan on spending a couple hundred bucks for a book that I won't be using any further after this course is done. If I wanted to really learn SQL I'd take a private course in night school and go further with it.

Also, I've spent a good 17 hours developing the database that my group has not helped me with, and I've done 18 of the 20 questions after a good 5 hours of work. These last 2 really have me stumped so figured I'd ask for help.

 

Old git comment, actual knowledge is far more useful than the ability to derive it.

 

Old git comment, actual knowledge is far more useful than the ability to derive it.

 

Its the requirements by our teacher. He made the logical design and we had to design the database to the logical design he gave us. As I said before, I can figure it out logically in my head I just can't transfer it into the program and have it working properly. Maybe I've been thinking and trying to hard and I'll figure it out for tomorrow but I was just wanted some help. After 5 hours of nothing but queries I've had just about enough. Also, its a group project but my group isn't here to help me, I've tried going through my teachers lectures, labs and homework to no luck. We've done the the parts separately but combining them is really giving me trouble.

As for question 8, I have part of it working but I know theres an issue because when I select the lastrented it's all lastrented and not the most recent. I don't know how to get the group by working with that but here's what I've come up with.

select * from Member where MemNo in
(select MemNo from DVDCopy
where LastDateRented in
(select lastdaterented from DVDCopy
where DVDNo in
(select distinct DVDNo from DVDCopy)))
order by Name

 

Old git comment, actual knowledge is far more useful than the ability to derive it.

 

If you do not plan on providing any help or insight to the original question proposed by mansueto who has been an active user to the community much longer than yourself and who turned to these forums as resourceful way to solve a problem he was having then I strongly recommend that you just stop posting in this thread. You have provided nothing except discouragement and useless replies. If you don't have anything good to say then don't say anything at all.

Thanks.

 

i could be wrong but...

i think q8 is asking for the member(s) who are currently renting the most DVDs
e.g. if alice has rented 2, bob has rented 3, and chris has rented 1... you need to output bob's details
if bob and chris have both rented 3... you need to output bob's details and chris's details

for q7, im guessing you will want to group the dvdCopy table by branch number and then do a count on the distinct categories (you mention table DVD is irrelevant... but i dont see genre/category information in the tables you describe, so this information is likely in the DVD table)
if you dont know what "group by", "count", "distinct" do or dont know how to use them, check out the following pages
http://www.w3schools.com/sql/sql_groupby.asp
http://www.w3schools.com/sql/sql_having.asp
http://www.w3schools.com/sql/sql_func_count.asp
http://www.w3schools.com/sql/sql_distinct.asp

 

Plugs, you sir deserve some rep. I must have misinterpreted the questions and your comment has made it all clear. I'll figure those 2 out now but after you've added your input they seem pretty straight forward.

Thank you good sir!

Edit: Here's my number 8, about to start 7 now. The lastdaterented must be null to know that the dvd is current rented.

select * from Member
where MemNo in
(select top 1 with ties dvdcopy.MemNo from DVDCopy inner join Member
on dvdcopy.MemNo = Member.MemNo
where LastDateReturned is null
group by dvdcopy.MemNo
order by COUNT(*) desc)
Order by Name

Double edit:

I've got part of 7 working. As of now it outputs copies available per branch per genre / category. I'm not sure where to get the BrNo from here, I've tried nesting the below with. Also when I do the having count(BrNo) >=3 counts with less are still showing up.

Select * from Branch
where BrNo in... but that didn't work

select count(distinct CopyNo)"Copies available", BrNo, category from DVDCopy inner join DVD
on DVDCopy.DVDNo = DVD.DVDNo
where LastDateReturned is not null
group by BrNo, Category
having COUNT(BrNo) >=3

 

No problem, I'm here to help

Your solution to q8 has the following in "select top 1"
will this not select only one member, even if there are two members who have equal numbers of current DVDs?

 

it has the with ties feature so if there are 2 people with the same value it selects both of them

 

I'm glad someone linked you to the w3schools, probably the best website for this kind of stuff.

I'm pretty sure that one guy just realized how small his dick was, and I seriously doubt anybody pays him 50k a year. If they do, I doubt they will for much longer, I know I couldn't work with him. I doubt he could even work with himself. What an idiot. For the record you don't really need to provide an entire table structure either for us to help you with the concepts of grouping and sorting in an SQL command.

Also, you're pretty much right, if you are not into coding... you're not into coding. It's not that hard to tell and I'm not sure it's something you can change. SQL syntax is not the worst of them though and you don't have to master it to get some really great use out of a database.

I'll try to come back here later to check if you are having issues and see if I can help but I'm sure you will have better luck with other bit-tech members as I have had with my own problems.

 

I've already done an intro to java course, I passed but not by much. I have no ambitions to further my knowledge in java. I realize it's probably the most used but I don't plan to do the programming / coding thing in the future. Not my kind of thing. Thanks for all the inputs though so far. I'll keep cracking away at 7 to see if I can make some more small dents in the query.