| 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 |
Sunday, July 31, 2016
PastPaper Answers For the 1st Year 2nd Semester(Mid)
Saturday, July 9, 2016
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...
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...
Thursday, July 7, 2016
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
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/ක්‍රියාවලීන්
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 භාවිතා වේ.
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 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
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--->
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 ආශ්‍රිතව.පරණ පෝස්ටු සදහා පහත ලින්ක් වෙත පිවිසෙන්න.
Today our topic is about STRUCTURED QUERY LANGUAGE (SQL)
අද අපගේ මාතෘකාව 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 දී හදුන්වා දී ඇත.
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 එකක් හදා ගන්න ඕන මුදෘකාන්ග කිහිපයකි.ඒවානම්;
Database එකක් හදා ගන්න ඕන මුදෘකාන්ග කිහිපයකි.ඒවානම්;
- SQL server
- MySQL
- Oracle
- SQL lite etc..
DB එකකින් දත්ත ලබා ගැනීමට ප්‍රතම DB එකක් හදා ගැනීමට දැන ගත යුතුය.DB එකක දත්ත වගු ලෙස ගබඩා වේ.එම නිසා අප එම වගු සෑදීමට දැන ගත යුතුය.
Inorder to create a database we use DDL statements.
වගු සෑදීමට DDL රීති භාවිතා වේ.
වගු සෑදීමට 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/දෙකෙන්ම එකම ප්‍රතිපලය ලැබේ.
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..
වගු වෙනස් කිරීම් කරන ආකාරය වෙන පෝස්ටුවක් මගින් පසුව විස්තර කරනු ඇත.
වගු වෙනස් කිරීම් කරන ආකාරය වෙන පෝස්ටුවක් මගින් පසුව විස්තර කරනු ඇත.
Next post will be based on DML......
Subscribe to:
Posts (Atom)