DBMS Solutions By Deepak Shah

                                                        PRACTICAL-9 Aim :- FUNCTION AND PROCEDURE BASED PL/SQL PROGRAMS 1. Create a Function which will work as addition e.g select ADDITION(10,10) from dual; Ans:- create or replace function addition(a1 in number,a2 in number)               return number is               ans number;               begin               ans:=a1+a2;            ...

Practicals of pl/sql

                                                 PRACTICAL – 6 1. What is Index ? Ans :- An Index is a copy of selected columns of data from a table that can be searched very efficiently that also includes a low-level disk block address or direct link to the complete row of data it was copied from. Some databases extend the power of indexing by letting developers create indexes on functions or expressions. 2. Create a Simple Index idx_order on order amount from the orders table. Ans:-create index idx_order on ordered(atm); 3. Create a unique index idx_order1 number and customer number columns of the orders table. Ans:- create unique index idx_order1 on ordered(atm,cnum); 4. What is sequence? Ans :- A Sequence is a set of integers 1,2,3… that are generated in order on demand. Sequences are frequently used in databases because m...

                                           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’,600...

                                     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(...

SQL QUERY PRACTICAL:-4

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...