Все манипуляции представлены в виде кода команды и результата ее выполнения. Исходные состояния таблиц приведены в предыдущем разделе

Перед каждой демонстрацией состояние базы данных сбрасывается к исходному!

SELECT, объединяющий две и более таблицы с простым условием:

SELECT a.Nick_Name, b.Name as 'Type', 'Type (case-when)' =

CASE a.Account_Type

WHEN 1 THEN 'Teacher'

WHEN 2 THEN 'Just a student'

WHEN 3 THEN 'Banned User'

WHEN 4 THEN 'Administator'

ELSE 'Unknown type = ' + CONVERT(nvarchar, a.Account_Type)

END,

'Type description' = b.Description, FLOOR((COS(PI()*b.Id/5))*1000)/1000 as 'Just an experiment'

FROM dbo.Account a, dbo.[Type] b -- Type is a keyword, so that's why we put it in brackets

WHERE a.Account_Type = b.Id;

SELECT a.Full_Name, t.Name, q.Name

FROM dbo.Account a, dbo.TestCase t, dbo.Question q

WHERE (a.Id = t.Id_Creator AND t.Id = q.Id_Test)

SELECT a.Full_Name, t.Name, s.Start_Time, 'Start time (Day of week)' = DATENAME(WEEKDAY, s.Start_Time), DATEDIFF(DAY, s.Start_Time, SYSDATETIME()) as 'Days ago'

FROM dbo.Account a, dbo.[Session] s, dbo.TestCase t

WHERE a.Id = s.Id_Account AND s.Id_Test = t.Id;

SELECT, объединяющий две и более таблицы со сложным условием:

SELECT a.Nick_Name, b.Name as 'Type', 'Type description' = b.Description, FLOOR((COS(PI()*b.Id/5))*1000)/1000 as 'Just an experiment'

FROM dbo.Account a, dbo.[Type] b -- Type is a keyword, so that's why we put it in brackets

WHERE a.Account_Type = b.Id AND CONVERT(nvarchar, FLOOR((COS(PI()*b.Id/5))*1000)/1000) LIKE '%8%'; -- there is some questions

DECLARE @SearchWord varchar(30);

SET @SearchWord ='БД';

SELECT a.Full_Name, t.Name, q.Name

FROM dbo.Account a, dbo.TestCase t, dbo.Question q

WHERE (a.Id = t.Id_Creator AND t.Id = q.Id_Test AND t.Name LIKE '%'+@SearchWord+'%');

SELECT a.Full_Name, t.Name, s.Start_Time, 'Start time (Day of week)' = DATENAME(WEEKDAY, s.Start_Time), DATEDIFF(DAY, s.Start_Time, SYSDATETIME()) as 'Days ago'

FROM dbo.Account a, dbo.[Session] s, dbo.TestCase t

WHERE a.Id = s.Id_Account AND s.Id_Test = t.Id AND DATEDIFF(DAY, s.Start_Time, SYSDATETIME()) BETWEEN 10 AND 20;