Question: Using SQL

Consider the two tables, created using the following definition queries for all the questions:

bsg_people

CREATE TABLE `bsg_people` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(255) NOT NULL, `lname` varchar(255) DEFAULT NULL, `homeworld` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `homeworld` (`homeworld`), CONSTRAINT `bsg_people_ibfk_1` FOREIGN KEY (`homeworld`) REFERENCES `bsg_planets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB

bsg_planets

CREATE TABLE `bsg_planets` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `population` bigint(20) DEFAULT NULL, `language` varchar(255) DEFAULT NULL, `capital` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Questions:

a. Find the population of the planet named 'Caprica'

b. Find the first name, last name, and age of people from bsg_people whose last name is not 'Adama'

c. Find the name and population of the planets with a population larger than 2,600,000,000

d. Find the first name, last name, and age of people from bsg_people whose age is NULL

e. 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.

f. 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.

g. Delete all people whose age is NULL. Then, list all such rows which still satisfy the criteria.

h. 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;"