Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

csanyipal wrote
> csanyipal wrote
> 2017-09-01|F|1-2|5|b
> 2017-09-01|F|1-2|7|c
> 2017-09-04|M|1-2|7|b
> 2017-09-04|M|1-2|5|a
> 2017-09-05|Tu|1-2|8|c
> 2017-09-05|Tu|1-2|8|b
> 2017-09-06|W|1-2|8|a
> 2017-09-06|W|1-2|7|a
> 2017-09-07|Th|1-2|6|a
> 2017-09-07|Th|1-2|5|c
> 2017-09-08|F|3-4|5|b
> 2017-09-08|F|3-4|7|c
> 2017-09-11|M|3-4|7|b
> 2017-09-11|M|3-4|5|a
> 2017-09-12|Tu|3-4|8|c
> 2017-09-12|Tu|3-4|8|b
> 2017-09-13|W|3-4|8|a
> 2017-09-13|W|3-4|7|a
> 2017-09-14|Th|3-4|6|a
> 2017-09-14|Th|3-4|5|c
> 2017-09-15|F|5-6|5|b
> 2017-09-15|F|5-6|7|c
> 2017-09-18|M|5-6|7|b
> 2017-09-18|M|5-6|5|a
> 2017-09-19|Tu|5-6|8|c
> 2017-09-19|Tu|5-6|8|b
> 2017-09-20|W|5-6|8|a
> 2017-09-20|W|5-6|7|a
> 2017-09-21|Th|5-6|6|a
> 2017-09-21|Th|5-6|5|c
> 
> Can this output be achieved with a  SELECT
> http://www.sqlite.org/lang_select.html;   query?

I think I must to create one more CTE called SchoolWeeks and must modify the
LBs table like this:
LBs(SchoolWeek,lessonBlock) AS (
 VALUES (1,'1-2'), (2,'3-4')/*, (3,'5-6'), (4,'7-8'),(5,'9-10'),
(6,'11-12'),
(7,'13-14'), (8,'15-16'), (9,'17-18'), (10,'19-20'),
(11,'21-22'), (12,'23-24'), (13,'25-26'), (14,'27-28'),
(15,'29-30'),
(16,'31-32'), (17,'33-34'), (18,'35-36'), (19,'37-38'), (20,'39-40'),
(21,'41-42'), (22,'43-44'), (23,'45-46'), (24,'47-48'),
(25,'49-50'),
(26,'51-52'), (27,'53-54'), (28,'55-56'), (29,'57-58'), (30,'59-60'),
(31,'61-62'), (32,'63-64'), (33,'65-66'), (34,'67-68'),
(35,'69-70'),
(36,'71-72')  */
SWs(SchoolWeek) AS VALUES
(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)
so I can JOIN these in a proper way. Right?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

csanyipal wrote
> 2017-09-01|F|1-2|5|b
> 2017-09-01|F|1-2|7|c
> 2017-09-04|M|1-2|7|b
> 2017-09-04|M|1-2|5|a
> 2017-09-05|Tu|1-2|8|c
> 2017-09-05|Tu|1-2|8|b
> 2017-09-06|W|1-2|8|a
> 2017-09-06|W|1-2|7|a
> 2017-09-07|Th|1-2|6|a
> 2017-09-07|Th|1-2|5|c
> 2017-09-08|F|3-4|5|b
> 2017-09-08|F|3-4|7|c
> 2017-09-11|M|3-4|7|b
> 2017-09-11|M|3-4|5|a
> 2017-09-12|Tu|3-4|8|c
> 2017-09-12|Tu|3-4|8|b
> 2017-09-13|W|3-4|8|a
> 2017-09-13|W|3-4|7|a
> 2017-09-14|Th|3-4|6|a
> 2017-09-14|Th|3-4|5|c
> 2017-09-15|F|5-6|5|b
> 2017-09-15|F|5-6|7|c
> 2017-09-18|M|5-6|7|b
> 2017-09-18|M|5-6|5|a
> 2017-09-19|Tu|5-6|8|c
> 2017-09-19|Tu|5-6|8|b
> 2017-09-20|W|5-6|8|a
> 2017-09-20|W|5-6|7|a
> 2017-09-21|Th|5-6|6|a
> 2017-09-21|Th|5-6|5|c

Can this output be achieved with a  SELECT
<http://www.sqlite.org/lang_select.html>   query?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/21 9:58 PM, csanyipal wrote:
>>
>> I am really trying to understand how CTEs works and trying to achive my
>> goal
>> ( see bellow ) so I modified a little your code:
>> ...
>> As you can see I tried to add more CTEs into code out there but must
>> these
>> comment out because I get wrong Results.
>> So for now, with this code above I get followings ( for three school
>> weeks
>> ):
>>
>> 2017-09-01|F|1-2
>> 2017-09-01|F|3-4
>> 2017-09-01|F|5-6
>> 2017-09-04|M|1-2
>> 
>>
>> but I want followings ( for three school weeks ):
>>
>> 2017-09-01|F|1-2|5|b
>> 2017-09-01|F|1-2|7|c
>> 2017-09-04|M|1-2|7|b
>> 2017-09-04|M|1-2|5|a
>> 
>>
>> So what SQL command should I use for this?
> 
> I'm not exactly sure how the classes work, it's hard to establish from 
> your explanation and SQL example output, for instance, in the output, 
> why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
> 5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
> only active on certain days? If so, we need a table to specify it so.
> 
> I like your table specifying the TimeTables. It needed fixing because 
> the VALUES mechanism works a bit differently than you think, but you can 
> compare between your and my version to see exactly how that works.
> Like I said, it's a bit unclear, but I'm going to assume some things and 
> suggest this:
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2017-09-01', '2017-09-21'
> ),DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'),
> (5,'F'),(6,'Sa')
> ),LBs(lessonBlock) AS (
>      VALUES ('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'), ('45-46'), ('47-48'), ('49-50'), 
> ('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
>     ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
> ('71-72')  */
> ), TimeTable(DoWeek,Grade,Class) AS (
>   VALUES ('M',7,'b'),('M',5,'a'),
>      ('Tu',8,'c'),('Tu',8,'b'),
>      ('W',8,'a'),('W',7,'a'),
>      ('Th',6,'a'),('Th',5,'c'),
>      ('F',5,'b'),('F',7,'c')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
>   UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate
> ), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
> TimeTable.Class
>    FROM CAL
>   CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>    JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
>   WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No
> Saturdays
> )
> SELECT *
>      FROM RES;
> 
> 
> I kept the majority of lesson blocks commented out for brevity, but if 
> it works, you can uncomment them and everything should work.
> 
> I hope it works, but if not, feel free to ask again, perhaps with a more 
> exact explanation of what the output should contain and how the lesson 
> blocks work.
> 
> 
> Cheers!
> Ryan

I tried it out, but it not gives the expected result.
I modified the 'Class' column name to 'ClassName' everywhere where it
occures, eg. like this:
TimeTable(DoWeek,Grade,ClassName)
and
TimeTable.ClassName

in order to make it more understandable.
Then I run it but it gives not the expected results.
This is because I was not clear enough about how the lesson blocks works.
But now this is what I'm supposed to do. My first language is not English,
so sorry if I fail in my attempt to explain this.
So in a school year on grade/class name ( like 5/a, 5/b, 5/c or 6/b, etc.)
must take 36 lesson blocks where a lesson block is built up of two lessons -
school hours - and lasts twice for 45 minutes. So in a school year there is
72 lessons, so 72/2=36 lesson blocks out there.
The lesson blocks I call like this: 1-2, 3-4, etc.
But there are school weeks too. In a school year there are 36 school weeks
out there.
In a school year there are 36 lesson blocks too. We numbering lesson blocks
in a school week, like this:
in the 1. school week (SW) the lesson block is called 1-2,
in the 2. SW 3-4,
...
in the 35. SW 69-70, and finally
in the 36. SW 71-72.

Now about the TimeTable.
On Friday 2017-09-01 I had one lesson block ( the 1-2 ) with 5/b Class and
one lesson block ( the 1-2 ) with 7/c Class.
On the nex

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

David Raymond wrote
> In the commented out section:
> 
> TimeTable(DoWeek,Grade,Class_) AS
> (VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')...
> 
> Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5,
> 'c')...?
> 
> WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
>  DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
> (4,'Th'), (5,'F'),(6,'Sa')),
>  LBs(lessonBlock) AS (VALUES ('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'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
> ('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
>   , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
>  /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
>  Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
>  TimeTable(DoWeek,Grade,Class_) AS (VALUES
> ('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
>  
> ('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
>  CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
>  date(calStartDate), -1
>  FROM PAR
>  UNION ALL
>   SELECT nextDay, date(nextDay,'+1 day'),
> CAST(STRFTIME('%w',nextDay) AS INT)
> FROM CAL,PAR
> WHERE nextDay <= calEndDate),
>  RES(dayDate, dayName, lessonBlock) AS
>(SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>   FROM CAL
>CROSS JOIN LBs
>JOIN DoW ON DoW.dayID = CAL.dayId
>WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
> Saturdays */
> SELECT *
> FROM RES;
> 

Indeed. I corrected that part and add the
JOIN TimeTable
like this:
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN TimeTable
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */

but get wrong outputs:
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2

etc.
What am I doing wrong?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) AS (VALUES ('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'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (VALUES
('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
 
('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
 

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) AS (VALUES ('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'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (VALUES
('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c'),('Tu'),(8),('b'),('W'),(8),('a'),
 
('W'),(7),('a'),('Th'),(6),('a'),('Th'),(5),('c'),('F'),(5),('b'),('F'),(7),('c')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
 

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> Here is a query that will produce all days of the year (without Sundays) 
> plus their week days (and I've expanded for lesson blocks too, but you 
> will probably need to add/edit as I don't know the exact values, but the 
> method should be clear). You can JOIN this to the other tables 
> containing courses and such to populate the hours table.
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
> ), DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'), 
> (6,'Sa')
> ), LBs(lessonBlock) AS (
>      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
>      UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
> ), RES(dayDate, dayName, lessonBlock) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>    FROM CAL
>    CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>   WHERE CAL.dayId > 0  -- No Sundays
> )
> SELECT *
>    FROM RES

PAR and RES are table names; please tell me what is the meaning of the PAR
and RES abbreviations?
This is what I need to better understand this query.



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> This seems like a whole assignment, and we are not in the habit to do 
> assignments for people,
> 
> BUT, we can get you started down the path.
> 
> You should know how to do all you are asking by simple RDBMS mechanics, 
> except maybe how to initialize a table with all dates and other things 
> pre-populated, so to assist with that
> 
> Here is a query that will produce all days of the year (without Sundays) 
> plus their week days (and I've expanded for lesson blocks too, but you 
> will probably need to add/edit as I don't know the exact values, but the 
> method should be clear). You can JOIN this to the other tables 
> containing courses and such to populate the hours table.

I did not even think that you would do the job for me.
However, thank you very much for the path you showed.
That's what I wanted to, to get me started down the path.
Thank you very much!



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

> Your TEXT NOT NULL fields should be declared as TEXT NOT NULL COLLATE
> NOCASE.  This will simplify your programming later.

I think I am going to write python scripts to use those with my database.
For start I find this:  http://zetcode.com/db/sqlitepythontutorial/
  
I think I could this way add few hundreds of rows to the 'uniqueworkpiece'
by running such a script.
Thank you all for help on this thread!



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

So here it is:
CREATE TABLE IF NOT EXISTS student (
  id INTEGER CONSTRAINT pk_student PRIMARY KEY AUTOINCREMENT, 
  idnum INTEGER UNIQUE NOT NULL COLLATE NOCASE,
  studentname TEXT NOT NULL COLLATE NOCASE,
  teachinglang TEXT NOT NULL COLLATE NOCASE,
  grade INTEGER,
  classname TEXT NOT NULL,
  formmaster TEXT
);
CREATE TABLE IF NOT EXISTS workpiecelist (
  id INTEGER CONSTRAINT pk_workpiecelist PRIMARY KEY AUTOINCREMENT,
  grade INTEGER,
  quarter INTEGER,
  workpiecenamehu TEXT NOT NULL COLLATE NOCASE,
  workpiecenamesr TEXT NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS uniqueworkpiece (
  id INTEGER CONSTRAINT pk_uniqueworkpiece PRIMARY KEY AUTOINCREMENT,
  student TEXT NOT NULL REFERENCES student (idnum) ON DELETE CASCADE ON
UPDATE CASCADE ,
  workpiece_list INTEGER NOT NULL REFERENCES workpiecelist (id) ON DELETE
CASCADE ON UPDATE CASCADE,
  mark INTEGER,
  cause TEXT
);



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

I added CASCADE, like this:
CREATE TABLE IF NOT EXISTS "student" (
  "id" INTEGER CONSTRAINT "pk_student" PRIMARY KEY AUTOINCREMENT, 
  "idchr" TEXT UNIQUE NOT NULL COLLATE NOCASE,
  "studentname" TEXT NOT NULL COLLATE NOCASE,
  "teachinglang" TEXT NOT NULL COLLATE NOCASE,
  "grade" INTEGER,
  "classname" TEXT NOT NULL,
  "formmaster" TEXT
);
CREATE TABLE IF NOT EXISTS "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" INTEGER,
  "quarter" INTEGER,
  "workpiecenamehu" TEXT NOT NULL COLLATE NOCASE,
  "workpiecenamesr" TEXT NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS "uniqueworkpiece" (
  "id" INTEGER CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY AUTOINCREMENT,
  "student" TEXT NOT NULL REFERENCES "student" ("idchr") ON DELETE CASCADE
ON UPDATE CASCADE ,
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id") ON
DELETE CASCADE ON UPDATE CASCADE,
  "mark" INTEGER,
  "cause" TEXT
);



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

I modified my database so it is now like:
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "student" (
  "id" INTEGER CONSTRAINT "pk_student" PRIMARY KEY AUTOINCREMENT, 
  "idchr" TEXT UNIQUE NOT NULL COLLATE NOCASE,
  "studentname" TEXT NOT NULL COLLATE NOCASE,
  "teachinglang" TEXT NOT NULL COLLATE NOCASE,
  "grade" INTEGER,
  "classname" TEXT NOT NULL,
  "formmaster" TEXT
);
CREATE TABLE IF NOT EXISTS "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" INTEGER,
  "quarter" INTEGER,
  "workpiecenamehu" TEXT NOT NULL COLLATE NOCASE,
  "workpiecenamesr" TEXT NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS "uniqueworkpiece" (
  "id" INTEGER CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY AUTOINCREMENT,
  "student" TEXT NOT NULL REFERENCES "student" ("idchr"),
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
  "mark" INTEGER,
  "cause" TEXT
);



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

> SQLite does not support VARCHAR(2).  All fields declared like that are
> TEXT and SQLite pays no attention to the length of the text.  Declare them
> as TEXT.
> 
> SQLite does not support TINYINT   All fields declared like that are
> INTEGER.  Declare them as INTEGER.
> 
> Your TEXT NOT NULL fields should be declared as TEXT NOT NULL COLLATE
> NOCASE.  This will simplify your programming later. 

Understood.

> Why is this field
> 
>>  "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY,
> 
> declared as TEXT when it has 'num' in the name ?
> 
> Although it will work, do not do this:
> 
>>  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student",
>> "workpiece_list")
> 
> instead allow that table to have
> 
> INTEGER PRIMARY KEY AUTOINCREMENT
> 
> like your workpiecelist table, and declare a UNIQUE index to enforce
> uniqueness.  This allows you to make changes without having SQLite
> complain about duplication in the primary key. 

I will in the 'student' table allow an 'id' INTEGER PRIMARY KEY
AUTOINCREMENT .
Every student have an identification number and such a number is 13 digit
long. But some idnumber start with leading zero so I think to it is better
to have this filed as TEXT to keep those leading zeros at the beginning of
the 'idnum'. I do not want to do calculations with 'idnum' values.



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

Don V Nielsen wrote
> Just asking some leading questions. You have students. And students have
> work pieces. You are then creating a list "uniqueworkpiece" showing the
> work pieces associated to each student. Your primary key will ensure the
> uniqueness of the student to work piece.
> 
> Do you also need to ensure that the work piece can have one and only one
> student? Do you need a unique constraint on both student>work piece and
> work piece>student?

Yes, I have students.
Every student belong to one grade and to one class, say 5a where 5 is the
grade ans 'a' is the class.
In the table 'workpiecelist' are workpieces listed, say in grade 5 there are
13 different workpieces out there, in grade 6 there are 14 different
workpieces, in grade 7 there are 7, and in grade 8 there are 14 workpieces
listed.
So, one student in ( or at? ) grade 5 during a school year must to make all
those 13 workpieces, but in the grade 5 class 'a' there are 19 students, and
every one of them must to make 13 workpieces. So I must have the
'uniqueworkpiece' table to associate every student to every workpiece what
that student must to make.

So, the 'uniqueworkpiece' table will have few hundred records.
So it would be for me easier to fill out the 'uniqueworkpiece' table if I
have to enter manually only 'mark' and 'cause' fields, when I am being
examine a unique wprokpiece of a student.

So that is why I am searching for a solution where a piece of code would
fill out for me the 'id' and 'student' fields in the 'uniqueworkpiece'
table.




-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create primary key from two another PK's?

[csanyipal]

I try to follow advices and modify my database so it is now like this:
*CREATE TABLE "student" (
  "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY,
  "studentname" TEXT NOT NULL,
  "teachinglang" VARCHAR(2) NOT NULL,
  "grade" TINYINT,
  "classname" VARCHAR(1) NOT NULL,
  "formmaster" TEXT NOT NULL
);

CREATE TABLE "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" TINYINT,
  "quarter" TINYINT,
  "workpiecenamehu" TEXT NOT NULL,
  "workpiecenamesr" TEXT NOT NULL
);

CREATE TABLE "uniqueworkpiece" (
  "student" TEXT NOT NULL REFERENCES "student" ("idnum"),
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
  "mark" TINYINT,
  "cause" TEXT NOT NULL,
  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")
);

CREATE INDEX "idx_uniqueworkpiece__workpiece_list" ON "uniqueworkpiece"
("workpiece_list")*

What do you think, did I achieve what I want?



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to create primary key from two another PK's?

[csanyipal]

Hi,

I have a small and simple database MyStudents.db .
It has three tables: *student*, *workpiecelist*, *uniqueworkpc*.
How can I manage to get primary key (pk) automatically for *uniqueworkpc*
table which is composed by pk of *student* table and pk of *workpiecelist*
table like below?
03256789415632-2
where
03256789415632
is a pk of a student in *student* table, and
2
is an id of a workpiece in *workpiecelist* table.



-
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users