Scribd
Upload
13 views2 pages

Write SQL Query To Create All The Tables in This Diagram

The document provides SQL CREATE TABLE queries for six tables: GROUPS, STUDENTS, TEACHERS, SUBJECTS, SUBJECT_TEACHER, and MARKS. Each table is defined with its primary keys and relevant foreign key relationships to establish connections between them. The queries facilitate the creation of a database schema for managing groups, students, teachers, subjects, and their associated marks.

Uploaded by

sahanaediga2506
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views2 pages

Write SQL Query To Create All The Tables in This Diagram

The document provides SQL CREATE TABLE queries for six tables: GROUPS, STUDENTS, TEACHERS, SUBJECTS, SUBJECT_TEACHER, and MARKS. Each table is defined with its primary keys and relevant foreign key relationships to establish connections between them. The queries facilitate the creation of a database schema for managing groups, students, teachers, subjects, and their associated marks.

Uploaded by

sahanaediga2506
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Here are the SQL CREATE TABLE queries for the tables shown in the diagram:

1. GROUPS Table

CREATE TABLE GROUPS (


GROUPID VARCHAR(255) PRIMARY KEY,
GROUPNAME VARCHAR(255)
);

Explanation: This query creates the GROUPS table with GROUPID as the primary key and
GROUPNAME to store the group's name.

2. STUDENTS Table

CREATE TABLE STUDENTS (


STUDENTID VARCHAR(255) PRIMARY KEY,
FIRSTNAME VARCHAR(255),
LASTNAME VARCHAR(255),
GROUPID VARCHAR(255),
FOREIGN KEY (GROUPID) REFERENCES GROUPS(GROUPID)
);

Explanation: This query creates the STUDENTS table, including STUDENTID as the primary
key, FIRSTNAME, LASTNAME, and GROUPID. It also establishes a foreign key relationship
with the GROUPS table using GROUPID.

3. TEACHERS Table

CREATE TABLE TEACHERS (


TEACHERID VARCHAR(255) PRIMARY KEY,
FIRSTNAME VARCHAR(255),
LASTNAME VARCHAR(255)
);

Explanation: This query creates the TEACHERS table with TEACHERID as the primary key,
and FIRSTNAME and LASTNAME for teacher details.

4. SUBJECTS Table

CREATE TABLE SUBJECTS (


SUBJECTID VARCHAR(255) PRIMARY KEY,
TITLE VARCHAR(255)
);

Explanation: This query creates the SUBJECTS table with SUBJECTID as the primary key and
TITLE for the subject's name.
5. SUBJECT_TEACHER Table (Junction Table)

CREATE TABLE SUBJECT_TEACHER (


SUBJECTID VARCHAR(255),
TEACHERID VARCHAR(255),
GROUPID VARCHAR(255),
PRIMARY KEY (SUBJECTID, TEACHERID, GROUPID),
FOREIGN KEY (SUBJECTID) REFERENCES SUBJECTS(SUBJECTID),
FOREIGN KEY (TEACHERID) REFERENCES TEACHERS(TEACHERID),
FOREIGN KEY (GROUPID) REFERENCES GROUPS(GROUPID)
);

Explanation: This query creates SUBJECT_TEACHER, a junction table to handle the many-to-
many relationship between subjects, teachers, and groups. It uses a composite primary key and
sets up foreign key constraints to SUBJECTS, TEACHERS, and GROUPS.

6. MARKS Table

CREATE TABLE MARKS (


MARKSID INT PRIMARY KEY,
STUDENTID VARCHAR(255),
SUBJECTID VARCHAR(255),
MARKSDATE DATE,
MARKS INT,
FOREIGN KEY (STUDENTID) REFERENCES STUDENTS(STUDENTID),
FOREIGN KEY (SUBJECTID) REFERENCES SUBJECTS(SUBJECTID)
);

Explanation: This query creates the MARKS table with MARKSID as the primary key,
STUDENTID, SUBJECTID, MARKSDATE, and MARKS. It includes foreign key constraints to
link marks to STUDENTS and SUBJECTS.

AI responses may include mistakes.

You might also like