SQL Chapter 5 As you do the exercises, unless it is stated otherwise, you will b

Question

SQL Chapter 5

As you do the exercises, unless it is stated otherwise, you will be using the tables from our standard Student- Course database. Also, as you do the exercises, it will be a good idea to copy/paste your query as well as your query result into a word processor.

Develop and execute a query to find the names of students who had HERMANO as an instructor and earned a grade of “B” or better in the class. Develop the query by first finding sections where HERMANO was the instructor. Save this query. Then, recall (EDIT) it and modify the query to join the Section table with the Grade_report table. Then add the grade constraint. Save this query (with the REPLACE option). Then, recall and go from there. Show all steps on your output

Create a duplicate table (call it Stutab) in your account from the Student table which contains all tuples from the Student table. Hint: Use DESC Student to see the attributes of the Student table. Create the Stutab table with a CREATE TABLE command. INSERT INTO Stutab... SELECT to populate it.

a. List student names and majors from the Stutab table for students who are juniors or seniors only.

b. List student names and computer science (COSC) majors from the Stutab table. c. Create a view (call it vstu) that contains student names and majors, but only for COSC

majors. (Use CREATE OR REPLACE VIEW....)

d. List the student names and majors from the vstu view in descending order by name.

e. Modify a tuple in your view of your table so that a student changes his or her major.

f. Re-execute the display of the view. Did modifying your vstu view also change the parent- table, Stutab?

g. Try to modify the view again, but this time change the major to ‘COMPSC’—an obviously invalid field in the Stutab table because the attribute was defined as four characters. Can you do it? What happens?

Explanation / Answer

This query will display the student name, student number and section ID of students whose instructor is "Hermano".

This query will display the student name, student number and section ID of students whose instructor is "Hermano" and whose grade is B or above.

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

First create Stutab table that includes all the data of Student table.

a) This query selects student name and major from Stutab who are juniors(class=3) or seniors(class=5)

SELECT sname, major FROM Stutab WHERE class = 3 OR class =4;

b) This query selects the student name fron Stutab whose major is computer science.

SELECT sname FROM Stutab WHERE major = 'COSC';

c) This query creates a view that contains student name and mojor of computer science students.

CREATE VIEW vstu AS SELECT SNAME, MAJOR FROM Stutab WHERE MAJOR = 'COSC';

d) This query selects data from view in desending order.

SELECT * FROM vstu ORDER BY sname DESC;

e) This query modifies the major of a student.

UPDATE vstu SET major = 'ECE' WHERE sname = 'Mary';

f) Displaying vstu

SELECT * FROM vstu;

The data which is modified in view will also change the values in parent table. When we display the parent table, it contains the modified value.

SELECT * FROM Stutab;

g) Modified view again and changed major to ‘COMPSC’

UPDATE vstu SET major = 'COMPSC' WHERE sname = 'Mary';

This produce an error. It cannot be modified

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.