Some time ago, my friend Kris asked me for help extracting data from databases in his company. They operate on complex software that uses multiple MS SQL databases.
One day, there was an urgent need to pull out a simple list, and you know what? It turned out that this very expensive software doesn’t allow for such an operation.
At this point, the knowledge of several SQL commands that export data directly from databases came in handy. In this post, I would like to share some basic information about SQL along with examples that can help teams solve similar problems in the future, alongside database development.
SQL stands for Structured Query Language, and it is a script language designed for managing and manipulating relational databases. It’s used to create, modify, and manage databases, as well as query, retrieve, and manipulate the data stored in them.
The majority of relational database management systems (RDBMS) support SQL, which is a widely used industry standard language. Teams use it to perform a wide range of tasks such as creating tables and views, inserting, updating and deleting data, and retrieving data using queries.
SQL is a powerful tool for data analysis and management, making it a critical skill for anyone working with data.
SELECT [DISTINCT] column1, column2, column3...
FROM table_name
[WHERE condition [AND condition OR condition]]
[ORDER BY column1 ASC/DESC]
[LIMIT 10];
DISTINCT - if you want only unique values as a query result,
LIMIT - take only x records from the query.
SELECT [DISTINCT] table_x.column1, table_x.column2, table_y.column1 AS `Name` ...
FROM table_x
JOIN table_y ON table_x.id=table_y.user_id
[WHERE condition [AND condition / OR condition ...]]
[ORDER BY table_x.column1 ASC/DESC]
[LIMIT 10];
INSERT INTO table_name (column1, column2, column3...) VALUES (value1, value2, value3...);
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
DELETE FROM table_name WHERE condition;
You can use this website to practice those basic queries. Learn about db schema you’ll be playing with by using the View Schema button on the left.
Example queries that should work with the schema from March 25, 2023:
SELECT DISTINCT patients.patient_id, patients.first_name, patients.last_name,patients.gender, patients.birth_date, province_names.province_name as province
FROM patients
INNER JOIN province_names ON patients.province_id=province_names.province_id
WHERE patients.birth_date between '1988-01-01' AND '2000-12-31'
ORDER BY patients.birth_date DESC
LIMIT 100;
T-SQL stands for Transact-SQL, which is an extension of SQL used by Microsoft SQL Server and Sybase ASE (Adaptive Server Enterprise) databases. T-SQL adds additional features to SQL, including programming constructs like variables, control flow statements, and error handling.
T-SQL includes all the basic SQL statements, such as SELECT, INSERT, UPDATE, and DELETE, as well as additional statements for creating and modifying database objects, like stored procedures, triggers, and views.
In addition, T-SQL includes support for transactions, which allows you to group a set of SQL statements into a single transaction that is either committed or rolled back as a single unit, ensuring data integrity.
T-SQL also provides built-in functions that can be used to perform complex calculations, manipulate strings, and work with date and time values.
Overall, T-SQL offers a robust set of features that can be used to create powerful, scalable database applications on the Microsoft SQL Server and Sybase ASE platforms.
Suppose we have two tables, orders and order_details, where orders contain order information and order_details contains details of the products ordered in each order.
We want to update the order_date field in the orders table to the most recent order date for each customer, which we can find in the order_details table.
UPDATE orders
SET order_date = (
SELECT MAX(order_date)
FROM order_details
WHERE orders.customer_id = order_details.customer_id
)
In this example, the subquery (SELECT MAX(order_date) FROM order_details WHERE orders.customer_id = order_details.customer_id) is used to retrieve the maximum order date for each customer in the order_details table. The WHERE clause ensures that only the order dates for the corresponding customer are selected.
The SET clause sets the order_date field in the orders table to the value returned by the subquery, updating the order date to the most recent order date for each customer.
To select data from two different databases in T-SQL, you can fully qualify the table names with their database names using the following syntax:
SELECT database1.dbo.table1.column1, database2.dbo.table2.column2
FROM database1.dbo.table1
JOIN database2.dbo.table2
ON database1.dbo.table1.id = database2.dbo.table2.id;
In this example, we are selecting data from two different databases, database1 and database2. The dbo refers to the database owner and is typically set to dbo for most user-defined objects.
The JOIN keyword is used to join the two tables, table1 from database1 and table2 from database2, based on a common column id.
Note that you need to have appropriate permissions to access both databases and the tables within them. If the databases are on different servers, you may need to use a linked server to access the data from the other server.
In T-SQL, you cannot use the ORDER BY clause directly in an UPDATE statement. However, you can use a subquery with the TOP and ORDER BY clauses to update the top N rows based on a specific order.
Here is an example of an UPDATE statement that uses a subquery with TOP and ORDER BY to update the top 10 employees with the highest salaries:
UPDATE employees
SET salary = salary * 1.1 -- increase salary by 10%
WHERE employee_id IN (
SELECT TOP 10 employee_id
FROM employees
ORDER BY salary DESC
)
In this example, we are updating the salary column of the employees table for the top 10 employees with the highest salaries. The subquery selects the top 10 employee_id values based on the descending order of the salary column. The IN keyword is used to specify the list of employee IDs to be updated.
Note that the subquery must return a unique list of values; otherwise, the UPDATE statement will fail with a "Subquery returned more than 1 value" error.
DECLARE @counter INT = 1;
DECLARE @max_count INT = 10;
DECLARE @result INT = 0;
WHILE (@counter <= @max_count)
BEGIN
SET @result = @result + @counter;
SET @counter = @counter + 1;
END
SELECT @result AS 'Sum of numbers 1 to 10';
In this example, we declare three variables @counter, @max_count, and @result using the DECLARE statement.
We then set the initial values for @counter and @max_count to 1 and 10, respectively.
The loop is defined using the WHILE statement, which continues to execute the block of code between the BEGIN and END keywords as long as the condition in the parentheses is true.
Inside the loop, we add the value of @counter to @result using the SET statement. We also increment @counter by 1 in each iteration of the loop. Once the loop completes, we use a SELECT statement to display the value of @result, which should be the sum of the numbers 1 to 10.
This is just a simple example, but variables and loops can be used to perform complex calculations and data manipulations in T-SQL.
DECLARE @customer_count INT;
SELECT @customer_count = COUNT(*) FROM customers;
PRINT 'Total number of customers: ' + CAST(@customer_count AS VARCHAR);
In this example, we declare a variable @customer_count using the DECLARE statement.
We then use a SELECT statement to query the customers table and count the total number of rows using the COUNT function. The result of the COUNT function is assigned to the @customer_count variable using the equals sign.
Finally, we use the PRINT statement to display a message along with the value of the @customer_count variable. We cast the @customer_count variable to VARCHAR to concatenate it with the message string.
To export the result of a T-SQL query to a CSV file, you can use the bcp utility, which is a command-line tool for copying data between an instance of SQL Server and a data file. Here's an example of how you can use bcp to export a query result to a CSV file:
bcp "SELECT column1, column2, column3 FROM table_name" queryout "C:\path\to\output\file.csv" -c -t , -S server_name -U username -P password
In this example:
After running the command, the result of the query will be exported to the specified CSV file. Note that you may need to adjust the permissions on the output folder to allow the SQL Server service account to write to it.
SQL is almost everywhere. Businesses of all sizes use SQL to "talk" to their databases and retrieve data because they can't make effective decisions without it. And poor business decisions can have dire consequences.
I hope this guide to SQl and T-SQL helps you leverage this language in your work with databases, setting your team for success.
