Listings

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'