A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.
Syntax : SELECT a.column_name, b.column_name... FROM table1 a, table1 b WHERE a.common_field = b.common_field; For understanding self join, assume below customer table. Customer Table :
Now, we have to find all pairs of customers having the same rating without duplication. So, Actually this query wants two things,First same rating and second is without duplication.
Let us, understand this step by step,
we run below query first to find same rating,
SQL> SELECT C1.CNAME,C2.CNAME,C1.RATING FROM CUSTOMER C1,CUSTOMER C2 WHERE C1.RATING=C2.RATING ORDER BY C1.RATING;
Above query gives below result...
Here question is our main query want same rating as well as no duplication but in above result there are so many duplicated rows like,
HARDIK HARDIK 100
GITA GITA 100
and
GITA LAXIT 200
LAXIT GITA 200 is also duplicate.
Note: So removing duplication, we use another condition along with C1.RATING=C2.RATING in where clause.
Final query becomes like,
SQL> SELECT C1.CNAME,C2.CNAME,C1.RATING FROM CUSTOMER C1,CUSTOMER C2
DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. In a single line,we say that these all commands are use for data of a table.There is no contact with structure of a table.So, be careful. List of DML commands are,
It is a DML(Data Manipulation Language)statement, which is used to update a row of a table,i.e. data within table. Syntax : UPDATE table_name SET column_name = value [WHERE condition]; here WHERE CLAUSE is optional. Lets see an example, Assume, We have below table : STUDENT,
Now, we want to update age of PAL from 10 to 15 then,
SQL> UPDATE student SET age=15;
Ooops , It changes all three students age to 15 which is wrong.
Note : If you want to update specific data of row then we have to use WHERE CLAUSE.
Below is the right one,
SQL> UPDATE student SET age=15 WHERE name=PAL;
See the above result,now only age of PAL is changed.
Note : If you want to change date or name means.date or char/varchar datatype column's data you have to use single quote.
If we want to change name of PAL to DHRUVIN then,
SQL> UPDATE student SET name='DHRUVIN' where sid=02;
We also update multiple columns at a time,
Here,we update DHRUVIN to PAL AND his age 15 to 10 same as before,
SQL>UPDATE student SET name='PAL',age=10 where sid=02;
It is used to add new rows(tuples) of data to a table in the database.
There are two basic syntaxes of INSERT INTO statement as follows:
Syntax 1:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data and value1,value2... valueN are the values of particular column.
Please be sure that you enter appropiate value for particular column i.e. for column1 enter value of column1(value1).If you don't do so,it's may lead to error because for example column1 having NUMBER datatype and for coloumn2,it is VARCHAR2.Assume in above syntax for coloumn1 you enter value of second column means varchar2 type value which leads to mismatch of datatype.
Example 1:
ASSUME : we have empty table named STUDENT.
Table : STUDENT
We run below queries now,
INSERT INTO STUDENT (SID,NAME,AGE)
VALUES (01, 'KULDEEP', 20);
INSERT INTO STUDENT (SID,NAME,AGE)
VALUES (02, 'PAL', 04);
Result of above query :
Table : STUDENT
You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows:
Syntax 2:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
Example 2:
INSERT INTO STUDENT
VALUES (03, 'ARPIT',15);
At last we run above query which gives below result,
The most commonly used SQL command is SELECT statement. SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement. Syntax : SELECT {* |selected_coulumns} FROM {table_name} [WHERE condition] [GROUP BY condition_list] [HAVING condition] [ORDER BY {column_name1,column_name2,...,column_nameN [ ASC | DESC ] }; Note : here '|' means OR. In above SELECT query, SELECT and FROM are compulsory words where as WHERE,GROUP BY,HAVING AND ORDER BY are optionals. DISTINCT is used for eliminate duplicates and display only the unique results in the query output. ALL is for select/display all the columns of table. ASC - sort data in ASCENDING order DESC - sort data inDESCENDING order Example : Assume we have table named Student which contain student's id(sid),name and age. SELECT * FROM student; SELECT sid,name FROM student; SELECT * FROM student where age>20;
Here,First query display all the data of student table where as second one display only student's id and name of all students.Last query display only those data whose age is greater than 20.
DELETE is a DML(Data Manipulation Language) Statement.
The DELETE is used to remove rows from a table. A WHERE clause can be used with delete statement to remove choice rows. If no WHERE condition is specified, all rows will be removed.
After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.
DELETE keeps records in buffers (temporary storage) till the first ROLLBACK or COMMIT.
You can rollback after fired this command.
Syntax: - DELETE FROM table_name; - DELETE FROM table_name WHERE some_column=some_value; Example: - DELETE FROM student; - DELETE FROM student WHERE name='JAK';
TRUNCATE removes all rows(With DATA) from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
TRUNCATE is a DDL command.
You CAN NOT rollback in TRUNCATE.
Truncate removes the record permanently.
Truncate is DDL meaning all the data will be lost and only structure remains and we CAN NOT rollback. You CAN NOT use conditions(WHERE clause) in TRUNCATE.
You cannot grant truncate, you must grant DROP ANY TABLE to SOMEONE, in order to let him truncate your table.
TRUNCATE is much faster than DELETE.
Reason: When you type DELETE. All the data get copied into the Rollback Table space first then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the ROLLBACK Table space).All this process take time. But when you type TRUNCATE, It removes data directly without copying it into the Rollback table space. That’s why TRUNCATE is faster. Once you truncate you CAN NOT get back the data.
Drop is DDL statement.
The SQL DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.
Caution : You have to be careful while using this command because once a table is deleted then all the information available in the table would also be lost forever.
Syntax :
1) For Drop table : DROP TABLE table_name;
2) For Index : DROP INDEX index_name;
3) For Database : DROP DATABASE database_name;
Installation on Windows 7 or 8 will return operating system
version compatibility error. However, there will not be any such issue during
installation on older version of Windows operating system.
You can see whole Oracle 10g installation video on My youtube channel :
Oracle 10g installation is done via Oracle Universal Installer. As
mentioned earlier, Installation of Oracle 10g on Windows 7 will return version
compatibility error as shown here below
“Checking operating system version: must be 5.0,5.1,5.2 or 6.0.
Actual 6.1”
Do not panic, you will only need to edit a couple of files to get
the installation working. Let me show you how to handle this in a series of
steps.
Step 2:
Locate the file /install/oraparam.ini after you extract the
installation zip file to your local computer or external disk.
Step 3:
Change the following to include the version “Actual 6.1” as per
the installation. You may see other version numbers as well. Use the
appropriate version number for the changes you are going to make.
[Certified Versions]
#You can customise error message shown for failure, provide value for
CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0
Change to
[Certified Versions]
# You can customise error message shown for failure, provide value for
CERTIFIED_VERSION_FAILURE_MESSAGE
Windows = 5.0,5.1,5.2,6.0,6.1
Step 4:
Add this
additional block needed [Windows-6.1-required]
# Minimum display colours for OUI to run
MIN_DISPLAY_COLORS = 256
# Minimum CPU speed required for OUI
# CPU = 300
Step 5:
You will need to add Windows 7 compatabilty entries in
databasestageprereqdbrefhost.xml and databasestageprereqdb_prereqsdb
refhost.xml.
Find the following entries and insert the following . Keep in mind
that 6.1 for VERSION VALUE is based on the version we are using in this tutorials.
Use the appropriate version number based on the error message you get.
<!–Microsoft
Windows 7–>
<OPERATING_SYSTEM>
<VERSION VALUE="6.1"/>
</OPERATING_SYSTEM>
Here is entry
for “CERTIFIED_SYSTEMS” block adding the entry for Windows 7.
Note: If any of
the entries does not match the Operating System version then you will see the
following error after the installation starts for “Checking operating system
requirements step”
Step 6:
Save the
modified files and restart the installation running setup.exe. You will see the
messages as shown here below
Step 7:
Change Oracle
Home location and select the Installation type.
If you are not planning to use the starter database then you need to uncheck
the “Create Starter Database (additional 720MB) check box option
Enter the database name and password.
Step 8:
Click “Next” to
continue with the installation.
If you encounter any status with Error(s) during “Product-Specific Prerequisite
Checks” then you need to check further before you can continue with the
installation; otherwise your Oracle Server may not work.
Step 9:
As soon as all
the requirements gets verified and the errors are eliminated, You will see the
“Succeeded” status as below screen shot for all the Product-Specific
Prerequisite checks.
It is assumed
that this is a fresh install so no other Oracle products should be installed at
this time.
In this screen,
the Source and Destination locations must be selected.
The Source path
should reflect either your CD-ROM or the directory in which you unzipped the
source files. Leave this as the default. In the example above, the source files
were unzipped into c:DOCUME~1HARILOCALS~1temprar$ex12.1593diskstageproduct.xml.
For the
Destination, leave the Oracle Home named oraDb10g_home1 as the default. Choose
a hard disk drive that has at least 6 gigabytes of free space. In the example
here, we are using the E: drive.
Leave the
Oracleproduct10.1.0db_1 path as it is (the default). If you must change this to
a different drive, only change the drive letter and not the directories.
Click on the
Next button to select installation type. This may take up to 2 minutes
(depending on the speed of your machine). Once the product information has been
read, the following 3 "Available Products" options are presented:
After selecting
the type click on the next button to get the screen:
For this
install, we chose the "General Purpose" Database option. Click on the
Next button to continue.
The next step
is to identify the database using the Global database name
Keep the
default names for GLOBAL and the SID Otherwise, use the following (make a note
of these for later on): Global Database Name is orcl, Oracle SID is orcl
Click on the
Next button to continue to the "database file storage option" screen
In this step,
you need to select where the Oracle data files will be located. In large
operations, we typically keep the data files on a separate disk (or disks),
however, for this install, keep the default storage
Click on the
Next button to move to the "Summary" screen:
In this screen
(shown above), the products and files that will be installed are summarized.
Note that in this example, it will take 1.5 GB MB of disk space for the
installation of the software. Additional space will be required for the default
database.
If everything
looks OK at this point, click on the Install button to begin the installation.
During the
install, you will notice various Oracle products being copied over to the hard
disk.
Once the
installation is completed, the next step will be to configure the various
additional services and the database.
The following
tools will be automatically starts and it is optional. It is recommended,
although not required
Click on the
next button .The Oracle Database Configuration Assistant will appear for some
time while the default database is created and opened. As below
Once the
default database has been installed, the following screen will appear
Click on the
Password Management button and change the default passwords for the SYS,
SYSTEM, SCOTT and DBSNMP accounts as shown below:
Click the OK
button to complete the installation. The following screen will be appears:
After
completion of installation .reboot your computer.
To Remove the Oracle Products
Go to start
menu – > programs – > orcle-oradb10g_home5 – >oracle installation
products – > Click the universal installer, the screen will appears as below
click the
button installed product. to see the installed products. you will see the
screen as below
Click the
button remove to remove the oracle products or you can remove directly on
clicking deinstall product button in welcome page.
You can see whole Oracle 10g installation video on My youtube channel :
Go to start
menu – > programs – >oracle-oradb10g_home5 – >application development
-> click on the sql plus.
It will appears
like the screen below, Once SQL Plus is running, you will be prompted for 3
things: Username, password and Host String.
Fill in the
Username and password fields with the SYSTEM account and leave the Host String
field blank. Since we are connecting to a local Oracle database, no Host String
is required. Click on the OK button as shown below:
If the database
is running and the username and password are typed correctly, SQL*Plus should
log the SYSTEM user in and present the SQL>prompt as shown below
Uninstalling Oracle 10g
Following step
will describes the uninstallation of oracle 10g:
First you have to remove all the oracle products by using oracle
universal installer Go to start button -> programs ->
oracle-oraDb10g_home1 -> oracle installation products -> click on the
universal installer. You will get welcome screen, click on the deinstall
products
To remove the
oracle products. After uninstall the products. Now you have to remove all the
remaining oracle products manually run this command regedit.exe. You will get
registry editor.
Find related to
oracle by using ctrl+f and remove all you find related to oracle, Do this again
and again until you get no search. After completing this, reboot your system
Note: when you
installing oracle 10g at first time on your system you may not encounter any
problem. If any thing wrong you done when installation you may get an error
like this staging area error
To overcome with that error you must delete every think related to oracle by
using above uninstallation steps
.
You can see whole Oracle 10g installation video on My youtube channel :
