-
Notifications
You must be signed in to change notification settings - Fork 5
/
animalsDBWithID.sql
44 lines (36 loc) · 1.53 KB
/
animalsDBWithID.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Drops the animals_db if it exists currently --
DROP DATABASE IF EXISTS animals_db;
-- Creates the "animals_db" database --
CREATE DATABASE animals_db;
-- Makes it so all of the following code will affect animals_db --
USE animals_db;
-- Creates the table "people" within animals_db --
CREATE TABLE people (
-- Creates a numeric column called "id" which will automatically increment its default value as we create new rows --
id INTEGER(11) AUTO_INCREMENT NOT NULL,
-- Makes a string column called "name" which cannot contain null --
name VARCHAR(30) NOT NULL,
-- Makes a boolean column called "has_pet" which cannot contain null --
has_pet BOOLEAN NOT NULL,
-- Makes a sting column called "pet_name" --
pet_name VARCHAR(30),
-- Makes an numeric column called "pet_age" --
pet_age INTEGER(10),
-- Sets id as this table's primary key which means all data contained within it will be unique --
PRIMARY KEY (id)
);
-- Creates new rows containing data in all named columns --
INSERT INTO people (name, has_pet, pet_name, pet_age)
VALUES ("Ahmed", true, "Rockington", 100);
INSERT INTO people (name, has_pet, pet_name, pet_age)
VALUES ("Ahmed", true, "Rockington", 100);
INSERT INTO people (name, has_pet, pet_name, pet_age)
VALUES ("Jacob",true,"Misty",10);
INSERT INTO people (name, has_pet)
VALUES ("Peter", false);
-- Updates the row where the column name is peter --
UPDATE people
SET has_pet = true, pet_name = "Franklin", pet_age = 2
WHERE id = 4;
-- THIS MEANS GIVE ME ALL OF THE DATA IN THE PEOPLE TABLE
SELECT * FROM people;