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;
COUNT(NAME)
-----------
3
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
Or
SELECT *
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
Operator |
Description |
| = |
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
