In the last lesson you started SQL Server and created a first table. Now you learn to ask questions of data: Which contacts have no email address? How many appointments does each company have? Who is on page 2 of the contact list?
SQL is different from C# here. In C# you write how something is calculated: a loop, a condition, a list that grows. In SQL you write
what you want to have, and the server decides by itself how to fetch it. That is called declarative. You already know the way of
thinking from LINQ in module 1.5: Where, OrderBy, GroupBy, and Select have their models in SQL. In lesson 2.4.9 the circle
closes, when EF Core translates your LINQ into exactly the SQL you learn here.
This lesson has no step for the Kladde. You practice on a small database of your own so that nothing can go wrong.
The practice database
In your practice folder k2-practice, create a folder sql-queries. First comes the file practice.sql. It creates a database
practice with three tables and fills them with invented companies, contacts, and appointments:
-- practice.sql: the practice database of lesson 2.4.2. Every person and company in it is invented.
-- Running it again starts from scratch: it drops the database "practice" first.
IF DB_ID(N'practice') IS NOT NULL
BEGIN
ALTER DATABASE practice SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE practice;
END;
GO
CREATE DATABASE practice;
GO
USE practice;
GO
CREATE TABLE dbo.Companies
(
Id int NOT NULL CONSTRAINT PK_Companies PRIMARY KEY,
Name nvarchar(100) NOT NULL,
City nvarchar(100) NOT NULL
);
CREATE TABLE dbo.Contacts
(
Id int NOT NULL CONSTRAINT PK_Contacts PRIMARY KEY,
Name nvarchar(100) NOT NULL,
Email nvarchar(254) NULL,
CompanyId int NULL CONSTRAINT FK_Contacts_Companies REFERENCES dbo.Companies (Id),
CreatedOn date NOT NULL
);
CREATE TABLE dbo.Appointments
(
Id int NOT NULL CONSTRAINT PK_Appointments PRIMARY KEY,
Title nvarchar(200) NOT NULL,
Start datetimeoffset(0) NOT NULL,
DurationMinutes int NOT NULL,
ContactId int NULL CONSTRAINT FK_Appointments_Contacts REFERENCES dbo.Contacts (Id)
);
INSERT INTO dbo.Companies (Id, Name, City)
VALUES (1, N'Kranich Software', N'Hamburg'),
(2, N'Lindenhof Practice', N'Bremen'),
(3, N'Elbweg Logistics', N'Hamburg'),
(4, N'Sunny Slope Bakery', N'Lüneburg'),
(5, N'Harbor View Tax Advisors', N'Kiel');
INSERT INTO dbo.Contacts (Id, Name, Email, CompanyId, CreatedOn)
VALUES (1, N'Ada Sample', N'ada@example.com', 1, '2024-11-04'),
(2, N'Bernd Example', N'bernd.example@example.com', 1, '2024-11-18'),
(3, N'Carla Trial', NULL, 2, '2025-01-09'),
(4, N'David Fictional', N'david@example.net', 3, '2025-01-20'),
(5, N'Emma Invented', N'emma@example.org', NULL, '2025-02-03'),
(6, N'Felix Placeholder', NULL, NULL, '2025-02-11'),
(7, N'Greta Imagined', N'greta@example.com', 4, '2025-02-17'),
(8, N'Hanna Template', N'hanna.template@example.net', 3, '2025-02-24'),
(9, N'Ivo Assumed', N'ivo@example.org', 1, '2025-03-01'),
(10, N'Jana Draft', NULL, 4, '2025-03-02');
INSERT INTO dbo.Appointments (Id, Title, Start, DurationMinutes, ContactId)
VALUES (1, N'Intro call', '2025-03-03T09:00:00+01:00', 60, 1),
(2, N'Discuss quote', '2025-03-03T14:00:00+01:00', 30, 4),
(3, N'Server maintenance', '2025-03-04T08:00:00+01:00', 120, NULL),
(4, N'Callback', '2025-03-04T11:30:00+01:00', 15, 3),
(5, N'Accounting training', '2025-03-05T09:00:00+01:00', 180, 7),
(6, N'Team meeting', '2025-03-06T10:00:00+01:00', 45, NULL),
(7, N'Sign contract', '2025-03-06T15:00:00+01:00', 30, 1),
(8, N'Callback', '2025-03-07T09:30:00+01:00', 15, 9),
(9, N'Check network', '2025-03-10T08:30:00+01:00', 90, 8),
(10, N'Intro call', '2025-03-10T13:00:00+01:00', 60, 5),
(11, N'Callback', '2025-03-11T16:00:00+01:00', 15, 2),
(12, N'Discuss quote', '2025-03-12T10:00:00+01:00', 45, 7),
(13, N'Team meeting', '2025-03-13T10:00:00+01:00', 45, NULL),
(14, N'Checkout training', '2025-03-14T08:00:00+01:00', 240, 10),
(15, N'Callback', '2025-03-14T12:00:00+01:00', 15, 4);
What you read in it:
- Three tables.
Companiesare companies,Contactsare contacts,Appointmentsare appointments. A contact can belong to a company (CompanyId), an appointment can have a contact (ContactId). Internal appointments like the team meeting have none. NULLstands for “no value”: Carla Trial has no email address, Emma Invented belongs to no company. What that means for queries is one of the most important sections of this lesson.REFERENCESis a foreign key: only a number that exists inCompanies.Idmay appear inContacts.CompanyId. An appointment for a contact 99 is rejected. Foreign keys and the other constraints are the topic of the next lesson.datetimeoffset(0)you know from lesson 2.4.1: a point in time with its offset from UTC. All appointments fall in March before the clocks change in Germany (March 30, 2025), which is why+01:00appears everywhere.ALTER DATABASE … SET SINGLE_USER WITH ROLLBACK IMMEDIATEat the start disconnects all open connections to the old database, for example your editor’s. Without this lineDROP DATABASEfails as long as anyone is still connected. That way you can run the script again at any time and are back at the starting state.
Creating and checking with check.cs
To create the database and later to check your answers, you get a small C# program. Save it as check.cs in the same folder:
// check.cs: creates the practice database (dotnet run check.cs -- create)
// and compares your answers answers/01.sql to answers/10.sql with the expected results (dotnet run check.cs).
#:package Microsoft.Data.SqlClient@7.1.1
#:package System.Security.Cryptography.Pkcs@10.0.14
#:property PublishAot=false
using System.Globalization;
using System.Text.RegularExpressions;
using Microsoft.Data.SqlClient;
string? kladde = Environment.GetEnvironmentVariable("ConnectionStrings__Kladde");
if (string.IsNullOrWhiteSpace(kladde))
{
Console.WriteLine("The environment variable ConnectionStrings__Kladde is missing (lesson 2.4.1).");
return 2;
}
if (args is ["create"])
{
await using var master = new SqlConnection(new SqlConnectionStringBuilder(kladde) { InitialCatalog = "master" }.ConnectionString);
await master.OpenAsync();
string script = await File.ReadAllTextAsync("practice.sql");
foreach (string batch in Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline))
{
if (batch.Trim().Length > 0)
{
await using var command = new SqlCommand(batch, master);
await command.ExecuteNonQueryAsync();
}
}
Console.WriteLine("The practice database is created.");
return 0;
}
await using var connection = new SqlConnection(new SqlConnectionStringBuilder(kladde) { InitialCatalog = "practice" }.ConnectionString);
await connection.OpenAsync();
string[][] expected =
[
["Carla Trial", "Felix Placeholder", "Jana Draft"],
[
"Check network | 2025-03-10 08:30", "Intro call | 2025-03-10 13:00", "Callback | 2025-03-11 16:00",
"Discuss quote | 2025-03-12 10:00", "Team meeting | 2025-03-13 10:00", "Checkout training | 2025-03-14 08:00",
"Callback | 2025-03-14 12:00",
],
[
"Ada Sample | Kranich Software", "Bernd Example | Kranich Software", "David Fictional | Elbweg Logistics",
"Hanna Template | Elbweg Logistics", "Ivo Assumed | Kranich Software",
],
[
"Kranich Software | 3", "Elbweg Logistics | 2", "Sunny Slope Bakery | 2", "Lindenhof Practice | 1",
"Harbor View Tax Advisors | 0",
],
[
"Intro call | 2025-03-03 09:00 | Ada Sample", "Discuss quote | 2025-03-03 14:00 | David Fictional",
"Server maintenance | 2025-03-04 08:00 | (internal)", "Callback | 2025-03-04 11:30 | Carla Trial",
"Accounting training | 2025-03-05 09:00 | Greta Imagined", "Team meeting | 2025-03-06 10:00 | (internal)",
"Sign contract | 2025-03-06 15:00 | Ada Sample", "Callback | 2025-03-07 09:30 | Ivo Assumed",
],
["Jana Draft | 240", "Greta Imagined | 225", "Ada Sample | 90", "Hanna Template | 90"],
["3"],
["Emma Invented", "Felix Placeholder", "Greta Imagined", "Hanna Template"],
["Felix Placeholder"],
["2025-03-03 | 2 | 09:00", "2025-03-04 | 2 | 08:00", "2025-03-06 | 2 | 10:00", "2025-03-10 | 2 | 08:30", "2025-03-14 | 2 | 08:00"],
];
int correct = 0;
for (int question = 1; question <= expected.Length; question++)
{
string file = Path.Combine("answers", $"{question:00}.sql");
if (!File.Exists(file))
{
Console.WriteLine($"Question {question}: {file} is missing");
continue;
}
List<string> rows;
try
{
rows = await ReadRowsAsync(connection, await File.ReadAllTextAsync(file));
}
catch (SqlException exception)
{
Console.WriteLine($"Question {question}: error {exception.Number}: {exception.Message}");
continue;
}
string[] want = expected[question - 1];
if (rows.SequenceEqual(want))
{
Console.WriteLine($"Question {question}: correct");
correct++;
continue;
}
Console.WriteLine($"Question {question}: not correct yet ({rows.Count} rows, expected {want.Length})");
for (int i = 0; i < Math.Min(rows.Count, want.Length); i++)
{
if (rows[i] != want[i])
{
Console.WriteLine($" Row {i + 1}: {rows[i]}");
Console.WriteLine($" expected: {want[i]}");
break;
}
}
}
Console.WriteLine($"{correct} of {expected.Length} correct");
return correct == expected.Length ? 0 : 1;
static async Task<List<string>> ReadRowsAsync(SqlConnection connection, string sql)
{
await using var command = new SqlCommand(sql, connection);
await using var reader = await command.ExecuteReaderAsync();
var rows = new List<string>();
while (await reader.ReadAsync())
{
var values = new string[reader.FieldCount];
for (int i = 0; i < reader.FieldCount; i++)
{
values[i] = Show(reader.GetValue(i));
}
rows.Add(string.Join(" | ", values));
}
return rows;
}
static string Show(object value) => value switch
{
DBNull => "NULL",
DateTimeOffset time => time.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture),
DateTime day when day.TimeOfDay == TimeSpan.Zero => day.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
DateTime time => time.ToString("yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture),
TimeSpan time => time.ToString(@"hh\:mm", CultureInfo.InvariantCulture),
_ => Convert.ToString(value, CultureInfo.InvariantCulture) ?? "",
};
You don’t have to understand this program; in the next lesson you write one yourself with the same library. Three things are still good to know:
It is a file-based app as in K1:
#:packagefetches the packages from nuget.org, and you don’t need a project. Microsoft.Data.SqlClient is the library that .NET programs use to talk to SQL Server. The line withSystem.Security.Cryptography.Pkcslifts a package that SqlClient brings along in an older version to a version without a known security vulnerability. Without it, NuGet reports a warning during the build; lesson 2.4.6 explains where it comes from.The connection string comes from the environment variable
ConnectionStrings__Kladde, whichcreate-sql-env.shfrom lesson 2.4.1 wrote into the.env. In the codespace it is set in every terminal. The program only swaps the name of the database inside it. On Way B you set the variable for the running session yourself, without the password appearing on the screen:$env:ConnectionStrings__Kladde = "Server=localhost,1433;Database=kladde;User Id=sa;Password=$(docker exec kladde-sql printenv MSSQL_SA_PASSWORD);TrustServerCertificate=True"GOis not SQL but a command to tools likesqlcmd(lesson 2.4.1). A program doesn’t knowGO. That is whycheck.cssplits the file itself at every line that contains onlyGOand sends the batches one by one.
In the folder sql-queries you create the database:
dotnet run check.cs -- create
The practice database is created.
The first run takes a little longer because dotnet downloads the packages and compiles the program.
Where you write the queries
For the queries in this lesson you use the editor of the SQL Server (mssql) extension from lesson 2.4.1. In the folder sql-queries,
create a file playground.sql, connect the editor to “Kladde local”, and choose the database practice at the top. Select a query and run
it with Ctrl+Shift+E. The result appears in the grid below, and that is exactly how the results appear in this lesson.
Always write the schema dbo. in front of the table in your queries. It is only four characters, and it protects you from the server
finding a table of the same name in another schema.