I have these tables in my database:

• Company (Symbol, Sector, Founded, Location)
• Stock (tDate, Symbol, Price)

Columns in bold are the primary key for each table.

I need to find the companies where for every date in the database (in tDate), their stock price has gone up, without using functions.

Is there a possible way of doing that?

CodePudding user response：

If they only have rising prices, then for the same symbol there's no newer stock with a lower price.

create table Company (
 Symbol varchar(30) primary key, 
 Name varchar(30),
 Sector int default 0, 
 Founded date, 
 Location varchar(30)
);

create table Stock (
 tDate date, 
 Symbol varchar(30), 
 Price decimal(10,2), 
 foreign key (Symbol) references Company(Symbol)
);

insert into Company (Symbol, Name) values
  ('rise', 'Rising Star Inc.')
, ('updo', 'Ups & Downs')

insert into Stock (tDate, Symbol, Price) values
  ('2021-12-01', 'rise', 101.00)
, ('2021-12-02', 'rise', 102.00)
, ('2021-12-03', 'rise', 103.00)
, ('2021-12-04', 'rise', 104.00)

, ('2021-12-01', 'updo', 202.00)
, ('2021-12-02', 'updo', 201.00)
, ('2021-12-03', 'updo', 204.00)
, ('2021-12-04', 'updo', 203.00)

SELECT Symbol
FROM Company
WHERE Symbol IN (
   SELECT Symbol
   FROM Stock
   GROUP BY Symbol
   HAVING COUNT(*) > 1
   EXCEPT
   SELECT DISTINCT Symbol
   FROM Stock s1
   WHERE EXISTS (
     SELECT 1
     FROM Stock s2
     WHERE s2.Symbol = s1.Symbol
       AND s2.tDate > s1.tDate
       AND s2.Price < s1.Price
   )
);

Demo on db<>fiddle here