free barcode generator using vb.net 2: A Quick Tour of SQL in Software

Making QR Code JIS X 0510 in Software 2: A Quick Tour of SQL

2: A Quick Tour of SQL
QR Code JIS X 0510 Scanner In None
Using Barcode Control SDK for Software Control to generate, create, read, scan barcode image in Software applications.
Making Denso QR Bar Code In None
Using Barcode drawer for Software Control to generate, create QR Code ISO/IEC18004 image in Software applications.
Overview
Recognizing QR Code In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
Encoding QR Code In C#.NET
Using Barcode drawer for .NET framework Control to generate, create QR Code 2d barcode image in VS .NET applications.
Before diving into the details of SQL, it's a good idea to develop an overall perspective on the language and how it works. This chapter contains a quick tour of SQL that illustrates its major features and functions. The goal of the quick tour is not to make you proficient in writing SQL statements; that is the goal of Part II of this book. Rather, by the time you've finished this chapter, you will have a basic familiarity with the SQL language and an overview of its capabilities.
Encode QR Code 2d Barcode In Visual Studio .NET
Using Barcode printer for ASP.NET Control to generate, create QR image in ASP.NET applications.
QR Code Generator In VS .NET
Using Barcode generator for .NET framework Control to generate, create QR image in .NET framework applications.
A Simple Database
QR Code Generator In VB.NET
Using Barcode creator for Visual Studio .NET Control to generate, create QR Code image in Visual Studio .NET applications.
Code 128B Drawer In None
Using Barcode maker for Software Control to generate, create Code 128A image in Software applications.
The examples in the quick tour are based on a simple relational database for a small distribution company. The database, shown in Figure 2-1, stores the information needed to implement a small order processing application. Specifically, it stores the following information:
DataMatrix Generation In None
Using Barcode maker for Software Control to generate, create Data Matrix ECC200 image in Software applications.
Drawing Barcode In None
Using Barcode creator for Software Control to generate, create barcode image in Software applications.
Figure 2-1: A simple relational database
UPCA Creator In None
Using Barcode maker for Software Control to generate, create UPC-A Supplement 2 image in Software applications.
Generating GS1-128 In None
Using Barcode maker for Software Control to generate, create GS1 128 image in Software applications.
the customers who buy the company's products,
EAN / UCC - 8 Creation In None
Using Barcode creation for Software Control to generate, create EAN-8 Supplement 2 Add-On image in Software applications.
Creating UPC-A In Visual C#
Using Barcode printer for VS .NET Control to generate, create UPC-A Supplement 5 image in .NET framework applications.
- 15 -
Barcode Generation In None
Using Barcode generator for Office Excel Control to generate, create bar code image in Microsoft Excel applications.
UPC Symbol Generator In VS .NET
Using Barcode encoder for ASP.NET Control to generate, create UPC Code image in ASP.NET applications.
the orders placed by those customers, the salespeople who sell the products to customers, and the sales offices where those salespeople work. This database, like most others, is a model of the "real world." The data stored in the database represents real entities customers, orders, salespeople, and offices. There is a separate table of data for each different kind of entity. Database requests that you make using the SQL language parallel real-world activities, as customers place, cancel, and change orders, as you hire and fire salespeople, and so on. Let's see how you can use SQL to manipulate data.
EAN 128 Generation In Java
Using Barcode printer for Eclipse BIRT Control to generate, create USS-128 image in BIRT reports applications.
Make GS1 - 12 In Objective-C
Using Barcode encoder for iPhone Control to generate, create UPC-A Supplement 5 image in iPhone applications.
Retrieving Data
Create Code128 In Java
Using Barcode maker for Java Control to generate, create USS Code 128 image in Java applications.
Drawing UPC-A Supplement 2 In Visual Basic .NET
Using Barcode generation for .NET Control to generate, create UPC Code image in .NET applications.
First, let's list the sales offices, showing the city where each one is located and its yearto-date sales. The SQL statement that retrieves data from the database is called SELECT. This SQL statement retrieves the data you want: SELECT CITY, OFFICE, SALES FROM OFFICES CITY -----------Denver New York Chicago Atlanta Los Angeles OFFICE -----22 11 12 13 21 SALES ----------$186,042.00 $692,637.00 $735,042.00 $367,911.00 $835,915.00
The SELECT statement asks for three pieces of data the city, the office number, and the sales for each office. It also specifies that the data comes from the OFFICES table, which stores data about sales offices. The results of the query appear, in tabular form, immediately after the request. The SELECT statement is used for all SQL queries. For example, here is a query that lists the names and year-to-date sales for each salesperson in the database. It also shows the quota (sales target) and the office number where each person works. In this case, the data comes from SALESREPS table: SELECT NAME, REP_OFFICE, SALES, QUOTA FROM SALESREPS NAME -------------Bill Adams Mary Jones Sue Smith Sam Clark Bob Smith Dan Roberts Tom Snyder Larry Fitch Paul Cruz REP_OFFICE ---------13 11 21 11 12 12 NULL 21 12 SALES ----------$367,911.00 $392,725.00 $474,050.00 $299,912.00 $142,594.00 $305,673.00 $75,985.00 $361,865.00 $286,775.00 QUOTA ----------$350,000.00 $300,000.00 $350,000.00 $275,000.00 $200,000.00 $300,000.00 NULL $350,000.00 $275,000.00
- 16 -
Nancy Angelli
$186,042.00
$300,000.00
SQL also lets you ask for calculated results. For example, you can ask SQL to calculate the amount by which each salesperson is over or under quota: SELECT NAME, SALES, QUOTA, (SALES - QUOTA) FROM SALESREPS NAME -------------Bill Adams Mary Jones Sue Smith Sam Clark Bob Smith Dan Roberts Tom Snyder Larry Fitch Paul Cruz Nancy Angelli SALES ----------$367,911.00 $392,725.00 $474,050.00 $299,912.00 $142,594.00 $305,673.00 $75,985.00 $361,865.00 $286,775.00 $186,042.00 QUOTA ----------$350,000.00 $300,000.00 $350,000.00 $275,000.00 $200,000.00 $300,000.00 NULL $350,000.00 $275,000.00 $300,000.00 (SALES-QUOTA) -------------$17,911.00 $92,725.00 $124,050.00 $24,912.00 -$57,406.00 $5,673.00 NULL $11,865.00 $11,775.00 -$113,958.00
The requested data (including the calculated difference between sales and quota for each salesperson) once again appears in a row/column table. Perhaps you would like to focus on the salespeople whose sales are less than their quotas. SQL lets you retrieve that kind of selective information very easily, by adding a mathematical comparison to the previous request: SELECT NAME, SALES, QUOTA, (SALES - QUOTA) FROM SALESREPS WHERE SALES < QUOTA NAME -------------Bob Smith Nancy Angelli SALES ----------$142,594.00 $186,042.00 QUOTA ----------$200,000.00 $300,000.00 (SALES-QUOTA) --------------$57,406.00 -$113,958.00
The same technique can be used to list large orders in the database and find out which customer placed the order, what product was ordered, and in what quantity. You can also ask SQL to sort the orders based on the order amount: SELECT ORDER_NUM, CUST, PRODUCT, QTY, AMOUNT FROM ORDERS WHERE AMOUNT > 25000.00 ORDER BY AMOUNT ORDER_NUM CUST PRODUCT ---------- -----------112987 2103 4100Y 113069 2109 775C 112961 2117 2A44L 113045 2112 2A44R QTY ---11 22 7 10 AMOUNT ---------$27,500.00 $31,350.00 $31,500.00 $45,000.00
Copyright © OnBarcode.com . All rights reserved.