Probnik/SQLQuery1.sql

USE [master]
GO
/****** Object:  Database [gr672_lii]    Script Date: 31.03.2021 12:50:14 ******/
CREATE DATABASE [gr672_lii]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'gr672_lii', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\gr672_lii.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'gr672_lii_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\gr672_lii_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [gr672_lii] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [gr672_lii].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [gr672_lii] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [gr672_lii] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [gr672_lii] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [gr672_lii] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [gr672_lii] SET ARITHABORT OFF 
GO
ALTER DATABASE [gr672_lii] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [gr672_lii] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [gr672_lii] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [gr672_lii] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [gr672_lii] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [gr672_lii] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [gr672_lii] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [gr672_lii] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [gr672_lii] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [gr672_lii] SET  ENABLE_BROKER 
GO
ALTER DATABASE [gr672_lii] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [gr672_lii] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [gr672_lii] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [gr672_lii] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [gr672_lii] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [gr672_lii] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [gr672_lii] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [gr672_lii] SET RECOVERY FULL 
GO
ALTER DATABASE [gr672_lii] SET  MULTI_USER 
GO
ALTER DATABASE [gr672_lii] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [gr672_lii] SET DB_CHAINING OFF 
GO
ALTER DATABASE [gr672_lii] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [gr672_lii] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [gr672_lii] SET DELAYED_DURABILITY = DISABLED 
GO
EXEC sys.sp_db_vardecimal_storage_format N'gr672_lii', N'ON'
GO
ALTER DATABASE [gr672_lii] SET QUERY_STORE = OFF
GO
USE [gr672_lii]
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
USE [gr672_lii]
GO
/****** Object:  Table [dbo].[OrderInWorkTime]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderInWorkTime](
	[ID_OrderInWorkTime] [int] IDENTITY(1,1) NOT NULL,
	[ID_Order] [int] NOT NULL,
	[ID_WorkTime] [int] NOT NULL,
 CONSTRAINT [PK_OrderInWorkTime] PRIMARY KEY CLUSTERED 
(
	[ID_OrderInWorkTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[ID_Order] [int] IDENTITY(1,1) NOT NULL,
	[Number_Order] [int] NOT NULL,
	[ID_Table] [int] NOT NULL,
	[Quantity_Clients] [int] NOT NULL,
	[ID_ProductInOrder] [int] NOT NULL,
	[ID_Status_Order] [int] NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[ID_Order] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Product]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[ID_Product] [int] IDENTITY(1,1) NOT NULL,
	[Product_Name] [nvarchar](max) NOT NULL,
	[Total_Price] [money] NOT NULL,
	[Time_Cooking] [time](7) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ID_Product] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ProductInOrder]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductInOrder](
	[ID_ProductInOrder] [int] IDENTITY(1,1) NOT NULL,
	[ID_Product] [int] NOT NULL,
	[ID_Order] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_ProductInOrder] PRIMARY KEY CLUSTERED 
(
	[ID_ProductInOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Role]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Role](
	[ID_Role] [int] IDENTITY(1,1) NOT NULL,
	[Name_Role] [nvarchar](13) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
	[ID_Role] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Status]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Status](
	[ID_Status] [int] IDENTITY(1,1) NOT NULL,
	[Name_Status] [nvarchar](8) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
	[ID_Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Status_Order]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Status_Order](
	[ID_Status_Order] [int] IDENTITY(1,1) NOT NULL,
	[Name_Status_Order] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Status_Order] PRIMARY KEY CLUSTERED 
(
	[ID_Status_Order] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Table]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table](
	[ID_Table] [int] NOT NULL,
	[NumberTable] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
(
	[ID_Table] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[User]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
	[ID_User] [int] IDENTITY(1,1) NOT NULL,
	[Surname] [nvarchar](150) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[MiddleName] [nvarchar](150) NULL,
	[ID_Role] [int] NOT NULL,
	[ID_Status] [int] NOT NULL,
	[Login] [nvarchar](150) NOT NULL,
	[Password] [nvarchar](150) NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[ID_User] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserInWorkTime]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInWorkTime](
	[ID_UserInWorkTime] [int] IDENTITY(1,1) NOT NULL,
	[ID_User] [int] NOT NULL,
	[ID_WorkTime] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_UserInWorkTime] PRIMARY KEY CLUSTERED 
(
	[ID_UserInWorkTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WorkTime]    Script Date: 31.03.2021 12:50:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WorkTime](
	[ID_WorkTime] [int] IDENTITY(1,1) NOT NULL,
	[Active] [bit] NOT NULL,
	[ID_OrderInWorkTime] [int] NOT NULL,
	[ID_UserInWorkTime] [int] NOT NULL,
 CONSTRAINT [PK_WorkTime] PRIMARY KEY CLUSTERED 
(
	[ID_WorkTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderInWorkTime]  WITH CHECK ADD  CONSTRAINT [FK_OrderInWorkTime_Orders] FOREIGN KEY([ID_Order])
REFERENCES [dbo].[Orders] ([ID_Order])
GO
ALTER TABLE [dbo].[OrderInWorkTime] CHECK CONSTRAINT [FK_OrderInWorkTime_Orders]
GO
ALTER TABLE [dbo].[OrderInWorkTime]  WITH CHECK ADD  CONSTRAINT [FK_OrderInWorkTime_WorkTime] FOREIGN KEY([ID_WorkTime])
REFERENCES [dbo].[WorkTime] ([ID_WorkTime])
GO
ALTER TABLE [dbo].[OrderInWorkTime] CHECK CONSTRAINT [FK_OrderInWorkTime_WorkTime]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Status_Order] FOREIGN KEY([ID_Status_Order])
REFERENCES [dbo].[Status_Order] ([ID_Status_Order])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Status_Order]
GO
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Table] FOREIGN KEY([ID_Table])
REFERENCES [dbo].[Table] ([ID_Table])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Table]
GO
ALTER TABLE [dbo].[ProductInOrder]  WITH CHECK ADD  CONSTRAINT [FK_ProductInOrder_Orders] FOREIGN KEY([ID_Order])
REFERENCES [dbo].[Orders] ([ID_Order])
GO
ALTER TABLE [dbo].[ProductInOrder] CHECK CONSTRAINT [FK_ProductInOrder_Orders]
GO
ALTER TABLE [dbo].[ProductInOrder]  WITH CHECK ADD  CONSTRAINT [FK_ProductInOrder_Product] FOREIGN KEY([ID_Product])
REFERENCES [dbo].[Product] ([ID_Product])
GO
ALTER TABLE [dbo].[ProductInOrder] CHECK CONSTRAINT [FK_ProductInOrder_Product]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Role] FOREIGN KEY([ID_Role])
REFERENCES [dbo].[Role] ([ID_Role])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Status] FOREIGN KEY([ID_Status])
REFERENCES [dbo].[Status] ([ID_Status])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Status]
GO
ALTER TABLE [dbo].[UserInWorkTime]  WITH CHECK ADD  CONSTRAINT [FK_UserInWorkTime_User] FOREIGN KEY([ID_User])
REFERENCES [dbo].[User] ([ID_User])
GO
ALTER TABLE [dbo].[UserInWorkTime] CHECK CONSTRAINT [FK_UserInWorkTime_User]
GO
ALTER TABLE [dbo].[UserInWorkTime]  WITH CHECK ADD  CONSTRAINT [FK_UserInWorkTime_WorkTime] FOREIGN KEY([ID_WorkTime])
REFERENCES [dbo].[WorkTime] ([ID_WorkTime])
GO
ALTER TABLE [dbo].[UserInWorkTime] CHECK CONSTRAINT [FK_UserInWorkTime_WorkTime]
GO
USE [master]
GO
ALTER DATABASE [gr672_lii] SET  READ_WRITE 
GO