database mysql Create and populate the table CARS so that you get the following:

Question

database mysql

Create and populate the table CARS so that you get the following: Create a view called CHEAPCARS that lists the NAME and COST of the cars that cost under $25,000 and show the results of the view. Alter the view to now display the cars costing under $30,000 Add a new column to the view so that now it displays SELL_PRICE (which is a 15% markup on the COST). Reduce the COST of all cars in the CHEAPCARS view by $500 and confirm that the COST has also changed in the underlying CARS table. Drop the CARS table. What happens if you try to (SELECT *) with the CHEAPCARS view? Recreate and populate the CARS table. Now try reusing the statement you tried in question 6. What happens? Use three SELECT statements and combine them using the UNION operator to create the view FAVOURITECARS for id's 2, 5, and 7.

Explanation / Answer

............................................creating cars table.............................................................

CREATE TABLE cars (
id INT(4) NOT NULL,
name CHAR(20) DEFAULT '' NOT NULL,
cost DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(id))

..............................................inserting data into cars....................................
INSERT INTO shop VALUES
(1,'Audi',52642),(2,'mercedes',57127),(3,'scoda',9000),(4,'volvo',29000),
(5,'bently',350000),(6,'citroen',21000),(7,'hummer',41400),(8,'volksvagon',21600);

.............................creating view "cheapcars"...............................

CREATE VIEW cheapcars AS SELECT id,name,cost
FROM
cars WHERE cost<=25000;

....................................getting data from the view..............................................

select *from cheapcars;

..............................altering view that previously have cars details under 25000 to 30000............

ALTER VIEW cheapcars AS SELECT name,cost
FROM
cars WHERE cost<=30000;

..........................alter table add SELL_PRICE column..................

ALTER VIEW cheapcars AS SELECT name,cost,(cost/100)*15 as sell_price
FROM
cars WHERE cost<=30000;

.........................decreasing cost of cars by 500 and changes reflected to base table.............................
using update we can change the data in base table also.

UPDATE VIEW cheapcars AS SELECT name,(cost-500) as Cost,(cost/100)*15 as sell_price
FROM
cars WHERE cost<=30000;

.....................accessing view data after droping the base table......................................

we can drop table using "drop table".
>drop table cars;
> Table dropped.

after dropping the base table we try to use the view constructed on baae table.

select *from cheapcars;

error occured,
references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

......................................again create cars table and access..........................

again create the cars and try to access the view then the view executes normally if any columns added it performs abnormal activities.....

................................union...............................................

select name from cheapcars
union
select cost from cheapcars
union
select sell_price from cheapcars;

.....................................create view for favourite cars 2,5 and 7............

CREATE OR REPLACE view favouritecars AS SELECT id,name,cost FROM cars;

  

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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