Write 10 SQL statements against the STUDENT schema you created for practice lab

Question

Write 10 SQL statements against the STUDENT schema you created for practice lab 1. Your statements should run error-free and should be valid. Submit two separate files: one plain text file (.txt or .sql file) with your statements only; and the other document (doc/docx/pdf) include both your statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.

1. Display all information about all courses that have no prerequisite
2. Add yourself as a new student to the Student table (use sequence STUDENT_ID_SEQ.NEXTVAL as Student_ID and 07070 as zip)
3. List all students (display Student_ID, first name and last name) who live in zip 07070
4. Show how many students who have phone area code 212 (HINT: use the SUBSTR function)
5. List all student names who work for Electronic Engineers and registered on or after Feb. 3, 2007
6. List all courses that have "Intro to Information Systems" as prerequisite (HINT: use a subquery)
7. For all students who have phone area code 212, change the code to 202 while keeping the same phone number (HINT: consider using functions such as SUBSTR and LENGTH)
8. Delete your own student record added for query 2
9. Lower cost of all courses by 300
10. List all course costs (show course description and cost only) by the order of cost from lowest to highest (show courses without cost first)

STUDENT DATABASE
SCHEMA
ENR_SECT_FK
ENR_STU_FK
GR_ENR_FK
GR_GRTW_FK
GRTW_GRTYP_FK
GRTW_SECT_FK
INST_ZIP_FK
SECT_CRSE_FK
SECT_INST_FK
STU_ZIP_FK
CRSE_CRSE_FK
ENROLLMENT
STUDENT_ID (PK)(FK) NUMBER(8,0) NOT NULL
SECTION_ID (PK)(FK) NUMBER(8,0) NOT NULL
ENROLL_DATE DATE NOT NULL
FINAL_GRADE NUMBER(3,0) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE
GRADE_CODE_OCCURRENCE (PK) NUMBER(38,0) NOT NULL
STUDENT_ID (PK)(FK) NUMBER(8,0) NOT NULL
SECTION_ID (PK)(FK) NUMBER(8,0) NOT NULL
GRADE_TYPE_CODE (PK)(FK) CHAR(2) NOT NULL
NUMERIC_GRADE NUMBER(3,0) NOT NULL
COMMENTS VARCHAR2(2000) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE_CONVERSION
LETTER_GRADE (PK) VARCHAR2(2) NOT NULL
GRADE_POINT NUMBER(3,2) NOT NULL
MAX_GRADE NUMBER(3,0) NOT NULL
MIN_GRADE NUMBER(3,0) NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE_TYPE
GRADE_TYPE_CODE (PK) CHAR(2) NOT NULL
DESCRIPTION VARCHAR2(50) NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
GRADE_TYPE_WEIGHT
SECTION_ID (PK)(FK) NUMBER(8,0) NOT NULL
GRADE_TYPE_CODE (PK)(FK) CHAR(2) NOT NULL
NUMBER_PER_SECTION NUMBER(3,0) NOT NULL
PERCENT_OF_FINAL_GRADE NUMBER(3,0) NOT NULL
DROP_LOWEST CHAR(1) NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
INSTRUCTOR
INSTRUCTOR_ID (PK) NUMBER(8,0) NOT NULL
SALUTATION VARCHAR2(5) NULL
FIRST_NAME VARCHAR2(25) NULL
LAST_NAME VARCHAR2(25) NULL
STREET_ADDRESS VARCHAR2(50) NULL
PHONE VARCHAR2(15) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
ZIP (FK) VARCHAR2(5) NULL
SECTION
SECTION_ID (PK) NUMBER(8,0) NOT NULL
SECTION_NO NUMBER(3,0) NOT NULL
START_DATE_TIME DATE NULL
LOCATION VARCHAR2(50) NULL
CAPACITY NUMBER(3,0) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
COURSE_NO (FK) NUMBER(8,0) NOT NULL
INSTRUCTOR_ID (FK) NUMBER(8,0) NOT NULL
STUDENT
STUDENT_ID (PK) NUMBER(8,0) NOT NULL
SALUTATION VARCHAR2(5) NULL
FIRST_NAME VARCHAR2(25) NULL
LAST_NAME VARCHAR2(25) NOT NULL
STREET_ADDRESS VARCHAR2(50) NULL
PHONE VARCHAR2(15) NULL
EMPLOYER VARCHAR2(50) NULL
REGISTRATION_DATE DATE NOT NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
ZIP (FK) VARCHAR2(5) NOT NULL
ZIPCODE
ZIP (PK) VARCHAR2(5) NOT NULL
CITY VARCHAR2(25) NULL
STATE VARCHAR2(2) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
COURSE
COURSE_NO (PK) NUMBER(8,0) NOT NULL
DESCRIPTION VARCHAR2(50) NOT NULL
COST NUMBER(9,2) NULL
CREATED_BY VARCHAR2(30) NOT NULL
CREATED_DATE DATE NOT NULL
MODIFIED_BY VARCHAR2(30) NOT NULL
MODIFIED_DATE DATE NOT NULL
PREREQUISITE (FK) NUMBER(8,0) NULL

Explanation / Answer

1. SELECT * FROM COURSE WHERE PREREQUISITE IS NULL;

2. INSERT INTO STUDENT VALUES (STUDENT_ID_SEQ.NEXTVAL, 'Mr.', 'John', 'Doe', 'Baker street', ''0123456789', 'XYZ', '2018-12-11 12:00:00', 'ABC', '2018-12-11 12:00:00', 'PQR', '2018-12-11 12:00:00', '07070');

3. SELECT STUDENT_ID, FIRST_NAME, LAST_NAME FROM Student WHERE ZIP = '07070';

4. SELECT COUNT(*) FROM Student WHERE SUBSTR(PHONE, 0, 3) = '212';

5. SELECT FIRST_NAME, LAST_NAME FROM Student WHERE EMPLOYER = 'Electronic Engineers' AND REGISTRATION_DATE >= '2017-02-03';

NOTE: As per Chegg policy, I can answer only 4 questions in a single post. Please post the rest questions separately and I will try to answer them. Hope it helps.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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