Assignment 5
Objectives :To query the tables using simple form of select statement Select from table [where order by ] Select from table [where group by <> having <> order by <>]
SELECT Statement:-
SELECT <column name> FROM <table name>;
The select statement : Used to read a tuple, tuples, parts of a tuple from a relation in the database. Tuple means a record in an RDB & a relation means a table.
The basic structure of a Select statement consists of 3 clauses: The select clause corresponds to the projection operation in relational algebra. It is used to list the attributes desired in the query. The from clause corresponds to the Cartesian product operation of RA. It lists the relations to be scanned in the evaluation of the expression.
The where clause corresponds to the selection operation of RA. It consists of a predicate involving the attributes of the relations that appear in the select clause.
The other clauses are Order by clause causes the result of the query to appear in a sorted order. Group by clause used to form groups of tuples , of the result . It is used when using aggregate functions.
Syntax: select <attribute-list>
from <relation-list>
[where <condition>
[group by <attribute list>
[having <condition> ]
[order by <attribute list>]]];
An SQL aggregate functions performs an operation on a group of rows and returns a single result. You may want retrieve group of item-prices and return total- price. This type of scenario is where you would use a aggregate functions.
The following is the summary of some SQL group function .
1 Sum():Gets the sumor total of the values of the specified attribute.
Syntax: Sum(attribute-name)
2 Count():Gives the count of members in the group.
Syntax: Count(attribute); Count(*);
3 Max():Gives the maximum value for an attribute, from a group of members.
Syntax: Max(attribute);
4 Min():Gives the minimum value for an attribute, from a group of members.
Syntax: Min(attribute);
5 Avg():Gives the average value for an attribute, from a group of members.
Syntax: Avg(attribute)
Set A
Consider the relations Person (pnumber, pname, birthdate, income), Area( aname,area_type). An area can have one or more person living in it , but a person belongs to exactly one area. The attribute ‘area_type’ can have values as either urban or rural.
Create the Relations accordingly, so that the relationship is handled properly and the relations are in normalized form (3NF). Assume appropriate data types for all the attributes. Add any new attributes as required, depending on the queries. Insert sufficient number of records in the relations / tables with appropriate values as suggested by some of the queries. Write select queries for following business tasks and execute them.
Queries:-
create table Area (
aname varchar(20) primary key,
area_type varchar check( area_type in('rural','urban')) );
create table Person(
pnumber int Primary key,
pname varchar(20),
birthdate date,
income numeric(7,2),
aname varchar(20),
constraint fk foreign key(aname) references Area(aname) on delete cascade on update cascade );
insert into Area values('Pune','urban');
insert into Area values('Satara','urban');
insert into Area values('Mumbai','urban');
insert into Area values('Washim','rural');
insert into Area values('Khed','rural');
insert into Person values(101,'Raj Hawaldar','08/24/1995',50000.00,'Pune');
insert into Person values(102,'Sanket Dewale','07/12/1996',20000.00,'Khed');
insert into Person values(103,'Mandar Athavle','05/02/1996',30000.00,'Washim');
insert into Person values(104,'Nilesh Bora','08/20/1995',40000,'Mumbai');
insert into Person values(105,'Prathmesh Gurav','08/12/1995',10000,'Satara');
insert into Person values(106,'Ram Sultan','05/02/1993',30000,'Pune');
insert into Person values(107,'Kiran Shinde','1/08/1994',40000,'Mumbai');
insert into Person values(108,'Swapnil Gore','2/05/1994',10000,'Satara');
insert into Person values(109,'Sunil Koli','7/04/1994',10000,'Satara');
select * from area;
aname | area_type
--------+-----------
Pune | urban
Satara | urban
Mumbai | urban
Washim | rural
Khed | rural
(5 rows)
select * from person;
pnumber | pname | birthdate | income | aname
---------+-----------------+------------+----------+--------
102 | Sanket Dewale | 1996-12-07 | 20000.00 | Khed
103 | Mandar Athavle | 1996-02-05 | 30000.00 | Washim
105 | Prathmesh Gurav | 1995-12-08 | 10000.00 | Satara
106 | Ram Sultan | 1993-02-05 | 30000.00 | Pune
107 | Kiran Shinde | 1994-08-01 | 40000.00 | Mumbai
108 | Swapnil Gore | 1994-05-02 | 10000.00 | Satara
109 | Sunil Koli | 1994-04-07 | 10000.00 | Satara
(7 rows)
1. List the names of all people living in ‘ pune ’ area.
Query:- Select * from Person where aname='Pune';
2. List details of all people whose names start with the alphabet ‘_’ & contains maximum _ alphabets in it.
Query:- Select pname,length(pname) from Person where pname like'S%' AND length(pname) >5 group by pname;
3. List the names of all people whose birthday falls in the month of .
Query:- select pname,birthdate from Person where extract (Month from birthdate)=08;
4. Give the count of people who are born on ‘ ’
Query:- Select pname,count(*) from person where extract (year from birthdate)=1994 group by pname;
or
Select count(*) from person where extract (year from birthdate)=1994;
5. Give the count of people whose income is below
Query:- Select pname,income from person where income<40000;
or
Select count(pname) from person where income<40000;
6. List names of all people whose income is between and _;
Query:-Select pname,income from person where income between 10000 and 30000;
7. List the names of people with average income
Query:-Select pname,avg(income) from person group by pname;
8. List the sum of incomes of people living in ‘ ’
Query:- select aname,sum(income) from person group by aname;//all area
//select aname,sum(income) from person group by aname having aname='Pune';//particular area
9. List the names of the areas having people with maximum income (duplicate areas must be omitted in the result)
Query:-select max(income),aname from person group by aname;
//or
select aname from area where aname= (select aname from person where income= (select max(income) from person));
10. Give the count of people in each area
Query:- Select aname,count(*) from person group by aname;
11. List the details of people living in ‘ ’ and having income greater than _________ ;
Query:- Select pname,income from person where aname='Pune' and income>10000;
12. List the details pf people, sorted by person number
Query:- Select * from person order by pnumber ;
or Select * from person order by pnumber desc ;// for descending order
13. List the details of people, sorted by area, person name
Query:- Select * from person order by aname,pname;
14. List the minimum income of people.
Query:- Select min(income) from person;
15. Transfer all people living in ‘pune’ to ‘mumbai’.
Query:-Update person set aname='Mumbai' where aname='Pune';
16. delete information of all people staying in ‘urban’ area
Query:- delete from person where aname IN (select aname from area where area_type ='urban');
No comments:
Post a Comment