Saturday, December 3, 2016

A/L ICT e Learning Now Available

visit http://ict.shilpa64.lk E Learning of ICT is easy.



Friday, August 19, 2016

SQL Joins

SQL Joins are used to relate information in different tables.

A Join query retrieves rows from two or more tables.

A SQL Join condition is used in the SQL 
WHERE Clause of select, update, delete statements.


 Joins in SQL

The SQL Syntax for joining two tables is:
SELECT col1, col2, col3...
FROM table_name1, table_name2 
WHERE table_name1.col2 = table_name2.col1; 
If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Joins Example

Lets use the below two tables to explain the sql join conditions.
Database table "product";
product_idproduct_namesupplier_nameunit_price
100CameraNikon300
101TelevisionOnida100
102RefrigeratorVediocon150
103IpodApple75
104MobileNokia50
Database table "order_items";
order_idproduct_idtotal_unitscustomer
510010430Infosys
51011025Satyam
510210325Wipro
510310110TCS
SQL Joins can be classified into Equi join and Non Equi join.
1) SQL Equi joins
It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.
For example: You can get the information about a customer who purchased a product and the quantity of product.
2) SQL Non equi joins
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=
1) SQL Equi Joins:
An equi-join is further classified into two categories:
a) SQL Inner Join
b) SQL Outer Join 

a) SQL Inner Join:

All the rows returned by the sql query satisfy the sql join condition specified.

SQL Inner Join Example:

If you want to display the product information for each order the query will be as given below. Since you are retrieving the data from two tables, you need to identify the common column between these two tables, which is the product_id.
The query for this type of sql joins would be like,
SELECT order_id, product_name, unit_price, supplier_name, total_units 
FROM product, order_items 
WHERE order_items.product_id = product.product_id; 
The columns must be referenced by the table name in the join condition, because product_id is a column in both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT statement.
The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number of tables involved. The rule must be true to avoid Cartesian product.
We can also use aliases to reference the column name, then the above query would be like,
SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units 
FROM product p, order_items o 
WHERE o.product_id = p.product_id; 

b) SQL Outer Join:

This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only.
The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join".
If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below:
SELECT p.product_id, p.product_name, o.order_id, o.total_units 
FROM order_items o, product p 
WHERE o.product_id (+) = p.product_id; 
The output would be like,
product_idproduct_nameorder_idtotal_units
----------------------------------------------------
100Camera
101Television510310
102Refrigerator51015
103Ipod510225
104Mobile510030
NOTE: If the (+) operator is used in the left side of the join condition it is equivalent to left outer join. If used on the right side of the join condition it is equivalent to right outer join.

SQL Self Join:

A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
The below query is an example of a self join,
SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM sales_person a, sales_person b 
WHERE a.manager_id = b.sales_person_id;

2) SQL Non Equi Join:

A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, >

SQL Non Equi Join Example:

If you want to find the names of students who are not studying either Economics, the sql query would be like, (lets use student_details table defined earlier.)
SELECT first_name, last_name, subject 
FROM student_details 
WHERE subject != 'Economics' 
The output would be something like,
first_namelast_namesubject
---------------------------------------
AnajaliBhagwatMaths
ShekarGowdaMaths
RahulSharmaScience
StephenFlemingScience

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key, Not Null, Unique, Check.
Constraints can be defined in two ways 
1) The constraints can be specified immediately after the column definition. This is called column-level definition.

2) The constraints can be specified after all the columns are defined. This is called table-level definition. 


1) Primary key:

Defines a column or combination of columns which uniquely identifies each row in the table.
Syntax to define a Primary key at column level:
column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Syntax to define a Primary key at table level:
[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)
  • column_name1, column_name2 are the names of the columns which define the primary Key.
  • The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.
For Example: To create an employee table with Primary Key constraint, the query would be like.
Primary Key at column level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
);
or
CREATE TABLE employee
( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);
Primary Key at column level:
CREATE TABLE employee 
( id number(5), 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);
Primary Key at table level:
CREATE TABLE employee 
( id number(5), NOT NULL,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)
);

2) Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.
Syntax to define a Foreign key at column level:
[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)
Syntax to define a Foreign key at table level:
[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
For Example:
1) Lets use the "product" table and "order_items". 

Foreign Key at column level:
CREATE TABLE product 
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, 
product_name char(20),
supplier_name char(20),
unit_price number(10)
);
CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);
Foreign Key at table level:
CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);
2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10) 
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.
Syntax to define a Not Null constraint:
[CONSTRAINT constraint name] NOT NULL
For Example: To create a employee table with Null value, the query would be like
CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10) 
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.
Syntax to define a Unique key at column level:
[CONSTRAINT constraint_name] UNIQUE
Syntax to define a Unique key at table level:
[CONSTRAINT constraint_name] UNIQUE(column_name)
For Example: To create an employee table with Unique key, the query would be like,
Unique Key at column level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE 
);
or
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE 
);
Unique Key at table level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location) 
);

5)  Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.
Syntax to define a Check constraint:
[CONSTRAINT constraint_name] CHECK (condition)
For Example: In the employee table to select the gender of a person, the query would be like
Check Constraint at column level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1) CHECK (gender in ('M','F')), 
salary number(10), 
location char(10) 
); 
Check Constraint at table level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1), 
salary number(10), 
location char(10), 
CONSTRAINT gender_ck CHECK (gender in ('M','F')) 
); 

Sunday, July 10, 2016

ශිල්ප සයුර A/L ICT e Learning ව්‍යාපෘතිය සාර්ථකයි!

A/L ICT e Learning ව්‍යාපෘතියට අදහස වැටුනේ 2012 ජනවාරියේ. Chemistry කර කර හිටපු අපේ දුව හදිසියේම ICT කරන්න තීරණය කරලා මට ICT  උගන්වන්න කිව්වා. මාස 7 ක් විභාගයට තියෙද්දි අවුරුදු 2ක විශය නිර්දේශයක් උගන්වන්න මම තෝරාගත්තේ e Learning. තාක්‍ෂණය දැන ගත්තට උසස් පෙළ ගුරුවරයෙක් ලෙස වැඩකර පුරුද්දක් නොතිබූණ නිසා හැම පාඩමක්ම සරළව හදලා online share කළා. 



නරක නැහැ. එයා B එකක් ගත්තා.  ටියුෂන් කරන අදහසක් මට නොතිබුණත් අපේ දුවට වගේ ප්‍රශ්න තිබුණ අය ගණනකටම ඒ පාඩම් උදව් වුනා. 2015 AppSMART ව්‍යාපෘතියේදී උසස් පෙළ ICT e Learning සඳහා app එකක් හදන්න යෝජනා කලෙත් අපේ දුවමයි. 2015 ඉඳලා වසරකට වඩා වැඩ කරලා සම්පූර්ණ විශය නිර්දේශය ආවරණය වෙන විදියට A/L ICT e Learning app එක හැදුවා. අන්තිම හරියට එනකොට එපාත් උනා, මොකද උසස් පෙළ ICT විෂය නිර්දේශයේ වපසරිය හරිම වැඩියි. වසර 2කදී ප්‍රායෝගික දැනුමත් එක්කඑය උගන්වන්න ගුරුවරු විශාල මහන්සියක් වෙන්න ඕනේ.

සරළ ඉංග්‍රීසියෙන් සාදන ලද මේ ඉසවි අධ්‍යන පද්ධතිය සිංහල බසින් නිමවීම පිළබඳව සිතුවත් බොහෝ පාසල් සිසුන් ඉංග්‍රීසි භාෂාවෙන් ICT ඉගන ගත්තේ නැතිනම් ඔවුන්ට උසස් පෙළින් පසුව ICT කරන්න අපහසු වෙනවා. සියලූම උපාධි ඉංග්‍රීසියෙන් තියෙන්නේ. මංහසුරුවක් හෝ උපලැකියානක් ගැන සම්මුඛ පරීක්‍ෂණ වලදී අහන්නේ නෑ.  ඒ එක්කම සිංහල ICT පාරිභාෂුක වචන මාලාව පිළිබඳ නියම එකඟතාවයක් නොමැති වීමත් තාක්‍ෂණික ප්‍රශ්නයක්. දැනට මෙය භාවිතා කරන සිසුන්ට සරළ ඉංග්‍රීසි ගැටළුවක් උනේ නෑ.  ඔවුන් A/L ICT ඉගනුම් උපකරණයක්  මෙය භාවිතා කරනවා.


මෙය නිර්මාණය කිරීමේදී වැඩියෙන්ම සිත යොමුකලේ Smart Phone එකෙන් ඉගනීම කරන්නට පහසුකම් සපයන්න. සියලුම පාඩම් mobile එකෙන් බලන්න පුළුවන්. Mobile Learning තුලින් සිසුන්ට ඕනෑම තැනක සිට ඕනෑම වෙලාවක A/L ICT ඉගන ගන්න පුළුවන්.

සිසුන්ට  පියවරෙන් පියවර තමන්ගේ කාලය අනුව සම්පුර්ණ විශය  නිර්දේෂයම ඉගනීම කරන්නට පුළුවන් විදියටයි මෙය නිර්මාණය කළේ. රණබිම යසස්, කිංස්වුඩ් චිරාත් හා හර්ෂණ, ආනන්දේ රවිඳු, නාලන්දේ කවිඳු  A/L ICT e Learning කරලා පද්ධතිය Test කලාට පස්සේ තමා හැමෝටම එය ලබාදෙන්න තීරණය කළේ. මේ සඳහා භාවිතා කරන server එකත්, එය නඩත්තු කිරීමට යන වියදමත් ,  අනිකුත් මානව සහ භෞතික සම්පත් විදයමුත් සළකා වසරකට රැ. 6600/= කට එය ලබාදෙන්න තීරණය කළා. ඉතින් ශිල්ප සයුර වගේ තවත් වැඩක් සාර්ථක ලෙස අවසාන කළා. සතුටුයි!