Downloading Full Free Courses From Udemy.com at Once

Downloading Videos From Lynda.com within 2 mins

Resolving port conflict in XAMPP Server in Windows

Creating Free Lynda Premier Account in 10 mins

Creating Phpmyadmin and Java connection

Installing XAMPP correctly in Windows

PastPaper Answers For the 1st Year 2nd Semester Final/Repeat/June

               

Year	Module
2011	FCS Final
2012	ST1 Final ST1 Repeat FCS Final FCS Repeat
2013	ST1 Final DB Final FCS Final
2014	ST1 Final ITA Final DB Final FCS Final FCS Repeat
2015	ST1 Final ITA Final ITA June DB Final FCS Final FCS June

Note:Contributor isn't responsible for any mistakes in the answers as these are not actual marking schemes and do not get too attached and depend on given sample answers. Use them with your own risk.©Copyright Reserved

PastPaper Answers For the 1st Year 2nd Semester(Mid)

Year	Module
2005	FCS
2006	FCS
2007	FCS
2008	FCS DB1
2009	FCS ITA ST1 DB1
2010	FCS ITA ST1 DB1
2011	FCS ITA ST1
2012	FCS ITA ST1
2013	FCS ITA

Note:Contributors aren't responsible for any mistakes in the answers as these are not actual marking schemes and do not get too attached and depend on given sample answers. Use them with your own risk.

DBMS Lesson 5 SQL Part 4

This post will be a continuation of the previous post.Today our discussion is about Group By clause and Having clause.අද කතා කරන්නේ පෙර පෝස්ටුවේ ඉතිරි කොටසයි.Group By රීතිය සහ Having රීතිය භාවිතය අද පාඩමෙන් කියා දෙනු ලැබේ.
If you haven't read my previous lessons please follow below links.පෙර පාඩම් මාලාව සදහා පිවිසෙන්න 

Lesson 1,Lesson 2,Lesson 3,Lesson 4 Part 1,Lesson 4 Part 2,Lesson 5 Part 1,Lesson 5 Part 2,Lesson 5 Part 3

Firstly we'll start with Group By clause/පලමුව Group By රීතිය ගැන බලමු.

Syntax:
Select < attributes > From < relations > Where < conditions >Group By < attributes > Order By < attributes > asc/desc

Let's take a look at some examples

These examples are based on the tables given in the Lesson 5 Part 3.So before that go through the tables given earlier.පහත උදාහරණ සියල්ල පෙර පෝස්ටුවේ වගු ආශ්‍රිතව දිග හැරේ.එම නිසා පෙර පෝස්ටුව ප්‍රතමයෙන් අධ්‍යනය කරන්න.

Example 1:
Q: Find the total quantity supplied for each project

A: Select JNo,sum(Qty) from Shipment Group by JNo

Example 2:
Q: Find the minimum quantity supplied by each supplier

A: Select SNo,min(Qty) from Shipment Group by SNo

Example 3:
Q: Display the average quantity supplied for each project in descending order of project number

A: Select JNo,avg(Qty) from Shipment Group byJNo Order by JNo desc

Example 4:
Q: Count the number of parts supplied by each supplier and display in ascending order of supplier number

A: Select SNo,count(Distinct PNo) from Shipment Group by SNo order by SNo asc

Example 5:
Q: Find the total quantity supplied in each part and display the results in descending order of part number.For this query Qty more than 100 will not be valid

A: Select PNo,sum(Qty) from Shipment where Qty<=100 Group by PNo Order by PNo desc


Using Having clause

Syntax:
Select < attribute > From < relations > Where < conditions > Group By < attribute > Having < conditions > Order By < attributes > asc/desc

Example 1:
Q: Find the projects having total quantity supplied more than 150

A: Select JNo from Shipment Group by JNo Having sum(Qty)>150

Example 2:
Q: Find the suppliers who supply at least 2 parts

A: Select SNo from Shipment Group by SNo Having count(Distinct PNo)>=2

Example 3:
Q: Display the project numbers in ascending order where more than 3 suppliers supplying parts

A: Select JNo from Shipment Group by JNo Having count(Distinct PNo)>3 order by JNo asc

Example 4:
Q: Display the part numbers in descending order where average quantity supplied is more than 50.For this query, quantity more than 100 are not valid

A: Select PNo From Shipment Where Qty <100 Group by PNo Having avg(Qty) > 50 Order by PNo desc


Click Here To Download This Lesson in PDF Format



So far we have discuss data retrieving.Next Post will be based on data insertion.ඊලග පෝස්ටුව වගුවකට දත්ත ඇතුලත් කිරීම පිලිබදව කතා කෙරේ.Keep in touch...

DBMS Lesson 5 SQL Part 3

Today we are going to discuss how to order results.අද අපි කතා කරන්නේ කොහොමද ප්‍රතිපලයක් පිලිවෙලකට හදා ගන්නේ කියලා.If you haven't gone through previous lessons click below  links.පෙර පාඩම් මාලාව සදහා පිවිසෙන්න..

Lesson 1,Lesson 2,Lesson 3,Lesson 4 Part 1,Lesson 4 Part 2,Lesson 5 Part 1,Lesson 5 Part 2

Ordering Of Results

Select < attribute > From < relation> [ Where < conditions > ] [ Order by < attribute > asc/desc ]

asc(ascending) - ආරෝහන( කුඩා අගයක සිට විශාල අගයකට ) 
desc(descending) - අවරෝහන( විශාල අගයක සිට කුඩා අගයකට )

Example 1:

Q: Display Supplier details in ascending order of name

A: Select * from Supplier order by Name asc

Example 2:

Q: Display cities of Suppliers in descending order

A:Select Distinct City from Supplier order by City desc

Example 3:

Q: Display name and status of Suppliers those who are in Colombo in descending order of names

A: Select Name,Status from Supplier where City='Colombo' order by Name desc

Example 4:

Q: Display details of suppliers in descending order of status of those who are in Colombo and those status>30

A: Select * from Supplier where City='Colombo' and Status>30 order by Status desc

Example 5:

Q: Display branch names of accounts in ascending order where balance between 2000 and 5000

A: Select BrName from Account where Balance between 2000 and 5000 order by BrName asc

Example 6:

Q: Display account numbers of accounts in Galle branch in descending order

A: Select AccNo from Accounts where BrName='Galle' order by AccNo desc

Example 7:

Q: Display details in Account table where balance in ascending order and account names in descending order

A: Select  * from Accounts order by AccNames desc,Balance asc

Next we'll talk about Aggregate Value Functions

• SUM() - To find the total/එකතු කිරීම් සදහා
• AVG() - To find the average/සාමාන්ය සෙවීම සදහා
• MAX() - To find the maximum value/වැඩිම අගය සෙවීම සදහා
• MIN() - To find the minimum value/අවම අගය සෙවීම සදහා
• COUNT() - To find the number of instances/ප්‍රමානය සෙවීම සදහා
• ROUND() - To round off to nearest number/ආසන්නතම අන්කයට වැටයීම සදහා

Lets see these functions through examples/උදාහරණ මගින් විමසමු....

Example 1:

Q: Count the number of projects

A: Select count(JNo) from Project

Example 2:

Q: Find the average status of suppliers

A: Select avg(Status) from Suppliers

Example 3:

Q: Find the minimum quantity supplied for a project

A: Select min(Qty) from Shipment

Example 4:

Q: Find the average quantity supplied by supplier S3

A: Select avg(Qty) from Shipment where SNo='S3'

Example 5:

Q: Count the number of parts supplied by supplier S2 for different projects

A: Select count(Distinct PNo) from Shipment where SNo='S2'

Example 6:

Q: Find the Suppliers who supplied maximum quantity for a project

A: Select SNo from Shipment where Qty=(Select max(Qty) from Shipment)

Query inside the brackets is called as a Sub Query and it works first before the main query gets executed.වරහන් තුල ඇති function එක subquery එකක් වේ.ප්‍රතමයෙන් එය ක්‍රියාත්මක වන අතර ඉන් පසු main query එක ක්‍රියාත්මක වේ.

Click Here To download This Lesson in PDF Format

Next post will be on Grouping and Having clauses.Keep in touch....

DBMS Lesson 5 SQL Part 2

Today our Lesson is about DML.අද අපි කතාකරන්නේ DML ගැන.If you haven't gone through previous Lessons click below links.

Lesson 1,Lesson 2,Lesson 3,Lesson 4 part 1,Lesson 4 part 2,Lesson 5 part 1

What is DML?? DML කියන්නේ මොකක්ද??
Data Manipulation Language(DML) that provides set of operations to support basic data manipulation from a database.
DB එකක මූලික දත්ත මෙහෙයවීම් කිරීම සදහා ලබා දී ඇති  ක්‍රියාවලීන්

Operations like/ක්‍රියාවලීන්

• Retrieval of data/දත්ත ගැනීම්
• Insertion of data/දත්ත ඇතුලත් කිරීම්
• Modification of data/දත්ත වෙනස් කිරීම්
• Deletion of data in a database/දත්ත ඉවත් කිරීම්

Lets talk about how retrieve data from a created database.දත්ත ගැනීම් ගැන අප දැන් කතා කරමු.

There are several clauses used to do that.
DB එකක දත්ත ගැනීම සදහා clauses භාවිතා වේ.

• Select < attribute > From < relation > [ Where < condition > ]

Above written statement is called a QUERY and words like select,from,where etc are the CLAUSES.

ඉහත ලියා ඇති වගන්තිය Query වන අතර select,from,where  යන ඒවා Clauses වේ.

Let's learn those clauses through examples....

Example 1:

Q:Display Supplier details

Example 2:
Q:Display Cities of Suppliers

Example 3:

Q:Display details of suppliers whose status more than 20

Example 4:
Q:Display number and names of suppliers who live in Jaffna

Example 5:
Q:Display details of Suppliers who live in Colombo or whose status equal to 20

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

A1:Select SNo,Name,Status,City From Supplier    

A2:Select City from Supplier

      or

     Select * From Supplier

Clause like Distinct can be used to remove duplication

A2:Select Distinct City from Supplier

A3:Select * from Supplier Where Status > 20

A4:Select SNo,Name from Supplier Where City='Jaffna'

A5:Select * from Supplier Where City='Colombo' or Status=20

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Answer 1---> 

Answer 2--->

Answer 2 with distinct->

Answer 3--->

Answer 4--->

Answer 5--->

Click here to download Additional Exercises

Note:Answers are given try them and go through the answers provided..

Click Here to download this lesson in PDF Format

Next post will be the continuation of this.Keep in Touch...

DBMS Lesson 5 SQL Part 1

Today I’ll begin a new lesson series related with DB. If you haven’t study the previous lessons click below links.

අද මම අලුත් පාඩම් මාලාවක් ගෙන එනවා DB ආශ්‍රිතව.පරණ පෝස්ටු සදහා පහත ලින්ක් වෙත පිවිසෙන්න.

Lesson 1, Lesson 2, Lesson 3, Lesson 4 part 1, Lesson 4 part2.

Today our topic is about STRUCTURED QUERY LANGUAGE (SQL)
අද අපගේ මාතෘකාව SQL

So what is SQL??මොකක්ද SQL කියන්නේ??

• Database Programming Language(Not case sensitive)
• Design to retrieve and manage data in Relational Database Management System(RDBMS)
• RDBMS තියෙන දත්ත ගැනීමට සහා ඒවා පාලනය කිරීමට SQL හදුන්වා දී ඇත.
• First published by ANSI in 1986 
• ANSI විසින් 1986 දී හදුන්වා දී ඇත.

SQL is mainly consists of 2 Parts. ප්‍රදාන කොටස් දෙකකි.

DDL (Data Definition Language) and DML (Data Manipulation Language).Now SQL supports DCL (Data Control Language) as well.

By this lesson series we talk more about DML and little about DDL. Later on you will get to know about those terms.

මෙම පාඩම් මාලාවෙන් DML ගැන වැඩියෙන් කතා කරන අතර DDL ගැනද කතා කරනු ලැබේ.

In order to create a database you can use several database softwares like;
Database එකක් හදා ගන්න ඕන මුදෘකාන්ග කිහිපයකි.ඒවානම්;

• SQL server
• MySQL
• Oracle
• SQL lite etc..

Before retrieving data from tables (data stored in DB as tables) in database we should know to create a simple database.

DB එකකින් දත්ත ලබා ගැනීමට ප්‍රතම DB එකක් හදා ගැනීමට දැන ගත යුතුය.DB එකක දත්ත වගු ලෙස ගබඩා වේ.එම නිසා අප එම වගු සෑදීමට දැන ගත යුතුය.

Inorder to create a database we use DDL statements.
වගු සෑදීමට DDL රීති භාවිතා වේ.

DDL statements are used to build and modify the structure of your tables and database.When you execute a DDL statement, it takes effect immediately.

DDL රීති වගු සෑදීමට හා  වෙනස් කිරීමට භවිතා කෙරේ.එම රීති වෙනස් කරමින් ක්ශනිකවම ප්‍රතිපලය ලබා ගත හැක.

Common way of creating a table

වගුවක් නිර්මාණය කිරීමට

• create table < table name > ( < attribute name 1> < data type>,< attribute name 2> < data type>,....,< attribute name n> < data type>)

Eg: creating a table named as Customers

create table Customers (C_no char(4),C_name varchar(25),Age int(3),Address varchar(50))

or

CREATE TABLE Customers (C_no CHAR(4),C_name VARCHAR(25),Age INT(3),Address VARCHAR(50))

Both give the same output/දෙකෙන්ම එකම ප්‍රතිපලය ලැබේ.

Click below link to watch How to create a simple database using a online IDE(MySQL)

ලේසි DB එකක් හදාගන්න විදිහ බලන්න පහත Link එක වෙත පිවිසෙන්න

Click below link to watch How to create a simple database using a SQL Server 2008

ලේසි DB එකක් හදාගන්න විදිහ බලන්න පහත Link එක වෙත පිවිසෙන්න

Deleting the created table/වගුවක් මකා දැමීමට

• Drop Table < name >

Eg: Drop table Customers

For permanent deletion of the table/වගුවක් නැවත ලබා ගැනීමට නොහැකි වන සේ මකා දැමීමට

• truncate table < name >

Eg: truncate table Customers

Table Alteration & Modification will be discussed later..
වගු වෙනස් කිරීම් කරන ආකාරය වෙන පෝස්ටුවක් මගින් පසුව විස්තර කරනු ඇත.

Click here to download this lesson in PDF format

Next post will be based on DML......