Use SQL to make the foflow.ng changes to the Premiere Products database (see Fig

Question

Use SQL to make the foflow.ng changes to the Premiere Products database (see Figure 1.2 Chapter 1). After each change, execute an appropriate query to show that the change was made correctly. If directed to do so by your instructor, use the information provided with the Chan-Exercises to print your output. For any exercises that use commands not supported by yourT 3 sion of SQL, write the command to accomplish the task. 1. Create a view named MAJOR CUSTOMER. It consists of the customer number na ance, credit limit, and rep number for every customer whose credit limit is $10,000 orfe^a.Write and execute the CREATE VIEW command to create the MAJORJDUSTOMER b.Write and execute the command to retrieve the customer number and name of customer in the MAJOR,CUSTOMER view with a balance that exceeds the credit limit c.Write and execute the query that the DBMS actually executes. d.Does updating the database through this view create any problems'? If so what a they? If not, why not? 2. Create a view named PARTORDER. It consists of the part number, description price order number, order date, number ordered, and quoted price for all order lines currently on file. a.Write and execute the CREATE VIEW command to create the PART_ORDER view. b.Write and execute the command to retrieve the part number, description, order num- ber, and quoted price for all orders in the PART_ORDER view for parts with quoted prices that exceed $100. c.Write and execute the query that the DBMS actually executes. d.Does updating the database through this view create any problems? If so, what are they? If not, why not?

Explanation / Answer

When creating view we need some table to refer , I didnt get which table to refer so I just refered to some name Products , you can check and change it as of which table you want to refer.

1.

a) create view MAJOR_CUSTOMER

b) create view MAJOR_CUSTOMER (number,name) as select p.name,p.number from Products p where p.balance > 10,000

d) when updating views we must be careful whether it involves one or more databases.You can only update columns that belong to a single base table.

lets take some example.

consider two tabes:

Students(name,age,rollnum)

Course(cname,dept)

Now we want to create a view:

create view StudentView(name,age,dept) as select from s.name,s.age,c.dept from Students s, Course c

now if we want to view like:

update StudentView set name = 'ABC' , dept = 'CSE' where age=18

This is not possible because it involves two tables (i.e. Students and Course).

2.

create view PART_ORDER (partnum,desc,ordernum,quotedP) as select p.partnum , p.desc , p.ordernum , p.quotedP from Products p where p.price > 100

d) as mentioned above it wont create any problems unless it involves two base tables references.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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