There was no SQL, so I did Java. Anyway, I’ve been working on this for the past…week, I think. I have my psql, my element.sh, and terminal codes right here:
SQL:
ALTER TABLE properties RENAME COLUMN weight TO atomic_mass;
ALTER TABLE properties RENAME COLUMN melting_point TO melting_point_celsius;
ALTER TABLE properties RENAME COLUMN boiling_point TO boiling_point_celsius;
ALTER TABLE elements ADD UNIQUE (symbol);
ALTER TABLE elements ADD UNIQUE (name);
ALTER TABLE elements ALTER COLUMN symbol SET NOT NULL;
ALTER TABLE elements ALTER COLUMN name SET NOT NULL;
ALTER TABLE properties ADD FOREIGN KEY (atomic_number) REFERENCES elements(atomic_number);
CREATE TABLE types (
type_id SERIAL PRIMARY KEY,
type VARCHAR(30) NOT NULL
);
INSERT INTO types (type) VALUES ('metal'), ('nonmetal'), ('metalloid');
ALTER TABLE properties ADD COLUMN type_id INT;
ALTER TABLE properties ADD FOREIGN KEY (type_id) REFERENCES types(type_id);
UPDATE properties SET type_id = (SELECT type_id FROM types WHERE type = properties.type);
ALTER TABLE properties ALTER COLUMN type_id SET NOT NULL;
ALTER TABLE properties DROP COLUMN type;
UPDATE elements SET symbol = INITCAP(symbol);
ALTER TABLE properties ALTER COLUMN atomic_mass TYPE DECIMAL;
UPDATE properties SET atomic_mass = TRIM(TRAILING '0' FROM atomic_mass::TEXT)::DECIMAL;
INSERT INTO elements (atomic_number, symbol, name) VALUES (9, 'F', 'Fluorine'), (10, 'Ne', 'Neon');
INSERT INTO properties (atomic_number, type_id, atomic_mass, melting_point_celsius, boiling_point_celsius)
VALUES (9, (SELECT type_id FROM types WHERE type = 'nonmetal'), 18.998, -220, -188.1),
(10, (SELECT type_id FROM types WHERE type = 'nonmetal'), 20.18, -248.6, -246.1);
DELETE FROM properties WHERE atomic_number = 1000;
DELETE FROM elements WHERE atomic_number = 1000;
Terminal:
mkdir periodic_table
cd periodic_table
git init
touch element.sh
chmod +x element.sh
element.sh:
#!/bin/bash
PSQL="psql --username=freecodecamp --dbname=periodic_table -t --no-align -c"
if [[ -z $1 ]]
then
echo "Please provide an element as an argument."
else
if [[ $1 =~ ^[0-9]+$ ]]
then
ELEMENT=$($PSQL "SELECT * FROM elements INNER JOIN properties USING(atomic_number) INNER JOIN types USING(type_id) WHERE atomic_number=$1")
else
ELEMENT=$($PSQL "SELECT * FROM elements INNER JOIN properties USING(atomic_number) INNER JOIN types USING(type_id) WHERE symbol='$1' OR name='$1'")
fi
if [[ -z $ELEMENT ]]
then
echo "I could not find that element in the database."
else
echo $ELEMENT | while IFS="|" read TYPE_ID ATOMIC_NUMBER SYMBOL NAME ATOMIC_MASS MELTING_POINT BOILING_POINT TYPE
do
echo "The element with atomic number $ATOMIC_NUMBER is $NAME ($SYMBOL). It's a $TYPE, with a mass of $ATOMIC_MASS amu. $NAME has a melting point of $MELTING_POINT celsius and a boiling point of $BOILING_POINT celsius."
done
fi
fi
My commits:
git add .
git commit -m “Initial commit”
git add element.sh
git commit -m “feat: Add element.sh script”
git add .
git commit -m “fix: Update database schema”
git add .
git commit -m “feat: Add new elements”
git add .
git commit -m “chore: Clean up database”
Please tell me how to fix this.