CODEWITHSUNDEEP
sqloracle-database
Kantakt
Kantakt

Reputation: 43

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 |
    +-----------------------------------------------------------------------------------------+

Upvotes: 0

Views: 95

Answers (3)

VBoka
VBoka

Reputation: 9083

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

Upvotes: 1

Kantakt
Kantakt

Reputation: 43

Exactly what I need is:

 SELECT count(p.p_num) FROM 
 SHOPS H 
 JOIN PRODUCTS P ON H.S_NUM = P.SHOP_NUM
 WHERE S_NAME = 'Shop2'
 group by P.SHOP_NUM;

+----------------+
| COUNT(P.P_NUM) |
+----------------+
| -------------- |
|              2 |
+----------------+

Upvotes: 2

Akr0n
Akr0n

Reputation: 23

Assuming that S_NUM and SHOP_NUM are both referring to the same values you can use JOIN to get a view filtered by S_NAME. Something like:

SELECT
    H.S_NUM
    , H.S_NAME
    , H.TEL
    , P.P_NAME
    , P.P_PRICE
FROM SHOPS H
JOIN PRODUCTS P ON H.S_NUM = P.SHOP_NUM
WHERE S_NAME = 'SHOP1'

Upvotes: 1

Related Questions