Write ten sql select statements to query the student

Assignment Help Computer Engineering

Reference no: EM132216454

Write Ten SQL SELECT statements to query the STUDENT schema you created for practice lab 2. Your Select Statements should run error-free and should be valid.

Submit two separate files: one plain text file (.txt or .sql file) with your SELECT statements only; and the other document (doc/docx/pdf) include both your SELECT 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.

Show the number of students whose zip is 07070.

List all students (display student_id, first name, last name, and employer) who live in Columbus, OH.

Show the zip, state and city that have > 5 students as residents.

List all students (display student_id, first name, last name, and registration date) who registered on or before 2/13/2007 but have not enrolled in any course.

Show the number of students enrolled in the ‘Intro to Unix' course.

List all students (display student_id, first name, last name, and employer) who took courses taught by instructor 'Anita Morris'.

List all distinct courses (display instructor name, course_no and description) taught by 'Anita Morris'.

For all students who took ‘Intro to Unix', calculate the highest, lowest, and average midterm exam grade.

Show which courses have > 10 students enrolled. Display course_no, description and number of students enrolled.

List any student who has enrolled in the same course multiple times. Display student name, course description, and enrollment count

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

I added the student database schema.

Reference no: EM132216454

Questions Cloud

Write a two-tape deterministic turing machine using jflap : Write a two-tape deterministic Turing machine using JFLAP to sort an input string w of the form (a+b)*.

Write an assembly function that tests a floating point : The fact that this can be done easily is the main motivation for biased exponent notation.

Display the details of a student after reading the roll num : Implement a search facility to display the details of a student after reading the roll number, using HTML and PHP.

What demographics can you learn about potential buyers : Visit the U.S. Census website. What information can you find that will help you to estimate the size of the market for your marketing plan topic?

Write ten sql select statements to query the student : Show the number of students whose zip is 07070. List all students (display student_id, first name, last name, and employer) who live in Columbus, OH.

Examine the pricing approach for the dental clinic : A dentist in Cali, Colombia, has decided to target adult orthodontia patients in the Southeastern United States. He offers the Invisalign braces.

Write a program that compares the values of y1 and y2 : For the following T(n) find values of n0 and c such that cn3 is larger than T(n) for all n larger than n0. T(n) = n3 - 5n2 + 20n - 10

Write a sub procedure that plays a simple number guessing : Write a sub procedure that plays a simple number guessing game in five rounds of play.

Does diversity affect ethical decisions : Include how these issues relate to each other. For instance: how do gender differences affect communication? How does technology affect communication.

User Account

All Pages