-
-
Save Asraf2asif/7ad5ea5d6b0757be1cdc35f01225c03d to your computer and use it in GitHub Desktop.
Build a Celestial Bodies Database (freecodecamp)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- PostgreSQL database dump | |
-- | |
-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1) | |
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1) | |
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 universe; | |
-- | |
-- Name: universe; Type: DATABASE; Schema: -; Owner: freecodecamp | |
-- | |
CREATE DATABASE universe WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8'; | |
ALTER DATABASE universe OWNER TO freecodecamp; | |
\connect universe | |
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: freecodecamp | |
-- | |
CREATE TABLE public.galaxy ( | |
galaxy_id integer NOT NULL, | |
name character varying(30), | |
description text NOT NULL, | |
distance_from_earth character varying(30) NOT NULL, | |
apparent_magnitude numeric NOT NULL, | |
constellation character varying(30) | |
); | |
ALTER TABLE public.galaxy OWNER TO freecodecamp; | |
-- | |
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE SEQUENCE public.galaxy_galaxy_id_seq | |
AS integer | |
START WITH 1 | |
INCREMENT BY 1 | |
NO MINVALUE | |
NO MAXVALUE | |
CACHE 1; | |
ALTER TABLE public.galaxy_galaxy_id_seq OWNER TO freecodecamp; | |
-- | |
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp | |
-- | |
ALTER SEQUENCE public.galaxy_galaxy_id_seq OWNED BY public.galaxy.galaxy_id; | |
-- | |
-- Name: moon; Type: TABLE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE TABLE public.moon ( | |
moon_id integer NOT NULL, | |
name character varying(30), | |
orbital_in_km integer, | |
planet_id integer NOT NULL, | |
discovery_time_in_year integer, | |
discovered_by character varying(30) | |
); | |
ALTER TABLE public.moon OWNER TO freecodecamp; | |
-- | |
-- Name: moon_moon_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE SEQUENCE public.moon_moon_id_seq | |
AS integer | |
START WITH 1 | |
INCREMENT BY 1 | |
NO MINVALUE | |
NO MAXVALUE | |
CACHE 1; | |
ALTER TABLE public.moon_moon_id_seq OWNER TO freecodecamp; | |
-- | |
-- Name: moon_moon_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp | |
-- | |
ALTER SEQUENCE public.moon_moon_id_seq OWNED BY public.moon.moon_id; | |
-- | |
-- Name: planet; Type: TABLE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE TABLE public.planet ( | |
planet_id integer NOT NULL, | |
name character varying(30), | |
mass numeric, | |
oribital_period_in_years numeric, | |
has_ring boolean NOT NULL, | |
rotation_period_in_days numeric, | |
star_id integer | |
); | |
ALTER TABLE public.planet OWNER TO freecodecamp; | |
-- | |
-- Name: planet_more_info; Type: TABLE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE TABLE public.planet_more_info ( | |
planet_more_info_id integer NOT NULL, | |
atmosphere character varying(30), | |
planet_id integer, | |
name character varying(30) NOT NULL | |
); | |
ALTER TABLE public.planet_more_info OWNER TO freecodecamp; | |
-- | |
-- Name: planet_more_info_planet_more_info_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE SEQUENCE public.planet_more_info_planet_more_info_id_seq | |
AS integer | |
START WITH 1 | |
INCREMENT BY 1 | |
NO MINVALUE | |
NO MAXVALUE | |
CACHE 1; | |
ALTER TABLE public.planet_more_info_planet_more_info_id_seq OWNER TO freecodecamp; | |
-- | |
-- Name: planet_more_info_planet_more_info_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp | |
-- | |
ALTER SEQUENCE public.planet_more_info_planet_more_info_id_seq OWNED BY public.planet_more_info.planet_more_info_id; | |
-- | |
-- Name: planet_planet_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE SEQUENCE public.planet_planet_id_seq | |
AS integer | |
START WITH 1 | |
INCREMENT BY 1 | |
NO MINVALUE | |
NO MAXVALUE | |
CACHE 1; | |
ALTER TABLE public.planet_planet_id_seq OWNER TO freecodecamp; | |
-- | |
-- Name: planet_planet_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp | |
-- | |
ALTER SEQUENCE public.planet_planet_id_seq OWNED BY public.planet.planet_id; | |
-- | |
-- Name: star; Type: TABLE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE TABLE public.star ( | |
star_id integer NOT NULL, | |
name character varying(30), | |
description text, | |
distance integer, | |
neg_abs_mag boolean, | |
abs_mag integer, | |
galaxy_id integer NOT NULL, | |
distance_unit character varying | |
); | |
ALTER TABLE public.star OWNER TO freecodecamp; | |
-- | |
-- Name: star_star_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp | |
-- | |
CREATE SEQUENCE public.star_star_id_seq | |
AS integer | |
START WITH 1 | |
INCREMENT BY 1 | |
NO MINVALUE | |
NO MAXVALUE | |
CACHE 1; | |
ALTER TABLE public.star_star_id_seq OWNER TO freecodecamp; | |
-- | |
-- Name: star_star_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp | |
-- | |
ALTER SEQUENCE public.star_star_id_seq OWNED BY public.star.star_id; | |
-- | |
-- Name: galaxy galaxy_id; Type: DEFAULT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.galaxy ALTER COLUMN galaxy_id SET DEFAULT nextval('public.galaxy_galaxy_id_seq'::regclass); | |
-- | |
-- Name: moon moon_id; Type: DEFAULT; Schema: public; Owner: freecodecamp | |
-- | |
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: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet ALTER COLUMN planet_id SET DEFAULT nextval('public.planet_planet_id_seq'::regclass); | |
-- | |
-- Name: planet_more_info planet_more_info_id; Type: DEFAULT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet_more_info ALTER COLUMN planet_more_info_id SET DEFAULT nextval('public.planet_more_info_planet_more_info_id_seq'::regclass); | |
-- | |
-- Name: star star_id; Type: DEFAULT; Schema: public; Owner: freecodecamp | |
-- | |
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: freecodecamp | |
-- | |
INSERT INTO public.galaxy VALUES (1, 'Milky Way', 'The 1alaxy containing Sun and its soler system and therefore Earth', '0', -6.5, 'Sagittarius'); | |
INSERT INTO public.galaxy VALUES (2, 'Large Magellanic Cloud', 'Visible only from the southern hemisphere', '160 kly (49 kpc)', 0.9, 'Dorado/Mensa'); | |
INSERT INTO public.galaxy VALUES (3, 'Small Magellanic Cloud', 'Visible only from the southern hemisphere', '200 kly (61 kpc)', 2.7, 'Tucana'); | |
INSERT INTO public.galaxy VALUES (4, 'Andromeda Galaxy', '', '2.5 Mly (770 kpc)', 3.4, 'Andromeda'); | |
INSERT INTO public.galaxy VALUES (5, 'Triangulum Galaxy', ' ', '2.9 Mly (890 kpc)', 5.7, 'Tringulum'); | |
INSERT INTO public.galaxy VALUES (6, 'Centaurus A', ' ', '13.7 Mly (4.4 mpc)', 6.84, 'Centaurus'); | |
-- | |
-- Data for Name: moon; Type: TABLE DATA; Schema: public; Owner: freecodecamp | |
-- | |
INSERT INTO public.moon VALUES (1, 'Moon', 384399, 3, 0, ''); | |
INSERT INTO public.moon VALUES (2, 'Phobos', 9380, 4, 0, ''); | |
INSERT INTO public.moon VALUES (3, 'Deimos', 23460, 4, 1877, 'Hall'); | |
INSERT INTO public.moon VALUES (4, 'lo', 421800, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (5, 'Europa', 671100, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (6, 'Ganymede', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (8, '1', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (9, '2', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (10, '3', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (11, '4', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (12, '5', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (13, '6', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (14, '7', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (15, '8', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (16, '9', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (76, '10', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (18, '11', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (19, '12', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (20, '13', 1070400, 5, 1610, 'Galileo'); | |
INSERT INTO public.moon VALUES (7, '14', 1070400, 5, 1610, 'Galileo'); | |
-- | |
-- Data for Name: planet; Type: TABLE DATA; Schema: public; Owner: freecodecamp | |
-- | |
INSERT INTO public.planet VALUES (1, 'Mercury', 0.06, 0.24, false, 58.65, 1); | |
INSERT INTO public.planet VALUES (2, 'Venus', 0.81, 0.62, false, 243.02, 1); | |
INSERT INTO public.planet VALUES (3, 'Earth', 1.00, 1.00, false, 1.00, 1); | |
INSERT INTO public.planet VALUES (4, 'Mars', 0.11, 1.88, false, 1.03, 1); | |
INSERT INTO public.planet VALUES (5, 'Jupitar', 317.83, 11.86, false, 0.41, 1); | |
INSERT INTO public.planet VALUES (6, 'Saturn', 95.16, 29.45, true, 0.44, 1); | |
INSERT INTO public.planet VALUES (7, 'Uranus', 14.54, 84.02, true, 0.72, 1); | |
INSERT INTO public.planet VALUES (8, 'Neptune', 17.15, 164.79, true, 0.67, 1); | |
INSERT INTO public.planet VALUES (9, 'Ceres', 0.00016, 4.60, false, 0.38, 1); | |
INSERT INTO public.planet VALUES (10, 'Orcus', 0.0001, 247.5, false, 0, 1); | |
INSERT INTO public.planet VALUES (11, 'Pluto', 0.0022, 283.8, false, 6.39, 1); | |
INSERT INTO public.planet VALUES (12, 'Haumea', 0.0007, 283.8, true, 0.16, 1); | |
-- | |
-- Data for Name: planet_more_info; Type: TABLE DATA; Schema: public; Owner: freecodecamp | |
-- | |
INSERT INTO public.planet_more_info VALUES (1, 'minimal', 1, 'Mercury'); | |
INSERT INTO public.planet_more_info VALUES (2, 'co2, n2', 2, 'Venus'); | |
INSERT INTO public.planet_more_info VALUES (3, 'n2, o2, ar', 3, 'Earth'); | |
-- | |
-- Data for Name: star; Type: TABLE DATA; Schema: public; Owner: freecodecamp | |
-- | |
INSERT INTO public.star VALUES (1, 'Batelgeuse', ' ', 548, true, 5, 1, 'ly'); | |
INSERT INTO public.star VALUES (2, 'UY Scuti', ' ', 9500, true, 6, 2, 'ly'); | |
INSERT INTO public.star VALUES (3, 'Bat99-7', ' ', 50000, true, 5, 3, 'pc'); | |
INSERT INTO public.star VALUES (4, 'HD-5980', ' ', 200000, true, 8, 4, 'ly'); | |
INSERT INTO public.star VALUES (5, 'alpha Andromedae', ' ', 97, true, 1, 5, 'ly'); | |
INSERT INTO public.star VALUES (6, 'alpha Trianguli', ' ', 63, false, 2, 6, 'ly'); | |
-- | |
-- Name: galaxy_galaxy_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp | |
-- | |
SELECT pg_catalog.setval('public.galaxy_galaxy_id_seq', 1, false); | |
-- | |
-- Name: moon_moon_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp | |
-- | |
SELECT pg_catalog.setval('public.moon_moon_id_seq', 1, false); | |
-- | |
-- Name: planet_more_info_planet_more_info_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp | |
-- | |
SELECT pg_catalog.setval('public.planet_more_info_planet_more_info_id_seq', 1, false); | |
-- | |
-- Name: planet_planet_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp | |
-- | |
SELECT pg_catalog.setval('public.planet_planet_id_seq', 1, false); | |
-- | |
-- Name: star_star_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp | |
-- | |
SELECT pg_catalog.setval('public.star_star_id_seq', 1, false); | |
-- | |
-- Name: galaxy galaxy_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.galaxy | |
ADD CONSTRAINT galaxy_name_key UNIQUE (name); | |
-- | |
-- Name: galaxy galaxy_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.galaxy | |
ADD CONSTRAINT galaxy_pkey PRIMARY KEY (galaxy_id); | |
-- | |
-- Name: moon moon_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.moon | |
ADD CONSTRAINT moon_name_key UNIQUE (name); | |
-- | |
-- Name: moon moon_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.moon | |
ADD CONSTRAINT moon_pkey PRIMARY KEY (moon_id); | |
-- | |
-- Name: planet palnet_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet | |
ADD CONSTRAINT palnet_name_key UNIQUE (name); | |
-- | |
-- Name: planet palnet_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet | |
ADD CONSTRAINT palnet_pkey PRIMARY KEY (planet_id); | |
-- | |
-- Name: planet_more_info planet_more_info_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet_more_info | |
ADD CONSTRAINT planet_more_info_name_key UNIQUE (name); | |
-- | |
-- Name: planet_more_info planet_more_info_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet_more_info | |
ADD CONSTRAINT planet_more_info_pkey PRIMARY KEY (planet_more_info_id); | |
-- | |
-- Name: star star_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.star | |
ADD CONSTRAINT star_name_key UNIQUE (name); | |
-- | |
-- Name: star star_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.star | |
ADD CONSTRAINT star_pkey PRIMARY KEY (star_id); | |
-- | |
-- Name: moon moon_planet_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.moon | |
ADD CONSTRAINT moon_planet_id_fkey FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id); | |
-- | |
-- Name: planet palnet_star_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet | |
ADD CONSTRAINT palnet_star_id_fkey FOREIGN KEY (star_id) REFERENCES public.star(star_id); | |
-- | |
-- Name: planet_more_info planet_more_info_planet_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.planet_more_info | |
ADD CONSTRAINT planet_more_info_planet_id_fkey FOREIGN KEY (planet_id) REFERENCES public.planet(planet_id); | |
-- | |
-- Name: star star_galaxy_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp | |
-- | |
ALTER TABLE ONLY public.star | |
ADD CONSTRAINT star_galaxy_id_fkey FOREIGN KEY (galaxy_id) REFERENCES public.galaxy(galaxy_id); | |
-- | |
-- PostgreSQL database dump complete | |
-- | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment