DBMS EX2

REM ******************************************************************
REM Drop if any existing relation PC

DROP TABLE PC;

REM ********************************************************************
REM Create the PC relation to hold information about PC - desktop computer
REM PC(model,speed,RAM,HD,RD,price)

CREATE TABLE PC (
model number(4) CONSTRAINT pk_model PRIMARY KEY,
speed number(4),
RAM number(3),
HD number(2),
RD varchar2(6),
price number(5)
);

DESC PC;

REM QUERY 1. Add the first row of data to the PC table from the above sample data. Do not REM list  the columns in the INSERT clause.

INSERT INTO PC VALUES(1001,1500,128,60,'12XDVD',2499);

REM QUERY  2. Populate the table with the second of sample data. This time, list the columns
REM  explicitly in the INSERT clause.

INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,'&RD',&price);

REM QUERY 3. Ensure that the tuples are added into the table.

SELECT *FROM PC;

REM QUERY  4. Write an insert statement in a text file named loadpc.sql to load rows into the REMtable.Concatenate the speed of removable disk (like 8x, 12x, 16x, …) with the type “DVD”
REMto produce the RD value of a PC.

@e:/loadpc.sql;

REM QUERY  5. Populate the table with the next two rows of sample data by running the script that REMyou created in (4).

@e:/loadpc.sql;
@e:/loadpc.sql;

REM QUERY 6. Change the RAM speed of 1001 model to 256.

SELECT *FROM PC;

UPDATE PC
SET SPEED =256
WHERE MODEL=1001;

SELECT *FROM PC;


REM QUERY 7. Due to increase in the price of processor, hike the price of PC with speed greater REMthan 1000 by 2%. Verify your changes to the table.

UPDATE PC
SET PRICE= PRICE + PRICE * .02
WHERE SPEED > 1000;

SELECT *FROM PC;

REM QUERY 8. Change the speed of RD to 16x for the PC model with speed atleast 1000MHz REMand having 128 RAM or atleast 40 GB HD.

UPDATE PC
SET RD='16XDVD'
WHERE (SPEED >1000)   AND (RAM > 128 OR HD>40);
SELECT *FROM PC;

REM QUERY  9. Delete 1002 model from PC table.

DELETE  FROM PC
WHERE MODEL=1002;

REM QUERY 10. Confirm your changes to the table.

SELECT *FROM PC;

REM QUERY 11. Commit the changes.

COMMIT;

REM QUERY 12. Populate the table with last row of sample data by using script loadpc.sql that REMyou created in query 4.

@e:/loadpc.sql;

REM QUERY 13. Confirm your addition to the table.

SELECT *FROM PC;

REM QUERY  14. Discard the recently populated row from the table without discarding the earlier
REMINSERT operation(s).

ROLL BACK;

REM QUERY 15. Repeat the query as stated in 12.

@e:/loadpc.sql;

REM QUERY  16. Mark an intermediate point in the processing of the transaction.

SAVEPOINT S1;

REM QUERY 17. Empty the entire table.

DELETE FROM PC;

REM QUERY 18. Confirm that the table is empty.

SELECT *FROM PC;

REM QUERY 19. Discard the most recent DELETE operation without discarding the earlier REM INSERT  operation.

ROLL BACK TO S1;

REM QUERY 20. Confirm that the newly added row is still intact. And make the data addition
REM permanent.

SELECT *FROM PC;
COMMIT;

SELECT *FROM PC;







REM ******************************************************************
REM Drop if any existing relation AUDIO
DROP TABLE audio;

REM ********************************************************************
REM Create the AUDIO relation to hold information about different
REM music albums
CREATE TABLE AUDIO(
 audio_id number(3) constraint pk_aid primary key,
 title varchar2(25),
 no_of_tracks number(2),
 release_date date,
 genre char(3) constraint ch_genre CHECK(genre IN('CAR','DIV','MOV')),
 music varchar2(25),
 studio varchar2(20),
 price number(3)
);

REM ********************************************************************
REM Populate the AUDIO relation
REM
REM audio (audio_id,title,no_of_tracks,release_date,genre,music,studio,price)
insert into audio values(100,'Nayagan',5,'21-OCT-87','MOV','Ilaiyaraja','Pyramid',85);
insert into audio values(101,'Agni Nakshatram',6,'15-APR-88','MOV','Ilaiyaraja','Pyramid',85);
insert into audio values(102,'Geethanjali',7,'19-MAY-89','MOV','Ilaiyaraja','Lahari',90);
insert into audio values(103,'Anjali',7,'03-DEC-90','MOV','Ilaiyaraja','Lahari',80);
insert into audio values(104,'Bombay',8,'11-MAR-95','MOV','AR Rahman','MagnaSound',150);
insert into audio values(105,'Thalapathi',7,'05-NOV-91','MOV','Ilaiyaraja','Lahari',100);
insert into audio values(106,'Roja',6,'11-MAY-92','MOV','AR Rahman','MagnaSound',125);
insert into audio values(107,'Nadhamrutham',5,'01-NOV-98','CAR',null,'SaReGaMa',150);
insert into audio values(108,'Gentleman',5,'30-JUL-93','MOV','AR Rahman','Pyramid',100);
insert into audio values(109,'Thiruda Thiruda',6,'11-NOV-93','MOV','AR Rahman','SaReGaMa',100);
insert into audio values(110,'Indian',6,'01-MAY-96','MOV','AR Rahman','Pyramid',150);
insert into audio values(111,'Jeans',6,'01-APR-98','MOV','AR Rahman','T-Series',150);
insert into audio values(112,'Rangeela',8,'08-SEP-95','MOV','AR Rahman','T-Series',120);
insert into audio values(113,'Divine Collections',5,'21-OCT-96','DIV','Kunnakudi Vaidhyanathan','SaReGaMa',175);
insert into audio values(114,'Krishnarpanam',6,'25-AUG-97','DIV',null,'SaReGaMa',175);

SELECT * FROM AUDIO;





REM AUDIO(audio_id, title, no_of_tracks ,release_date, genre ,music ,studio,price)

REM QUERY  21. Select the audio id, title and release date that was released after the year 1997.

SELECT audio_id, title, release_date
FROM AUDIO
WHERE  extract(year from release_date) >1997;

REM QUERY  22. Display the title, release date, hike the price by 10% for the audio released after 1995 and  label the column as Audio Name and New Price respectively.

SELECT  title "Audio Name", release_date,price+price*.1 "New Price "
FROM AUDIO
WHERE  extract(year from release_date) >1995;


REM QUERY  23. Display the unique studio from AUDIO relation.

SELECT DISTINCT studio
FROM AUDIO;

REM QUERY 24. Show the title, release date, genre, studio and price of MOV or DIV type audio, but not released by Pyramid studio.

SELECT title,release_date, genre,studio,price
FROM AUDIO
WHERE  genre IN('MOV','DIV')
AND  STUDIO NOT IN ('Pyramid');

REM QUERY  25. Display the audio id, title, music and price of audios either in the range of Rs.100 to Rs.150 or music by AR Rahman.

SELECT audio_id, title,music,price
FROM AUDIO
WHERE price BETWEEN 100 AND 150
OR music ='AR Rahman';

REM QUERY 26. Display the audio title, number of tracks, release date, musician and studio that was released during 1995.

SELECT audio_id, title, no_of_tracks ,release_date,music ,studio
FROM AUDIO
WHEREextract(year from release_date) =1995;

REM QUERY 27. Display the title, genre, musician, studio and price of an audio for which the music was scored and the title has either a as second letter or starts with N.

SELECT  title,genre ,music ,studio,price
FROM AUDIO
WHERE (music IS NOT NULL)
AND (title LIKE 'N%' OR title LIKE '_a%');

REM QUERY 28. Display the audio details like title, number of tracks, genre, music and studio that has atmost 6 tracks by SaReGaMa Studio. If the music is not scored by any musician, display as No Music.

SELECT title, no_of_tracks,genre ,nvl(music,'No Music') ,studio
FROM AUDIO
WHERE no_of_tracks<=6 AND studio='SaReGaMa';

REM QUERY 29. Display the title and genre of the album that starts with B, N, K or R, but not a CAR genre.

SELECT  title,genre
FROM AUDIO
WHERE SUBSTR(title,1,1) IN('B','N','K','R')
AND genre NOT IN('CAR');


REM QUERY 30. List the album title, Date of Release, number of tracks, studio and musician that was released before 20 years and 3 months.

SELECT title,release_date,no_of_tracks,studio,music
FROM AUDIO
WHERE release_date <  (sysdate - interval  '20-3' year to month);

REM QUERY 31. List the title, number of tracks, release date, genre, music, studio and price of audio for which AR Rahman had scored the music from 1995 to 1998 in alphabetical order by title.

SELECT title, no_of_tracks ,release_date, genre ,music ,studio,price
FROM AUDIO
WHERE music='AR Rahman' AND release_date between '1-jan-1995' and '31-dec-1998'
ORDER BY title;

REM QUERY 32. How many movie audio(s) have a name that ends with letter a.

SELECT COUNT(title) "No of albums ending in a is"
FROM AUDIO
WHERE title like '%a';

REM QUERY 33. Display the maximum, minimum and average price of movie audio.

SELECT MAX(price) "Costly",MIN(price)"Cheaper",AVG(price) "Average"
FROM AUDIO;

REM QUERY 34. Show the cheapest, costlier, average and total price of audio for studiowise.Label the columns Cheaper, Costly, Average, and Total respectively. Round your results to the nearest whole number. Sort your result by alphabetical order of studio.

SELECT studio,MIN(price) "Cheaper",MAX(price) "Costly",ROUND(AVG(price))  "Average",SUM(price) "Total"
FROM AUDIO
GROUP BY studio
HAVING studio IS NOT NULL
ORDER BY studio;


REM QUERY 35. List the studio which sells the audio for a minimum price of atleast Rs.100.

SELECT studio,MIN(PRICE) "cheaper"
FROM AUDIO
GROUP BY studio
HAVING MIN(price) >=100;

REM QUERY 36. For each studio, show the musician name and number of times a musician scored the  music. Include only the audio that was released with in
22 years. Exclude any groups if a musician scored music atmost once. Sort the output by studio name in descending order.

SELECT music,count(music)
FROM AUDIO
WHERE extract(year from sysdate) -extract(year from release_date)  <=22
GROUP BY music,studio
HAVING count (music) > 1
ORDER BY studio DESC;