Queries: To find name of project ,which has used maximum number of parts

Question

I am doing an assignment in which I have created three tables namely "Project", "Part" and "Use". The structure of the tables are given below:

 SQL> CREATE TABLE PROJECT
  2  (
  3  PNO VARCHAR2(5) PRIMARY KEY,
  4  PNAME VARCHAR2(15) NOT NULL,
  5  CITY VARCHAR2(15) NOT NULL
  6  );

SQL> CREATE TABLE PART
  2  (
  3  PID VARCHAR2(5) PRIMARY KEY,
  4  PNAME VARCHAR2(15) NOT NULL,
  5  COLOR VARCHAR2(15) NOT NULL
  6  );

SQL> CREATE TABLE USE
  2  (
  3  PNO VARCHAR2(5),
  4  PID VARCHAR2(5),
  5  QTY NUMBER CHECK(QTY >5 AND QTY<100),
  6  FOREIGN KEY(PNO) REFERENCES PROJECT,
  7  FOREIGN KEY(PID) REFERENCES PART,
  8  PRIMARY KEY(PNO,PID)
  9  );

I want to write a query to find out the project name which has used the highest number of products.

I am able to write the following query which returns the number of parts used by each project.

SQL> SELECT PNO, SUM(QTY)
  2  FROM USE
  3  GROUP BY PNO;

PNO     SUM(QTY)
----- ----------
P4           205
P1            95
P2            95
P3            20

Answer 1

you can try below-

select * from
(
SELECT PNO, SUM(QTY) as totalqty
  FROM USE
  GROUP BY PNO order by SUM(QTY) desc
)A where rownum=1