TP vente

Tout ce qui tourne autour de Postgresql.
Avatar de l’utilisateur
fabio
Gourou
Gourou
Messages : 1340
Inscription : 11 sept. 2011, 16:32

TP vente

Message par fabio » 15 mai 2017, 09:58

Avec la correction
tp_note_05_10_corr.pdf
(217.68 Kio) Téléchargé 160 fois

Code : Tout sélectionner

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: appartient; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE appartient (
    id_vendeur integer NOT NULL,
    id_service integer NOT NULL
);


ALTER TABLE appartient OWNER TO vente_admin;

--
-- Name: client; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE client (
    id_client integer NOT NULL,
    nom character varying(20) NOT NULL,
    prenom character varying(20),
    id_ville integer,
    tel_perso character(10),
    tel_prof character(10),
    tel_mobile character(10)
);


ALTER TABLE client OWNER TO vente_admin;

--
-- Name: commande; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE commande (
    id_commande character(8) NOT NULL,
    date date NOT NULL,
    montant_ht numeric(7,2),
    id_client integer,
    id_vendeur integer,
    remise integer,
    CONSTRAINT commande_montant_ht_check CHECK ((montant_ht > (0)::numeric))
);


ALTER TABLE commande OWNER TO vente_admin;

--
-- Name: departement; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE departement (
    id_departement character(2) NOT NULL,
    departement character varying(30)
);


ALTER TABLE departement OWNER TO vente_admin;

--
-- Name: ligne_commande; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE ligne_commande (
    id_commande character(8) NOT NULL,
    id_ligne integer NOT NULL,
    quantite integer NOT NULL,
    id_produit character(5),
    CONSTRAINT ligne_commande_quantite_check CHECK ((quantite > 0))
);


ALTER TABLE ligne_commande OWNER TO vente_admin;

--
-- Name: produit; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE produit (
    id_produit character(5) NOT NULL,
    designation character varying(50) NOT NULL,
    descriptif character varying(200),
    prix numeric(6,2),
    delai_livraison integer DEFAULT 0
);


ALTER TABLE produit OWNER TO vente_admin;

--
-- Name: service; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE service (
    id_service integer NOT NULL,
    nom character varying(30) NOT NULL
);


ALTER TABLE service OWNER TO vente_admin;

--
-- Name: vendeur; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE vendeur (
    id_vendeur integer NOT NULL,
    nom character varying(20) NOT NULL,
    prenom character varying(20) NOT NULL,
    salaire numeric(6,2),
    annee_embauche character(4),
    id_vendeur_responsable integer,
    CONSTRAINT vendeur_salaire_check CHECK ((salaire > (0)::numeric))
);


ALTER TABLE vendeur OWNER TO vente_admin;

--
-- Name: ville; Type: TABLE; Schema: public; Owner: vente_admin; Tablespace: 
--

CREATE TABLE ville (
    id_ville integer NOT NULL,
    ville character varying(30),
    id_departement character(2)
);


ALTER TABLE ville OWNER TO vente_admin;

--
-- Data for Name: appartient; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY appartient (id_vendeur, id_service) FROM stdin;
1	3
2	1
3	2
4	1
5	1
5	2
6	2
7	3
\.


--
-- Data for Name: client; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY client (id_client, nom, prenom, id_ville, tel_perso, tel_prof, tel_mobile) FROM stdin;
1	DURAND	Pascal	1	0326334455	0326443355	0655334411
2	DUPONT	Christophe	2	0324583144	0336321200	\N
3	PALMIER	Christine	3	0326991244	0326112233	0606060606
4	SIGAUT	Jean	4	0324380017	0624031121	0632457629
5	DEV-INFO	\N	1	0326367600	\N	\N
6	CNAM	\N	1	0326368000	\N	\N
7	DUPOND	Yannick	1	0326839402	\N	0623658739
8	PASCAL	Jean	1	0326121212	0324382211	0605040302
9	DUPONTEL	Bastien	1	0326828180	\N	0699887766
10	FAIM	Jessie	1	0326112233	0326112233	0626112233
11	ARD-INFO	\N	4	0324380001	\N	0633380001
12	COPTER	Elie	3	0326583355	0326382244	\N
13	LECOUVERT	Jaimie	2	0324581276	\N	0623242511
14	LECOUVERT	Jérémi	2	0324581276	\N	0623242512
15	PIERRE	Jean	1	\N	\N	\N
\.


--
-- Data for Name: commande; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY commande (id_commande, date, montant_ht, id_client, id_vendeur, remise) FROM stdin;
00000001	2015-12-20	1043.00	1	2	\N
00000002	2015-12-22	1396.00	2	1	\N
00000003	2015-12-28	369.00	3	2	\N
00000004	2015-12-30	735.00	4	2	\N
00000005	2016-01-04	93.00	1	2	\N
00000006	2016-01-22	5229.00	5	4	10
00000007	2016-01-28	22523.20	6	4	20
00000008	2016-02-02	2930.00	7	2	\N
00000009	2016-02-02	108.00	8	4	\N
00000010	2016-02-02	155.00	9	5	\N
00000011	2016-02-08	598.50	5	5	10
00000012	2016-02-14	850.00	10	4	\N
00000013	2016-02-15	2484.00	11	5	10
00000014	2016-02-16	38.00	12	7	\N
00000015	2016-02-24	49.00	13	4	\N
00000016	2016-03-01	952.00	14	4	\N
00000017	2016-03-12	7038.50	6	5	50
00000018	2016-03-12	1998.00	11	5	10
00000019	2016-03-12	369.00	15	6	\N
00000020	2016-03-13	1056.00	11	4	20
\.


--
-- Data for Name: departement; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY departement (id_departement, departement) FROM stdin;
51	MARNE
08	ARDENNES
\.


--
-- Data for Name: ligne_commande; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY ligne_commande (id_commande, id_ligne, quantite, id_produit) FROM stdin;
00000001	1	1	b3375
00000001	2	1	c4948
00000001	3	1	p4752
00000001	4	2	m3536
00000001	5	1	d3899
00000001	6	1	g4438
00000001	7	1	d3575
00000001	8	1	v3545
00000001	9	1	e3837
00000001	10	1	c4843
00000001	11	1	h4767
00000001	12	1	i4627
00000002	1	1	i4694
00000002	2	1	s4871
00000002	3	1	m4597
00000003	1	1	e4817
00000004	1	1	e4409
00000004	2	1	v4875
00000005	1	1	s4829
00000005	2	1	m4338
00000006	1	5	b3375
00000006	2	5	c4876
00000006	3	5	p4857
00000006	4	5	m3536
00000006	5	5	c1757
00000006	6	5	d3575
00000006	7	5	v4498
00000006	8	5	e3837
00000006	9	5	c4843
00000007	1	24	b3375
00000007	2	24	c4876
00000007	3	24	p4857
00000007	4	24	m3536
00000007	5	24	c1757
00000007	6	24	d3575
00000007	7	24	v4498
00000007	8	24	e3837
00000007	9	24	c4843
00000007	10	2	i4802
00000008	1	10	s4871
00000008	2	10	i4627
00000009	1	1	s4721
00000009	2	1	i4895
00000010	1	1	d4839
00000011	1	5	i4802
00000012	1	1	b3375
00000012	2	1	c4948
00000012	3	1	p4752
00000012	4	2	m3536
00000012	5	1	d3899
00000012	6	1	g4438
00000012	7	1	d3575
00000012	8	1	v3545
00000012	9	1	e3837
00000012	10	1	c4843
00000012	11	1	h4767
00000013	1	15	b3816
00000013	2	15	d4637
00000014	1	1	m4338
00000015	1	1	g4438
00000016	1	1	b1286
00000016	2	1	c4536
00000016	3	1	p4221
00000016	4	1	m4810
00000016	5	1	g4438
00000016	6	1	c1976
00000016	7	1	d3575
00000016	8	1	v4875
00000017	1	12	b3375
00000017	2	12	c4876
00000017	3	12	p4857
00000017	4	12	m3536
00000017	5	12	c1757
00000017	6	12	d3575
00000017	7	12	v4498
00000017	8	12	e3837
00000017	9	12	c4843
00000017	10	1	i4802
00000018	1	15	i4895
00000018	2	15	s4829
00000019	1	1	e4817
00000020	1	1	i4694
00000020	2	1	s4871
\.


--
-- Data for Name: produit; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY produit (id_produit, designation, descriptif, prix, delai_livraison) FROM stdin;
b3375	boîtier standard moyen tour noir	\N	41.00	0
b1593	boîtier standard moyen tour blanc	\N	53.00	0
b1286	boîtier desktop plat	\N	82.00	5
b3817	boîtier externe deux pouces et demi usb 2.0	\N	33.00	7
b3816	boîtier externe trois pouces et demi usb 2.0	\N	35.00	0
b3114	boîtier externe cinq pouces un quart usb 2.0	\N	42.00	0
c4948	carte mère amd asrock	\N	59.00	0
c4536	carte mère asus athlon 64	\N	106.00	0
c4876	carte mère asus pentium 4	\N	106.00	0
c4258	carte mère msi athlon 64	\N	98.00	0
c4620	carte mère msi p5	\N	117.00	0
p4752	processeur amd sempron 3000+	\N	101.00	0
p4280	processeur amd sempron 3100+	\N	116.00	0
p4221	processeur athlon 64 3800+	\N	334.00	0
p4412	processeur athlon 64 4000+	\N	397.00	3
p4863	processeur intel celeron 326	\N	86.00	0
p4864	processeur intel celeron 336	\N	98.00	3
p4857	processeur intel p4-660	\N	467.00	0
p4858	processeur intel p4-670	\N	702.00	8
m4338	clé 512 mo usb 2.0	livrée avec rallonge et pilotes	38.00	0
m4597	clé 1024 mo usb 2.0	livrée avec rallonge et pilotes	76.00	3
m3454	mémoire 256 mo	mémoire de marque	34.00	0
m3536	mémoire 512 mo	mémoire de marque	59.00	0
m4810	mémoire 1024 mo	mémoire de marque	131.00	3
d0065	lecteur de disquette blanc	\N	10.00	0
d3899	lecteur de disquette noir	\N	10.00	0
c1757	lecteur de cd-rom 52x	lecteur de cd-rom standard - ide	19.00	0
c1976	lecteur de dvd 16x/48x	lecteur de cd-rom standard - ide - cdrom 48x - dvd 16x	28.00	0
g3793	graveur cd-rom ide	\N	29.00	0
g4438	graveur dvd +/- rw ide	\N	49.00	0
d4909	disque dur u-dma 7200tr - 200 go - 16 mo	\N	104.00	0
d4076	disque dur u-dma 7200tr - 250 go - 16 mo	\N	117.00	0
d4637	disque dur u-dma 7200tr - 300 go - 16 mo	\N	149.00	3
d3575	disque dur sata 7200tr - 200 go	\N	104.00	0
d4075	disque dur sata 7200tr - 250 go	\N	116.00	0
d4839	disque dur sata 7200tr - 300 go	\N	155.00	3
d4449	disque dur sata 10000tr - 74 go	\N	199.00	8
v3468	carte vidéo standard 32 mo pci	\N	76.00	8
v3545	carte vidéo standard 128 mo agp	connecteurs vga et tv	49.00	0
v4498	carte vidéo pci express 128 mo	\N	61.00	0
v4875	carte vidéo pci express 256 mo	\N	118.00	0
v4610	carte vidéo 256 mo agp	marque connue	485.00	8
e4384	écran crt 17 pouces	résolution recommandée : 1024* 768 - 85 hz	133.00	0
e4007	écran crt 19 pouces	résolution recommandée : 1280* 1024 - 90 hz	206.00	0
e4409	écran crt 22 pouces	résolution recommandée : 1920* 1440 - 85 hz	617.00	8
e3837	écran tft 17 pouces - 8 ms	\N	280.00	0
e4817	écran tft 19 pouces - 8 ms	\N	369.00	0
c4677	clavier	usb/ps2	15.00	0
c4843	clavier + souris	\N	25.00	0
s4721	souris optique	\N	15.00	0
h4766	kit haut-parleur blanc	\N	13.00	0
h4767	kit haut-parleur noir	\N	14.00	0
i4895	imprimante jet d'encre	\N	93.00	0
i4627	imprimante jet d'encre photo	qualité photo - connectique rj45 et usb	193.00	0
i4802	imprimante laser	12 pages par minute	133.00	0
i4694	imprimante laser couleur	13 pages par minute	1220.00	8
s4829	scanner standard	usb	55.00	0
s4871	scanner photo	usb	100.00	0
\.


--
-- Data for Name: service; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY service (id_service, nom) FROM stdin;
1	COMMERCIAL
2	SAV
3	ADMINISTRATIF
\.


--
-- Data for Name: vendeur; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY vendeur (id_vendeur, nom, prenom, salaire, annee_embauche, id_vendeur_responsable) FROM stdin;
1	DURENARD	Fabienne	2700.00	2004	\N
3	MAURICE	Pascal	1723.00	2005	1
6	NOEL	Florine	1310.00	2005	3
7	SERRURIER	Isabelle	1430.00	2005	1
2	PRIOUX	Sophie	1800.00	2004	1
4	DURAND	Pascal	1490.00	2005	6
5	NORO	Philippe	1280.00	2005	6
\.


--
-- Data for Name: ville; Type: TABLE DATA; Schema: public; Owner: vente_admin
--

COPY ville (id_ville, ville, id_departement) FROM stdin;
1	Reims	51
2	Charleville-Mézières	08
3	Châlons-en-Champagne	51
4	Rethel	08
\.


--
-- Name: appartient_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY appartient
    ADD CONSTRAINT appartient_pkey PRIMARY KEY (id_vendeur, id_service);


--
-- Name: client_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY client
    ADD CONSTRAINT client_pkey PRIMARY KEY (id_client);


--
-- Name: commande_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY commande
    ADD CONSTRAINT commande_pkey PRIMARY KEY (id_commande);


--
-- Name: departement_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY departement
    ADD CONSTRAINT departement_pkey PRIMARY KEY (id_departement);


--
-- Name: ligne_commande_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY ligne_commande
    ADD CONSTRAINT ligne_commande_pkey PRIMARY KEY (id_commande, id_ligne);


--
-- Name: produit_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY produit
    ADD CONSTRAINT produit_pkey PRIMARY KEY (id_produit);


--
-- Name: service_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY service
    ADD CONSTRAINT service_pkey PRIMARY KEY (id_service);


--
-- Name: vendeur_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY vendeur
    ADD CONSTRAINT vendeur_pkey PRIMARY KEY (id_vendeur);


--
-- Name: ville_pkey; Type: CONSTRAINT; Schema: public; Owner: vente_admin; Tablespace: 
--

ALTER TABLE ONLY ville
    ADD CONSTRAINT ville_pkey PRIMARY KEY (id_ville);


--
-- Name: appartient_id_service_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY appartient
    ADD CONSTRAINT appartient_id_service_fkey FOREIGN KEY (id_service) REFERENCES service(id_service) ON UPDATE CASCADE;


--
-- Name: appartient_id_vendeur_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY appartient
    ADD CONSTRAINT appartient_id_vendeur_fkey FOREIGN KEY (id_vendeur) REFERENCES vendeur(id_vendeur);


--
-- Name: client_id_ville_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY client
    ADD CONSTRAINT client_id_ville_fkey FOREIGN KEY (id_ville) REFERENCES ville(id_ville);


--
-- Name: commande_id_client_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY commande
    ADD CONSTRAINT commande_id_client_fkey FOREIGN KEY (id_client) REFERENCES client(id_client);


--
-- Name: commande_id_vendeur_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY commande
    ADD CONSTRAINT commande_id_vendeur_fkey FOREIGN KEY (id_vendeur) REFERENCES vendeur(id_vendeur);


--
-- Name: fk_vendeur_responsable; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY vendeur
    ADD CONSTRAINT fk_vendeur_responsable FOREIGN KEY (id_vendeur_responsable) REFERENCES vendeur(id_vendeur);


--
-- Name: ligne_commande_id_commande_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY ligne_commande
    ADD CONSTRAINT ligne_commande_id_commande_fkey FOREIGN KEY (id_commande) REFERENCES commande(id_commande);


--
-- Name: ligne_commande_id_produit_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY ligne_commande
    ADD CONSTRAINT ligne_commande_id_produit_fkey FOREIGN KEY (id_produit) REFERENCES produit(id_produit);


--
-- Name: ville_id_departement_fkey; Type: FK CONSTRAINT; Schema: public; Owner: vente_admin
--

ALTER TABLE ONLY ville
    ADD CONSTRAINT ville_id_departement_fkey FOREIGN KEY (id_departement) REFERENCES departement(id_departement);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--




:ugeek:

Répondre