LABORATORUL NR.

10
NUCLEUL SQL DIN VISUAL FOXPRO 6.0.

Consultarea datelor din bazele de date

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] ...]]

Exemple de interogări
Selecţia, proiecţia , reuniunea

Ce agenti economici mari au sediul în zona BURDUJENI?

SELECT *;
FROM AGENTI1;
WHERE zona="BURDUJENI"
Care sunt facturile emise în data de 29.01.1999, cu valoare mai mare de 2000000 corespunzător
apei potabile?
SELECT *;
FROM FACTURI;
WHERE DATA= {^1999-01-29}AND TIP=12 AND VAL >2000000
Care sunt facturile pentru care valoarea consumului de apă potabilă este mai mare de 1500000
şi mai mică de 4000000?
SELECT *;
FROM FACTURI;
WHERE Tip=12 AND Val>1500000 AND val <4000000
Aceeaşi interogare se poate scrie astfel:
SELECT *;
FROM FACTURI;
WHERE Tip=12 AND Val BETWEEN 1500000 AND 4000000
Care sunt agenţii al căror nume începe cu "S" şi sunt societăţi pe acţiuni?
SELECT *;
FROM Agenti1;
WHERE Nume LIKE "S%SA"
Care sunt agenţii al căror nume este format din 9 caractere, începe cu "S" şi sunt societăţi pe
acţiuni?
SELECT *;
FROM Agenti1;
WHERE Nume LIKE "S______SA"
Care sunt agenţii din zonele: CENTRALĂ şi ZAMCA?
SELECT *;
FROM AGENTI1;
WHERE Zona = "CENTRALĂ" OR Zona="ZAMCA"
Sau
SELECT * ;
FROM AGENTI1;
WHERE Zona IN ("CENTRALĂ"," ZAMCA")

Care sunt agenţii pentru care nu s-a introdus numele managerului?

SELECT *;
FROM AGENTI1;
WHERE manger IS NULL
In ce zone ale oraşului îşi au sediul agenţii comerciali, clienţi ai firmei?
SELECT DISTINCT Zona;
FROM Agenti
Care este numele fiecărui agent comercial şi adresa sa?
SELECT Nume, Adresa;
FROM Agenti
Dacă se doreşte o prezentare ordonată a agenţilor se urtilizează clauza ORDER BY.
SELECT *;
FROM AGENTI;
ORDER BY Nume
Dacă se doreşrte ordonarea descrescătoare se foloseşte următoarea formulare:
SELECT *;
FROM Agenţi;
ORDER BY Zona ASCENDING, Nume DESCENDING

Pornind de la tabelele Agenti1 şi Agenti2 , tabelul reuniune care reprezintă tabelul AGENTI va fi
obţinut în SQL prin fraza:
SELECT *;
FROM AGENTI1;
UNION;
SELECT *;
FROM AGENTI2;

Operatorului de joncţiune în SQL

Căror agenţii le-au fost listate facturile şi care sunt seriile şi numerele speciale alocate acestora?
SELECT DISTINCT [Link], Agenţ[Link], Agenţi. Adresa, [Link], [Link];
FROM Agenti, NRSP;
WHERE Agenţ[Link]=[Link]
sau
SELECT DISTINCT [Link], nume, Adresa, seria, nrs;
FROM Agenti, NRSP;
WHERE Agenţ[Link]=[Link]
 Echijoncţiunea poate fi realizată prin clauza INNER JOIN plasată în clauza FROM, fără
clauza WHERE. Astfel, ultima frază SELECT poate fi scrisă astfel:
SELECT *;
FROM Agenti INNER JOIN NRSP ON;
Agenţ[Link]=[Link]
In ce zone ale oraşului se găsesc agentii economici cărora li s-a facturat o sumă mai mare de
1.000.000 lei pentru apa potabilă?
SELECT DISTINCT Zona;
FROM Agenti, Facturi;
WHERE Tip=12 AND Val>1000000 AND ;
[Link]=[Link]

Funcţiile predefinite: COUNT, SUM, AVG, MAX, MIN

Formatul general al unei fraze SELECT ce conţine funcţii predefinite este:

SELECT fcţ_pred1, fcţ_pred2,..fcţ_predn;

FROM listă de tabele;
WHERE condiţii
In lipsa opţiunii GROUP BY, dacă în clauza SELECT este prezentă o funcţie predefinită,
tabelul rezultat va conţine o singură linie.

Funcţia COUNT
Câţi clienţi are firma?
SELECT COUNT (cod) AS nr_clienti;
FROM Agenti
Câte formulare de facturi au fost anulate?
SELECT COUNT (anulat);
FROM NRSP;
WHERE anulat="Da"
Câte facturi s-au emis în data de 29.01.1999
SELECT COUNT (DISTINCT numar);
FROM FACTURI;
WHERE Data={^1999-01-29}

Funcţia SUM
Calculează suma valorilor unei coloane.
Care este valoarea totală a facturilor emise?
SELECT SUM(Val) AS Total_fe;
FROM Facturi
Care este valoarea apei potabile facturate?
SELECT SUM(Val) AS Total_fe;
FROM Facturi;
WHERE tip=12
Care este valoarea facturii emise pentru AKROM AKAL?
SELECT SUM(val) AS Total_ELCO;
FROM FACTURI, AGENTI;
WHERE [Link]=[Link] AND AGENŢ[Link]="AKROM AKAL"
sau
SELECT SUM(val) AS Total_ELCO;
FROM FACTURI;
WHERE cod IN;
(SELECT cod;
 FROM AGENTI;
WHERE nume="AKROM AKAL" )
Funcţia AVG
Calculează media aritmetică a unei coloane într-un tabel oarecare, prin divizarea sumei
valorilor coloanei respective la numărul de valori nenule ale acesteia.
Care este valoarea medie a prestaţiilor facturate în factura cu numărul 10589?
SELECT AVG (Pret*Cant) AS Medie_10589;
FROM FACTURI;
WHERE Numar=10589
Observaţie: Valoarea medie a tuturor facturilor nu poate fi calculată cu funcţia AVG. In acest caz
este necesară utilizarea opţiunii GROUP BY.
Care este valoarea medie a serviciilor de canalizare facturate?
SELECT AVG (Pret*Cant) AS Medie_canal;
FROM FACTURI;
WHERE tip=22
Care este valoarea medie facturată pentru serviciile de canal în zonele Burdujeni şi Iţcani?
SELECT AVG (Pret*Cant) AS Medie_canal;
FROM FACTURI;
WHERE tip=22 AND cod IN;
(SELECT cod;
FROM AGENTI;
WHERE Zona IN ("BURDUJENI","Iţcani")
Funcţiile MAX şi MIN
Determină valorile maxime, respectiv minime ale unei coloane în cadrul unui tabel.
Care este codul agentului pentru care s-a facturat cea mai mare cantitate de apă?
SELECT cod ;
FROM Facturi;
WHERE val IN;
(SELECT MAX (Val);
FROM Facturi;
WHER tip=12)
Gruparea tuplurilor. Clauzele GROUP BY şi HAVING
SQL permite utilizarea clauzei GROUP BY pentru a forma grupuri de tupluri într-o relaţie
pe baza valorilor comune ale unei coloane. Prin asocierea unei clauze HAVING la o clauză
GROUP BY este posibilă selectarea anumitor grupe de tupluri care îndeplinesc un criteriu.
Formatul general al clauzei GROUP BY este:
SELECT col1, col2,..coln;
FROM tabel;
GROUP BY coloană de grupare
Care este totalul valorilor facturate pentru fiecare serviciu prestat?
SELECT Tip, Produs, SUM(val);
FROM FACTURI ;
GROUP BY Tip
In acest caz tabelul rezultat va aveaun număr de linii egal cu numărul tipurilor de prestaţii distincte
dintabelul Facturi. Pentru toate poziţiile din facturi care se referă la un anumit produs se va calcula
suma valorilor deoarece se foloseşte funcţia SUM.
Care este numărul facturilor trimise în fiecare zonă a orasului?
SELECT Zona, COUNT(numar);
FROM AGENTI, NRSP;
WHERE [Link]=[Link];
GROUP BY [Link]
Care este valoarea totală a facturilor emise, pe zile, pentru fiecare zona ?
SELECT Zona, Data, SUM (val);
FROM Facturi, Agenti;
WHERE [Link]=Agenţ[Link] ;
GROUP BY Zona, Data

Clauza HAVING
Clauza HAVING lucrează împreună cu o clauză GROUP BY, fiind practic o clauză WHERE
aplicată acesteia. Formatul general este:
SELECT col1, col2,..coln;
FROM tabel;
GROUP BY coloană de regrupare;
HAVING caracteristică de grup
Pentru facturile emise, interesează valoarea zilnică, dar numai dacă această valoare este mai
mare de 5.000.000 lei.
SELECT Data, SUM(val);
FROM Facturi;
GROUP BY Data;
HAVING SUM(val)>5000000
Care sunt agenţii pentru care în facturi există mai mult de două poziţii pentru un produs
furnizat?
SELECT nume;
FROM Agenti;
WHERE cod IN;
(SELECT cod;
FROM FACTURI;
GROUP BY tip, cod;
HAVING COUNT(*)>2)

SQL IN VFP
DIN VFP HELP
Structured Query Language (SQL) commands.
Visual FoxPro supports the following SQL commands:
SELECT - SQL
You can create a SELECT command query in these areas:
• In the Command window.
• In a Visual FoxPro program (like any other Visual FoxPro command).
• In the Query Designer.
ALTER TABLE - SQL
Modifies an existing table. You can modify the name, type, precision, scale, null value support, and
referential integrity rules for each field in the table.
CREATE CURSOR - SQL
Creates a temporary table. Each field in the temporary table is defined with a name, type, precision,
scale, null value support, and referential integrity rules. These definitions can be obtained from the
command itself or from an array.
CREATE TABLE -SQL
Creates a table. Each new table field is defined with a name, type, precision, scale, null value
support, and referential integrity rules. These definitions can be obtained from the command itself or
from an array.
DELETE - SQL
Marks records in a table for deletion using SQL syntax.
INSERT - SQL
Appends a new record to the end of an existing table. The new record contains data listed in the
INSERT command or from an array.
UPDATE - SQL
Updates records in a table. The records can be updated based on the results of a SELECT - SQL
statement.

INTRODUCERE

Astfel pentru MANIPULAREA DATELOR sint utilizate comenzile

 SELECT Extragerea datelor din BD

 INSERT Adaugarea de noi linii/inscrieri in BD
 DELETE Stergerea de linii dintr-o tebela
 UPDATE Modificarea valorilor unor atribute

Pentru DEFINREA BAZEI DE DATE

 CREATE TABEL Adaugarea unei noi tabele in BD

 DROP TABLE Stergerea unei tabele din BD
 ALTER TABLE Modificarea structurii unui tabel
 CREATE VIEW Crearea unei tabele virtuale
 CREATE CURSOR Crearea unei tabele temporare

Pentru CONTROLUL TRANZACTIILOR

 BEGIN TRANZACTION Marcheaza inceputul unei tranzactii

 END TRANZACTION Marcheaza sfirsitul unei tranzactii
 ROLLBACK Abandoneaza tranzactia in curs

1. CREAREA TABELELOR SI DECLARAREA RESTRICTIILOR

Se efectuiaza cu ajutorul comenzii CREATE TABLE iar pentru modificarea structurii tabelului cu
ALTER TABLE
Syntaxa
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]] && Tablename2 tabela-parinte a
legaturii
[NOCPTRANS]
[, FieldName2 ...]
[, PRIMARY KEY eExpression2 TAG TagName2
|, UNIQUE eExpression3 TAG TagName3] && Se creaza un index candidat
[, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] && Se creaza un index regular
REFERENCES TableName3 [TAG TagName5]]
[, CHECK lExpression2 [ERROR cMessageText2]])
| FROM ARRAY ArrayName
Exemplu din Materialul HELP al VFP.
The following example creates a new database named Mydata1. CREATE TABLE is used to create
three tables (Salesman, Customer, and Orders). The FOREIGN KEY and REFERENCES clauses
in the second CREATE TABLE command create a persistent one-to-many relationship between the
Salesman and Customer tables. The DEFAULT clauses in the third CREATE TABLE command
establish default values, and the CHECK and ERROR clauses establish business rules for entering
data into specific fields. The MODIFY DATABASE is used to display the relationship between the
three tables.
CLOSE DATABASES
CLEAR
* Create mydata database in the current directory or folder
CREATE DATABASE mydata1

* Create a salesman table with a primary key

CREATE TABLE salesman ;
(SalesID c(6) PRIMARY KEY, ;
SaleName C(20))

* Create a customer table and relate it to the salesman table.

CREATE TABLE customer ;
(SalesID c(6), ;
CustId i PRIMARY KEY, ;
CustName c(20) UNIQUE, ;
SalesBranch c(3), ;
FOREIGN KEY SalesId TAG SalesId REFERENCES salesman)

* Create an orders table related to customer with its own primary

* key and some business rules such as defaults & checks.
CREATE TABLE orders ;
(OrderId i PRIMARY KEY, ;
CustId i REFERENCES customer TAG CustId, ;
OrderAmt y(4), ;
OrderQty i ;
DEFAULT 10 ;
CHECK (OrderQty > 9) ;
ERROR "Order Quantity must be at least 10", ;
DiscPercent n(6,2) NULL ;
 DEFAULT .NULL., ;
CHECK (OrderAmt > 0) ERROR "Order Amount Must be > 0" )

* Display new database, tables, and relationships

MODIFY DATABASE

* Delete example files

SET SAFETY OFF && To suppress verification message
CLOSE DATABASES && Close database before deleting
DELETE DATABASE mydata1 DELETETABLES

2. UTILIZAREA INSTRUCTIUNILOR SQL PENTRU CREAREA BD SI A

TABELELOR

******program care prezinta crearea si manipularea cu datele a unei BD prin intermediul SQL -
******comenzilor

CLOSE DATA ALL

CLOSE TABLES ALL
CLOSE ALL

set default to home()+"\SQL_test\"

set path to TIF

DELETE DATABASE xxxxxx DELETETABLES

wait window "A fost distrusa BD xxxxxx" AT 20,60 timeout 3

CREATE DATABASE xxxxxx

CREATE TABLE raioane (;
nrai CHAR(2);
PRIMARY KEY;
CHECK(nrai=LTRIM(UPPER(nrai)));
ERROR 'indicativul judetului se scrie cu majuscule !', ;
raion CHAT(25) ;
NOT NULL ;
CHECK(raion=LTRIM(PROPER(raion))) ;
ERROR 'Prima literea din fiecare cuvint al' +chr(13)+;
'denumirii raionului este majuscula'+chr(13)+;
'restul literelor sint mici !',;
regiune CHAR(15);
DEFAULT 'Centru';
CHECK (INLIST(regiune,'Centru','Nord','Sud'));
ERROR 'Regiunea poate avea doar o valoare din
lista'+CHR(13)+;
'Centru,Nord,Sud';
)

********** Tabelul 2

CREATE TABLE localitati (;

codpost CHAR(5);
PRIMARY KEY;
 CHECK(cospost=LTRIM(UPPER(codpost)));
ERROR 'Codul postal se scrie fara spatii la inceput !', ;
loc CHAT(25) ;
NOT NULL ;
CHECK(loc=LTRIM(PROPER(loc))) ;
ERROR 'Prima literea din fiecare cuvint al' + CHR (13)+;
'denumirii localitatii este majuscula'+ CHR (13)+;
'restul literelor sint mici !',;
nrai CHAR(2);
DEFAULT 'IS',;
FOREIGN KEY nrai TAG nrai REFERENCES raioane TAG nrai;
);
********** Tabelul 3

CREATE TABLE clienti (;

codcl NUMBER(6);
PRIMARY KEY;
CHECK(codcl>1000),;
ERROR 'Codul clientului trebuie sa fie mai mare decit 1000 !', ;
dencl CHAR(30) ;
CHECK(SUBSTR(dencl,1,1)=UPPER(SUBSTR(dencl,1,1))) ;
ERROR 'Prima literea din denumirea clientului este'+ CHR
(13)+;
'obligatoriu majuscula!' ,;
codfiscal CHAR(9);
NULL,;
CHECK(SUBSTR(codfiscal,1,1)=UPPER(SUBSTR(codfiscal,1,1))) ;
ERROR 'Prima literea din codul fiscal este'+ CHR (13)+;
'obligatoriu majuscula!' ,;
adresa CHAR(40);
NULL;
CHECK(SUBSTR(adresa,1,1)=UPPER(SUBSTR(adresa,1,1))) ;
ERROR 'Prima literea din adresa este'+ CHR (13)+;
'obligatoriu majuscula!' ,;
codpost CHAR(5),;
telefon CHAR(10) ;
NULL, ;
FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG
codpost;
);
********** Tabelul 4

CREATE TABLE persoane (;

cnp CHAR(14);
PRIMARY KEY;
CHECK(cnp=LTRIM(UPPER(cnp)));
ERROR 'Codul postal se scrie fara spatii la inceput !', ;
nume CHAR(20) ;
CHECK(nume= LTRIM(PROPER(nume))) ;
ERROR 'Prima literea din fiecare cuvint al' + CHR (13)+;
'numelui persoanei este majuscula'+ CHR (13)+;
'restul literelor sint mici !',;
 prenume CHAR(20);
CHECK(prenume= LTRIM(PROPER(prenume))) ;
ERROR 'Prima literea din fiecare cuvint al' + CHR (13)+;
'prenumelui persoanei este majuscula'+ CHR (13)+;
'restul literelor sint mici !',;
adresa CHAR(40);
NULL;
CHECK(SUBSTR(adresa,1,1)=UPPER(SUBSTR(adresa,1,1))) ;
ERROR 'Prima literea din adresa este'+ CHR (13)+;
'obligatoriu majuscula!' ,;
sex CHAR(1) DEFAULT 'B';
CHECK(INLIST(SEX,'F','B'));
ERROR 'Atributul Sex poate avea valorile F pentru femei'+ CHR
(13)+;
'si B pentru barabti' ,;
codpost CHAR(5),;
telacasa CHAR(10) NULL,;
telbirou CHAR(10) NULL,;
telmobil CHAR(10) NULL,;
email CHAR(20) NULL,;
FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG
codpost;
);
********** Tabelul 5

CREATE TABLE persclient (;

cnp CHAR(14),;
codcl NUMBER(6),;
functie CAHR(25);
CHECK(SUBSTR(functie,1,1)=UPPER(SUBSTR(functie,1,1))) ;
ERROR 'Prima litera din functie se scrie cu majuscule !', ;
PRIMARY KEY cnp+STR(codcl,6)+functie TAG primaru, ;
FOREIGN KEY cnp TAG cnp REFERENCES persoane TAG cnp,;
FOREIGN KEY codcl TAG codcl REFERENCES clienti TAG codcl;
)
********** Tabelul 6

CREATE TABLE produse (;

codpr NUMBER(6);
PRIMARY KEY;
CHECK(codcl>0),;
ERROR 'Codul produsului trebuie sa fie mai mare decit 0 !', ;
denpr CHAR(30) ;
CHECK(SUBSTR(denpr,1,1)=UPPER(SUBSTR(denpr,1,1))) ;
ERROR 'Prima literea din denumirea produsului este'+ CHR
(13)+;
'obligatoriu majuscula!' ,;
um CHAR(10),;
grupa CHAR(15);
CHECK(SUBSTR(grupa,1,1)=UPPER(SUBSTR(grupa,1,1))) ;
ERROR 'Prima literea din grupa este'+ CHR (13)+;
'obligatoriu majuscula!' ,;
 procTVA NUMBER(3,2);
DEFAULT 0.28,;
imagine GENERAL;
)
********** Tabelul 7

CREATE TABLE gestiuni (;

gestiune CHAR(4);
PRIMARY KEY,;
den_gest CHAR(25);
CHECK(SUBSTR(den_gest,1,1)=UPPER(SUBSTR(den_gest,1,1))) ;
ERROR 'Prima literea din denumirea produsului este'+ CHR (13)+;
'obligatoriu majuscula!' ,;
adresa CHAR(40) ;
NULL;
CHECK(SUBSTR(adresa,1,1)=UPPER(SUBSTR(adresa,1,1))) ;
ERROR 'Prima literea din adresa este'+ CHR (13)+;
'obligatoriu majuscula!' ,;
codpost CHAR(5),;
telefon CHAR(10);
NULL,;
cnp CHAR(14),;
email CHAR(20) NULL,;
FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG
codpost,;
FOREIGN KEY cnp TAG cnp REFERENCES persoane TAG cnp;
)
********** Tabelul 8

CREATE TABLE facturi(;

nrfact NUMBER(8);
PRIMARY KEY,;
datafact DATE;
DEFAULT DATE();
CHECK(BETWEEN(datafact,{^2001/01/01},{^2010/12/31})) ;
ERROR 'Data fact se afal in interv 1 ian 2001-31 decembrie 2010!',
;
gestiune CHAR(4) NOT NULL,;
codcl NUMBER(6),;
obs CHAR(50) NULL,;
FOREIGN KEY gestiune TAG gestiune REFERENCES gestiuni TAG
gestiune,;
FOREIGN KEY codcl TAG codcl REFERENCES clienti TAG codcl;
)
********** Tabelul 9
CREATE TABLE liniifact(;
nrfact NUMBER(8),;
linie NUMBER(2);
CHECK(linie>0),;
ERROR 'Codul linie trebuie sa fie mai mare decit 0 !', ;
codpr NUMBER(6),;
cantitate NUMBER(10),;
 pretunit NUMBER(12),;
PRIMARY KEY STR(nrfact,8)+STR(linie,2) TAG primaru, ;
FOREIGN KEY nrfact TAG nrfact REFERENCES facturi TAG nrfact,;
FOREIGN KEY codpr TAG codpr REFERENCES produse TAG codpr;
)
********** Tabelul 10

CREATE TABLE incasari(;

codinc NUMBER(8);
PRIMARY KEY,;
datainc DATE;
DEFAULT DATE();
CHECK(BETWEEN(datainc,{^2001/01/01},{^2010/12/31})) ;
ERROR 'Data inc se afal in interv 1 ian 2001-31 decembrie
2010!', ;
coddoc CHAR(4);
CHECK(coddoc= UPPER (LTRIM (coddoc)));
ERROR 'Codul documentului se scrie cu majuscule!', ;
nrdoc CHAR(16),;
datadoc DATE;
DEFAULT DATE()-5;
)
********** Tabelul 11

CREATE TABLE incasfact(;

codinc NUMBER(8),;
nrfact NUMBER(8),;
transa NUMBER(16) NOT NULL,;
PRIMARY KEY STR(codinc,8)+STR(nrfact,8) TAG primaru, ;
FOREIGN KEY codinc TAG codinc REFERENCES incasari TAG codinc,;
FOREIGN KEY nrfact TAG nrfact REFERENCES facturi TAG nrfact;
)
Atentie!!!
La crearea indexilor compusi, la crearea indexilor Primari / PRIMARY KEY /, Regulari /FOREIGN
KEY/ si Candidat /UNOQUE/.
Din pacate declararea cheilor straine nu inseamna si intituirea restrictiei referentiale. Pentru a o
institui este necesar de a institui restrictiile mentionate cu Referential Integrity Builder.

3. MODIFICAREA STRUCTURII TABELELOR

O proiectare buna in general creaza putine probleme ce tin de modificarea structurii BD. Ea de fapt
ramine intacta. Cu toate acestea pot aparea unele necesitati de modificare a lungimii unui atribut,
adaugarii unui atribut, stergerii unui atribut s.a.
Pentru a modifica structura tabelelor de date este utilizata comanda ALTER TABLE. In cele ce
urmeaza vom prezenta citeva formate de sintaxa a acestei comenzi si citeva exemple.
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]
-or-
ALTER TABLE TableName1
ALTER [COLUMN] FieldName2
[NULL | NOT NULL]
[SET DEFAULT eExpression2]
[SET CHECK lExpression2 [ERROR cMessageText2]]
[DROP DEFAULT]
[DROP CHECK]
[NOVALIDATE]
-or-
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]
Exemple din HELP VFP.
ALTER TABLE – SQL Command Examples
Example 1 adds a field called fax to the customer table and allows the field to have null values.
Example 2 makes the cust_id field the primary key of the customer table.
Example 3 adds a field validation rule to the quantity field of the orders table so that values in the
quantity field must be non-negative.
Example 4 adds a one-to-many persistent relation between the customer and orders tables based
on the primary key cust_id in the customer table and a new foreign key index cust_id in the
orders table.
Example 5 removes the field validation rule from the quantity field in the orders table.
Example 6 removes the persistent relation between the customer and orders tables, but keeps the
cust_id index tag in the orders table.
Example 7 adds a field called fax2 to the customer table and prevents the field from containing null
values. The new structure of the table is displayed. Two ALTER COLUMN clauses are used to allow
the field to have null values and set the default value for the field to the null value. Note that multiple
ALTER COLUMN clauses are required to change more than one property of a field in a single
ALTER TABLE command. The new field is then removed from the table to restore the table to its
original state.

* Example 1
SET PATH TO (HOME(2) + 'Data\') && Sets path to table
ALTER TABLE customer ADD COLUMN fax c(20) NULL

* Example 2
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY

* Example 3
ALTER TABLE orders;
ALTER COLUMN quantity SET CHECK quantity >= 0;
ERROR "Quantities must be non-negative"

* Example 4
ALTER TABLE orders;
ADD FOREIGN KEY cust_id TAG cust_id REFERENCES customer

* Example 5
ALTER TABLE orders ALTER COLUMN quantity DROP CHECK

* Example 6
ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE

* Example 7
CLEAR
ALTER TABLE customer ADD COLUMN fax2 c(20) NOT NULL
DISPLAY STRUCTURE

ALTER TABLE customer;

ALTER COLUMN fax2 NULL;
ALTER COLUMN fax2 SET DEFAULT .NULL.

ALTER TABLE customer DROP COLUMN fax2

In cazul exemplului BD generate de noi

Adaugarea atributului DataNast – adica data nasterii in tabela PERSOANE se realizeaza

astfel

ALTER TABLE PERSOANE ADD DataNast DATE

Redenumirea unui atribut, il poate face doar VFP printere cele mai renumite SGBD-uir

ALTER TABLE PERSOANE RENAME COLUMN DataNast TO DataNasterii

Stergerea unui atribut, sa zicem pe cel pe care numai ce l-am adaugat se va face prin
comanda

ALTER TABLE PERSOANE DROP COLUMN DataNasterii

Modificarea tipului/lungimii unui atribut

ALTER TABLE PERSOANE ALTER COLUMN Prenume CHAR(21)

ALTER TABLE PERSOANE ALTER COLUMN Nume CHAR(17)

Adaugarea/modificarea valorii implicite

ALTER TABLE PERSOANE ALTER COLUMN Sex SET DEFAULT ‘F’

Se definesc valorile prin Default, deoarece se presupune ca exista mai multe femei decit
barbate.
Interzicerea valorilor nule pentru atributul Sex se executa dupa comanda.

ALTER TABLE PERSOANE ALTER COLUMN Sex NOT NULL

Adaugarea/anularea restrictiilor se executa tot cu ajutorul ALTER

Restrictiile – PRIMARY KEY – cheie primara
– UNIQUE – unicitate
– FOREIGN KEY – referentiala
– CHECK – de comportament.
De exemplu dezactivarea cheiei primare se efectuiaza cu ajutorul comenzii

ALTER TABLE PERSOANE DROP PRIMARY KEY

Pentru restabilirea keiei

ALTER TABLE PERSOANE ADD PRIMARY KEY (CNP) && numele cimpului in paranteze

Atentie!!!
NOVALIDATE – se permite operarea unor modificari ale structurii chiar daca
acestea ar viola integritatea datelor.

[Link] DE ACTUALIZARE
Trei sunt comenzile de actualizare – INSERT, UPDATE si DELETE
A. Adaugarea unei linii/inscrieri
Din HELP al VFP
Appends a record to the end of a table that contains the specified field values.
Syntaxa
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.
INSERT – SQL Command Examples din HELP VFP
The following example opens the employee table and adds one record.
USE employee
INSERT INTO employee (emp_no, fname, lname, officeno) ;
VALUES (3022, "John", "Smith", 2101)
The following example opens the customer table in the testdata database. The contents of the
current record are scattered to variables, and the table's structure is copied to a new table named
cust2. INSERT - SQL is used to insert a new record in the cust2 table, and BROWSE is issued to
display the new record.
CLOSE DATABASES
CLEAR

OPEN DATABASE (HOME(2) + 'Data\testdata')

USE Customer && Open customer table
* Scatter current record to memory variables
SCATTER MEMVAR

* Copy structure of current table to example table

COPY STRUCTURE TO cust2

* Insert record from memory variable

INSERT INTO cust2 FROM MEMVAR

SELECT CUST2
BROWSE

* Close and delete example table

USE
DELETE FILE [Link]

Atentie!!!
Ordinea valorilor in clauza VALUES trebuie sa fie identica cu cea declarata la
crearea (sau modificarea structurii) tabelelor. Modificarea este posibila
numai prin enumararea dupa numele tabelei, a atributelor ce vor primi valorile
specificate

B. STERGEREA UNOR LINII/INSCRIERI

Din HELP VFP
Marks records for deletion.
Syntax
DELETE
[Scope] [FOR lExpression1] [WHILE lExpression2]
 [IN nWorkArea | cTableAlias]
[NOOPTIMIZE]
Arguments
Scope
Specifies a range of records to mark for deletion. The scope clauses are: ALL, NEXT nRecords,
RECORD nRecordNumber, and REST.
The default scope for DELETE is the current record (NEXT 1).
FOR lExpression1
Specifies a condition whereby only the records that satisfy the logical condition lExpression1 are
marked for deletion.
Rushmore optimizes a query created with DELETE ... FOR if lExpression1 is an optimizable
expression and the table is indexed on DELETED( ). For best performance, use an optimizable
expression in the FOR clause.
WHILE lExpression2
Specifies a condition whereby records are marked for deletion for as long as lExpression2 evaluates
to true (.T.).
IN nWorkArea
Specifies the work area of the table in which records are marked for deletion.
IN cTableAlias
Specifies the alias of the table in which records are marked for deletion.
If you omit nWorkArea and cTableAlias, records are marked for deletion in the table in the currently
selected work area.
NOOPTIMIZE
Disables Rushmore optimization of DELETE.
Remarks
Records marked for deletion aren't physically removed from the table until PACK is issued. Records
marked for deletion can be recalled (unmarked) with RECALL.
DELETE Command Example din HELP VFP
The following example opens the customer table in the testdata database. DELETE is used to
mark all records for deletion where the country field contains USA. All the records marked for
deletion are displayed. RECALL ALL is used to unmark all the records marked for deletion.

CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'Data\testdata')
USE customer && Opens Customer table

DELETE FOR country = 'USA' && Mark for deletion

CLEAR
LIST FIELDS company, country FOR DELETED( ) && List marked records
RECALL ALL && Unmark all records marked for deletion
PROGRAM DE COMPLETARE CU DATE A BD [Link]
Set deafult to home()+ “\SQL_test\”
IF !DBUSED(‘xxxxxx’)
OPEN DATABASE xxxxxx EXCLUSIVE
ENDIF
DELETE FROM incasfact
SELE incasfact
ZAP
DELETE FROM incasari
SELE incasari
ZAP
DELETE FROM liniifact
SELE liniifact
ZAP
DELETE FROM facturi
SELE facturi
ZAP
DELETE FROM gestiuni
SELE gestiuni
ZAP
DELETE FROM produse
SELE produse
ZAP
DELETE FROM persclienti
SELE persclienti
ZAP
DELETE FROM persoane
SELE persoane
ZAP
DELETE FROM clienti
SELE clienti
ZAP
DELETE FROM localitati
SELE localitati
ZAP
DELETE FROM raioane
SELE raioane
ZAP
*********Inserare de date
INSERT INTO raioane VALUES (‘AN’,’Anenii Noi’, ‘Centru’)
INSERT INTO raioane VALUES (‘ST’,’Straseni’, ‘Centru’)
INSERT INTO raioane VALUES (‘CR’,’Criuleni’, ‘Centru’)
INSERT INTO raioane VALUES (‘IA’,’Ialoveni’, ‘Centru’)
INSERT INTO raioane VALUES (‘HI’,’Hincesti’, ‘Centru’)
INSERT INTO raioane VALUES (‘NI’,’Nisporeni’, ‘Centru’)
INSERT INTO raioane VALUES (‘SO’,’Soroca’, ‘Nord’)
INSERT INTO raioane VALUES (‘ED’,’Edinet’, ‘Nord’)
INSERT INTO raioane VALUES (‘BR’,’Briceni’, ‘Nord’)
INSERT INTO raioane VALUES (‘RE’,’Rezina’, ‘Nord’)
INSERT INTO raioane VALUES (‘CH’,’Cahul’, ‘Sud’)
INSERT INTO raioane VALUES (‘CA’,’Cantemir’, ‘Sud’)
INSERT INTO raioane VALUES (‘LE’,’Leova’, ‘Sud’)
INSERT INTO raioane VALUES (‘GA’,’UTAG’, ‘Sud’)

INSERT INTO localitati VALUES (‘2200’,’Anenii Noi’, ‘AN’)

INSERT INTO localitati VALUES (‘2300’,’Anenii Noi’, ‘AN’)
INSERT INTO localitati VALUES (‘2000’,’Straseni’, ‘ST’)
INSERT INTO localitati VALUES (‘2100’,’Straseni’, ‘ST’)
INSERT INTO localitati VALUES (‘2400’,’Criuleni’, ‘CR’)
INSERT INTO localitati VALUES (‘2500’,’Criuleni’, ‘CR’)
INSERT INTO localitati VALUES (‘2600,’Cantemir’, ‘CA’)
INSERT INTO localitati VALUES (‘2700’,’Ialoveni’, ‘IA’)
INSERT INTO localitati VALUES (‘3000’,’Hincesti’, ‘HI’)
INSERT INTO localitati VALUES (‘3200’,’Hincesti’, ‘HI’)
INSERT INTO localitati VALUES (‘3400’,’Edinet’, ‘ED’)
INSERT INTO localitati VALUES (‘3600’,’Cahul’, ‘CH’)

INSERT INTO clienti VALUES (1001,’Client 1 SRL’,’R1001’,’Cucima 13’,’2000’,NULL)

INSERT INTO clienti (codcl,dencl,codfiscal,codpost,telefon);
VALUES (1002,’Client 2 SRL’,’R1002’,’Cucima 13’,’2700’,’032-212121’)
INSERT INTO clienti VALUES (1003,’Client 1 SRL’,’R1003’,’Putin 13’,’2200’,NULL)
INSERT INTO clienti (codcl,dencl,codfiscal,codpost,telefon);
VALUES (1004,’Client 2 SRL’,’R1004’,’Cucima 13’,’2200’,’032-212121’)
INSERT INTO clienti VALUES (1005,’Client 1 SRL’,’R1005’,’Brejnev 13’,’2000’,NULL)
INSERT INTO clienti VALUES (1006,’Client 1 SRL’,’R1006’,’Ustinov 13’,’2200’,NULL)
INSERT INTO clienti VALUES (1007,’Client 1 SRL’,’R1007’,’Andropov 13’,’3400’,NULL)
INSERT INTO clienti VALUES (1008,’Client 1 SRL’,’R1008’,’Cosighin 13’,’2200’,NULL)

INSERT INTO persoane

VALUES (‘CNP1’,’Ion’, ‘Vasile’,’Sloboda
13’,’B’,’2200’,’123456’,’987654’,’094222222’,NULL)
INSERT INTO persoane
VALUES (‘CNP2’,’Chiron’, ‘Ileana’,’Sloboda
13’,’F’,’2200’,’123456’,’987654’,’094222222’,NULL)
INSERT INTO persoane
VALUES (‘CNP3’,’Vanea’, ‘Petru’,’Sloboda
13’,’B’,’2100’,’123456’,’987654’,’094222222’,NULL)
INSERT INTO persoane
VALUES (‘CNP4’,’Ghita’, ‘Valea’,’Sloboda
13’,’F’,’2000’,’123456’,’987654’,’094222222’,NULL)
INSERT INTO persoane
VALUES (‘CNP5’,’Tiron’, ‘Iulia’,’Sloboda
13’,’F’,’2700’,’123456’,’987654’,’094222222’,NULL)
INSERT INTO persoane
VALUES (‘CNP6’,’Papion’, ‘Nelea’,’Sloboda
13’,’F’,’2500’,’123456’,’987654’,’094222222’,NULL)
INSERT INTO persoane
VALUES (‘CNP7’,’Ghidon’, ‘Peste’,’Sloboda
13’,’B’,’3000’,’123456’,’987654’,’094222222’,NULL)

INSERT INTO persclient VALUES (‘CNP1’,1001,’Director general’)

INSERT INTO persclient VALUES (‘CNP2’,1002,’Director general’)
INSERT INTO persclient VALUES (‘CNP3’,1003,’Sef aprovizionare’)
INSERT INTO persclient VALUES (‘CNP4’,1004,’Sef aprovizionare’)
INSERT INTO persclient VALUES (‘CNP5’,1005,’Director financiar’)
INSERT INTO persclient VALUES (‘CNP6’,1006,’Director general’)
INSERT INTO persclient VALUES (‘CNP7’,1007,’Director financiar’)
INSERT INTO persclient VALUES (‘CNP8’,1008,’Sef aprovizionare’)

INSERT INTO produse (codpr,denpr,um,grupa,proctva);

VALUES (1,’Produs 1’,’buc’,’Tigari’,0.19)
INSERT INTO produse (codpr,denpr,um,grupa,proctva);
VALUES (2,’Produs 2’,’kg’,’Bere’,0.19)
INSERT INTO produse (codpr,denpr,um,grupa,proctva);
VALUES (3,’Produs 3’,’kg’,’Bere’,0.19)
INSERT INTO produse (codpr,denpr,um,grupa,proctva);
VALUES (4,’Produs 4’,’buc’,’Dulciuri’,0.19)
ADIR( ) Function Example
*includerea imaginilor in cimpurile GENERAL. !!!! In SQL nu exista astfel de posibilitati
The following example uses ADIR( ) to create an array
containing database information. The names of the databases
are then displayed.
SELECT Produse CLOSE DATABASES
GO TOP SET PATH TO (HOME(2) + 'Data')
gnDbcnumber = ADIR(gaDatabase, '*.DBC') && Create array
nrFisierTif=ADIR(A_fTifuri,”*.tif”) CLEAR
FOR nCount = 1 TO gnDbcnumber && Loop for number of
databases
? gaDatabase(nCount,1) && Display database names
ENDFOR
SET PATH TO HOME( ) && Set path to Visual FoxPro
directory
IF nrFisierTif>0
FOR I=1 TO nrFisierTif
APPEND GENERAL imagine FROM A_fTifuri(I,1)
SKIP
ENDFOR
ENDIF

INSERT INTO gestiuni

VALUES (‘001’,’Depozitul 1‘,’Soseaua Hincesti,
13’,’2200’,’CNP10’,’212121’,’xgest001@[Link]’)
INSERT INTO gestiuni
VALUES (‘002’,’Depozitul 2‘,’Soseaua Hincesti,
13’,’2300’,’CNP11’,’212121’,’xgest001@[Link]’)
INSERT INTO gestiuni
VALUES (‘003’,’Depozitul 3‘,’Soseaua Hincesti,
13’,’2700’,’CNP12’,’212121’,’xgest001@[Link]’)

INSERT INTO facturi (nrfact,datafact,gestiune,codcl)

VALUES(\ (1111,{^2001/08/01},’001’,1001)
INSERT INTO facturi (nrfact,datafact,gestiune,codcl)
VALUES(\ (1112,{^2001/08/01},’001’,1001)
INSERT INTO facturi (nrfact,datafact,gestiune,codcl)
VALUES(\ (1113,{^2001/08/01},’001’,1002)
INSERT INTO facturi (nrfact,datafact,gestiune,codcl)
VALUES(\ (1114,{^2001/08/01},’001’,1002)
INSERT INTO facturi (nrfact,datafact,gestiune,codcl)
VALUES(\ (1115,{^2001/08/01},’001’,1003)
INSERT INTO facturi (nrfact,datafact,gestiune,codcl)
VALUES(\ (1116,{^2001/08/01},’001’,1003)
INSERT INTO facturi (nrfact,datafact,gestiune,codcl)
VALUES(\ (1117,{^2001/08/01},’001’,1004)

INSERT INTO liniifact VALUES(\ (1111,1,1,50,10000)

INSERT INTO liniifact VALUES(\ (1111,2,2,75,10500)
INSERT INTO liniifact VALUES(\ (1111,3,5,500,6500)
INSERT INTO liniifact VALUES(\ (1112,1,2,80,10300)
INSERT INTO liniifact VALUES(\ (1112,2,3,40,7500)
INSERT INTO liniifact VALUES(\ (1113,1,2,100,9750)
INSERT INTO liniifact VALUES(\ (1113,1,2,70,10700)
INSERT INTO liniifact VALUES(\ (1113,2,4,30,15800)
INSERT INTO liniifact VALUES(\ (1114,3,5,700,10000)
INSERT INTO liniifact VALUES(\ (1114,1,2,150,9300)
INSERT INTO liniifact VALUES(\ (1114,1,2,125,9300)
INSERT INTO liniifact VALUES(\ (1115,1,2,100,9500)
INSERT INTO liniifact VALUES(\ (1116,2,1,150,6400)
INSERT INTO liniifact VALUES(\ (1117,1,2,35,10500)

INSERT INTO incasari VALUES(\ (1234,{^2001/08/15},’OP’,’111’,{^2000/08/10})

INSERT INTO incasari VALUES(\ (1235,{^2001/08/15},’CHIT’,’222’,{^2000/08/15})
INSERT INTO incasari VALUES(\ (1236,{^2001/08/16},’OP’,’333’,{^2000/08/09})
INSERT INTO incasari VALUES(\ (1237,{^2001/08/17},’CEC’,’444’,{^2000/08/10})
INSERT INTO incasari VALUES(\ (1238,{^2001/08/17},’OP’,’555’,{^2000/08/10})
INSERT INTO incasari VALUES(\ (1239,{^2001/08/18},’OP’,’666’,{^2000/08/11})

INSERT INTO incasfact VALUES(\ (1234,1111,5399625)

INSERT INTO incasfact VALUES(\ (1234,1118,5399625)
INSERT INTO incasfact VALUES(\ (1235,1112,5399625)
INSERT INTO incasfact VALUES(\ (1235,1117,5399625)
INSERT INTO incasfact VALUES(\ (1236,1118,5399625)
INSERT INTO incasfact VALUES(\ (1236,1120,5399625)
INSERT INTO incasfact VALUES(\ (1237,1117,5399625)
INSERT INTO incasfact VALUES(\ (1238,1113,5399625)
INSERT INTO incasfact VALUES(\ (1239,1117,5399625)
Atentie!!!
In exemplul de mai sus am pornit de la presupunerea ca in directoriul current exista cite un fisier
graphic cu extensia tif pentru fiecare produs, iar ordinea acestora coiencide cu ordinea produselor din
tabela, ceea ce e destul de riscant. Astfel incit procedura ce urmeaza pare a fi una mai adecvata.

SELECT PRODUSE
SCAN
Nume_=’figura_’+LTRIM(STR(codpr,6))+’.TIF’
IF FILE((nume_))
APPEND GENERAL image FROM (nume_) LINK
ENDIF
ENDSCAN
C. MODIFICAREA VALORILOR UNOR ATTRIBUTE
Updates records in a table with new values.
Syntax
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.
UPDATE - SQL Command Example din HELP VFP
The following example opens the customer table in the testdata database. UPDATE -SQL is
used to set all of the values in the maxordamt field to 25.
CLOSE DATABASES

OPEN DATABASE (HOME(2) + 'Data\testdata')

USE Customer && Open customer table

* Set and display amounts for customers

UPDATE customer SET maxordamt = 25
BROWSE FIELDS company,maxordamt

Exemple cu referinta la BD creata mai sus

Remarca
Modificarea se produce pe toate liniile tabelei care indeplinesc conditia formulata prin
predicat.
De exemplu
Noul numar de telefon al clientului ce are codul 1001 este 032-313131. Sa se opereze
modificarea in baza de date

UPDATE CLIENT;
SET telefon =’032-313131’;
WHERE codcl=1001

Modificarea cresterii TVA pentru toate produsele de la 0.19 la 0.22 se va efectua cu

ajutorul comenzii

UPDATE PRODUSE;
SET procTVA=0.22;

5. INTEROGAREA BAZELOR DE DATE – COMANDA SELECT

Syntaxa

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] ...]]
Exemple din HELP VFP
SELECT - SQL Command Examples
The following examples illustrate the use of user-defined functions with SELECT - SQL:
Example 1
Example 1 displays the names of all companies in customer (one field from one table).
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT [Link] ;
FROM customer
Example 2
Example 2 displays the contents of three fields from two tables and joins the two tables based on the
cust_id field. It uses local aliases for both tables.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT [Link], b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id
Example 3
Example 3 displays only records with unique data in the specified fields.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT DISTINCT [Link], b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id
Example 4
Example 4 displays the country, postalcode, and company fields in ascending order.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT country, postalcode, company ;
FROM customer ;
ORDER BY country, postalcode, company
Example 5
Example 5 stores the contents of fields from two tables in a third table.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT [Link], b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id ;
INTO TABLE [Link]
BROWSE
Example 6
Example 6 displays only records with an order date earlier than 02/16/1994.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT [Link], b.order_date, b.shipped_on ;
FROM customer a, orders b ;
WHERE a.cust_id = b.cust_id ;
AND b.order_date < {^1994-02-16}
Example 7
Example 7 displays the names of all companies from customer with a postal code that matches a
postal code in the orders table.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT company FROM customer a WHERE ;
EXISTS (SELECT * FROM orders b WHERE [Link] = [Link])

Example 8
Example 8 displays all records from customer having a company name that begins with an
uppercase C and is an unknown length.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer a WHERE [Link] LIKE "C%"
Example 9
Example 9 displays all records from customer having a country name that begins with an uppercase
U and is followed by one unknown character.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer a WHERE [Link] LIKE "U_"
Example 10
Example 10 displays the names of all cities in customer in uppercase and names the output column
CityList.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT UPPER(city) AS CityList FROM customer
Example 11
Example 11 demonstrates how you can perform a query on data that contains percentage signs (%).
A backslash (\) is placed before the percentage sign to indicate that it should be treated as a literal,
and the backslash is specified as the escape character in the ESCAPE clause.
Because the sample tables included with Visual FoxPro do not contain the percentage sign character,
this query returns no results.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%\%%" ESCAPE "\"
Example 12
Example 12 demonstrates how you can perform a query on data that contains underscores (_). A
backslash (\) is placed before the underscore to indicate that it should be treated as a literal, and the
backslash is specified as the escape character in the ESCAPE clause.
Because the sample tables included with Visual FoxPro do not contain the underscore character, this
query returns no results.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%\_%" ESCAPE "\"
Example 13
In example 13, the Escape character uses itself as a literal. The dash is both the escape character and
a literal. The query returns all rows where the company name contains a percentage sign followed by
a dash.
Because the sample tables included with Visual FoxPro do not contain the percentage sign character,
this query returns no results.
CLOSE ALL
CLOSE DATABASES
OPEN DATABASE (HOME(2) + 'data\testdata')
SELECT * FROM customer;
WHERE company LIKE "%-%--%" Escape "-"
Exemple cu referinta la Structura BD create in primul punct

Exemple cu referinta la BD creata mai sus

Comanda SELECT respecta in VfP sintaxa de baza din standardul. SQL.92, cele trei clauze
principale fiind SELECT, FROM si WHERE, la care se adauga ORDER BY, GROUP BY,
HAVING si alte elemente legate de subinterogari/subconsultari. De asemenea, VFP utilizeaza si
jonctiunea interna INNER JOIN si extema OUTER JOIN.

Exemplul 1
Presupundnd ca plata se face la 14 zile, care sunt facturile ce urmeaza a fi incasate in perioada
15-18 august 2001 ?

Solutia 1.1

SELECT DISTINCT NrFact, DataFact, DataFact + 14 AS Scadenta ;

FROM Facturi ;
WHERE DataFact + 14 >= {^200l/08/15} AND ;
DataFact+l4 <= {"2001/08/18}

Solutia l.2 - folosind propozitia BETWEEN:

SELECT DISTINCT NrFact, DataFact, DataFact+ 14 AS Scadenta ;

FROM Facturi;
WHERE DataFact + 14 BETWEEN {^2001/08/15} AND {"2001/08/18}

Exemplul 2
Care sunt clienlii din din Anenii Noi si Criuleni?

Solutia 2.1 :

SELECT CLIENTI.* ;
FROM CLIENTI INNER JOIN LOCALITATI ;
ON [Link] = [Link] ;
WHERE Loc=’Anenii Noi’ OR Loc=’Criuleni’

Solutia 2.2- folosind operatorul IN:

SELECT CLIENTI.* ;
FROM CLIENTI INNER JOIN LOCALITATI ;
ON [Link] = [Link] ;
WHERE Loc IN ('Anenii Noi', 'Criuleni')

Solutia 2.3 -vechea sintaxa din SQL-89:

SELECT CLIENTI.* ;
FROM CLIENTI, LOCALITATI ;
WHERE [Link] = [Link] AND Loc ;
IN ('Anenii Noi', 'Criuleni')

Solutia 2.4- sintaxa SQL-89, cu aliasuri pentru tabele:

SELECT C.* ;
FROM CLIENTI C, LOCALITATI L ;
WHERE [Link] = [Link] AND ;
Loc IN ('Anenii Noi', 'Criuleni')

Solutia 2.5 - bazat pe subinterogari/subconsultari

SELECT * ;
FROM [Link] ;
WHERE CodPost IN ;
(SELECT CodPost ;
FROM LOCALITATI ;
WHERE Loc IN ('Anenii Noi', 'Criuleni');
)

Exemplul 3
Ce persoane cheie trebuie felicitate de Sfintul ION?

Solutia 3.1 folosind operatorul LIKE

SELECT dencl, functie, p.*;
FROM persoane P INNER JOIN persclienti pc;
ON [Link]=[Link];
INNER JOIN clienti c
ON [Link]=[Link];
WHERE UPPER(Presume) LIKE ‘ION&’ OR;
UPPER(Presume) LIKE ‘ION&’ OR;
UPPER(Presume) LIKE ‘% ION&’ OR;
UPPER(Presume) LIKE ‘% IOAN&’ OR;
UPPER(Presume) LIKE ‘%-ION&’ OR;
UPPER(Presume) LIKE ‘%-IOAN&’

Exemplul 4
Ce persoane chepe de la firmele client un au e-mail?

Solutia 4.1 folosind operatorul IS NULL

SELECT dencl, functie, p.*;
FROM persoane P INNER JOIN persclienti pc;
ON [Link]=[Link];
INNER JOIN clienti c
ON [Link]=[Link];
WHERE email IS NULL
Exemplul 5
Care sunt facturile emise in aceeasi zi ca si factura 1113?

Solutia 5.1 bazata pe subinterogare/subconsultare

SELECT *;
FROM facturi;
where DataFact IN ;
(SELECT DataFact;
FROM facturi;
WHERE NrFact=1113)

Solutia 5.2 bazata pe jonctiunea interna

SELECT [Link], [Link];
FROM facturi F1 INNER JOIN facturi F2;
ON [Link]=[Link] AND [Link]=1113

Solutia 5.3 folosind o interogare corelata

SELECT *;
FROM facturi F1;
where EXISTS ;
(SELECT F1;
FROM facturi F2;
WHERE [Link]=[Link] AND [Link]=1113)

Exemplul 6
Care dintre persoanele din tabela cu acelasi nume un au functii la firmele client?

Returns a non-null value from two expressions.

Solutia 6.1 bazata pe subinterogare/subconsultare Syntax
SELECT P.*; NVL(eExpression1, eExpression2)
Returns
FROM persoane P; Character, Date, DateTime, Numeric, Currency,
where cnp NOT IN ; Logical, or the null value
Arguments
(SELECT CNP; eExpression1, eExpression2
FROM persclienti) NVL( ) returns eExpression2 if eExpression1
evaluates to a null value. NVL( ) returns
eExpression1 if eExpression1 is not a null value.
Solutia 6.2 bazata pe jonctiunea externa eExpression1 and eExpression2 may be any data
type. NVL( ) returns .NULL. if both
SELECT *; eExpression1 and eExpression2 both evaluate to
FROM persoane P LEFT OUTER JOIN persclienti PC ; the null value.

ON [Link]=[Link];
WHERE NVL([Link],0)=0

Exemplul 7
/Functii agregat cu si fara grupare/
La citi clienti sau trimis facturi?

Solutia 7.1 bazata pe functia COUNT si o subinterogare/subconsultare

SELECT COUNT(*);
FROM clienti;
where codcl IN ;
(SELECT codcl;
FROM facturi)
Solutia 7.2 bazata pe functia COUNT si clauza DISTINCT
SELECT COUNT(DISTINCT codcl);
FROM facturi

Exemplul 8
Care este valoarea totala a facturii 1119, valoarea medie, maxima si minima a produselor vindute
pe aceasta factura?

SELECT SUM(cantitate*pretunit*(1+procTVA) AS Val_Totala;

AVG(cantitate*pretunit+ cantitate*pretunit*procTVA) AS Val_Medie;
MIN(cantitate*pretunit+ cantitate*pretunit*procTVA) AS Val_Minima;
MAX(cantitate*pretunit+ cantitate*pretunit*procTVA) AS Val_Maxima;
FROM produse P INNER JOIN linii Fac. LF;
ON [Link]=[Link]

Exemplul 9
Care este valoarea totald a vinzdrilor pentru fiecare zi in care s-au emis facturi :
Pentru rezolvarea problemei este obligatorie folosirea gruparii

SELECT DataFact, SUM(Cantitate * PretUnit * (l+ProcTVA)) AS ValTotala ;

FROM LINIIFACT LF, PRODUSE P, FACTURI F ;
WHERE [Link] = [Link] AND [Link] = [Link] ;
GROUP BY DataFact

Exemplul 10
Care sunt vtinzarile, cantitativ si valoric, pentru fiecare produs ?

SELECT DenPr, UM, SUM(Cantitate) AS Cantitativ, ;

SUM(Cantitate * PretUnit * (l+ProcTVA)) AS Valoric ;
FROM LINIIFACT LF, PRODUSE P, FACTURI F ;
.WHERE [Link] = [Link] AND [Link] = [Link] ;
GROUP BY DenPr, UM

Exemplul ll
Sa se oblina situalia vanzarilor pe clienli si zile, afisindu-se cate un subtotal la nivel , client si un
total general.
Varianta propusa, reuneste multimea facturilor propriu-zise, obtinuta din prima fraza SELECT, cu
multimea subtotalurilor la nivel de client (al doilea SELECT) si cu o linie ce reprezintA totalul
general.

SELECT PADR(ALLT(dencl),40) As DenumireClient, ;

datafact,;
SUM(Cantitate * PretUnit * (l+ProcTVA)) AS Vinzari ;
FROM liniifact LF, produse P, facturi F, clienti C;
WHERE [Link]=[Link] AND [Link]=[Link];
AND [Link]=[Link];
GROUP BY dencl, datafact;
UNION;
SELECT PADR(ALLT(dencl) +’-Subtotal’,40), { / / },;
SUM(Cantitate * PretUnit * (l+ProcTVA));
FROM liniifact LF, produse P, facturi F, clienti C;
WHERE [Link]=[Link] AND [Link]=[Link];
AND [Link]=[Link];
GROUP BY dencl, datafact;
UNION;
SELECT CHR(255)+’Total General’, { / / },;
SUM(Cantitate * PretUnit * (l+ProcTVA));
FROM liniifact LF, produse P;
WHERE [Link]=[Link]

Exemplul 12
Care sunt zilele in care sau intocmit cel putin 3 facturi?

SELECT datafact, COUNT(*) AS Nr_Facturi;

FROM facturi;
GROUP BY datafact;
HAVING COUNT(*)>=3

Exemplul 13
In ce zile s-au vandut si produsul cu denumirea "Produs 1 " si cel cu denumir "Produs 2" ?
Desi are un enunt banal, rezolvarea acestei probleme ofera oportunitatea unor interogari dintre cele
mai spumoase. Va prezentam numai cinci dintre acestea.

.Solutia 13.1.- jonctionarea unei instante obtinuta prin jonctiunea PRODUSE-LINIIFACT-

FACTURI (In care DenPr = 'Produs 1 ') cu o alta instanta aceleiasi combinatii (In care DenPr =
'Produs 2'):

SELECT DISTINCT [Link] ;

FROM PRODUSE Pl ;
INNER JOIN LINIIFACT LF1;
ON [Link] = [Link] ;
INNER JOIN FACTURI Fl ;
ON [Link] = [Link] ;
INNER JOIN FACTURI F2;
ON [Link]=F2,DataFact ;
INNER JOIN LINIIFACT LF2;
ON LF2..Nrfact= [Link] ;
INNER JOIN PRODUSE P2;
[Link] = [Link] ;
WHERE [Link] = 'Produs l' AND [Link] = 'Produs 2'

.Solutia 13.2- folosind clauza HAVING si functia COUNT:

SELECT DISTINCT DataFact ;

FROM PRODUSE INNER JOIN LINIIFACT ;
ON PRODUSE. CodPr = LINIIFACT. CodPr ;
INNER JOIN FACTURI ;
ON [Link] =[Link];
WHERE DenPr;
IN ('Produs l',’Produs 2') I;
GROUP BY DataFact ;
HAVING COUNT(DISTINCT [Link]) = 2

Solutia 13.3- simuland intersectia prin subconsultari:

SELECT DISTINCT DataFact ;

FROM PRODUSE INNER JOIN [Link] ;
ON [Link] = [Link] ;
INNER JOIN FACTURI
ON [Link] = [Link]
WHERE DenPr = 'Produs l' AND DataFact IN ;
(SELECT DISTINCT DataFact ;
FROM PRODUSE ;
INNER JOIN LINIIFACT;
ON [Link] = [Link] ;
INNER JOIN FACTURI;
ON [Link] = [Link] ;
WHERE DenPr = 'Produs 2')

Solutia 13.4 -prin corelarea a doua instante ale jonctiunii PRODUSE-LINIIFACT - FACTURI;
prima contine liniile legate de Produs I, iar a doua de Produs 2:

SELECT DISTINCT DataFact ;

FROM PRODUSE Pl, LINIIFACT LF1, FACTORI Fl ;
WHERE [Link] = [Link] AND ;
[Link] =[Link] AND DenPr = ‘Produs 1 ' ;
AND EXISTS;
(SELECT 1;
FROM PRODUSE P2, LINIIFACT LF2, FACTUEI F2 ;
WHERE [Link] = [Link] AND Lf2;Nrfact = [Link];
AND [Link]= 'Produs 2' AND [Link]=[Link])

Solutia 13.5:

SELECT DISTINCT DataFact ;

FROM FACTURI ;
INNER JOIN LINIIFACT
ON [Link]=[Link] ;
INNER JOIN PROPUSE
ON [Link]=[Link] AND DenPr IN ('Produs .1', 'Produs 2') ;
WHERE DTOC(DataFact)+DTOC({ / / }) NOT IN ;
(SELECT DISTINCT ;
DTOC([Link])+DTOC(NVL([Link],{ / / })) ;
FROM FACTURI Fl INNER JOIN PRODUSE P1 ;
ON [Link] IN ('Produs 1', 'Produs 2') ;
LEFT OUTER JOIN ([Link] LF2 INNER JOIN FACTURI F2 ;
]N LF2 .NrFact:F2 .NrFaCt )
ON F1. DataFact=F2 .DataFact AND [Link]=LF2 .CodPr)

Exemplul 14
Afisarea pe coloane separate, pentru cele trei gestiuni, a facturilor necesita folosirea unei
secvente de IF-uri immediate (IIF-uri):
SELECT IIF(gestiune = '001', STR([Link],8), space{8)) ;
AS Gestiune 001, ;
IIF(gestiune ='002’, STR{[Link],8), space{8)) ;
AS Gestiune 002, ;
IIF(gestiune ='003’, STR{[Link],8), space{8)) ;
AS Gestiune 003, ;
DataFact, SUM(Cantitate * PretUnit * (l+ProcTVA)) ;
AS ValTotala ;
FROM FACTURI F ;
INNER JOIN LINIIFACT LF
ON [Link] = [Link] ;
INNER JOIN PRODUSE P
ON [Link] = [Link] ;
GROUP BY [Link]

Exemplul 15
Scadenla fiecarei facturi emise este de 20 de zile. Daca insa data-limita cade intr-o sambata sau
duminica, atunci scadenla se muta in lunea urmatoare. Care sunt zilele, scadente in aceste
condilii ?

Visual FoxPro prezinta functiile CDOW (Character Day Of the Week) si DOW (Day of the Week).In
acest caz putem obtine urmatorul rezultat.
SELECT NrFact AS Factura, DataFact, ;
DataFact + 20 AS Scadental, ;
CDOW(DataFact+20) AS NumeZil, ;
IIF(DOW(DataFact+20)=6, ;
DataFact+22, ;
IIF(DOW(DataFact+20)=1, ;
DataFact+21, ;
DataFact+20) ) AS Scadenta,;
CDOW(IIF(DOW(Datafact+20)=6,;
DataFact+22, ;
IIF(DOW(DataFact+20)=1, ;
DataFact+21,;
DataFact+20) ) ) AS ZI_Scadenta ;
FROM FACTURI

Exemplul 16
Care sunt valorile facturate si incasate ale fieciirei facturi ?
Solutia de mai jos, reune~te facturile care au macar o transa de incasare cu cele nelncasate deloc:

SELECT F. NrFact, ;
SUM(Cantitate *PfretUnit * (l+ProcTVA).) / ;
COUNT (DISTINCT I..CodInc) AS Facturat, ;
SUM(Transa) / MAX([Link]) AS Incasat ;
FROM LINIIFACT LF, PRODUSE P, FACTURI F, INCASFACT I ;
WHERE [Link] = [Link] AND [Link] = [Link] ;
AND [Link]=[Link] ;
GROUP BY [Link] ;
UNION ;
SELECT F. NrFact, ;
SUM(Cantitate * P+PretUpit * (l+ProcTVA)) AS Facturat,
0 AS Incasat ;
FROM LINIIFACT LF, PRODUSE P, FACTURI F ;
WHERE [Link] = [Link] AND [Link] = [Link] ;
AND [Link] NOT IN ;
(SELECT NrFact ;
F'ROM INCASFACT) ;
GROUP BY [Link]

Clauza TOP. Operatorii ALL si ANY (SOME)

Exemplul 17
Care sunt cele mai mari cinci preluri unitare de vanzare, produsele si facturile in care apar cele
cinci preluri maxime?

In alte SGBD-uri, aceasta problema ar crea mari dureri de cap. Clauza TOP din VFP realizeaza
ordonarea si extragerea celor cinci valori relative simplu )

SELECT TQP 5 NrFact, DenPr, PretUnit ;

FROM LINIIFACT INNER JOIN PRODUSE ;
ON [Link]=[Link];
ORDER BY PretUnit DESC

Exemplul 18
Care sunt produsele vandute la preturi unitare superioare oricarui pret unitar la carea fost
vandut 'Produs I' ?
Unul dintre obiectivele acestui exemplu este de a prezenta modul de conexiune a unei consultari cu
subconsultarea sa prin operatorul ALL.

SELECT DISTINCT DenPr, PretUnit ;

FROM PRODUSE P, LINIIFACT LF ;
WHERE [Link]=[Link] AND Pretunit > ALL ;
(SELECT DISTINCT PretUnit;
FROM PRODUSE P, LINIIFACT LF;
WHERE [Link]=[Link] AND DenPr ='Produs 1'

Exemplul 19
Care sunt produsele vandute la preluri unitare superioare macar unui prel unitar al 'Produsului l'
?

Este genul de situatii in care se foloseste SOME sau ANY (au aceeasi functiune).

SELECT DISTINCT DenPr, PretUnit ;

FROM RODUSE P INNER JO1N LINIIFACT LF ,
ON [Link]=[Link] ;
WHERE PretUnit > ANY;
(SELECT DISTINCT PretUnit ;
FROM PRODUSE P INNER JOIN LINIIFACT
ON [Link]=[Link] ;
WHERE DenPr ='Produs 1')
Exemplul 20
Care este ultima factura intocmita /factura cea mai recenta/ si data in care a fost remisa ?
Aceasta este o problema cu multe variante de rezolvare

Solutie 20.1
SELECT DataFact, NrFact AS Ultima_Factura ;
FROM facturi;
WHERE NrFact IN;
(SELECT MAX(Nrfact);
FROM facturi)

Solutie 20.2 in locul operatorului de conexiune IN este utilizat semnul =

SELECT DataFact, NrFact AS Ultima_Factura ;

FROM facturi;
WHERE NrFact =;
(SELECT MAX(Nrfact);
FROM facturi)

Solutie 20.3 in locul operatorului de conexiune IN este utilizat operatorul ANY

SELECT DataFact, NrFact AS Ultima_Factura ;

FROM facturi;
WHERE NrFact = ANY;
(SELECT MAX(Nrfact);
FROM facturi)

Solutie 20.4 in locul operatorului de conexiune IN este utilizat operatorul ALL

SELECT DataFact, NrFact AS Ultima_Factura ;

FROM facturi;
WHERE NrFact = ALL;
(SELECT MAX(Nrfact);
FROM facturi)

Solutie 20.5 in locul operatorului de conexiune IN este utilizat operatorul >=ALL

SELECT DataFact, NrFact AS Ultima_Factura ;

FROM facturi;
WHERE NrFact = ALL;
(SELECT Nrfact;
FROM facturi)

Solutie 20.6 Se utilizeaza clauza TOP

SELECT TOP 1 DataFact, COUNT(*) AS Nr ;

FROM facturi;
GROUP BY DataFact;
ORDER BY Nr DESC

Exemplul 21
Care este gestiunea cu vinzarile cele mai bune ?
Daca in alte SGBD-uri sunt necesare subconsultari in clauza FROM, in VFP, aceasi clauza TOP
salveaza totul!

SELECT TOP 1 Gestiune, SUM(Cantitate * PretUnit * (l+ProcTVA)) AS Vinzari_Gestiuni ;

FROM FACTURI F ;
INNER JOIN LINIIFACT LF
ON [Link]=[Link] ;
INNER JOIN PRODUSE P
ON [Link]=[Link] ;
GROUP BY Gestiune ;
ODER BY Vinzari_Gestiuni DESC

6. Includerea comenzii SELECT in programe

Multe din necesitatile VFP necesita folosirea in SQL a mai multor nivele de subconsultare in mod
deosebit in propozitiile FROM sau HAVING. Aceste facilitate nucleul SQL in VFP nu le are in
deplina masura si putere.
In schim in VFP aceste rezultate se pot crea ca rezultate intermediare stocate in CURSOARE sau
TABELE virtuale care la rindul lor pot fi utilizate ca argumente in alte noi consultari.

Salvarea unei operatii SELECT intr-un CURSOR

CURSORUL este in VFP o tabelea temporara care exista din momentul formarii comenzii SELECT
cu clauza INTO CURSOR sau CREATE CURSOR si pina la includerea sa implicita sau explicita

Sa revenim la Exemplul 13.

In ce zile sau vindut si produsul cu denumirea Produs 1 si produsul cu denumirea Produs 2

La seria interogarilor care au fost prezentate deja mai adaugam si o varianta simpla de program dupa
cum urmeaza

Programe VFP ce salveaza rezultatele intermediare ale interogarilor in cursoare

SELECT DISTINCT DataFact ;

INTO CURSOR Zile_P1 ;
FROM PRODUSE INNER JOIN LINIIFACT ON [Link] = [Link] ;
INNER JOIN FACTURI ON [Link] = [Link] ;
WHERE DenPr = 'Produs l'

SELECT DISTINCT DataFact ;

INTO CURSOR Zile_P2 ;
FROM PRODUSE INNER JOIN LlNIIFACT ON [Link] = [Link] ;
INNER JOIN FACTURI ON [Link] = [Link] ;
WHERE DenPr = 'Produs 2'

SELECT *;
FROM Zile_P1
WHERE DataFact IN
(SELECT DataFact;
FROM ZILE_P2)
Prima interogare creaza cursoul Zile_P1 si contine numai zilele in care s-a vindut primul produs.
Zile_P2 este cursorul ce tine de zilele cind s-a vindut al doilea produs.
Ultima interogare contine intersectia celor doua cursoare ce rezolva problema

Exemplul 22
Care este raionul cu vinzari imediat superioare raionului Anenii Noi ?
Interesul este aici ca in WHERE are loc jonctiunea care solutioneaza cazul

**** Vinzarile raionului Anenii Noi

SELECT SUM(Cantitate * PretUnit * (1 +ProcTVA)) AS Vinzari ;
FROM raioane R ;
INNER JOIN LOCALITATI L ON [Link]=[Link] ;
INNER JOIN CLIENTI C ON [Link]=[Link] ;
INNER JOIN FACTURI F ON [Link]=[Link] ;
INNER JOIN LINIIFACT LF ON [Link]=[Link] ;
INNER JOIN PRODUSE P ON [Link]=[Link] ;
INTO CURSOR cVinzari_Aneni_Noi WHERE raion='Anenii Noi'

**** Vinzarile fjecarui raion

SELECT raion, SUM(Cantitate .PretUnit .(1+ProcTVA)) AS Vinzari ;
FROM raioane R ;
INNER JOIN LOCALITATI L ON [Link]=[Link] ;
INNER JOIN CLIENTI C ON [Link]=[Link] ;
INNER JOIN FACTURI F ON [Link]=[Link] ;
INNER JOIN LlNIIFACT LF ON [Link]=[Link] ;
INNER JOIN PRODUSE P ON [Link]=[Link] ;
INTO CURSOR cVinzari_Raioane GROUP BY raion
SELECT raion, Vinzari ;
FROM cVinzari_Raioane;
WHERE Vinzari <= ALL ;
(SELECT cVinzari_Raioane.Vinzari ;
FROM cVinzari_Raioane, cVinzari_Anenii_Noi ;
WHERE cVinzari_Raioane.Vinzari > cVinzari_Anenii_Noi.Vinzari) ;
AND Vinzari > ALL ;
(SELECT Vinzari ;
FROM cVinzari_Anenii_Noi)

7. Alte consideratii privind cursoarele

Un cursor poate ti creat si prin comanda CREATE CURSOR, caz in care tabela temporara este
actualizabila. Formatul comenzii prezinta clauze similare CRE
Syntaxa
CREATE CURSOR alias_name
(fname1 type [(precision [, scale])
[NULL | NOT NULL]
[CHECK lExpression [ERROR cMessageText]]
[DEFAULT eExpression]
[UNIQUE]
[NOCPTRANS]]
 [, fname2 ...])
| FROM ARRAY ArrayName
Din HELP VFP
The following example creates a cursor with the alias employee. A blank record is appended, filled, and displayed
with the BROWSE command.
CLOSE DATABASES
CLEAR
CREATE CURSOR employee ;
(EmpID N(5), Name C(20), Address C(30), City C(30), ;
PostalCode C(10), OfficeNo C(8) NULL, Specialty M)
DISPLAY STRUCTURE
WAIT WINDOW "Press a key to add a record."

INSERT INTO employee (EmpId, Name, Address, City, PostalCode, ;

OfficeNo, Specialty);
VALUES (1002, "Dr. Bonnie Doren", "University of Oregon", "Eugene", ;
"98403", "", "Secondary Special Education")
BROWSE

* At this point you could copy this record to a permanent table

CLOSE ALL && Once the cursor is closed, all data is flushed
&& from memory
CLEAR

O data creat si declarate restrictiile, un cursor poate fi actualizat ca orice tabela obisnuita, prin
comenzile SQL: INSERT, UPDATE si DELETE, precum si orice alta comanda de editare specifica
VFP. De asemenea, orice incalcare a vreunei restrictii definite va fi sernnalizata. Cursoarelor, similar
tabelelor virtuale, li se pot vizualiza si configura proprietatile prin functiile CURSORGETPROP( ) si
CURSORSETPRQP().
Continutul unui cursor poate fi adaugat la inregistrarile curente ale unei tabele prin comanda
APPEND FROM DBF ( '<NumeCursor>' ) .

Cursoarele pot fi, in unele cazuri un bun inlocuitor al vectorilor.

Exemplu. Este stiut ca agentilor care achita bine platile si impozitele li se acorda anumite facilitati,
de exemplu anumite reduceri la plati.

Program de calcul al reducerilor acordate pentru incasari si anume pentru transele de facturi
platite /[Link]/
in termen de pina la 10 zile se acorda o reducere de 10%
intre 11 si 12 zile se acorda o reducere de 9%
intre 13 si 15 zile se acorda o reducere de 8%
peste 16 zile nu se acorda nici o favaore /in realitate se acorda penalizari/
Transele acordate a reducerilor pot fi stocate in cursoare si astfel folosite la interogari.

* se creaza si introduce datele in cursorul cu transele de reduceri

CREATE CURSOR transe_red ( ;

zile_min INTEGER, ;
zile_max INTEGER, ;
procent INTEGER ;
)
INSERT INTO transe_red VALUES ( 0, 10, 10)
INSERT INTO transe_red VALUES (11, 12, 9)
INSERT INTO transe_red VALUES (13, 15, 8)
INSERT INTO transe_red VALUES (16,99999, 0)

*pentru fiecare factura cu valoare peste zero se insereaza

*in cursorul FACT_TRANSE cite o linie pentru fiecare transa
*posibila de reduceri

SELECT NrFact, DataFact, ValTotala, DataFact + zile_min AS Liminf,

DataFact + zile_max AS LimSup, procent ;
FROM FACTURI, TRANSE_RED
INTO CURSOR FACT_TRANSE ;
WHERE procent > 0 AND ValTotala > 0

* se calculeaza reducerile pe fiecare transa

SELECT FACT_TRANSE.*, Datalnc, Transa, INT(Procent * Transa 1100) AS Reducere ;

INTO CURSOR reduceri_transe ;
FROM INCASFACT INNER JOIN INCASARI ON [Link]=[Link] ;
INNER JOIN FACT_TRANSE ;
ON [Link] = FACT_TRANSE.NrFact ;
WHERE [Link] >= FACT_TRANSE.Liminf AND ;
[Link] <= FACT_TRANSE.LimSup

* se actualizeaza atributul [Link]

DIME suma_(1, 1)
SELECT facturi
SCAN
Suma_ = 0
SELECT SUM(Reducere) ;
INTO ARRAY suma_;
FROM REDUCERI- TRANSE ;
WHERE NrFact = [Link]
SELECT facturi
REPLACE Reduceri WITH suma_(1 , 1 )
ENDSCAN

Este o solutie nu prea simpla dar operationala!!! Aici pielea costa cit dubala!

8. Variante pentru utilizarea interogarilor

Exemplu.
De listat facturile emise catre un client pentru un anumit interval de timp definit de o data initiala si
una finala.
Programul ce urmeaza este o procedura si la ea se poate de adresat cu parametri

PROCEDURE listare
PARAMETER codcl_, datainf_,datasup_
SELECT DenCI AS Client, Nrfact, DataFact, Gestiune, ValTotala AS Valoare;
FROM clienti C LEFT OUTER JOIN facturi F
ON [Link]=[Link];
WHERE [Link]=codcl_ AND datafact BETWEEN datainf_ AND datasup_
RETURN
ENDPROC
Pentru a obtine lista clientului 1 /cod 1001/ in perioada 2-7 august 2001 apelam la procedura scrisa
dupa cum urmeaza

DO listare WITH 1001,{^2001/08/02},{^2001/08/07}

Varianta ce urmeaza permite afisarea pe ecran

PROCEDURE listare
PARAMETER codcl_, datainf_,datasup_
SELECT DenCI AS Client, Nrfact, DataFact, Gestiune, ValTotala AS Valoare;
INTO CURSOR c1 ;
FROM clienti C LEFT OUTER JOIN facturi F
ON [Link]=[Link];
WHERE [Link]=codcl_ AND datafact BETWEEN datainf_ AND datasup_
Titlu_=’Lista facturilor pentru clientul’+ALLTRIM([Link])+’in perioada’+ ;
DTOC(datainf_)+’-‘+DTOC(datasup_)

SELECT C1
BROWSE TITLE Titlu_
RETURN
ENDPROC

Important este ca in asa mod obtinem rezultatele fara a incarca memoria cu “Gunoi” adica tabele in
plus

9. Macrosubstitutia in fraze SELECT

Sa presupunem ca o aceeasi lista - a facturilor ce contine urmatoarele date:

- numarul facturii,
- data emiterii,
- numele clientului,
- localjtatea in care-si are sediul c1ientul,
- valoarea incasata pana in momentul curent -
trebuie obtinuta pentru un anumit interval calendaristic, in orice ordine declarata prin toate
combinatiile posibile ale celor cinci cimpuri

Iata o varianta de program

PROCEDURE listare1
PARAMETER datainf_,datasup_,cimp1_,cimp2_,cimp3_,cimp4_
SELECT Dencl AS Client, Loc As Localitate, Nrfact, DataFact, Gestiune, ValTotala AS
Valoare;
FROM clienti C LEFT OUTER JOIN facturi F
ON [Link]=[Link];
INNER JOIN LOCALITATI L
ON [Link] = [Link];
WHERE Datafact BETWEEN datainf_ AND datasup_ ;
ORDER BY &cimp1, &cimp2_, &cimp3_, &cimp4_
RETURN
ENDPROC
Clauza ORDER BY se construieste dinamic cu ajutorul macrosubstitutiei.
Lansarea acestui program se face cu comanda DO:

DO listare1 WITH {^2001/08/02}, {^2001/08/07}, 'Loc', 'DataFact', 'DenCl', 'ValTotala'

In continuare se propune o varianta mai eleganta.

Ce-ar fi ca, in arara celor doua date calendaristice, de inceput si de sfarsit, ordonarea sa fie precizata
printr-o lista care sa contina unul, doua, trei. ..sau chiar nici un atribut?
Programul ce urmeaza preia un parametru de tip sir de caractere care este, de rapt, lista atributelor de
ordonare, atribute separate prin virgula.
Asa incat programul analizeaza virgulele din sir si extrage atributele pe baza carora se va construi,
dinamic, clauza WHERE.

In exemplul ce urmeaza se preia numai data initiala si finala, plus ordinea de prezentare, sub
forma de lista de atribute separate prin virgula
Returns the beginning numeric position of the first occurrence of a character expression or memo
field within another character expression or memo field, counting from the leftmost character. La
inceput sa facem cunostinta cu functia AT()
Syntaxa
AT(cSearchExpression, cExpressionSearched [, nOccurrence])
Returns
Numeric
Arguments
cSearchExpression
Specifies the character expression that AT( ) searches for in cExpressionSearched.
cExpressionSearched
Specifies the character expression cSearchExpression searches for.
Both cSearchExpression and cExpressionSearched can be memo fields of any size.
nOccurrence
Specifies which occurrence (first, second, third, and so on) of cSearchExpression is searched for in
cExpressionSearched. By default, AT( ) searches for the first occurrence of cSearchExpression
(nOccurrence = 1). Including nOccurrence lets you search for additional occurrences of
cSearchExpression in cExpressionSearched. AT( ) returns 0 if nOccurrence is greater than the
number of times cSearchExpression occurs in cExpressionSearched.
Remarks
AT( ) searches the second character expression for the first occurrence of the first character
expression. It then returns an integer indicating the position of the first character in the character
expression found. If the character expression isn't found, AT( ) returns 0.
The search performed by AT( ) is case-sensitive. To perform a search that isn't case-sensitive, use
ATC( ).

PROCEDURE listare2
PARAMETER datainf_, datasup_, ordinea_
I=1

*se determina prima pozitie a virgulei (asta inseamna cu sunt macar

*doua atribute de ordonare)

poz_ = AT (',', ordinea_, i)

IF poz_ > 0 && exista macar o virgula, deci sunt mai multe atribute de ordonare
* se localizeaza toate virgulele si se extrag atributele cuprinse intre virgule

poz_preced = O
DO WHILE poz_ # O
ii = str(i,1)
cimp&ii = SUBSTR(ordinea_, poz_preced + 1, poz_ -poz_preced -1 )
poz_preced = poz_
i = i+ 1
poz_ = AT (',', ordinea_, i)
ENDDO

*a mai ramas atributul dintre ultima virgula si sfirsitul sirului de ordonare

ii = str(i,1)
cimp&ii = SUBSTR(ordinea_, poz_preced + 1, LEN(ordinea_) -poz_preced)

*se construieste clauza ORDER BY

order_by_ = ‘ ’
FOR j = 1 TO i
jj = STR(j,1)
IF j > 1
order_by_ = order_by_ + ‘,’
ENDIF
order_by_ = order_by_ + ALLTRIM(cimp&jj)
ENDFOR

ELSE && exista maximum un atribut de ordonare de la IF poz_>0

IF LEN(ALLT(ordinea_))=0 && de fapt, nu exista nici un atribut de ordonare

Order_by_=’1’ && ordonarea se face dupa prima coloana
ELSE
Order_by_=ALLTRIM(ordinea_) && exista un singur atribut de ordonare
ENDIF

ENDIF

* si acum presentam propriu zis ordonarea

SELECT Dencl AS Client, Loc As Localitate, Nrfact, DataFact, Gestiune, ValTotala AS

Valoare;
FROM clienti C LEFT OUTER JOIN facturi F
ON [Link]=[Link];
INNER JOIN LOCALITATI L
ON [Link] = [Link];
WHERE Datafact BETWEEN datainf_ AND datasup_ ;
ORDER BY &order_by_
RETURN
ENDPROC

Prezentam 3 moduri de adresare la programul prezentat mai sus

DO listare2 WITH {^2001/08/02}, {^2001/08/07}, 'ValTotala,Dencl,NrFact'
DO listare2 WITH {^2001/08/02}, {^2001/08/07}, 'NrFact'
DO listare2 WITH {^2001/08/02}, {^2001/08/07}, ' '

10. Macrosubstitutie si iteratii SQL

Tot cu ajutorul macrosubstitutiei se pot rezolva situatii dintre cele mai dificile de obtinere a unor
rapoarte pretentioase. Spre exemplificare, dorim obtinerea unui raport matriceal in care, pe verticala,
sa fie prezente toate produsele comercializate de firma, iar "ordonata" sa fie constituita dfn primele
patru zile ale lunii august 2001, adica

DenPr CodPr Zi 01 08 2001 Zi 02 08 2001 Zi 03 08 2001 Zi 04 08 2001

Pentru facilitarea prezentarii, sa ne limitam la patru zile, dar programul ce urmeaza functioneaza
pentru oricate zile dorim, singura problema fiind completarea cursorului ZILE.
In prograrnul ce urmeaza, aceasta se face prin INSERT-uri directe, insa in aplicatii datele initiale si
finale ale intervalului se pot prelua printr-un formular si, astfel, completarea cursorului se
automatizeaza.
SET MARK TO '_' && separatorul pentru data calendaristica

CREATE CURSOR Zile (Zi DATE)

INSERT INTO Zile VALUES ({^2001/08/01})

INSERT INTO Zile VALUES ({^2001/08/02})
INSERT INTO Zile VALUES ((^2001/08/03})
INSERT INTO Zile VALUES ({^2001/08/04})

SELECT DataFact, DenPr, [Link], SUM(INT(Cantitate .PretUnit .(1+ProcTVA))) AS

Valoare;
INTO CURSOR Zile_Produse ;
FROM FACTURI F INNER JOIN LINIIFACT LF ON [Link] = [Link] ;
INNER JOIN PRODUSE P ON [Link]=[Link] ;
WHERE DataFact IN ;
(SELECT Zi ;
FROM Zile) ;
GROUP BY DataFact, DenPr, [Link]

SELECT DenPr, CodPr;

INTO CURSOR c1 ;
FROM PRODUSE ;
ORDER BY DenPr

i= 1
SELECT Zile
SCAN

ii = ALL T(STR(i,2))
ii_plus_1 = ALL T(STR(i+1 ,2))
ziua_= '_'+DTOC([Link])
SELECT c&ii..*, NVL(ZILE_PRODUSE.Valoare,0) AS Zi&ziua_ ;
FROM c&ii LEFT OUTER JOIN ZILE_PRODUSE ;
ON c&ii..CodPr = ZILE_PRODUSE.CodPr AND DataFact = [Link] ;
INTO CURSOR c&ii_plus_1
i=i+ 1
SELECT Zile

ENDSCAN

SELECT c&ii_plus_1
BROWSE TITLE 'Vinzari_Produse/Zile'

SELECT Zile
USE
SELECT Zile_Produse
USE

FOR j=1 TO i
ii = ALLT(STR(j,2))
SELECT c&ii
USE
ENDFOR

Cateva explicatii. Cursorul ZILE contine toate datele calendaristice care trebuie sa apara drept
coloane ale raportului.
Valorile vanzarilor pe produse si zile sunt calculate si stocate in cursorul Z ILE_PRODUSE.
Liniile raportului sunt alcatuite din toate produsele firmei, astfel incat cursorul "de start" - C1 -este
obtinut din tabela PRODUSE.
Numarul iteratiilor depinde de numarul inregistrarilor din cursorul ZILE - numarul zilelor pentru
care intereseaza raportul.
Corpul ciclului contine jonctiunea cursorului "curent" cu ziua corespunzatoare inregistrarii curente
din cursorul ZILE.