Manoj presented lecute number 7 this week, titled “SQL III”. True to the title, we churned through 2 hours of SQL definitions and examples. Interestingly there are a number of differences between Oracle and MySQL. To really get the hang of SQL I have just set up a MySQL server on my home PC and running over the commands show in the lectures/tutorials and in the Text book.
Interestingly in some I stumbled upon the following in a google search: ‘On its earnings call last night, Oracle president Charles Phillips crowed about IDC data showing Oracle’s database share at 44.3% vs. 21% for IBM vs. 18.5% for Microsoft.’ Source: http://itknowledgeexchange.techtarget.com/channel-marker/database-market-share-war-resumes/
This makes me wonder why Oracle is a such a superior option over other (is the actual product better?). On some further investigation (of Oracle’s wikipedia page: http://en.wikipedia.org/wiki/Oracle_Database)there may be a couple of major reasons:
Oracle Corporation claims to have provided:
- the first commercially-available SQL-based database (1979)[38]
- the first database to support symmetric multiprocessing (SMP) (1983)
- the first distributed database (1986)
- the first database product tested to comply with the ANSI SQL standard (1993)[38]
- the first 64-bit database (1995)
- the first database to incorporate a native JRE (1998)
- the first proprietary RDBMS to become available on Linux (1998)[39]
- the first database to support XML (1999)
The name Oracle comes from the code-name of a CIA-funded project… <- hah
In any case, Oracle is and will be the king of RDBMSs for some time into the future (perhaps OODBs will make the emergence soon?) so I am keen to become competent in SQL. Below are some of the queries we have covered thus far:
***SHOW ALL TABLES ON A GIVEN DB
SELECT table_name FROM tabs;
CREATE TABLE tnt_departure (
t_code CHAR(3),
d_dep_date date, d_ret_date date, d_director VARCHAR2(25) ,
d_adult_fare integer, d_child_fare integer,
PRIMARY KEY (t_code, d_dep_date)
);
Pearl of the week: Adding Foreign Key constraints is much easier to do last using the ALTER TABLE command:
ALTER TABLE tnt_departure add CONSTRAINT dep_tcode_pk FOREIGN KEY references tnt_tour(t_code);
ALTER TABLE tnt_departure ADD CONSTRAINT dep_pk PRIMARY KEY (t_code, d_dep_date);