use dataExamples;
SELECT * FROM planets;
SELECT planetName, Distance_From_Sun_MAU FROM planets;
SELECT planetName, Distance_From_Sun_MAU FROM planets WHERE planetName != 'Moon';
SELECT planetName, Diameter_km FROM planets;
SELECT planetName, Diameter_km FROM planets ORDER BY Diameter_km;
SELECT planetName, Diameter_km FROM planets ORDER BY Diameter_km DESC;
SELECT planetName, Mean_Temperature_C FROM planets ORDER BY Mean_Temperature_C;
SELECT planetName, Mean_Temperature_C FROM planets WHERE Mean_Temperature_C BETWEEN 42 AND -73 ORDER BY Mean_Temperature_C;
SELECT planetName, Mean_Temperature_C FROM planets WHERE Mean_Temperature_C BETWEEN -73 AND 42 ORDER BY Mean_Temperature_C;
SELECT planetName, Mean_Temperature_C
FROM planets
WHERE Mean_Temperature_C BETWEEN -73 AND 42
ORDER BY Mean_Temperature_C;
SELECT
planetName AS "Planet",
Mean_Temperature_C AS "Average Temperature in Celsius",
15 + ABS(Mean_Temperature_C) AS "Required Technology Adjustment in Celsius"
FROM planets
ORDER BY Mean_Temperature_C;
SELECT
planetName AS "Planet",
Surface_Pressure AS "Surface Pressure",
(1 - ABS(Surface_Pressure)) * 3 AS "Required Suit Thickness in mm"
FROM planets
WHERE Surface_Pressure IS NOT NULL
/* This script is for populating the table with the
NASA Plant data so I can use it for examples.
*/
-- Create a new database just for my data examples
-- CREATE DATABASE dataExamples;
-- Select the database we want to modify
USE dataExamples;
-- New table so if another one with this name exists - delete it
DROP TABLE IF EXISTS `planets`;
-- Create a new table from scratch
CREATE TABLE `planets` (
-- Let us have the database provide a unique key
`planetID` INT(5) AUTO_INCREMENT,
-- The name of the object
`planetName` VARCHAR(10) NOT NULL,
-- Add one line for each field in the table
`Mass` DECIMAL(10,4),
`Diameter_km` INT(10),
`Density_kmpmc` INT(10),
`Gravity_mpss` DECIMAL(7,2),
`Escape_Velocity_kmps` DECIMAL(7,2),
`Rotation_Period_Hours` DECIMAL(10,2),
`Length_of_Day_Hours` DECIMAL(10,2),
`Distance_From_Sun_MAU` DECIMAL(10,3),
`Perihelion_MAU` DECIMAL(10,3),
`Aphelion_MAU` DECIMAL(10,3),
`Orbital_Period_Days` DECIMAL(10,2),
`Orbital_Velocity_kps` DECIMAL(5,1),
`Orbital_Inclination` DECIMAL(5,1),
`Orbital_Eccentricity` DECIMAL(6,3),
`Obliquity_to_Orbit` DECIMAL(10,3),
`Mean_Temperature_C` INT(10),
`Surface_Pressure` DECIMAL(10,5),
`Number_of_Moons` INT(10),
`Ring_System` CHAR(3),
`Global_Magnetic_Field` CHAR(3),
-- Indicate the primary key for this table
PRIMARY KEY(`planetID`)
);
-- Populate additional data into table
INSERT INTO `planets` VALUES
(NULL,'Mercury',0.330,4879,5427,3.7,4.3,1407.6,4222.6,57.9,46.0,69.8,88.0,47.4,7.0,0.205,0.034,167,0,0,'No','Yes'),
(NULL,'Venus',4.87,12104,5243,8.9,10.4,-5832.5,2802.0,108.2,107.5,108.9,224.7,35.0,3.4,0.007,177.4,464,92,0,'No','No'),
(NULL,'Earth',5.97,12756,5514,9.8,11.2,23.9,24.0,149.6,147.1,152.1,365.2,29.8,0.0,0.017,23.4,15,1,1,'No','Yes'),
(NULL,'Moon',0.073,3475,3340,1.6,2.4,655.7,708.7,0.384,0.363,0.406,27.3,1.0,5.1,0.055,6.7,-20,0,0,'No','No'),
(NULL,'Mars',0.642,6792,3933,3.7,5.0,24.6,24.7,227.9,206.6,249.2,687.0,24.1,1.9,0.094,25.2,-65,0.01,2,'No','No'),
(NULL,'Jupiter',1898,142984,1326,23.1,59.5,9.9,9.9,778.6,740.5,816.6,4331,13.1,1.3,0.049,3.1,-110,NULL,79,'Yes','Yes'),
(NULL,'Saturn',568,120536,687,9.0,35.5,10.7,10.7,1433.5,1352.6,1514.5,10747,9.7,2.5,0.057,26.7,-140,NULL,82,'Yes','Yes'),
(NULL,'Uranus',86.8,51118,1271,8.7,21.3,-17.2,17.2,2872.5,2741.3,3003.6,30589,6.8,0.8,0.046,97.8,-195,NULL,27,'Yes','Yes'),
(NULL,'Neptune',102,49528,1638,11.0,23.5,16.1,16.1,4495.1,4444.5,4545.7,59800,5.4,1.8,0.011,28.3,-200,NULL,14,'Yes','Yes'),
(NULL,'Pluto',0.0146,2370,2095,0.7,1.3,-153.3,153.3,5906.4,4436.8,7375.9,90560,4.7,17.2,0.244,122.5,-225,0.00001,5,'No',NULL);
These work with my
03-nasa-planets-loader.sql
Script