IT Genie: November 2016

Saturday, 26 November 2016

Function in Oracle 11gR2 if your version supported

How to create scott user in oracle database by running demo.ddl script or some ddl script.?

create scott user

SQL> create user scott identified by tiger
default tablespace users temporary tablespace temp profile default;

User created.

SQL> grant connect, resource to scott;

Grant succeeded.

SQL> alter user scott account unlock;

User altered.

Demo build execution.

The script to create the tables (demobld.sql) is located in

%oracle_home%\sqlplus\demo\demobld.sql
or
$ORACLE_HOME/sqlplus/demo/demobld.sql,
for either windows or unix.

Depending on which version of Oracle you use, it will create the following tables:

DEPT

BONUS

SALGRADE

DUMMY

How to run it:

SQL> connect scott/tiger
Connected.

SQl> @?/sqlplus/demo/demobld.sql
Building demonstration tables. Please wait.

To drop the demo tables, run the demodrop.sql script in the same directory

or you can run below script or save in a scott.sql file and run from command prompt.

-------------------------------------------------------------------

CREATE TABLE EMP

(EMPNO NUMBER(4) NOT NULL,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7, 2),

COMM NUMBER(7, 2),

DEPTNO NUMBER(2));

INSERT INTO EMP VALUES

(7369, 'SMITH', 'CLERK', 7902,

TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);

INSERT INTO EMP VALUES

(7499, 'ALLEN', 'SALESMAN', 7698,

TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);

INSERT INTO EMP VALUES

(7521, 'WARD', 'SALESMAN', 7698,

TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);

INSERT INTO EMP VALUES

(7566, 'JONES', 'MANAGER', 7839,

TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

INSERT INTO EMP VALUES

(7654, 'MARTIN', 'SALESMAN', 7698,

TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES

(7698, 'BLAKE', 'MANAGER', 7839,

TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

INSERT INTO EMP VALUES

(7782, 'CLARK', 'MANAGER', 7839,

TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

INSERT INTO EMP VALUES

(7788, 'SCOTT', 'ANALYST', 7566,

TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES

(7839, 'KING', 'PRESIDENT', NULL,

TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

INSERT INTO EMP VALUES

(7844, 'TURNER', 'SALESMAN', 7698,

TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);

INSERT INTO EMP VALUES

(7876, 'ADAMS', 'CLERK', 7788,

TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

INSERT INTO EMP VALUES

(7900, 'JAMES', 'CLERK', 7698,

TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

INSERT INTO EMP VALUES

(7902, 'FORD', 'ANALYST', 7566,

TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

INSERT INTO EMP VALUES

(7934, 'MILLER', 'CLERK', 7782,

TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

CREATE TABLE DEPT

(DEPTNO NUMBER(2),

DNAME VARCHAR2(14),

LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS

(ENAME VARCHAR2(10),

JOB VARCHAR2(9),

SAL NUMBER,

COMM NUMBER);

CREATE TABLE SALGRADE

(GRADE NUMBER,

LOSAL NUMBER,

HISAL NUMBER);

INSERT INTO SALGRADE VALUES (1, 700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

CREATE TABLE DUMMY

(DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

SET TERMOUT ON

PROMPT Demonstration table build is complete.

EXIT

---------------------------------------------------------------

What is SQL, and why is it useful?

SQL

SQL, the Structured Query Language, is a standardized relational database access language. It is

useful because it allows a user to query, manipulate, define, and control data in an RDBMS. SQL is
sanctioned by ANSI, which determines standards on all aspects of SQL, including data types. However,
most relational database products, including Oracle, have their own extensions to the ANSI standard,
providing additional functionality within their respective products by further extending the use of SQL.
By learning SQL, you will be able to use the language on other non-Oracle databases with some minor
syntax adjustments.
SQL (pronounced “sequel”) is an acronym for Structured Query Language, a standardized
language used to access and manipulate data. The history of SQL corresponds closely with the
development of a relational databases concept published in a paper by Dr. E. F. Codd at IBM in 1970

Why Learn SQL?

SQL is accepted as the universal standard database access language.
SQL is used by most commercial database applications.
Although the language has evolved over the years with a large array of syntax enhancements and additions, most of the basic functionality has remained essentially unchanged. SQL knowledge will continue to be a fundamental skill because there is currently no mature and viable alternative language that accomplishes the same functionality. Learning Oracle’s specific SQL implementation allows you great insight into the feature-rich functionality of one of the largest and most successful database vendors.

An Introduction to Databases

What is a database? A database is an organized collection of data. A database

management system (DBMS) is software that allows the creation, retrieval, and manipulation of data. You use such systems to maintain patient data in a hospital,

bank accounts in a bank, or inventory in a warehouse.

A relational database management system (RDBMS) provides this functionality within the context of the relational database theory and the rules defined for Relational databases by Codd. These rules, called “Codd’s Twelve Rules,”

What Is Data?

Data is all around you; you make use of it every day. Your hair may be brown, your flight leaves from gate K10, you try to get up in the morning at 6:30 A.M. Storing data in related groups and making the connections among them are what databases are all about

How Is Data Organized?

Relational databases offer data independence, meaning a user does not need to know on which hard drive and file a particular piece of information is stored. The RDBMS provides users with data consistency and data integrity.

A relational database stores data in tables, essentially a two-dimensional matrix consisting of columns and rows. Let’s start with a discussion of the terminology used in relational databases. For example, if an employee works in the finance department, and we know that he can work for only one

department, then there should not be duplicate department records or contradicting data in the database. As you work through this lab, you will discover many of these useful and essential features.

A relational database stores data in tables, essentially a two-dimensional matrix consisting of columns and rows. Let’s start with a discussion of the terminology used in relational databases.

Tables

A table typically contains data about a single subject. Each table has a unique name that signifies the contents of the data. A database usually consists of many tables. For example, you may store data about books you read in a table called BOOK and store details about authors in the AUTHOR table.

Columns

Columns in a table organize the data further, and a table consists of at least one column. Each column represents a single, low-level detail about a particular set of data. The name of the column is unique within a table and identifies the data you find in the column. For example, the BOOK table may have a column for the title, publisher, date the book was published, and so on. The order of the columns is unimportant because SQL allows you to display data in any order you choose.

Rows

Each row usually represents one unique set of data within a table.

Primary Key

When working with tables, you must understand how to uniquely identify data within a table. This is the purpose of the primary key

Sunday, 6 November 2016

Oracle Architecture

Oracle Architecture-

oracle software works on basically few componet on computer

1- RAM- oracle created memory components.

2- some background process or program.

3- Harddisc- permanent file like datafiles and password file and others

The architecture includes physical components, memory components, processes, and logical structures.

An Oracle server includes an Oracle Instance and an Oracle database.

Oracle instance: An Oracle Instance consists of tw o different sets of components:

The first component set is the set of background processes (PMON, SMON, RECO, DBW0,
LGWR, CKPT, D000 and others).

Each background process is a computer program. These processes perform input/output and monitor other Oracle processes to providegood performance and database reliability.

The second component set includes the memory structures that comprise the Oracle instance.

When an instance starts up, a memory structure called the System Global Area (SGA) is allocated.

At this point the background processes also start. An Oracle Instance provides access to one and only one Oracle database.

Oracle database: An Oracle database consists of files.

· Sometimes these are referred to as operating system files, but they are actually database files

that store the database information that a firm or organization needs in order to operate.
· The redo log files are used to recover the database in the event of application program
failures, instance failures and other minor failures.
· The archived redo log files are used to recover the database if a disk fails.

Other files not shown in the figure include:

The required parameter file that is used to specify parameters for configuring an Oracle
instance when it starts up.

The optional password file authenticates special users of the database – these are
termed privileged users and include database administrators.

Alert and Trace Log Files – these files store information about errors and actions taken that affect the configuration of the database.

User and server processes:-

These processes are used to manage the execution of SQL statements.

A Shared Server Process can share memory and variable processing for multiple user
processes.

A Dedicated Server Process manages memory and variables for a single user process .

thanks plz continue...for next

Saturday, 5 November 2016

what is difference between data,metadata and information?

Data,metadata and information:-

The below images you can see how human innovated various ways to store thier data from ancident age to current .... from cave stone images to current datacenter