The tables are joined exactly the same way as in the previous example. The output shows us the highest salaries in the Corporate and Private Individuals department. Finally, the output is grouped by the column department. The query is almost exactly the same as in the previous example. The only difference is we’re ordering the output by the employee’s name descendingly.
Only a few examples will be based on other tables; in these cases, the tables will be explained along with the example. One of the best ways to learn advanced SQL is by studying example queries. In this article, we’ll show 25 examples of advanced SQL queries from medium to high complexity. You can use them to refresh your knowledge of advanced SQL or to review before a SQL interview. In this article, we covered the basic SQL queries that can be used to perform simple data tasks. However, SQL offers many more clauses and techniques that allow you to do more operations on data.
My Learning
In other words, you simply need to write the queries on your own. That way, you’ll consolidate all the concepts you learned here. This is desirable, as there’s no better way of learning than trying to correct your own mistakes.
You probably noticed that outputs in our two latest examples are sorted a bit randomly. This is not something you have to put up with – you can order data with ORDER BY even when using two tables. We subtracted one quarter from another to calculate the change between the quarters. In this case, it’s the column with the fourth quarter sales minus the third quarter sales. Some employees are obviously missing, as they should be.
Example #1 – Ranking Rows Based on a Specific Ordering Criteria
Understanding and using these joins effectively enables complex data retrieval and is essential for robust database management. LEFT JOIN returns all records from the left table (Customers), and the matched records from the right table (Orders). If there is no match, NULL values are returned for the right table. In this example, we want to delete all data from the table product.
- This time, we sort by salary descending and then by last name ascendingly.
- Only a few examples will be based on other tables; in these cases, the tables will be explained along with the example.
- This clause groups rows with the same dept_id, ordering the rows in each group by salary DESC.
- A similar operator to IS NOT NULL is IS NULL, which checks if a value is equal to NULL.
- If you didn’t install SQL Server yourself, check with your administration
which connection information you need to supply.
This query shows all records from both Customers and Orders. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). Simply put the name of the table you want to delete after DROP TABLE.
Example #2 – List The First 5 Rows of a Result Set
Of course, you can filter data in joined tables the same way as you can with only one table. This type of query is used whenever you want to access data from two or more tables. We’ll show you INNER JOIN, but it’s not the only join type you can use.
In it, we again subtract the third quarter from the fourth and set the condition that the result has to be below zero, i.e. a decrease. As you noticed, WHERE comes after the tables are joined. Here’s another basic SQL query that uses an aggregate function.
Using SUM(), WHERE, and GROUP BY
More info about DELETE can be found in the tip
The T-SQL DELETE statement. One special statement is
the MERGE statement, which can be used to insert, delete and update at the same
time. The MERGE statement takes source data (this can be a table or the result of
a SELECT statement) and matches it against a destination table. New rows are inserted,
matched rows are updated and rows present in the destination table but not in the
source are optionally deleted.
We’ll soon show you 20 basic SQL query examples to start talking with the database. All these queries are taught in our SQL Basics course; this course will give you even more structure, examples, and challenges to solve. It has 129 interactive exercises on querying one or more tables, aggregating and grouping data, JOINs, subqueries, and set operations. Even with the 20 upcoming examples, we won’t show all the details or even all the basic-level queries. That’s why we recommend using the course as a platform for practicing the fundamentals we’ll discuss here. A SQL query is a structured command written in SQL syntax.
SQL Tutorial
In this example, we want to join data from the tables customer and city. INNER JOIN needs to come after FROM and the name of the first table, customer. After INNER JOIN, place the name of the second table, city. The records with data from both tables are matched by ON with the condition to join.
When the salary is the same (green rows), the data is ordered alphabetically by last name. Ordering or sorting the output is done using the ORDER BY clause. In other words, the values in the column first_name have to be equal to Luca. Also, when the condition is not a number but a text or a date/time, it has to be written in single quotes (”). That’s why our condition is written as ‘Luca’, not simply Luca. In general, this table is a list of each quarter’s sales made by every employee shown in the first table.
In this example, we only want to retrieve the last names listed in both tables. To see what is different between the sets, use the basic sql queries operators MINUS or EXCEPT. DDL is a set of SQL commands used to create, modify, and delete database structures but not data.