SQL
Defining Data Tables
CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]
(FieldName1 FieldType [(nFieldWidth [, nPrecision])]
[NULL | NOT NULL]
[CHECK lExpression1 [ERROR cMessageText1]]
[DEFAULT eExpression1]
[PRIMARY KEY | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]]
[NOCPTRANS]
[, FieldName2 ...]
[, PRIMARY KEY eExpression2 TAG TagName2
|, UNIQUE eExpression3 TAG TagName3]
[, FOREIGN KEY eExpression4 TAG TagName4 [NODUP]
REFERENCES TableName3 [TAG TagName5]]
[, CHECK lExpression2 [ERROR cMessageText2]])
| FROM ARRAY ArrayName
Arguments
TableName1
Specifies the name of the table to create. The TABLE and DBF options are identical.
NAME LongTableName
Specifies a long name for the table. A long table name can be specified only when a database is open
because long table names are stored in databases.
Long names can contain up to 128 characters and can be used in place of short file names in the
database.
FREE
Specifies that the table will not be added to an open database. FREE isn't required if a database isn't
open.
(FieldName1 FieldType [(nFieldWidth [, nPrecision])]
Specifies the field name, field type, field width, and field precision (number of decimal places),
respectively.
A single table can contain up to 255 fields. If one or more fields allow null values, the limit is reduced
by one to 254 fields.
FieldType is a single letter indicating the field's data type. Some field data types require that you
specify nFieldWidth or nPrecision, or both.
The following table lists the values for FieldType and whether nFieldWidth and nPrecision are
required.
FieldType nFieldWidth nPrecision Description
C n – Character field of width n
D – – Date
T – – DateTime
N n d Numeric field of width n with d decimal places
F n d Floating numeric field of width n with d decimal places
I – – Integer
B – d Double
Y – – Currency
L – – Logical
M – – Memo
G – – General
NULL
Allows null values in the field. If one or more fields can contain null values, the maximum number of
fields the table can contain is reduced by one, from 255 to 254.
NOT NULL
Prevents null values in the field.
If you omit NULL and NOT NULL, the current setting of SET NULL determines if null values are
allowed in the field. However, if you omit NULL and NOT NULL and include the PRIMARY KEY
or UNIQUE clause, the current setting of SET NULL is ignored and the field defaults to NOT NULL.
CHECK lExpression1
Specifies a validation rule for the field. lExpression1 can be a user-defined function. Note that when a
blank record is appended, the validation rule is checked. An error is generated if the validation rule
doesn't allow for a blank field value in an appended record.
ERROR cMessageText1
Specifies the error message Visual FoxPro displays when the validation rule specified with CHECK
generates an error. The message is displayed only when data is changed within a Browse window or
Edit window.
DEFAULT eExpression1
Specifies a default value for the field. The data type of eExpression1must be the same as the field's
data type.
PRIMARY KEY
Creates a primary index for the field. The primary index tag has the same name as the field.
UNIQUE
Creates a candidate index for the field. The candidate index tag has the same name as the field. For
more information about candidate indexes, see "Using Indexes" in Chapter 7, Working with Tables, in
the Programmer's Guide.
Note Candidate indexes (created by including the UNIQUE option in CREATE TABLE or ALTER
TABLE – SQL) are not the same as indexes created with the UNIQUE option in the INDEX command.
An index created with the UNIQUE option in the INDEX command allows duplicate index keys;
candidate indexes do not allow duplicate index keys.
Null values and duplicate records are not permitted in a field used for a primary or candidate index.
However, Visual FoxPro will not generate an error if you create a primary or candidate index for a
field that supports null values. Visual FoxPro will generate an error if you attempt to enter a null or
duplicate value into a field used for a primary or candidate index.
REFERENCES TableName2 [TAG TagName1]
Specifies the parent table to which a persistent relationship is established. If you omit TAG TagName1,
the relationship is established using the primary index key of the parent table. If the parent table does
not have a primary index, Visual FoxPro generates an error.
Include TAG TagName1 to establish a relation based on an existing index tag for the parent table.
Index tag names can contain up to 10 characters.
The parent table cannot be a free table.
NOCPTRANS
Prevents translation to a different code page for character and memo fields. If the table is converted to
another code page, the fields for which NOCPTRANS has been specified are not translated.
NOCPTRANS can only be specified for character and memo fields.
PRIMARY KEY eExpression2 TAG TagName2
Specifies a primary index to create. eExpression2 specifies any field or combination of fields in the
table. TAG TagName2 specifies the name for the primary index tag that is created. Index tag names
can contain up to 10 characters.
Because a table can have only one primary index, you cannot include this clause if you have already
created a primary index for a field. Visual FoxPro generates an error if you include more than one
PRIMARY KEY clause in CREATE TABLE.
UNIQUE eExpression3 TAG TagName3
Creates a candidate index. eExpression3 specifies any field or combination of fields in the table.
However, if you have created a primary index with one of the PRIMARY KEY options, you cannot
include the field that was specified for the primary index. TAG TagName3 specifies a tag name for the
candidate index tag that is created. Index tag names can contain up to 10 characters.
A table can have multiple candidate indexes.
FOREIGN KEY eExpression4 TAG TagName4 [NODUP]
Creates a foreign (non-primary) index, and establishes a relationship to a parent table. eExpression4
specifies the foreign index key expression and TagName4 specifies the name of the foreign index key
tag that is created. Index tag names can contain up to 10 characters. Include NODUP to create a
candidate foreign index.
You can create multiple foreign indexes for the table, but the foreign index expressions must specify
different fields in the table.
REFERENCES TableName3 [TAG TagName5]
Specifies the parent table to which a persistent relationship is established. Include TAG TagName5 to
establish a relation based on an index tag for the parent table. Index tag names can contain up to 10
characters. If you omit TAG TagName5, the relationship is established using the parent table's primary
index key by default.
CHECK eExpression2 [ERROR cMessageText2]
Specifies the table validation rule. ERROR cMessageText2 specifies the error message Visual FoxPro
displays when the table validation rule is executed. The message is displayed only when data is
changed within a Browse window or Edit window.
FROM ARRAY ArrayName
Specifies the name of an existing array whose contents are the name, type, precision, and scale for
each field in the table. The contents of the array can be defined with the AFIELDS( ) function.
Remarks
The new table is opened in the lowest available work area, and can be accessed by its alias. The new
table is opened exclusively, regardless of the current setting of SET EXCLUSIVE.
If a database is open and you don't include the FREE clause, the new table is added to the database.
You cannot create a new table with the same name as a table in the database.
If a database isn't open when you create the new table, including the NAME, CHECK, DEFAULT,
FOREIGN KEY, PRIMARY KEY, or REFERENCES clauses generates an error.
Ex01 CD working_directory
** Create Sales Database
CREATE DATABASE Sales
** Create Customers Table
CREATE TABLE Customers;
( CustID C(6) PRIMARY KEY, Name C(15), Phone C(14) )
** Create Books Table
CREATE TABLE Books;
( BookID C(6) PRIMARY KEY, Name C(3) UNIQUE, ;
Price y CHECK(Price>0.0) ;
ERROR ―Price must be positive‖ ;
DEFAULT 0;
)
** Create Orders Table
CREATE TABLE Orders;
( BookID C(6), CustID C(6), Date D, ;
Qty I CHECK(Qty>0) ;
ERROR ―Book quantity must be positive‖ DEFAULT 0, ;
FOREIGN KEY BookID TAG BookID REFERENCES Books, ;
FOREIGN KEY CustID TAG CustID REFERENCES Customers, ;
PRIMARY KEY BookID+CustID TAG OrdID;
)
** Display Database
DISPLAY DATABASE TO FILE tmp
MODIFY FILE ? NOEDIT
Deleting Data Tables
DROP TABLE TableName | FileName | ? [RECYCLE]
Settings
TableName
Specifies the table to remove from the current database and delete from disk.
FileName
Specifies a free table to delete from disk.
?
Displays the Remove dialog from which you can choose a table to remove from the current database
and delete from disk.
RECYCLE
Specifies that the table isn't immediately deleted from disk and is placed in the Microsoft Windows 95
Recycle Bin.
Modifying Data Tables
ALTER TABLE TableName1
ADD | ALTER [COLUMN] FieldName1
FieldType [(nFieldWidth [, nPrecision])]
[NULL | NOT NULL]
[CHECK lExpression1 [ERROR cMessageText1]]
[DEFAULT eExpression1]
[PRIMARY KEY | UNIQUE]
[REFERENCES TableName2 [TAG TagName1]]
[NOCPTRANS]
[NOVALIDATE]
Ex02 ** Add a Field Fax to Customers Table
ALTER TABLE Customers ADD COLUMN Fax C(20) NULL
ALTER TABLE TableName1
ALTER [COLUMN] FieldName2
[NULL | NOT NULL]
[SET DEFAULT eExpression2]
[SET CHECK lExpression2 [ERROR cMessageText2]]
[DROP DEFAULT]
[DROP CHECK]
[NOVALIDATE]
Ex03 ** Delete the Check Rule, and Then Set a New CHECK
ALTER TABLE Orders ALTER COLUMN Qty DROP CHECK
ALTER TABLE Orders ALTER COLUMN Qty SET CHECK Qty>10
ALTER TABLE TableName1
[DROP [COLUMN] FieldName3]
[SET CHECK lExpression3 [ERROR cMessageText3]]
[DROP CHECK]
[ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]]
[DROP PRIMARY KEY]
[ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]]
[DROP UNIQUE TAG TagName4]
[ADD FOREIGN KEY [eExpression5] TAG TagName4 [FOR lExpression6]
REFERENCES TableName2 [TAG TagName5]]
[DROP FOREIGN KEY TAG TagName6 [SAVE]]
[RENAME COLUMN FieldName4 TO FieldName5]
[NOVALIDATE]
Ex04 ** Delete the Primary Key CustID, and Then Add Primary Key CustomerID
ALTER TABLE Customers DROP PRIMARY KEY
ALTER TABLE Customers ADD PRIMARY KEY CustomerID TAG CustomerID
Ex05 ** Add a Field Fax2 With non-NULL Value, then Modify to NULL, and Set Default to NULL
ALTER TABLE Customers ADD COLUMN Fax2 C(20) NOT NULL
ALTER TABLE Customers;
ALTER COLUMN Fax2 NULL;
ALTER COLUMN Fax2 SET DEFAULT .NULL.
Ex06 ** Rename the Name of a Field
ALTER TABLE Customers RENAME COLUMN Company TO Cust_Name
Insert Data Into Data Table
INSERT INTO dbf_name [(fname1 [, fname2, ...])] VALUES (eExpression1 [, eExpression2, ...])
-or-
INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR
Arguments
INSERT INTO dbf_name
Specifies the name of the table to which the new record is appended. dbf_name can include a path and
can be a name expression.
If the table you specify isn't open, it is opened exclusively in a new work area and the new record is
appended to the table. The new work area isn't selected; the current work area remains selected.
If the table you specify is open, INSERT appends the new record to the table. If the table is open in a
work area other than the current work area, it isn't selected after the record is appended; the current
work area remains selected.
[(fname1 [, fname2 [, ...]])]
Specifies the names of the fields in the new record into which the values are inserted.
VALUES (eExpression1 [, eExpression2 [, ...]])
Specifies the field values inserted into the new record. If you omit the field names, you must specify
the field values in the order defined by the table structure. If SET NULL is ON, INSERT – SQL
attempts to insert null values into any fields not specified in the VALUES clause.
FROM ARRAY ArrayName
Specifies the array whose data is inserted into the new record. The contents of the elements of the
array, starting with the first element, are inserted into the corresponding fields of the record. The
contents of the first array element are inserted into the first field of the new record; the contents of the
second array element are inserted into the second field, and so on.
Any default values for fields are ignored when you include the FROM ARRAY clause.
FROM MEMVAR
Specifies that the contents of variables are inserted into fields with the same names as the variables. If
a variable doesn't exist with the same name as the field, the field is left empty.
Remarks
The new record contains the data listed in the VALUES clause or contained in the specified array or
variables. The record pointer is positioned on the new record.
Ex07 INSERT INTO Books ;
(BookID, Name, Price) ;
VALUE(‗012345‘, ‗SAS‘, 210)
Ex08 CLOSE DATABASES
OPEN DATABASE Library
USE Student
SCATTER MEMVAR
COPY STRUCTURE TO NewTable
INSERT INTO NewTable FROM MEMVAR
SELECT NewTable
BROWSE
Update Record In Data Table
UPDATE [DatabaseName1!]TableName1
SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2 ...]
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
Arguments
[DatabaseName1!]TableName1
Specifies the table in which records are updated with new values.
DatabaseName1! specifies the name of a non-current database containing the table. You must include
the name of the database containing the table if the database is not the current one. Include the
exclamation point (!) delimiter after the database name and before the table name.
SET Column_Name1 = eExpression1
[, Column_Name2 = eExpression2
Specifies the columns that are updated and their new values. If you omit the WHERE clause, every
row in the column is updated with the same value.
WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
Specifies the records that are updated with new values.
FilterCondition specifies the criteria that records must meet to be updated with new values. You can
include as many filter conditions as you like, connecting them with the AND or OR operator. You can
also use the NOT operator to reverse the value of a logical expression, or use EMPTY( ) to check for
an empty field.
Remarks
UPDATE - SQL can only update records in a single table. Note that subqueries are supported in
UPDATE – SQL.
Unlike REPLACE, UPDATE - SQL uses record locking when updating multiple records in a table
opened for shared access. This reduces record contention in multiuser situations, but may reduce
performance. For maximum performance, open the table for exclusive use or use FLOCK( ) to lock
the table.
Ex09 ** Reduce Price of Books
UPDATE Books SET Price=Price*0.95 WHERE Name=‖SAS‖
Mark Records For Deletion in Data Table
DELETE FROM [DatabaseName!]TableName
[WHERE FilterCondition1 [AND | OR FilterCondition2 ...]]
Ex10 DELETE FROM Customers WHERE Name=‖Chan‖
Retrieving Data From Data Tables
SELECT [ALL | DISTINCT] [TOP nExpr [PERCENT]]
[Alias.] Select_Item [AS Column_Name]
[, [Alias.] Select_Item [AS Column_Name] ...]
FROM [FORCE]
[DatabaseName!]Table [[AS] Local_Alias]
[[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN
DatabaseName!]Table [[AS] Local_Alias]
[ON JoinCondition …]
[[INTO Destination]
| [TO FILE FileName [ADDITIVE] | TO PRINTER [PROMPT]
| TO SCREEN]]
[PREFERENCE PreferenceName]
[NOCONSOLE]
[PLAIN]
[NOWAIT]
[WHERE JoinCondition [AND JoinCondition ...]
[AND | OR FilterCondition [AND | OR FilterCondition ...]]]
[GROUP BY GroupColumn [, GroupColumn ...]]
[HAVING FilterCondition]
[UNION [ALL] SELECTCommand]
[ORDER BY Order_Item [ASC | DESC] [, Order_Item [ASC | DESC] ...]]
Arguments
SELECT
Specifies the fields, constants, and expressions that are displayed in the query results.
ALL
By default, displays all the rows in the query results.
DISTINCT
Excludes duplicates of any rows from the query results.
Note You can use DISTINCT only once per SELECT clause.
TOP nExpr [PERCENT]
Specifies that the query result contains a specific number of rows or a percentage of rows in the query
result. You must include an ORDER BY clause when you include the TOP clause. The ORDER BY
clause specifies the columns on which the TOP clause determines the number of rows to include in the
query result.
You can specify from 1 to 32,767 rows. Rows with identical values for the columns specified in the
ORDER BY clause are included in the query result. Therefore, if you specify 10 for nExpr, the query
result can contain more than 10 rows if there are more than 10 rows with identical values for the
columns specified in the ORDER BY clause.
If the PERCENT keyword is included, the number of rows returned in the result is rounded up to the
next highest integer. Permissible values for nExpr when the PERCENT keyword is included are 0.01
to 99.99.
Alias.
Qualifies matching item names. Each item you specify with Select_Item generates one column of the
query results. If two or more items have the same name, include the table alias and a period before the
item name to prevent columns from being duplicated.
Select_Item specifies an item to be included in the query results. An item can be one of the following:
The name of a field from a table in the FROM clause.
A constant specifying that the same constant value is to appear in every row of the query
results.
An expression that can be the name of a user-defined function.
AS Column_Name
Specifies the heading for a column in the query output. This option is useful when Select_Item is an
expression or contains a field function and you want to give the column a meaningful name.
Column_Name can be an expression but cannot contain characters (for example, spaces) that aren't
permitted in table field names.
FROM
Lists the tables containing the data that the query retrieves. If no table is open, Visual FoxPro displays
the Open dialog box so you can specify the file location. Once open, the table remains open once the
query is complete.
FORCE specifies that tables are joined in the order in which they appear in the FROM clause. If
FORCE is omitted, Visual FoxPro attempts to optimize the query. However, the query might be
executed faster by including the FORCE keyword to disable the Visual FoxPro query optimization.
DatabaseName!
Specifies the name of a non-current database containing the table. You must include the name of
database containing the table if the database is not the current database. Include the exclamation point
(!) delimiter after the database name and before the table name.
[AS] Local_Alias
Specifies a temporary name for the table named in Table. If you specify a local alias, you must use the
local alias in place of the table name throughout the SELECT statement.
INNER JOIN specifies that the query result contains only rows from a table that match one or more
rows in another table.
LEFT [OUTER] JOIN specifies that the query result contains all rows from the table to the left of the
JOIN keyword and only matching rows from the table to the right of the JOIN keyword. The OUTER
keyword is optional; it can be included to emphasize that an outer join is created.
RIGHT [OUTER] JOIN specifies that the query result contains all rows from the table to the right of
the JOIN keyword and only matching rows from the table to the left of the JOIN keyword. The
OUTER keyword is optional; it can be included to emphasize that an outer join is created.
FULL [OUTER] JOIN specifies that the query result contains all matching and non matching rows
from both tables. The OUTER keyword is optional; it can be included to emphasize that an outer join
is created.
ON JoinCondition specifies the conditions for which the tables are joined.
INTO Destination
Specifies where to store the query results. If you include the INTO clause and the TO clause in the
same query, the TO clause is ignored. If you don't include the INTO clause, query results are
displayed in a Browse window. You can also use TO to direct query results to the printer or a file.
Destination can be one of the following clauses:
ARRAY ArrayName, which stores query results in a memory variable array. The array isn't
created if the query selects 0 records.
CURSOR CursorName [NOFILTER], which stores query results in a cursor. If you specify the
name of an open table, Visual FoxPro generates an error message. After SELECT is executed,
the temporary cursor remains open and is active but is read-only. Once you close this
temporary cursor, it is deleted. Cursors may exist as a temporary file on the drive or volume
specified by SORTWORK.
Include NOFILTER to create a cursor that can be used in subsequent queries. In previous
versions of Visual FoxPro, it was necessary to include an extra constant or expression as a
filter to create a cursor that could be used in subsequent queries. For example, adding a logical
true as a filter expression created a query that could be used in subsequent queries:
SELECT *, .T. FROM customers INTO CURSOR myquery
Including NOFILTER can reduce query performance because a temporary table is created on
disk. The temporary table is deleted from disk when the cursor is closed.
DBF | TABLE TableName
[DATABASE DatabaseName [NAME LongTableName]] stores query results in a table. If you
specify a table that is already open and SET SAFETY is set to OFF, Visual FoxPro overwrites
the table without warning. If you don't specify an extension, Visual FoxPro gives the table
a .DBF extension. The table remains open and active after SELECT is executed.
Include DATABASE DatabaseName to specify a database to which the table is added. Include
NAME LongTableName to specify a long name for the table. Long names can contain up to
128 characters and can be used in place of short file names in the database.
TO FILE FileName
If you include a TO clause but not an INTO clause, you can direct query results to an ASCII text file
named FileName, to the printer, or to the main Visual FoxPro window.
ADDITIVE appends query output to the existing contents of the text file specified in TO FILE
FileName.
TO PRINTER [PROMPT] directs query output to a printer. Use the optional PROMPT clause to
display a dialog box before printing starts. In this dialog box, you can adjust printer settings. The
printer settings that you can adjust depend on the currently installed printer driver. Place PROMPT
immediately after TO PRINTER.
TO SCREEN directs query output to the main Visual FoxPro window or to an active user-defined
window.
PREFERENCE PreferenceName
Saves the Browse window's attributes and options for later use, if query results are sent to a Browse
window. PREFERENCE saves the attributes, or preferences, indefinitely in the FOXUSER resource
file. Preferences can be retrieved at any time.
Issuing SELECT with a PREFERENCE PreferenceName for the first time creates the preference.
Issuing SELECT later with the same preference name restores the Browse window to that preference
state. When the Browse window is closed, the preference is updated.
If you exit a Browse window by pressing CTRL+Q+W, changes you've made to the Browse window
are not saved to the resource file.
NOCONSOLE
Prevents display of query results sent to a file, the printer, or the main Visual FoxPro window.
PLAIN
Prevents column headings from appearing in the query output that is displayed. You can use PLAIN
whether or not a TO clause is present. If an INTO clause is included, PLAIN is ignored.
NOWAIT
Continues program execution after the Browse window is opened and query results are directed to it.
The program doesn't wait for the Browse window to be closed, but continues execution on the
program line immediately following the SELECT statement.
When TO SCREEN is included to direct output to the main Visual FoxPro window or to a user-
defined window, output pauses when the main Visual FoxPro window or user-defined window is full
of query results. Press a key to see the next set of query results. If NOWAIT is included, the query
results are scrolled off the main Visual FoxPro window or the user-defined window without pausing
for a key press. NOWAIT is ignored if included with the INTO clause.
WHERE
Tells Visual FoxPro to include only certain records in the query results. WHERE is required to
retrieve data from multiple tables.
JoinCondition
specifies fields that link the tables in the FROM clause. If you include more than one table in a query,
you should specify a join condition for every table after the first.
You must use the AND operator to connect multiple join conditions. Each join condition has the
following form:
FieldName1 Comparison FieldName2
FieldName1 is the name of a field from one table, FieldName2 is the name of a field from another
table, and Comparison is one of the following operators:
Operator Comparison
= Equal
== Exactly equal
LIKE SQL LIKE
, !=, # Not equal
> More than
>= More than or equal to
= 1000
Example 3
Example 3 displays FilterCondition in the form of FieldName Comparison ALL (Subquery)
When the filter condition includes ALL, the field must meet the comparison condition for all values
generated by the subquery before its record is included in the query results.
company 80
Ex16 ** Retrieve Courses of Credit Between 3 and 4
SELECT Cid, Name FROM Courses WHERE Credit BETWEEN 3 AND 4
Ex17 ** Retrieve Student Names in Classes AMA268 And AMA488
SELECT Name FROM Students WHERE Class IN (‗AMA268, ‗AMA488‘)
Ex18 ** Retrieve Student Names in Classes AMA2*
SELECT Name FROM Students WHERE Class LIKE ‗AMA2%‘
Ex19 ** Retrieve Student Names in Classes AMA??8
SELECT Name FROM Students WHERE Class LIKE ‗AMA__8‘
Ex20 ** Retrieve Sid AND Cid With NULL Score
SELECT Sid, Cid FROM Scores WHERE Score IS NULL
Ex21 ** Retrieve Sid AND Score Of Cid 0001 In Descending Order of Score
SELECT Sid, Score FROM Scores WHERE Cid=‘0001‘ ORDER BY Score DESC
Using Set Functions
Ex22 SELECT count(Class) FROM Students
SELECT count(DISTINCT Class) FROM Students
Ex23 SELECT avg(Score) FROM Scores WHERE Cid=‘0002‘
SELECT avg(DISTINCT Score) FROM Scores WHERE Cid=‘0002‘
Grouping
Ex24 SELECT Class, count(Sid) FROM Students GROUP BY Class
Ex25 ** Retrieve Sid of Students Who Have At Least 2 Scores
SELECT Sid FROM Scores GROUP BY Sid HAVING count(*)>=2
TOP --- ORDER BY Query
Ex26 SELECT TOP 2 Sid FROM Scores ORDER BY Score DESC
SELECT---FROM---WHERE is a query module. We can place a query module (inner query)
underneath another one (outer query) to form a complex query.
IN is used when the inner query returns a collection of records.
Ex27 SELECT Name FROM Students WHERE Sid IN ;
(SELECT Sid FROM Scores WHERE Cid=‘0001‘)
SELECT Name, Class FROM Students WHERE Class IN ;
(SELECT Class FROM Students WHERE Name=‘Cheung Sam‘)
Inner query and outer query can be joined by comparison of the two queries.
Ex28 SELECT Sid, Name, Class FROM Students WHERE ;
Class=(SELECT Class FROM Students WHERE Name=‘Cheung Sam‘)
Ex29 SELECT Name FROM Students WHERE ;
YEAR(Birthday)‘AMA289‘
Ex30 SELECT Name FROM Students WHERE ;
YEAR(Birthday)‘AMA289‘
The outer query only needs a ‗TRUE‘ or ‗FALSE‘ return.
Ex31 SELECT Name FROM Students WHERE EXISTS ;
(SELECT * FROM Scores WHERE Sid=Students.Sid AND Cid=‘0001‘)
Query Joining Two Tables
Ex32 SELECT Students., Scores.* FROM Students, Scores ;
WHERE Students.Sid=Scores.Sid
Ex33 ** WHERE is replaced by ON
SELECT Students., Scores.* FROM Students LEFT JOIN Scores ;
ON Students.Sid=Scores.Sid
SELECT Students., Scores.* FROM Students RIGHT JOIN Scores ;
ON Students.Sid=Scores.Sid
SELECT Students., Scores.* FROM Students FULL JOIN Scores ;
ON Students.Sid=Scores.Sid