Select-sql JoJo talk-third week

Third week of August 8, 2001

a.1997 customers who have business dealings with the company in the year

The so-called business dealings is to have sales relationship with the enterprise, whether there is a sales relationship can be obtained from the Orders table, so it is easy to think of using Word query technology to solve the problem.

We can get a sales relationship customer number through the following query:

SELECT ORDERS. CUSTOMERID from Orders WHERE orders. ORDERDATE BETWEEN ' 19970101 ' and ' 19971231 '

Because of the need for more detailed customer information, you must use the Customers table. So in SQL Server, the final code code is as follows:

SELECT Customers.customerid,customers.companyname,customers.city,customers.country
From CUSTOMERS WHERE CUSTOMERS. CUSTOMERID in
(SELECT ORDERS.) CUSTOMERID from Orders WHERE orders. ORDERDATE BETWEEN ' 19970101 ' and ' 19971231 ')

The same code is implemented in Visual FoxPro:

SELECT Customers.customerid,customers.companyname,customers.city,customers.country;
From CUSTOMERS WHERE CUSTOMERS. CUSTOMERID in;
(SELECT ORDERS.) CUSTOMERID from Orders WHERE orders. ORDERDATE BETWEEN {^1997-01-01} and {^1997-12-31})

For this example there is a more efficient way to solve the problem, and there is a saying in SQL Server that a connection is more efficient than a subquery. So can we use tables and tables to solve the problem here? OK, in SQL Server we'll write this code first:

SELECT Customers.customerid,customers.companyname,customers.city,customers.country
From CUSTOMERS INNER JOIN ORDERS on CUSTOMERS. Customerid=orders. CUSTOMERID
WHERE ORDERS. ORDERDATE BETWEEN ' 19970101 ' and ' 19971231 '

Does this complete the need? No, run a look at the results you will know: The original customer in 97 may have several sales relationship with the company, so in the Orders table will produce a number of sales information, through the Oreders table and customers connection, a customer may correspond to several orders, Thus generated a duplication of customer information, which is clearly not what we want, then we must guarantee: the customer label the same data row only shows one of them, with the keyword: distinct can be achieved. Implemented in SQL Server as follows:

SELECT DISTINCT Customers.customerid,customers.companyname,customers.city,customers.country
From CUSTOMERS INNER JOIN ORDERS on CUSTOMERS. Customerid=orders. CUSTOMERID
WHERE ORDERS. ORDERDATE BETWEEN ' 19970101 ' and ' 19971231 '

The same can be achieved in Visual FoxPro:

SELECT DISTINCT Customers.customerid,customers.companyname,customers.city,customers.country;
From CUSTOMERS INNER JOIN ORDERS on CUSTOMERS. Customerid=orders. CUSTOMERID;
WHERE ORDERS. ORDERDATE BETWEEN {^1997-01-01} and {^1997-12-31}

B. List products with a higher unit price than average

This is an example of a subquery that must be used, why can't it be implemented with a connection? The original average price generation must be achieved through a "compression" Recordset, and now that the recordset is compressed, the other tables cannot be connected to the compressed recordset, as the average price is calculated in SQL Server:

SELECT AVG (UnitPrice) from

Looking at this recordset is only one field, one record, and the other table is not connected to it, in SQL Server, the final implementation of this example is as follows:

SELECT products. Productname,products. UnitPrice from the products WHERE products. Unitprice> (SELECT AVG (UnitPrice) from products)

The implementation in Visual FoxPro is as follows:

SELECT products. Productname,products. UnitPrice from the products WHERE products. unitprice>;
(SELECT AVG (UnitPrice) from products)

C. List products with the highest price of 10

This example does not have too much skill, this is a formula, must remember. This can be done in SQL Server:

SELECT top Productname,unitprice from the products order by UnitPrice DESC

The whole process is this: the DataSet is sorted in a certain order (in this case, descending by unit price), and SQL Server retrieves the first 10 records to the user. This is implemented in Visual FoxPro:

SELECT top Productname,unitprice from the products order by UnitPrice DESC

D. List the lowest-priced products (5% of the total number of products expected)

This is done in SQL Server:

SELECT Top 5 PERCENT Productname,unitprice from the order by UnitPrice

The same is true in Visual FoxPro:

SELECT Top 5 PERCENT Productname,unitprice from the order by UnitPrice

E. List the affiliation relationship between employees (one level relationship)

This is a typical connection, since the connection is relatively easy to confuse the head of things, in fact, we can think of it as a connection between the two tables, rather than the eyes of a table, the situation may be better. This can be done in SQL Server:

SELECT A.employeeid as leader, A.firstname+ ' +a.lastname as leader name, A.title as leadership title, B.employeeid as subordinate work number, b.firstname+ ' +b.las Tname as subordinate name, B.title as subordinate title from EMPLOYEES A INNER JOIN EMPLOYEES B in A.employeeid=b.reportsto order by A.employeeid

Implemented in Visual FoxPro with the same code:

SELECT A.employeeid as leader, A.firstname+ ' +a.lastname as leader name, A.title as leadership title,
B.employeeid as subordinate work number, b.firstname+ ' +b.lastname as subordinate name, B.title as subordinate title;
From EMPLOYEES A INNER JOIN EMPLOYEES B on A.employeeid=b.reportsto order by A.employeeid

This week's experiment is done, do not know you have what experience? My feeling is to learn sql-select must be more understanding, and sometimes die to do dead practice is not the effect, and a moment of cheerful will make you trample on the entrance! For Visual FoxPro programmers, Sql-select may be love and hate: We have great data manipulation languages, great cursors, so we don't have to write very long, annoying, hard to understand sql-select can solve problems, although visual FoxPro's support for Sql-select is great (so far as SQL Server can be implemented, Visual FoxPro basically doesn't have to change the code). Now all the development tools, database support Sql-select, especially the Visual FoxPro two, three-tier architecture after the popular, SQL will become more and more important, so do not learn! See you next week!