Quantcast

Troubles with sqlile sql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Troubles with sqlile sql

David Niklas
Good evening gentlemen,
I read the post:
http://linuxgizmos.com/ringing-in-2017-with-90-hacker-friendly-single-board-computers/
which lists a number of SBC.
I was interested, but the info in the table was lacking. The article also
left out much of what I considered "Interesting" information. It seems to
be more like a windowz PC sales ad.
So, I decided to follow all the links and get the specs on the boards
more thoroughly, and place them into an sqlite database, for
searchability.
Now, I'm no sql expert, but I have tried to learn and get this stuff
correct, so please bear with my inadequacy.

# ALTER TABLE processors ADD CONSTRAINT bit NOT NULL
Error: near "CONSTRAINT": syntax error

# ALTER TABLE processors DROP bit;
Error: near "DROP": syntax error

I expected that both of the above would add the constraint.

# SELECT boards.*,processors.*,storage.emmc FROM processors INNER
> JOIN storage ON processors.board = storage.board;
Error: no such table: boards

# .schema
CREATE TABLE boards(
board varchar(30) PRIMARY KEY NOT NULL,
price SMALLINT NOT NULL,
vendor varchar(27) NOT NULL,
oses varchar(32));
...

I expected that the table boards would exist.
I also can't figure out how to join 3 tables, each on the board column.

# SELECT processors.*,storage.* FROM processors INNER JOIN storage ON
> processors.board = storage.board WHERE processors.board LIKE "%Rose%";
Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0
Roseapple Pi 4GB eMMC 0 0 0 0

I wanted the board section of both tables to be joined and the one of
them displayed (they have to be identical to join right?), not to
be repeated, one after the other.

Thank you in advance,
David
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Troubles with sqlile sql

Richard Hipp-3
On 1/28/17, David Niklas <[hidden email]> wrote:
>
> # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL
> Error: near "CONSTRAINT": syntax error
>
> # ALTER TABLE processors DROP bit;
> Error: near "DROP": syntax error


ALTER TABLE is one of the few areas where SQLite's SQL support is
thin.  SQLite only supports ALTER TABLE ADD COLUMN and ALTER TABLE
RENAME TABLE.

>
> I expected that both of the above would add the constraint.
>
> # SELECT boards.*,processors.*,storage.emmc FROM processors INNER
>> JOIN storage ON processors.board = storage.board;
> Error: no such table: boards
>

All tables used by the query must be named in the FROM clause.  What
database engine are you used to that does not require this?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Troubles with sqlile sql

Simon Slavin-3
In reply to this post by David Niklas

On 28 Jan 2017, at 7:27pm, David Niklas <[hidden email]> wrote:

> # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL
> Error: near "CONSTRAINT": syntax error
>
> # ALTER TABLE processors DROP bit;
> Error: near "DROP": syntax error
>
> I expected that both of the above would add the constraint.

Neither adding nor deleting a constraint are supported by ALTER in SQLite.  If you didn’t create the constraint when you originally made the table you can’t do it.

Generally, to get this effect in SQLite, rename the original table, create a new table with the constraint, and copy the data across using something like

INSERT INTO processors (SELECT * FROM processors_old)

> # SELECT boards.*,processors.*,storage.emmc FROM processors INNER
>> JOIN storage ON processors.board = storage.board;
> Error: no such table: boards
>
> # .schema
> CREATE TABLE boards(
> board varchar(30) PRIMARY KEY NOT NULL,
> price SMALLINT NOT NULL,
> vendor varchar(27) NOT NULL,
> oses varchar(32));
> ...
>
> I expected that the table boards would exist.

You didn’t mention the table 'boards' in the list of tables in the JOIN.  It’s neither the main column of the SELECT (processors) nor the table you’ve joined to it (storage).

> I also can't figure out how to join 3 tables, each on the board column.

My initial thought is that you would have JOIN clauses in the same SELECT.

> # SELECT processors.*,storage.* FROM processors INNER JOIN storage ON
>> processors.board = storage.board WHERE processors.board LIKE "%Rose%";
> Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0
> Roseapple Pi 4GB eMMC 0 0 0 0
>
> I wanted the board section of both tables to be joined and the one of
> them displayed (they have to be identical to join right?), not to
> be repeated, one after the other.

Not sure i’ve grasped what you want here, but you might want to JOIN with the UNION of two tables.  Or for your SELECT to to a UNION of two tables with JOINs.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Troubles with sqlile sql

David Niklas
In reply to this post by David Niklas
I'm going to reply to both messages at once.

On  Sat, 28 Jan 2017 15:44:07 -0500
Richard Hipp <[hidden email]> wrote:

> On 1/28/17, David Niklas <[hidden email]> wrote:
> >
> > # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL
> > Error: near "CONSTRAINT": syntax error
> >
> > # ALTER TABLE processors DROP bit;
> > Error: near "DROP": syntax error  
>
>
> ALTER TABLE is one of the few areas where SQLite's SQL support is
> thin.  SQLite only supports ALTER TABLE ADD COLUMN and ALTER TABLE
> RENAME TABLE.

Ah.

> >
> > I expected that both of the above would add the constraint.
> >
> > # SELECT boards.*,processors.*,storage.emmc FROM processors INNER  
> >> JOIN storage ON processors.board = storage.board;  
> > Error: no such table: boards
> >  
>
> All tables used by the query must be named in the FROM clause.  What
> database engine are you used to that does not require this?
I used postgresql, but I learned sql from wikibooks* .

Namely this syntax:
SELECT[ ALL| DISTINCT] <column name>[[ AS] <alias>][,[ ALL| DISTINCT]
<column name>[[ AS] <alias>]]* FROM <table>[[ AS] <alias>|[[ FULL| LEFT|
RIGHT] OUTER| INNER] JOIN <table> ON <expression>]

<!-- This part -->
[, <table>[[ AS]
<alias>|[[ FULL| LEFT| RIGHT] OUTER| INNER] JOIN <table> ON
<expression>]]*


On Sat, 28 Jan 2017 20:44:30 +0000
Simon Slavin <[hidden email]> wrote:

> On 28 Jan 2017, at 7:27pm, David Niklas <[hidden email]> wrote:
>
> > # ALTER TABLE processors ADD CONSTRAINT bit NOT NULL
> > Error: near "CONSTRAINT": syntax error
> >
> > # ALTER TABLE processors DROP bit;
> > Error: near "DROP": syntax error
> >
> > I expected that both of the above would add the constraint.  
>
> Neither adding nor deleting a constraint are supported by ALTER in
> SQLite.  If you didn’t create the constraint when you originally made
> the table you can’t do it.
>
> Generally, to get this effect in SQLite, rename the original table,
> create a new table with the constraint, and copy the data across using
> something like
>
> INSERT INTO processors (SELECT * FROM processors_old)
Thanks!

> > # SELECT boards.*,processors.*,storage.emmc FROM processors INNER  
> >> JOIN storage ON processors.board = storage.board;  
> > Error: no such table: boards
> >
> > # .schema
> > CREATE TABLE boards(
> > board varchar(30) PRIMARY KEY NOT NULL,
> > price SMALLINT NOT NULL,
> > vendor varchar(27) NOT NULL,
> > oses varchar(32));
> > ...
> >
> > I expected that the table boards would exist.  
>
> You didn’t mention the table 'boards' in the list of tables in the
> JOIN.  It’s neither the main column of the SELECT (processors) nor the
> table you’ve joined to it (storage).
>
> > I also can't figure out how to join 3 tables, each on the board
> > column.  
>
> My initial thought is that you would have JOIN clauses in the same
> SELECT.
>
> > # SELECT processors.*,storage.* FROM processors INNER JOIN storage
> > ON  
> >> processors.board = storage.board WHERE processors.board LIKE
> >> "%Rose%";  
> > Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0
> > Roseapple Pi 4GB eMMC 0 0 0 0
> >
> > I wanted the board section of both tables to be joined and the one of
> > them displayed (they have to be identical to join right?), not to
> > be repeated, one after the other.  
>
> Not sure I’ve grasped what you want here, but you might want to JOIN
> with the UNION of two tables.  Or for your SELECT to to a UNION of two
> tables with JOINs.

I've not heard of a UNION (It seems that the wikibooks article is
incomplete* ). I'll have to look that up.
I expected this:
Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0
4GB eMMC 0 0 0 0
Instead of:
Roseapple Pi Actions S500 4x A9 @ 1.6GHz PowerVR SGX544 256MB0
Roseapple Pi 4GB eMMC 0 0 0 0

* See:
https://en.wikibooks.org/wiki/Structured_Query_Language/Classic_Track_Print

Thank you,
David
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Troubles with sqlile sql

Simon Slavin-3

On 30 Jan 2017, at 3:48am, David Niklas <[hidden email]> wrote:

> I've not heard of a UNION (It seems that the wikibooks article is
> incomplete* )

That page is documentation for some other version of SQL, neither the SQL standard nor SQLite.  A copy of the SQL specification for 1992 can be found here:

<https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>

Documentation for SQLite’s own implementation of SQL can be found here:

<https://www.sqlite.org>

but neither of those are easy to learn from, so you should probably find documentation for the SQL UNION keyword by googling 'SQL UNION'.  This page is a good one:

<http://www.w3schools.com/sql/sql_union.asp>

and the w3schools site in general is a good way to learn new computer languages.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...