Scribd
Upload
35 views2 pages

SQL Animal Table Management Guide

The document creates an animal table with columns for id, name, species, life expectancy, and extinct status. It inserts initial animal records, alters columns, adds the extinct column, updates values, inserts a new record, deletes records, removes the extinct column, updates the elephant's life expectancy, selects mammals over 40 years, and updates any null life expectancies to 0.

Uploaded by

mohitsharma83881
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
35 views2 pages

SQL Animal Table Management Guide

The document creates an animal table with columns for id, name, species, life expectancy, and extinct status. It inserts initial animal records, alters columns, adds the extinct column, updates values, inserts a new record, deletes records, removes the extinct column, updates the elephant's life expectancy, selects mammals over 40 years, and updates any null life expectancies to 0.

Uploaded by

mohitsharma83881
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

CREATE TABLE animal (id int NOT NULL,

name varchar(45) NOT NULL,


species varchar(45) DEFAULT 'Mammal',
life_exp int DEFAULT NULL,
PRIMARY KEY (id));
INSERT INTO animal VALUES (1,'Cat','Mammal',20),
(2,'Elephant','Mammal',70),
(3,'Trout','Fish',5),
(4,'Shark','Fish',25),
(5,'Canary','Bird',20),
(6,'Albatross','Bird',40),
(7,'Swift','Bird',5);

--Change the maximum length of life expectancy column to INT(3)


ALTER TABLE animal
ALTER COLUMN life_exp int NULL;

-- Add a new column of a suitable data type named Is_Extinct with a suitable default
value
ALTER TABLE animal
ADD Is_Extinct BIT DEFAULT 0;

--Set the value of the new column to TRUE for all animals

UPDATE animal
SET Is_Extinct = 1;

--Insert a new animal Beaver


INSERT INTO animal (id,name, species,life_exp, Is_Extinct)
VALUES (8,'Beaver', 'Mammal',15, 0);

--Delete all animals with life expectancy less than 10 years


DELETE FROM animal
WHERE life_exp < 10;

--Remove the Is_Extinct column from the animal table


ALTER TABLE animal
DROP COLUMN Is_Extinct;

--You found out that Elephants live twice as long in the wild compared to in
captivity[1].
--Change the life expectancy of elephants to half their current value in the animal
table

UPDATE animal
SET life_exp = life_exp / 2
WHERE name = 'Elephant';

--List all mammals with life expectancy over 40 years


SELECT *
FROM animal
WHERE species = 'Mammal' AND life_exp > 40;
--Check if any animal has a null value for their life expectancy.
--Then replace the null value with a 0
SELECT *
FROM animal
WHERE life_exp IS NULL;
UPDATE animal
SET life_exp = 0
WHERE life_exp IS NULL;

You might also like