Danilo Alessandro Vinciprova

Danilo Alessandro Vinciprova

AI-Assisted Web Developer · React · Next.js · Claude Code

Ho imparato a pensare al costruire come a parti che devono incastrarsi tra loro.

Competenza 02 / 03

SQL e PostgreSQL

Due schemi di database disegnati dai loro file SQL, e le query di uno script Bash.

  • SQL, PostgreSQL e Bash(verificato)
(verificato) indica una competenza verificata

Panoramica

Due progetti di database di freeCodeCamp, dai miei repository pubblici: uno schema PostgreSQL di galassie, stelle, pianeti e lune, e un’agenda appuntamenti per un salone scritta in Bash sopra psql. I diagrammi sono disegnati dai file SQL stessi.

In questa pagina

  1. 02 Schema: due diagrammi entità-relazione, disegnati in fase di build dai file SQL
  2. 03 Query: le righe di query dello script del salone
  3. 04 Prove: i 2 certificati Relational Database e i 2 repository

SQL, PostgreSQL e Bash (verificato)

Vedi la prova

  1. Affermazione

    SQL, PostgreSQL e Bash

    ✓ Verificato

  2. Prove

  3. Risultato

    Due certificazioni freeCodeCamp Relational Database (una con 5 progetti) e due progetti pubblici di database su GitHub.

Chi ha scritto questo codice

“Ho scritto io il codice degli esercizi freeCodeCamp mostrato nelle pagine delle competenze.”

Vedi la prova

  1. Affermazione

    Ho scritto io il codice degli esercizi freeCodeCamp mostrato nelle pagine delle competenze.

    Dichiarato da Danilo

  2. Prove

  3. Risultato

    Il codice è copiato testualmente da repository pubblici, dove i commit sono stati fatti dall’account GitHub di Danilo (zurukzuruk1). Che l’abbia scritto lui stesso è una sua dichiarazione del 2 ott 2026.

Schema

Entrambi i diagrammi vengono generati quando il sito viene costruito, leggendo le istruzioni CREATE TABLE e i vincoli sulle chiavi dei file SQL testuali: colonne e tipi, chiavi primarie, colonne uniche e chiavi esterne. Niente è disegnato a mano.

Database dei corpi celesti: universe.sql

Livello di prova: Prova esistente · disegnato dal file

5 tabelle · 4 chiavi esterne · 27 colonne

galaxy_typePKgalaxy_type_idintegerUQnamecharacter varying(255)descriptiontextgalaxyPKgalaxy_idintegerUQnamecharacter varying(255)FKgalaxy_type_idintegerdistance_from_earthnumerichas_dark_matterbooleandiameter_light_yearsintegerplanetPKplanet_idintegerUQnamecharacter varying(255)FKstar_idintegerhas_ringsbooleanorbital_period_daysnumericmoons_countintegerstarPKstar_idintegerUQnamecharacter varying(255)FKgalaxy_idintegermass_solar_massesnumericis_variablebooleanage_million_yearsintegermoonPKmoon_idintegerUQnamecharacter varying(255)FKplanet_idintegerhas_atmospherebooleandiameter_kmnumericdiscovery_yearintegergalaxy_typePKgalaxy_type_idintegerUQnamecharacter varying(255)descriptiontextgalaxyPKgalaxy_idintegerUQnamecharacter varying(255)FKgalaxy_type_idintegerdistance_from_earthnumerichas_dark_matterbooleandiameter_light_yearsintegerstarPKstar_idintegerUQnamecharacter varying(255)FKgalaxy_idintegermass_solar_massesnumericis_variablebooleanage_million_yearsintegerplanetPKplanet_idintegerUQnamecharacter varying(255)FKstar_idintegerhas_ringsbooleanorbital_period_daysnumericmoons_countintegermoonPKmoon_idintegerUQnamecharacter varying(255)FKplanet_idintegerhas_atmospherebooleandiameter_kmnumericdiscovery_yearinteger
Diagramma entità-relazione di universe.sql: 5 tabelle e 4 chiavi esterne, lette dal file quando questo sito è stato generato. Ogni linea unisce una colonna chiave esterna (○) alla colonna a cui fa riferimento (●). PK chiave primaria, FK chiave esterna, UQ unica.
Tabelle, colonne e chiavi come testo

galaxy

ColonnaTipoNon nulloChiavi
galaxy_idintegersìchiave primaria
namecharacter varying(255)sìunica
galaxy_type_idintegernochiave esterna
distance_from_earthnumericsì–
has_dark_matterbooleanno–
diameter_light_yearsintegerno–

galaxy_type

ColonnaTipoNon nulloChiavi
galaxy_type_idintegersìchiave primaria
namecharacter varying(255)sìunica
descriptiontextsì–

moon

ColonnaTipoNon nulloChiavi
moon_idintegersìchiave primaria
namecharacter varying(255)sìunica
planet_idintegersìchiave esterna
has_atmospherebooleanno–
diameter_kmnumericno–
discovery_yearintegerno–

planet

ColonnaTipoNon nulloChiavi
planet_idintegersìchiave primaria
namecharacter varying(255)sìunica
star_idintegersìchiave esterna
has_ringsbooleanno–
orbital_period_daysnumericno–
moons_countintegerno–

star

ColonnaTipoNon nulloChiavi
star_idintegersìchiave primaria
namecharacter varying(255)sìunica
galaxy_idintegersìchiave esterna
mass_solar_massesnumericno–
is_variablebooleanno–
age_million_yearsintegerno–

Chiavi esterne

ColonnaRiferimenti
galaxy.galaxy_type_idgalaxy_type.galaxy_type_id
moon.planet_idplanet.planet_id
planet.star_idstar.star_id
star.galaxy_idgalaxy.galaxy_id

universe.sql righe 1–464

Raw: testo di universe.sql, righe 1–464Repo: repository celestial-bodies-database su GitHub
Mostra tutto il file (464 righe)
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)
-- Dumped by pg_dump version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: galaxy; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.galaxy (
    galaxy_id integer NOT NULL,
    name character varying(255) NOT NULL,
    galaxy_type_id integer,
    distance_from_earth numeric NOT NULL,
    has_dark_matter boolean,
    diameter_light_years integer
);


--
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.galaxy_galaxy_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.galaxy_galaxy_id_seq OWNED BY public.galaxy.galaxy_id;


--
-- Name: galaxy_type; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.galaxy_type (
    galaxy_type_id integer NOT NULL,
    name character varying(255) NOT NULL,
    description text NOT NULL
);


--
-- Name: galaxy_type_galaxy_type_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.galaxy_type_galaxy_type_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: galaxy_type_galaxy_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.galaxy_type_galaxy_type_id_seq OWNED BY public.galaxy_type.galaxy_type_id;


--
-- Name: moon; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.moon (
    moon_id integer NOT NULL,
    name character varying(255) NOT NULL,
    planet_id integer NOT NULL,
    has_atmosphere boolean,
    diameter_km numeric,
    discovery_year integer
);


--
-- Name: moon_moon_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.moon_moon_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: moon_moon_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.moon_moon_id_seq OWNED BY public.moon.moon_id;


--
-- Name: planet; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.planet (
    planet_id integer NOT NULL,
    name character varying(255) NOT NULL,
    star_id integer NOT NULL,
    has_rings boolean,
    orbital_period_days numeric,
    moons_count integer
);


--
-- Name: planet_planet_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.planet_planet_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: planet_planet_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.planet_planet_id_seq OWNED BY public.planet.planet_id;


--
-- Name: star; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.star (
    star_id integer NOT NULL,
    name character varying(255) NOT NULL,
    galaxy_id integer NOT NULL,
    mass_solar_masses numeric,
    is_variable boolean,
    age_million_years integer
);


--
-- Name: star_star_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.star_star_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: star_star_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.star_star_id_seq OWNED BY public.star.star_id;


--
-- Name: galaxy galaxy_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy ALTER COLUMN galaxy_id SET DEFAULT nextval('public.galaxy_galaxy_id_seq'::regclass);


--
-- Name: galaxy_type galaxy_type_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy_type ALTER COLUMN galaxy_type_id SET DEFAULT nextval('public.galaxy_type_galaxy_type_id_seq'::regclass);


--
-- Name: moon moon_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.moon ALTER COLUMN moon_id SET DEFAULT nextval('public.moon_moon_id_seq'::regclass);


--
-- Name: planet planet_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.planet ALTER COLUMN planet_id SET DEFAULT nextval('public.planet_planet_id_seq'::regclass);


--
-- Name: star star_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.star ALTER COLUMN star_id SET DEFAULT nextval('public.star_star_id_seq'::regclass);


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

COPY public.galaxy (galaxy_id, name, galaxy_type_id, distance_from_earth, has_dark_matter, diameter_light_years) FROM stdin;
1	Milky Way	1	0	t	105700
2	Andromeda	1	2537000	t	220000
3	Triangulum	1	2723000	t	60000
4	Whirlpool	1	23160000	t	76000
5	Cartwheel	3	500000000	t	150000
6	Sombrero	4	29350000	t	50000
\.


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

COPY public.galaxy_type (galaxy_type_id, name, description) FROM stdin;
1	Spiral	A galaxy with a flat, rotating disk containing stars, gas and dust, and a central concentration of stars
2	Elliptical	A galaxy with an ellipsoidal shape and a smooth, nearly featureless brightness profile
3	Irregular	A galaxy that does not have a distinct regular shape
4	Lenticular	A galaxy intermediate between elliptical and spiral galaxies
\.


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

COPY public.moon (moon_id, name, planet_id, has_atmosphere, diameter_km, discovery_year) FROM stdin;
1	The Moon	3	f	3474.8	\N
2	Phobos	4	f	22.2	1877
3	Deimos	4	f	12.4	1877
4	Io	5	f	3643.2	1610
5	Europa	5	f	3121.6	1610
6	Ganymede	5	t	5268.2	1610
7	Callisto	5	f	4820.6	1610
8	Amalthea	5	f	167	1892
9	Titan	6	t	5149.5	1655
10	Rhea	6	f	1527	1672
11	Iapetus	6	f	1469	1671
12	Dione	6	f	1123	1684
13	Tethys	6	f	1062	1684
14	Enceladus	6	f	504	1789
15	Titania	7	f	1578	1787
16	Oberon	7	f	1523	1787
17	Umbriel	7	f	1169	1851
18	Ariel	7	f	1158	1851
19	Miranda	7	f	471	1948
20	Triton	8	t	2706.8	1846
21	Nereid	8	f	340	1949
\.


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

COPY public.planet (planet_id, name, star_id, has_rings, orbital_period_days, moons_count) FROM stdin;
1	Mercury	1	f	88	0
2	Venus	1	f	224.7	0
3	Earth	1	f	365.25	1
4	Mars	1	f	687	2
5	Jupiter	1	t	4331	4
6	Saturn	1	t	10747	5
7	Uranus	1	t	30589	3
8	Neptune	1	t	59800	2
9	Proxima b	6	f	11.2	0
10	Sirius Ab	2	f	300	0
11	Rigel b	4	f	500	0
12	Centauri Bb	5	f	3.2	0
\.


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

COPY public.star (star_id, name, galaxy_id, mass_solar_masses, is_variable, age_million_years) FROM stdin;
1	Sun	1	1.0	f	4600
2	Sirius	1	2.02	f	242
3	Betelgeuse	1	16.5	t	10
4	Rigel	1	21	t	8
5	Alpha Centauri A	1	1.1	f	4850
6	Proxima Centauri	1	0.12	t	4850
\.


--
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.galaxy_galaxy_id_seq', 6, true);


--
-- Name: galaxy_type_galaxy_type_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.galaxy_type_galaxy_type_id_seq', 4, true);


--
-- Name: moon_moon_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.moon_moon_id_seq', 21, true);


--
-- Name: planet_planet_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.planet_planet_id_seq', 12, true);


--
-- Name: star_star_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
--

SELECT pg_catalog.setval('public.star_star_id_seq', 6, true);


--
-- Name: galaxy galaxy_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_name_key UNIQUE (name);


--
-- Name: galaxy galaxy_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_pkey PRIMARY KEY (galaxy_id);


--
-- Name: galaxy_type galaxy_type_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy_type
    ADD CONSTRAINT galaxy_type_name_key UNIQUE (name);


--
-- Name: galaxy_type galaxy_type_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy_type
    ADD CONSTRAINT galaxy_type_pkey PRIMARY KEY (galaxy_type_id);


--
-- Name: moon moon_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_name_key UNIQUE (name);


--
-- Name: moon moon_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_pkey PRIMARY KEY (moon_id);


--
-- Name: planet planet_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_name_key UNIQUE (name);


--
-- Name: planet planet_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_pkey PRIMARY KEY (planet_id);


--
-- Name: star star_name_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_name_key UNIQUE (name);


--
-- Name: star star_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_pkey PRIMARY KEY (star_id);


--
-- Name: galaxy galaxy_galaxy_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_galaxy_type_id_fkey FOREIGN KEY (galaxy_type_id) REFERENCES public.galaxy_type(galaxy_type_id);


--
-- Name: moon moon_planet_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_planet_id_fkey FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id);


--
-- Name: planet planet_star_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_star_id_fkey FOREIGN KEY (star_id) REFERENCES public.star(star_id);


--
-- Name: star star_galaxy_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_galaxy_id_fkey FOREIGN KEY (galaxy_id) REFERENCES public.galaxy(galaxy_id);


--
-- PostgreSQL database dump complete
--

Livello di prova: Prova esistente

Database dei corpi celesti: l’intero file SQL · copiato il 2 ottobre 2026 · sha256 5404507373b9…

Agenda appuntamenti per un salone: salon.sql

Livello di prova: Prova esistente · disegnato dal file

3 tabelle · 2 chiavi esterne · 9 colonne

customersPKcustomer_idintegerUQphonecharacter varying(20)namecharacter varying(50)appointmentsPKappointment_idintegerFKcustomer_idintegerFKservice_idintegertimecharacter varying(20)servicesPKservice_idintegernamecharacter varying(50)customersPKcustomer_idintegerUQphonecharacter varying(20)namecharacter varying(50)appointmentsPKappointment_idintegerFKcustomer_idintegerFKservice_idintegertimecharacter varying(20)servicesPKservice_idintegernamecharacter varying(50)
Diagramma entità-relazione di salon.sql: 3 tabelle e 2 chiavi esterne, lette dal file quando questo sito è stato generato. Ogni linea unisce una colonna chiave esterna (○) alla colonna a cui fa riferimento (●). PK chiave primaria, FK chiave esterna, UQ unica.
Tabelle, colonne e chiavi come testo

appointments

ColonnaTipoNon nulloChiavi
appointment_idintegersìchiave primaria
customer_idintegersìchiave esterna
service_idintegersìchiave esterna
timecharacter varying(20)sì–

customers

ColonnaTipoNon nulloChiavi
customer_idintegersìchiave primaria
phonecharacter varying(20)sìunica
namecharacter varying(50)sì–

services

ColonnaTipoNon nulloChiavi
service_idintegersìchiave primaria
namecharacter varying(50)sì–

Chiavi esterne

ColonnaRiferimenti
appointments.customer_idcustomers.customer_id
appointments.service_idservices.service_id

salon.sql righe 1–267

Raw: testo di salon.sql, righe 1–267Repo: repository salon-appointment-scheduler su GitHub
Mostra tutto il file (267 righe)
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)
-- Dumped by pg_dump version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE salon;
--
-- Name: salon; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE salon WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE salon OWNER TO freecodecamp;

\connect salon

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: appointments; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.appointments (
    appointment_id integer NOT NULL,
    customer_id integer NOT NULL,
    service_id integer NOT NULL,
    "time" character varying(20) NOT NULL
);


ALTER TABLE public.appointments OWNER TO freecodecamp;

--
-- Name: appointments_appointment_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.appointments_appointment_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.appointments_appointment_id_seq OWNER TO freecodecamp;

--
-- Name: appointments_appointment_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.appointments_appointment_id_seq OWNED BY public.appointments.appointment_id;


--
-- Name: customers; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.customers (
    customer_id integer NOT NULL,
    phone character varying(20) NOT NULL,
    name character varying(50) NOT NULL
);


ALTER TABLE public.customers OWNER TO freecodecamp;

--
-- Name: customers_customer_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.customers_customer_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.customers_customer_id_seq OWNER TO freecodecamp;

--
-- Name: customers_customer_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.customers_customer_id_seq OWNED BY public.customers.customer_id;


--
-- Name: services; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.services (
    service_id integer NOT NULL,
    name character varying(50) NOT NULL
);


ALTER TABLE public.services OWNER TO freecodecamp;

--
-- Name: services_service_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.services_service_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.services_service_id_seq OWNER TO freecodecamp;

--
-- Name: services_service_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.services_service_id_seq OWNED BY public.services.service_id;


--
-- Name: appointments appointment_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments ALTER COLUMN appointment_id SET DEFAULT nextval('public.appointments_appointment_id_seq'::regclass);


--
-- Name: customers customer_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.customers ALTER COLUMN customer_id SET DEFAULT nextval('public.customers_customer_id_seq'::regclass);


--
-- Name: services service_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.services ALTER COLUMN service_id SET DEFAULT nextval('public.services_service_id_seq'::regclass);


--
-- Data for Name: appointments; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--



--
-- Data for Name: customers; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--



--
-- Data for Name: services; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.services VALUES (1, 'cut');
INSERT INTO public.services VALUES (2, 'color');
INSERT INTO public.services VALUES (3, 'perm');
INSERT INTO public.services VALUES (4, 'style');
INSERT INTO public.services VALUES (5, 'trim');


--
-- Name: appointments_appointment_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.appointments_appointment_id_seq', 6, true);


--
-- Name: customers_customer_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.customers_customer_id_seq', 5, true);


--
-- Name: services_service_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.services_service_id_seq', 5, true);


--
-- Name: appointments appointments_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_pkey PRIMARY KEY (appointment_id);


--
-- Name: customers customers_phone_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_phone_key UNIQUE (phone);


--
-- Name: customers customers_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (customer_id);


--
-- Name: services services_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.services
    ADD CONSTRAINT services_pkey PRIMARY KEY (service_id);


--
-- Name: appointments appointments_customer_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customers(customer_id);


--
-- Name: appointments appointments_service_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_service_id_fkey FOREIGN KEY (service_id) REFERENCES public.services(service_id);


--
-- PostgreSQL database dump complete
--

Livello di prova: Prova esistente

Agenda appuntamenti per un salone: l’intero file SQL · copiato il 2 ottobre 2026 · sha256 9bca9e9176eb…

Query

Lo script del salone invia il suo SQL a PostgreSQL tramite psql: 6 istruzioni (SELECT, INSERT) in queste righe, dall’elenco dei servizi alla prenotazione dell’appuntamento.

Le istruzioni, in ordine

Livello di prova: Prova esistente

  1. riga 14SELECT service_id, name FROM services ORDER BY service_id
  2. riga 31SELECT name FROM services WHERE service_id = $SERVICE_ID_SELECTED
  3. riga 42SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'
  4. riga 47INSERT INTO customers(phone, name) VALUES('$CUSTOMER_PHONE', '$CUSTOMER_NAME')
  5. riga 51SELECT customer_id FROM customers WHERE phone = '$CUSTOMER_PHONE'
  6. riga 57INSERT INTO appointments(customer_id, service_id, time) VALUES($CUSTOMER_ID, $SERVICE_ID_SELECTED, '$SERVICE_TIME')

Esercizio del corso: l’input dell’utente va direttamente nella stringa SQL; un codice di produzione userebbe query parametrizzate.

salon.sh righe 14–57

Raw: testo di salon.sh, righe 14–57Repo: repository salon-appointment-scheduler su GitHub
  SERVICES=$($PSQL "SELECT service_id, name FROM services ORDER BY service_id")
  echo "$SERVICES" | while read SERVICE_ID BAR NAME
  do
    if [[ -n $SERVICE_ID ]]
    then
      echo "$SERVICE_ID) $NAME"
    fi
  done

  read SERVICE_ID_SELECTED

  if [[ ! $SERVICE_ID_SELECTED =~ ^[0-9]+$ ]]
  then
    MAIN_MENU "I could not find that service. What would you like today?"
    return
  fi

  SERVICE_NAME=$($PSQL "SELECT name FROM services WHERE service_id = $SERVICE_ID_SELECTED")
  if [[ -z $SERVICE_NAME ]]
  then
    MAIN_MENU "I could not find that service. What would you like today?"
    return
  fi
  SERVICE_NAME=$(echo $SERVICE_NAME | sed -E 's/^ *| *$//g')

  echo -e "\nWhat's your phone number?"
  read CUSTOMER_PHONE

  CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone = '$CUSTOMER_PHONE'")
  if [[ -z $CUSTOMER_NAME ]]
  then
    echo -e "\nI don't have a record for that phone number, what's your name?"
    read CUSTOMER_NAME
    INSERT_CUSTOMER_RESULT=$($PSQL "INSERT INTO customers(phone, name) VALUES('$CUSTOMER_PHONE', '$CUSTOMER_NAME')")
  fi
  CUSTOMER_NAME=$(echo $CUSTOMER_NAME | sed -E 's/^ *| *$//g')

  CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone = '$CUSTOMER_PHONE'")
  CUSTOMER_ID=$(echo $CUSTOMER_ID | sed -E 's/^ *| *$//g')

  echo -e "\nWhat time would you like your $SERVICE_NAME, $CUSTOMER_NAME?"
  read SERVICE_TIME

  INSERT_APPOINTMENT_RESULT=$($PSQL "INSERT INTO appointments(customer_id, service_id, time) VALUES($CUSTOMER_ID, $SERVICE_ID_SELECTED, '$SERVICE_TIME')")

Livello di prova: Prova esistente

Agenda appuntamenti per un salone: le righe di query dello script · copiato il 2 ottobre 2026 · sha256 5994d6b57c0e…

Prove

Le due certificazioni Relational Database (un esame, e una certificazione con 5 progetti) e i due repository pubblici da cui vengono i file qui sopra.

Certificati

Repository