Learning SQL through MySQL software

Installing WAMP Server and Starting MySQL Console

To learn SQL you don’t necessarily need an Oracle SQL Server(oracle/sql is really big in size). We can achieve our goals still with MySQL. Install wamp server to get MySQL along with Apache and PHP. Latest release till today is 2.0i
          http://www.wampserver.com/en/download.php

W stands for Windows and amp stands for Apache, MySQL and PHP.

Now start wamp server clicking Start Menu -> Programs -> WampServer -> Start WampServer.

You’ll see there is a icon on the right side of the taskbar. Click (not right button click) it. In the Menu under submenu of MySQL click MySQL console.

It will ask for a password. By default there is no password set. Simply press enter.

Working with Databases

First step is to list the databases. You will use the following query to list databases.

show databases

Every query ends with a semicolon. Hence here is how the console responds to the query.

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | +--------------------+ 3 rows in set (0.01 sec)

Create your own database. The command to create database is

create database databasename

Here is the console response for a sample command.

mysql> create database bankdb; Query OK, 1 row affected (0.01 sec)

Now you have to select a database to work with. In our case it’s bankdb;

mysql> use bankdb; Database changed

Working with Tables

To list tables inside the database you have to use “show tables” query.

mysql> show tables; Empty set (0.00 sec)

Note Oracle SQL query “select * from tab” doesn’t work here.

mysql> select * from tab; ERROR 1146 (42S02): Table 'bankdb.tab' doesn't exist

As there are no tables on the database we’ll create some.

During creating tables we need to remember that data type number and varchar2 does not exist in mysql. To know more about mysql data types follow here.

          http://dev.mysql.com/doc/refman/5.0/en/data-types.html

To compare with oracle data types check link this too.

          http://ss64.com/ora/syntax-datatypes.html

Hence we replace varchar2 with varchar and number with float.

And we create tables with following commands.

create table branch         (branch_name varchar(15),         branch_city varchar(12) not null,         assets float(10,2) not null,         constraint b_pk primary key(branch_name),         constraint b_chk check(assets>=0)); create table customer         (customer_name varchar(20),         customer_street varchar(15),         customer_city varchar(15)not null,         constraint c_pk primary key(customer_name)); create table account         (account_no char(5),         branch_name varchar(15),         balance float(10,2) not null,         constraint a_pk primary key(account_no),         constraint a_fk foreign key (branch_name) references branch(branch_name),         constraint a_chk1 check (balance>=0),         constraint a_chk2 check (account_no like 'A-%')); create table loan         (loan_no char(5),         branch_name varchar(15),         amount float(10,2) not null,         constraint l_pk primary key(loan_no),         constraint l_fk foreign key (branch_name) references branch(branch_name),         constraint l_chk1 check (amount >= 0),         constraint l_chk2 check (loan_no like 'L-%')); create table depositor         (customer_name varchar(15),         account_no char(5),         constraint de_pk primary key (customer_name, account_no),         constraint de_fk1 foreign key (customer_name) references customer(         customer_name), constraint de_fk2 foreign key (account_no) references         account(account_no)); create table borrower         (customer_name varchar(15),         loan_no char(5),         constraint bo_pk primary key (customer_name, loan_no),         constraint bo_fk1 foreign key (customer_name) references customer (         customer_name), constraint bo_fk2 foreign key (loan_no) references         loan (loan_no));

Show current list of tables.

mysql> show tables; +------------------+ | Tables_in_bankdb | +------------------+ | account | | borrower | | branch | | customer | | depositor | | loan | +------------------+ 6 rows in set (0.00 sec)

You use desc command to view descriptions of a table.

mysql> desc account; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | account_no | char(5) | NO | PRI | | | | branch_name | varchar(15) | YES | MUL | NULL | | | balance | float(10,2) | NO | | NULL | | +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)

Now it’s time to insert data into the tables.

insert into branch values('Brighton','Brooklyn',7100000); insert into branch values('Downtown', 'Brooklyn',9000000); insert into branch values('Mianus', 'Horseneck',400000); insert into branch values('North Town', 'Rye',3700000); insert into branch values('Perryridge', 'Horseneck',1700000); insert into branch values('Pownal', 'Bennington',300000); insert into branch values('Redwood', 'Palo Alto',2100000); insert into branch values('Round Hill', 'Horseneck',8000000); insert into customer values('Adams','Spring','Pittsfield'); insert into customer values('Brooks','Senator','Brooklyn'); insert into customer values('Curry','North','Rye'); insert into customer values('Glenn','Sand Hill','Woodside'); insert into customer values('Green','Walnut','Stamford'); insert into customer values('Hayes','Main','Harrison'); insert into customer values('Jhonson','Alma','Palo Alto'); insert into customer values('Jones','Main','Harrison'); insert into customer values('Lindsay','Park','Pittsfield'); insert into customer values('Smith','North','Rye'); insert into customer values('Turner','Putnam','Stamford'); insert into customer values('Williams','Nassau','Princeton'); insert into account values('A-101','Downtown',500); insert into account values('A-102','Perryridge',400); insert into account values('A-201','Brighton',900); insert into account values('A-215','Mianus',700); insert into account values('A-217','Brighton',750); insert into account values('A-222','Redwood',700); insert into account values('A-305','Round Hill',350); insert into loan values('L-11', 'Round Hill',900); insert into loan values('L-14', 'Downtown',1500); insert into loan values('L-15', 'Perryridge',1500); insert into loan values('L-16', 'Perryridge',1300); insert into loan values('L-17', 'Downtown',1000); insert into loan values('L-23', 'Redwood',2000); insert into loan values('L-93','Mianus',500); insert into depositor values('Hayes', 'A-102'); insert into depositor values('Jhonson', 'A-101'); insert into depositor values('Jhonson', 'A-201'); insert into depositor values('Jones', 'A-217'); insert into depositor values('Lindsay', 'A-222'); insert into depositor values('Smith', 'A-215'); insert into depositor values('Turner', 'A-305'); insert into borrower values('Adams', 'L-16'); insert into borrower values('Curry', 'L-93'); insert into borrower values('Hayes', 'L-15'); insert into borrower values('Jackson', 'L-14'); insert into borrower values('Jones', 'L-17'); insert into borrower values('Smith', 'L-11'); insert into borrower values('Smith', 'L-23'); insert into borrower values('Williams', 'L-17');

Now you can perform queries on existing tables and data for practicing. I’ll add some nice queries soon.