Structured Query Language (SQL) is mainly used to store, manipulate, and query data in databases. It also acts as an interface for managing databases and it has all the functionality to fully administer the database server, users, tables, etc. if the user running the query has the appropriate privileges. There are several different types of databases and there are also several “dialects” of SQL, but in this course, we will be concentrating strictly on Microsoft Transact-SQL (T-SQL). This is the version of SQL you will be using when dealing with Microsoft’s SQL products and services, such as SQL Server and Azure SQL Database. Since we will not cover advanced functionality or concepts, it is likely that much of what you will learn is transferable to other SQL dialects and databases but be aware that there still may be some details in the syntax that are specific to T-SQL.
The data in databases are (at least for the most part) stored in tables, which consist of rows and columns. Columns have a name and a data type, and they can either be nullable or they must contain a value that matches the data type. The column definitions define the structure of the table, whereas rows only represent the amount of data stored in the table. Think of a table “dbo.Orders”, for example, the columns define what information is needed for an order (order number, amount, client name, etc.) and each row in the table represents an order. Note the “dbo” in front of the table name “Orders”. This is called a “schema” and it is a sort of container for database objects. Schemas help create structure in the database.
When dealing with tables, you will most likely be either retrieving data from the table, removing data from the table, updating data already in the table or adding data to the table. This means the queries that you’ll need the most are SELECT, DELETE, UPDATE and INSERT. Here’s a simple example of each one:
- Selecting order number and amount from each row of the table “dbo.Orders”:
SELECT [order_number], [amount] FROM dbo.Orders;
- Deleting the row with order number 100 from table “dbo.Orders”:
DELETE FROM dbo.Orders WHERE [order_number] = 100;
- Updating the amount on the row with order number 100 in table “dbo.Orders”:
UPDATE dbo.Orders SET [amount] = 200 WHERE [order_number] = 100;
- Insert a new row into table “dbo.Orders”:
INSERT INTO dbo.Orders ( [order_number], [amount], [client_name]) VALUES (101, 500, ‘Do Good Ltd.’);
These are not the only keywords you will come across. If you need to retrieve data using information from several tables, you may need to JOIN tables using one or more columns; if you want to UPDATE, INSERT and DELETE rows in a single query, you may need to use MERGE and so on, most of the time you will be performing variations of these four basic operations.
To connect an SQL server you need to provide connection info in form of the connection string. A typical SQL connection string looks like
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
Where myServerAddress is IP, or the domain name of the SQL server, myDataBase is the database within the server Trusted_Connection=True means frends Agent's credentials are used to log in to the server.
You can see all available keywords here https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring but online resources, such as https://www.connectionstrings.com/sql-server/, dedicated to explain all possibilities might also be helpful.
The next article is Example of an SQL