select select Att 1 Att 2 Att 3

select

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 conditions must be satisfied • OR – The 1 st, the 2 nd or both of them must be satisfied

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 * 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 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 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. 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;

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 >=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. – 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 students, college where students. cid=college. cid group by college. cid, cname;

Select From Where Group by Having Order 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 students where sid=12345);