CODEWITHSUNDEEP
sqlsql-server
GrahamNorton
GrahamNorton

Reputation: 121

Adding Rows to a table based on other Table data

I am trying to Create a table which pulls data from other tables.

For my instance I have 5 students. Each student student takes 3 subjects (Maths, English and Science) Each subject has 3 tests. Which means all 5 students take a total of 9 Tests each throughout the 3 subjects.

I have created the Student Table, the Subject Table and the Test Table. I now am trying to create the Student_Results Table whereby I need to have All 9 tests for all 5 students in one table which will display their given result for their tests.

I have been struggling to get this right.

See tables I created below. 

CREATE TABLE DBO.STUDENTS
(
STUDENT_ID VARCHAR(15) PRIMARY KEY,
STUDENT_FIRSTNAME VARCHAR(255) NOT NULL,
STUDENT_SURNAME VARCHAR(255) NOT NULL,
STUDENT_SCORE_AVERAGE VARCHAR(30)
);

-- Creating the 'Subjects' Table 
CREATE TABLE DBO.SUBJECTS
(
SUBJECT_ID VARCHAR(12) PRIMARY KEY,
SUBJECT_NAME VARCHAR(30) NOT NULL,
SUBJECT_AVERAGE VARCHAR(30)
);

-- Creating the 'Tests' Table 
CREATE TABLE DBO.TESTS
(
TEST_ID VARCHAR(12) PRIMARY KEY,
TEST_NAME VARCHAR(30) NOT NULL,
TEST_DESCRIPTION VARCHAR(50),
TEST_AVERAGE VARCHAR(3)
);

-- Creating the 'Student_Score' Table 
CREATE TABLE DBO.STUDENT_SCORES
(
RESULT_ID VARCHAR (12) PRIMARY KEY,
STUDENT_ID VARCHAR(12) ,
TEST_ID VARCHAR(12),
STUDENT_SCORE VARCHAR(30)
);

create table #TempStudent
    ( 
    STUDENT_KEY INT identity (10000000,1),
    STUDENT_ID  AS CONCAT('STD',STUDENT_KEY),
    STUDENT_FIRSTNAME VARCHAR(255), 
    STUDENT_SURNAME VARCHAR(255)
    )

INSERT INTO #TempStudent
VALUES  ( 'Daenerys' , 'Targaryen' ),
        ( 'Jon' , 'Snow' ),
        ( 'Gregor' , 'Clegane' ),
        ( 'Arya' , 'Stark' ),
        ( 'Cersei' , 'Lannister' )

INSERT INTO STUDENTS (STUDENT_ID, STUDENT_FIRSTNAME , STUDENT_SURNAME)
SELECT STUDENT_ID, STUDENT_FIRSTNAME, STUDENT_SURNAME
FROM #TempStudent

create table #TempSubject
    ( 
    SUBJECT_KEY INT identity (10000000,1),
    SUBJECT_ID  AS CONCAT('SUB',SUBJECT_KEY),
    SUBJECT_NAME VARCHAR(255)
    )

INSERT INTO #TempSubject
VALUES ('Maths'),
       ('Science'),
       ('English')

INSERT INTO SUBJECTS (SUBJECT_ID, SUBJECT_NAME )
SELECT SUBJECT_ID, SUBJECT_NAME
FROM #TempSubject

create table #TempTest
    ( 
    TEST_KEY INT identity (100,1),
    TEST_ID  AS CONCAT('TST',TEST_KEY),
    TEST_NAME VARCHAR(255),
    TEST_DESCRIPTION VARCHAR(255)
    )

INSERT INTO #TempTest
VALUES ('Maths 1', 'Geometry'),
       ('Maths 2', 'Algebra'),
       ('Maths 3', 'Fractions'),
       ('Science 1', 'Astronomy'),
       ('Science 2', 'Biology'),
       ('Science 3', 'Chemistry'),
       ('English 1', 'Grammer'),
       ('English 2', 'Spelling'),
       ('English 3', 'Literature')

INSERT INTO TESTS(TEST_ID, TEST_NAME , TEST_DESCRIPTION )
SELECT TEST_ID, TEST_NAME, TEST_DESCRIPTION
FROM #TempTest

create table #TempScoreSubmission
    ( 
    SCORE_KEY INT identity (1234,1),
    RESULT_ID  AS CONCAT('RES',SCORE_KEY)
    )

Upvotes: 0

Views: 61

Answers (1)

sticky bit
sticky bit

Reputation: 37472

I don't know where you want to go with all that temporary tables and why don't you just create the actual tables with the primary key as an identity and directly insert into them... But to get all pairs of student ID and test ID you can use a cross join. I believe that is what you're searching for.

SELECT s.student_id,
       t.test_id
       FROM dbo.students s
            CROSS JOIN dbo.tests t;

Upvotes: 1

Related Questions