SQL - Function
SQL functions are built-in or user-defined routines that accept parameters, perform a specific action or complex calculation on data, and return a single value or a result set (table). They are essential for modular programming, faster execution by caching plans, and reducing network traffic so now we are discuss in details with types.
1.SQL Group BY Clause
Arrange identical data into groups.
Example: -
SELECT max(salary), dep_id from empdata Group by dept_id
Practical Example of students table:
alter table studs add column S_Branch varchar (50);
update studs set S_Branch="Sales" where S_Roll=82000;
select *from studs;
select S_Name, max(S_Marks),S_Branch from studs group by S_Branch;
Output:
2.SQL Group BY Clause
Having with aggregate functions due to its non-performance in the WHERE clause.
Must follow the GROUP BY clause in a query and must also precede the ORDER By clause if used.
Example:
SELECT AVG(S_Marks),S_Branch from Studs Group by S_Branch HAVING count (S_Branch)>=2;
Students Table for Practical in MySQL
SELECT S_Name, avg(S_Marks),S_Branch from studs group by S_Branch having count(S_Branch)>=3;
Can you try check : avg place of [min or max]
Output:
3.SQL ORDER BY Clause
Used to sort output of select statement
Default is to sort in ASC (Ascending)
Can sort in reverse(Descending) Order with “DESC” after the column name
Example:
SELECT *from studs ORDER BY Marks DESC / ASC;
Can you please try DESC;
Output: ASC ORDER:
GERAL CODE:
#use company;
#delete from employee where emp_id in(100);
#create table Employee( Emp_Id int not null, First_Name varchar(30), Last_Name varchar(30), Birth_Date bigint, Sex varchar(10), Salary bigint, Super_Id int, Branch_Id int, primary key(Emp_id));
#insert into employee(Emp_Id,First_Name, Last_Name, Birth_Date, Sex, Salary, Super_Id, Branch_Id)value(100, 'David', 'Wallace','1967','M','250000','100' ,1);
#update employee set Emp_Id= '100' where first_name='Wallace';
#update employee set emp_id='101' where Emp_Id=100;
#into studs(S_Roll,S_Name,S_Marks,S_Fees,S_Branch)value(82075,'Sunny','654','18500','BBA');
#select *from studs;
#desc studs;
#select S_Name, avg(S_Marks),S_Branch from studs group by S_Branch;
#SELECT S_Name, avg(S_Marks),S_Branch from studs group by S_Branch having count(S_Branch)>=1;
#select *from studs Order by s_name asc;
4.SQL UNION and UNION ALL
UNION
Used to combine the result-set-of two or more select statement removing duplicates.
Each SELECT statement within the UNION must have the same number of columns.
Selected within the UNION must have the same order in each SELECT statement.
More than two quires can be clubbed using more than one UNION statement.
It combines the results set from multiple tables and returns distinct records into a single result set.
Example : Create two table Product1 and Product2 (Code)
Product 1 Table
Product2 TableNo2
Union Command:
select category_name from product1
union
select category_name from product2;
Output:
Union All:
Used to combine the results of two SELECT statements including duplicates rows.
The same rules that apply to the UNION clauses will apply to the UNION ALL operators.
It combines the result set from multiple tables and return all records into a single result set
Union Syntax:
select category_name from product1
union all
select category_name from product2;
Output:
0 Comments