Pfthb

I want to get the customer who mostly borrowed films of category 3 in 2016, July

SELECT c_firstName, c_lastName, rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID HAVING rental.c_ID=MAX((SELECT COUNT(rental.c_ID) 
FROM customer, copies, rentalprocess, rental, film
WHERE customer.c_ID=rental.c_ID AND rentalprocess.r_ID=rental.r_ID AND
 rentalprocess.s_ID=copies.s_ID AND film.f_ID=copies.f_ID AND
 f_category=3 AND r_date LIKE "2016-07%" GROUP BY rental.c_ID))

But ir doesn't work because it said that the subquery returns more than one row

What can I do?

Max() is an aggregate function that needs to be in a select statement

SELECT 
 c_firstName
 , c_lastName
 , rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID 
HAVING rental.c_ID=
 (
 select 
 MAX(i.iID) 
 from 
 (
 SELECT 
 COUNT(rental.c_ID) iID
 FROM customer, copies, rentalprocess, rental, film
 WHERE 
 customer.c_ID=rental.c_ID AND 
 rentalprocess.r_ID=rental.r_ID AND
 rentalprocess.s_ID=copies.s_ID AND 
 film.f_ID=copies.f_ID AND
 f_category=3 
 AND r_date LIKE "2016-07%" 
 GROUP BY rental.c_ID
 ) i
 )

In this case the sub-select returns multiple rows but then you take the max value of that query

Comment from Mr Linoff is correct, you should use explicity joins:

SELECT 
 c_firstName
 , c_lastName
 , rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID 
HAVING rental.c_ID=
 (
 select 
 MAX(i.iID) 
 from 
 (
 SELECT 
 COUNT(rental.c_ID) iID
 FROM 
 customer 
 inner join rental 
 on customer.c_ID=rental.c_ID
 inner join rentalprocess 
 on rentalprocess.r_ID=rental.r_ID
 inner join copies 
 on rentalprocess.s_ID=copies.s_ID
 inner join film on film.f_ID=copies.f_ID
 WHERE 
 f_category=3 
 AND r_date LIKE "2016-07%" 
 GROUP BY rental.c_ID
 ) i
 )

your code should look something like this, join the tables properly in the code.
I dont know wich columns and table would suit best for the solution becuse I dont got your full schema. but this should give faster query. Put more columns in the select if you wish.

select c_firstName | ' ' | c_lastName, count(rental.c_ID) as rentalCustomer
 from customer
 inner join rental
 on join " connect the both tables"
 innner join rentalprocess
 on "connect rental with rentalprocess"
 inner join copies
 on " connect rentalprocess with copies"
 inner join film
 on "connect copies with film"
 WHERE customer.c_ID=rental.c_ID AND 
 rentalprocess.r_ID=rental.r_ID AND
 rentalprocess.s_ID=copies.s_ID AND 
 film.f_ID=copies.f_ID AND
 f_category=3 AND r_date LIKE "2016-07%"
 group by c_firstName, c_lastName, rental.c_ID
 order by rental.c_ID desc;