Oracle Database Object SQL query

In Oracle Database. There are two object tables SHOPS(S_NUM, S_NAME, TEL). And PRODUCTS(P_NUM, P_NAME, P_VAL, P_PRICE, SHOP_NUM). How to write in Oracle Object SQL language query to get how much there are PRODUCTS in the shop with name “SHOP1”?

CREATE OR REPLACE TYPE T_SHOP as Object (
   S_NUM Integer,
   S_NAME Varchar(30),
   TEL Varchar2(300)  
);

CREATE OR REPLACE TYPE T_PRODUCT as Object (
   P_NUM Integer,
   P_VAL Integer,
   P_PRICE Number,
   SHOP_NUM INteger,
   P_NAME Varchar(30),
   TEL Varchar2(300)  
);

CREATE TABLE SHOPS of T_SHOP;
CREATE TABLE PRODUCTS of T_PRODUCT;

Shops:
    +---------------------------------------------------------------+
    |            S_NUM S_NAME                         TEL           |
    +---------------------------------------------------------------+
    |          0 Shop1                          +371256215215       |
    |          1 Shop2                          +37125623232323232  |
    |          2 Shop3                          +37125623223323     |
    +---------------------------------------------------------------+
Products:
    +-----------------------------------------------------------------------------------------+
    |          P_NUM      P_VAL    P_PRICE   SHOP_NUM P_NAME                         TEL      |
    +-----------------------------------------------------------------------------------------+
    |          0          1         10          1 SIRI                           332323233232 |
    |          1          1         11          1 SIRI2                          332323233232 |
    +-----------------------------------------------------------------------------------------+

Answer

Try with this:

select count(distinct P.P_NUM) 
from SHOPS S
left join PRODUCTS p on P.SHOP_NUM = S.S_NUM 
Where S.S_NAME = 'SHOP1'
group by P.SHOP_NUM

Small DEMO

Advertisement

Answer