Listing 1: Instruction qui crée la table Customers
CREATE TABLE customers (customerid int not NULL,
companyName varchar(4à˜) not NULL,
contactName char(3à˜) NULL,
address varchar (6à˜) NULL,
income money NULL,
city char(15) NULL,
country char(3à˜) NULL,
gender char(1) NULL,
phone char(24) NULL,
fax char(24) NULL)
Listing 2: Batch qui insère
100.000 lignes dans la table Customers
SET NOCOUNT ON
DECLARE @counter int
DECLARE @companyname char(25)
SET @counter = 1
WHILE @counter < 1à˜à˜à˜à˜1
BEGIN
SET @companyname = ‘Knosys'
INSERT INTO customers (customerid, companyname,
contactname, address, country, gender)
values(@counter, @companyname,
‘contact' + cast(@counter as char),
‘address' + cast(@counter as varchar),
‘USA', ‘M')
SET @counter = @counter + 1
END
UPDATE customers SET country = ‘Canada'
WHERE customerid % 1à˜ = 1
UPDATE customers SET country = ‘Mexico'
WHERE customerid % 2à˜ = à˜
UPDATE customers SET country = ‘Germany'
WHERE customerid % 2à˜à˜à˜ = 13
UPDATE customers SET gender = ‘F'
WHERE customerid % 2 = à˜
Listing 3: Première requête sur la table Customers
CREATE INDEX i_country on customers(country)
GO
SELECT customerid from customers
WHERE gender = ‘M' and country = ‘Germany'
Listing 4: Seconde requête sur la table Customers
SELECT customerid from customers
WHERE gender = ‘M' and country = ‘USA'
Listing 5: Requête générique sur la table Customers
DECLARE @country char(25)
SET @country = ‘Germany'
SELECT customerid FROM customers
WHERE gender = ‘M' and country = @country
Listing 6: Instruction qui crée la table Orders
CREATE TABLE orders (orderid int not NULL,
customerid int not NULL,
item_id int,
item_name varchar(2à˜),
orderdate datetime default getdate() NULL,
shippeddate datetime NULL,
freight money NULL,
shipname varchar(4à˜) NULL,
shipaddress varchar(6à˜) NULL)
Listing 7: Batch qui insère 500.000 lignes dans la table Orders
SET NOCOUNT ON
DECLARE @i integer, @j integer
DECLARE @job char(2à˜), @enter_date datetime
DECLARE @project_no char(4)
DECLARE @counter int
DECLARE @shipped_date datetime
DECLARE @freight money
SET @i = 1
SET @j = 1
SET @shipped_date = getdate()
SET @freight = 1à˜à˜.à˜à˜
WHILE @i < 5
BEGIN
WHILE @j < 1à˜à˜à˜à˜à˜
BEGIN
INSERT INTO orders (orderid, customerid,
item_name, shippeddate, freight)
values(@i, @j, NULL, @shipped_date,
@freight)
SET @j = @j+1
END
SET @i = @i+1
SET @j = 1
END
UPDATE orders SET item_name = ‘PC'
WHERE customerid % 5 = 1
UPDATE orders SET item_name = ‘Controller'
WHERE customerid % 2à˜à˜à˜ = 13
Listing 8: Requête qui extrait des valeurs Customer-Income des
tables Customers et Orders, d'après les ventes de PC
CREATE INDEX i_cust_customerid on customers(customerid)
CREATE INDEX i_ord_customerid on orders (customerid)
CREATE INDEX i_itemname on orders(item_name)
SELECT avg(income)
FROM customers JOIN orders
ON customers.customerid = orders.customerid
WHERE orders.item_name = ‘PC'
Listing 9: Requête qui extrait des valeurs Customer-Income des
tables Customers et Orders, d'après les ventes de contrôleurs
SELECT avg(income)
FROM customers JOIN orders
ON customers.customerid = orders.customerid
WHERE orders.item_name = ‘Controller'
Téléchargez gratuitement cette ressource
Guide Azure Virtual Desktop
Comment optimiser les coûts, gagner en agilité, en sécurité et en conformité avec Azure Virtual Desktop ? Découvrez, dans ce Guide Infographique, les bénéfices clés pour les utilisateurs et les avantages de la mise en œuvre pour les équipes IT.