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

[R Smith]

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.

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


PS: There are efficiency improvements possible, but this is more to show 
the method than to be 100% processor friendly.


On 2018/03/11 2:05 PM, Csányi Pál wrote:
Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users