QUERIES USING THE STUDENT DATABASE
| No. | SQL Queries (Instructions) |
Required Tables (Instructions) |
Query Output |
| 1. | SELECT dbo_student_sem_curr.numb_stud_id AS [Studdent ID], dbo_student_sem_curr.name_stud AS Name, dbo_student_sem_curr.qnty_stud_gpa_cum AS [Cumulative GPA], dbo_cmajropt.short_description AS [First option in primary major], dbo_cclasytm.short_description AS [Semester Classification] FROM (dbo_student_sem_curr INNER JOIN dbo_cmajropt ON dbo_student_sem_curr.code_stud_majr1_opt_mod_1 = dbo_cmajropt.code_value) INNER JOIN dbo_cclasytm ON dbo_student_sem_curr.code_stud_clsfctn_yrtm = dbo_cclasytm.code_value WHERE (((dbo_student_sem_curr.code_camp)="up") AND ((dbo_student_sem_curr.code_stud_majr1)=[Enter your department: for eg: ASTRO, BMB, BIOL, CHEM, MTHBS, MTHBA, PHYS, STAT]) AND ((dbo_student_sem_curr.qnty_stud_gpa_cum)>3)); | student_sem_curr, cclasytm, cmajropt | Report showing undergraduates having GPA more than 3 |
| 2. | SELECT dbo_student_sem_curr.numb_stud_id AS [Student ID], dbo_student_sem_curr.name_stud AS Name, dbo_student_sem_curr.code_stud_clsfctn_yrtm AS [Semester Classification], Trim([code_stud_access_acct_id]) & '@psu.edu;' AS [Email Address] FROM dbo_student_sem_curr WHERE (((dbo_student_sem_curr.code_camp)="up") AND ((dbo_student_sem_curr.code_stud_majr1)=[Enter your department: for eg: ASTRO, BMB, BIOL, CHEM, MTHBS, MTHBA, PHYS, STAT]) AND ((dbo_student_sem_curr.code_univ_yr_sem)="200708fa")) ORDER BY dbo_student_sem_curr.name_stud; | student_sem_curr | List of undergraduates in a department and their Email id. |
| 3. | SELECT dbo_student_sem.numb_stud_id AS [Student ID], dbo_student_sem.name_stud AS Name, dbo_uf_student_transcript.section AS [Course section], dbo_uf_student_transcript.grade AS Grade, dbo_student_sem.code_univ_yr_sem AS Semester FROM dbo_student_sem INNER JOIN dbo_uf_student_transcript ON (dbo_student_sem.code_univ_yr_sem = dbo_uf_student_transcript.semester) AND (dbo_student_sem.numb_stud_id = dbo_uf_student_transcript.student_id) WHERE (((dbo_uf_student_transcript.grade)<="c") AND ((dbo_student_sem.code_camp)="up") AND ((dbo_uf_student_transcript.course)=[Enter the course code: for eg MATH 141])) ORDER BY dbo_student_sem.code_univ_yr_sem DESC; | student_sem_curr, uf_student_transcript | List of students having grade 'C' or higher for a particular course |
| 4. | SELECT dbo_student_sem_curr.numb_stud_id AS [Student ID], dbo_student_sem_curr.name_stud AS [Student name], dbo_student_sem_curr.name_advr AS [Advisor name] FROM dbo_student_sem_curr WHERE (((dbo_student_sem_curr.code_camp)="up") AND ((dbo_student_sem_curr.code_stud_majr1)=[Enter your department: for eg: ASTRO, BMB, BIOL, CHEM, MTHBS, MTHBA, PHYS, STAT]) AND ((dbo_student_sem_curr.code_univ_yr_sem)="200708fa")); | student_sem_curr | List of students who have an advisor and advisor name |
| 5. | SELECT dbo_student_sem_curr.numb_stud_id AS [Student ID], dbo_student_sem_curr.name_stud AS [Student name], dbo_student_sem_curr.name_advr AS [Advisor name], dbo_student_sem_curr.code_univ_yr_sem AS [Date], dbo_student_sem_curr.code_stud_degr1 AS Degree, dbo_student_sem_curr.date_stud_graduating_upd AS [Graduation Date] FROM dbo_student_sem_curr WHERE (((dbo_student_sem_curr.code_camp)="up") AND ((dbo_student_sem_curr.code_stud_majr1)=[Enter your department: for eg: ASTRO, BMB, BIOL, CHEM, MATH, PHYS, STAT]) AND ((dbo_student_sem_curr.code_stud_degr1)="PH D") AND ((dbo_student_sem_curr.indc_stud_graduating)="y")); | student_sem_curr | Details of PhD students graduating this semester |
| 6. | SELECT dbo_student_sem_curr.numb_stud_id AS [Student ID], dbo_student_sem_curr.name_stud AS [Student name], dbo_student_sem_curr.name_advr AS [Advisor name], dbo_student_sem_curr.code_univ_yr_sem AS [Date], dbo_student_sem_curr.code_stud_degr1 AS Degree, dbo_student_sem_curr.date_stud_graduating_upd AS [Graduation Date] FROM dbo_student_sem_curr WHERE (((dbo_student_sem_curr.code_camp)="up") AND ((dbo_student_sem_curr.code_stud_majr1)=[Enter your department: for eg: ASTRO, BMB, BIOL, CHEM, MATH, PHYS, STAT]) AND ((dbo_student_sem_curr.code_stud_degr1)="M S") AND ((dbo_student_sem_curr.indc_stud_graduating)="y")); | student_sem_curr | Details of Master of Sceince students graduating this semester |
| 7. | SELECT dbo_uf_student_transcript.course AS [Course Key], dbo_uf_student_transcript.section AS [Course section], dbo_student_sem.code_univ_yr_sem AS [Academic Year], dbo_uf_student_transcript.grade AS Grade FROM dbo_student_sem INNER JOIN dbo_uf_student_transcript ON (dbo_student_sem.numb_stud_id = dbo_uf_student_transcript.student_id) AND (dbo_student_sem.code_univ_yr_sem = dbo_uf_student_transcript.semester) WHERE (((dbo_uf_student_transcript.course)=[Enter the course code: for eg MATH 141]) AND ((dbo_student_sem.code_camp)="up")) ORDER BY dbo_uf_student_transcript.course, dbo_uf_student_transcript.section, dbo_student_sem.code_univ_yr_sem DESC; | student_sem and uf_student_transcript | (For statistics) Grade listing based on courses & sections for past 9-10 years |
| 8. | SELECT dbo_student_sem_future.numb_stud_id AS [Student ID], dbo_student_sem_future.name_stud AS [Student Name], dbo_student_sem_future.code_univ_yr_sem AS Semester FROM dbo_student_sem_future WHERE (((dbo_student_sem_future.code_camp)="up") AND ((dbo_student_sem_future.code_stud_majr1)=[Enter your department: for eg: ASTRO, B M B, BIOL, CHEM, MATH, PHYS, STAT])) ORDER BY dbo_student_sem_future.name_stud; | student_bio_future | List of future department students |
| 9. | SELECT dbo_student_sem_curr.numb_stud_id AS [Student ID], dbo_student_sem_curr.name_stud AS [Student Name], dbo_student_sem_curr.qnty_stud_crdt_enrl_yrtm AS [Current Credits] FROM dbo_student_sem_curr WHERE (((dbo_student_sem_curr.qnty_stud_crdt_enrl_yrtm)>=18) AND ((dbo_student_sem_curr.code_stud_majr1)=[Enter your department course key: for eg: ASTRO, B M B, BIOL, CHEM, MATH, PHYS, STAT])) ORDER BY dbo_student_sem_curr.name_stud; | student_sem_curr | List of students currently enrolled in more than 18 credits |
| 10. | SELECT dbo_student_sem.numb_stud_id AS [Student ID], dbo_student_sem.name_stud AS [Student Name], dbo_student_sem.code_univ_yr_sem AS [Graduating Semester], dbo_student_sem.code_stud_degr1 AS Degree FROM dbo_student_sem WHERE (((dbo_student_sem.code_stud_degr1)="b s") AND ((dbo_student_sem.code_camp)="up") AND ((dbo_student_sem.code_stud_majr1)=[Enter your department: for eg: ASTRO, B M B, BIOL, CHEM, MATH, PHYS, STAT])) ORDER BY dbo_student_sem.code_univ_yr_sem DESC; | student_sem | List of undergrad students graduated from the department |
| 11. | SELECT dbo_tcm.code_univ_yr_sem AS Semester, dbo_tcm.qnty_csec_enrl AS [Total Enrollment], dbo_tcm.code_csec_crse_key AS [Course Key] FROM dbo_tcm WHERE (((dbo_tcm.code_univ_yr_sem)=[Enter the semester for ex: Fall 2007 will be 200708FA:]) AND ((dbo_tcm.code_csec_crse_key)=[Enter you department mneomonic or the course key: for ex BIOL or BIOL 003: ] & "*")); | tcm | Student count per course in a particular semester |
| 12. | SELECT dbo_gre_scores.numb_stua_id AS [Student ID], dbo_gr_applicant.name_pers_last AS [Last Name], dbo_gr_applicant.name_pers_first AS [First Name], dbo_gre_scores.date_pers_gre_taken AS [GRE Date], dbo_gre_scores.qnty_pers_score_gre_analyt AS [Analytical Score], dbo_gre_scores.qnty_pers_score_gre_verb AS [Verbal Score], dbo_gre_scores.qnty_pers_score_gre_quant AS [Quantative Score], dbo_gr_applicant.code_stua_samx_degr AS [Degree Applying for], dbo_gr_applicant.code_pers_sex AS Gender, dbo_gr_applicant.qnty_stua_gpa_jr_sr AS GPA FROM dbo_gre_scores INNER JOIN dbo_gr_applicant ON dbo_gre_scores.numb_stua_id = dbo_gr_applicant.numb_stua_id WHERE (((dbo_gr_applicant.code_stua_samx_majr)=[Enter your department: for eg: ASTRO, B M B, BIOL, CHEM, MATH, PHYS, STAT]) AND ((dbo_gr_applicant.code_stua_samx_camp)="up")) ORDER BY dbo_gr_applicant.name_pers_last; | gre_scores and gr_applicants | GRE score and GPA information for students applying to the department |
| 13. | SELECT DISTINCT dbo_student_sem_curr.numb_stud_id AS [Student ID], dbo_student_sem_curr.name_stud AS Name, Trim([code_stud_access_acct_id]) & '@psu.edu;' AS [Email Address], dbo_uf_student_transcript.course AS Course FROM dbo_student_sem_curr INNER JOIN dbo_uf_student_transcript ON dbo_student_sem_curr.numb_stud_id = dbo_uf_student_transcript.student_id WHERE (((dbo_uf_student_transcript.course)=[Enter course key:]) AND ((dbo_student_sem_curr.code_camp)="up") AND ((dbo_student_sem_curr.code_univ_yr_sem)=[Enter semester year like 200708FA:])) ORDER BY dbo_student_sem_curr.name_stud; | student_sem_curr and uf_student_transcript | List of email addresses for students enrolled in a particular class |
| 14. | SELECT dbo_gr_applicant.numb_stua_id AS [Student ID], dbo_gr_applicant.name_pers_last AS [Last Name], dbo_gr_applicant.name_pers_first AS [First Name], dbo_gr_applicant.code_stua_samx_degr AS Degree, dbo_gr_applicant.code_stua_samx_yrtm AS Semester FROM dbo_gr_applicant WHERE (((dbo_gr_applicant.code_stua_samx_majr)=[Enter your department: for eg: ASTRO, B M B, BIOL, CHEM, MATH, PHYS, STAT]) AND ((dbo_gr_applicant.code_stua_samx_camp)="UP") AND ((dbo_gr_applicant.code_stua_samx_yrtm)=[Enter semester like 200708FA:])) ORDER BY dbo_gr_applicant.name_pers_last; | gr_applicant | Details of applicants applying for graduate program |
| Go top | |||
This page is maintained by Rubina V. Nashine: rvn2@psu.edu, (814) 360-1882
Eberly College of Science, 517 Thomas Building, University Park, PA 16802-2112
This page was last updated on 10 December 2007 © Penn State University
If you would like to communicate with the keepers of the Eberly College of Science Web server, send electronic mail to: science-web@science.psu.edu
Technology Webmaster: Brian Broking < brb10@psu.edu >
Content Webmaster: Barbara Kennedy < science@psu.edu >