Lesson 04 (Order & Group Data)

 

ORDER BY CLAUSE (Sort Command)

Order  by clause is used to sort the result by the select statement. ये कमांड query के परिणाम को sort करके प्रदर्शित करती है |


Syntax-

order by column1[ASC|DEC],

Column2[ASC|DEC],......


  • By default ASC (Ascending Order) होता है |


  1. student table में से सभी छात्रों की जानकारी नाम के  ascending order में प्रदर्शित करो |

select * from student order by sname asc;


  • asc दें या नहीं asc ही होगा लेकिन descending में देने के लिए dec दें |


  1. student table में से marks के घटते क्रम में छात्रों की जानकारी प्रदर्शित करो |

select * from student order by marks dec;


  1. student table में से छात्रों के नाम ascending और उनके marks descending order में प्रदर्शित करो |

select * from student order by sname asc, marks dec;


  1. faculty table में से qualification के निम्न क्रम से प्रदर्शित करो - M.Tech, Ph.D, B.Tech.

select * from faculty order by field(qualification, ‘M.Tech’, ‘Ph.D’, ‘B.Tech’);


Group by & Having Clause (Grouping Data)


select sum(marks) from student;

|--------------------|

| sum(marks)       |

|--------------------|

|       -------           |

|--------------------| 


group by clause group a set of rows into set of summary rows by values of columns or expression.

Generally it is used with aggregate function like sum, avg, max, min and count etc.

Syntax-

select  col1, col2, …. aggregate func(col) from tablename where condition group by col1, col2……


Group data with having clause 

Syntax-


select cl1, col2,.......aggregate function(col) from tablename where condition group by col1, col2,..... having condition.


Sample Queries

  1. find total number of student in each branch.

select count(*) from student;

    select branch, count(*) from student group by branch;


  1. Find total marks of each branch.

select branch, sum(marks) from student GROUP BY branch;


  1. Find average marks of each branch having marks branch only CSE or IT.

select branch, sum(marks) from student GROUP BY branch having branch IN(‘CSE’, ‘IT’);


Sub Query, Exist and Not Exist


sub query(nested query)

sub query is nested query within another queries LIKE, SELECT, INSERT, UPDATE OR DELETE.


Syntax-

select col1, col2…..

from tablename

where condition[=] IN|NOT IN……]{NESTED QUERY}


Sample Queries

  1. Find the name of faculty working in IT department.

select fname from faculty where deptid IN(select deptid from department where dname=’IT’);


  1. Find the name of caculty working in IT or CSE department.

select fname from faculty where deptid IN(select deptid from department where dname IN(‘IT’, ‘CSE’));

Comments

Post a Comment

Popular posts from this blog

Lesson 07 (Primary Key & Foreign Key)

  Primary Key/Foreign Key Primary Key Combinagtion of column that uniquely identifies a row in table. Rules for Primary Key Primary key must contain unique value. Primary key can’t be null. There is only one primary key in table. Syntax- Col_name data_type PRIMARY KEY. like roll no in every class. We can define more than one column for primary key. PRIMARY KEY(COL1, COL2,......) PRIMARY KEY in existing table ALTER table(table_name) add PRIMARY KEY(primary key column) use college; create table emp11(empid int PRIMARY KEY, ename varchar(10)); describe emp11; create table emp12 (empid int, ename varchar(10),  → PRIMARY KEY(empid, ename); now you can see by describe command create table emp13 (empid int, ename varchar(10)); alter table emp13 → add PRIMARY KEY(empid); FOREIGN KEY It is a field in a table that matches another field of another table. It is used to maintain referential integrity. A table may have more than one foreign key and each fireign key in the child table may re...
Read more

Lesson 03 (Filter Data)

Filter Data with Where clause- Syntax- select              column1, column2…………… from              table1, table2…………………… where condition Data Filter करने के लिए हमें कुछ Conditional & Logical Operators का प्रयोग करना होता है - Conditional Operator =         Equal             बराबर  != or <>     Not Equal         बराबर नहीं  <         Less Than         बड़ा  >         More Than         छोटा  >=         More than Equal     बड़ा या बराबर  <=         Less than Equal     छोटा या बराबर  Logial Operator  An...
Read more

Lesson 05(Modifying Data)

  Modifying Data  (Insert, Update, Delete) Insert command का प्रयोग table में डाटा भरने के लिए किया जाता है | Syntax- insert into table(column1, column2,........) value(value1, value2….) select * from department; insert into department values (8, ‘ICE’); insert into department values (‘temp’, 6); ----error आ जाएगी | insert into department (dname, deptid) values (‘Temp’, 6) हो जायेगा | you can insert multiple rows insert into department values<enter> → (7, ‘A’), → (8, ‘B’);<enter> Insert with SELECT statement  It is used to insert data into table from another table. insert into table_name select col1, col2, …. from table_name; Update Statement Update statement is used to update existing data in a table . Syntax- update table_name set col1=exp1 col2=exp2 where condition We want to change deptname where deptid=7 in dept_temp. update department_temp → set deptname=’Applide Sc.’, → where deptid=7; select * from dept_temp; update dept_temp → set deptname=’test’; sel...
Read more