Common Table Expression, Sql Server

Common Table Expression

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

CREATE TABLE Products (
    ProductID int,
    ProductName varchar(255),
    ProductCategory varchar(255),
    ProductPrice decimal(10)
    )
    
INSERT INTO Products (
    ProductID, ProductName, ProductCategory, ProductPrice)
VALUES
    (1, 'iPhone X',         'Technology',  1200),
    (2, 'Nike Vapor Shoes', 'Apparel',     180),
    (3, 'Ford Focus',       'Automobile', 18500),
    (4, 'Nissan Altima',    'Automobile', 18500),
    (5, 'Astros Cap',       'Apparel',      80),
    (6, 'Barcelona Jersey', 'Apparel',      90),
    (7, 'Samsung Galaxy',   'Technology',   850),
    (8, 'Apple Watch',      'Technology',   320)

-- Full list
--SELECT * FROM Products

SELECT ProductCategory, COUNT(ProductCategory) AS Counter
FROM Products
GROUP BY ProductCategory
ORDER BY Counter DESC
--WHERE ProductCategory = 'Technology'

CREATE TABLE Sales (
    SaleID int,
    ProductID_fk int
    )
    
INSERT INTO Sales 
    (SaleID, ProductID_fk)
VALUES
    (1, 1),
    (2, 1),
    (3, 2),
    (4, 3),
    (5, 3),
    (6, 3),
    (7, 5),
    (8, 6),
    (9, 6),
    (10, 7),
    (11, 9)
    
SELECT p.ProductName, p.ProductPrice
FROM Sales s 
INNER JOIN Products p 
ON p.ProductID = s.ProductID_fk

SELECT p.ProductName, SUM(p.ProductPrice)
FROM Sales s 
INNER JOIN Products p 
ON p.ProductID = s.ProductID_fk
GROUP BY


run \| edit \| history \| help	0

Please log in to post a comment.