D415 Database Assignment 1 Exam with accurate detailed solutions

D415 Database Assignment 1 Exam with || || || || || ||




accurate detailed solutions || ||




Write SQL command to create a table called "persons" to store the data in person.csv. Use
|| || || || || || || || || || || || || || || ||




applicable data types for the column attributes. Do not add any constraints other than the primary
|| || || || || || || || || || || || || || || ||




key and foreign key (if needed) in this SQL script.
|| || || || || || || || ||




CREATE TABLE Persons || ||




(
person_id serial PRIMARY KEY, || || ||




first_name varchar(20), ||




middle_name varchar(20), ||




last_name varchar(30), ||




major varchar(50),
||




minor varchar(50), ||




favorite_food varchar(50), ||




favorite_color varchar(50), ||




favorite_programming_language varchar(50), ||




student boolean ||




);
Write SQL command to create a table called "pets" to store the data in pet.csv. Use applicable data
|| || || || || || || || || || || || || || || || ||




types for the column attributes. Do not add any constraints other than the primary key and foreign
|| || || || || || || || || || || || || || || || ||




key (if needed) in this SQL script.
|| || || || || || ||




CREATE TABLE Pets || ||




(
pet_id serial PRIMARY KEY,
|| || ||




pet_name varchar(20), ||




pet_type varchar(20), ||




pet_age float, ||




virtual_pet boolean, ||




has_fur boolean, ||




owner_id BIGINT, ||




CONSTRAINT pets_fk FOREIGN KEY (owner_id) REFERENCES Persons(person_id) || || || || || ||




);
Write SQL commands to insert data corresponding to the first three rows in person.csv.
|| || || || || || || || || || || || ||