Hi, I have a txt file with the info below (it shows row data with School-List Da

Question

Hi,

I have a txt file with the info below (it shows row data with School-List Dade-County- SpecializedCenters)

Haitian American Senior Center
150 NW 83rd Street
Miami, Florida 33150
Phone:305-758-3561

Miami Gardens Senior Focal Point
16405 NW 25 Avenue
Miami, Florida 33054
Phone:305-628-4354

Jack Orr Senior Center
550 NW 5 St.
Miami, Florida 33137
Phone:305-579-5588

AND I have to create a txt file with something like this. (See below) Obviously with the info that I need about the schools (above). The reason I am asked to create this txt below is because I will insert it in a .csv file in Excel to bulk import into sqlite. PLEASEEE help me to just create the txt file as they ask me to do, and I will take care of the rest. I will give thumbs up :)

CREATE TABLE [Departments] (  
[DepartmentId] INTEGER NOT NULL PRIMARY KEY,  
[DepartmentName] NVARCHAR(50) NULL  
);  
INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Physics');
INSERT INTO Departments VALUES(3, 'Arts');
INSERT INTO Departments VALUES(4, 'Math');

CREATE TABLE [Students] (  
[StudentId] INTEGER PRIMARY KEY NOT NULL,  
[StudentName] NVARCHAR(50) NOT NULL,  
[DepartmentId] INTEGER NULL,
[DateOfBirth] DATE NULL,
FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);  
INSERT INTO Students VALUES(1, 'Michael', 1, '1998-10-12');
INSERT INTO Students VALUES(2, 'John', 1, '1998-10-12');
INSERT INTO Students VALUES(3, 'Jack', 1, '1998-10-12');
INSERT INTO Students VALUES(4, 'Sara', 2, '1998-10-12');
INSERT INTO Students VALUES(5, 'Sally', 2, '1998-10-12');
INSERT INTO Students VALUES(6, 'Jena', NULL, '1998-10-12');
INSERT INTO Students VALUES(7, 'Nancy', 2, '1998-10-12');
INSERT INTO Students VALUES(8, 'Adam', 3, '1998-10-12');
INSERT INTO Students VALUES(9, 'Stevens', 3, '1998-10-12');
INSERT INTO Students VALUES(10, 'George', NULL, '1998-10-12');

CREATE TABLE [Tests] (
[TestId] INTEGER NOT NULL PRIMARY KEY,
[TestName] NVARCHAR(50) NOT NULL,
[TestDate] DATE NULL
);
INSERT INTO [Tests] VALUES(1, 'Mid Term IT Exam', '2015-10-18');
INSERT INTO [Tests] VALUES(2, 'Mid Term Physics Exam', '2015-10-23');
INSERT INTO [Tests] VALUES(3, 'Mid Term Arts Exam', '2015-10-10');
INSERT INTO [Tests] VALUES(4, 'Mid Term Math Exam', '2015-10-15');

CREATE TABLE [Marks] (  
[MarkId] INTEGER NOT NULL PRIMARY KEY,
[TestId] INTEGER NOT NULL,
[StudentId] INTEGER NOT NULL,  
[Mark] INTEGER NULL,
FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
FOREIGN KEY(TestId) REFERENCES Tests(TestId)
);  

INSERT INTO Marks VALUES(1, 1, 1, 18);
INSERT INTO Marks VALUES(2, 1, 2, 20);
INSERT INTO Marks VALUES(3, 1, 3, 16);
INSERT INTO Marks VALUES(4, 2, 4, 19);
INSERT INTO Marks VALUES(5, 2, 5, 14);
INSERT INTO Marks VALUES(6, 2, 7, 20);
INSERT INTO Marks VALUES(7, 3, 8, 20);
INSERT INTO Marks VALUES(8, 3, 9, 20);

Explanation / Answer

The csv file which you are about to create looks as shown below. The first row is the header and the rest of the lines are the actual values.

schoolname,location,state,country,pincode,Phone
Haitian American Senior Center, 150 NW 83rd Street, Miami, Florida 33150,305-758-3561
Miami Gardens Senior Focal Point, 16405 NW 25 Avenue, Miami, Florida 33054,305-628-4354
Jack Orr Senior Center,550 NW 5 St.,Miami, Florida 33137,305-579-5588

The database table design is as follows

CREATE TABLE schooldetails (
   id INT AUTO_INCREMENT PRIMARY KEY,
   schoolname varchar(30),
   location varchar(30),
   state varchar(30),
   country varchar(30),
   pincode int(6),
   phoneno varchar(30)
);

The insert statements are given below.

insert into schooldetails(schoolname,location,state,country,pincode,phoneno) values ('Haitian American Senior Center','150 NW 83rd Street','Miami','Florida',33150,'305-758-3561');
insert into schooldetails(schoolname,location,state,country,pincode,phoneno) values ('Miami Gardens Senior Focal Point', '16405 NW 25 Avenue', 'Miami', 'Florida', 33054,'305-628-4354');
insert into schooldetails(schoolname,location,state,country,pincode,phoneno) values ('Jack Orr Senior Center','550 NW 5 St.','Miami', 'Florida', 33137,'305-579-5588');

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.