devitfro

USE Storik

-- 1. Показать товары, средняя цена продажи которых была больше 500
SELECT p.name, p.price, c.name AS category_name,  AVG(s.price) AS [avg sale price]
FROM Product p 
JOIN Sale s ON p.id = s.id_product
JOIN Category c ON p.id_category = c.id
GROUP BY p.name, p.price, c.name
HAVING AVG(s.price) > 500

devitfro

USE Storik
-- INNER JOINS:
-- 1. Показать названия и категории товаров, поставщиками которых являются ООО "Паньки" или ООО «Какие люди»
SELECT 
	p.name AS product_name, 
	c.name AS category_name, 
	pr.name AS producer_name
FROM Product p 
JOIN Category c ON p.id_category = c.id
JOIN Producer pr ON pr.id = p.id_producer

devitfro

USE [master]
GO
/****** Object:  Database [Storik]    Script Date: 11.02.2025 23:49:06 ******/
CREATE DATABASE [Storik]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Storik', FILENAME = N'C:\datab\Storik.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Storik_log', FILENAME = N'C:\datab\Storik_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF

devitfro

USE Store;

SELECT *
FROM Product 
ORDER BY date_of_delivery

-- 1. Посчитать возможную выручку за Phones и Tablet (с учётом скидок на эти товары)
SELECT 
	category,
	SUM(ROUND(price - (price * discount/ 100), 2) * quantity ) AS [total price]

devitfro

USE [master]
GO
/****** Object:  Database [Store]    Script Date: 03.02.2025 23:21:19 ******/
CREATE DATABASE [Store]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Store', FILENAME = N'C:\datab\Store.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Store_log', FILENAME = N'C:\datab\Store_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF