Looking for a Tutor Near You?

Post Learning Requirement »
x

Choose Country Code

x

Direction

x

Ask a Question

x

x
x
For better view click here
x
Hire a Tutor
DISCOVER

Structured Query Languge

Loading...

Published in: Computer Science
377 Views

Question answer series

Priyanka / Navi Mumbai

8 years of teaching experience

Qualification: master's pf computer engineering

Teaches: Computer Science

Contact this Tutor
  1. Que : what is package? Ans: A package is a group of related PL/SQL objects (variables, constants, types, and cursors) and subprograms that is stored in the database as a unit. Being a database object, a package resides in a schema, and its use is controlled by privileges. Among its differences from regular PL/SQL programs are that a package as such does not do anything. It is a collection of subprograms and objects, at least some of which are accessible to applications outside of it. It is the subprograms in the package that contain the executable code. A package has the following two parts: 1. 2. The package specification is the public interface to the package. It declares all objects and subprograms that are to be accessible from outside the package. Packages do not take parameters, so these constitute the entire public interface. The package body is the internal portion of the package. It contains all objects and subprograms that are to be local to the package. It also contains definitions of the public cursors and subprograms. The package specification declares but does not define these. One of the advantages of using packages is that the package specification is independent of the body. You can change the body and, so long as it still matches the specification, no changes to other code are needed, nor will any other references become invalid. • Packages cannot be nested, but they can call one another's public subprograms and reference one another's public objects Creating Packages To create a package, you use the SQL statement CREATE PACKAGE for the specification and CREATE PACKAGE BODY for the body. Creating the Package Specification • The syntax of the CREATE PACKAGE statement is as follows: CREATE [OR REPLACE] PACKAGE package_name IS {PL/SQL declarations} END;
  2. Que. How array are declared? Ans: PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. A varray is used to store an ordered collection of data, but it is often more useful to think of an array as a collection of variables of the same type. All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element. First Element Numbers[ll Numbers[2J Last Element Numbers[3J An array is a part of collection type data and it stands for variable-size arrays. Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically. Creating a Varray Type: A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray. The basic syntax for creating a VRRAY type at the schema level is: CREATE OR REPLACE TYPE varra t e name IS VARRAY n of Where, • varray_type_name is a valid attribute name, • n is the number of elements (maximum) in the varray, element_type is the data type of the elements of the array. Maximum size of a varray can be changed using the ALTER TYPE statement. For example, CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); Type created.
  3. QUE: IMPORTANCE OF PL/SQL? ANS: FOLLOWING ARE THE IMPORTANCE OF PL/SQL: SQL is the standard database language and PL/SQL is strongly integrated with SQL. PL/SQL supports both static and dynamic SQL. Static SQL supports DML operations and transaction control from PL/SQL block. Dynamic SQL is SQL allows embedding DDL statements in PL/SQL blocks. PL/SQL allows sending an entire block of statements to the database at one time. This reduces network traffic and provides high performance for the applications. PL/SQL gives high productivity to programmers as it can query, transform, and update data in a database. PL/SQL saves time on design and debugging by strong features, such as exception handling, encapsulation, data hiding, and object-oriented data types. Applications written in PL/SQL are fully portable. PL/SQL provides high security level. PL/SQL provides access to predefined SQL packages. PL/SQL provides support for Object-Oriented Programming. PL/SQL provides support for Developing Web Applications and Server Pages.
  4. QUE: HOW TO INITIATE THE TRIGGER?or HOW TRIGGER PREDICATE ARE ACTIVATED ANS: Triggers are blocks of PL/SQL code that execute automatically in response to events. Database triggers reside in a database and respond to changes in data. These triggers need not to be confused with application triggers. Database triggers are a technology that superseded application triggers. Triggers are created the way stored procedures and packages are created, by using text editors and run them using SQL* Plus or Server Manager. A trigger is like a package in that: It takes no parameters as such. It refers to, responds to, and possibly affects the data in 1. a database. It cannot be directly called like a procedure. To fire (execute) a trigger, you must make a 2. change in a database to respond. Triggers can be classified in three ways: 1. 2. 3. INSERT triggers, UPDATE triggers, and DELETE triggers. This is a classification based on a statement to which a trigger responds. The categories are not mutually exclusive, meaning one trigger can respond to any or all of these statements. Row triggers and statement triggers. Any of the above statements can affect any number of rows in a table at once. A row trigger is fired once for each row affected. A statement trigger is fired once for each statement, however many rows it affects. BEFORE triggers and AFTER triggers. This specifies whether the trigger is fired before or after the data modification occurs. All three of these classifications apply to all triggers. So there are, for example, BEFORE DELETE OR INSERT statement triggers and AFTER UPDATE row triggers. Creating Triggers The syntax of the CREATE TRIGGER statement is as follows: CREATE [OR REPLACE] TRIGGER trigger_name BEFORE I AFTER DELETE I INSERT I UPDATE [OF column _ list] ON table name [ FOR EACH ROW [ WHEN predicate ] ] {PL/SQL block}; In the above, square brackets ([ ]) enclose optional elements. Vertical bars ( I ) indicate that what precedes may be replaced by what follows.
  5. Here is an example: CREATE TRIGGER give_bonus AFTER UPDATE OF sales ON salespeople FOR EACH ROW WHEN sales >500000 BEGIN UPDATE salescommissions SET bonus = bonus + salary; END; Enabling and Disabling Triggers/initiate the trigger/activate the trigger: Just because a trigger exists does not mean it is effective. If a trigger is disabled, it does not fire. By default, all triggers are enabled when created, but you can disable a trigger using the ALTER TRIGGER statement. To do this, a trigger must be in your schema, or you must have the ALTER ANY TRIGGER system privilege. Here is the syntax: ALTER TRIGGER trigger_name DISABLE;
  6. Que: How variable are defined in pl/sql? Ans: Variable are defined in pl/sql as follows: PL/SQL Placeholders: Placeholders are temporary storage area. PL/SQL Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block. Define PL/SQL Placeholders: Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below. Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile PL/SQL Variables: These are placeholders that store the values that can change through the PL/SQL Block. General Syntax to declare a variable is variable name datatype [NOT NULL variable name is the name of the variable. datatype is a valid PL/SQL datatype. value 1 ; NOT NULL is an optional specification on the variable. value or DEFAULT valueis also an optional specification, where you can initialize a variable. Each variable declaration is a separate statement and must be terminated by a semicolon. For example, if you want to store the current salary of an employee, you can use a variable. DECLARE salary number (6) ; salary" is a variable of datatype number and of length 6.
  7. Que: how partition the table? Ans: Table partitioning: Now a day's enterprises run databases of hundreds of Gigabytes in size. These databases are known as Very Large Databases (VLDB). From Oracle Ver. 8.0 Oracle has provided the feature of table partitioning i.e. you can partition a table according to some criteria. For example you have a SALES table with the following structure Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993 and 1994. And most of the time you are concerned about only one year i.e. you give queries like the following: select sum (amt) from sales where year—1991 ; select product, sum (amt) from sales where year—1992 Group by product; Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table. CREATING PARTITION TABLES • In the below example sales table is created with 5 partitions. Partition pl will contain rows of year 1991 and it will be stored in tablespace ul . Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2. Similarly p3 and p4.
  8. To create a partition table give the following statement: create table sales (year number (4) product varchar2 (10) amt number (10, 2) ) partition parti tion parti tion parti tion parti tion by pl p2 p3 p4 range values values values values (year) less than less than less than less than (1992) (1993) (1994) (1995) tablespace ul , tablespace u2 , tablespace u3 , tablespace u4 , parti tion p5 values less than (MAXVALUE) tablespace u5 ; In Oracle you can partition a table by 1. Range Partitioning 2. Hash Partitioning 3. List Partitioning 4. Composite Partitioning Range Partitioning: This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, value of year. Performance is best when the data evenly distributes across the range Hash partitioning Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key
  9. The following example shows how to create a hash partition table. The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tabl ,tab2, ...). CREATE TABLE products (partno NT-TIGER , description VARCHAR2 (60) ) PARTITION BY HASH (partno) PARTITIONS 4 STORE IN (tabl, tab2, tab3, List Partitioning tab4) ; Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping. example as follow: create table customers (custcode nurnber (5) Name varchar2 (20) Addr varchar2 (10 , 2) City varchar2 (20) Bal nurnber (10 , 2) ) Partition by list (city) Partition north India values ( 'DELHI' 'CHANDIGARH' ) Partition east India values ( 'KOLKOTA' 'PATNA' ) Parti tion south India values ( 'HYDERABAD' 'BANGALORE' ' CHENNAI' ) Parti tion west India values ( 'BOE,'EAY' GOA' )
  10. COMPOSITE PARTITONING Composite partitioning partitions data using the range method, and within each partition, sub partitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning. When creating composite partitions, you specify the following: 1. 2. 3. 4. 5. 6. Partitioning method: range Partitioning column(s) Partition descriptions identifying partition bounds Subpartitioning method: hash Subpartitioning column(s) Number of subpartitions for each partition or descriptions of subpartitions Example: three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (tabl, ...,tab4). CREATE TABLE PRODUCTS (partno NUIvEER, description VARCHAR(32) costprice PARTITION BY RANGE (partno) SUBPARTITION BY HASH (description) SUBPARTITIONS 8 (PARTITION pi PARTITION p2 PARTITION p3 STORE IN (tabl, tab2, tab3, tab4 ) VALUE S VALUE S VALUE S LESS LESS LESS THAN THAN THAN (100) , (200) , (MAXVALUE) ) ;
  11. Que: purpose of foreign key? Ans: A foreign key is a column that references a column of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. For example, • say we have two tables, 1. a CUSTOMER table that includes all customer data, and 2. an ORDERS table that includes all customer orders. Business logic requires that all orders must be associated with a customer that is already in the CUSTOMER table. To enforce this logic, we place a foreign key on the ORDERS table and have it reference the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. Que: what is database, dbms. Rdbms: Ans: Database: a database is basically a collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system. Traditional databases are organized by fields, records, and files. o Dbms: A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone number. A database management system (DBMS) is a collection ofproqrams that enables you to store, modify, and extract information from a database. There are many different types of database management systems, ranging from small systems that run on personal computers to huge systems that run on mainframes.
  12. Rdbms: • relational database management system and pronounced as separate letters, a type of database management system (DBMS) that stores data in the form of related tables. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational systems is that a single database can be spread across several tables

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related Study Notes

Upload Study Notes

If you have your own Study Notes which you think can benefit others, please upload on LearnPick. For each approved Study Note you will get 100 Credit Points and 100 Activity Score which will increase your profile visibility.

Upload Now
Home > Study Notes > Structured Query Languge