Mark-Uri

11111

--SELECT p.name AS [названные],
--    (
--	SELECT COUNT(1) 
--    FROM Sale s 
--    WHERE s.id_product = p.id) AS [количество]
--FROM Product p
--WHERE p.id IN 
--(

Mark-Uri

1111111

SELECT p.name, AVG(s.price) as [средняя цена]
FROM Product p
JOIN Sale s ON p.id = s.id_product
GROUP BY p.name
HAVING AVG(s.price) > 50

22222

Mark-Uri

111111

SELECT DISTINCT  
		Product.name AS Продукт,
		Category.name AS Категория

FROM Product 
INNER JOIN Category ON Product.id_category = Category.id
INNER JOIN Producer ON Product.id_producer = Producer.id

Mark-Uri

USE [master]
GO
/****** Object:  Database [ATB2]    Script Date: 10.02.2025 15:54:21 ******/
CREATE DATABASE [ATB2]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ATB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ATB2.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'ATB2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\ATB2_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF

Mark-Uri

1. Посчитать возможную выручку за хлеб и молоко (с учётом скидок на эти товары)
-- SELECT name,price,discount,
-- SUM(price * (1 - COALESCE(discount, 0)/100)) OVER() AS [Сумма]
-- FROM Product
-- WHERE name LIKE '%Молоко%' OR name LIKE '%Батон'



2. Получить информацию о том, каких товаров вчера и сегодня доставили более 10 штук (getdate, dateadd)

Mark-Uri

USE [master]
GO
/****** Object:  Database [Store]    Script Date: 05.02.2025 21:23:03 ******/
CREATE DATABASE [Store]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Store', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Store.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Store_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Store_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF