Create a table StudentInfo with following fields ID First Name Last N...

Create a Table StudentInfo Create table StudentInfo StudentID varchar10 NOT NULL F ....

Create a table â€˜StudentInfoâ€™ with following fields: ID First Name Last Name SSN Date of Birth Create a table â€˜ClassInfoâ€™ table: ID Class...

Free

70.2K

Verified Solution

Question

Programming

Create a table â€˜StudentInfoâ€™ with following fields:
ID
First Name
Last Name
SSN
Date of Birth
Create a table â€˜ClassInfoâ€™ table:
ID
Class Name
Class Description
Create a table â€˜RegisteredClassesâ€™ table:
StudentID
ClassID
The RegisteredClasses table should have a foreign keyrelationship to StudentInfo and ClassInfo tables for the respectiveIDs. Also the IDs in StudentInfo and ClassInfo need to be primarykeys.
When you submit the file your email should also contain thefollowing SQL Queries:
Query to show all records from StudentInfo whose first name isJohn
Query to show all records from StudentInfo whose last name isDoe
Query to delete all records from from ClassInfo where classname starts with â€˜ISAM'

Answer & Explanation Solved by verified expert

3.7 Ratings (500 Votes)

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')

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member