USE Testus
GO
SELECT sys.objects.name AS TableName
FROM sys.objects
INNER JOIN sys.columns AS columns1 ON columns1.object_id = sys.objects.object_id
INNER JOIN sys.columns AS columns2 ON columns2.object_id = sys.objects.object_id
WHERE (sys.objects.type = 'U')
AND (columns1.name = 'SalesOrderID')
AND (columns2.name = 'RevisionNumber')
ORDER BY TableName
CREATE OR ALTER PROCEDURE SwitchTable @R INT = 1
AS
IF @R = 1 SELECT * FROM T1
IF @R = 2 SELECT * FROM T2
GO
EXECUTE SwitchTable @P
dbo
.-- Только значимые столбцы
CREATE TABLE exam_mark (ID INT IDENTITY, student_id INT, mark INT)
INSERT exam_mark (student_id, mark )
VALUES (4, 5), (4, 4), (4, 4), (4, 5), (4, 2),
(5, 5), (5, 5), (5, 4), (5, 3), (5, 5),
(6, 2), (6, 2), (6, 3), (6, 2), (6, 2),
(7, 3), (7, 3), (7, 3), (7, 3), (7, 3)
SELECT gd.*
FROM exam_mark AS gd
LEFT JOIN exam_mark AS bd ON bd.student_id = gd.student_id AND bd.mark = 2
-- у кого есть 4, 5 но нет 2
WHERE (gd.mark IN (4, 5)) AND (bd.student_id IS NULL)
CREATE TABLE T (dt DATETIME, t FLOAT)
INSERT T VALUES ( '20060706 00:10', 24.0),
( '20060706 10:00', 25.0),
( '20060706 07:00', 21.0),
( '20060707 01:10', 7.0)
SELECT CONVERT(VARCHAR, dt, 104) AS [Дата],
T.t AS [Температура],
LEFT(CONVERT(VARCHAR, dt, 108),7) AS [Время]
FROM T
INNER JOIN ( SELECT CONVERT(VARCHAR, dt, 112) AS D, MAX(t) AS T
FROM T
GROUP BY CONVERT(VARCHAR, dt, 112)) AS MT
ON MT.D = CONVERT(VARCHAR, T.dt, 112) AND MT.T = T.t
ORDER BY dt
[Провайдер] nvarchar(50) references [Провайдеры]([Название провайдера])
[Название тарифа] nvarchar(50) references [Тарифы всех провайдеров]([Название тарифа])
CREATE TABLE [Клиент]
( id_client INT PRIMARY KEY,
[Фамилия] NVARCHAR(30) NOT NULL,
[Имя] NVARCHAR(20) NOT NULL,
[Отчество] NVARCHAR(20) NOT NULL,
[Паспорт] NVARCHAR(MAX) NOT NULL,
[Адрес] NVARCHAR(MAX) NULL )
CREATE TABLE [Провайдер]
( [Название] NVARCHAR(50) PRIMARY KEY,
[Название суб-провайдера] NVARCHAR(50) NULL,
[ФИО директора] NVARCHAR(100) NOT NULL,
[Телефон директора] CHAR(10) NOT NULL,
[ОКПО] CHAR(10) NOT NULL )
CREATE TABLE [Тариф]
( [Название] NVARCHAR(50) PRIMARY KEY,
[Скорость] INT NOT NULL,
[Стоимость] MONEY NOT NULL,
[Тип] NVARCHAR(50) NOT NULL CHECK ( [Тип] in
( 'Проводной/Лимитный',
'Проводной/безлимитный',
'Беспроводной/Лимитный',
'Беспроводной/Безлимитный' )))
CREATE TABLE [Провайдер Тарифа]
( [Название провайдера] NVARCHAR(50) REFERENCES [Провайдер] ([Название]),
[Название тарифа] NVARCHAR(50) REFERENCES [Тариф]([Название])
PRIMARY KEY ( [Название провайдера],
[Название тарифа]))
CREATE TABLE [Договор]
( [Номер] INT,
id_client INT,
[Провайдер] NVARCHAR(50),
[Тариф] NVARCHAR(50)
PRIMARY KEY ([Номер], id_client),
CONSTRAINT [Договор Клиент]
FOREIGN KEY (id_client) REFERENCES [Клиент]
ON DELETE CASCADE,
FOREIGN KEY([Провайдер], [Тариф])
REFERENCES [Провайдер Тарифа] ([Название провайдера], [Название тарифа]))
DROP TABLE IF EXISTS Orders
DROP TABLE IF EXISTS OrderItems
GO
CREATE TABLE Orders ( row_id INT, parent_id INT, group_name VARCHAR(50) )
CREATE TABLE OrderItems ( order_id INT, price MONEY )
GO
INSERT Orders VALUES (1, NULL, 'test1'),
(2, NULL, 'test2'),
(3, 1, 'test1-3' ),
(4, 3, 'test1-3-4'),
(7, 3, 'test1-3-7'),
(5, 2, 'test2-5' ),
(6, 2, 'test2-6' ),
(8, 2, 'test2-8' ),
(9, 7, 'test1-3-7-9' )
GO
INSERT OrderItems VALUES (1, 11),
(2, 12),
(3, 13),
(4, 14),
(7, 15),
(5, 16),
(6, 17),
(8, 18),
(9, 19)
GO
CREATE OR ALTER FUNCTION dbo.OrderSum (@ORD INT) RETURNS MONEY
AS BEGIN
DECLARE @SUMM MONEY;
WITH calculate_cte(row_id, parent_id, group_name) AS (
SELECT row_id, parent_id, group_name FROM Orders WHERE row_id = @ORD
UNION ALL
SELECT Orders.row_id, Orders.parent_id, Orders.group_name
FROM Orders
INNER JOIN calculate_cte ON calculate_cte.row_id = Orders.parent_id
)
SELECT @SUMM = SUM(price) FROM calculate_cte
INNER JOIN OrderItems ON calculate_cte.row_id = OrderItems.order_id
RETURN @SUMM
END
GO
SELECT dbo.OrderSum(1), dbo.OrderSum(2), dbo.OrderSum(3), dbo.OrderSum(4)
create table [Тарифы всех провайдеров]
(
[Название тарифа] varchar(50) primary key,
[Скорость] integer not null,
[Характеристики] varchar(50) NOT NULL CHECK ( [Характеристики] IN ('Проводной/Лимитный',
'Проводной/безлимитный',
'Беспроводной/Лимитный' ,
'Беспроводной/Безлимитный'))
)