On Mon, May 26, 2014 at 10:33 PM, Humblebee 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 state that I was a comp
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 please
On 25 May 2014, at 9:23pm, RSmith 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 languages.
With peop
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 ha
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 the
@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
we
On Sat, 24 May 2014 22:24:01 +0200, RSmith 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)
VALUES
(1,3,4,0
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
lea
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. Each
On 24 May 2014, at 11:08am, Humblebee 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
BEGIN ... UPDATE .
On Sat, 24 May 2014 03:08:32 -0700 (PDT), 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
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 mu
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]
wrote:
>
>
>
> On
On 23 May 2014, at 7:53pm, Humblebee 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 data associated
On 2014/05/23 20:54, Stephan Beal wrote:
On Fri, May 23, 2014 at 8:53 PM, Humblebee 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
On Fri, May 23, 2014 at 8:53 PM, Humblebee 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 visible to that specif
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 norma
On Fri, May 23, 2014 at 8:33 PM, Stephan Beal 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 TRANSACTION;
COMMIT
On Fri, May 23, 2014 at 8:31 PM, Andy Goth 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 used to enter stri
-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
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 for
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.
Many
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 sa
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
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
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 wrote:
> Ah yes, you are correct.
>
> It was my typo. Works perfectly.
>
> On 5/2
Ah yes, you are correct.
It was my typo. Works perfectly.
On 5/22/14, RSmith [via SQLite] 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
> query. Perhaps i'm doing somethin
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 you
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] wrote:
>
>
>
> On 2014/05/22 14:28, Humblebee wrote:
>> Thanks for yo
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 t
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] wro
On 2014/05/22 13:39, Humblebee wrote:
TeamTable
+---+
| id | name |
+---+
| 1 | blue|
| 2 | green |
+---|
PersonTable
+--+
| id | name |
+--+
| 4 | john |
| 5
from IN - from a newbie.
TeamTable
+---+
| id | name |
+---+
| 1 | blue|
| 2 | green |
+---|
PersonTable
+--+
| id | name |
+--+
| 4 | john |
| 5 | bill
TeamTable
+---+
| id | name |
+---+
| 1 | blue|
| 2 | green |
+---|
PersonTable
+--+
| id | name |
+--+
| 4 | john |
| 5 | bill |
+-+---+
TeamP
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
imp
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 dos
@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 expec
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 does
;
> 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.
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
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. Lo
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
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, Sim
On May 21, 2014, at 6:00 PM, Humblebee 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 'a,b,c,' as string
),
C
On Wed, May 21, 2014 at 8:45 PM, Jim Dodgen 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 pay forever
On 21 May 2014, at 7:20pm, Petite Abeille wrote:
> On May 21, 2014, at 6:00 PM, Humblebee 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
On May 21, 2014, at 6:00 PM, Humblebee 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, better fix it now… as
th
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 :)
-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
> wrote:
>> | 1 | 4 | 1,5,2,3,4 | | 2 | 5 |
>> 2,6,3,5,1 |
>
> Without doing what Simon suggests, there is no good solution to
>
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 |
+-
On Wed, May 21, 2014 at 6:00 PM, Humblebee 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 suggests, there is no g
M PersonTable WHERE id IN (SELECT personIDs FROM TeamTable
>> WHERE
>> parId = 4);
>>
>> -----Ursprüngliche Nachricht-
>> Von: fantasia dosa [mailto:fantasia.d...@gmail.com]
>> Gesendet: Mittwoch, 21. Mai 2014 15:00
>> An: sqlite-users@sqlite.org
>> Be
a dosa [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-- kin
. 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 -
On 21 May 2014, at 1:59pm, fantasia dosa 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 keeping your data.
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 p
56 matches
Mail list logo