SQL Where: Applications and Examples. SQL Where Applications and Examples Sample Query to Select Numeric Values

Search for objects in all server databases

Have you ever needed to find a table to look up data or a structure, but you don't remember exactly which database it is in? This happens to me from time to time. An example is to find tables from a five-year-old project to see how the current problem was solved then.

Fortunately, there is a procedure sp_MSforeachdb, which allows you to write such a script quite compactly:

DECLARE @name AS SYSNAME,
@strSQL AS VARCHAR(MAX)
SET @name = "Hierarchy"

SET @strSQL = "
IF EXISTS(SELECT * FROM ?..sysobjects WHERE name LIKE ""%" +@name+"%" ")
BEGIN
SELECT " "?" " db, name FROM ?..sysobjects WHERE name LIKE ""%" +@name+"%" "
END
"
CREATE TABLE #result (
dbSYSNAME,
name SYSNAME,
PRIMARY KEY (db,name))

INSERT #result EXEC sp_MSforeachdb @strSQL
SELECT * FROM #result

DROP TABLE #result

Everything is quite simple here:

  1. We set the part of the name by which we will search (in this case - Hierarchy).
  2. We collect dynamic SQL that searches for sysobjects (for some older versions of MS SQL, you can use VARCHAR(2000) instead of VARCHAR(MAX), for example).
    In this case, the symbol "?" will be replaced by the name of the database.
  3. Using the INSERT ... EXEC construct, we store all the results in a temporary table in order to display, as a result, a single result.

Search for a substring in texts of stored procedures

As you know, the texts of stored procedures, triggers and views can be found in the table syscomments.

In order for our previous request to allow us to find a mention in syscomments of some substring, it can be rewritten as follows:

SET @strSQL="
SELECT DISTINCT " "?" "as db, o.name FROM [?].dbo.syscomments s
JOIN [?].dbo.sysobjects o ON o.id = s.id WHERE s. LIKE""%" +@name+"%" ""

Pay attention to DISTINCT - it can come in handy, because sometimes there are several entries in syscomments for one object.

Search for a substring in all string fields of all tables

Such a script helps to sleep peacefully after "cleaning" the database before sending it to the client. Naturally, its application is not limited to this area.

For this case, an assistant in the form of a procedure from MS was not found, so I had to use cursors:

declare @pattern as nvarchar(MAX )
set @pattern = N"%Test%"
set no count on
declare @sql as nvarchar(MAX )
declare @table as sysname
declare tables cursor local static read_only for select name from sys.tables t where t.is_ms_shipped = 0
open tables

create table #results(name sysname not null , value nvarchar(MAX ) not null )
while @@FETCH_STATUS = 0
begin
set @sql=""
select @sql = @sql + "
insert into #results select""" + @table + "." + name + "" " as name, [" + name + "] from [" + @table + "] where [" + name + "] like " "" + @pattern + "" ""
from sys.columns c where c.object_id = OBJECT_ID(@table ) and c.system_type_id in
(select system_type_id from sys.types where collation_name is not null )
exec (@sql)
fetch next from tables into @table
end
select * from #results
close tables
drop table #results

Other than cursors, the principles are the same. There are a couple of nuances:

  • We are looking for tables without the is_ms_shipped flag in order to view only tables created during development.
  • To select string columns, instead of a list of types (which is tedious to maintain), the filter "collation_name is not null" is used.

Summary

The attentive reader must have guessed from the design that the last example was not written at the same time as the first two. To be more precise, I wrote the last request a month ago. This I mean that based on this approach, you can write useful scripts yourself that solve your specific problems.

If readers like this format, I will sometimes post useful scripts from my personal collection.

If you have any comments, suggestions or new topics - write in the comments,

It is difficult to explain the syntax for the SQL Server WHERE clause, so let's look at some examples.

We"ll start by looking at how to use the WHERE clause with only a single condition.

SELECT * FROM employees WHERE first_name = "Jane";

In this SQL Server WHERE clause example, we"ve used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is "Jane". Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

Example - Using AND conditions

Let's look at how to use the WHERE clause with the AND condition.

SELECT * FROM employees WHERE last_name = "Anderson" AND employee_id >= 3000;

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND condition to return all employees that have a last_name of "Anderson" and the employee_id is greater than or equal to 3000.

Example - Using OR condition

Let's look at how to use the WHERE clause with the OR condition.

SELECT employee_id, last_name, first_name FROM employees WHERE last_name = "Johnson" OR first_name = "Danielle";

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND condition , it uses the OR condition . In this case, this SELECT statement would return all employee_id, last_name, and first_name values ​​from the employees table where the last_name is "Johnson" or the first_name is "Danielle".

Example - Combining AND & OR conditions

Let's look at how to use the WHERE clause when we combine the AND & OR conditions in a single SQL statement.

SELECT * FROM employees WHERE (state = "California" AND last_name = "Smith") OR (employee_id = 82);

This SQL Server WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND condition and the OR condition . This example would return all employees that reside in the state of "California" and whose last_name is "Smith" as well as all employees whose employee_id is equal to 82.

The parentheses determine the order that the and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Join Tables

Let's look at how to use the WHERE clause when we join multiple tables together.

SELECT employees.employee_id, contacts.last_name FROM employees INNER JOIN contacts ON employees.employee_id = contacts.contact_id WHERE employees.first_name = "Sarah";

This SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name in the employees table is "Sarah". And the employee s and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table.

In most cases, you do not need to retrieve all records, but only those that meet certain criteria. Therefore, in order to filter the selection in SQL there is a special operator WHERE.

1. Simple filtering with the WHERE operator.

Let's, for example, select records from our table that relate only to a particular product. To do this, we will specify an additional selection parameter that will filter the value by the column product.

Sample query for selecting text values:

SELECT * FROM Sumproduct WHERE Product = "Bikes"

As you can see, the selection condition is enclosed in single quotes, which is mandatory when filtering text values. Quotes are not needed when filtering numeric values.

Sample query for selecting numeric values:

SELECT > 40000 ORDER BY Amount

In this example, we have selected records in which the sales revenue was more than 40 thousand $ and, additionally, all records are sorted in ascending order by field Amount.

The table below lists the conditional statements supported SQL:

2. Filtering by range of values ​​( BETWEEN).

To select data that lies in a certain range, use the operator BETWEEN. The next query will select all values ​​between 1000 $ V 2000 $ inclusive, in the field Amount.

SELECT * FROM Sumproduct WHERE Amount BETWEEN 1000 AND 2000

The sort order will depend on the order of the fields in the query. That is, in our case, the data will first be sorted by column Amount, and then by city.

3. Selection of empty records ( IS NULL).

IN SQL there is a special operator for selecting empty records (called NULL). A blank entry is any cell in a table that does not contain any characters. If the cell is entered 0 or space, then the field is considered to be filled.

SELECT * FROM Sumproduct WHERE Amount IS NULL

In the example above, we deliberately removed two values ​​in the field Amount to demonstrate the operation of the operator NULL.

4. Advanced filtering ( AND, OR).

Language SQL is not limited to filtering by one condition, for your own purposes you can use quite complex structures to select data simultaneously by many criteria. For this in SQL there are additional operators that expand the capabilities of the operator WHERE. These operators are: AND, OR, IN, NOT. Here are some examples of how these operators work.

SELECT * FROM Sumproduct WHERE Amount > 40000 AND city = "Toronto"

SELECT * FROM Sumproduct WHERE Month= "April"OR month= "March"

Let's combine operators AND And OR. To do this, let's make a selection of bicycles ( Bikes ) and skates ( skates ), which were sold in March (March ).

SELECT * FROM Sumproduct WHERE Product= "Bikes"OR product= "skates" AND month= "March"

We see that our sample included many values ​​(except for March ( March), also January ( January), February ( February) and April ( April)). What is the reason? And in that SQL has command execution priorities. That is, the operator AND has higher precedence than operator OR, so the records with skates that were sold in March were selected first, and then all records related to bicycles.

So, in order to get the correct selection, we need to change the commands execution priorities. For this we use brackets like in mathematics. Then, the operators in brackets will be processed first, and then all the rest.

SELECT * FROM Sumproduct WHERE (Product= "Bikes"OR product= "skates") AND month= "March"

5. Advanced filtering ( IN operator).

SELECT * FROM Sumproduct WHERE IDIN (4, 12, 58, 67)

Operator IN performs the same function as OR, however, has a number of advantages:

  • When working with long lists, the sentence with IN easier to read;
  • Fewer operators are used, which speeds up request processing;
  • The most important advantage IN in that in its construction it is possible to use an additional construction SELECT, What
    • opens up great opportunities for creating complex subqueries.

6. Advanced filtering ( NOT operator).

SELECT * FROM Sumproduct WHERE NOT CityIN ("Toronto", "Montreal")

Keyword NOT allows you to remove unnecessary values ​​from the selection. Also, its feature is that it is placed before the name of the column involved in filtering, and not after.

In this article, I will tell you about the conditional CASE statement, without which data accounting in a number of tasks would turn into heaps of pieces of code, as well as how to use it in SQL queries.

No matter how well you design your database, there will always be problems where conditional statements are indispensable. For example, instead of huge numbers, get a verbal description, or, depending on the presence (absence) of data, add additional characters to the string, or, as a more complex example, depending on various nuances, attribute the record to one or another group. Lots of situations.

In principle, this operator has at least two forms in different databases. The first option resembles the usual switch from any programming language. The second one depends on boolean expressions.

However, in this article, I will consider the second option, since it does not have problems with situations like CASE WHEN NULL (null is not a specific value in the database, so it cannot be used in a switch like statement). In addition, in everyday life, tasks are most often encountered specifically for the second option - calculation through logical expressions. Therefore, it is better to immediately learn and continue to use it.

Usually, it is described like this (syntax may vary depending on the database):

CASE WHEN bool_expression1 THEN value1 ..... WHEN bool_expressionN THEN valueN ELSE valueElse END

bool_expressionX is a boolean condition

valueX is the value that will be substituted if the corresponding boolean condition is met

valueElse is the value that will be substituted if no condition has previously been met.

After such a small reference, let's move on to practice.

Note: By the way, it's worth knowing that usually this statement can be used not only in select, but also in any place where fields can be used. For example, when join tables or even filtering (having) when grouping (group by).

Conditional statement CASE...WHEN...THEN

To better understand the CASE...WHEN...THEN conditional statement, imagine a small problem. Let's say you have a table with data about customers and their total number of purchases. And the task is to dynamically form a discount. It would be possible, of course, to set the discount manually. But, you have a threshold, and the thresholds are hardwired (something like - the amount is more than 1000 get a 2% discount, and more than 5000 - get 5%) and you would like to automate this process so that you do not have to look for errors every time delve into the database (the client has accumulated the required amount - the discount automatically appeared).

Let's take a conditional table client with three clients. For example, they will be enough.

Now, let's set several conditions for automatically granting discounts based on the task. At the same time, we believe that the maximum discount is provided to the client in any case.

1. Amount from 1000 - 2% discount

2. Amount from 5000 - 5% discount

3. Amount from 10000 - 8% discount

4. Quantity of orders from 10 - 7% discount

5. Quantity of orders from 20 - 8% discount

As you can see, the discount depends on two factors: the amount and the quantity. Now, let's try to create conditions from them based on the discount, that is, the rules are vice versa so that they can be used in an sql query. We get the following:

1. 2% - Amount from 1000 to 4999 and the number of orders is less than 10.

2. 5% - Amount from 5000 to 9999 and the number of orders is less than 10.

3. 7% - Number of orders from 10 to 19 and the amount is less than 10000

4. 8% - Quantity from 20 or amount from 10000

Now, all that's left is to write it down. Get the following sql query

Displaying the name and other data select name, order_count, total_sum, -- And now displaying the discount CASE -- First rule 2% WHEN c.total_sum >= 1000 and c.total_sum<= 4999 and c.order_count < 10 THEN 2 -- Второе правило 5% WHEN c.total_sum >= 5000 and c.total_sum<= 9999 and c.order_count < 10 THEN 5 -- Третье правило 7% WHEN c.total_sum < 10000 and c.order_count >= 10 and c.order_count<= 19 THEN 5 -- Четвертое правило 8% WHEN c.total_sum >= 10000 or c.order_count >= 20 THEN 5 -- No rules matched, so discount is 0. ELSE 0 END as discount from client c

As a result of execution, we get the following table:

As you can see, two customers received an 8 percent discount, and one customer received a 2 percent discount. At the same time, with each order, the percentage will be automatically calculated and you will not need to adjust anything. For example, if Petya's amount increases to 5000, then his discount will automatically rise to 5% (at least, since there are still a number of orders).

mob_info