Create table, 2nd step


#1


https://www.codecademy.com/en/courses/learn-sql/projects/learn_sql_create_table


Bit baffled by error messages I was getting here - now I'm not getting any errors, but nor is a new line being inserted into the table?



CREATE TABLE friends (id INTEGER, name TEXT, birthday TEXT );

INSERT INTO friends (id, name, birthday) VALUES (1, 'Jane Doe', '1993-05-19');


#2

So when you run the query, you're not getting this?

id  name           birthday       
1   Jane Doe       1993-05-19
2   Jane Smith     1993-05-19
3   John Smith     1977-05-05
4   Ada Lovelace   1815-12-10

#3

can you post your code for that? Im not getting any errors but I know its not right.

My final SQL code is:

Create Table friends (id INTEGER, name TEXT, birthday INTEGER);

Insert Into friends (id, name, birthday) Values (1, 'Jane Doe', 1993-05-19);

Insert Into friends (id, name, birthday) Values (2, 'Hanif', 1997-07-16);

Insert Into friends (id, name, birthday) Values (3, 'Nala', 1947-01-01);

Insert Into friends (id, name, birthday) Values (4, 'Liza', 1996-07-16);

Update friends
Set name = 'Jane Smith'
Where id = 1;

Alter Table friends ADD COLUMN email TEXT;

Update friends
Set email = 'jdoe@example.com'
Where id= 1;

Update friends
Set email = 'hanif.black@gmail.com'
Where id= 2;

Update friends
Set email = 'nala@gmail.com'
Where id= 3;

Update friends
Set email = 'liza@gmail.com'
Where id= 4;

Delete From friends
Where id = 1;


#4

Not an integer. It is a DATE type, that I'm pretty sure needs to be in quotes.


#5

Okay thanks let me try that


#6

I changed it, it came back with the wrong table, dont see any of the data I put in the code i.e different people and their birthdays.

Create Table friends (id INTEGER, name TEXT, birthday DATE);

Insert Into friends (id, name, birthday) Values (1, 'Jane Doe', 1993-05-19);

Insert Into friends (id, name, birthday) Values (2, 'Hanif', 1997-07-16);

Insert Into friends (id, name, birthday) Values (3, 'Nala', 1947-01-01);

Insert Into friends (id, name, birthday) Values (4, 'Liza', 1996-07-16);

Update friends
Set name = 'Jane Smith'
Where id = 1;

Alter Table friends ADD COLUMN email TEXT;

Update friends
Set email = 'jdoe@example.com'
Where id= 1;

Update friends
Set email = 'hanif.black@gmail.com'
Where id= 2;

Update friends
Set email = 'nala@gmail.com'
Where id= 3;

Update friends
Set email = 'liza@gmail.com'
Where id= 4;

Delete From friends
Where id = 1;


#7

DATE type object needs to be in quotes.


#8

Okay I put quotes on my dates but still no table im expecting. Here is the code:

Create Table friends (id INTEGER, name TEXT, birthday DATE);

Insert Into friends (id, name, birthday) Values (1, 'Jane Doe', '1993-05-19');

Insert Into friends (id, name, birthday) Values (2, 'Hanif', '1997-07-16');

Insert Into friends (id, name, birthday) Values (3, 'Nala', '1947-01-01');

Insert Into friends (id, name, birthday) Values (4, 'Liza', '1996-07-16');

Update friends
Set name = 'Jane Smith'
Where id = 1;

Alter Table friends ADD COLUMN email TEXT;

Update friends
Set email = 'jdoe@example.com'
Where id= 1;

Update friends
Set email = 'hanif.black@gmail.com'
Where id= 2;

Update friends
Set email = 'nala@gmail.com'
Where id= 3;

Update friends
Set email = 'liza@gmail.com'
Where id= 4;

Delete From friends
Where id = 1;


#9

Because of this line,

You are removing a critical row. To get around this, add it in just after your set Jane Smith's name...

INSERT INTO friends (id, name, birthday)
VALUES (4, 'Jane Doe', '1993-05-19');

This,

Update friends
Set email = 'jdoe@example.com'
Where id= 1;

should be id=4, (copy and paste it in lower down) and the email for id=1 set to janes@example.com.

Here is the table I ended up with:

id  name           birthday       email
1   Jane Smith     1993-05-19     janes@example.com
2   John Smith     1977-05-05     jsmith@example.com
3   Ada Lovelace   1815-12-10     aal@example.com

#10

Okay I think I see now, I added this to the bottom of my code to get a read out of the table:

Select *
From friends

I ran this as a final version:

Create Table friends (id INTEGER, name TEXT, birthday INTEGER);

Insert Into friends (id, name, birthday) Values (1, 'Jane Doe', '1993-05-19');

Insert Into friends (id, name, birthday) Values (2, 'Hanif', '1997-07-16');

Insert Into friends (id, name, birthday) Values (3, 'Nala', '1947-01-01');

Insert Into friends (id, name, birthday) Values (4, 'Liza', '1996-07-16');

Update friends
Set name = 'Jane Smith'
Where id = 1;

Alter Table friends ADD COLUMN email TEXT;

Update friends
Set email = 'jdoe@example.com'
Where id= 1;

Update friends
Set email = 'hanif.black@gmail.com'
Where id= 2;

Update friends
Set email = 'nala@gmail.com'
Where id= 3;

Update friends
Set email = 'liza@gmail.com'
Where id= 4;

Delete From friends
Where id = 1;

Select *
From friends;

The Table it gave out is as follows:

id name birthday email
2 Hanif 1997-07-16 hanif.black@gmail.com
3 Nala 1947-01-01 nala@gmail.com
4 Liza 1996-07-16 liza@gmail.com

Look right to you? My only gripe is that the id's dont start from 1 but instead start from 2 instead.


#12