DBMS Lab Manual. Jitendra Inc. Patel

      

      DBMS LAB MANUAL

      The Structured Query Language (SQL) is a computer language for accessing and manipulating databases.

      The fundamental concept is to think of the data as being stored in one or more tables. When a request is made to retrieve data from these tables, which is called a "query", the resultant output is also presented in as table.

      There are many different versions of the SQL language, but to be in compliance with the ANSI SQL '92 Standard, they must use and support the same major keywords in a similar manner (such as SELECT, UPDATE, DELETE, INSERT, WHERE, and others.

      This manual is specially written for Students who are interested in understanding Structured Query Language and PL-SQL concepts in the Computer Engineering and Information technology field and wants to gain enhance knowledge about power of SQL Language in Relational Database Management System Development.

      The manual covers practical point of view in all aspects of SQL and PL/SQL including DDL, DML,DCL sublanguages, also there are practices for Views, Group by, Having Clause. All PL-SQL concepts like Condition and Loop Structures, Functions and Procedures, Cursor, Triggers, Locks are illustrated using best examples.

      Copyright © 2012 Jitendra Patel

      All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.

      Warning and Disclaimer

      Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an "as is" basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

      Practical 1: Create synonyms, sequence and index.

      Synonyms:

      A synonym is an alias for a database object (table, view, procedure, function, package, sequence, etc.). Synonyms may be used to reference the original object in SQL as wel as PL/SQL.

      They can be used to hide ownership and location of the database objects they refer to and minimize the impact of moving or renaming the database objects.

      The syntax for creating a synonym is:

      create [or replace] [public] synonym [schema .] synonym_name

      for [schema .] object_name [@ dblink];

      There are two types of synonyms:

      · private

      Private synonyms exist only in a specific user schema. The owner of the synonym maintains control over availability to other users.

      · public

      A public synonym is available to all users

      For example:

      create public synonym suppliers

      for app.suppliers;

      This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

      select * from suppliers;

      If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

      create or replace public synonym suppliers

      for app.suppliers;

      Dropping a synonym

      It is also possible to drop a synonym.

      For example:

      drop public synonym suppliers;

      This drop statement would drop the synonym called suppliers that we defined earlier.

      Sequences (Autonumber)

      In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

      The syntax for a sequence is:

      CREATE SEQUENCE sequence_name

      MINVALUE value

      MAXVALUE value

      START WITH value

      INCREMENT BY value

      CACHE value;

      For example:

      CREATE SEQUENCE supplier_seq

      MINVALUE 1

      MAXVALUE 999999999999999999999999999

      START WITH 1

      INCREMENT BY 1

      CACHE 20;

      This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

      If you omit the MAXVALUE option, your sequence will automatically default to:

      MAXVALUE 999999999999999999999999999

      So you can simplify your CREATE SEQUENCE command as follows:

      CREATE SEQUENCE supplier_seq

      MINVALUE 1

      START WITH 1

      INCREMENT BY 1

      CACHE 20;

      Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

      For example:

      supplier_seq.nextval

      This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

      INSERT INTO suppliers

      (supplier_id, supplier_name)

      VALUES

      (supplier_seq.nextval, 'Kraft Foods');

      This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

      Index

      What is an Index?

      An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

      Create an Index

      The syntax for creating a index is:

      CREATE [UNIQUE] INDEX index_name

      ON table_name (column1, column2, . column_n)

      [ COMPUTE STATISTICS ];

      UNIQUE indicates that the combination of values in the indexed columns must be unique.

      COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when