SQL> @ e:/ass2.sql;
SQL> REM ******************************************************************
SQL> REM Drop if any existing relation PC
SQL>
SQL> DROP TABLE PC;
Table dropped.
SQL>
SQL> REM ********************************************************************
SQL> REM Create the PC relation to hold information about PC - desktop computer
SQL> REM PC(model,speed,RAM,HD,RD,price)
SQL>
SQL> CREATE TABLE PC (
2 model number(4) CONSTRAINT pk_model PRIMARY KEY,
3 speed number(4),
4 RAM number(3),
5 HD number(2),
6 RD varchar2(6),
7 price number(5)
8 );
Table created.
SQL>
SQL> DESC TABLE PC;
Usage: DESCRIBE [schema.]object[@db_link]
SQL>
SQL> REM CONSTRAINT 1. Add the first row of data to the PC table from the above sample data. Do not list REM the columns in the INSERT clause.
SQL>
SQL> INSERT INTO PC VALUES(1001,1500,128,60,'12XDVD',2499);
1 row created.
SQL>
SQL> REM CONSTRAINT 2. Populate the table with the second of sample data. This time, list the columns
SQL> REM explicitly in the INSERT clause.
SQL>
SQL> INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,'&RD',&price);
Enter value for model: 1002
Enter value for speed: 866
Enter value for ram: 128
Enter value for hd: 20
Enter value for rd: 8
Enter value for price: 1999
old 1: INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,'&RD',&price)
new 1: INSERT INTO PC VALUES(1002,866,128,20,'8',1999)
1 row created.
SQL>
SQL> REM CONSTRAINT 3. Ensure that the tuples are added into the table.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 1500 128 60 12XDVD 2499
1002 866 128 20 8 1999
SQL>
SQL> REM CONSTRAINT 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”
SQL> REMto produce the RD value of a PC.
SP2-0734: unknown command beginning "REMto prod..." - rest of line ignored.
SQL>
SQL> @e:/loadpc.sql;
SQL> INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price);
Enter value for model: 1003
Enter value for speed: 1000
Enter value for ram: 128
Enter value for hd: 40
Enter value for rd: 12
Enter value for price: 1499
old 1: INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price)
new 1: INSERT INTO PC VALUES(1003,1000,128,40,concat(substr('12',1,2),'xDVD'),1499)
1 row created.
SQL>
SQL>
SQL> REM CONSTRAINT 5. Populate the table with the next two rows of sample data by running the script that REMyou created in (4).
SQL>
SQL> @e:/loadpc.sql;
SQL> INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price);
Enter value for model: 1004
Enter value for speed: 1200
Enter value for ram: 128
Enter value for hd: 80
Enter value for rd: 12
Enter value for price: 1699
old 1: INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price)
new 1: INSERT INTO PC VALUES(1004,1200,128,80,concat(substr('12',1,2),'xDVD'),1699)
1 row created.
SQL>
SQL> @e:/loadpc.sql;
SQL> INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price);
Enter value for model: 1005
Enter value for speed: 1300
Enter value for ram: 256
Enter value for hd: 80
Enter value for rd: 16
Enter value for price: 2299
old 1: INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price)
new 1: INSERT INTO PC VALUES(1005,1300,256,80,concat(substr('16',1,2),'xDVD'),2299)
1 row created.
SQL>
SQL>
SQL> REM CONSTRAINT 6. Change the RAM speed of 1001 model to 256.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 1500 128 60 12XDVD 2499
1002 866 128 20 8XDVD 1999
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 12xDVD 1699
1005 1300 256 80 16xDVD 2299
SQL>
SQL> UPDATE PC
2 SET SPEED =256
3 WHERE MODEL=1001;
1 row updated.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 256 128 60 12XDVD 2499
1002 866 128 20 8XDVD 1999
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 12xDVD 1699
1005 1300 256 80 16xDVD 2299
SQL>
SQL>
SQL> REM CONSTRAINT 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.
SQL>
SQL> UPDATE PC
2 SET PRICE= PRICE + PRICE * .02
3 WHERE SPEED > 1000;
2 rows updated.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 256 128 60 12XDVD 2499
1002 866 128 20 8XDVD 1999
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 12xDVD 1733
1005 1300 256 80 16xDVD 2345
SQL>
SQL> REM CONSTRAINT 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.
SQL>
SQL> UPDATE PC
2 SET RD='16XDVD'
3 WHERE (SPEED >1000) AND (RAM > 128 OR HD>40);
2 rows updated.
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 256 128 60 12XDVD 2499
1002 866 128 20 8XDVD 1999
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 16XDVD 1733
1005 1300 256 80 16XDVD 2345
SQL>
SQL> REM CONSTRAINT 9. Delete 1002 model from PC table.
SQL>
SQL> DELETE FROM PC
2 WHERE MODEL=1002;
1 row deleted.
SQL>
SQL> REM CONSTRAINT 10. Confirm your changes to the table.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 256 128 60 12XDVD 2499
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 16XDVD 1733
1005 1300 256 80 16XDVD 2345
SQL>
SQL> REM CONSTRAINT 11. Commit the changes.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> REM CONSTRAINT 12. Populate the table with last row of sample data by using script loadpc.sql that REMyou created in query 4.
SQL>
SQL> @e:/loadpc.sql;
SQL> INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price);
Enter value for model: 1002
Enter value for speed: 866
Enter value for ram: 128
Enter value for hd: 20
Enter value for rd: 8
Enter value for price: 1999
old 1: INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price)
new 1: INSERT INTO PC VALUES(1002,866,128,20,concat(substr('8',1,2),'xDVD'),1999)
1 row created.
SQL>
SQL>
SQL> REM CONSTRAINT 13. Confirm your addition to the table.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 256 128 60 12XDVD 2499
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 16XDVD 1733
1005 1300 256 80 16XDVD 2345
1002 866 128 20 8xDVD 1999
SQL>
SQL> REM CONSTRAINT 14. Discard the recently populated row from the table without discarding the earlier
SQL> REMINSERT operation(s).
SP2-0734: unknown command beginning "REMINSERT ..." - rest of line ignored.
SQL>
SQL> ROLL BACK;
Rollback complete.
SQL>
SQL> REM CONSTRAINT 15. Repeat the query as stated in 12.
SQL>
SQL> @e:/loadpc.sql;
SQL> INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price);
Enter value for model: 1002
Enter value for speed: 866
Enter value for ram: 128
Enter value for hd: 20
Enter value for rd: 8
Enter value for price: 1999
old 1: INSERT INTO PC VALUES(&model,&speed,&RAM,&HD,concat(substr('&RD',1,2),'xDVD'),&price)
new 1: INSERT INTO PC VALUES(1002,866,128,20,concat(substr('8',1,2),'xDVD'),1999)
1 row created.
SQL>
SQL>
SQL> REM CONSTRAINT 16. Mark an intermediate point in the processing of the transaction.
SQL>
SQL> SAVEPOINT S1;
Savepoint created.
SQL>
SQL> REM CONSTRAINT 17. Empty the entire table.
SQL>
SQL> DELETE FROM PC;
5 rows deleted.
SQL>
SQL> REM CONSTRAINT 18. Confirm that the table is empty.
SQL>
SQL> SELECT *FROM PC;
no rows selected
SQL>
SQL> REM CONSTRAINT 19. Discard the most recent DELETE operation without discarding the earlier REM INSERT operation.
SQL>
SQL> ROLL BACK TO S1;
Rollback complete.
SQL>
SQL> REM CONSTRAINT 20. Confirm that the newly added row is still intact. And make the data addition
SQL> REM permanent.
SQL>
SQL> SELECT *FROM PC;
MODEL SPEED RAM HD RD PRICE
---------- ---------- ---------- ---------- ------ ----------
1001 256 128 60 12XDVD 2499
1003 1000 128 40 12xDVD 1499
1004 1200 128 80 16XDVD 1733
1005 1300 256 80 16XDVD 2345
SQL> COMMIT;
Commit complete.
SQL> spool off;
No comments:
Post a Comment