PostgreSQL answers (Q6 to Q12)

======== Q6 ========
CREATE TABLE my_stocks (
    symbol VARCHAR(20) NOT NULL,
    n_shares INTEGER NOT NULL,
    date_acquired DATE NOT NULL
);

CREATE TABLE

\copy my_stocks FROM 'sql/my_stocks.txt' WITH (FORMAT text, DELIMITER E'\t')

COPY 5

SELECT * FROM my_stocks;

 symbol | n_shares | date_acquired 
--------+----------+---------------
 AAPL   |       10 | 2026-01-15
 MSFT   |       20 | 2026-02-10
 GOOG   |        5 | 2026-03-21
 NVDA   |        8 | 2026-04-12
 META   |       12 | 2026-05-05
(5 rows)


======== Q7 ========
CREATE TABLE stock_prices AS
SELECT DISTINCT
    symbol,
    CURRENT_DATE AS quote_date,
    31.415::numeric AS price
FROM my_stocks;

SELECT 5

SELECT * FROM stock_prices ORDER BY symbol;

 symbol | quote_date | price  
--------+------------+--------
 AAPL   | 2026-09-17 | 31.415
 GOOG   | 2026-09-17 | 31.415
 META   | 2026-09-17 | 31.415
 MSFT   | 2026-09-17 | 31.415
 NVDA   | 2026-09-17 | 31.415
(5 rows)

CREATE TABLE newly_acquired_stocks (
    symbol VARCHAR(20) NOT NULL,
    n_shares INTEGER NOT NULL,
    date_acquired DATE NOT NULL
);

CREATE TABLE

INSERT INTO newly_acquired_stocks (symbol, n_shares, date_acquired)
SELECT symbol, n_shares, date_acquired
FROM my_stocks
WHERE date_acquired >= DATE '2026-03-01';

INSERT 0 3

SELECT * FROM newly_acquired_stocks ORDER BY date_acquired;

 symbol | n_shares | date_acquired 
--------+----------+---------------
 GOOG   |        5 | 2026-03-21
 NVDA   |        8 | 2026-04-12
 META   |       12 | 2026-05-05
(3 rows)


======== Q8 ========
SELECT
    m.symbol,
    m.n_shares,
    p.price,
    m.n_shares * p.price AS current_value
FROM my_stocks AS m
INNER JOIN stock_prices AS p
    ON m.symbol = p.symbol
ORDER BY m.symbol;

 symbol | n_shares | price  | current_value 
--------+----------+--------+---------------
 AAPL   |       10 | 31.415 |       314.150
 GOOG   |        5 | 31.415 |       157.075
 META   |       12 | 31.415 |       376.980
 MSFT   |       20 | 31.415 |       628.300
 NVDA   |        8 | 31.415 |       251.320
(5 rows)


======== Q9 ========
INSERT INTO my_stocks (symbol, n_shares, date_acquired)
VALUES ('AIT', 10, CURRENT_DATE);

INSERT 0 1

SELECT
    m.symbol,
    m.n_shares,
    p.price,
    m.n_shares * p.price AS current_value
FROM my_stocks AS m
INNER JOIN stock_prices AS p
    ON m.symbol = p.symbol
ORDER BY m.symbol;

 symbol | n_shares | price  | current_value 
--------+----------+--------+---------------
 AAPL   |       10 | 31.415 |       314.150
 GOOG   |        5 | 31.415 |       157.075
 META   |       12 | 31.415 |       376.980
 MSFT   |       20 | 31.415 |       628.300
 NVDA   |        8 | 31.415 |       251.320
(5 rows)

SELECT
    m.symbol,
    m.n_shares,
    p.price,
    m.n_shares * p.price AS current_value
FROM my_stocks AS m
LEFT OUTER JOIN stock_prices AS p
    ON m.symbol = p.symbol
ORDER BY m.symbol;

 symbol | n_shares | price  | current_value 
--------+----------+--------+---------------
 AAPL   |       10 | 31.415 |       314.150
 AIT    |       10 |        |              
 GOOG   |        5 | 31.415 |       157.075
 META   |       12 | 31.415 |       376.980
 MSFT   |       20 | 31.415 |       628.300
 NVDA   |        8 | 31.415 |       251.320
(6 rows)


======== Q10 ========
CREATE OR REPLACE FUNCTION stock_value(p_symbol TEXT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER;
    total INTEGER := 0;
BEGIN
    FOR i IN 1 .. char_length(p_symbol) LOOP
        total := total + ascii(substr(p_symbol, i, 1));
    END LOOP;
    RETURN total;
END;
$$;

CREATE FUNCTION

SELECT stock_value('IBM') AS stock_value_ibm;

 stock_value_ibm 
-----------------
             216
(1 row)

SELECT stock_value('ibm') AS stock_value_ibm_lower;

 stock_value_ibm_lower 
-----------------------
                   312
(1 row)

UPDATE stock_prices
SET price = stock_value(symbol);

UPDATE 5

SELECT * FROM stock_prices ORDER BY symbol;

 symbol | quote_date | price 
--------+------------+-------
 AAPL   | 2026-09-17 |   286
 GOOG   | 2026-09-17 |   300
 META   | 2026-09-17 |   295
 MSFT   | 2026-09-17 |   314
 NVDA   | 2026-09-17 |   297
(5 rows)

CREATE OR REPLACE FUNCTION portfolio_value()
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
    total NUMERIC := 0;
BEGIN
    FOR rec IN
        SELECT m.n_shares, p.price
        FROM my_stocks AS m
        INNER JOIN stock_prices AS p
            ON m.symbol = p.symbol
    LOOP
        total := total + rec.n_shares * rec.price;
    END LOOP;
    RETURN total;
END;
$$;

CREATE FUNCTION

SELECT portfolio_value();

 portfolio_value 
-----------------
           16556
(1 row)


======== Q11 ========
INSERT INTO my_stocks (symbol, n_shares, date_acquired)
SELECT m.symbol, m.n_shares, CURRENT_DATE
FROM my_stocks AS m
INNER JOIN stock_prices AS p
    ON m.symbol = p.symbol
WHERE p.price > (
    SELECT AVG(price)
    FROM stock_prices
);

INSERT 0 2

SELECT symbol, SUM(n_shares) AS total_shares
FROM my_stocks
GROUP BY symbol
ORDER BY symbol;

 symbol | total_shares 
--------+--------------
 AAPL   |           10
 AIT    |           10
 GOOG   |           10
 META   |           12
 MSFT   |           40
 NVDA   |            8
(6 rows)

SELECT
    m.symbol,
    SUM(m.n_shares) AS total_shares,
    p.price AS price_per_share,
    SUM(m.n_shares) * p.price AS total_current_value
FROM my_stocks AS m
INNER JOIN stock_prices AS p
    ON m.symbol = p.symbol
GROUP BY m.symbol, p.price
ORDER BY m.symbol;

 symbol | total_shares | price_per_share | total_current_value 
--------+--------------+-----------------+---------------------
 AAPL   |           10 |             286 |                2860
 GOOG   |           10 |             300 |                3000
 META   |           12 |             295 |                3540
 MSFT   |           40 |             314 |               12560
 NVDA   |            8 |             297 |                2376
(5 rows)

SELECT
    m.symbol,
    SUM(m.n_shares) AS total_shares,
    SUM(m.n_shares) * p.price AS total_current_value
FROM my_stocks AS m
INNER JOIN stock_prices AS p
    ON m.symbol = p.symbol
GROUP BY m.symbol, p.price
HAVING COUNT(*) >= 2
ORDER BY m.symbol;

 symbol | total_shares | total_current_value 
--------+--------------+---------------------
 GOOG   |           10 |                3000
 MSFT   |           40 |               12560
(2 rows)

SELECT portfolio_value() AS portfolio_value_after_q11;

 portfolio_value_after_q11 
---------------------------
                     24336
(1 row)


======== Q12 ========
CREATE VIEW stocks_i_like AS
SELECT
    m.symbol,
    SUM(m.n_shares) AS total_shares,
    SUM(m.n_shares) * p.price AS total_current_value
FROM my_stocks AS m
INNER JOIN stock_prices AS p
    ON m.symbol = p.symbol
GROUP BY m.symbol, p.price
HAVING COUNT(*) >= 2;

CREATE VIEW

SELECT * FROM stocks_i_like;

 symbol | total_shares | total_current_value 
--------+--------------+---------------------
 GOOG   |           10 |                3000
 MSFT   |           40 |               12560
(2 rows)