Base Restaurant

Commandes de création de la base et des TABLES

CREATE DATABASE restaurantxxxx
GO
USE restaurantxxxx
CREATE TABLE tableduresto
(NumTable INT,
 ZoneGeo VARCHAR(1),
 TypeFumeur VARCHAR(2),
 CONSTRAINT PKTable PRIMARY KEY (NumTable))

CREATE TABLE serveur
 (Numserveur INT ,
NomServeur VARCHAR(20),
PrenomServeur VARCHAR(15),
CONSTRAINT PKserveur PRIMARY KEY (NumServeur))

CREATE TABLE Plat
(NumPlat INT IDENTITY(1,1),
NomPlat VARCHAR(20),
 PrixVente smallmoney,
CoutRevient smallmoney,
 Categorie VARCHAR(1),
 QteStock INT,
CONSTRAINT PKplat PRIMARY KEY (NumPlat))


CREATE TABLE Commande (NumCde INT IDENTITY(1,1), DateCde Datetime, MontantCde smallmoney, NumTable INT, NumServeur INT,
 CONSTRAINT PKCommande PRIMARY KEY (Numcde),
CONSTRAINT FKServeur FOREIGN KEY (NumServeur) REFERENCES Serveur(Numserveur),
CONSTRAINT FKTable FOREIGN KEY (NumTable) REFERENCES TableDuresto(NumTable))

CREATE TABLE LigneCommande(NumCde INT, NumPlat INT, Quantite INT, CONSTRAINT PkLigneCom PRIMARY KEY (Numcde,NumPlat), CONSTRAINT FKCommande FOREIGN KEY (Numcde) REFERENCES Commande(NumCde), CONSTRAINT FKplat FOREIGN KEY (NumPlat) REFERENCES Plat(NumPlat))
GO



INSERT INTO TableduResto
VALUES (1,'A','N')
GO

INSERT INTO TableduResto
 VALUES (2,'B','O')
GO

INSERT INTO TableduResto
VALUES (3,'C','N')
GO

INSERT INTO TableduResto
VALUES (4,'B','O')
GO

INSERT INTO TableduResto
VALUES (5,'A','N')
GO

INSERT INTO TableduResto
VALUES (6,'B','O')
GO

INSERT INTO TableduResto
VALUES(7,'C','N')
GO

INSERT INTO TableduResto
VALUES (8,'B','O')
GO
INSERT INTO Serveur
VALUES (1,'Aubert','Jean-Louis')
GO

INSERT INTO Serveur
VALUES(2,'Fabian','Lara')
GO

INSERT INTO Serveur
VALUES (3,'Thiersen','Yann')
GO

INSERT INTO Serveur
VALUES (4,'Lebreton','Arnaud')
GO

INSERT INTO Serveur
VALUES (5,'Segara','Hélène')
GO


INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES('Salade de chèvre ','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Salade du pêcheur','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Huitres','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Foie gras','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Homard','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Pizza reine','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Pizza personalisée','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES('Calzone','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Spaghetti bolognaise','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES('Tagliatèles à la carbo','1','1','P','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Colonel','1','1','D','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient, Categorie,QteStock)
VALUES('Crêpe au sucre','1','1','D','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient, Categorie,QteStock)
VALUES ('Glace au fruit','1','1','D','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Coupe de fruits ','1','1','D','1000')
GO

INSERT INTO Plat (NomPlat,PrixVente,CoutRevient,Categorie,QteStock)
VALUES ('Steak haché','1','1','P','1000')
GO

INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('14/08/2013',0.0,2,1)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('14/08/2013',0.0,5,1)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('15/08/2013',0.0,1,2)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('15/08/2013',0.0,3,3)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('15/08/2013',0.0,5,2)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('16/08/2013',0.0,1,1)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('16/08/2013',0.0,2,1)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('16/08/2013',0.0,3,4)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('16/08/2013',0.0,4,4)
INSERT INTO Commande (DateCde,MontantCde,NumTable,NumServeur)
VALUES ('16/08/2013',0.0,5,4)



INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (1,2,2)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (1,5,1)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (1,8,2)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (2,4,2)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (2,6,3)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (2,11,3)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (2,15,2)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (3,9,1)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (3,14,4)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (3,15,2)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (3,1,2)

INSERT INTO LigneCommande (NumCde,NumPlat,Quantite)
VALUES (3,5,1)