Mastering SQL for Data Science: A Comprehensive Guide Day-8

Mastering SQL for Data Science: A Comprehensive Guide 

Day-8

Other SQL Database Objects

v  Views:

A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. A view is a database object that allows generating a logical subset of data from one or more tables. A table is a database object or an entity that stores the data of a database.

Views are used for security purpose because they provide encapsulation of the name of the table. Data is in the virtual table, nor stored permanently. Views display only selected data. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on written SQL query to create a view. A view contains no data of its own but it is like a window through which data from table/tables can be viewed or changed. The table/tables on which a view is based is called base table/tables.

v  Create a view:

Syntax: create view <view_name> as select <*/Column_names> from <table_name>;

v  Display the structure of any created view:

Syntax: desc <view_name>;

v  DML operation on view:

ü  Insert command:

Syntax: insert into <view_name>( <Column_lists>) values (<values list>);

E.g: insert into v_test values(‘102’, ‘Jay’, ‘Jamnagar’);

ü  Update command:

Syntax: update <view_name> set <column_name> = <new_value> where <condition>;

E.g: update v_test set ename =’Tanushri’ where empno=101;

ü  Delete command:

Syntax: delete from <view_name> where <condition>;

E.g: delete from v_test where empno=102;

v  Drop any view:

Syntax: drop view <view_name>;

v  Types of Views:

 1.    Simple View (Updatable View):

This type of view can only contain a single base table or is created from only one table. DML operations could be performed through a simple view. Simple view does not contain group by, distinct, pseudo column like rownum, columns defined by expressions. When user will change within the view /base table, then effect of that changes will be visible simultaneously with in view/base table.

E.g: create view v_emp as select * from emp;              OR

       Create view v_test as select empno, ename, salary from emp;

2.   Complex View (Readable View):

This view can be constructed on more than one base table. Particularly, complex views can contain: join conditions, a group clause, order by clause. DML operators could not always be performed through a complex view. User can not apply insert, update and delete on complex view directly.

E.g: create view v_read as select e.emp_no, e.ename, e.job, e.salary, d.dname, d.location from emp e, dept d where e.deptno=d.deptno;

v  Synonym:

    A synonym is a database object that provides an alternative name for another database objects, referred to as the base object that can exist on a local or remote server. A synonym can only be defined on the name of a table or view at the current server. An alias can be defined on an undefined name. A synonym can only be defined on the name of an existing table or view.

ü  Create Synonym:

    Syntax: create synonym <synonym_name> for <table_name>;

    E.g: create synonym emp for employee;

ü  Drop Synonym:

    Syntax: drop synonym <synonym_name>;

    E.g: drop synonym emp;

v  Sequence:

A sequence is a database object that generates and produces integer values in sequential order. It automatically generates the primary key and unique key values. It may be in ascending or descending order. It can be used for multiple tables. SQL sequences specifies the properties of a sequence object while creating it. An object bound to a user-defined schema called a sequence produces a series of numerical values in accordance with the specification used to create it. The series of numerical values can be configured to restart (cycle) when it runs out and is generated in either ascending or descending order at a predetermined interval. Contrary to identity columns, sequences are not linked to particular tables. Applications use a sequence object to access the next value in the sequence. The application has control over how sequences and tables interact. A sequence object can be referred to by user applications, and the values can be coordinated across various rows and tables.

Different Features of Sequences

1. A sequence is a database object that generates and produces integer values in sequential order.
2. It automatically generates the primary key and unique key values.
3. It may be in ascending or descending order.
4. It can be used for multiple tables.
5. Sequence numbers are stored and generated independently of tables.
6. It saves time by reducing application code.
7. It is used to generate unique integers.
8. It is used to create an auto number field.
9. Useful when you need to create a unique number to act as a primary key.
10. Oracle provides an object called a Sequence that can generate numeric values. The value generated can have maximum of 38 digits
11. Provide intervals between numbers.

v  Generate a sequence:

    CREATE SEQUENCE sequence_name

    START WITH initial_value

    INCREMENT BY increment_value

    MINVALUE minimum value

    MAXVALUE maximum value

    CYCLE|NOCYCLE ;

 

    E.g: CREATE SEQUENCE rollno

    start with 1

    increment by 1

    minvalue 0

    maxvalue 100

    cycle;

 The above query will create a sequence named rollno. The sequence will start from 1 and will be incremented by 1 having maximum value of 100. The sequence will repeat itself from the start value after exceeding 100.

ü  Use of Sequence:

    Create a table named students with columns as id and name.

    CREATE TABLE students(ID number(10),NAME char(20));

Now insert values into a table

    INSERT into students VALUES(rollno.nextval,'Shubham');

    INSERT into students VALUES(rollno.nextval,'Aman');

ü  Drop a Sequence:

    Syntax: drop sequence <sequence_name>;

    E.g: drop sequence rollno;