Difference Between Sql - MySql and Oracle

1
Difference Between SQL / MYSQL / ORACLE
www.myassignmenthelp.net
www.assignmenthelp.net
2

• SQL Database
• SQL (Structured Query Language) is a
  database computer language designed for managing
  data in relational database management systems
  (RDBMS).
• MYSQL Database
• MySQL is a relational database management
  system (RDBMS) that runs as a server providing
  multi-user access to a number of databases.
• ORACLE Database
• Oracle is the name of the database
  management system that comes from Oracle
  Corporation.

3

• PRIMARY KEY
• A Primary Key is a column or combination of
  columns whose values uniquely identify a row or
  record in the table.
• Examples for specifying a Primary Key,

SQL MYSQL ORACLE
Primary Key CREATE table table_name ( id integer PRIMARY KEY, FirstName varchar(30), LastName varchar(30) ) CREATE table table_name ( id integer, FirstName varchar(30), LastName varchar(30), PRIMARY KEY (id) ) CREATE table table_name ( id integer PRIMARY KEY, FirstName varchar(30), LastName varchar(30) )
Specifying a primary key by Altering a table ALTER TABLE table_name ADD PRIMARY KEY (id) ALTER TABLE table_name ADD PRIMARY KEY (id) ALTER TABLE table_name ADD PRIMARY KEY (id)
DROP TABLE DROP TABLE table_name DROP TABLE table_name DROP TABLE table_name
www.assignmenthelp.net
4
Sample Output
Sample output for CREATE Table
Sample output for DROP Table
www.assignmenthelp.net
5

• FOREIGN KEY
• A FOREIGN KEY in one table points to a PRIMARY
  KEY in another table.
• Examples for specifying a Foreign key when
  creating a table (using CREATE TABLE) or by
  changing the existing table structure (using
  ALTER TABLE).
• Table1 Student
• Table2 Book

Column Name Datatype characteristic
Sid integer Primary Key
LastName varchar  
FirstName varchar  
Column Name Datatype characteristic
BookId integer Primary Key
BookName varchar  
Student_Sid integer Foreign Key
Amount integer  
6
Foreign Key
SQL MYSQL ORACLE
Foreign Key Create table Book ( BookId integer PRIMARY KEY, BookName varchar(30), Amount integer, Student_Sid interger references Student(Sid)) Create table Book ( BookId integer, BookName varchar(30), Student_Sid integer, Amount integer, Primary Key (BookId), Foreign Key (Student_Sid) references Student(Sid)) Create table Book ( BookId integer PRIMARY KEY, BookName varchar(30), Amount integer, Student_Sid integer references Student(Sid))
Specifying a Foreign Key by Altering a table ALTER table Book ADD FOREIGN KEY (Student_Sid) references Student(Sid) ALTER table Book ADD FOREIGN KEY (Student_Sid) references Student(Sid) ALTER table Book ADD FOREIGN KEY (Student_Sid) references Student(Sid)
DROP Table DropTABLE Book Drop TABLE Book drop table Book cascade constraints Note if foreign key are available in that table
7
Sample Output
Sample Output for CREATE Table
Sample Output for Drop Table
8
CREATE TABLE
SQL MYSQL ORACLE
The CREATE TABLE statement is used to create a table in a database. CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, .... ) CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, .... ) CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, .... )
Creates an index on a table. Duplicate values are allowed. CREATE INDEX index_name ON table_name (column_name) CREATE INDEX index_name ON table_name (column_name) CREATE INDEX index_name ON table_name (column_name)
Creates a unique index on a table. Duplicate values are not allowed. CREATE UNIQUE INDEX index_name ON table_name (column_name) CREATE UNIQUE INDEX index_name ON table_name (column_name) CREATE UNIQUE INDEX index_name ON table_name (column_name)
Create View syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
9
DROP TABLE
SQL MYSQL ORACLE
It is used to delete a Table. DROP TABLE Student DROP TABLE Student DROP TABLE Student
It is used to delete a database. DROP DATABASE database_name DROP DATABASE database_name DROP DATABASE database_name
Delete the data inside the table only TRUNCATE TABLE table_name TRUNCATE TABLE table_name TRUNCATE TABLE table_name
The DROP INDEX statement is used to delete an index in a table. DROP INDEX table_name.index_name ALTER TABLE table_name DROP INDEX index_name DROP INDEX index_name
10
ALTER TABLE
SQL MYSQL ORACLE
Rename the table name ALTER TABLE table_name  RENAME TO new_table_name ALTER TABLE table_name  RENAME TO new_table_name ALTER TABLE table_name  RENAME TO new_table_name
Add a column in a table ALTER TABLE table_name  ADD column_name datatype ALTER TABLE table_name  ADD column_name column-definition ALTER TABLE table_name  ADD column_name column-definition
Delete a column in a table ALTER TABLE table_name DROP COLUMN column_name ALTER TABLE table_name DROP COLUMN column_name ALTER TABLE table_name DROP COLUMN column_name
Change the data type of a column in a table ALTER TABLE table_name ALTER COLUMN column_name datatype ALTER TABLE table_name ALTER COLUMN column_name datatype ALTER TABLE table_name ALTER COLUMN column_name datatype
11

• References
• http//en.wikipedia.org/wiki/Sql
• Dr. Chens homepage http//bit.csc.lsu.edu/chen/
  chen.html
• Database Systems A First Course, J.D. Ullman
  J. Widom
• http//en.wikipedia.org/wiki/Oracle
• http//en.wikipedia.org/wiki/Mysql
• http//www.w3schools.com/sql/default.asp

12

Thank You
www.myassignmenthelp.net