Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Sunday, December 30, 2012

comment_icon 0 Slice of SQL

Wanna learn sql ? in a different way . lets try this.
Once there was a quy who went  shop and said “ give me few chocolates “. Shopkeeper sais do you have basket he said no .shopkeeper said go and bring the basket . he just created a basket . in database our basket is the TABLE  and we can create it by using this following statement.

SQL> create table basket(name varchar2(20),price number(4),quantity number(2));
(Output:>)Table created.

In above query > basket is the name of table .
name is the column of type character of length 20 .
price is the column of type number of size 4
quantity is the column of size 4.
After getting the basket he again went to shop and said give me 5 dairymilk,4 5star and 3 perk in this basket . we can do this in database by  “insert command”

SQL> insert into basket(name,price,quantity) values('dairy milk',20,5);
1 row created.

We can even do this in a simple way .

SQL> insert into basket values('5star',10,5);  

1 row created. (note: to use this all values need to be entered )
Now he just wants to crosscheck the basket so he checked the chocolates in basket similarly we can do this by using the  “SELECT command”

SQL> select * from basket ;
NAME                      PRICE   QUANTITY
-------------------- ---------- ----------
dairy milk                   20          5
5star                        10          5
perk                         15          3

where *  indicates all the column
Now when he  was going to meet his girlfriend ,he met with his friend and he took some chocolates from his basket. Now he just wants to confirm the number of 5star chocolates availbe so he counts the 5star chocolate. We can also do this in database using following command.
SQL> select name,quantity from basket where name='5star';
NAME                   QUANTITY
-------------------- ----------
5star                         5

Finally he meets with his girlfriend and gave basket to her. After going home the girl checks the basket and counts the number of types chocolate given by the guy. In database we use “Aggregate functions” for this .

SQL> select count(name) from basket;

From this tutorial what we learned is
  • Creating a table : create command.
CREATE TABLE table_name
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
  • Inserting values in table : insert command
INSERT INTO table_name
VALUES (value1, value2, value3,...)
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
  • Selecting or viewing the rows : select command
SELECT column_name(s)
FROM table_name
FROM table_name
  • Selecting specific rows on conditions : where clause
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
Operator and their meanings

= Equal
<>  Not equal
Greater than
Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
  • Counting the number of rows: Aggregate function- count
Useful aggregate functions:
  • AVG() - Returns the average value
  • COUNT() - Returns the number of rows
  • FIRST() - Returns the first value
  • LAST() - Returns the last value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
  • SUM() - Returns the sum