Question

1. Create a table ‘StudentInfo’ with following fields:
   1. ID
   2. First Name
   3. Last Name
   4. SSN
   5. Date of Birth
2. Create a table ‘ClassInfo’ table:
   1. ID
   2. Class Name
   3. Class Description
3. Create a table ‘RegisteredClasses’ table:
   1. StudentID
   2. ClassID

The RegisteredClasses table should have a foreign key relationship to StudentInfo and ClassInfo tables for the respective IDs. Also the IDs in StudentInfo and ClassInfo need to be primary keys.

When you submit the file your email should also contain the following SQL Queries:

1. Query to show all records from StudentInfo whose first name is John
2. Query to show all records from StudentInfo whose last name is Doe
3. Query to delete all records from from ClassInfo where class name starts with ‘ISAM'

Answer 1

• Create a Table StudentInfo

Create table StudentInfo(
StudentID varchar(10) NOT NULL,
FirstName varchar(20),
LastName varchar(20),
SSN int,
DateofBirth DATE,
PRIMARY KEY (StudentID)
);

• Create a Table ‘ClassInfo’

  Create table ClassInfo(
ClassID varchar(10) NOT NULL,
ClassName varchar(4),
ClassDescription varchar(20),
PRIMARY KEY ( ClassID)
);

• Create a table ‘RegisteredClasses’​​​​​​​

Create table RegisteredClasses(
FOREIGN KEY (StudentID) REFERENCES Persons(StudentID),
FOREIGN KEY (ClassID) REFERENCES Persons(ClassID)
);

• Query to show all records from StudentInfo whose first name is John

Select * from StudentInfo
where FirstName="John"

• Query to show all records from StudentInfo whose last name is Doe

Select * from StudentInfo
where FirstName="Doe"

• Query to delete all records from from ClassInfo where class name starts with ‘ISAM'

Delete from ClassInfo
WHERE (ClassName REGEXP '^ISAM')