hw3.sql

USE [master]
GO
/****** Object:  Database [productDatabase]    Script Date: 1/9/2026 12:24:38 AM ******/
CREATE DATABASE [productDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'productDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\productDB.mdf' , SIZE = 15360KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'productDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\productDBlog.ldf' , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [productDatabase] SET COMPATIBILITY_LEVEL = 170
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [productDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [productDatabase] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [productDatabase] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [productDatabase] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [productDatabase] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [productDatabase] SET ARITHABORT OFF 
GO
ALTER DATABASE [productDatabase] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [productDatabase] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [productDatabase] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [productDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [productDatabase] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [productDatabase] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [productDatabase] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [productDatabase] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [productDatabase] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [productDatabase] SET  DISABLE_BROKER 
GO
ALTER DATABASE [productDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [productDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [productDatabase] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [productDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [productDatabase] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [productDatabase] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [productDatabase] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [productDatabase] SET RECOVERY FULL 
GO
ALTER DATABASE [productDatabase] SET  MULTI_USER 
GO
ALTER DATABASE [productDatabase] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [productDatabase] SET DB_CHAINING OFF 
GO
ALTER DATABASE [productDatabase] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [productDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [productDatabase] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [productDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
ALTER DATABASE [productDatabase] SET OPTIMIZED_LOCKING = OFF 
GO
EXEC sys.sp_db_vardecimal_storage_format N'productDatabase', N'ON'
GO
ALTER DATABASE [productDatabase] SET QUERY_STORE = ON
GO
ALTER DATABASE [productDatabase] 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 [productDatabase]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Category] 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
/****** Object:  Table [dbo].[City]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[id_country] [int] NOT NULL,
 CONSTRAINT [PK_City] 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
/****** Object:  Table [dbo].[Country]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[id_region] [int] NOT NULL,
 CONSTRAINT [PK_Country] 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
/****** Object:  Table [dbo].[Delivery]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Delivery](
	[id] [int] NOT NULL,
	[id_product] [int] NOT NULL,
	[id_supplier] [int] NOT NULL,
	[price] [money] NOT NULL,
	[quantity] [int] NOT NULL,
	[date_of_delivery] [date] NOT NULL,
 CONSTRAINT [PK_Delivery] 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
/****** Object:  Table [dbo].[Discount]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Discount](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[percentage] [tinyint] NOT NULL,
 CONSTRAINT [PK_Discount] 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
/****** Object:  Table [dbo].[Measurement]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Measurement](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Measurement] 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
/****** Object:  Table [dbo].[Producer]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Producer](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[id_address] [int] NULL,
 CONSTRAINT [PK_Producer] 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
/****** Object:  Table [dbo].[productTable]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[productTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[id_category] [int] NOT NULL,
	[price] [money] NULL,
	[quantity] [int] NULL,
	[id_discount] [int] NOT NULL,
	[id_measurement] [int] NOT NULL,
	[id_producer] [int] NOT NULL,
 CONSTRAINT [PK_productTable] 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
/****** Object:  Table [dbo].[Region]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Region](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Region] 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
/****** Object:  Table [dbo].[Sale]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sale](
	[id] [int] NOT NULL,
	[id_product] [int] NOT NULL,
	[price] [money] NOT NULL,
	[quantity] [int] NOT NULL,
	[date_of_sale] [date] NOT NULL,
 CONSTRAINT [PK_Sale] 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
/****** Object:  Table [dbo].[StreetAddress]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StreetAddress](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[id_city] [int] NOT NULL,
 CONSTRAINT [PK_StreetAddress] 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
/****** Object:  Table [dbo].[Supplier]    Script Date: 1/9/2026 12:24:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Supplier](
	[id] [int] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[id_address] [int] NOT NULL,
 CONSTRAINT [PK_Supplier] 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
INSERT [dbo].[Category] ([id], [name]) VALUES (1, N'Snacks')
INSERT [dbo].[Category] ([id], [name]) VALUES (2, N'Bakery')
INSERT [dbo].[Category] ([id], [name]) VALUES (3, N'Dairy')
INSERT [dbo].[Category] ([id], [name]) VALUES (4, N'Drinks')
INSERT [dbo].[Category] ([id], [name]) VALUES (5, N'Alcohol')
INSERT [dbo].[Category] ([id], [name]) VALUES (6, N'Baking')
GO
INSERT [dbo].[City] ([id], [name], [id_country]) VALUES (1, N'city1', 1)
INSERT [dbo].[City] ([id], [name], [id_country]) VALUES (2, N'city2', 1)
INSERT [dbo].[City] ([id], [name], [id_country]) VALUES (3, N'city3', 1)
INSERT [dbo].[City] ([id], [name], [id_country]) VALUES (4, N'city4', 2)
INSERT [dbo].[City] ([id], [name], [id_country]) VALUES (5, N'city5', 3)
GO
INSERT [dbo].[Country] ([id], [name], [id_region]) VALUES (1, N'Ukraine', 1)
INSERT [dbo].[Country] ([id], [name], [id_region]) VALUES (2, N'Moldova', 1)
INSERT [dbo].[Country] ([id], [name], [id_region]) VALUES (3, N'Poland', 1)
INSERT [dbo].[Country] ([id], [name], [id_region]) VALUES (4, N'Mexico', 2)
INSERT [dbo].[Country] ([id], [name], [id_region]) VALUES (5, N'China', 3)
GO
INSERT [dbo].[Delivery] ([id], [id_product], [id_supplier], [price], [quantity], [date_of_delivery]) VALUES (1, 13, 4, 5000.0000, 500, CAST(N'2026-11-20' AS Date))
INSERT [dbo].[Delivery] ([id], [id_product], [id_supplier], [price], [quantity], [date_of_delivery]) VALUES (2, 14, 4, 15000.0000, 1000, CAST(N'2026-11-20' AS Date))
INSERT [dbo].[Delivery] ([id], [id_product], [id_supplier], [price], [quantity], [date_of_delivery]) VALUES (3, 20, 7, 40000.0000, 10000, CAST(N'2026-11-22' AS Date))
INSERT [dbo].[Delivery] ([id], [id_product], [id_supplier], [price], [quantity], [date_of_delivery]) VALUES (4, 22, 7, 50000.0000, 5000, CAST(N'2026-11-22' AS Date))
INSERT [dbo].[Delivery] ([id], [id_product], [id_supplier], [price], [quantity], [date_of_delivery]) VALUES (5, 30, 8, 30000.0000, 1500, CAST(N'2026-11-21' AS Date))
GO
INSERT [dbo].[Discount] ([id], [name], [percentage]) VALUES (1, N'none', 0)
INSERT [dbo].[Discount] ([id], [name], [percentage]) VALUES (2, N'very low', 10)
INSERT [dbo].[Discount] ([id], [name], [percentage]) VALUES (3, N'low', 20)
INSERT [dbo].[Discount] ([id], [name], [percentage]) VALUES (4, N'normal', 30)
INSERT [dbo].[Discount] ([id], [name], [percentage]) VALUES (5, N'big', 50)
INSERT [dbo].[Discount] ([id], [name], [percentage]) VALUES (6, N'huge', 65)
GO
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (1, N'SOLIDsmall', N'10g - 250g')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (2, N'SOLIDmedium', N'251g - 1kg')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (3, N'SOLIDlarge', N'1kg - 2kg')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (4, N'SOLIDhuge', N'larger than 2kg')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (5, N'LIQUIDsmall', N'10ml - 250ml')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (6, N'LIQUIDmedium', N'251ml - 1L')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (7, N'LIQUIDlarge', N'1L - 2L')
INSERT [dbo].[Measurement] ([id], [name], [description]) VALUES (8, N'LIQUIDhuge', N'larger than 2L')
GO
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (1, N'Mondelez', 1)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (2, N'candyproducer1', 2)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (3, N'magicalcandy22', 3)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (4, N'bakery23', 4)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (5, N'dairyfarm33', NULL)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (6, N'citywater11', 6)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (7, N'fizzy11drinks', 6)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (8, N'naturalgreen42', 5)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (9, N'greenzone32', 6)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (10, N'drybreadproductions', 1)
INSERT [dbo].[Producer] ([id], [name], [id_address]) VALUES (11, N'superiourwines65', 6)
GO
SET IDENTITY_INSERT [dbo].[productTable] ON 

INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (1, N'chips1', 1, 50.0000, 1000, 4, 1, 1)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (4, N'chips2', 1, 60.0000, 1000, 1, 2, 1)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (6, N'chips3', 1, 90.0000, 1000, 1, 2, 1)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (8, N'candy1', 1, 20.0000, 10000, 3, 1, 2)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (9, N'candy2', 1, 35.0000, 10000, 3, 1, 3)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (11, N'candy3', 1, 10.0000, 50000, 1, 1, 3)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (13, N'bread1', 2, 20.0000, 500, 1, 2, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (14, N'bread2', 2, 30.0000, 1000, 1, 3, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (15, N'cinnabun', 2, 25.0000, 1000, 2, 1, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (16, N'honeybun', 2, 20.0000, 1000, 2, 1, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (17, N'milk1', 3, 50.0000, 10000, 1, 7, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (18, N'milk2', 3, 80.0000, 10000, 5, 7, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (19, N'butter1', 3, 90.0000, 10000, 1, 1, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (20, N'water1', 4, 10.0000, 10000, 1, 6, 6)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (22, N'water2', 4, 25.0000, 10000, 1, 7, 6)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (24, N'water3', 4, 81.0000, 10000, 1, 8, 6)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (25, N'fizzydrink1', 4, 25.0000, 5000, 1, 6, 7)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (28, N'fizzydrink2', 4, 30.0000, 5000, 1, 7, 7)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (29, N'rootbeer1', 4, 45.0000, 2500, 1, 6, 7)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (30, N'beer1', 5, 20.0000, 3000, 1, 6, 7)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (32, N'yogurt1', 3, 28.0000, 8000, 1, 4, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (33, N'yogurt2', 3, 32.0000, 6000, 1, 5, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (34, N'cheese1', 3, 120.0000, 4000, 1, 3, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (36, N'cheese2', 3, 150.0000, 3000, 1, 3, 5)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (37, N'juice1', 4, 22.0000, 9000, 1, 6, 8)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (39, N'juice2', 4, 30.0000, 7000, 1, 6, 9)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (40, N'sparklingwater1', 4, 13.0000, 5000, 1, 6, 6)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (42, N'sparklingwater2', 4, 30.0000, 3500, 1, 7, 6)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (43, N'cookie1', 1, 15.0000, 15000, 1, 1, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (44, N'cookie2', 1, 20.0000, 14000, 1, 2, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (45, N'crackers1', 1, 25.0000, 10000, 1, 2, 10)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (46, N'crackers2', 1, 30.0000, 9000, 4, 2, 10)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (47, N'cake1', 2, 180.0000, 3000, 5, 3, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (48, N'cake2', 2, 300.0000, 1500, 6, 4, 4)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (49, N'wine1', 5, 180.0000, 2000, 1, 6, 11)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (51, N'wine2', 5, 210.0000, 1500, 1, 6, 11)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (53, N'sugar1', 6, 40.0000, 6000, 1, 2, 2)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (54, N'sugar2', 6, 60.0000, 5000, 1, 3, 2)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (55, N'flour1', 6, 30.0000, 3000, 3, 2, 2)
INSERT [dbo].[productTable] ([id], [name], [id_category], [price], [quantity], [id_discount], [id_measurement], [id_producer]) VALUES (56, N'flour2', 6, 45.0000, 5000, 3, 3, 2)
SET IDENTITY_INSERT [dbo].[productTable] OFF
GO
INSERT [dbo].[Region] ([id], [name]) VALUES (1, N'EU')
INSERT [dbo].[Region] ([id], [name]) VALUES (2, N'NA')
INSERT [dbo].[Region] ([id], [name]) VALUES (3, N'AS')
GO
INSERT [dbo].[Sale] ([id], [id_product], [price], [quantity], [date_of_sale]) VALUES (1, 1, 140.0000, 4, CAST(N'2026-11-22' AS Date))
INSERT [dbo].[Sale] ([id], [id_product], [price], [quantity], [date_of_sale]) VALUES (2, 4, 120.0000, 2, CAST(N'2026-11-23' AS Date))
INSERT [dbo].[Sale] ([id], [id_product], [price], [quantity], [date_of_sale]) VALUES (3, 8, 160.0000, 10, CAST(N'2026-11-20' AS Date))
INSERT [dbo].[Sale] ([id], [id_product], [price], [quantity], [date_of_sale]) VALUES (4, 16, 45.0000, 2, CAST(N'2026-11-20' AS Date))
INSERT [dbo].[Sale] ([id], [id_product], [price], [quantity], [date_of_sale]) VALUES (5, 18, 200.0000, 5, CAST(N'2026-11-21' AS Date))
GO
INSERT [dbo].[StreetAddress] ([id], [name], [id_city]) VALUES (1, N'street1123', 1)
INSERT [dbo].[StreetAddress] ([id], [name], [id_city]) VALUES (2, N'street2132', 1)
INSERT [dbo].[StreetAddress] ([id], [name], [id_city]) VALUES (3, N'street3422', 1)
INSERT [dbo].[StreetAddress] ([id], [name], [id_city]) VALUES (4, N'mstreet43', 2)
INSERT [dbo].[StreetAddress] ([id], [name], [id_city]) VALUES (5, N'vstreet5', 2)
INSERT [dbo].[StreetAddress] ([id], [name], [id_city]) VALUES (6, N'dstreet66', 2)
GO
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (1, N'supplier1', 1)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (2, N'supplier2', 2)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (3, N'supplier3', 3)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (4, N'bakery23', 4)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (5, N'supplydairy1', 4)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (6, N'supplydairy2', 5)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (7, N'supplierwater', 6)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (8, N'fizzy11drinks', 6)
INSERT [dbo].[Supplier] ([id], [name], [id_address]) VALUES (9, N'supplier4', 5)
GO
ALTER TABLE [dbo].[City]  WITH CHECK ADD  CONSTRAINT [FK_City_Country] FOREIGN KEY([id_country])
REFERENCES [dbo].[Country] ([id])
GO
ALTER TABLE [dbo].[City] CHECK CONSTRAINT [FK_City_Country]
GO
ALTER TABLE [dbo].[Country]  WITH CHECK ADD  CONSTRAINT [FK_Country_Region] FOREIGN KEY([id_region])
REFERENCES [dbo].[Region] ([id])
GO
ALTER TABLE [dbo].[Country] CHECK CONSTRAINT [FK_Country_Region]
GO
ALTER TABLE [dbo].[Delivery]  WITH CHECK ADD  CONSTRAINT [FK_Delivery_productTable] FOREIGN KEY([id_product])
REFERENCES [dbo].[productTable] ([id])
GO
ALTER TABLE [dbo].[Delivery] CHECK CONSTRAINT [FK_Delivery_productTable]
GO
ALTER TABLE [dbo].[Delivery]  WITH CHECK ADD  CONSTRAINT [FK_Delivery_Supplier] FOREIGN KEY([id_supplier])
REFERENCES [dbo].[Supplier] ([id])
GO
ALTER TABLE [dbo].[Delivery] CHECK CONSTRAINT [FK_Delivery_Supplier]
GO
ALTER TABLE [dbo].[Producer]  WITH CHECK ADD  CONSTRAINT [FK_Producer_StreetAddress] FOREIGN KEY([id_address])
REFERENCES [dbo].[StreetAddress] ([id])
GO
ALTER TABLE [dbo].[Producer] CHECK CONSTRAINT [FK_Producer_StreetAddress]
GO
ALTER TABLE [dbo].[productTable]  WITH CHECK ADD  CONSTRAINT [FK_productTable_Category] FOREIGN KEY([id_category])
REFERENCES [dbo].[Category] ([id])
GO
ALTER TABLE [dbo].[productTable] CHECK CONSTRAINT [FK_productTable_Category]
GO
ALTER TABLE [dbo].[productTable]  WITH CHECK ADD  CONSTRAINT [FK_productTable_Discount] FOREIGN KEY([id_discount])
REFERENCES [dbo].[Discount] ([id])
GO
ALTER TABLE [dbo].[productTable] CHECK CONSTRAINT [FK_productTable_Discount]
GO
ALTER TABLE [dbo].[productTable]  WITH CHECK ADD  CONSTRAINT [FK_productTable_Measurement] FOREIGN KEY([id_measurement])
REFERENCES [dbo].[Measurement] ([id])
GO
ALTER TABLE [dbo].[productTable] CHECK CONSTRAINT [FK_productTable_Measurement]
GO
ALTER TABLE [dbo].[productTable]  WITH CHECK ADD  CONSTRAINT [FK_productTable_Producer] FOREIGN KEY([id_producer])
REFERENCES [dbo].[Producer] ([id])
GO
ALTER TABLE [dbo].[productTable] CHECK CONSTRAINT [FK_productTable_Producer]
GO
ALTER TABLE [dbo].[Sale]  WITH CHECK ADD  CONSTRAINT [FK_Sale_productTable] FOREIGN KEY([id_product])
REFERENCES [dbo].[productTable] ([id])
GO
ALTER TABLE [dbo].[Sale] CHECK CONSTRAINT [FK_Sale_productTable]
GO
ALTER TABLE [dbo].[StreetAddress]  WITH CHECK ADD  CONSTRAINT [FK_StreetAddress_City] FOREIGN KEY([id_city])
REFERENCES [dbo].[City] ([id])
GO
ALTER TABLE [dbo].[StreetAddress] CHECK CONSTRAINT [FK_StreetAddress_City]
GO
ALTER TABLE [dbo].[Supplier]  WITH CHECK ADD  CONSTRAINT [FK_Supplier_StreetAddress] FOREIGN KEY([id_address])
REFERENCES [dbo].[StreetAddress] ([id])
GO
ALTER TABLE [dbo].[Supplier] CHECK CONSTRAINT [FK_Supplier_StreetAddress]
GO
USE [master]
GO
ALTER DATABASE [productDatabase] SET  READ_WRITE 
GO