Skip to content

Instantly share code, notes, and snippets.

@malbontee

malbontee/hw1.sql

Created February 22, 2025 13:44
Show Gist options
  • Save malbontee/bed0e4cf09de55cef3581cf99285d6b3 to your computer and use it in GitHub Desktop.
Save malbontee/bed0e4cf09de55cef3581cf99285d6b3 to your computer and use it in GitHub Desktop.
Download ZIP
HW1
Raw
hw1.sql
USE [master]
GO
/****** Object: Database [MyStore] Script Date: 22.02.2025 15:21:32 ******/
CREATE DATABASE [MyStore]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'MyStore', FILENAME = N'C:\Program Files\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyStore.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'MyStore_log', FILENAME = N'C:\Program Files\MSSQL16.MSSQLSERVER\MSSQL\DATA\MyStore_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF
GO
ALTER DATABASE [MyStore] SET COMPATIBILITY_LEVEL = 160
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyStore].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [MyStore] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MyStore] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MyStore] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MyStore] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MyStore] SET ARITHABORT OFF
GO
ALTER DATABASE [MyStore] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [MyStore] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MyStore] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MyStore] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MyStore] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MyStore] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MyStore] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MyStore] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MyStore] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MyStore] SET DISABLE_BROKER
GO
ALTER DATABASE [MyStore] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MyStore] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MyStore] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MyStore] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MyStore] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MyStore] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [MyStore] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [MyStore] SET RECOVERY FULL
GO
ALTER DATABASE [MyStore] SET MULTI_USER
GO
ALTER DATABASE [MyStore] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MyStore] SET DB_CHAINING OFF
GO
ALTER DATABASE [MyStore] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [MyStore] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [MyStore] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [MyStore] SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'MyStore', N'ON'
GO
ALTER DATABASE [MyStore] SET QUERY_STORE = ON
GO
ALTER DATABASE [MyStore] 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 [MyStore]
GO
/****** Object: Table [dbo].[Products] Script Date: 22.02.2025 15:21:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NULL,
[category] [nvarchar](50) NULL,
[price] [real] NULL,
[discount] [real] NULL,
[quantity] [int] NULL,
[measurement] [nvarchar](50) NULL,
[producer] [nvarchar](100) NULL,
[country] [nvarchar](50) NULL,
[supplier] [nvarchar](50) NULL,
[date_of_delivery] [date] NULL,
[expire_date] [date] NULL,
CONSTRAINT [PK_Products] 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].[Products] ON
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (1, N'Пиво ', N'Алкоголь', 219.7, 28, 10, N'л', N'Kronenbourg', N'Украина', N'PivasLover', CAST(N'2025-02-22' AS Date), CAST(N'2026-02-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (2, N'Чипсы', N'Снеки и закуски', 56.99, 15, 5, N'г', N'Lay''s', N'Польша', N'КлубППшников', CAST(N'2025-02-22' AS Date), CAST(N'2025-11-12' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (3, N'Икра черная', N'Рыба', 1299, 0, 1, N'г', N'MoneyByeBye', N'Украина', N'ДорагаБагата', CAST(N'2025-02-22' AS Date), CAST(N'2025-02-28' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (4, N'Колбаски-гриль', N'Мясо', 104, 0, 2, N'г', N'Глобино', N'Украина', N'НямНям', CAST(N'2025-02-22' AS Date), CAST(N'2025-03-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (5, N'Яблоко', N'Фрукты', 3.99, 0, 15, N'г', N'Огород', N'Украина', N'Дядя Степа', CAST(N'2025-02-22' AS Date), CAST(N'2025-06-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (6, N'Сет "Морской коктейль"', N'Рыба', 527, 0, 1, N'шт', N'MoneyByeBye', N'Норвегия', N'ДорагаБагата', CAST(N'2025-02-22' AS Date), CAST(N'2026-02-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (7, N'Пица «Пепероні»', N'Кулинария', 150, 1, 2, N'г', N'Тетя Вера', N'Украина', N'Тетя Вера', CAST(N'2025-02-22' AS Date), CAST(N'2025-02-28' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (8, N'Кабачок', N'Овощи', 11.48, 11, 99, N'г', N'Огород', N'Украина', N'Тетя Галя', CAST(N'2025-02-22' AS Date), CAST(N'2025-03-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (9, N'Сметана', N'Молочные продукты', 33.99, 17, 3, N'г', N'Тетя Галя', N'Украина', N'Тетя Галя', CAST(N'2025-02-22' AS Date), CAST(N'2025-02-28' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (10, N'Огурец', N'Овощи', 16.9, 0, 5, N'г', N'Огород', N'Украина', N'Кукумбер', CAST(N'2025-02-22' AS Date), CAST(N'2025-03-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (11, N'Помидор', N'Овощи', 12.94, 16, 5, N'г', N'Огород', N'Украина', N'СеньорПомидор', CAST(N'2025-02-22' AS Date), CAST(N'2025-03-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (12, N'Артишок', N'Овощи', 179, 0, 15, N'шт', N'Плантация', N'Испания', N'СкаженийАртішок', CAST(N'2025-02-22' AS Date), CAST(N'2025-03-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (13, N'Сигареты', N'Сигареты и стики', 125, 0, 1, N'пачка', N'Terea', N'Греция', N'КлубЗОЖников', CAST(N'2025-02-22' AS Date), CAST(N'2027-02-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (14, N'Сыр с плесенью', N'Сыры', 99.9, 17, 2, N'г', N'La Memee', N'Франция', N'Плесень', CAST(N'2025-02-22' AS Date), CAST(N'2025-08-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (15, N'Хамон', N'Мясные деликатесы', 349.9, 29, 3, N'г', N'Milencinas', N'Испания', N'НямНям', CAST(N'2025-02-22' AS Date), CAST(N'2025-05-31' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (16, N'Квас', N'Напитки', 58.49, 0, 4, N'л', N'Квас Тарас', N'Украина', N'Тарас', CAST(N'2025-02-22' AS Date), CAST(N'2025-04-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (17, N'Вода', N'Напитки', 71.99, 0, 1, N'л', N'Моршинська', N'Украина', N'ОлегВода', CAST(N'2025-02-22' AS Date), CAST(N'2027-02-22' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (18, N'Багет французский', N'Хлеб и выпечка', 34.99, 0, 2, N'г', N'Крафтяр', N'Украина', N'Дядя Степа', CAST(N'2025-02-22' AS Date), CAST(N'2025-02-28' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (19, N'Сосиски веганские', N'Мясные деликатесы', 149, 0, 15, N'г', N'RealПП', N'Украина', N'Веганы', CAST(N'2025-02-22' AS Date), CAST(N'2025-05-31' AS Date))
INSERT [dbo].[Products] ([id], [name], [category], [price], [discount], [quantity], [measurement], [producer], [country], [supplier], [date_of_delivery], [expire_date]) VALUES (20, N'Мед', N'Бакалея', 229, 0, 20, N'г', N'Пчелы', N'Украина', N'Капатыч', CAST(N'2025-02-22' AS Date), NULL)
SET IDENTITY_INSERT [dbo].[Products] OFF
GO
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_discount] DEFAULT ((0)) FOR [discount]
GO
USE [master]
GO
ALTER DATABASE [MyStore] SET READ_WRITE
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment