PROBLEM SCENARIO A file is to be created from data entered at the keyboard. The
ID: 3872313 • Letter: P
Question
PROBLEM SCENARIO
A file is to be created from data entered at the keyboard. The file (to be called STUDENT FILE) will contain student records (STUDENT RECORD). Refer to Record Structure and Process Requirements below for details
NOTE
The file name in the program will be called STUDENT-FILE.
The file name as saved in permanent memory ( N drive, memory stick etc.) will be called STUFILE.DATor STUFILE.TXT
The record name in the program will be called STUDENT-RECORD.
For the field names, use the names noted in the Record Structure below following proper COBOL coding rules. Abbreviations are acceptable as long as they are meaningful
Record Structure (STUDENT-RECORD)
Each record will contain the following fields (attributes) in this order.
Student Name 20 bytes alphameric
Student Number 9 bytes alphameric
Course Number 7 bytes alphameric
Program ID 7 bytes alphameric
Process Requirements
Once a record has been entered at the keyboard it is to be written to an external file. Please note, as this file is to be a stored data file for other processing, there should be no spacing or any form of editing.
The output record will follow exactly the same structure and sequence as that for the input described above.
Each time through the loop, the program should prompt whether another record is to be entered and if so, prompt for each of the 4 fields to be entered. Please note the prompt whether a record is to be entered should be executed in the Initiation and Mainline routines.
Refer to Screen Layout Requirements below for prescribed screen layout for prompts and data entry positions. Refer to Test Data below for the records to be entered.
You must use the test data provided in the Test Data below.
To view or print the created file, use NOTEPAD, or WORDPAD and retrieve the file.
Screen Layout Requirements
Prompt column line Response column line
RECORD TO ENTER
5
5
Y or N
5
6
ENTER NAME
5
8
Student name
5
9
ENTER NUMBER
5
10
Student number
5
11
ENTER COURSE
5
12
Course number
5
13
ENTER PROGRAM
5
14
Program ID
5
15
Test Data
This is the test data to be used when you run your program. You must use this data.
Jones, John
123456789
CST8283
COMPSCI
Smith, Bill
234567890
CST2234
COMPPRG
----------------------------------------------------------------------------------------------------------------------------------------------------------
Only looking for dump of output file (use Notepad or WordPad to access and print the file created by this program). DONE IN COBOL!!
RECORD TO ENTER
5
5
Y or N
5
6
ENTER NAME
5
8
Student name
5
9
ENTER NUMBER
5
10
Student number
5
11
ENTER COURSE
5
12
Course number
5
13
ENTER PROGRAM
5
14
Program ID
5
15
Explanation / Answer
Create a table Student with fields
RollNo Number (6) Primary key
Name Character (25)
Batch Character (20)
Total Number (4)
Batch have values COMMERCE, SCIENCE, HUMANITIES
SQL> CREATE TABLE STUDENT(
2 ROLLNO NUMBER(6) PRIMARY KEY,
3 NAME CHARACTER(25),
4 BRANCH CHARACTER(20) CHECK(BRANCH IN('COMMERCE','SCIENCE','HUMANITIES')),
5 TOTAL NUMBER(4));
Table created.
Q:2
Show the structure of the STUDENT table.
SQL> DESC STUDENT;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
NAME CHAR(25)
BRANCH CHAR(20)
TOTAL NUMBER(4)
Q:3
Insert data in the fields RollNo, Name, Batch, and Total. (at least 6 records)
SQL> INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL);
Enter value for rollno: 201
Enter value for name: JOHN
Enter value for branch: COMMERCE
Enter value for total: 300
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(201,'JOHN','COMMERCE',300)
1 row created.
SQL> /
Enter value for rollno: 202
Enter value for name: BABU
Enter value for branch: SCIENCE
Enter value for total: 200
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(202,'BABU','SCIENCE',200)
1 row created.
SQL> /
Enter value for rollno: 203
Enter value for name: GEORGE
Enter value for branch: COMPUTER
Enter value for total: 500
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(203,'GEORGE','COMPUTER',500)
INSERT INTO STUDENT VALUES(203,'GEORGE','COMPUTER',500)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C003040) violated
SQL> /
Enter value for rollno: 203
Enter value for name: GEORGE
Enter value for branch: HUMANITIES
Enter value for total: 500
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(203,'GEORGE','HUMANITIES',500)
1 row created.
SQL> /
Enter value for rollno: 204
Enter value for name: JIBIN
Enter value for branch: COMMERCE
Enter value for total: 400
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(204,'JIBIN','COMMERCE',400)
1 row created.
SQL> /
Enter value for rollno: 205
Enter value for name: VARGHESE
Enter value for branch: SCIENCE
Enter value for total: 250
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(205,'VARGHESE','SCIENCE',250)
1 row created.
SQL> /
Enter value for rollno: 206
Enter value for name: SOMAN
Enter value for branch: HUMANITIES
Enter value for total: 420
old 1: INSERT INTO STUDENT VALUES(&ROLLNO,'&NAME','&BRANCH',&TOTAL)
new 1: INSERT INTO STUDENT VALUES(206,'SOMAN','HUMANITIES',420)
1 row created.
Q:4
Display a list of students having mark < 400.
SQL> SELECT * FROM STUDENT WHERE TOTAL < 400;
ROLLNO NAME BRANCH TOTAL
---------- ------------------------- -------------------- ----------
201 JOHN COMMERCE 300
202 BABU SCIENCE 200
205 VARGHESE SCIENCE 250
Q:5
Display the number of students in each batch.
SQL> SELECT BRANCH, COUNT(*) FROM STUDENT GROUP BY BRANCH;
BRANCH COUNT(*)
-------------------- ----------
COMMERCE 2
HUMANITIES 2
SCIENCE 2
Q:6
Display the highest and lowest mark in COMMERCE batch.
SQL> SELECT MIN(TOTAL), MAX(TOTAL) FROM STUDENT WHERE BRANCH='COMMERCE';
MIN(TOTAL) MAX(TOTAL)
---------- ----------
300 400
Q:7
Display the Names of all students in COMMERCE batch.
SQL> SELECT NAME FROM STUDENT WHERE BRANCH ='COMMERCE';
NAME
-------------------------
JOHN
JIBIN
Q:8
Display the highest mark in the table.
SQL> SELECT MAX(TOTAL) FROM STUDENT;
MAX(TOTAL)
----------
500
Q:9
Display the names of students in the descending order of total mark.
SQL> SELECT ROLLNO, NAME, TOTAL FROM STUDENT ORDER BY TOTAL DESC;
ROLLNO NAME TOTAL
---------- ------------------------- ----------
203 GEORGE 500
206 SOMAN 420
204 JIBIN 400
201 JOHN 300
205 VARGHESE 250
202 BABU 200
6 rows selected.
Q:10
Display the details of students with RollNo from 202 to 205.
SQL> SELECT * FROM STUDENT WHERE ROLLNO BETWEEN 202 AND 205;
ROLLNO NAME BRANCH TOTAL
---------- ------------------------- -------------------- ----------
202 BABU SCIENCE 200
203 GEORGE HUMANITIES 500
204 JIBIN COMMERCE 400
205 VARGHESE SCIENCE 250
Q:11
Display the total number of students in each batch in the order of total number.
SQL> SELECT BRANCH, COUNT(*) "Number" FROM STUDENT GROUP BY BRANCH ORDER BY 2;
BRANCH Number
-------------------- ----------
COMMERCE 2
HUMANITIES 2
SCIENCE 2
Q:12
Show all the records from the table.
SQL> SELECT * FROM STUDENT;
ROLLNO NAME BRANCH TOTAL
---------- ------------------------- -------------------- ----------
201 JOHN COMMERCE 300
202 BABU SCIENCE 200
203 GEORGE HUMANITIES 500
204 JIBIN COMMERCE 400
205 VARGHESE SCIENCE 250
206 SOMAN HUMANITIES 420
6 rows selected.
Q:13
Update the branch of student with roll no 201 as Science.
SQL> UPDATE STUDENT SET BRANCH='SCIENCE' WHERE ROLLNO=201;
1 row updated.
Q:14
Display the total number of students in each batch in the order of total number.
SQL> SELECT BRANCH, COUNT(*) "Number" FROM STUDENT GROUP BY BRANCH ORDER BY 2;
BRANCH Number
-------------------- ----------
COMMERCE 1
HUMANITIES 2
SCIENCE 3
Q:15
Display the total number of students in each batch in the descending order of total number.
SQL> SELECT BRANCH, COUNT(*) "Number" FROM STUDENT GROUP BY BRANCH ORDER BY 2 DESC;
BRANCH Number
-------------------- ----------
SCIENCE 3
HUMANITIES 2
COMMERCE 1
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.