Explore BrainMass
Share

SQL Query: Student Information

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Query1 Write a SQL statement to display Students' First and Last Name.

Query2 Write a SQL statement to display the Major of students with no duplications. Do not display student names.

Query3 Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888.

Query4 Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888 and have the major of Biology.

Query5 Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888 or 88808. Do not use IN.

Query6 Write a SQL statement to display the First and Last Name of students who have the major of Biology or Math. Use the SQL command IN.

Query7 Write a SQL statement to display the First and Last Name of students who have the Status greater than 1 and less than 10. Use the SQL command BETWEEN.

Query8 Write a SQL statement to display the First and Last Name of students who have a last name that starts with an S.

Query9 Write a SQL statement to display the First and Last Name of students having an a in the second position in their first names.

Query10 Write a SQL expression to display the Status and a sum of the Status of each Status value as SumOfStatus. Group by Status and display the results in descending order of SumOfStatus.

© BrainMass Inc. brainmass.com October 25, 2018, 7:39 am ad1c9bdddf
https://brainmass.com/computer-science/sql/sql-query-student-information-510726

Attachments

Solution Preview

1. Query1 Write a SQL statement to display Students' First and Last Name.
SELECT Student.First_name, Student.Last_name
FROM Student;

2. Query2 Write a SQL statement to display the Major of students with no duplications. Do not display student names.
SELECT DISTINCT MAJOR
FROM Student;

3. Query3 Write a SQL statement to display the First and Last Name of students who live in the Zip code 88888.

SELECT Student.First_name, Student.Last_name
FROM Student
WHERE ZIP ='88888'

4. Query4 Write a SQL statement to display the First and Last Name of ...

Solution Summary

The expert examines SQL Query for student information.

$2.19
See Also This Related BrainMass Solution

Forms and Reports

A. Create a report for each of the following requirements. Reports may be created using the wizard or an SQL query. Reports should follow the formatting guidelines given in the next step. Reports should be named as indicated below (e.g., Report1, Report2, etc.).

Report1: Show Instructor information (Instructor_ID, First_Name, Last_Name) grouped by Approved Course_No.

Report2: Show student information (Student_ID, First_Name, Last_Name, Status, Zip) grouped by Major.

Report3: Show student information (Student_ID, First_Name, Last_Name, Major) grouped by Zip code. Zip codes should be in increasing order.

Report4: Show student information (First_Name, Last_Name) grouped by Course Number and Course Name. Course Number should be in in increasing order.

Report5: Show student information (Student_ID, First_Name, Last_Name, Address, Zip) grouped by Status. Students should be in alphabetical order by Last_Name and;then First_Name.

B. Report Format

Reports should display two or more child rows for each parent row. For example, Report1 should display each Course_No with instructors who are approved to teach the course grouped under it. In this case, the parent row would be the Approved Course_No with Instructor listed as child rows underneath each parent row

There should be at least two child rows of data for each parent row. Add data to the tables in order to have reports show at least two child rows per parent.

Form1: Create a form based on an SQL query.
Create and run an SQL query that displays Student Information (Student_ID, First_Name, Last_Name, Address, Zip).
Save the query in the database named Form1Query.
Create a form based on Form1Query. While Form1Query is selected, go to the Create menu and select Form to build the basic form.
Select the form and switch between the views (Forms, Layout, and Design) to change the properties or the form's appearance as desired.
Save the form in the database named Form1Query.

Form2: Create a form using the Form Wizard.

Invoke the Form Wizard as shown in the tutorials above.
Using the Form Wizard, create a form showing instructor information (Instructor_ID, First_Name, Last_Name, Office_No).
Switch between the views (Forms, Layout, and Design) to change the properties or the form's appearance as desired.
Save the form in the database named Form2Wizard.

Form3: Create a form using a master detail relationship. A master detail relationship is simply a 1:N relationship between two tables. Use subforms to format the form in a split data entry form as shown in Figure 1-9. You may use SQL queries or the wizard or both to create your form and subform.
Create a master form showing student information (Student_ID, First_Name, Last_Name, Address, Zip, Major, Status).

Create a detail Subform showing Student_ID, Class_ID, and Student_Grade.
For each student displayed, the student's classes and grades should be displayed in the detail form.

Switch between the views (Forms, Layout, and Design) to change the properties or the form's appearance.

View Full Posting Details