Assume that we have two tables bsg_people and bsg_planets. Just write the correc
ID: 3709492 • Letter: A
Question
Assume that we have two tables bsg_people and bsg_planets. Just write the correct sql queries pertaining to the questions.
(1) Find the population of the planet named 'Caprica'
(2) Find the first name, last name, and age of people from bsg_people whose age is NULL.
(3) Insert information about the planet Mars which has a population of 2, language as "Binary" and "Olympus Mons" as Capital, in bsg_planets. Then list the row(s), with all the information for that planet.
(4) Update age of all those people whose last name is 'Adama' and first name is 'William' to 62 and then print all rows with all the columns which match the same criteria, in a separate query.
(5) Create a table with the following properties:
Name: bsg_spaceship:
id - an auto-incrementing integer which is also the primary key
name- variable -length string with a max of 255 characters, cannot be null.
seperate_saucer_section- a boolean property which specifies whether or not there is a separate saucer section on the spaceship. This defaults to No.
length - integer, cannot be null
Once you have created the table, run the query "DESCRIBE bsg_spaceship;"
Explanation / Answer
1) Find the population of the planet named 'Caprica':
Answer: SELECT population from `bsg_planets` WHERE name = 'Caprica';
(2) Find the first name, last name, and age of people from bsg_people whose age is NULL.
Answer: SELECT fname,lname, age FROM `bsg_people` WHERE age IS NULL;
(3) Insert information about the planet Mars which has a population of 2, language as "Binary" and "Olympus Mons" as Capital, in bsg_planets. Then list the row(s), with all the information for that planet.
Answer: INSERT INTO `bsg_planets` ( `name`, `population`, `language`, `capital`) VALUES ( 'Mars', 2, 'Binary', 'Olympus Mons');
//displaying data
SELECT * FROM `bsg_planets` WHERE name = 'Mars';
(4) Update age of all those people whose last name is 'Adama' and first name is 'William' to 62 and then print all rows with all the columns which match the same criteria, in a separate query.
// Updating Age
UPDATE `bsg_people` SET `age` = 62 WHERE `fname` = 'William' and `lname` = 'Adama';
//Printing rows
SELECT * FROM `bsg_people` WHERE `fname` = 'William' and `lname` = 'Adama';
(5) Create a table with the following properties:
Name: bsg_spaceship:
id - an auto-incrementing integer which is also the primary key
name- variable -length string with a max of 255 characters, cannot be null.
seperate_saucer_section- a boolean property which specifies whether or not there is a separate saucer section on the spaceship. This defaults to No.
length - integer, cannot be null
Once you have created the table, run the query "DESCRIBE bsg_spaceship;"
CREATE TABLE IF NOT EXISTS `bsg_spaceship` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`seperate_saucer_section` tinyint(1) NOT NULL DEFAULT '0',
`length` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
// Describing Table
DESC `bsg_spaceship`