SQL assignment

qasimz33

SQLExperienceFall20171.doc

Home >Computer Science homework help >SQL assignment

SQL Experience Project

Fall 2017

2Introduction:

Installing Sql Developer and Connecting to the Database 2

Using SQL Developer on the Bauer Lab PC’s. 4

Using SQL Developer to Create, Edit and Execute SQL Queries 4

Instructions for Submitting SQL Experience Assignments 5

SQL Assignment 1 6

SQL Assignment 2 9

SQL Assignment 3 10

Entity Relationship Diagram for SQL Experience 12

Tables for SQL Experience 14

Introduction:

The 35 queries that constitute the SQL Experience will collectively provide you with a good introduction to SQL. Some are quite easy while others are a bit more challenging. Queries in assignment 1 & 2 are worth 4 points each. There is an optional extra credit opportunity to earn an additional 20 points (2 pts/query). Thus a total of 120 points can be earned on the SQL Experience if you attempt and get correct all 35 queries. The SQL Experience is worth 20% of your overall course grade. The assignment due dates are as follows:

· SQL Experience Assignment #1 (15 queries) is due on Thursday November 6th.

· SQL Experience Assignment #2 (10 queries) is due on Tuesday November 19th

· SQL Experience Assignment #3 (10 queries) optional Extra Credit is due on Tuesday November 19th

The SQL queries you will be working on are based on nine tables that make up a database for the School Scheduling System. The database is stored on Amazon Web Services’ Relational Data Service (RDS) and is accessible via an internet connection. The server is running Oracle Standard Edition One 13.?. You will be required to download and install SQL Developer and then connect to the SQL Experience database.

Installing Sql Developer and Connecting to the Database

Step 1: To begin using the database, you must first download and install SQL Developer. This software can be downloaded from: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html .

Select the download based on your Operating System (Windows, OS X, Linux/Unix). The steps to follow include:

Downloading & Installing SQL Developer 4.0

1. Go to ORACLE.COM and register for a free Oracle Web account

2. Go to the website above and accept OTN License agreement and select the appropriate version of Oracle SQL Developer to download.

3. Login using your Oracle Web Account and download the file

4. Extract the files into a directory on the hard drive (e.g. C:\Program Files\Oracle)

Step 2: You must also have Java SE Developer version 8.0 or higher installed. This software can be downloaded from the following website:

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html The steps to follow include:

Downloading & Installing Java SE Development Kit

1. Go to the website above and accept the license agreement and select the appropriate version to download

2. Follow the instructions in the install wizard. No optional features are needed. Make note of the directory in which you install the software as you will need it later.

3. Click close to exit the wizard once the software installation is complete

Step 3: Once you have successfully installed both Oracle SQL Developer and, if necessary, Java SE Development Kit, you are ready to begin.

1. launch SQL Developer

2. The first time only, you will be prompted to enter the directory where Java SE Development Kit 7u51 is installed

3. In the upper left hand pane, right click on “Connections” and choose “New Connection”.

4. For connection Name, enter SQL Experience

5. For username and password, enter the following information.

a. Username: Your Blackboard Userid

b. Password: Your PeopleSoft ID (include leading zero’s)

6. Check the “Save Password” box

7. For Hostname enter the following exactly as shown: Sqlexperience.cjcp50idto41.us-east-1.rds.amazonaws.com

8. Verify that the port number is 1521

9. Change to SID to orcl

10. Press “Connect” to connect to the server.

Using SQL Developer on the Bauer Lab PC’s.

I expect that the majority of the students will use their own computers for this exercise. If installing the client software on your own computer is not an option, SQL Developer and JAVA JDK has been installed on the middle section of PC's in Lab 272. To use the lab machines:

1. launch SQL Developer

2. The first time only, you will be prompted to enter the directory where Java SE Development Kit 7u51 is installed

3. In the upper left hand pane, right click on “Connections” and choose “New Connection”.

4. For connection Name, enter SQL Experience

5. For username and password, enter the following information.

i. Username: Your Blackboard Userid

ii. Password: Your PeopleSoft ID (include leading zero’s)

6. Check the “Save Password” box.

7. For Hostname enter the following exactly as shown: Sqlexperience.cjcp50idto41.us-east-1.rds.amazonaws.com

8. Verify that the port number is 1521

9. Change to SID to orcl

10. Press “Connect” to connect to the server.

Using SQL Developer to Create, Edit and Execute SQL Queries

Queries are due at 12.59 p.m. on due date. You will submit your queries through Blackboard Learn.!

There are 3 steps to create and save a query:

1. Draft an SQL query that you believe will result in the data needed.

2. Test/debug in SQL Developer

3. Save finished query in appropriate location in the Assignment#.sql file (see Instructions for submitting SQL Experience Assignments below)

Step 1: Draft SQL query that results in the data needed

Enter the query in the worksheet tab on SQL Developer and execute.

Step 2: Test/debug in SQL Developer

In all cases, please refrain from the temptation that your results are correct the first time output from a query is obtained. In other words, please study your output in order to verify the accuracy of the result. There are many feature in SQL Developer to help you create queries, the SQL Developers users guide is available on the our Blackboard classroom under the SQL Experience folder. There are also online tutorials.

Step 3: Save the query in a file.

1. Copy the query from the screen in to clipboard (Ctrl-c)

2. Open Assignment#.sql in a plain text editor like Notepad and copy the query (Ctrl-v) under the appropriate query number.

Instructions for Submitting SQL Experience Assignments

The steps below outline how you will submit your assignments

1. To start the assignment, download Assignment#.sql, where # is the assignment number (1, 2, or 3), from blackboard.

2. One you have completed work on all the queries and have copied the completed queries into Assignment#.sql you are ready to generate the Assignment.lst file that you will turn in.

3. Open Assignment#.sql and copy its contents to the clipboard (Ctrl-A).

4. In Sql Developer paste the contents of Assignment#.sql into the worksheet area.

5. Run the queries in Assignment#.sql by clicking Run Script or F5.

6. Click the Save file icon on the Script Output tool bar and save the query results as Assignment#.lst

7. Upload Assignment#.lst to the appropriate blackboard assignment dropbox.

SQL Assignment 1

(20 Queries): Due Tuesday, November 6th.

The output for each query should include the column headings shown below the statement of the query itself. Thus you will find yourself using column aliases frequently. You must follow the instructions outlined in the Instructions for submitting SQL Experience assignments section of this document to prepare you files for submission . Each time you get into SQL*Plus, please make sure to change the linesize to 200 by entering the SQL*Plus command SET LINESIZE 200 and the Page size to 1000 by entering the SET PAGESIZE 1000. By doing this you will ensure that the column heading appear on once and you should never experience any kind of word wrap when executing a query.

1. Display all information on programs (majors) within in the program field (college) of Business. Hint: use the * operator in the select clause.

PRG_I PRG_FIELD PRG_NAME P PRG_HRS_REQUIRED

----- -------------------- ------------------------- - ----------------

2. Display program name (major), type, field (college) and hours required from Program. Your result should be displayed in ascending order by field and within field by name. Adjust PRG_TYPE column format so the entire field name is displayed.

PRG_NAME PRG_TYPE PRG_FIELD PRG_HRS_REQUIRED

------------------------- -------- -------------------- ----------------

3. Use logical operators AND & OR to display the name, title and salary of professors who make more than $50,000/yr and whose are title is either Professor or Assistant Professor. Sort the results on salary descending. Hint: make sure that you account for the hierarchy of SQL operations when writing the logical expression.

PR_NAME PR_TITLE PR_SALARY

------------------------------ -------------------- ----------

4. For each program (major) display total number of students declared in each major as Number of Students. Sort by major ascending. Label the number of students column as Number of Students.

STU_MAJOR Number of Students

------------------------- ------------------

5. Calculate and display the average salary for professors in each college as Average for those colleges whose total salary budget is greater than $500,000. Format the average salary comma delimited with 2 decimal places.

PR_COLLEGE AVERAGE

-------------------- --------

6. Display the section id, course id, course name, course term, day and time, room number and room type for all sections offered in an auditorium. Order your results in ascending order by room id and course id.

Section ID Course ID Course Name Term Day Room Room Type

---------- ---------- ---------------------------------------- -------- -------- -------- ----------

7. List the majors without any students. Hint: there is a relational algebra set theory operation that is particularly appropriate for this type of query.

Prg_Name

--------

8. List the courses that do not have coordinators assigned. Sort in ascending order.

CRS_COURSE_NAME

----------------------------------------

9. List the course name and id for courses who’s name includes Psychology or Biology, Sort in ascending order by course name.

CRS_ID CRS_COURSE_NAME

-------- ----------------------------------------

10. For each course with a coordinator, list the course id, the name of the course and the name of the professor responsible for coordinating the course. Order the results in ascending order by Course ID.

Course ID Course Name Course Coordinator

---------- ---------------------------------------- ------------------------------

11. Display student schedules. Display student last & first name, section id & term, meeting time, building and room number. Sort by student last name, first name, section and term ascending.

STU_LAST_NAME STU_FIRST_NAME SEC_ID SEC_CRS_ SEC_DAY_ RM_BUILDING RM_NO

-------------------- -------------------- ---------- -------- -------- ----------------------------------- --------

12. List course name, id, credit hours and professor assigned as coordinators. Include in your list information on courses that do not have coordinators. Hint as special type of join is useful here.

CRS_ID CRS_COURSE_NAME CRS_CREDIT_HRS PR_NAME

-------- ---------------------------------------- -------------- ------------------------------

13. List the number of sections offered in each building. Order the results in descending order by number of sections offered.

Building Number of Sections Offered

----------------------------------- --------------------------

14. List the program name (i.e., major) and hours required for a graduate degree where the number of hours required for a graduate degree is less than the number of hours required for an undergraduate degree. Display the program name along with the number of hours required for the undergraduate degree and graduate degree in order to check your work. Hint: this query requires the use of only one table; however, you will need to work with multiple copies of the table.

Major Undergraduate Hours Required Graduate Hours Required

------------------------------ ---------------------------- -----------------------

15. Display the number of students who are freshman, sophomores, juniors and seniors as “Number Enrolled” . Adjust STU_CLASSIFICAITON column format so the entire field name is displayed.

STU_CLASSIFICATION Number Enrolled

------------------ ---------------

SQL Assignment 2

(15 Queries): Due Tuesday, November 19th.

1. List the course names for courses that have sections offered in the Fall term using an uncorrelated sub query.

CRS_COURSE_NAME

----------------------------------------

2. List the name and salaries of professors who earn more than all professors in the college of education. Format the average salary comma delimited with 2 decimal places. Order by salary descending

PR_NAME PR_COLLEGE PR_SALARY

------------------------------ -------------------- -----------

3. Which courses are offered in all terms (fall, spring, summer) at least one time? Display the course id, course name. This query is a good candidate for the use of SELECT DISTINCT. Order the results by Course name. Hint: this query requires relational algebra division.

CRS_COURSE_NAME

----------------------------------------

4. List the names, colleges and salary of professors who do not work in the Natural Sciences college but who earn a salary that is greater than or equal to the highest salary of a professor who works in the Natural Sciences. Round the value of salary to zero decimal places. Order by salary descending

PR_NAME PR_COLLEGE Monthly Salary

------------------------------ -------------------- -------------

5. List all professors’ name, college, salary and department average salary (as “Department Average”) for professors who have a salary that is equal to or greater than the average salary of the professors in their college. Round the salary and average salary to zero decimal places. Order by salary descending. Hint: this query contains a subquery in the FROM clause.

PR_NAME PR_COLLEGE Monthly Salary Department Average

------------------------------ -------------------- ------------- ------------------

6. Display student names and the total number of section taken (as “Sections Taken”), please display students who have yet to take a section. Sort by student name. Hint this requires a Union operation or an outer join operaiton.

PR_TITLE PR_NAME Tenure

-------------------- ------------------------------ ----------

7. Assume that the first digit of each course id represent the level with a 1 representing freshman, 2 representing sophomore, 3 representing junior and 4 representing senior. List the course ids and course names in ascending order by level and within level in ascending order by course id. You may find TRANSLATE function and Order By clause useful on this query. This query should generate the same number of rows as there are rows in the COURSE table.

CRS_COURSE_NAME CRS_ID

---------------------------------------- --------

8. Display the student id as “ID” first name as “First Name”, last name as “Last Name”, city as "City", state as "State" for students who don’t live in Texas and have a 5-digit student ID.

ID First Name Last Name City St

------ -------------------- -------------------- --------------- --

9. List those professors (include all assistant professors, associate professors, and professors in this query) whose names do not include either middle initial or a middle name). Assume that Names do not contain two adjacent blank characters. Hint use INSTR function.

PR_NAME

------------------------------

10. List the names and colleges of all professors with the same last name. This is a challenging query and will give you an opportunity to work with the INSTR and SUBSTR functions.

Professor Name College

------------------------------ --------------------

SQL Assignment 3

(15 queries): Due on November 19th.

1. List the number of sections offered in each building. Order the results in descending order by number of sections offered.

Building Number of Sections Offered

----------------------------------- --------------------------

2. Display the first and last names of students who have not served as graders using a corelated subquery and the not exists operator.

STU_FIRST_NAME STU_LAST_NAME

-------------------- --------------------

3. Display program name as “Dept”, maximum salary as “Max Salary”, minimum salary as “Min Salary”, average salary as “Avg Salary, total salary as “Salary Budget”, departmental head count as “Headcount” for each program. Round salary information to zero decimal places. Hint: user the Count(*) function for headcount.

Dept Max Salary Min Salary Avg Salary Total Salary Budget Headcount

-------------------- ---------- ---------- ---------- ------------------- ----------

4. List room numbers, building and room type for rooms what do not have any sections scheduled, order by building, room type and room number. Hint a set operation may be helpful.

RM_NO RM_BUILDING RM_TYPE

-------- -----------------------------------

5. Which courses are offered during the fall and spring semesters only (not in the Summer)? Display the course id, course name, and term. Order the results in ascending order by course id and term.

Course ID Course Name Term

---------- ---------------------------------------- --------

6. List the college, major, type for majors that do not have advisors. Order by college & major.

PRG_FIELD PRG_NAME P PR_NAME

-------------------- ------------------------- - ------------------------------

7. Display the first and last name of students who’s last name end in a vowel 'a','e','i','o','u'. Hint: the substring and length function may be of use

First Name Last Name

-------------------- --------------------

8. For each College, display the college name, professor name and hire date for the professor with the greatest tenure. Hint you may want to use a correlated subquery.

PR_COLLEGE PR_NAME PR_DATE_H

-------------------- ------------------------------ ---------

9. Display the sum of the salaries of all students who are currently working for Richard Harrington as Salary budget for Harrington.

Salary budget for Harrington

----------------------------

10. Display the name of all courses that are not 3 credit hours.

Course Name

----------------------------------------

EMPLOYMENTEmploysPROFESSORServe_asADVISOR

REGISTRATION

STUDENTSECTIONROOM

COURSECOURSE_PROGRAMPROGRAMWork_forBelongs_toContains1

Administered_by

Contains2

Consists_of

Enrolls_in

Meets_in

(0,n)

(1,n)

(0,n)

(1,n)

(1,1)

(1,1)(0,n)(1,1)

(1,1)

(0,n)

(1,1)

Stu_id

Stu_First_NameStu_Last_Name

Stu_City

Stu_State

Stu_Date_of_Admit

Stu_Class

Stu_Major

Pr_id

Pr_Name

Pr_Date_hired

Pr_Title

Pr_College

Pr_Salary

Prg_id

Prg_Field

Prg_Name

Prg_Type

Prg_Hrs_required

Emp_Stu_id

Emp_Pr_id

Emp_Wage

Emp_Start_date

Emp_End_date

Crs_id

Crs_name

Crs_Credit_hrs

Crs_Pr_id

CP_Prg_id

Crs_id

CP_Requirement_type

Adv_Prg_id

Adv_Pr_id

Rm_no

RM_Building

RM_Type

Sec_id

Sec_crs_id

Sec_crs_Term

Sec_Day_time

Sec_rm_id

Reg_Sec_id

Reg_Stu_id

Coordinates

(

)

(

)

WORK_EXPERIENCE

WRK_STU_ID

WRK_JOB_TITLE

WRK_YEARS

WRK_COMPANY

Worked_At

States

US_STATES

ABBREVIATION

STATE

(0,n)

(1,1)

(0,n)

Work_for Relationship: You will notice that the foreign key Stu_id in the EMPLOYMENT table indicates that a student can work for a professor. In fact, according to the ER diagram, each employment involves a student working for a professor and it is “conceivable” that over time a student could work for several professors. EMPLOYMENT(emp_stu_id)( STUDENT(stu_id))

Employs Relationship: According to the ER diagram, each employment involves a professor employing a student (perhaps as a research assistant) and it is conceivable that a professor could employ several students (e.g., HF3370 Horace Fairlamb employs three students in the sample data).

(In EMPLOYMENT( emp_pr_id) ( PROFESSOR(pr_id)

Serve_as Relationship: While not all professors serve as advisors, it is possible that a professor may advise several academic programs (i.e. majors). For example, in the sample data you will observe that LR4235 advises both the undergraduate and graduate physics programs.

(In ADVISOR( adv_pr_id) ( PROFESSOR(pr_id)

Administered_by Relationship: Each academic program (i.e., major) has one and only one advisor. However, an advisor can advise more than one academic program. ADVISOR(adv_prg_id) ( PROGRAM(prg_id)

Contains1 Relationship: An academic program (i.e., major) can contain many courses. For example, the accounting undergraduate program (ACC01) consists of six courses (ACCT2331, ACCT2332, ACCT3367, ACCT3368, ACCT4330, and ACCT4335). COURSE_PROGRAM (cp_prg_id) ( PROGRAM (Prg_id)

Belongs_to Relationship: A course can belong to more than one academic program (i.e., major). For example, MECE2336 is part of the Electrical Engineering and Mechanical Engineering programs. COURSE_PROGRAM(cp_crs_id) ( COURSE(crs_id))

Contains2 Relationship: A course can be offered in many different sections. SECTION(sec_crs_id) ( COURSE (crs_id)

Meets_in Relationship: While an individual section meets in only one room, an individual room could be the home of many different sections. SECTION(sec_rm_no) ( ROOM (rm_no)

Enrolls_in and Consists_of Relationships: Basically an individual registration involves a student registering for a section of a course.

REGISTRATION(reg_stu_id) ( STUDENT(stu_id)

Coordinate s Relationship: Some professors are responsible for coordinating one or more courses . COURSE(crs_pr_id)( PROFESSOR(pr_id) Not all courses have coordinators assigned.

Worked_for Relationship: Students can can optionally work for many companies WORKED_FOR(wrk_stu_id) ( STUDENT(stu_id)

Grades: Relationship: Each section has at most 1 student assigned to Grade. A student can optionally grade more than one section.

SECTION(sec_grader_id) ( STUDENT(stu_id)

Consists_of Relationship: A given Section can have 1 or more students registered. Students enroll in only 1 section.

REGRISTRAION(reg_sec_id) ( SECTION(sec_id))

Tables for SQL Experience

SQL> describe student

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

STU_ID NOT NULL VARCHAR2(6)

STU_FIRST_NAME VARCHAR2(20)

STU_LAST_NAME VARCHAR2(20)

STU_CITY VARCHAR2(15)

STU_STATE VARCHAR2(2)

STU_DATE_OF_ADMIT DATE

STU_CLASSIFICATION VARCHAR2(2)

STU_MAJOR VARCHAR2(25)

See the PROGRAM table for additional information about the major of each student.

SQL> select * from student order by stu_major, stu_id;

STU_ID STU_FIRST_NAME STU_LAST_NAME STU_CITY STU_STATE STU_DATE_OF_ADMIT STU_CLASSIFICATION STU_MAJOR

------ -------------------- -------------------- --------------- ---------- -------------------- -------------------- -------------------------

BM376 Brooke Mangan Huntsville TX 01-AUG-05 SR Accounting

DD335 Danny Dennis Houston TX 10-JUN-10 JR Accounting

GL3766 Gina Lower Louisville KY 20-AUG-07 SR Accounting

LJ336 Luke Johnson Jackson MS 05-AUG-09 SR Accounting

ML3767 Megan Little Kemah TX 01-JUN-03 SR Accounting

RD300 Ryan Dougherty Chapel Hill NC 22-AUG-09 JR Accounting

DL367 Derek Lincoln Tampa FL 12-JUN-09 JR Anthropology

JD400 Jane Daniels Sugar Land TX 01-JUN-09 JR Biology

JT390 Jennifer Thompson Birmingham AL 11-JUL-11 JR Biology

CB392 Christa Benton Tampa FL 12-JUL-09 JR Chemical Engineering

BD3429 Brent DesRoches Beaumont TX 12-JUN-10 SR Civil Engineering

DS338 Dan Stucky Houston TX 11-JUN-09 SR Civil Engineering

AW316 Andrew Ward Wichita Falls TX 21-JUL-11 SO Communications

CK3601 Carolyn Kieger Galveston TX 23-JUL-10 JR Communications

DE3833 Drew Eder St. Louis MO 20-AUG-10 JR Communications

KM397 Kim Miles Charlotte NC 24-JUL-09 SR Communications

KO3688 Krystion Obie Sugar Land TX 21-AUG-11 JR Communications

SC347 Sharon Cambridge Houston TX 21-AUG-11 JR Communications

WH398 William Hanna Nutley NJ 04-JUL-10 JR Economics

CB3909 Camille Blackerby Fort Worth TX 01-JUL-10 SO Electrical Engineering

JJ3853 Jodi Jakubek Milwaukee WI 04-JUN-10 JR Electrical Engineering

JR3474 Jenna Rubino Houston TX 10-AUG-12 SO Elementary Education

LR3694 Leah Rygh Chicago IL 07-JUN-08 SR Elementary Education

DW3579 Donnie Warner Cincinnati OH 10-JUN-07 SR English

SP400 Samantha Pankey Bellaire TX 20-AUG-09 SR English

AB3491 Alex Bucewicz Houston TX 10-JUL-10 SO Exercise Science

JL288 Jason LaFountain Houston TX 10-JUN-08 SR Exercise Science

KR4000 Kim Rigg Houston TX 20-AUG-11 SO Exercise Science

LD3713 Lindsey Deason Katy TX 01-JUL-08 JR Exercise Science

RL323 Robert Leffew Plano TX 21-JUL-09 SR Exercise Science

SG3331 Stephen Goskowski Boston MA 23-JUN-10 JR Exercise Science

BC3230 Brian Cogbill The Woodlands TX 23-JUN-06 SR Finance

CB3522 Cameron Barber The Woodlands TX 12-JUL-11 SO Finance

CS330 Chris Sager Houston TX 21-AUG-09 SR Finance

LC3161 Lewis Clarke Yoakum TX 13-JUN-09 SO Finance

MT3819 Matt Traina Nashville TN 12-AUG-10 JR Finance

RS381 Raymond Sheddy San Diego CA 09-AUG-11 SO Finance

WK347 Wade Koehl Midland TX 20-JUN-09 SR Finance

CA350 Clif Alexander Floresville TX 12-JUN-09 SR Geology

JT3627 Julie Thompson Milwaukee WI 16-JUN-09 JR History

JO366 JoAnn Overstreet Dallas TX 10-JUL-09 SR Kinesiology

KH339 Kaneshi Hart Houston TX 12-AUG-09 JR Kinesiology

NH359 Nikki Hawkins Dallas TX 11-JUN-09 SR Kinesiology

BQ310 Bradford Quiri Houston TX 23-JUN-08 SR Management

JW313 Justin Whitmore Houston TX 11-JUN-10 JR Management

KB400 Katherine Billue Birmingham AL 21-JUL-09 SR Management

LH3555 Lindsay Hulwick Baton Rouge LA 14-JUL-08 SR Management

MM377 Marc Maddox Jackson MS 23-JUN-08 JR Management

DR380 Daniella Ronderos Norman OK 09-JUN-12 SO Marketing

JK386 Jennifer Keefe Brownsville TX 10-AUG-08 SR Marketing

KM368 Kyle MacKenzie Humble TX 21-AUG-08 SR Marketing

MT304 Melissa Taylor Louisville KY 08-JUL-11 SO Marketing

RS3965 Robyn Smart Denver CO 25-JUN-09 SO Marketing

TM3427 Tommy Manzella New Orleans LA 06-JUN-09 SR Marketing

EZ3492 Emily Zimmerman St. Louis MO 04-JUL-09 SR Mechanical Engineering

MK3512 Mark Kennedy Glencoe IL 22-JUL-09 SR Physics

CG3061 Cassandra Goodson Houston TX 09-AUG-07 SR Psychology

CR395 Christopher Raye Miami FL 11-JUN-07 SR Psychology

DR3776 Daniele Riendeau Memphis TN 05-JUN-12 SO Psychology

DD3438 Drake Diener Bellaire TX 20-JUL-11 JR Sociology

MM3818 Michael Mentz Austin TX 19-JUL-11 SO

61 rows selected.

SQL> describe professor

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

PR_ID NOT NULL VARCHAR2(6)

PR_NAME VARCHAR2(30)

PR_DATE_HIRED DATE

PR_TITLE VARCHAR2(20)

PR_COLLEGE VARCHAR2(20)

PR_SALARY NUMBER

The PR_COLLEGE column in the PROFESSOR table relates to the PRG_FIELD column in the PROGRAM table

SQL> select * from professor order by 5, 4;

PR_ID PR_NAME PR_DATE_HIRED PR_TITLE PR_COLLEGE PR_SALARY

------ ------------------------------ --------------- -------------------- -------------------- ----------

LH4563 Linda Hayes 30-JUN-07 Assistant Professor Business 75000

DH4231 Daniel Edward Hallock 26-MAR-01 Assistant Professor Business 83059

ST4269 Steven E. Trowbridge 16-JAN-08 Assistant Professor Business 75590

DF4259 Donald Dean Fry 21-JUN-11 Assistant Professor Business 83000

CL3370 Colleen R. Logan 23-AUG-09 Associate Professor Business 83500

CS4215 Cynthia D. Schnebly 14-JAN-01 Associate Professor Business 85000

DP4261 Diane Prince 28-JUL-07 Professor Business 107500

JF4283 Jane Frances Fry 21-JUN-11 Assistant Professor Education 58475

SV4213 Sandy S. Venneman 09-DEC-06 Assistant Professor Education 52500

GL4256 Gayla H. Lawson 24-APR-12 Assistant Professor Education 53750

TH4253 Tina Marie Herrington 24-JUL-09 Assistant Professor Education 57525

MM4265 Martin K. Munoz 11-NOV-02 Assistant Professor Education 62500

ED4255 Estella Delossantos 04-AUG-98 Associate Professor Education 60000

MP4267 Marie Plemons 31-DEC-00 Associate Professor Education 59000

RH4205 Richard Harrington 23-FEB-78 Professor Education 85150

RG4218 Richard S. Gunasekera 07-JAN-03 Assistant Professor Engineering 88000

DS2643 David Summers 03-SEP-09 Assistant Professor Engineering 79500

NR3318 Nagarajan Ramamoorthy 13-SEP-08 Assistant Professor Engineering 83500

TM3341 Thomas L. Matula 03-DEC-96 Assistant Professor Engineering 81450

AY4270 Andria Teresa Young 26-OCT-06 Assistant Professor Engineering 76000

HF3370 Horace Fairlamb 10-OCT-76 Professor Engineering 105000

LC4222 Lawrence Hart 09-JAN-02 Assistant Professor Natural Sciences 62350

CK4284 Carol L. Klages 17-JUL-09 Assistant Professor Natural Sciences 65000

DT4201 Delin Tan 24-NOV-09 Assistant Professor Natural Sciences 75000

VN4242 Victor Wayne Nichol 22-MAR-08 Assistant Professor Natural Sciences 80000

DS4236 David R. Satava 29-FEB-00 Associate Professor Natural Sciences 90000

GH3919 George S. Hime 21-JUL-99 Associate Professor Natural Sciences 89900

LR4235 Luh-Yu Ren 20-FEB-96 Associate Professor Natural Sciences 90000

RF4257 Roy L. Foley 30-SEP-80 Professor Natural Sciences 97250

CP4223 Catherine A. Perz 31-MAY-09 Assistant Professor Social Sciences 50000

EH4281 Elizabeth Heywood 19-JUN-03 Assistant Professor Social Sciences 59000

RH4206 Robert Dean Hawkins 12-DEC-08 Assistant Professor Social Sciences 53650

JL3341 June Lu 02-MAY-07 Assistant Professor Social Sciences 55000

PC3300 Peggy Ann Cloninger 23-JUN-10 Assistant Professor Social Sciences 53650

VA3381 Vera Adamchik 02-FEB-95 Assistant Professor Social Sciences 59000

DH4201 Dayton Hall 01-JAN-00 Associate Professor Social Sciences 68500

PC3370 Paul Carlson 12-FEB-87 Professor Social Sciences 87500

NH4254 Nora N. Hutto 03-MAY-90 Professor Social Sciences 81450

HS4212 Harold Lester Smith 30-JUN-80 Professor Social Sciences 82550

39 rows selected.

SQL> describe program

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

PRG_ID NOT NULL VARCHAR2(5)

PRG_FIELD VARCHAR2(20)

PRG_NAME VARCHAR2(25)

PRG_TYPE VARCHAR2(1)

PRG_HRS_REQUIRED NUMBER

The PRG_NAME column ties to the STU_MAJOR column in the STUDENT table on page 1. The PRG_FIELD column relates to the PR_COLLEGE column in the PROFESSOR table.

SQL> select * from program order by prg_field;

PRG_ID PRG_FIELD PRG_NAME PRG_TYPE PRG_HRS_REQUIRED

-------- -------------------- ------------------------- -------- ----------------

MAN01 Business Management U 18

FIN01 Business Finance U 18

ACC01 Business Accounting U 21

MKT01 Business Marketing U 18

ACC02 Business Accounting G 12

FIN02 Business Finance G 12

MAN02 Business Management G 12

MKT02 Business Marketing G 12

KIN01 Education Kinesiology U 39

EXS01 Education Exercise Science U 42

ELE01 Education Elementary Education U 36

ELE02 Education Elementary Education G 30

ADM02 Education Administration G 36

CHE01 Engineering Chemical Engineering U 36

EL01 Engineering Electrical Engineering U 36

CVL01 Engineering Civil Engineering U 36

IND01 Engineering Industrial Engineering U 30

MEC02 Engineering Mechanical Engineering G 36

CHE02 Engineering Chemical Engineering G 36

EL02 Engineering Electrical Engineering G 36

CVL02 Engineering Civil Engineering G 36

IND02 Engineering Industrial Engineering G 30

MEC01 Engineering Mechanical Engineering U 36

GEO02 Natural Sciences Geology G 36

PHY02 Natural Sciences Physics G 42

CHM02 Natural Sciences Chemistry G 45

BIO02 Natural Sciences Biology G 42

PHY01 Natural Sciences Physics U 42

GEO01 Natural Sciences Geology U 39

CHM01 Natural Sciences Chemistry U 42

BIO01 Natural Sciences Biology U 42

PHL01 Social Sciences Philosophy U 30

ANT01 Social Sciences Anthropology U 30

ECO02 Social Sciences Economics G 36

ENG02 Social Sciences English G 48

HIS02 Social Sciences History G 36

POL02 Social Sciences Political Science G 36

PSY02 Social Sciences Psychology G 36

SOC02 Social Sciences Sociology G 36

SOC01 Social Sciences Sociology U 30

PSY01 Social Sciences Psychology U 30

POL01 Social Sciences Political Science U 36

HIS01 Social Sciences History U 36

ENG01 Social Sciences English U 36

ECO01 Social Sciences Economics U 30

COM01 Social Sciences Communications U 36

COM02 Social Sciences Communications G 42

47 rows selected.

SQL> describe employment

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

EMP_PR_ID NOT NULL VARCHAR2(6)

EMP_STU_ID NOT NULL VARCHAR2(6)

EMP_START_DATE DATE

EMP_END_DATE DATE

EMP_WAGE NUMBER

The EMP_PR_ID column relates to the PR_ID column in the PROFESSOR table on page 3. The EMP_STU_ID column relates to the STU_ID column in the STUDENT table..

SQL> select * from employment order by 1, 2;

EMP_PR_ID EMP_STU_ID EMP_START_DATE EMP_END_DATE EMP_WAGE

---------- ------------ --------------- --------------- ----------

CL3370 JW313 15-JUN-12 15-JAN-13 850

DP4261 CS330 07-JUL-12 1000

HF3370 BD3429 01-JUN-12 1000

HF3370 CB392 01-SEP-12 1000

HF3370 JJ3853 01-JAN-12 1200

NH4254 CG3061 14-APR-11 800

RF4257 MK3512 01-SEP-12 31-DEC-12 1500

RH4205 JL288 01-MAY-12 800

RH4205 JO366 11-MAY-11 30-APR-12 800

9 rows selected.

SQL> describe work_experience

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

WRK_STU_ID NOT NULL VARCHAR2(6)

WRK_JOB_TITLE NOT NULL VARCHAR2(30)

WRK_COMPANY NOT NULL VARCHAR2(30)

WRK_YEARS NUMBER

The WRK_STU_ID column relates back to the STU_ID column of the STUDENT table.

SQL> select * from work_experience;

WRK_STU_ID WRK_JOB_TITLE WRK_COMPANY WRK_YEARS

------------ ------------------------------ ------------------------------ ----------

GL3766 Junior Accountant Shell Trading .8

JT390 Lab Technician II M. D. Andersen 1.25

AW316 Assistant Video Coordinator Houston Texans 2

WH398 Teller Coordinator Bank of America .5

SP400 Teaching Assistant Bellaire High School 1.5

AB3491 Swimming Coach Weekley YMCA 2

RL323 Assistant Football Coach Yates High School .25

MT3819 Loan Officer Compass Bank 3

JT3627 Teaching Assistant Bellaire High School

NH359 Physical Therapist Methodist Hospital

DR380 Assistant Buyer Bath and Body Shoppe 1.25

MK3512 Lab Assistant Shell Technology 2.5

MM3818 Caddy River Oaks Country Club

JT390 Lab Technician I Baylor College of Medicine 2

RL323 Assistant Football Coach Bellaire High School

DR380 Inventory Control Clerk Bath and Body Shoppe 1.25

RL323 Head Football Coach Homer Hanna High School

17 rows selected.

SQL> describe course

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

CRS_ID NOT NULL VARCHAR2(8)

CRS_COURSE_NAME VARCHAR2(40)

CRS_CREDIT_HRS NUMBER

CRS_PR_ID VARCHAR2(6)

The CRS_PR_ID column relates back to the PR_ID column of the PROFESSOR table .

SQL> select * from course order by crs_id;

CRS_ID CRS_COURSE_NAME CRS_CREDIT_HRS CRS_PR_ID

-------- ---------------------------------------- -------------- ----------

ACCT2331 Accounting Principles I 3 DH4231

ACCT2332 Accounting Principles II 3 DH4231

ACCT3367 Intermediate Accounting I 3 DP4261

ACCT3368 Intermediate Accounting II 3 DP4261

ACCT4330 Advanced Accounting 3

ACCT4335 Auditing 3

ANTH2301 Physical Anthropology 3

ANTH2302 Cultural Anthropology 3

ANTH3342 Food and Culture 3

BIOL1310 General Biology 3 DT4201

BIOL2333 Elementary Microbiology 3

BIOL3201 Genetics Laboratory 2

BIOL3301 Genetics 3

BIOL4315 Neuroscience 3

CHEE2331 Chemical Processes 3 DS2643

CHEE2332 Thermodynamics 3

CHEE3363 Fluid Mechanics 3

CHEE4366 Biochemical Engineering 3

CHEM1301 Foundations of Chemistry 3 LR4235

CHEM1302 General Organic Chemistry 3 RF4257

CHEM3369 Analytical Chemistry 3

CHEM4336 Fundamental Biochemistry 3

CHEM4370 Physical Chemistry 3

CIVE3332 Engineering Materials 3 TM3341

CIVE3339 Geotechnical Engineering 3

CIVE4363 Concrete Design 3

CIVE4364 Structural Steel Design 3

CIVE4369 Foundation Engineering 3

COMM1302 Communication Theory 3 PC3370

COMM1331 Public Speaking 3 VA3381

COMM1333 Interpersonal Communication 3

COMM2320 Media Production 3

COMM2322 Television Production 3

COMM2370 Film Appreciation 3

COMM3321 Film Production 3

COMM3323 Multimedia Production 3

COMM3368 Public Relations 3 NH4254

COMM4112 Investigating Reporting 1

COMM4310 Opinion Writing 3

ECON2304 Microeconomics 3 JL3341

ECON2305 Macroeconomics 3 JL3341

ECON3365 Labor Economics 3

ECON4365 Econometrics 3

ECON4370 International Trade 3

ELED3320 Reading For Young Children 3

ELED4307 Methods of Teaching Art 3

ELED4308 Methods of Teaching Physical Education 3

ELEE2300 Circuit Analysis 3 RG4218

ELEE3455 Electronics 4

ELEE3456 Analog Electronics 4

ELEE3457 Digital Electronics 4

ELEE4436 Microprocessor Systems 4

ENGL1303 Freshman Composition I 3 PC3300

ENGL1304 Freshman Composition II 3 EH4281

ENGL2306 Introduction to Poetry 3

ENGL2307 Introduction to Drama 3

ENGL2308 Introduction to Nonfiction 3

ENGL3304 Chaucer 3

ENGL3305 English Renaissance 3

ENGL3311 John Milton 3

ENGL3340 Advanced Composition 3

ENGL3362 Women in Literature 3

ENGL4351 Poetry Writing 3

FINA3332 Principles of Financial Management 3 DF4259

FINA3333 Money and Capital Markets 3

FINA4310 Behavioral Finance 3

FINA4320 Investment Management 3

FINA4330 Corporate Finance 3

FINA4350 Options and Futures 3

GEOL1330 Physical Geology 3 CK4284

GEOL3331 Environmental Geology 3

GEOL3333 Earth Resources 3

GEOL3334 Earthquakes 3

GEOL3345 Structural Geology 3

HIST1376 The United States to 1877 3 HS4212

HIST1377 The United States to 1878 3 HS4212

HIST3305 The Old South 3

HIST3308 The American West 3

HIST3339 Ancient Greece 3

HIST3340 Ancient Rome 3

HIST3342 Texas-Mexican History 3

HIST3383 World Revolutions 3

HIST4311 The Age of Roosevelt 3

HIST4330 The Middle Ages 3

INDE2333 Engineering Statistics I 3 HF3370

INDE3310 Statistical Quality Control 3

INDE3333 Engineering Economy 3

INDE3364 Engineering Statistics II 3 HF3370

INDE3381 Linear Optimization 3

INDE3382 Stochastic Models 3

INDE4337 Human Factors and Ergonomics 3

KIN3206 Principles of Coaching 3 GL4256

KIN3300 Motor Development 3

KIN3309 Biomechanics 3

KIN3325 Athletic Training 3

KIN4370 Exercise Testing 3

MANA3335 Management Principles 3 CL3370

MANA4310 Behavioral Finance 3

MANA4334 Industrial Relations 3

MANA4349 Managerial Decision Making 3

MARK3336 Elements of Marketing 3 ST4269

MARK3337 Professional Selling 3 LH4563

MARK4362 Applied Buyer Behavior 3

MARK4372 Services Marketing 3

MARK4374 Sales Management 3

MARK4389 Marketing Strategy 3

MECE2336 Mechanics I 3 AY4270

MECE3336 Mechanics II 3 AY4270

MECE3345 Materials Science 3

MECE3369 Solid Mechanics 3

MECE4364 Heat Transfer 3

PHIL1300 Introduction to Philosophy 3

PHIL1305 Introduction to Ethics 3

PHIL2310 Critical Thinking 3

PHIL3335 Theory of Knowledge 3

PHIL3344 Philosophy of Science 3

PHYS1301 Introductory General Physics 3 LR4235

PHYS3112 Modern Optics 3

PHYS3327 Thermal Physics 3

PHYS4350 Computational Physics 3

POLS1336 Texas Government 3 CP4223

POLS1337 United States Government 3 CP4223

POLS3343 Democractic Theory 3

POLS3349 American Political Thought 3

POLS3359 Criminal Justice 3

POLS3366 Political Parties 3

POLS3369 The Presidency 3

POLS4362 Energy Politics 3

PSYC1300 Introduction to Psychology 3 RH4206

PSYC2344 Cultural Psychology 3

PSYC2350 Child Development 3

PSYC3325 Psychology of Personality 3

PSYC3339 Clinical Psychology 3

PSYC4321 Abnormal Psychology 3

PSYC4376 Work Motivation 3

SOC1300 Introduction to Sociology 3 RH4206

SOC2310 Social Problems 3

SOC3313 Criminology 3

SOC3316 Sociology of Sport and Leisure 3

SOC3343 Social Movements 3

SOC3370 Popular Music and Society 3

SOC3375 Religion and Society 3

142 rows selected.

SQL> describe course_program

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

CP_PRG_ID NOT NULL VARCHAR2(5)

CP_CRS_ID NOT NULL VARCHAR2(8)

CP_REQUIREMENT_TYPE VARCHAR2(10)

The CP_PRG_ID column relates back to the PRG_ID column in the PROGRAM table on page 4. The CP_CRS_ID column relates back to the CRS_ID column of the COURSE table.

SQL> select * from course_program;

CP_PRG_ID CP_CRS_ID CP_REQUIREMENT_TYPE

---------- ---------- --------------------

ACC01 ACCT2331 Required

ACC01 ACCT2332 Required

ACC01 ACCT3367 Required

ACC01 ACCT3368 Required

ACC01 ACCT4330 Required

ACC01 ACCT4335 Elective

FIN01 FINA3332 Required

FIN01 FINA3333 Required

FIN01 FINA4320 Required

FIN01 FINA4330 Required

FIN01 FINA4350 Elective

FIN01 FINA4310 Elective

MAN01 MANA3335 Required

MAN01 MANA4334 Required

MAN01 MANA4349 Required

MAN01 MANA4310 Elective

MKT01 MARK3336 Required

MKT01 MARK3337 Required

MKT01 MARK4362 Elective

MKT01 MARK4374 Elective

MKT01 MARK4389 Elective

ELE01 ELED3320 Required

ELE01 ELED4307 Required

ELE01 ELED4308 Required

EXS01 KIN3206 Elective

EXS01 KIN3300 Required

EXS01 KIN3309 Required

EXS01 KIN3325 Required

EXS01 KIN4370 Required

KIN01 KIN3206 Required

KIN01 KIN3300 Required

KIN01 KIN3309 Required

KIN01 KIN3325 Required

KIN01 KIN4370 Required

CHE01 CHEM1301 Required

CHE01 CHEM1302 Required

CHE01 CHEE2331 Required

CHE01 CHEE2332 Required

CHE01 CHEE3363 Required

CHE01 CHEE4366 Elective

EL01 ELEE2300 Required

EL01 ELEE3456 Required

EL01 ELEE3457 Required

EL01 ELEE4436 Required

EL01 MECE2336 Elective

EL01 MECE3336 Elective

CVL01 CIVE4363 Required

CVL01 CIVE4364 Required

CVL01 CIVE4369 Required

IND01 INDE2333 Required

IND01 INDE3310 Required

IND01 INDE3333 Required

IND01 INDE3364 Required

IND01 INDE3381 Required

IND01 INDE3382 Required

IND01 INDE4337 Required

MEC01 MECE2336 Required

MEC01 MECE3336 Required

MEC01 MECE3345 Required

MEC01 MECE3369 Required

MEC01 MECE4364 Required

MEC01 CHEE2332 Elective

ANT01 ANTH2301 Required

ANT01 ANTH2302 Required

ANT01 ANTH3342 Required

ANT01 SOC1300 Required

ANT01 PSYC1300 Required

COM01 COMM1302 Required

COM01 COMM1331 Required

COM01 COMM1333 Required

COM01 COMM2320 Elective

COM01 COMM2322 Elective

COM01 COMM2370 Elective

COM01 COMM3321 Elective

COM01 COMM3323 Elective

COM01 COMM3368 Elective

COM01 COMM4112 Elective

COM01 COMM4310 Elective

ECO01 ECON2304 Required

ECO01 ECON2305 Required

ECO01 ECON3365 Required

ECO01 ECON4365 Required

ECO01 ECON4370 Elective

ENG01 ENGL1303 Required

ENG01 ENGL1304 Required

ENG01 ENGL2306 Required

ENG01 ENGL2307 Required

ENG01 ENGL2308 Required

ENG01 ENGL3304 Elective

ENG01 ENGL3305 Elective

ENG01 ENGL3311 Elective

ENG01 ENGL3340 Elective

ENG01 ENGL3362 Elective

ENG01 ENGL4351 Elective

HIS01 HIST1376 Required

HIS01 HIST1377 Required

HIS01 HIST3305 Elective

HIS01 HIST3308 Elective

HIS01 HIST3339 Elective

HIS01 HIST3340 Elective

HIS01 HIST3342 Elective

HIS01 HIST3383 Elective

HIS01 HIST4311 Elective

HIS01 HIST4330 Elective

PHL01 PHIL1300 Required

PHL01 PHIL1305 Required

PHL01 PHIL2310 Required

PHL01 PHIL3344 Required

POL01 POLS1337 Required

POL01 POLS1336 Required

POL01 POLS3343 Required

POL01 POLS3359 Required

POL01 POLS3369 Elective

POL01 POLS4362 Elective

PSY01 PSYC1300 Required

PSY01 SOC1300 Required

PSY01 PSYC2344 Required

PSY01 PSYC2350 Required

PSY01 PSYC3325 Required

PSY01 PSYC3339 Elective

PSY01 PSYC4321 Elective

PSY01 PSYC4376 Elective

PSY01 PHIL1300 Required

SOC01 SOC1300 Required

SOC01 PSYC1300 Required

SOC01 SOC2310 Required

SOC01 SOC3313 Required

SOC01 SOC3316 Elective

SOC01 SOC3343 Required

SOC01 SOC3370 Elective

SOC01 SOC3375 Elective

SOC01 PHIL1300 Required

BIO01 BIOL1310 Required

BIO01 BIOL2333 Required

BIO01 BIOL3201 Required

BIO01 BIOL3301 Required

BIO01 BIOL4315 Required

BIO01 CHEM1301 Required

BIO01 CHEM1302 Required

CHM01 CHEM1301 Required

CHM01 CHEM1302 Required

CHM01 CHEM3369 Required

CHM01 CHEM4336 Required

CHM01 CHEM4370 Required

GEO01 GEOL1330 Required

GEO01 GEOL3331 Required

GEO01 GEOL3333 Required

GEO01 GEOL3334 Required

GEO01 GEOL3345 Required

PHY01 PHYS1301 Required

PHY01 PHYS3112 Required

PHY01 PHYS3327 Required

PHY01 PHYS4350 Required

PHY01 CHEM1301 Required

PHY01 CHEM1302 Required

155 rows selected.

SQL> describe advisor

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

ADV_PRG_ID NOT NULL VARCHAR2(5)

ADV_PR_ID NOT NULL VARCHAR2(6)

The ADV_PRG_ID column relates back to the PRG_ID column of the PROGRAM table on page 4. The ADV_PR_ID column relates back to the PR_ID column of the PROFESSOR table .

SQL> select * from advisor;

ADV_PRG_ID ADV_PR_ID

------------ ------------

MEC01 AY4270

GEO01 CK4284

MAN01 CL3370

POL01 CP4223

FIN01 DF4259

ACC01 DH4231

CHE01 DS2643

BIO01 DT4201

EXS01 GL4256

KIN01 GL4256

IND01 HF3370

HIS01 HS4212

ECO01 JL3341

CHM01 LR4235

PHY01 LR4235

PHY02 LR4235

CHM02 RF4257

EL01 RG4218

ANT01 RH4206

PSY01 RH4206

SOC01 RH4206

MKT01 ST4269

COM01 VA3381

23 rows selected.

SQL> describe room;

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

RM_NO NOT NULL VARCHAR2(8)

RM_BUILDING VARCHAR2(35)

RM_TYPE VARCHAR2(15)

SQL> select * from room order by 2, 1, 3;

RM_NO RM_BUILDING RM_TYPE

-------- ----------------------------------- ---------------

AH102 Agnes Arnold Hall Lecture

AH103 Agnes Arnold Hall Lecture

AH104 Agnes Arnold Hall Lecture

AH107 Agnes Arnold Hall Lecture

AH122 Agnes Arnold Hall Lecture

AH123 Agnes Arnold Hall Lecture

AH128 Agnes Arnold Hall Seminar

AH150 Agnes Arnold Hall Auditorium

D130 Engineering Complex Lecture

D135 Engineering Complex Lecture

D137 Engineering Complex Laboratory

D150 Engineering Complex Auditorium

D160 Engineering Complex Auditorium

D230 Engineering Complex Lecture

D232 Engineering Complex Lecture

D237 Engineering Complex Seminar

D238 Engineering Complex Seminar

D300 Engineering Complex Laboratory

D310 Engineering Complex Laboratory

D320 Engineering Complex Laboratory

H101 Heyne Building Lecture

H102 Heyne Building Lecture

H103 Heyne Building Lecture

H104 Heyne Building Lecture

H110 Heyne Building Seminar

H114 Heyne Building Seminar

H116 Heyne Building Seminar

H200 Heyne Building Lecture

H206 Heyne Building Lecture

H210 Heyne Building Lecture

H220 Heyne Building Seminar

H250 Heyne Building Lecture

H260 Heyne Building Lecture

PGH302 Hoffman Hall Lecture

PGH304 Hoffman Hall Lecture

PGH306 Hoffman Hall Lecture

PGH308 Hoffman Hall Lecture

PGH320 Hoffman Hall Auditorium

PGH420 Hoffman Hall Lecture

PGH422 Hoffman Hall Lecture

PGH450 Hoffman Hall Seminar

PGH550 Hoffman Hall Seminar

PGH650 Hoffman Hall Seminar

F100 Lamar Fleming Building Lecture

F110 Lamar Fleming Building Lecture

F120 Lamar Fleming Building Laboratory

F121 Lamar Fleming Building Laboratory

F122 Lamar Fleming Building Laboratory

F130 Lamar Fleming Building Lecture

F140 Lamar Fleming Building Lecture

F150 Lamar Fleming Building Lecture

F180 Lamar Fleming Building Auditorium

MH101 Melcher Hall Lecture

MH102 Melcher Hall Lecture

MH103 Melcher Hall Lecture

MH104 Melcher Hall Lecture

MH105 Melcher Hall Lecture

MH121 Melcher Hall Lecture

MH122 Melcher Hall Lecture

MH123 Melcher Hall Lecture

MH124 Melcher Hall Lecture

MH125 Melcher Hall Lecture

MH150 Melcher Hall Auditorium

MH160 Melcher Hall Auditorium

MH170 Melcher Hall Auditorium

MH180 Melcher Hall Auditorium

MH248 Melcher Hall Lecture

MH290 Melcher Hall Seminar

MH302 Melcher Hall Seminar

C100 Roy Cullen Building Lecture

C103 Roy Cullen Building Lecture

C110 Roy Cullen Building Lecture

C113 Roy Cullen Building Seminar

C115 Roy Cullen Building Seminar

C120 Roy Cullen Building Lecture

C123 Roy Cullen Building Lecture

C140 Roy Cullen Building Auditorium

C201 Roy Cullen Building Seminar

C204 Roy Cullen Building Seminar

SR100 Science and Research Auditorium

SR110 Science and Research Auditorium

SR120 Science and Research Lecture

SR125 Science and Research Lecture

SR130 Science and Research Lecture

SR135 Science and Research Lecture

SR140 Science and Research Lecture

SR200 Science and Research Lecture

SR210 Science and Research Lecture

SR220 Science and Research Lecture

SR230 Science and Research Lecture

SR320 Science and Research Laboratory

SR322 Science and Research Laboratory

SR330 Science and Research Laboratory

SR340 Science and Research Laboratory

94 rows selected.

SQL> describe section

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

SEC_ID NOT NULL NUMBER

SEC_GRADER_ID VARCHAR2(6)

SEC_CRS_ID VARCHAR2(8)

SEC_CRS_TERM VARCHAR2(8)

SEC_DAY_TIME VARCHAR2(8)

SEC_RM_ID VARCHAR2(8)

The SEC_CRS_ID column relates to the COURSE table on page 7 while the SEC_RM_ID column relates to the ROOM table.

SQL> select * from section order by sec_crs_id, sec_id;

SEC_ID SEC_GRADER_ID SEC_CRS_ID SEC_CRS_TERM SEC_DAY_TIME SEC_RM_ID

---------- --------------- ------------ -------------- --------------- --------------

70 ACCT2332 FALL MWF9 MH101

71 ACCT2332 FALL MWF9 MH102

72 ACCT2332 FALL MWF9 MH103

73 ACCT2332 FALL MWF9 MH104

74 ACCT2332 FALL MWF9 MH105

75 ACCT2332 SPRING MWF9 MH121

76 ACCT2332 SPRING MWF10 MH121

77 ACCT2332 SPRING MWF11 MH121

78 ACCT2332 SPRING MWF12 MH121

79 ACCT2332 SPRING MWF1 MH121

80 ACCT2332 SUMMER MTWTF11 MH160

105 ACCT3367 FALL TT8:30 MH248

106 ACCT3367 SPRING TT8:30 MH248

107 ACCT3367 SUMMER MTWTF9 MH248

132 ACCT3368 FALL MWF10 MH248

133 ACCT3368 SPRING MWF10 MH248

134 ACCT3368 SUMMER MTWTF11 MH248

42 ANTH2301 FALL MWF11 PGH320

43 ANTH2301 SPRING MWF11 AH150

44 ANTH2301 SUMMER MTWTF10 AH123

166 ANTH3342 SPRING TT4 AH128

1 BIOL1310 FALL MWF10 SR100

2 BIOL1310 SPRING MWF10 SR110

3 BIOL1310 SUMMER MTWTF10 F180

81 BIOL3201 FALL MT3 F121

151 BIOL3301 FALL MWF11 F122

152 BIOL3301 SUMMER MTWTH11 F122

108 CHEE2331 FALL TT11:30 D135

109 CHEE2331 SPRING TT11:30 D135

110 CHEE2331 SUMMER MTWTF2 D135

45 CHEE3363 FALL MWF3 D310

135 CHEM1301 FALL MWF10 SR100

136 CHEM1301 FALL MWF1 SR100

137 CHEM1301 SPRING MWF10 SR100

138 CHEM1301 SUMMER MTWTF10 SR110

4 CHEM3369 FALL TT10 F140

82 CHEM4336 SPRING TT8:30 SR140

153 CIVE4369 SPRING MWF9 D137

46 COMM1302 FALL MWF12 AH150

47 COMM1302 SPRING MWF12 AH150

48 COMM1302 SUMMER MTWTF12 AH122

124 COMM2320 FALL MWF4 AH150

125 COMM2320 SPRING MWF4 PGH320

5 COMM2322 FALL MWF11 AH102

6 COMM2322 SPRING MWF2 AH123

83 COMM2370 FALL MWF2 AH107

84 COMM2370 SPRING MWF2 AH107

139 COMM3323 FALL MWF1 PGH420

140 COMM3323 SPRING TT2:30 PGH420

167 COMM3368 FALL MWF12 AH122

168 COMM3368 SPRING MWF4 AH102

169 COMM3368 SUMMER MTWTF2 AH122

49 COMM4310 FALL MWF3 AH128

111 ECON2305 FALL MWF12 H104

112 ECON2305 FALL MWF1 H104

113 ECON2305 FALL MWF2 H104

114 ECON2305 SPRING MWF8 H104

115 ECON2305 SPRING MWF8 H103

116 ECON2305 SUMMER MTWTF10 H260

154 ECON3365 FALL TT4 H103

7 ECON4365 SPRING TT8:30 H260

85 ECON4370 FALL MWF11 H103

126 ELEE2300 FALL MWF2 D150

127 ELEE2300 SPRING MWF2 D150

128 ELEE2300 SUMMER MTWTF12 D230

50 ELEE3456 SPRING MTWT4 D310

170 ELEE3457 FALL MWF12 D238

141 ELEE4436 SPRING TT2:30 D310

8 ENGL1304 FALL MWF8 C103

9 ENGL1304 FALL MWF8 C100

10 ENGL1304 FALL MWF8 C110

11 ENGL1304 FALL MWF10 C103

12 ENGL1304 FALL MWF10 C123

13 ENGL1304 SPRING MWF8 C103

14 ENGL1304 SPRING MWF8 C123

15 ENGL1304 SUMMER MTWTF8 C140

16 ENGL1304 SPRING MTWTF8 C140

86 ENGL2306 FALL MWF11 C123

87 ENGL2306 SPRING MWF2 C123

88 ENGL2306 SPRING TT2:30 C123

89 ENGL2306 SUMMER MTWTF10 C123

155 ENGL2307 FALL MWF10 C100

156 ENGL2307 SPRING MWF10 C103

157 ENGL2307 SPRING MWF10 C110

158 ENGL2307 SPRING MWF3 C103

51 ENGL3311 FALL TT1 C204

17 FINA3332 FALL MWF10 MH160

18 FINA3332 SPRING TT11:30 MH160

19 FINA3332 SUMMER MTWTF12 MH160

90 FINA3333 FALL MWF10 MH101

91 FINA3333 FALL MWF11 MH101

92 FINA3333 SPRING TT10 MH101

93 FINA3333 SPRING TT10 MH101

94 FINA3333 SUMMER MTWTF11 MH125

146 FINA4310 SPRING MWF12 MH302

117 FINA4320 FALL MWF9 MH123

118 FINA4320 SPRING MWF9 MH123

142 FINA4330 FALL MWF1 MH124

143 FINA4330 SPRING MWF1 MH124

159 GEOL1330 FALL MWF11 SR100

160 GEOL1330 FALL TTF11:30 SR100

161 GEOL1330 SPRING MWF11 SR110

162 GEOL1330 SUMMER MTWTF8 SR100

52 GEOL3331 FALL MWF2 SR200

53 GEOL3331 SPRING MWF2 SR200

20 HIST1377 FALL MWF8 AH150

21 HIST1377 FALL TT10 AH150

22 HIST1377 FALL MWF2 AH150

23 HIST1377 SPRING MWF8 AH150

24 HIST1377 SPRING TT10 AH150

25 HIST1377 SPRING MWF2 AH150

26 HIST1377 SUMMER MWF10 AH150

95 HIST3305 SPRING TT2:30 AH128

171 HIST3308 SUMMER MTWTF2 AH128

119 HIST3339 SUMMER MTWTF12 AH128

129 HIST3340 SPRING TT2:30 AH128

54 HIST3342 FALL MWF2 AH128

144 HIST4311 FALL MWF2 AH107

27 INDE3310 SPRING MWF3 D135

96 INDE3333 FALL MWF2 D230

97 INDE3333 SPRING TT11:30 D230

55 INDE4337 SPRING MWF11 D130

28 KIN4370 FALL MWF11 AH104

29 KIN4370 SPRING MWF11 AH104

98 MANA3335 FALL MWF8 MH160

99 MANA3335 SPRING TT8:30 MH160

100 MANA3335 SUMMER MTWTF10 MH160

145 MANA4310 SPRING MWF12 MH302

56 MARK3336 FALL MWF10 MH170

57 MARK3336 FALL MWF12 MH170

58 MARK3336 SPRING MWF10 MH170

59 MARK3336 SPRING MWF12 MH170

60 MARK3336 SUMMER MTWTF2 MH170

120 MARK4362 FALL MWF2 MH125

121 MARK4362 SPRING TT1 MH121

172 MARK4374 FALL TT11:30 MH105

173 MARK4374 SPRING TT11:30 MH125

30 MECE2336 FALL TT8:30 D230

31 MECE2336 SPRING TT8:30 D230

32 MECE2336 SUMMER MTWTF9 D232

101 MECE3336 FALL MWF9 D232

102 MECE3336 SPRING MWF9 D232

163 MECE3369 FALL MWF9 D237

61 PHIL1300 FALL MWF10 PGH320

62 PHIL1300 SPRING MWF10 PGH320

63 PHIL1300 SUMMER MTWTF10 MH180

147 PHIL2310 FALL TT10 MH101

148 PHIL2310 FALL TT11:30 MH101

149 PHIL2310 SPRING TT10 AH104

150 PHIL2310 SPRING TT10 AH107

122 PHIL3344 FALL MWF3 AH128

33 PHYS1301 FALL MWF9 SR100

34 PHYS1301 SPRING MWF9 SR100

35 PHYS1301 SUMMER MWF9 SR100

103 PHYS3112 FALL W9 F121

64 POLS1337 FALL MWF11 AH150

65 POLS1337 FALL MWF11 F180

66 POLS1337 SPRING TT2:30 AH150

67 POLS1337 SPRING TT1 AH150

68 POLS1337 SUMMER MTWTF2 AH150

164 POLS3343 SPRING MWF11 PGH550

36 PSYC1300 FALL MWF1 MH180

37 PSYC1300 FALL MWF2 MH180

38 PSYC1300 SPRING MWF1 MH180

39 PSYC1300 SPRING MWF1 MH170

40 PSYC1300 SUMMER MTWTF1 MH180

130 PSYC2350 FALL TT1 H210

131 PSYC2350 SPRING TT2:30 H210

69 PSYC4321 FALL TT1 H206

104 PSYC4376 SPRING MWF3 H114

165 SOC3313 FALL MWF10 PGH308

41 SOC3316 SPRING MWF10 PGH304

123 SOC3370 SPRING MWF10 PGH422

173 rows selected.

SQL> describe registration

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

REG_SEC_ID NUMBER

REG_STU_ID VARCHAR2(6)

The REG_SEC_ID column relates to the SECTION table on page 16 while the REG_STU_ID column relates to the STUDENT table.

SQL> select * from registration order by 2, 1;

REG_SEC_ID REG_STU_ID

---------- ----------

1 DR3776

1 KR4000

2 KH339

4 CB392

4 JT390

4 MK3512

5 CK3601

5 DE3833

5 KO3688

5 SC347

9 KR4000

10 LC3161

10 RS381

11 AB3491

11 JR3474

14 KH339

17 BM376

17 LJ336

21 CB3522

21 DR380

21 JD400

21 JJ3853

21 JT390

21 MM3818

23 DL367

24 KH339

28 JL288

29 JO366

29 KH339

29 NH359

29 RL323

29 SG3331

31 EZ3492

34 BD3429

37 AB3491

37 AW316

37 DL367

37 DR3776

37 JR3474

37 MT304

37 RS3965

40 KH339

41 JO366

41 NH359

41 RL323

41 SG3331

42 DL367

43 CK3601

45 CB392

46 CA350

46 CB3522

46 KR4000

49 KM397

51 DW3579

51 KM397

51 SP400

52 CA350

54 CS330

54 JT3627

57 MT3819

60 GL3766

60 ML3767

61 KR4000

62 LC3161

62 RS381

64 AB3491

64 JJ3853

64 JR3474

65 CK3601

65 DE3833

65 KO3688

65 SC347

66 DR3776

66 DR380

66 MM3818

69 BM376

69 DR3776

69 LJ336

70 CB3522

71 AW316

71 MT304

71 RS3965

73 DR380

73 MM3818

74 RD300

78 LC3161

78 RS381

81 JT390

82 MK3512

85 GL3766

85 ML3767

85 WH398

86 CK3601

86 DE3833

86 DR380

86 DW3579

REG_SEC_ID REG_STU_ID

---------- ----------

86 KO3688

86 MM3818

86 SC347

86 SP400

88 JO366

90 MT3819

91 CS330

93 BM376

93 LJ336

96 CB392

97 EZ3492

98 KM368

98 TM3427

99 BD3429

99 JW313

99 KB400

102 EZ3492

103 MK3512

104 CG3061

104 CR395

104 DR3776

104 JK386

105 BM376

105 GL3766

105 LJ336

105 ML3767

105 MT3819

106 DD335

111 DR3776

112 AW316

112 DR380

112 LC3161

112 MM3818

112 MT304

112 RS381

112 RS3965

117 CS330

117 GL3766

117 ML3767

117 MT3819

117 WH398

118 BC3230

119 BQ310

119 KB400

119 LH3555

120 BM376

120 KM368

120 LJ336

120 TM3427

121 BQ310

121 JK386

121 JW313

121 KB400

121 LH3555

122 DS338

122 KM397

123 CG3061

123 CK3601

124 CK3601

124 DE3833

124 KO3688

124 SC347

129 BC3230

129 DE3833

129 JT3627

129 SG3331

129 WK347

130 CR395

130 DR3776

130 DW3579

132 CS330

133 BM376

133 GL3766

133 LJ336

133 ML3767

133 WK347

134 DD335

136 AB3491

136 CB3522

136 JD400

136 JR3474

137 LC3161

137 RS381

140 CK3601

142 KM368

142 TM3427

142 WH398

143 BC3230

143 GL3766

143 ML3767

143 WK347

144 SP400

145 BQ310

145 JK386

145 JW313

145 KB400

145 LH3555

145 WK347

146 BQ310

146 JW313

146 KB400

146 LH3555

148 JD400

148 RD300

150 EZ3492

151 JD400

151 JT390

153 BD3429

153 DS338

154 WH398

155 DL367

155 DW3579

155 JD400

155 RD300

155 SP400

158 DE3833

158 DR3776

159 AW316

159 MT304

159 RS3965

160 DR3776

163 JJ3853

164 CG3061

164 JT3627

165 DD3438

165 KO3688

165 SC347

167 KM397

168 DE3833

168 DS338

169 BQ310

169 CK3601

169 KB400

169 LH3555

170 JJ3853

172 KM368

172 TM3427

173 BC3230

173 JK386

236 rows selected

SQL> describe work_experience

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

WRK_STU_ID NOT NULL VARCHAR2(6)

WRK_JOB_TITLE NOT NULL VARCHAR2(30)

WRK_COMPANY NOT NULL VARCHAR2(30)

WRK_YEARS NUMBER

SQL> select * from work_experience;

WRK_STU_ID WRK_JOB_TITLE WRK_COMPANY WRK_YEARS

------------ ------------------------------ ------------------------------ ----------

GL3766 Junior Accountant Shell Trading .8

JT390 Lab Technician II M. D. Andersen 1.25

AW316 Assistant Video Coordinator Houston Texans 2

WH398 Teller Coordinator Bank of America .5

SP400 Teaching Assistant Bellaire High School 1.5

AB3491 Swimming Coach Weekley YMCA 2

RL323 Assistant Football Coach Yates High School .25

MT3819 Loan Officer Compass Bank 3

JT3627 Teaching Assistant Bellaire High School

NH359 Physical Therapist Methodist Hospital

DR380 Assistant Buyer Bath and Body Shoppe 1.25

MK3512 Lab Assistant Shell Technology 2.5

MM3818 Caddy River Oaks Country Club

JT390 Lab Technician I Baylor College of Medicine 2

RL323 Assistant Football Coach Bellaire High School

DR380 Inventory Control Clerk Bath and Body Shoppe 1.25

RL323 Head Football Coach Homer Hanna High School

17 rows selected.

SQL> describe months

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

MNUM VARCHAR2(2)

MNAME VARCHAR2(10)

SQL> select * from months;

MNUM MNAME

----- ----------

01 January

02 Februrary

03 March

04 April

05 May

06 June

07 July

08 August

09 September

10 October

11 November

12 December

12 rows selected.

SQL> describe us_states

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- -------------------------

STATE VARCHAR2(20)

ABBREVIATION VARCHAR2(2)

CAPITAL VARCHAR2(20)

SQL> select * from us_states;

STATE ABBREVIATION CAPITAL

-------------------- -------------------- --------------------

Alabama AL Montgomery

Alaska AK Juneau

Arizona AZ Phoenix

Arkansas AR Little Rock

California CA Sacremento

Colorado CO Denver

Connecticut CT Hartford

Delaware DE Dover

Florida FL Tallahassee

Georgia GA Atlanta

Hawaii HI Honolulu

Idaho ID Boise

Illinois IL Springfield

Indiana IN Indianapolis

Iowa IA Des Moines

Kansas KS Topeka

Kentucky KY Frankfort

Louisiana LA Baton Rouge

Maine ME Augusta

Maryland MD Annapolis

Massachusetts MA Boston

Michigan MI Lansing

Minnesota MN St. Paul

Mississippi MS Jackson

Missouri MO Jefferson City

Montana MT Helena

Nebraska NE Lincoln

Nevada NV Carson City

New Hampshire NH Concord

New Jersey NJ Trenton

New Mexico NM Sante Fe

New York NY Albany

North Carolina NC Raleigh

North Dakota ND Bismarck

Ohio OH Columbus

Oklahoma OK Oklahoma City

Oregon OR Salem

Pennsylvania PA Harrisburg

Rhode Island RI Providence

South Carolina SC Columbia

South Dakota SD Pierre

Tennessee TN Nashville

Texas TX Austin

Utah UT Salt Lake City

Vermont VT Montpelier

Virginia VA Richmond

Washington WA Olympia

West Virginia WV Charleston

Wisconsin WI Madison

Wyoming WY Cheyenne

50 rows selected.

SQL> spool off

Your

PeopleSoft ID

Your Blackboard Userid

Execute Query here

Query output

Enter query here

Click Save File to create

Assignment#.lst

Click Run Script or F5 to execute

Worksheet Area

Entity Relationship Diagram for SQL Experience

PAGE

_1513762048.vsd

EMPLOYMENT

Employs

PROFESSOR

Serve_as

ADVISOR

REGISTRATION

STUDENT

SECTION

ROOM

COURSE

COURSE_PROGRAM

PROGRAM

Work_for

Belongs_to

Contains1

Administered_by

Contains2

Consists_of

Enrolls_in

Meets_in

(0,n)

(1,n)

(0,n)

(1,n)

(1,1)

(0,n)

(1,1)

(0,n)

(1,1)

Stu_id

Stu_First_Name

Stu_Last_Name

Stu_City

Stu_State

Stu_Date_of_Admit

Stu_Class

Stu_Major

Pr_id

Pr_Name

Pr_Date_hired

Pr_Title

Pr_College

Pr_Salary

Prg_id

Prg_Field

Prg_Name

Prg_Type

Prg_Hrs_required

Emp_Stu_id

Emp_Pr_id

Emp_Wage