query 1
select sw_id, sw_name from s_water where sw_quality::text::integer >= 5; //converting enum
type into integer (jer text typeshi da mere integershi)
to see data (only 2 rows)
filling up with zeros(in the beginning) when numbers are missing
select to_char doesn't change anything it just shows users how the postalcodes are like
read data from sportsclub database
(selection is not through yet)
----> select course_id, course_name, targetgroup, area from course where area='fitness' and
(targetgroup='kid' or targetgrou
p ='fam');
select people born before 2000
to count the number of members:
male of female?
isTrainer?
roca ginda to Return number and name of the fitness courses that are available for targetgroups children (kid) or families (fam),
query iqneba:
select count(*) as "Number of courses", course_name
from course
where area ='fitness' and
(targetgroup ='kid' or targetgroup ='fam')
group by course_name;
oldest member- min from their birthday dates -----> select min(birthday) from member;
oldest male member: --> select min(birthday) from member where gender = 'm';
name and birthday date of the oldest member: ---> select memname, min(birthday) from
member (throws an error, computed value is not evaluated as a condition)
select memname, birthday from member where birthday = min(birthday); (still an error-
aggregate functions aren't allowed there)
select memname, birthday from member where birthday = (select min(birthday) from member);
(yep)
first subselect is conducted, finds that oldest guys and then returns its name
oldest male member-name and birthday date --->
select memname, birthday from member
where birthday =
(select min(birthday) from member where gender = 'm');
group both eldest members
group both eldest members (name+date) --->
select memname, birthday from member
where birthday IN
(select min(birthday) from member group by gender);
group by at first the condition is trainer with three courses
second one _ with more than 3 courses --->
select count(), trainer
from course
group by trainer
having count() >= 3;
more than 4 courses ---group by names and number of courses -->
professors and contact hours
same but ordered by prof-id
grouping by more than one columns:
SELECT prof_id, contact_hours, count(contact_hours) as "Number"
FROM course
group by prof_id, contact_hours
order by prof_id;