fcc-celestial.sql

fcc-celestial.sql

create table galaxy(
  galaxy_id serial primary key,
  name varchar(30) not null unique,
  is_milky boolean not null default false,
  age int,
  distance numeric
);

create table star(
  star_id serial primary key,
  name varchar(30) not null unique,
  galaxy_id int references galaxy(galaxy_id),
  is_dying boolean not null default false,
  age int
);

create table planet(
  planet_id serial primary key,
  name varchar(30) not null unique,
  star_id int references star(star_id),
  has_water boolean not null default false,
  age int
);

create table moon(
  moon_id serial primary key,
  name varchar(30) not null unique,
  planet_id int references planet(planet_id),
  is_sologuy boolean not null default false,
  age int
);

create table blackhole(
  blackhole_id serial primary key,
  name varchar(30) not null unique,
  is_black boolean not null default false,
  items_swallowed text,
  age int
);


---

-- Galaxies
INSERT INTO galaxy (name, is_milky, age, distance) VALUES
  ('Milky Way',      true,  13600, 0),
  ('Andromeda',      false, 10100, 2537000),
  ('Triangulum',     false,  8000, 2730000),
  ('Whirlpool',      false, 13500, 23000000),
  ('Sombrero',       false, 13250, 28000000),
  ('Cartwheel',      false,  7000, 500000000);

-- Stars
INSERT INTO star (name, galaxy_id, is_dying, age) VALUES
  ('Sun',           1, false, 4600),
  ('Sirius',        1, false, 242),
  ('Betelgeuse',    1, true,  8000),
  ('Proxima Cen',   1, false, 4850),
  ('Alpheratz',     2, false, 200),
  ('Mirach',        2, false, 1000),
  ('Andromeda P32', 2, true,  12000);

-- Planets
INSERT INTO planet (name, star_id, has_water, age) VALUES
  ('Mercury',    1, false, 4500),
  ('Venus',      1, false, 4500),
  ('Earth',      1, true,  4500),
  ('Mars',       1, false, 4500),
  ('Jupiter',    1, false, 4500),
  ('Saturn',     1, false, 4500),
  ('Uranus',     1, false, 4500),
  ('Neptune',    1, false, 4500),
  ('Kepler-22b', 4, true,  4000),
  ('Sirius Ab1', 2, false, 300),
  ('Sirius Ab2', 2, false, 300),
  ('Mir-Alpha',  5, true,  500),
  ('Mir-Beta',   5, false, 500);

-- Moons
INSERT INTO moon (name, planet_id, is_sologuy, age) VALUES
  ('Moon',        3,  true,  4500),
  ('Phobos',      4,  false, 4500),
  ('Deimos',      4,  false, 4500),
  ('Io',          5,  false, 4500),
  ('Europa',      5,  false, 4500),
  ('Ganymede',    5,  false, 4500),
  ('Callisto',    5,  false, 4500),
  ('Amalthea',    5,  false, 4500),
  ('Titan',       6,  false, 4500),
  ('Enceladus',   6,  false, 4500),
  ('Mimas',       6,  false, 4500),
  ('Rhea',        6,  false, 4500),
  ('Dione',       6,  false, 4500),
  ('Titania',     7,  false, 4200),
  ('Oberon',      7,  false, 4200),
  ('Ariel',       7,  false, 4200),
  ('Triton',      8,  true,  3800),
  ('Nereid',      8,  false, 3800),
  ('K22-Luna',    9,  true,  3900),
  ('Mir-Moon-I',  12, true,  600);

-- Black holes
INSERT INTO blackhole (name, is_black, items_swallowed, age) VALUES
  ('Sagittarius A*', true,  'gas clouds, rogue stars',     13000),
  ('Cygnus X-1',     true,  'stellar winds, accretion disk', 5000),
  ('M87 Core',       false, 'entire star clusters',        13200);

universe.sql

--
-- 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 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: blackhole; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.blackhole (
    blackhole_id integer NOT NULL,
    name character varying(30) NOT NULL,
    is_black boolean DEFAULT false NOT NULL,
    items_swallowed text,
    age integer
);


ALTER TABLE public.blackhole OWNER TO freecodecamp;

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

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


ALTER TABLE public.blackhole_blackhole_id_seq OWNER TO freecodecamp;

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

ALTER SEQUENCE public.blackhole_blackhole_id_seq OWNED BY public.blackhole.blackhole_id;


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

CREATE TABLE public.galaxy (
    galaxy_id integer NOT NULL,
    name character varying(30) NOT NULL,
    is_milky boolean DEFAULT false NOT NULL,
    age integer,
    distance numeric
);


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) NOT NULL,
    planet_id integer,
    is_sologuy boolean DEFAULT false NOT NULL,
    age integer
);


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) NOT NULL,
    star_id integer,
    has_water boolean DEFAULT false NOT NULL,
    age integer
);


ALTER TABLE public.planet OWNER TO freecodecamp;

--
-- 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) NOT NULL,
    galaxy_id integer,
    is_dying boolean DEFAULT false NOT NULL,
    age integer
);


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: blackhole blackhole_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.blackhole ALTER COLUMN blackhole_id SET DEFAULT nextval('public.blackhole_blackhole_id_seq'::regclass);


--
-- 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: 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: blackhole; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.blackhole VALUES (1, 'Sagittarius A*', true, 'gas clouds, rogue stars', 13000);
INSERT INTO public.blackhole VALUES (2, 'Cygnus X-1', true, 'stellar winds, accretion disk', 5000);
INSERT INTO public.blackhole VALUES (3, 'M87 Core', false, 'entire star clusters', 13200);


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

INSERT INTO public.galaxy VALUES (1, 'Milky Way', true, 13600, 0);
INSERT INTO public.galaxy VALUES (2, 'Andromeda', false, 10100, 2537000);
INSERT INTO public.galaxy VALUES (3, 'Triangulum', false, 8000, 2730000);
INSERT INTO public.galaxy VALUES (4, 'Whirlpool', false, 13500, 23000000);
INSERT INTO public.galaxy VALUES (5, 'Sombrero', false, 13250, 28000000);
INSERT INTO public.galaxy VALUES (6, 'Cartwheel', false, 7000, 500000000);


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

INSERT INTO public.moon VALUES (1, 'Moon', 3, true, 4500);
INSERT INTO public.moon VALUES (2, 'Phobos', 4, false, 4500);
INSERT INTO public.moon VALUES (3, 'Deimos', 4, false, 4500);
INSERT INTO public.moon VALUES (4, 'Io', 5, false, 4500);
INSERT INTO public.moon VALUES (5, 'Europa', 5, false, 4500);
INSERT INTO public.moon VALUES (6, 'Ganymede', 5, false, 4500);
INSERT INTO public.moon VALUES (7, 'Callisto', 5, false, 4500);
INSERT INTO public.moon VALUES (8, 'Amalthea', 5, false, 4500);
INSERT INTO public.moon VALUES (9, 'Titan', 6, false, 4500);
INSERT INTO public.moon VALUES (10, 'Enceladus', 6, false, 4500);
INSERT INTO public.moon VALUES (11, 'Mimas', 6, false, 4500);
INSERT INTO public.moon VALUES (12, 'Rhea', 6, false, 4500);
INSERT INTO public.moon VALUES (13, 'Dione', 6, false, 4500);
INSERT INTO public.moon VALUES (14, 'Titania', 7, false, 4200);
INSERT INTO public.moon VALUES (15, 'Oberon', 7, false, 4200);
INSERT INTO public.moon VALUES (16, 'Ariel', 7, false, 4200);
INSERT INTO public.moon VALUES (17, 'Triton', 8, true, 3800);
INSERT INTO public.moon VALUES (18, 'Nereid', 8, false, 3800);
INSERT INTO public.moon VALUES (19, 'K22-Luna', 9, true, 3900);
INSERT INTO public.moon VALUES (20, 'Mir-Moon-I', 12, true, 600);


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

INSERT INTO public.planet VALUES (1, 'Mercury', 1, false, 4500);
INSERT INTO public.planet VALUES (2, 'Venus', 1, false, 4500);
INSERT INTO public.planet VALUES (3, 'Earth', 1, true, 4500);
INSERT INTO public.planet VALUES (4, 'Mars', 1, false, 4500);
INSERT INTO public.planet VALUES (5, 'Jupiter', 1, false, 4500);
INSERT INTO public.planet VALUES (6, 'Saturn', 1, false, 4500);
INSERT INTO public.planet VALUES (7, 'Uranus', 1, false, 4500);
INSERT INTO public.planet VALUES (8, 'Neptune', 1, false, 4500);
INSERT INTO public.planet VALUES (9, 'Kepler-22b', 4, true, 4000);
INSERT INTO public.planet VALUES (10, 'Sirius Ab1', 2, false, 300);
INSERT INTO public.planet VALUES (11, 'Sirius Ab2', 2, false, 300);
INSERT INTO public.planet VALUES (12, 'Mir-Alpha', 5, true, 500);
INSERT INTO public.planet VALUES (13, 'Mir-Beta', 5, false, 500);


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

INSERT INTO public.star VALUES (1, 'Sun', 1, false, 4600);
INSERT INTO public.star VALUES (2, 'Sirius', 1, false, 242);
INSERT INTO public.star VALUES (3, 'Betelgeuse', 1, true, 8000);
INSERT INTO public.star VALUES (4, 'Proxima Cen', 1, false, 4850);
INSERT INTO public.star VALUES (5, 'Alpheratz', 2, false, 200);
INSERT INTO public.star VALUES (6, 'Mirach', 2, false, 1000);
INSERT INTO public.star VALUES (7, 'Andromeda P32', 2, true, 12000);


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

SELECT pg_catalog.setval('public.blackhole_blackhole_id_seq', 3, true);


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

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


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

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


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

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


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

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


--
-- Name: blackhole blackhole_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.blackhole
    ADD CONSTRAINT blackhole_name_key UNIQUE (name);


--
-- Name: blackhole blackhole_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.blackhole
    ADD CONSTRAINT blackhole_pkey PRIMARY KEY (blackhole_id);


--
-- 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 planet_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

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


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

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_pkey PRIMARY KEY (planet_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 planet_star_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

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: 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
--