Create A Query To Select Two Columns; (company, No. Of Films) From The Database

I have created a database as part of university assignment and I have hit a snag with the question in the title. More likely I am being asked to find out how many films each compan

Solution 1:

The answer you need comes from three basic SQL concepts, I'll step through them with you. If you need more assistance to create an answer from these hints, let me know and I can try to keep guiding you.

Group By

As you mentioned, SQL offers a GROUP BY function that can help you.

A SQL Query utilizing GROUP BY would look like the following.

SELECT list, fields, aggregate(value)
FROM tablename
--WHERE goes here, if you need to restrict your result setGROUPBY list, fields

a GROUP BY query can only return fields listed in the group by statement, or aggregate functions acting on each group.

---

Aggregate Functions

Your homework question also needs an Aggregate function called Count. This is used to count the results returned. A simple query like the following returns the count of all records returned.

SELECTCount(*)
FROM tablename

---

The two can be combined, allowing you to get the Count of each group in the following way.

SELECT list, fields, count(*)
FROM tablename
GROUPBY list, fields

---

Column Aliases

Another answer also tried to introduce you to SQL column aliases, but they did not use SQLPLUS syntax.

SELECT Count(*) as count
...

SQLPLUS column alias syntax is shown below.

SELECTCount(*) "count"
...

Solution 2:

I'm not going to provide you the SQL, but instead a way to think about it.

What you want to do is select where the company matches and count the total rows returned. That count is the number of films made by the specified company.

Hope that points you in the right direction.

Solution 3:

Select company, count(*) AS count 
from Movie 
groupby company

select * group by company won't work in Oracle.