select select Att 1 Att 2 Att 3

  • Slides: 16
Download presentation
select

select

select Att 1, Att 2, Att 3, …, Attn from Tablename where (condition) Order

select Att 1, Att 2, Att 3, …, Attn from Tablename where (condition) Order by att 1, att 2 |desc; //Order By (Ascending & Descending) Explanation: – condition will determine the row(s) which will be retrieved from the table. – If you want to select all the attributes from a table you can use all (*) operator.

Logical Operators • NOT – Negation of the condition • AND – The two

Logical Operators • NOT – Negation of the condition • AND – The two conditions must be satisfied • OR – The 1 st, the 2 nd or both of them must be satisfied

Logical Operation • >, >=, <, <=, =, <> • between lowervalue and uppervalue

Logical Operation • >, >=, <, <=, =, <> • between lowervalue and uppervalue – attribute/condition value lays in the closed interval [lowervalue, uppervalue] • in (v 1, v 2, v 3, … , vn) – attribute/condition value must match one value in the arches • Like

select example • select * from college; • select Cid, Cname from college; •

select example • select * from college; • select Cid, Cname from college; • select * from college where cid=10; • select Cid, Cname from college where cid between 10 and 30; • select Cid, Cname from college where cid in (10 , 30);

select from more than one table select T 1. Att 1, T 1. Att

select from more than one table select T 1. Att 1, T 1. Att 2, T 2. Att 1, T 2. Att 2 from table 1 T 1, table 2 T 2 where (condition); • Explanation: – Condition will determine which rows in the table 1 related with table 2

select example select T 1. sid, T 1. Sname, T 2. cname from students

select example select T 1. sid, T 1. Sname, T 2. cname from students T 1, college T 2 where T 1. cid = T 2. cid;

View benefits Create or replace view Std_view as select T 1. sid, T 1.

View benefits Create or replace view Std_view as select T 1. sid, T 1. Sname, T 2. cname from students T 1, college T 2 where T 1. cid = T 2. cid;

Select from dual • Select 1+3, sysdate • from dual;

Select from dual • Select 1+3, sysdate • from dual;

Alias Name Select att 1 as “col-alias”, att 2 as “col-alias”, att 3 as

Alias Name Select att 1 as “col-alias”, att 2 as “col-alias”, att 3 as “colalias” From Table. Name Where (conditon) Order by att 1 | col-alias; Notes: – As is optional. – “ ” bounded the column alias name is optional if your column alias name has no space, or special characters. – if the letter case is important.

Alias Name Select sid as “Student. ID”, Sname “Student Name” From students Where sid

Alias Name Select sid as “Student. ID”, Sname “Student Name” From students Where sid >=20 Order by “Student. ID”; • ----------------------------------Note: – Use can use the alias name with the “order by” phrase. – You can’t use the alias name with the “where” phrase.

Aggregation Functions • The aggregation function return number value and ignore the null values.

Aggregation Functions • The aggregation function return number value and ignore the null values. – Count(att_name) – Sum(att_name) – Max(att_name) – Min(att_name) – Avg(att_name)

example Select count(saverage), sum(saverage)/count(saverage), avg(saverage) from students; ------------ Select college. cid, cname, avg(saverage) from

example Select count(saverage), sum(saverage)/count(saverage), avg(saverage) from students; ------------ Select college. cid, cname, avg(saverage) from students, college where students. cid=college. cid group by college. cid, cname;

Select From Where Group by Having Order by

Select From Where Group by Having Order by

Select college. cid, cname, avg(saverage) from students, college where students. cid=college. cid group by

Select college. cid, cname, avg(saverage) from students, college where students. cid=college. cid group by college. cid, cname having avg(saverage)> 80;

 • select * from students • where • cid = (select cid from

• select * from students • where • cid = (select cid from students where sid=12345);