Question

1. Write CREAT TABLE statements for the following tables (primary keys are underlined, foreign keys are in italic and bold). Make sure you have all needed constraints and appropriate datatypes for attributes: Student (stID, stName, dateOfBirth, advID, majorName, GPA) Advisor (advID, advName, specialty) 2. Insert several records in each table.

Answer 1

Hi there!

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Solution to the problem :

Create table statement for student table:

CREATE TABLE Student(stID int, stName varchar(50), dateOfBirth date, advID int, majorName varchar(50) , GPA real);

=> Primary key is stID -type integer and foreign key is advID - type integer references Advisor table.

Create table statement for advisor table:

CREATE TABLE Advisor(advID int, advName varchar(50), specialty varchar(100));

=> Primary key is AdvID - type integer

**primary keys are underlined, foreign keys are in italic and bold

INSERTING VALUES INTO STUDENT TABLE: (4 rows)

INSERT INTO Student VALUES (1, 'Rachel', 01-01-2000,2, 'Science',8.8);

INSERT INTO Student VALUES (2, 'Abby', 01-05-2001,3, 'Science',8.2);

INSERT INTO Student VALUES (1, 'Loise', 01-01-1999,2, 'Science',8.0);

INSERT INTO Student VALUES (1, 'Amy', 01-01-1998,1, 'Maths',6.1);

INSERTING VALUES INTO ADVISOR TABLE: (3 rows)

INSERT INTO Advisor VALUES (1,'Rose','Doctor');

INSERT INTO Advisor VALUES (2,'Reene','Masters');

INSERT INTO Advisor VALUES (3,'Alan','Scientist');

Please vote if you find this solution helful.

thank you.