practicals of View


                                           PRACTICAL-7            
       1. WHAT IS VIEW?

ANS:-A VIEW CONTAINS NO DATA OF ITS OWN BUT ITS LIKE WINDOW THROUGH WHICH DATA FROM TABLES CAN BE VIEWED OR CHANGED. THE TABLE ON WHICH A VIEW IS BASED ARE CALLED BASE TABLES.


2. CREATE A VIEW VW_ORDER ON ORDER DATE AND AMOUNT OF THE ORDERS TABLE.

ANS:-CREATE VIEW VW_ORDER AS SELECT ODATE, ATM FROM ORDERED;
     

3. CREATE A VIEW VW_CUST ON CUSTOMER NUMBER, CUSTOMER NAME, CITY OF THE CUSTOMER TABLE.

ANS:-CREATE VIEW VW_CUST AS SELECT CNUM, CNAME, CITY FROM CUSTOMER;


4. INSERT THE VALUES(’21-SEP-06’,6000) IN A VIEW VW_ORDER, EXPLAIN THE ERROR IF ANY.

ANSWER:-INSERT INTO VW_ORDER VALUES('21-SEP-06',6000);


5. INSERT THE VALUES(‘C2008’,‘HARSH’,’MEHSANA’) IN A VIEW VW_CUST.

ANS:-INSERT INTO VW_CUST VALUES('C2008','HARSH','MEHSANA');


6. DELETE A RECORD WHERE ORDER_NUMBER IS O3001 USING VIEW VW_ORDER.

ANS:-DELETE FROM VW_ORDER WHERE ODATE = (SELECT ODATE FROM ORDERED WHERE ONUM = 'O3001');


7. CHANGE THE NAME ‘KAVISH’ TO ‘DEEP’ USING VIEW VW_CUST.

ANS:-UPDATE VW_CUST SET CNAME = 'DEEP' WHERE CNAME = 'kavish';


8. GIVE THE USER HSM PERMISSION ONLY TO VIEW RECORDS IN THE TABLES SALESMAN AND CUSTOMER ALONG WITH AN OPTION TO FURTHER GRANT PERMISSION ON THESE TABLES TO OTHER USERS.

ANS:-GRANT ALL ON salesman TO deepak WITH GRANT OPTION;
          GRANT ALL ON CUSTOMER TO deepak WITH GRANT OPTION;

9. GIVE THE USER HSM ALL DATA MANIPULATION PRIVILEGES ON THE TABLE CUSTOMER WITHOUT AN OPTION TO FURTHER GRANT PERMISSION TO OTHER USERS.

ANS:-GRANT ALL ON salesman to deepak;

10.  TAKE BACK ALL PRIVILEGES GIVEN TO THE USER HSM ON THE TABLE CUSTOMER.

ANS:-REVOKE ALL  on customer FROM deepak;
            


Comments

Post a Comment

Popular posts from this blog

SQL QUERY FOR PRIMARY AND FOREIGN KEY

Practical-3 1. Create table Account_key Column name Data Type Size Attributes Acc_no Varchar2 5 Primary key / first letter must start with ‘A’ Name Varchar2 30 NOT NULL City Varchar2 20 NOT NULL Balance Number 10,2 Balance >=500 Loan_taken Varchar2 3 Values(‘NO’,’YES’) ANS . create table account_key(acc_no varchar2(5) primary key check(acc_no like 'A%'),name varchar2(30) not null,city varchar2(20) not null,balance number(10,2) check(balance>=500),loan_taken varchar2(3) check(loan_taken='yes' or loan_taken='no')); acc_no Name City Balance loan_taken A001 Patel Jigar Mehsana 50000 YES A002 Patel Ramesh Mehsana 50000 YES A003 Dave Hardik Ahmeda...
Read more

SQL QUERY

                                Prac tical-2 1. Retrieve specified information for the account holder who are not in ‘Ahmedabad’. Ans: select * from account where city not in ‘Ahemdabad’; 2. Retrieve specified information for the account holder who are not in ‘Ahmedabad or ‘Vadodara’. Ans: select * from account where city not in('Ahemadabad', 'Vadodara'); 3. Retrieve those records of Account holder whose balance between is 50000 and 100000. Ans: select * from account where balance between 50000 and   1000000; .4. Retrieve those records of Account holder whose balance not between is 50000 and 100000. Ans: select * from account where balance not between 50000 and 100000; 5. Display only those records whose amount is 5000, 25000, 30000. Ans: select * from Transaction where amount in (5000, 25...
Read more

SQL QUERY practical-5

                                     PRACTICAL-5 CREATE TABLE SALESMEN(SNUM VARCHAR2(6) PRIMARY KEY CHECK(SNUM LIKE 'S%'), SNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), COMM NUMBER(5,2)); INSERT INTO SALESMEN VALUES('S1001','PIYUSH','LONDON',0.12); INSERT INTO SALESMEN VALUES('S1002','NIRAJ','SAN JOSE',0.13); INSERT INTO SALESMEN VALUES('S1003','MITI','LONDON',0.11); INSERT INTO SALESMEN VALUES('S1004','RAJESH','BARCELONA',0.15); INSERT INTO SALESMEN VALUES('S1005','HARESH','NEW YORK',0.10); INSERT INTO SALESMEN VALUES('S1006','RAM','BOMBAY',0.10); INSERT INTO SALESMEN VALUES('S1007','NEHAL','DELHI',0.09); CREATE TABLE CUSTOMER(CNUM VARCHAR2(6) PRIMARY KEY CHECK(CNUM LIKE 'C%'), CNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), RATING NUMBER(5), SNUM VARCHAR2(6)); INSERT INTO CUSTOMER VALUES(...
Read more

Practicals of RollBack, Commit and Cursor programs

Image
                                                                 Practical-10 AIM :- RollBack   , Commit and Cursor bases Programs. 1. Analyze output of following scripts: A.      Begin                               Insert into new_salary values ('6', '6000');                               Update new_salary set salary=4000 where id=4;    ...
Read more

Programs Based on Exception Handling and Trigger.

                                                       PRACTICAL-11 Aim :-   Programs Based on Exception Handling and Trigger. 1. Table: STUDENT (sr_no, name, address). Write a pl/sql code to retrieve the data from                student table whose sr.no is 02ce33. if     data not found then show the message “data not                found “  (Use EXCEPTION in pl/sql code). Ans :- declare no int; nm varchar2(20); begin no:=&no; select NAME into nm from student where ROLLNO=no; dbms_output.put_line('Name of student is '||nm); exception when NO_DATA_FOUND then dbms_output.put_line('No d...
Read more