EVALUTATIONSQL

USE [master]
GO
/****** Object:  Database [DYLAN1]    Script Date: 28/12/2018 15:48:37 ******/
CREATE DATABASE [DYLAN1]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'DYLAN1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DYLAN1.mdf' , SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'DYLAN1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DYLAN1_log.ldf' , SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DYLAN1] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DYLAN1].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [DYLAN1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DYLAN1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DYLAN1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DYLAN1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DYLAN1] SET ARITHABORT OFF
GO
ALTER DATABASE [DYLAN1] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DYLAN1] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DYLAN1] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DYLAN1] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DYLAN1] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DYLAN1] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DYLAN1] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DYLAN1] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DYLAN1] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DYLAN1] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DYLAN1] SET  ENABLE_BROKER
GO
ALTER DATABASE [DYLAN1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DYLAN1] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DYLAN1] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DYLAN1] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DYLAN1] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DYLAN1] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [DYLAN1] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [DYLAN1] SET RECOVERY FULL
GO
ALTER DATABASE [DYLAN1] SET  MULTI_USER
GO
ALTER DATABASE [DYLAN1] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [DYLAN1] SET DB_CHAINING OFF
GO
ALTER DATABASE [DYLAN1] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [DYLAN1] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
EXEC sys.sp_db_vardecimal_storage_format N'DYLAN1', N'ON'
GO
USE [DYLAN1]
GO
/****** Object:  Table [dbo].[ADMINISTRATEUR]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ADMINISTRATEUR](
	[ID_ADMINISTRATEUR] [int] IDENTITY(1,1) NOT NULL,
	[LIB_NOM_ADMINISTRATEUR] [varchar](50) NULL,
	[LIB_PRENOM_ADMINISTRATEUR] [varchar](50) NULL,
	[ADRESSE_MAIL_ADMINISTRATEUR] [varchar](50) NULL,
	[MDP_ADMINISTRATEUR] [varchar](50) NULL,
	[NUM_PORTABLE_ADMINISTRATEUR] [varchar](10) NULL,
PRIMARY KEY CLUSTERED
(
	[ID_ADMINISTRATEUR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[COMPORTER]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[COMPORTER](
	[ID_GRAINE] [int] NOT NULL,
	[ID_RECETTE] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_GRAINE] ASC,
	[ID_RECETTE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FAMILLE]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FAMILLE](
	[ID_FAMILLE] [int] IDENTITY(1,1) NOT NULL,
	[LIB_NOM_FAMILLE] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
	[ID_FAMILLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[FOURNIR]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FOURNIR](
	[PRIX_FOURNIR] [money] NULL,
	[POIDS_FOURNIR] [float] NULL,
	[ID_FOURNISSEUR] [int] NOT NULL,
	[ID_GRAINE] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_GRAINE] ASC,
	[ID_FOURNISSEUR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FOURNISSEUR]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[FOURNISSEUR](
	[ID_FOURNISSEUR] [int] IDENTITY(1,1) NOT NULL,
	[LIB_NOM_FOURNISSEUR] [varchar](50) NULL,
	[LIB_PRENOM_FOURNISSEUR] [varchar](50) NULL,
	[ADRESSE_MAIL_FOURNISSEUR] [varchar](50) NULL,
	[MDP_FOURNISSEUR] [varchar](50) NULL,
	[NUM_FAX_FOURNISSEUR] [varchar](50) NULL,
	[VALIDATION_COMPTE_FOURNISSEUR] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_FOURNISSEUR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[GRAINE]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GRAINE](
	[ID_GRAINE] [int] IDENTITY(1,1) NOT NULL,
	[LIB_NOM_GRAINE] [varchar](50) NULL,
	[DESCRIPTION_GRAINE] [text] NULL,
	[SEMAIN_MIN_GRAINE] [int] NULL,
	[SEMAINE_MAX_GRAINE] [int] NULL,
	[ESPACEMENT_PIED_GRAINE] [float] NULL,
	[ESPACEMENT_LIGNE_GRAINE] [float] NULL,
	[CONSEILS_GRAINE] [text] NULL,
	[ID_FAMILLE] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_GRAINE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[JARDINIER]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JARDINIER](
	[ID_JARDINIER] [int] IDENTITY(1,1) NOT NULL,
	[LIB_NOM_JARDINIER] [varchar](50) NULL,
	[LIB_PRENOM_JARDINIER] [varchar](50) NULL,
	[ADRESSE_MAIL_JARDINIER] [varchar](50) NULL,
	[MDP_JARDINIER] [varchar](10) NULL,
	[DATE_NAISSANCE_JARDINIER] [date] NULL,
	[DATE_INSCRIPTION_JARDINIER] [datetime] NULL,
	[VALIDATION_COMPTE_JARDINIER] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_JARDINIER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PHOTO]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PHOTO](
	[ID_PHOTO] [int] IDENTITY(1,1) NOT NULL,
	[CHEMIN_PHOTO] [varchar](250) NULL,
	[ID_GRAINE] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_PHOTO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[RECETTE]    Script Date: 28/12/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RECETTE](
	[ID_RECETTE] [int] IDENTITY(1,1) NOT NULL,
	[LIB_NOM_RECETTE] [varchar](50) NULL,
	[PROTOCOLE_RECETTE] [text] NULL,
	[CONTENU_RECETTE] [text] NULL,
	[ID_JARDINIER] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
	[ID_RECETTE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[COMPORTER]  WITH CHECK ADD FOREIGN KEY([ID_GRAINE])
REFERENCES [dbo].[GRAINE] ([ID_GRAINE])
GO
ALTER TABLE [dbo].[COMPORTER]  WITH CHECK ADD FOREIGN KEY([ID_RECETTE])
REFERENCES [dbo].[RECETTE] ([ID_RECETTE])
GO
ALTER TABLE [dbo].[FOURNIR]  WITH CHECK ADD FOREIGN KEY([ID_FOURNISSEUR])
REFERENCES [dbo].[FOURNISSEUR] ([ID_FOURNISSEUR])
GO
ALTER TABLE [dbo].[FOURNIR]  WITH CHECK ADD FOREIGN KEY([ID_GRAINE])
REFERENCES [dbo].[GRAINE] ([ID_GRAINE])
GO
ALTER TABLE [dbo].[GRAINE]  WITH CHECK ADD FOREIGN KEY([ID_FAMILLE])
REFERENCES [dbo].[FAMILLE] ([ID_FAMILLE])
GO
ALTER TABLE [dbo].[PHOTO]  WITH CHECK ADD FOREIGN KEY([ID_GRAINE])
REFERENCES [dbo].[GRAINE] ([ID_GRAINE])
GO
ALTER TABLE [dbo].[RECETTE]  WITH CHECK ADD FOREIGN KEY([ID_JARDINIER])
REFERENCES [dbo].[JARDINIER] ([ID_JARDINIER])
GO
USE [master]
GO
ALTER DATABASE [DYLAN1] SET  READ_WRITE
GO









INSERT INTO FOURNISSEUR (LIB_NOM_FOURNISSEUR, LIB_PRENOM_FOURNISSEUR, ADRESSE_MAIL_FOURNISSEUR, NUM_FAX_FOURNISSEUR, MDP_FOURNISSEUR, VALIDATION_COMPTE_FOURNISSEUR)



VALUES


('Mitterrand','Francois','francois.mitterrand@gmail.com','060606060600','mdp0','0'),


('Washington','George','george.washington@gmail.com','0606060601','mdp1','0'),


('Adams','John','lafamilleadams@gmail.com','0606060602','mdp2','0'),


('Jefferson','Thomas','thoms@gmail.com','0606060603','mdp3','0'),


('Madison','James','jmlemadison@gmail.com','0606060604','mdp4','0'),


('Monroe','James','monroe@gmail.com','0606060605','mdp5','0'),


('Quincy Adams','John','lafamilleadams2.0@gmail.com','0606060606','mdp6','0'),


('Jackson','Andrew','beatit@gmail.com','0606060607','mdp7','0'),


('Van Buren','Martin','oui@gmail.com','0606060608','mdp8','0'),


('Obama','Barack','god@gmail.com','0606060609','mdp9','0');





INSERT INTO JARDINIER (LIB_NOM_JARDINIER, LIB_PRENOM_JARDINIER, ADRESSE_MAIL_JARDINIER, DATE_NAISSANCE_JARDINIER, MDP_JARDINIER, DATE_INSCRIPTION_JARDINIER, VALIDATION_COMPTE_JARDINIER)


VALUES


('Dunant','Henri','croixrouge@gmail.com','18280508','mpd0','20181221 11:46:01.00','0'),


('Passy','Frédéric','sedunioninterparlementaire@gmail.com','18220520','mpd1','20181221 11:46:01.00','0'),


('Ducommun','Élie','secretairebip@gmail.com','18330219','mpd2','20181221 11:46:01.00','0'),


('Gobat','Albert','sedsecretaireui@gmail.com','18430521','mpd3','20181221 11:46:01.00','0'),


('Randal Cremer','William','pereui@gmail.com','18280318','mpd4','20181221 11:46:01.00','0'),


('Roosevelt','Theodore','sauveur@gmail.com','18581027','mpd5','20181221 11:46:01.00','0'),


('Renault','Louis','vroumvroum@gmail.com','18430521','mpd6','20181221 11:46:01.00','0'),


('Fried','Alfred','goodman@gmail.com','18641111','mpd7','20181221 11:46:01.00','0'),


('La fontaine','Henri','detrevi@gmail.com','18540422','mpd8','20181221 11:46:01.00','0'),

('Mukwege','Denis','accouchements@gmail.com','19550301','mpd0','20181221 11:46:01.00','0');




INSERT INTO ADMINISTRATEUR (LIB_NOM_ADMINISTRATEUR, LIB_PRENOM_ADMINISTRATEUR, ADRESSE_MAIL_ADMINISTRATEUR, NUM_PORTABLE_ADMINISTRATEUR, MDP_ADMINISTRATEUR)


VALUES


('Lardet', 'Valentin', 'lardet.valentin@gmail.com','0666666660','mdp1'),


('De Araujo', 'Julienne','juliendearaujo@gmail.com','0666666661','mdp2'),


('Gonin', 'Arnaud','arnaud.gonin@gmail.com','0666666662','mdp3'),


('Arthaud', 'Nathalie', 'nathalie.arthaud@gmail.com','0666666663','mdp4'),


('Macron', 'Manu','macromem@gmail.com','0666666664','mdp5'),


('Mélenchon', 'Jean-Luc','jlmelenchon@gmail.com','0666666665','mdp6'),


('Sarokzy', 'Nicolas', 'fric@gmail.com','0666666666','mdp7'),


('Castaner', 'Edouard','wtf@gmail.com','0666666667','mdp8'),


('Halliday', 'Johny','allumerlefeu@gmail.com','0666666668','mdp9'),


('Degaulle', 'Général','Jevousaicompris@gmail.com','9','mdp10');





INSERT INTO RECETTE (LIB_NOM_RECETTE, PROTOCOLE_RECETTE, CONTENU_RECETTE, ID_JARDINIER)


VALUES

('Confiture de Mirabelle','Cuire mirabelle + eau + sucre 3 heures','eau sucre mirabelle','1'),


('Confiture de Choux','Cuire choux + eau + sucre 3 heures','eau sucre choux','2'),


('Confiture de Cerise','Cuire cerise + eau + sucre 3 heures','eau sucre cerise','1'),


('Confiture de Fraise','Cuire fraise + eau + sucre 3 heures','eau sucre fraie','3'),


('Confiture de pomme','Cuire pomme + eau + sucre 3 heures','eau sucre pomme','1'),


('Confiture de poire','Cuire poire + eau + sucre 3 heures','eau sucre poire','3'),


('Confiture de myrtille','Cuire myrtille + eau + sucre 3 heures','eau sucre myrtille','4'),


('Confiture de Coing','Cuire coing + eau + sucre 3 heures','eau sucre coing','5'),


('Confiture de Abricot','Cuire abricot + eau + sucre 3 heures','eau sucre abricot','7'),


('Confiture de Figue','Cuire figue + eau + sucre 3 heures','eau sucre figue','9');




INSERT INTO FAMILLE (LIB_NOM_FAMILLE)


VALUES


('agrume'),


('cucurbitacé'),


('racine'),


('haricot'),


('salade'),


('champignon'),


('oignon'),


('legumevert'),


('fruit'),


('Fabaceae');


INSERT INTO GRAINE (LIB_NOM_GRAINE,DESCRIPTION_GRAINE, SEMAIN_MIN_GRAINE,SEMAINE_MAX_GRAINE,ESPACEMENT_PIED_GRAINE,ESPACEMENT_LIGNE_GRAINE,CONSEILS_GRAINE,ID_FAMILLE)


VALUES


('Courgette','Lorem Ipsum','4','8','7','20','Planter a 15 cm de profondeur et arroser','2'),


('citron','Lorem Ipsum','1','3','0','20','Planter a 15 cm de profondeur et arroser','1'),


('carotte','Lorem Ipsum','9','12','8','20','Planter a 15 cm de profondeur et arroser','3'),


('haricot blanc','Lorem Ipsum','9','14','15','20','Planter a 15 cm de profondeur et arroser','4'),


('feuille de chene','Lorem Ipsum','3','4','15','20','Planter a 15 cm de profondeur et arroser','5'),


('truffe','Lorem Ipsum','2','2','15','20','Planter a 15 cm de profondeur et arroser','6'),


('oignon rouge','Lorem Ipsum','13','20','5','20','Planter a 15 cm de profondeur et arroser','7'),


('choux vert','Lorem Ipsum','50','52','3','20','Planter a 15 cm de profondeur et arroser','8'),


('Poire','Lorem Ipsum','46','47','15','9.5','Planter a 15 cm de profondeur et arroser','9'),


('lentilles','Lorem Ipsum','39','45','15','20','Planter a 15 cm de profondeur et arroser','10');





INSERT INTO FOURNIR (PRIX_FOURNIR, POIDS_FOURNIR, ID_FOURNISSEUR, ID_GRAINE)


VALUES


('15.99','0.1','9','10'),


('14.99','0.12','6','1'),


('13.99','0.13','7','3'),


('12.99','0.14','8','2'),


('11.99','0.15','1','5'),


('10.99','0.16','2','6'),


('17.99','0.17','3','4'),


('18.99','0.18','4','9'),


('19.99','0.19','4','8'),


('9.99','0.2','8','7');





INSERT INTO PHOTO (CHEMIN_PHOTO, ID_GRAINE)


VALUES


('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail_2x/p2634-1.jpg', '1'),


('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail/a0114-1.jpg', '2'),


('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail/m0021-1.jpg', '3'),

('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail_2x/l0208-1.jpg', '4'),


('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail/p4103-1.jpg', '5'),


('https://cache.marieclaire.fr/data/photo/w1000_c17/cuisine/137/truffe.jpg', '6'),


('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail/l0030-1.jpg', '7'),
('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail/p0907-1.jpg', '8'),

('https://kokopelli-semences.fr/media/cache/shop_product_large_thumbnail_2x/p6233-1.jpg', '9'),


('https://media.gerbeaud.net/2013/10/640/lentille-gousse.jpg', '10');
























SELECT * FROM GRAINE;




SELECT LIB_NOM_GRAINE, LIB_NOM_FAMILLE FROM GRAINE, FAMILLE WHERE SEMAIN_MIN_GRAINE > '20180101' AND SEMAINE_MAX_GRAINE < '20180630' ORDER BY SEMAIN_MIN_GRAINE ASC;




SELECT LIB_NOM_GRAINE, LIB_NOM_FAMILLE FROM GRAINE, FAMILLE WHERE GRAINE.ID_FAMILLE = FAMILLE.ID_FAMILLE;





SELECT LIB_NOM_GRAINE, CHEMIN_PHOTO FROM GRAINE, PHOTO WHERE GRAINE.ID_GRAINE = PHOTO.ID_GRAINE;





SELECT * FROM GRAINE, COMPORTER, RECETTE WHERE LOWER(LIB_NOM_RECETTE) LIKE 'p%' AND GRAINE.ID_GRAINE = COMPORTER.ID_GRAINE AND COMPORTER.ID_RECETTE = RECETTE.ID_RECETTE;





SELECT * FROM GRAINE, COMPORTER, RECETTE WHERE LOWER(LIB_NOM_RECETTE) LIKE '%e' AND GRAINE.ID_GRAINE = COMPORTER.ID_GRAINE AND COMPORTER.ID_RECETTE = RECETTE.ID_RECETTE;






SELECT TOP 10 * FROM FAMILLE;

SELECT TOP 10 LIB_NOM_FOURNISSEUR, LIB_PRENOM_FOURNISSEUR FROM FOURNISSEUR;

SELECT TOP 10 LIB_NOM_JARDINIER, LIB_PRENOM_JARDINIER FROM JARDINIER ORDER BY DATE_NAISSANCE_JARDINIER ASC;

SELECT TOP 10 LOWER(LIB_NOM_GRAINE) FROM GRAINE;
SELECT TOP 10 UPPER(LIB_NOM_GRAINE) FROM GRAINE;
SELECT TOP 10 LOWER(ADRESSE_MAIL_JARDINIER) FROM JARDINIER WHERE ADRESSE_MAIL_JARDINIER LIKE 'sed%';
SELECT TOP 10 UPPER(LIB_NOM_GRAINE) FROM GRAINE WHERE ESPACEMENT_PIED_GRAINE BETWEEN 5 AND 10;


SELECT TOP 10 UPPER(LIB_NOM_GRAINE) FROM GRAINE WHERE ESPACEMENT_PIED_GRAINE < 5;

SELECT COUNT(*) FROM GRAINE;

SELECT COUNT(*) FROM GRAINE GROUP BY ID_FAMILLE;



ALTER TABLE GRAINE DROP COLUMN SEMAIN_MIN_GRAINE;


ALTER TABLE GRAINE DROP COLUMN SEMAINE_MAX_GRAINE;


ALTER TABLE GRAINE ADD SEMAIN_MIN_GRAINE DATE;


ALTER TABLE GRAINE ADD SEMAINE_MAX_GRAINE DATE;


SELECT TOP 10 * FROM FAMILLE;