Creating 18 queries

Store.sql

USE [master]
GO
/****** Object:  Database [Store]    Script Date: 25/12/2025 7:48:31 PM ******/
CREATE DATABASE [Store]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Store', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\Store.mdf' , SIZE = 73728KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Store_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\Store_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [Store] SET COMPATIBILITY_LEVEL = 170
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Store].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Store] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Store] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Store] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Store] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Store] SET ARITHABORT OFF 
GO
ALTER DATABASE [Store] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Store] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Store] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Store] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Store] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Store] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Store] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Store] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Store] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Store] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Store] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Store] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Store] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Store] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Store] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Store] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Store] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Store] SET RECOVERY FULL 
GO
ALTER DATABASE [Store] SET  MULTI_USER 
GO
ALTER DATABASE [Store] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Store] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Store] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Store] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [Store] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [Store] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
ALTER DATABASE [Store] SET OPTIMIZED_LOCKING = OFF 
GO
EXEC sys.sp_db_vardecimal_storage_format N'Store', N'ON'
GO
ALTER DATABASE [Store] SET QUERY_STORE = ON
GO
ALTER DATABASE [Store] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO
USE [Store]
GO
/****** Object:  Table [dbo].[Product]    Script Date: 25/12/2025 7:48:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[category] [nvarchar](50) NOT NULL,
	[price] [real] NOT NULL,
	[discount] [real] NULL,
	[quantity] [int] NOT NULL,
	[measurement] [nvarchar](50) NOT NULL,
	[producer] [nvarchar](50) NOT NULL,
	[country] [nvarchar](50) NOT NULL,
	[supplier] [nvarchar](50) NOT NULL,
	[date_of_delivery] [date] NOT NULL,
	[expire_date] [date] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Product] ON 

INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (68, N'Milk "Milkland" 3.2%', N'Dairy', 89.99, 0.05, 120, N'liter', N'Milkland', N'Germany', N'Euro Dairy Supply', CAST(N'2026-01-10' AS Date), CAST(N'2026-01-25' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (69, N'Natural Yogurt "Danone"', N'Dairy', 65.5, 0, 200, N'piece', N'Danone', N'France', N'European Foods Ltc.', CAST(N'2026-01-15' AS Date), CAST(N'2026-02-10' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (72, N'Kefir "BioKult"', N'Dairy', 75.3, 0.03, 150, N'liter', N'BioKult', N'Poland', N'Central European Foods', CAST(N'2026-01-12' AS Date), CAST(N'2026-01-28' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (73, N'Sour Cream 20%', N'Dairy', 98.5, 0, 110, N'kg', N'Mlekovita', N'Poland', N'Baltic Dairy Froup', CAST(N'2026-01-08' AS Date), CAST(N'2026-02-05' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (74, N'Chicken Eggs L', N'Eggs', 115, 0.08, 300, N'dozen', N'Ovostar', N'Poland', N'Poultry Products EU', CAST(N'2026-01-03' AS Date), CAST(N'2026-02-10' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (84, N'Rye Bread', N'Bakery', 65, 0, 180, N'piece', N'Bon Matin', N'Switzerland', N'European Bakery Supply', CAST(N'2025-12-26' AS Date), CAST(N'2025-12-28' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (85, N'Croissants', N'Bakery', 45, 0.05, 220, N'piece', N'Viennese Baker', N'Austria', N'Pastry Distributors', CAST(N'2026-01-05' AS Date), CAST(N'2026-01-10' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (88, N'Buckwheat Groats', N'Grocery', 98, 0.08, 120, N'kg', N'Poltava', N'Ukraine', N'Central Grain Company', CAST(N'2026-01-15' AS Date), CAST(N'2026-12-31' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (90, N'Sunflower Oils', N'Oils', 145, 0.05, 200, N'liter', N'KTC', N'India', N'Global Oil Traders ', CAST(N'2026-01-20' AS Date), CAST(N'2026-12-20' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (93, N'White Sugar', N'Grocery', 78, 0, 150, N'kg', N'Tate & Lyle ', N'UK', N'Sweet Trade Europe', CAST(N'2026-01-25' AS Date), CAST(N'2027-01-25' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (94, N'Iodized Salt', N'Grocery', 32, 0, 180, N'kg', N'Morton', N'USA', N'Global Salt Company', CAST(N'2026-01-18' AS Date), CAST(N'2028-01-18' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (96, N'Black Tea "Lipton"', N'Beverages', 450, 0.08, 130, N'kg', N'Lipton', N'UK', N'Tea & Coffee Distributors', CAST(N'2026-01-28' AS Date), CAST(N'2027-01-28' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (98, N'Coca-Cola 1.5L', N'Beverages', 98, 0.05, 300, N'bottle', N'Coca-Cola', N'USA', N'Beverages Distributors EU', CAST(N'2026-01-10' AS Date), CAST(N'2026-12-10' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (101, N'Bananas', N'Fruits', 89, 0.05, 180, N'kg', N'Chiquita', N'Ecuador', N'Tropical Fruits Ltd.', CAST(N'2025-12-30' AS Date), CAST(N'2026-01-20' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (103, N'Potatoes', N'Vegetables', 45, 0, 300, N'kg', N'McCain', N'Canada', N'Potato Products Int.', CAST(N'2026-01-15' AS Date), CAST(N'2026-05-15' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (104, N'Onions', N'Vegetables', 38, 0, 220, N'kg', N'Gills Onions', N'USA', N'Vegetable Trade Global', CAST(N'2026-01-20' AS Date), CAST(N'2026-04-20' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (106, N'Cookies "Oreo"', N'Confectionery', 145, 0.08, 190, N'pack', N'Mondelez', N'USA', N'Global Cookie Distributor', CAST(N'2026-01-25' AS Date), CAST(N'2026-07-25' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (108, N'Frozen Vegetable Mix', N'Frozen', 189, 0.05, 120, N'kg', N'Birds Eye', N'UK', N'Frozen Foods Europe', CAST(N'2026-02-12' AS Date), CAST(N'2027-02-12' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (110, N'Chicken Nuggets', N'Frozen', 278, 0.08, 110, N'kg', N'Tyson', N'USA', N'Global Food Supply', CAST(N'2026-01-18' AS Date), CAST(N'2026-06-18' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (111, N'Mineral Water', N'Water', 65, 0, 280, N'bottle', N'Evian', N'France', N'Water Import Europe', CAST(N'2026-01-14' AS Date), CAST(N'2027-01-14' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (112, N'Sparkling Water', N'Water', 45, 0, 250, N'bottle', N'San Pellegrino', N'Italy', N'Beverage Trade Europe', CAST(N'2026-01-09' AS Date), CAST(N'2026-12-09' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (113, N'Tomato Paste', N'Canned', 78, 0.05, 140, N'kg', N'Mutti', N'Italy ', N'Canned Goods Import', CAST(N'2026-02-08' AS Date), CAST(N'2028-02-08' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (114, N'Canned Corn', N'Canned', 56, 0, 160, N'can', N'Green Giant', N'USA', N'Global Canned Foods', CAST(N'2026-01-30' AS Date), CAST(N'2027-01-30' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (116, N'Premium FLour', N'Grocery', 78, 0.05, 130, N'kg', N'Caputo', N'Italy', N'Flour Import Europe', CAST(N'2026-01-16' AS Date), CAST(N'2027-01-16' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (118, N'Mayonnaise', N'Sauces', 98, 0.08, 180, N'kg', N'Hellmann''s', N'USA', N'Sauce Import Europe', CAST(N'2026-01-28' AS Date), CAST(N'2026-07-28' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (120, N'Dijon Mustard', N'Sauces', 89, 0.05, 120, N'kg', N'Maille', N'France', N'Condiment Import EU', CAST(N'2026-01-19' AS Date), CAST(N'2026-12-19' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (121, N'Apple Cider Vinegar', N'Sauces', 67, 0, 110, N'liter', N'Bragg', N'USA', N'Health Food Import', CAST(N'2026-01-24' AS Date), CAST(N'2028-01-24' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (123, N'Spaghetti', N'Grocery', 112, 0.05, 140, N'kg', N'De Cecco', N'Italy', N'Passta Import Europe', CAST(N'2026-01-31' AS Date), CAST(N'2027-01-31' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (132, N'Oatmeal', N'Breakfast', 89, 0.05, 160, N'kg', N'Quaker', N'USA', N'Cereal Products Int.', CAST(N'2026-01-03' AS Date), CAST(N'2026-12-03' AS Date))
INSERT [dbo].[Product] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (133, N'Muesli', N'Breakfast', 189, 0.08, 110, N'kg', N'Alpen', N'UK', N'Health Food Europe', CAST(N'2026-02-18' AS Date), CAST(N'2026-08-18' AS Date))
SET IDENTITY_INSERT [dbo].[Product] OFF
GO
/****** Object:  Index [IX_Product]    Script Date: 25/12/2025 7:48:31 PM ******/
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_Product] ON [dbo].[Product]
(
	[id]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_discount]  DEFAULT ((0)) FOR [discount]
GO
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_1_expire_date] CHECK  (([expire_date]>='2023-01-01'))
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [CK_Product_1_expire_date]
GO
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_date_of_delivery] CHECK  (([date_of_delivery]>='2023-01-01'))
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [CK_Product_date_of_delivery]
GO
USE [master]
GO
ALTER DATABASE [Store] SET  READ_WRITE 
GO




---------------------------------------------------------------------------------------------------
QUERIES

SELECT * FROM Product
-- 1 --
SELECT 
 name,
 price * quantity * (1 - discount) AS [income]
FROM Product
WHERE name = 'Parmesan Cheese'

UNION

SELECT 
 name,
 price * quantity * (1 - discount) AS [income]
FROM Product
WHERE name = 'Natural Yogurt "Danone"'

-- 2 --
SELECT name, quantity, date_of_delivery
FROM Product
WHERE date_of_delivery = GETDATE() OR date_of_delivery = DATEADD(day, -1, GETDATE()) 
AND 
quantity >= 10

-- 3 --
SELECT name, price, date_of_delivery
FROM Product
WHERE date_of_delivery >= DATEADD(month, -1, GETDATE())
AND date_of_delivery <= GETDATE()

-- 4 --
SELECT name, price, category, supplier
FROM Product
WHERE category LIKE 'Dairy'
AND supplier NOT LIKE 'Euro Dairy Supply'

-- 5 --
SELECT *
FROM Product
WHERE name LIKE 'K%'
AND category LIKE '%A%'

-- 6 --
SELECT *
FROM Product
WHERE name LIKE '[B-L]%'

-- 7 --
SELECT *
FROM Product
WHERE price <= 50
AND date_of_delivery < DATEADD(week, -1, GETDATE())

-- 8 --
SELECT *
FROM Product
WHERE category LIKE 'Beverages'
AND category NOT LIKE '%Alcoholic%'
AND quantity > 100

-- 9 --
SELECT *
FROM Product
WHERE price BETWEEN 100 AND 200
ORDER BY price ASC

-- 10 --
SELECT name, price * (1 - 0.05) AS [price]
FROM Product

-- 11 --
UPDATE Product
SET date_of_delivery = GETDATE()
WHERE date_of_delivery IS NULL

-- 12 --
DELETE FROM Product
WHERE quantity < 100 AND price > 70

-- 13 --
DELETE FROM Product
WHERE category = 'Beverages (Alcoholic)'
OR category = 'Chocolate';

-- 14 --
SELECT *
FROM Product
WHERE name LIKE '%o%o%o%'
AND name NOT LIKE '%o%o%o%o%'

-- 15 --
DELETE FROM Product
WHERE LEN(name) = 5

-- 16 --
DELETE FROM Product
WHERE date_of_delivery < DATEADD(month, -3, GETDATE())

-- 17 --
SELECT TOP 5 name, price
FROM Product
ORDER BY 2 DESC

-- 18 --
DELETE FROM Product
WHERE supplier IS NULL
OR discount >= 0.10