Question 113442 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Python/ Teradata SQL: Extract data from a table and then do some analysis and put them in a table and then put the related data in other table

I have a main table which contains all columns

student id  name    subject    marks    class
123         John    Maths      56       7
234         Meth    Maths      78       8
456         Jes     Science    67       7
678         Tom     Science    89       8
879         Ria     Maths      92       7
890         Carl    Science    88       8
654         Gerd    Science    77       8

From the above table I want to create two tables where student_id acts as the primary key, and in 1st table I want to extract the student_id, name of all the students where class=8 and in 2nd table I want to put in student_id, name, subject and marks of students from table 1.

Table 1

student id  name        
234         Meth        
678         Tom         
890         Carl        
654         Gerd

Table 2

student id  name    subject    marks    
234         Meth    Maths      78       
678         Tom     Science    89       
890         Carl    Science    88       
654         Gerd    Science    77

How can I do this in Teradata SQL or Python? The above data is just an example. I am dealing with a huge amount of data at once. So I want to find the best way possible.

Thank you:)

Tags:

2

Answers


  1. 0 
    create table students as 
(select unique student_id, "name" from main_table where "class"=8) 
with data primary index (student_id);

create table marks as 
(select s.student_id, s."name", subject, marks 
 from main_table mt, students s 
 where s.student_id=mt.student_id) with data;

    class and name are both reserved words in Teradata, which is why double-quotes.

    p.s. What kind of a student name is Meth, anyway? Is this is a chemistry class or mathematics?

    Login or Signup to reply.
  2. 0

    SQL:

    CREATE MULTISET TABLE table1 AS (
    SELECT
    student_id
    ,name
    FROM maintable
    WHERE class = 8
)WITH DATA PRIMARY INDEX(student_id)

CREATE MULTISET TABLE table2 AS (
    SELECT
    student_id
    ,name
    ,subject
    ,marks
    FROM maintable
    WHERE student_id IN (SELECT student_id FROM table1)
)WITH DATA PRIMARY INDEX(student_id)

    Python:

    data = {'student_id': [123,234,456,678,879,890,654], 
'name':['John', 'Meth', 'Jes', 'tom', 'Ria', 'Carl', 'Gerd'],
'subject':['Maths', 'Maths', 'Science', 'Science', 'Maths', 'Science','Science'],
'marks':[56,78,67,89,92,88,77],
'class':[7,8,7,8,7,8,8]
}

maintable = pd.DataFrame(data=data)
    
table1 = maintable[['student_id', 'name']].where(maintable['class'] == 8).dropna().astype({"student_id": int})
    
    student_id  name
1   234         Meth
3   678         tom
5   890         Carl
6   654         Gerd
        
student_ids = table1['student_id']
table2 = maintable.loc[:, 'student_id':'marks'].where(maintable['student_id'].isin(student_ids)).dropna().astype({"student_id": int, 'marks':int})

    student_id  name    subject  marks
1   234         Meth    Maths    78
3   678         tom     Science  89
5   890         Carl    Science  88
6   654         Gerd    Science  77
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search