Using SQL to make a single table query

A single table query is one that is relative to a multiple table query and refers to querying data from a datasheet.
4.2.1 Query all records
In the command edit area, perform the input "select * from Scott.emp" and click the Execute button to appear with all the records in the EMP datasheet as shown in Figure 4.3.
"See CD-ROM File": \ 4th Chapter \4.2\421.sql.

The SELECT * from data table, where "*" represents all the fields in the datasheet.
4.2.2 query for some fields of all records
Enter the select Empno,ename,job from scott.emp in the command edit area, and then click the Execute button to display the Empno, ename, and job fields of the EMP datasheet, as shown in Figure 4.4.
"See CD-ROM File": \ 4th Chapter \4.2\422.sql.

Select Field Name 1, field name 2,...... from datasheet, some specific fields will be displayed, note that the comma between the field names here is a comma in English state.
4.2.3 query Some fields for different records
In the job field shown in Figure 4.4, you can find the same data, in order to query for how many different jobs, enter the select distinct job from scott.emp in the command edit area, and then click the Execute button to see the results shown in Figure 4.5.
"See CD-ROM File": \ 4th Chapter \4.2\423.sql.

Select distinct the field name from data table, where the "distinct" reserved word means that the same record is stripped when it is displayed, and that the corresponding "all" will keep the same record, and the default is "all".
4.2.4 a single condition query
(1) in the command edit area, enter the Select Empno,ename,job from Scott.emp where job= ' manager ', and then click the Execute button, The result of a character field condition query, as shown in Figure 4.6, is a query that is a record for the job manager.
"See CD-ROM File": \ 4th Chapter \4.2\424-1.sql.

(2) in the command edit area, enter select Empno,ename,sal from Scott.emp where sal<=2500, and then click the Execute button. The result of the numeric field condition query, as shown in Figure 4.7, is that the query satisfies a record that Sal is less than or equal to 2500.
"See CD-ROM File": \ 4th Chapter \4.2\424-2.sql.

Where you can specify a query condition, in the form of a field name operator ' String ' If you specify a character field query condition, or a field name operator ' String ' If you specify a numeric field query condition. The comparison operator used for a single condition query is shown in Table 4.1.
"See CD-ROM File": \ 4th Chapter \4.2\table41.sql.
Table 4.1 Comparison operators

Name	Instance
= (equal To)	SELECT * from scott.emp where job= ' MANAGER ';
SELECT * from Scott.emp where sal=1100;
!= (not equal to)	SELECT * from scott.emp where job!= ' MANAGER ';
SELECT * from Scott.emp where sal!=1100;
^= (not equal to)	SELECT * from scott.emp where job^= ' MANAGER ';
SELECT * from Scott.emp where sal^=1100;
<> (not equal to)	SELECT * from scott.emp where job<> ' MANAGER ';
SELECT * FROM Scott.emp where sal<>1100;
< (less than)	SELECT * FROM Scott.emp where sal<2000;
SELECT * from scott.emp where job< ' MANAGER ';
> (greater than)	SELECT * FROM Scott.emp where sal>2000;
SELECT * from scott.emp where job> ' MANAGER ';
<= (less than or equal)	SELECT * FROM Scott.emp where sal<=2000;
SELECT * from scott.emp where job<= ' MANAGER ';
>= (greater than or equal to)	SELECT * FROM Scott.emp where sal>=2000;
SELECT * from scott.emp where job>= ' MANAGER ';
In (list)	SELECT * from Scott.emp where Sal in (2000,1000,3000);
SELECT * from Scott.emp where job in (' MANAGER ', ' Clerk ');
Not in (no list)	SELECT * from Scott.emp where Sal isn't in (2000,1000,3000);
SELECT * from scott.emp where job isn't in (' MANAGER ', ' Clerk ');
Between (between)	SELECT * from Scott.emp where Sal between and 3000;
SELECT * from scott.emp where job between ' MANAGER ' and ' clerk ';
Not between (no between)	SELECT * from scott.emp where Sal not between and 3000;
SELECT * from scott.emp where job is not between ' MANAGER ' and ' clerk ';
Like (pattern matching)	SELECT * from scott.emp where job like ' m% ';
SELECT * from scott.emp where job like ' m__ ';
Not like (pattern mismatch)	SELECT * from scott.emp where job isn't like ' m% ';
SELECT * from scott.emp where job isn't like ' m__ ';
is null (empty)	SELECT * from scott.emp where Sal is null;
SELECT * from Scott.emp the where job is null;
is not null (empty)	SELECT * from scott.emp where sal isn't null;
select * from scott.emp where job was not null;

    like and not-like queries for character fields,% for strings of any length, _ underscores for an arbitrary character. Like ' m% ' represents an arbitrary length string at the beginning of M, like ' m__ ' represents a string with a length of 3 at the beginning of M.
4.2.5 query for combination conditions
     (1) in the command editing area, enter the Select Empno,ename,job from scott.emp where job>= ' Clerk ' and sal<=2000 ' and click the ' Execute ' button to see the results of the logical and combined queries shown in Figure 4.8.
     "See CD-ROM Files": \ 4th Chapter \4.2\425-1.sql.

     (2) in the command editing area, enter the Select Empno,ename,job from scott.emp where job>= ' clerk ' or sal<= 2000, and then click the Execute button to show the results of the logical or combined query as shown in Figure 4.9.
     "See CD-ROM Files": \ 4th Chapter \4.2\425-2.sql.

     (3) in the command editing area, enter select Empno,ename,job from scott.emp where not job= ' clerk ', and then click Execute button, the result of the logical, ungrouped query as shown in Figure 4.10 appears.
     "See CD-ROM Files": \ 4th Chapter \4.2\425-3.sql.
 
     not job= ' clerk ' is equivalent to ' job<> ' clerk '. The logical comparison characters used in the
     combination conditions are shown in Table 4.2.
     "See CD-ROM Files": \ 4th Chapter \4.2\table42.sql.
Table 4.2 Logical comparison characters

Name	Instance
and (with)	SELECT * from scott.emp where job= ' MANAGER ' and sal<>2000;
or (OR)	SELECT * from scott.emp where job!= ' MANAGER ' or sal<>2000;
Not (non)	SELECT * from scott.emp where not job>= ' MANAGER ';

4.2.6 Sort Query
In the command edit area, enter the Select Empno,ename,job from scott.emp where job<= ' clerk ' orders by Job Asc,sal desc, and then click the Execute button, The result of the sort query appears as shown in Figure 4.11.
"See CD-ROM File": \ 4th Chapter \4.2\426.sql.

Order by can specify how query results are sorted, in the form of sort keywords for field names, ASC for ascending order, desc for descending order, and multiple sort fields separated by commas. If there is a where query condition, the order by is placed behind the where statement.
4.2.7 Group Query
A grouped query is a grouping of query results by field.
(1) in the command edit area, enter select Empno,ename,job,sal from Scott.emp GROUP by Job,empno,ename,sal have sal<=2000, and then click the Execute button. The result of the grouped query appears as shown in Figure 4.12.
"See CD-ROM File": \ 4th Chapter \4.2\427-1.sql.

(2) in the command edit area, enter the Select Empno,ename,job,sal from Scott.emp where sal<=2000 group by Job,empno,ename,sal, and then click the Execute button. The result of the grouped query appears as shown in Figure 4.13.
"See CD-ROM File": \ 4th Chapter \4.2\427-2.sql.

Where to check whether each record meets the criteria, having is to check whether groups after grouping meet the criteria. The having statement can only be used with the group BY statement, and the having is not available without group by, but where is available.
4.2.8 Field Operation query
There are several basic arithmetic operators that can be used to query data.
Common + (plus),-(minus), * (multiply),/(except) 4 arithmetic operations can be used to query data.
Enter the select Empno,ename,sal,mgr,sal+mgr from scott.emp in the command edit area, and then click the Execute button to see the results shown in Figure 4.14.
"See CD-ROM File": \ 4th Chapter \4.2\428.sql.

The arithmetic operator is used only for the operation of multiple numeric fields or fields with numbers.
4.2.9 Transform query Display
In the command edit area, enter the select empno number, ename name, Job job, Sal pay from Scott.emp, and then click the Execute button, which appears as shown in Figure 4.15, to display the default field name in the name you set.
"See CD-ROM File": \ 4th Chapter \4.2\429.sql.

Above we learned the query statement for a single datasheet. By combining the above basic examples, you can complete the basic daily Data Query task, and then further learn more table query.