using SQL consider the two tables, created using the following definition querie
ID: 3714627 • Letter: U
Question
using SQL consider the two tables, created using the following definition queries ...
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:
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.
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;"
Explanation / Answer
e.
insert into bsg_planets?(name,population,language,capital) values('Mars',2,'Binary','Olympus Mons');
Select * from bsg_planets;
f.
Update bsg_people set age = 62 where fname = 'Adama' and lname = 'William' ;
Select * from bsg_people where age = 62;
h.
bsg_spaceship
CREATE TABLE `bsg_spaceship` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
'seperate_saucer_section' boolean NOT NULL DEFAULT 0,
'length' int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DESCRIBE bsg_spaceship;
Do ask if any doubt. Please upvote.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.