GkScDVST

1use master
2go
3if exists (select* from sys.databases s where s.name='Usines')
4begin
5drop database Usines
6print'database DROPPED MutherFucker'
7end
8
9create database usines
10print 'Database Created'
11go
12use usines
13go
14--CTRL Shift R ,refresh la cashe pour lintelisense
15create table Fournisseur
16(---->IDENTITY=auto increment,1,1 par defaut start 1 et incremente de 1
17FournisseurID int IDENTITY primary key,
18nom varchar(50) not null default'',
19ville varchar(50) null default 'Ile Moukmouk',
20
21)
22print 'Table fournisseur créé'
23
24go
25insert into Fournisseur (nom,ville)
26values
27 ('Exotixliquid','paris'),
28 ('Exid','bruxelle'),
29  ('ExoGear','Montreal'),
30  ('DancingPoleINC','Montreal'),
31  ('CocaineShop','Montreal'),
32  ('Exacapela','London'),
33  ('TrustedMetal','London')
34  
35  go
36create table products
37(
38produitID int IDENTITY primary key,
39nom varchar(50) not null default'',
40couleur varchar(50) null default 'blanc'
41)
42
43insert into products(nom,couleur)
44values
45('mr net','vert'),
46('ptite vache','Blanc'),
47('café','brun velouté'),
48('cocaine','blanc')
49go
50create table FournisseurProduits
51(
52FournisseurProduitsID INT identity primary key,
53produitID int not null foreign key references products(produitID),
54FournisseurID int not null foreign key references fournisseur (FournisseurID),
55prix int not null default 100,
56qty int null default 0
57)
58go
59insert into FournisseurProduits(produitID,FournisseurID,prix,qty)
60values
61(1,1,500,250),
62(2,1,500,250),
63(3,1,500,250),
64(4,2,500,250),
65(3,3,500,250),
66(3,4,500,250),
67(1,5,500,250),
68(2,6,500,250)
69
70select * from Fournisseur f
71inner join FournisseurProduits fp
72on f.FournisseurID=fp.FournisseurID
73inner join products p
74on fp.produitID=p.produitID
75
76
77--sortir la moyenne de prix des produits par fournisseurs avec le nom du founiseeur
78
79select max(f.nom),avg(prix)as 'Moyenne' from FournisseurProduits fp
80inner join fournisseur f
81on fp.FournisseurID=f.FournisseurID
82group by f.FournisseurID
83
84--sortir les fournisseur ayant une moyenne de prix de produits plus élevé
85-- que la moyenne de prix du fournisseur
86
87--1 sortir la moyenne de prix des fournisseur
88select AVG(prix) from FournisseurProduits fp
89inner join Fournisseur f
90on fp.FournisseurID=f.FournisseurID
91where f.nom='CocaineShop'
92
93--2 sortir la moyenne des prix de tout les fournisseurs
94
95
96select AVG(prix) from FournisseurProduits fp
97group by FournisseurID
98
99--3 merger 1 et 2 en sous requete
100
101select AVG(prix) from FournisseurProduits fp
102group by FournisseurID having AVG(prix)>
103(select AVG(prix) from FournisseurProduits fp
104inner join Fournisseur f
105on fp.FournisseurID=f.FournisseurID
106where f.nom='CocaineShop')
107
108)