Thank you everyone for your advice.
I will definitely try to find a good course to strengthen my core
knowledge of SQL. Thanks everyone for helping me to get started. I
hope that I wasn't being a burden. I did state that I was a complete
Newbie and my questions were going to be dumb. So
On Mon, May 26, 2014 at 10:33 PM, Humblebee fantasia.d...@gmail.com wrote:
Thank you everyone for your advice.
I will definitely try to find a good course to strengthen my core
knowledge of SQL. Thanks everyone for helping me to get started. I
hope that I wasn't being a burden. I did
On Sat, 24 May 2014 22:24:01 +0200, RSmith rsm...@rsweb.co.za wrote:
INSERT OR UPDATE TeamPersonTable (tpId,teamId,personId,orderId)
VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0);
That will raise a syntax error.
Make it:
INSERT OR REPLACE INTO TeamPersonTable
(tpId,teamId,personId,orderId)
@OP: This isn't meant as something to be meant as mean, but I'd recommend
taking an online course for SQL in general. There are some free sites,
there are a lot more paid sites. I did my SQL training back when SQL 2000
was just coming out, and I paid 5 digits for the course. Those five digits
Thanks for the tip about the online course with an instructor. I will
look into online courses, although the 5 digits is a bit outside of
my limited budget. Learning is generally a very personal thing and
there are many ways to take in information. I have to say that I
learned so much from
On 2014/05/25 19:37, Humblebee wrote:
Thanks for the tip about the online course with an instructor. I will
look into online courses, although the 5 digits is a bit outside of
my limited budget. Learning is generally a very personal thing and
there are many ways to take in information. I
On 25 May 2014, at 9:23pm, RSmith rsm...@rsweb.co.za wrote:
On the plus side, they are mostly free and even the most expensive ones
doesn't come near what Stephen paid (All I can say to him is: Dude, you wuz
robbed).
The rise of the internet has changed the way people learn computer
I'm making good headway with the conversion from the use of strings
into a normalized database. With the kind help of everyone on the
list, I can retrieve the data from the new structure.
I am now at the stage of saving the data to the new structure. Each
time I do an update, I need to update
On Sat, 24 May 2014 03:08:32 -0700 (PDT), Humblebee
fantasia.d...@gmail.com wrote:
I'm making good headway with the conversion from the use of strings
into a normalized database. With the kind help of everyone on the
list, I can retrieve the data from the new structure.
I am now at the stage of
On 24 May 2014, at 11:08am, Humblebee fantasia.d...@gmail.com wrote:
I looked up the SQLite
documentation and found that Insert works on multiple rows. Can I
update multiple rows with one statement ?
No. But if you do not already understand BEGIN and END you should read about
them.
Doing
On 2014/05/24 12:08, Humblebee wrote:
I'm making good headway with the conversion from the use of strings
into a normalized database. With the kind help of everyone on the
list, I can retrieve the data from the new structure.
I am now at the stage of saving the data to the new structure.
Thank you everyone for your help and especially for the very detailed
and clear explanation.
There are so many intricacies of SQL that it's hard to know what to
do as a beginner. Having such clear and understandable explanations
makes learning the ropes so much easier. I am supplementing my
On 2014/05/23 04:48, Humblebee wrote:
CTEs seems like another magic ball. I just read up a little bit about
them. The bag of tricks is getting more colorful.
I would love to use this new feature, sadly the version of Sqlite is 3.7.7.1,
you mentioned views, is this something that I can use
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 5/22/2014 5:23 PM, RSmith wrote:
On 2014/05/22 17:25, Humblebee wrote:
I would like to order the grouping of the Persons in a
particular order. So the sequence of the result is based on the
TeamPersonTable's order field.
First a word of
On Fri, May 23, 2014 at 8:31 PM, Andy Goth andrew.m.g...@gmail.com wrote:
CREATE TABLE table
(id INTEGER PRIMARY KEY, a, b, c);
table in the KEY of C won't cause any confusion in the context of music,
will it ;)
So that's what double quotes means. Single quotes, on the other hand,
are
On Fri, May 23, 2014 at 8:33 PM, Stephan Beal sgb...@googlemail.com wrote:
FWIW, you can also use [table] instead of table.
With, apparently, some corner-cases:
sqlite create table [t] (a,b,c);
sqlite insert into [t] values(1,2,3);
But...
sqlite .dump [t]
PRAGMA foreign_keys=OFF;
BEGIN
Many Thanks Ryan. I'm learning so much in the last 2 days
(Normalization,Grouping,CTEs, Views,..)
I have a question: so every time you run a query against a view,
that view's query is run/updated if not cached .
Does this mean that if the View is Temporary, then it's not cached?
and for normal
On Fri, May 23, 2014 at 8:53 PM, Humblebee fantasia.d...@gmail.com wrote:
Does this mean that if the View is Temporary, then it's not cached?
and for normal views, it's cached?
No - a TEMP VIEW means the view is automatically destroyed when you close
the db connection, and that view is ONLY
On 2014/05/23 20:54, Stephan Beal wrote:
On Fri, May 23, 2014 at 8:53 PM, Humblebee fantasia.d...@gmail.com wrote:
Does this mean that if the View is Temporary, then it's not cached?
and for normal views, it's cached?
No - a TEMP VIEW means the view is automatically destroyed when you close
On 23 May 2014, at 7:53pm, Humblebee fantasia.d...@gmail.com wrote:
I have a question: so every time you run a query against a view,
that view's query is run/updated if not cached .
Does this mean that if the View is Temporary, then it's not cached?
and for normal views, it's cached?
The
I had the misconception that a View contains stored data - come to
think of it, it would not be practical if the result set was really
large.
Thank you everyone for giving me a better understanding of what a view
really is about.
Cheers.
On 5/24/14, Simon Slavin-3 [via SQLite]
Thank you everyone for your kind input and suggestions. That is quite
a lot to consider. I didn't realize it would be so difficult for a
Select statement to return a string.
Out of curiosity, when I do:
SELECT *
FROM Person WHERE id IN(2,2,3,3)
It doesn't seem to give back 4 rows, only 2.
No. The internal table stores only unique keys.
-Ursprüngliche Nachricht-
Von: Humblebee [mailto:fantasia.d...@gmail.com]
Gesendet: Donnerstag, 22. Mai 2014 11:39
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Simple Select from IN - from a newbie.
Thank you everyone for your kind
+s1065341n75770...@n5.nabble.com wrote:
No. The internal table stores only unique keys.
-Ursprüngliche Nachricht-
Von: Humblebee [mailto:fantasia.d...@gmail.com]
Gesendet: Donnerstag, 22. Mai 2014 11:39
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Simple Select from IN - from a newbie
On 2014/05/22 11:39, Humblebee wrote:
Thank you everyone for your kind input and suggestions. That is quite
a lot to consider. I didn't realize it would be so difficult for a
Select statement to return a string.
Out of curiosity, when I do:
SELECT *
FROM Person WHERE id IN(2,2,3,3)
It
@RSmith,
Very much appreciate you taking the time to write such a detailed and
awesome explanation of how the string and list works in SQL. I had no
idea what goes on there.
I also liked the car-jacking example, very funny - that made the
entire thing very clear. You're right, I wouldn't
To be more clear.
So now I have 3 tables.
Person
Team
Person_Team
Query:
1. Get all persons that belongs to a certain team.
The output should be:
persons = name1,name2,name3
personIds = id1,id2,id3
So in other words, each field is a string delimited by a comma.
On 5/22/14, fantasia dosa
On 2014/05/22 13:02, Humblebee wrote:
@RSmith,
Very much appreciate you taking the time to write such a detailed and
awesome explanation of how the string and list works in SQL. I had no
idea what goes on there.
It is my pleasure, sadly I know exactly how it feels to be under the wrong
TeamTable
+---+
| id | name |
+---+
| 1 | blue|
| 2 | green |
+---|
PersonTable
+--+
| id | name |
+--+
| 4 | john |
| 5 | bill |
+-+---+
select p.id,p.name from TeamPersonTable tp join PersonTable p on (p.id =
tp.personId) where tp.teamId = 1;
-Ursprüngliche Nachricht-
Von: Humblebee [mailto:fantasia.d...@gmail.com]
Gesendet: Donnerstag, 22. Mai 2014 13:40
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Simple Select
On 2014/05/22 13:39, Humblebee wrote:
TeamTable
+---+
| id | name |
+---+
| 1 | blue|
| 2 | green |
+---|
PersonTable
+--+
| id | name |
+--+
| 4 | john |
| 5
Thanks for your answers.
Wow, I just tried the Group thing and it's truly magic. Big Bang !
The first query gave the Exact answer I was looking for. It even
assembled a string.
Amazing how this works.
Is the last query the equivalent of the first query?
On 5/22/14, RSmith [via SQLite]
On 2014/05/22 14:28, Humblebee wrote:
Thanks for your answers.
Wow, I just tried the Group thing and it's truly magic. Big Bang !
The first query gave the Exact answer I was looking for. It even
assembled a string.
Amazing how this works.
I agree :)
Is the last query the equivalent of
I'm trying to understand the grouping magic and testing the first and
last query.
Only thing is for some reason, I'm not getting any result for the last
query. Perhaps i'm doing something wrong.
On 5/22/14, RSmith [via SQLite] ml-node+s1065341n7578...@n5.nabble.com wrote:
On 2014/05/22
I'm trying to understand the grouping magic and testing the first and
last query.
Only thing is for some reason, I'm not getting any result for the last
query. Perhaps i'm doing something wrong.
Works perfectly for me, are you sure you have it exactly so? Check the table names correspond to
Ah yes, you are correct.
It was my typo. Works perfectly.
On 5/22/14, RSmith [via SQLite] ml-node+s1065341n75787...@n5.nabble.com wrote:
I'm trying to understand the grouping magic and testing the first and
last query.
Only thing is for some reason, I'm not getting any result for the last
I'm very happy to have taken the advice of the Awesome people on this
list to add an additional table and make the data normalized. From
today, I learned that the word Normalized - Magic.
On 5/22/14, fantasia dosa fantasia.d...@gmail.com wrote:
Ah yes, you are correct.
It was my typo. Works
RSmith, Two thumbs up for your super kind help.
I will study the queries that you gave and try them backwards and
forwards to better understand the magic. Hope you don't mind if I ask
more newbie questions in the future.
Cheers to the many nice people in this Great list.
On 5/22/14, fantasia
Oops back sooner than I thought.
I would like to order the grouping of the Persons in a particular
order. So the sequence of the result is based on the TeamPersonTable's
order field.
TeamTable
+---+
| id | name |
+---+
| 1 | blue|
| 2
On 2014/05/22 17:25, Humblebee wrote:
Oops back sooner than I thought.
I would like to order the grouping of the Persons in a particular
order. So the sequence of the result is based on the TeamPersonTable's
order field.
First a word of warning - Please do not use column names that are the
CTEs seems like another magic ball. I just read up a little bit about
them. The bag of tricks is getting more colorful.
I would love to use this new feature, sadly the version of Sqlite is 3.7.7.1,
you mentioned views, is this something that I can use for the version
of Sqlite that I'm on.
Hi all wonderful people on this list.
I'm a newbilie so my questions might seem very well-- kinda dumb so
please forgive me.
I'm trying to do the following in Sqlite.
TeamTable -
parId: 4
personIDs : 1,5,9,6
PersonTable -
id:
name:
SELECT * FROM PersonTable WHERE id IN (SELECT
On 21 May 2014, at 1:59pm, fantasia dosa fantasia.d...@gmail.com wrote:
TeamTable -
parId: 4
personIDs : 1,5,9,6
PersonTable -
id:
name:
SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM
PersonTable WHERE parId = 4);
Your problem is cause by the way you're
. Mai 2014 15:00
An: sqlite-users@sqlite.org
Betreff: [sqlite] Simple Select from IN - from a newbie.
Hi all wonderful people on this list.
I'm a newbilie so my questions might seem very well-- kinda dumb so please
forgive me.
I'm trying to do the following in Sqlite.
TeamTable -
parId: 4
[mailto:fantasia.d...@gmail.com]
Gesendet: Mittwoch, 21. Mai 2014 15:00
An: sqlite-users@sqlite.org
Betreff: [sqlite] Simple Select from IN - from a newbie.
Hi all wonderful people on this list.
I'm a newbilie so my questions might seem very well-- kinda dumb so please
forgive me.
I'm trying to do
]
Gesendet: Mittwoch, 21. Mai 2014 15:00
An: sqlite-users@sqlite.org
Betreff: [sqlite] Simple Select from IN - from a newbie.
Hi all wonderful people on this list.
I'm a newbilie so my questions might seem very well-- kinda dumb so
please
forgive me.
I'm trying to do the following in Sqlite
On Wed, May 21, 2014 at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote:
| 1 | 4 | 1,5,2,3,4 |
| 2 | 5 | 2,6,3,5,1 |
+--|
...
@Simon, thank you for showing me a better way to setup the tables,
Without doing what Simon
On 2014/05/21 18:00, Humblebee wrote:
Thank you for your replies.
I'm sorry for not being super clear about the problem:
Note: The parId belongs to another table not shown here.
TeamTable
+-+
| id | parId | personIds |
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 5/21/2014 11:09 AM, Stephan Beal wrote:
On Wed, May 21, 2014 at 6:00 PM, Humblebee
fantasia.d...@gmail.com wrote:
| 1 | 4 | 1,5,2,3,4 | | 2 | 5 |
2,6,3,5,1 |
Without doing what Simon suggests, there is no
Andy Goth wrote:
And honestly, please don't give people with no knowledge of SQL theory the power to
set your SQL schema in stone.
Quote of the day - Probably the single most valuable bit of advice ever!
// I don't do facebook, but if I did, that would go on my wall :)
On May 21, 2014, at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote:
only problem is that in this situation, the tables have already been defined
and made by someone
else so I cannot change it. I'm a bit stuck with the way it is.
Nah… it’s software… you can always change it… in fact,
On 21 May 2014, at 7:20pm, Petite Abeille petite.abei...@gmail.com wrote:
On May 21, 2014, at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote:
only problem is that in this situation, the tables have already been defined
and made by someone
else so I cannot change it. I'm a bit stuck
On Wed, May 21, 2014 at 8:45 PM, Jim Dodgen j...@dodgen.us wrote:
I fully agree a bad database design can impact you for the life of the
application. If this is a class assignment and the instructor gave you this
as a problem then I can understand I cannot change it otherwise fix it
now or
On May 21, 2014, at 6:00 PM, Humblebee fantasia.d...@gmail.com wrote:
At least this is what I'm thinking from my very very limited understanding of
SQL and with the way that I'm trying to do this.
SMITH: Doctor, it hurts when I do _this_.
DALE: Don’t _do_ that.
with
DataSet
as
(
select
I fully agree a bad database design can impact you for the life of the
application. If this is a class assignment and the instructor gave you this
as a problem then I can understand I cannot change it otherwise fix it
now or pay forever.
*Jim Dodgen*
On Wed, May 21, 2014 at 11:27 AM,
Andy Goth wrote:
And honestly, please don't give people with no knowledge of
SQL theory the power to set your SQL schema in stone.
I am sure you mean Relational Theory, when using a database implementing
Relational semantics, such as SQLite.
There is no requirement that SQL (Structured Query
56 matches
Mail list logo