SQL Server Count and group

[Zathara]

I have up till now one more project to due that’s due in some days and out of 15 queries there are 2 I am confused on. I can think of them sensibly in my head but I am not capable to actually interpret it into a SQL server query. The two questions are:

1. List the details for the branches which have as a minimum 1 DVD copy in a minimum of 3 dissimilar groups
2. List details for the members which have the most recently borrowed copies. Series the output alphabetically by member name

The database is a DVD hire company and there are 5 dissimilar movie category / genres across 7 dissimilar stores. There are quite a lot of copies of each DVD obtainable and there are a bit like 37 DVD titles though lots of copies all over the stores. I am hesitant of how to go regarding the query. As for question 2, I think it's asking, who was the final person to rent a title, in spite of of which copy number it is. Once more, this would be a few sort of min or max as the database has a Date borrowed DATETIME variable in one of the tables, but I am hesitant of how to go about writing the cipher. Any help would be very much appreciated.

[Badrunath]

Well I am in school for "IT" although I have not at all been bear to any sort of programming prior to Java that I was first introduced to just a year before, and I disliked it. Our textbook is next to ineffective with very basic things. I came here for a few help and support, and if that's how you feel then so be it. I don't actually wish to start any advice so I will just leave this be. As well, just 2 years of "IT" Education and my program consists of more commerce than IT at the moment.

[MahaGuru]

I could be mistaken but, I think 2nd question is asking for the member(s) who are presently renting the majority DVDs. e.g. if alice has borrowed 2, bob has borrowed 3, and chris has borrowed 1, you necessitate to output bob's details. If bob and chris have both borrowed 3, you want to output bob's details and chris's details. For question 1, I am guessing you will need to group the dvdCopy table by branch number and after that do a count on the separate groups(you talk about table DVD is immaterial, but I don’t notice genre or group information in the tables you illustrate, so this information is probable in the DVD table)

[GeforceUser]

Here I also wish to add something regarding this query. I have previously done an intro to java course, I passed but not by a great extent. I have no goals to further my knowledge in java. I understand it's possibly the most utilized but I don't preparation to do the programming or coding thing in the future. Not my type of thing. I will continually cracking away at 6 to see if I can make a few additional small dents in the query.

[Bull50]

For your 1st question:

SELECT * FROM Branch WHERE (BranchNo) IN (SELECT BranchNo FROM DVDCopy having (MemberNumber is not null and Count (*)>0 group by category, MemberNumber));

How are the groups determined? I may have misinterpreted but I can't make out a field for this. Is it just that there is one DVD for every category, and quite a lot of copies of each DVD? The script above would work if there is a field for group, if not I will seem at it further. Unluckily I don't get paid >50k to write SQL, more like 20k, but I will assist if I can.

[sergioKomic]

For your second question List details for the members which have the most presently borrowed copies. Series the output alphabetically by member name

select * from Memb where Membno in (select MembNo from DVDCopy group by MembNo having count (MembNum) = (select max (cnt) from (select MembNum, count (MembNum) cnt from dvdcopy
group by Membnum))) order by name asc;

Once more, if I have read the query properly. There is possibly a more stylish way of doing this, but mainly I have completed a count of all the DVDs grouped by Membnum, and after that linked this over to the member table. Thinking regarding it though you will possibly require to discount too so:

select * from Memb where Membno in (select MembNo from DVDCopy group by MembNo having count(MembNum) = (select max(cnt) from (select MembNum, count(MembNum) cnt from DVDCopy where MembNum is not null group by membnum))) order by name asc;

Let me know how mistaken these are.