Question

In: Computer Science

Create a view that will display the student id, name, event id, category, and score for...

Create a view that will display the student id, name, event id, category, and score for all

events for students who scored a 70 or below on any test. Call this view

atriskstudents.

a. What code did you write for this view? Insert the snip of the contents of the view SQL

code here:

b. Using this view, display those students who earned a score of 65 or greater. Insert your

snip here

***********************************************************************************************************************************************************

***********************************************************************************************************************************************************

CREATE DATABASE Class;


#-- Using the database

USE Class;

# create student table


DROP TABLE IF EXISTS student;

CREATE TABLE student

(
  
name VARCHAR(20) NOT NULL,
  
gender ENUM('F','M') NOT NULL,
  
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  
PRIMARY KEY (student_id)

);

# create grade event table

DROP TABLE IF EXISTS grade_event;

CREATE TABLE grade_event

(
  
date DATE NOT NULL,
  
category ENUM('T','Q') NOT NULL,
  
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  
PRIMARY KEY (event_id)

);

# create score table


# The PRIMARY KEY comprises two columns to prevent any combination
# of event_id/student_id from appearing more than once.


DROP TABLE IF EXISTS score;


CREATE TABLE score

(
  
student_id INT UNSIGNED NOT NULL,
  
event_id INT UNSIGNED NOT NULL,
  
score INT NOT NULL,
  
PRIMARY KEY (event_id, student_id),
  
INDEX (student_id),
  
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
  
FOREIGN KEY (student_id) REFERENCES student (student_id)

);

# create absence table


DROP TABLE IF EXISTS absence;

CREATE TABLE absence

(
  
student_id INT UNSIGNED NOT NULL,
  
date DATE NOT NULL,
  
PRIMARY KEY (student_id, date),
  
FOREIGN KEY (student_id) REFERENCES student (student_id)

);

#--Populate the student table


INSERT INTO student VALUES ('Megan','F',NULL);

INSERT INTO student VALUES ('Joseph','M',NULL);

INSERT INTO student VALUES ('Kyle','M',NULL);

INSERT INTO student VALUES ('Katie','F',NULL);

INSERT INTO student VALUES ('Abby','F',NULL);

INSERT INTO student VALUES ('Nathan','M',NULL);

INSERT INTO student VALUES ('Liesl','F',NULL);

INSERT INTO student VALUES ('Ian','M',NULL);

INSERT INTO student VALUES ('Colin','M',NULL);

INSERT INTO student VALUES ('Peter','M',NULL);

INSERT INTO student VALUES ('Michael','M',NULL);

INSERT INTO student VALUES ('Thomas','M',NULL);

INSERT INTO student VALUES ('Devri','F',NULL);

INSERT INTO student VALUES ('Ben','M',NULL);

INSERT INTO student VALUES ('Aubrey','F',NULL);

INSERT INTO student VALUES ('Rebecca','F',NULL);

INSERT INTO student VALUES ('Will','M',NULL);

INSERT INTO student VALUES ('Max','M',NULL);

INSERT INTO student VALUES ('Rianne','F',NULL);

INSERT INTO student VALUES ('Avery','F',NULL);

INSERT INTO student VALUES ('Lauren','F',NULL);

INSERT INTO student VALUES ('Becca','F',NULL);

INSERT INTO student VALUES ('Gregory','M',NULL);

INSERT INTO student VALUES ('Sarah','F',NULL);

INSERT INTO student VALUES ('Robbie','M',NULL);

INSERT INTO student VALUES ('Keaton','M',NULL);

INSERT INTO student VALUES ('Carter','M',NULL);

INSERT INTO student VALUES ('Teddy','M',NULL);

INSERT INTO student VALUES ('Gabrielle','F',NULL);

INSERT INTO student VALUES ('Grace','F',NULL);

INSERT INTO student VALUES ('Emily','F',NULL);


#--Populate grade event table

INSERT INTO grade_event VALUES('2015-09-03', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-09-06', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-09-09', 'T', NULL);
INSERT INTO grade_event VALUES('
2015-09-16', 'Q', NULL);
INSERT INTO grade_event VALUES(
'2015-09-23', 'Q', NULL);
INSERT INTO grade_event VALUES('
2015-10-01', 'T', NULL);


#--Populate the score table


INSERT INTO score VALUES (1,1,20);

INSERT INTO score VALUES (3,1,20);

INSERT INTO score VALUES (4,1,18);

INSERT INTO score VALUES (5,1,13);

INSERT INTO score VALUES (6,1,18);

INSERT INTO score VALUES (7,1,14);

INSERT INTO score VALUES (8,1,14);

INSERT INTO score VALUES (9,1,11);

INSERT INTO score VALUES (10,1,19);

INSERT INTO score VALUES (11,1,18);

INSERT INTO score VALUES (12,1,19);

INSERT INTO score VALUES (14,1,11);

INSERT INTO score VALUES (15,1,20);

INSERT INTO score VALUES (16,1,18);

INSERT INTO score VALUES (17,1,9);

INSERT INTO score VALUES (18,1,20);

INSERT INTO score VALUES (19,1,9);

INSERT INTO score VALUES (20,1,9);

INSERT INTO score VALUES (21,1,13);

INSERT INTO score VALUES (22,1,13);

INSERT INTO score VALUES (23,1,16);

INSERT INTO score VALUES (24,1,11);

INSERT INTO score VALUES (25,1,19);

INSERT INTO score VALUES (26,1,10);

INSERT INTO score VALUES (27,1,15);

INSERT INTO score VALUES (28,1,15);

INSERT INTO score VALUES (29,1,19);

INSERT INTO score VALUES (30,1,17);

INSERT INTO score VALUES (31,1,11);

INSERT INTO score VALUES (1,2,17);

INSERT INTO score VALUES (2,2,8);

INSERT INTO score VALUES (3,2,13);

INSERT INTO score VALUES (4,2,13);

INSERT INTO score VALUES (5,2,17);

INSERT INTO score VALUES (6,2,13);

INSERT INTO score VALUES (7,2,17);

INSERT INTO score VALUES (8,2,8);

INSERT INTO score VALUES (9,2,19);

INSERT INTO score VALUES (10,2,18);

INSERT INTO score VALUES (11,2,15);

INSERT INTO score VALUES (12,2,19);

INSERT INTO score VALUES (13,2,18);

INSERT INTO score VALUES (14,2,18);

INSERT INTO score VALUES (15,2,16);

INSERT INTO score VALUES (16,2,9);

INSERT INTO score VALUES (17,2,13);

INSERT INTO score VALUES (18,2,9);

INSERT INTO score VALUES (19,2,11);

INSERT INTO score VALUES (21,2,12);

INSERT INTO score VALUES (22,2,10);

INSERT INTO score VALUES (23,2,17);
INSERT INTO score VALUES (24,2,19);

INSERT INTO score VALUES (25,2,10);

INSERT INTO score VALUES (26,2,18);

INSERT INTO score VALUES (27,2,8);

INSERT INTO score VALUES (28,2,13);

INSERT INTO score VALUES (29,2,16);

INSERT INTO score VALUES (30,2,12);

INSERT INTO score VALUES (31,2,19);

INSERT INTO score VALUES (1,3,88);

INSERT INTO score VALUES (2,3,84);

INSERT INTO score VALUES (3,3,69);

INSERT INTO score VALUES (4,3,71);

INSERT INTO score VALUES (5,3,97);

INSERT INTO score VALUES (6,3,83);

INSERT INTO score VALUES (7,3,88);
INSERT INTO score VALUES (8,3,75);

INSERT INTO score VALUES (9,3,83);

INSERT INTO score VALUES (10,3,72);
INSERT INTO score VALUES (11,3,74);

INSERT INTO score VALUES (12,3,77);

INSERT INTO score VALUES (13,3,67);
INSERT INTO score VALUES (14,3,68);

INSERT INTO score VALUES (15,3,75);

INSERT INTO score VALUES (16,3,60);

INSERT INTO score VALUES (17,3,79);

INSERT INTO score VALUES (18,3,96);

INSERT INTO score VALUES (19,3,79);

INSERT INTO score VALUES (20,3,76);

INSERT INTO score VALUES (21,3,91);

INSERT INTO score VALUES (22,3,81);

INSERT INTO score VALUES (23,3,81);

INSERT INTO score VALUES (24,3,62);

INSERT INTO score VALUES (25,3,79);

INSERT INTO score VALUES (26,3,86);

INSERT INTO score VALUES (27,3,90);

INSERT INTO score VALUES (28,3,68);

INSERT INTO score VALUES (29,3,66);

INSERT INTO score VALUES (30,3,79);

INSERT INTO score VALUES (31,3,81);

INSERT INTO score VALUES (2,4,7);

INSERT INTO score VALUES (3,4,17);

INSERT INTO score VALUES (4,4,16);

INSERT INTO score VALUES (5,4,20);

INSERT INTO score VALUES (6,4,9);

INSERT INTO score VALUES (7,4,19);

INSERT INTO score VALUES (8,4,12);

INSERT INTO score VALUES (9,4,17);

INSERT INTO score VALUES (10,4,12);

INSERT INTO score VALUES (11,4,16);

INSERT INTO score VALUES (12,4,13);

INSERT INTO score VALUES (13,4,8);

INSERT INTO score VALUES (14,4,11);

INSERT INTO score VALUES (15,4,9);

INSERT INTO score VALUES (16,4,20);

INSERT INTO score VALUES (18,4,11);

INSERT INTO score VALUES (19,4,15);

INSERT INTO score VALUES (20,4,17);

INSERT INTO score VALUES (21,4,13);

INSERT INTO score VALUES (22,4,20);

INSERT INTO score VALUES (23,4,13);

INSERT INTO score VALUES (24,4,12);
INSERT INTO score VALUES (25,4,10);

INSERT INTO score VALUES (26,4,15);

INSERT INTO score VALUES (28,4,17);

INSERT INTO score VALUES (30,4,11);

INSERT INTO score VALUES (31,4,19);

INSERT INTO score VALUES (1,5,15);

INSERT INTO score VALUES (2,5,12);

INSERT INTO score VALUES (3,5,11);

INSERT INTO score VALUES (5,5,13);

INSERT INTO score VALUES (6,5,18);

INSERT INTO score VALUES (7,5,14);

INSERT INTO score VALUES (8,5,18);

INSERT INTO score VALUES (9,5,13);

INSERT INTO score VALUES (10,5,14);

INSERT INTO score VALUES (11,5,18);

INSERT INTO score VALUES (12,5,8);

INSERT INTO score VALUES (13,5,8);

INSERT INTO score VALUES (14,5,16);

INSERT INTO score VALUES (15,5,13);

INSERT INTO score VALUES (16,5,15);

INSERT INTO score VALUES (17,5,11);

INSERT INTO score VALUES (18,5,18);

INSERT INTO score VALUES (19,5,18);
INSERT INTO score VALUES (20,5,14);

INSERT INTO score VALUES (21,5,17);

INSERT INTO score VALUES (22,5,17);

INSERT INTO score VALUES (23,5,15);

INSERT INTO score VALUES (25,5,14);

INSERT INTO score VALUES (26,5,8);

INSERT INTO score VALUES (28,5,20);

INSERT INTO score VALUES (29,5,16);

INSERT INTO score VALUES (31,5,9);

INSERT INTO score VALUES (1,6,100);

INSERT INTO score VALUES (2,6,91);

INSERT INTO score VALUES (3,6,94);

INSERT INTO score VALUES (4,6,74);

INSERT INTO score VALUES (5,6,97);

INSERT INTO score VALUES (6,6,89);

INSERT INTO score VALUES (7,6,76);

INSERT INTO score VALUES (8,6,65);

INSERT INTO score VALUES (9,6,73);

INSERT INTO score VALUES (10,6,63);

INSERT INTO score VALUES (11,6,98);

INSERT INTO score VALUES (12,6,75);

INSERT INTO score VALUES (14,6,77);

INSERT INTO score VALUES (15,6,62);

INSERT INTO score VALUES (16,6,98);

INSERT INTO score VALUES (17,6,94);

INSERT INTO score VALUES (18,6,94);

INSERT INTO score VALUES (19,6,74);
INSERT INTO score VALUES (20,6,62);

INSERT INTO score VALUES (21,6,73);

INSERT INTO score VALUES (22,6,95);

INSERT INTO score VALUES (24,6,68);

INSERT INTO score VALUES (25,6,85);

INSERT INTO score VALUES (26,6,91);
INSERT INTO score VALUES (27,6,70);

INSERT INTO score VALUES (28,6,77);

INSERT INTO score VALUES (29,6,66);

INSERT INTO score VALUES (30,6,68);

INSERT INTO score VALUES (31,6,76);


#--Populate the absence table

INSERT INTO `absence` VALUES (3,'2015-09-03');

INSERT INTO `absence` VALUES (5,'2015-09-03');

INSERT INTO `absence` VALUES (10,'2015-09-06');

INSERT INTO `absence` VALUES (10,'2015-09-09');

INSERT INTO `absence` VALUES (17,'2015-09-07');

INSERT INTO `absence` VALUES (20,'2015-09-07');

***********************************************************************************************************************************************************

***********************************************************************************************************************************************************

Solutions

Expert Solution

A view is a virtual table contains the result-set of an SQL statement. It also contains rows and columns, like an actual table.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

a) CREATE VIEW atriskstudents
AS SELECT a.student_id,a.name, b.event_id,b.category,c.score
FROM student a, grade_event b, score c
WHERE a.student_id=c.student_id
AND (b.event_id=c.event_id) AND c.score<=70;

Note:We can make this VIEW with some conditions in WHERE clause. Give 3 aliases a, b, c then use use a.student-id = c.student-id, because score table contains student -id. We want to select the data which is in both table and use the condition b.event-id=c.event-id because we want to select the data from both tables also. And should also satisfy c.score<=70 to selselect only 70 or below values.

Screenshots

b) select * from atriskstudents WHERE score>=65;

Note:The view contains student details with only score<=70. If we want to print score>=65, use query like this.

venereology answered 3 months ago
Next > < Previous

Related Solutions

Create a view named ReservationCustomer_VW. It consists of the reservation ID, trip ID, trip name, trip...
Create a view named ReservationCustomer_VW. It consists of the reservation ID, trip ID, trip name, trip date, customer number, customer last name, customer first name, and phone number for trips whose guide is Glory Unser or Susan Kiley. Show the SQL Server code for this view.
Student Structure Assignment Create a Student Structure globally consisting of student ID, name, completed credits, and...
Student Structure Assignment Create a Student Structure globally consisting of student ID, name, completed credits, and GPA. Define one student in main() and initialize the student with data. Display all of the student’s data on one line separated by tabs. Create another student in main(). Assign values to your second student (do not get input from the user). Display the second student’s data on one line separated by tabs. Create a third student in main(). Use a series of prompts...
JAVA PROGRAMMING Part 1 Create a class Student, with attributes id, first name, last name. (All...
JAVA PROGRAMMING Part 1 Create a class Student, with attributes id, first name, last name. (All the attributes must be String) Create a constructor that accepts first name and last name to create a student object. Create appropriate getters and setters Create another class StudentOperationClient, that contains a main program. This is the place where the student objects are created and other activities are performed. In the main program, create student objects, with the following first and last names. Chris...
Q3. Create a Company trading computer accessories with your Student ID & Name, address, College Email...
Q3. Create a Company trading computer accessories with your Student ID & Name, address, College Email ID and phone number for the year ended 31st Mar, 2020, and enter the following transactions using appropriate vouchers in Tally ERP 9 software: (3 Marks + 7 Marks) 1st Jan 2020, Started his business with an investment of RO 45,000 in cash. 2nd Jan 2020, Purchases computer accessories of RO 20,000 on credit from Mr. Salim. 31st Jan 2020, Sold computer accessories worth...
Q3. Create a Company trading computer accessories with your Student ID & Name, address, College Email...
Q3. Create a Company trading computer accessories with your Student ID & Name, address, College Email ID and phone number for the year ended 31st Mar, 2020, and enter the following transactions using appropriate vouchers in Tally ERP 9 software: (3 Marks + 7 Marks) 1st Jan 2020, Started his business with an investment of RO 45,000 in cash. 2nd Jan 2020, Purchases computer accessories of RO 20,000 on credit from Mr. Salim. 31st Jan 2020, Sold computer accessories worth...
In this create a code that will drop a student by ID number //////////////////////////////////////////////////////////////////////// import java.util.Scanner;...
In this create a code that will drop a student by ID number //////////////////////////////////////////////////////////////////////// import java.util.Scanner; public class COurseCom666 {     private String courseName;     private String [] students = new String[1];     private int numberOfStudents;     public COurseCom666(String courseName) {         this.courseName = courseName;     }     public String[] getStudents() {         return students;     }     public int getNumberOfStudents() {         return numberOfStudents;     }     public String getCourseName() {         return courseName;     }     public void...
Student ID Student ACT Score (Independent Variable) Student            GPA     (Dependent Variable) 1 24 3.25 2 21...
Student ID Student ACT Score (Independent Variable) Student            GPA     (Dependent Variable) 1 24 3.25 2 21 2.87 3 18 2.66 4 22 3.33 5 22 2.87 6 22 3.21 7 18 2.76 8 28 3.91 9 29 3.55 10 18 2.55 11 20 2.44 12 24 3.22 13 25 3.22 14 24 3.44 15 21 3.01 Instructions: Generate on a separate worksheet a standard set of SUMMARY OUTPUT for two-variable regression, and label the worksheet "Simple Regression Output". Then, using...
Student Name and ID no. _________________________________________________________ Write the journal entries in the given table for below...
Student Name and ID no. _________________________________________________________ Write the journal entries in the given table for below transactions of Clean Corporation during June 2020. 1 Provided services to customers on account for $650. 2 Purchased a building using 10 year Note Payable for $200,000. 3 Paid salaries to employees, $2,600. 4 Received payment from customers to whom service was given in transaction 1 5 Paid $400 dividends to shareholders. Answer Accounts Debit Credit 1 2 3 4 5
Create a java program with class Customer: Name, Surname, ID (incremental ID by 1 for each...
Create a java program with class Customer: Name, Surname, ID (incremental ID by 1 for each new customer), Email, Phone, Address. The program must create New customer, and Print information for customer with a certain ID.   
BOXPLOT & WHISKERS Student ID Score pre training Score post training Scenario: 102 50 75 You...
BOXPLOT & WHISKERS Student ID Score pre training Score post training Scenario: 102 50 75 You work for the Medical Center's Corporate Education Department. You developed a new   442 60 75 leadership program as part of the Center's succession plan. You had your first cohort of 325 65 85 managers successfully complete the training program. Each participant completed pre- 154 75 90 assessment before starting the classroom instruction and a post-assessment following the 114 80 95 completion of the program....
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT