ACID Properties
- Atomicity -> A transaction should either be successful or fail.
- Consistency -> Data should be consistent ac
- Isolation
- Durability
DDL (Data Definition)(Auto-Commit)
- create
- drop
- alter
- truncate
DML (Data Manipulation)
- insert
- update
- delete
DRL (Data Retrieval)
- select
show databases;
-> Show all databases
use database;
-> Use given database
desc table;
-> Show table structure
show tables;
-> Show all tables in current database
Create Table
Creates a new table.
create table <table_name> (column datatype, column datatyoe(length/size));
- Eg.
create table student (roll int, name varchar(50));
- Eg.
create table emp (eid int, ename varchar(50), city varchar(40), doj date);
insert
Values should match the columns.
insert into <table_name> values(value, value);
- Eg.
insert into student values(1, "Niranjan");
- Eg.
insert into emp values(1, "Peter", "Pune", "2020-01-01");
//(YYYY-MM-DD); - Eg.
insert into emp (col1, col2, col3) values (val1, val2, val3);
- Eg.
insert into emp (roll, name, city, doj) values(1, "Peter", "Pune", "2020-01-01");
//(YYYY-MM-DD);
update
Update existing records
- Single Column ->
update <table_name> set <col_name>=<new_value> where <condition_>;
- Multiple Columns ->
update <table_name> set <col_name>=<new_value>, <col_name>=<new_value> where <condition_>;
- Eg.
update student set roll = 4 where name='Niranjan';
delete
Delete exiting records.
delete from <table_name> where <condition>;
- Eg.
delete from student where roll=1;
delete from <table_name>;
-> Deletes all records from given table.
truncate
Maintain table structure and Delete all data.
truncate table <table_name>;
- Eg.
truncate table emp;
drop
Delete the whole table.
drop table <table_name>;
- Eg.
drop table emp;
alter
alter is used for,
- Adding new columns ->
alter table <table_name> add column <column_name> <data_type>
-
- Eg.
alter table subscriber add column samount long;
- Eg.
- Removing existing columns ->
alter table <table_name> drop column <column_name>;
- Eg.
alter table subscriber drop column extrac;
- Eg.
- Rename tables ->
alter table <old_table_name> rename to <new_table_name>;
- Eg.
alter table subscriber rename to subs;
- Eg.
- Change data type ->
- Change Column Name & Datatype ->
alter table <table_name> change column <old_column> <new_col_name> <new_data_type>;
- Eg.
alter table subscriber change column samount amount double;
- Eg.
- Change Column Name ->
alter table <table_name> change column <old_column> <new_col_name> <old_data_type>;
- Eg.
alter table subscriber change column cid sid int;
- Eg.
- Change Data type ->
alter table <table_name> change column <old_column> <old_column> <new_datatype>;
- Eg.
alter table subscriber change column sid sid varchar(100);
- Eg.
- Change Column Name & Datatype ->
Constraints
- Not Null -> Does not all
null
values.create table student (roll int NOT NULL, name varchar(100));
- Unique -> Does not allow
duplicate
values.create table student (roll int UNIQUE, name varchar(100));
- Primary Key -> Does not allow
null
andduplicate
values.- Single Column ->
create table student (roll int PRIMARY KEY, name varchar(100));
- Multiple Column -> `create table student (roll int, name varchar(100), PRIMARY KEY(roll, name));
- Single Column ->
27 Oct
between .. and
sql select * from emp where eid between 0 and 10;
- Range
like
select * from emp where ename like 'A%';
- Starts with Aselect * from emp where ename like '%A;
- Ends with Aselect * from emp where ename like '%A%';
- Anything that contains Aselect * from emp where ename like '_____';
- '_' - wildcard
order by
select * from emp order by ename asc;
- List records in Ascending Orderselect * from emp order by ename desc;
- List records in Descending Orderselect * from emp order by ename desc, city asc;
- List desc by ename first then by city
limit
select * from emp limit 8;
- List the top 8 recordsselect * from emp limit 8,2;
- List the 2 records after the top 8 records
case
select ename, case when city='Pune' then 1000 when city='Mumbai' then 1500 when city='Delhi' then 2000 else 500 end bonus from emp;
Joins
Inner Join -> Matching in both
select * from emp, dept.dname from emp join dept on emp.did=dept.did;
+------+--------+------------+------------+-------+------+-------+
| eid | ename | city | doj | sal | did | dname |
+------+--------+------------+------------+-------+------+-------+
| 8 | Gaurav | Ahmednagar | 2000-01-01 | 35000 | 10 | IT |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 | RD |
| 10 | Komal | Bengaluru | 1998-01-01 | 65000 | 10 | IT |
| 11 | Xin | China | 1998-01-01 | 65000 | 10 | IT |
+------+--------+------------+------------+-------+------+-------+
4 rows in set (0.001 sec)
Left Join -> Matching from Left
select emp.*, dname from emp left join dept on emp.did = dept.did;
+------+----------+------------+------------+-------+------+-------+
| eid | ename | city | doj | sal | did | dname |
+------+----------+------------+------------+-------+------+-------+
| 8 | Gaurav | Ahmednagar | 2000-01-01 | 35000 | 10 | IT |
| 10 | Komal | Bengaluru | 1998-01-01 | 65000 | 10 | IT |
| 11 | Xin | China | 1998-01-01 | 65000 | 10 | IT |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 | RD |
| 1 | Vivek | Pune | 1999-01-01 | 20000 | NULL | NULL |
| 2 | Abhishek | Mumbai | 2000-01-01 | 30000 | NULL | NULL |
| 3 | Shivam | Delhi | 1996-05-05 | 40000 | NULL | NULL |
| 4 | Neha | Kashmir | 1999-05-01 | 50000 | NULL | NULL |
| 5 | Akshay | Nagpur | 1994-06-03 | 80000 | NULL | NULL |
| 6 | Omkar | Savedi | 2000-07-03 | 20000 | NULL | NULL |
| 7 | Kavita | Shirdi | 1999-01-01 | 35000 | NULL | NULL |
+------+----------+------------+------------+-------+------+-------+
Right Join -> Matching from right
select emp.*, dname from emp right join dept on emp.did = dept.did;
+------+--------+------------+------------+-------+------+-------+
| eid | ename | city | doj | sal | did | dname |
+------+--------+------------+------------+-------+------+-------+
| 8 | Gaurav | Ahmednagar | 2000-01-01 | 35000 | 10 | IT |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 | RD |
| 10 | Komal | Bengaluru | 1998-01-01 | 65000 | 10 | IT |
| 11 | Xin | China | 1998-01-01 | 65000 | 10 | IT |
| NULL | NULL | NULL | NULL | NULL | NULL | DM |
+------+--------+------------+------------+-------+------+-------+
Full Outer Join -> All records
Nov 9
Cross Join
Match every row from both tables.
MariaDB [b16]> select eid, ename, dname from emp cross join dept;
+------+----------+-------+
| eid | ename | dname |
+------+----------+-------+
| 1 | Vivek | IT |
| 1 | Vivek | RD |
| 1 | Vivek | DM |
| 2 | Abhishek | IT |
| 2 | Abhishek | RD |
| 2 | Abhishek | DM |
| 3 | Shivam | IT |
| 3 | Shivam | RD |
| 3 | Shivam | DM |
| 4 | Neha | IT |
| 4 | Neha | RD |
| 4 | Neha | DM |
| 5 | Akshay | IT |
| 5 | Akshay | RD |
| 5 | Akshay | DM |
| 6 | Omkar | IT |
| 6 | Omkar | RD |
| 6 | Omkar | DM |
| 7 | Kavita | IT |
| 7 | Kavita | RD |
| 7 | Kavita | DM |
| 8 | Gaurav | IT |
| 8 | Gaurav | RD |
| 8 | Gaurav | DM |
| 9 | Pooja | IT |
| 9 | Pooja | RD |
| 9 | Pooja | DM |
| 10 | Komal | IT |
| 10 | Komal | RD |
| 10 | Komal | DM |
| 11 | Xin | IT |
| 11 | Xin | RD |
| 11 | Xin | DM |
+------+----------+-------+
33 rows in set (0.006 sec)
Self Join
Join with itself
MariaDB [b16]> select * from emps;
+------+---------+-------+
| eid | ename | mgrid |
+------+---------+-------+
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | Diana | 2 |
| 5 | Eve | 2 |
| 6 | Frank | 3 |
| 7 | Grace | 3 |
| 8 | Hank | 4 |
| 9 | Ivy | 4 |
| 10 | Jack | 5 |
+------+---------+-------+
10 rows in set (0.001 sec)
Output ->
MariaDB [b16]> select e.eid, e.ename , m.ename as Manager from emps e left join emps m on e.mgrid = m.eid;
+------+---------+---------+
| eid | ename | Manager |
+------+---------+---------+
| 2 | Bob | Alice |
| 3 | Charlie | Alice |
| 4 | Diana | Bob |
| 5 | Eve | Bob |
| 6 | Frank | Charlie |
| 7 | Grace | Charlie |
| 8 | Hank | Diana |
| 9 | Ivy | Diana |
| 10 | Jack | Eve |
| 1 | Alice | NULL |
+------+---------+---------+
10 rows in set (0.001 sec)
Functions
- Scaler -> Returns same number of records, n records -> n output
- Group -> Returns a single output, n records -> 1 output
Scaler Functions
upper -> Convert to Upper case
MariaDB [b16]> select ename, upper(ename) from emp;
+----------+--------------+
| ename | upper(ename) |
+----------+--------------+
| Vivek | VIVEK |
| Abhishek | ABHISHEK |
| Shivam | SHIVAM |
| Neha | NEHA |
| Akshay | AKSHAY |
| Omkar | OMKAR |
| Kavita | KAVITA |
| Gaurav | GAURAV |
| Pooja | POOJA |
| Komal | KOMAL |
| Xin | XIN |
+----------+--------------+
MariaDB [b16]> select upper('abcd');
+---------------+
| upper('abcd') |
+---------------+
| ABCD |
+---------------+
MariaDB [b16]> select * from emp where upper(city) = 'Pune';
+------+-------+------+------------+-------+------+
| eid | ename | city | doj | sal | did |
+------+-------+------+------------+-------+------+
| 1 | Vivek | Pune | 1999-01-01 | 20000 | NULL |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 |
+------+-------+------+------------+-------+------+
lower -> Convert to Lower Case
MariaDB [b16]> select ename, lower(ename) from emp;
+----------+--------------+
| ename | lower(ename) |
+----------+--------------+
| Vivek | vivek |
| Abhishek | abhishek |
| Shivam | shivam |
| Neha | neha |
| Akshay | akshay |
| Omkar | omkar |
| Kavita | kavita |
| Gaurav | gaurav |
| Pooja | pooja |
| Komal | komal |
| Xin | xin |
+----------+--------------+
MariaDB [b16]> select lower('ABCD');
+---------------+
| lower('ABCD') |
+---------------+
| abcd |
+---------------+
length -> Counts characters in a string
MariaDB [b16]> select length(' Hello World ');
+--------------------------+
| length(' Hello World ') |
+--------------------------+
| 14 |
+--------------------------+
trim -> Remove space from left and right side of the string
MariaDB [b16]> select length(trim(' Hello World '));
+--------------------------------+
| length(trim(' Hello World ')) |
+--------------------------------+
| 11 |
+--------------------------------+
ltrim -> Remove space from left side of the string
MariaDB [b16]> select length(ltrim(' Hello World '));
+---------------------------------+
| length(ltrim(' Hello World ')) |
+---------------------------------+
| 12 |
+---------------------------------+
1 row in set (0.001 sec)
rtrim -> Remove space from right side of the string
MariaDB [b16]> select length(rtrim(' Hello World '));
+---------------------------------+
| length(rtrim(' Hello World ')) |
+---------------------------------+
| 13 |
+---------------------------------+
repeat -> Repeats the selected records n times
MariaDB [b16]> select repeat(ename,2) from emp;
+------------------+
| repeat(ename,2) |
+------------------+
| VivekVivek |
| AbhishekAbhishek |
| ShivamShivam |
| NehaNeha |
| AkshayAkshay |
| OmkarOmkar |
| KavitaKavita |
| GauravGaurav |
| PoojaPooja |
| KomalKomal |
| XinXin |
+------------------+
reverse -> Reverses the selected column
MariaDB [b16]> select ename, reverse(ename) from emp;
+----------+----------------+
| ename | reverse(ename) |
+----------+----------------+
| Vivek | keviV |
| Abhishek | kehsihbA |
| Shivam | mavihS |
| Neha | aheN |
| Akshay | yahskA |
| Omkar | rakmO |
| Kavita | ativaK |
| Gaurav | varuaG |
| Pooja | ajooP |
| Komal | lamoK |
| Xin | niX |
+----------+----------------+
concat -> Concatenates columns
If one the value is null returns null
MariaDB [b16]> select ename, city, concat(ename,'-',city) from emp;
+----------+------------+------------------------+
| ename | city | concat(ename,'-',city) |
+----------+------------+------------------------+
| Vivek | Pune | Vivek-Pune |
| Abhishek | Mumbai | Abhishek-Mumbai |
| Shivam | Delhi | Shivam-Delhi |
| Neha | Kashmir | Neha-Kashmir |
| Akshay | Nagpur | Akshay-Nagpur |
| Omkar | Savedi | Omkar-Savedi |
| Kavita | Shirdi | Kavita-Shirdi |
| Gaurav | Ahmednagar | Gaurav-Ahmednagar |
| Pooja | Pune | Pooja-Pune |
| Komal | Bengaluru | Komal-Bengaluru |
| Xin | China | Xin-China |
+----------+------------+------------------------+
replace -> replace char/string with given char/string
MariaDB [b16]> select ename, replace(ename, 'a','x') from emp;
+----------+-------------------------+
| ename | replace(ename, 'a','x') |
+----------+-------------------------+
| Vivek | Vivek |
| Abhishek | Abhishek |
| Shivam | Shivxm |
| Neha | Nehx |
| Akshay | Akshxy |
| Omkar | Omkxr |
| Kavita | Kxvitx |
| Gaurav | Gxurxv |
| Pooja | Poojx |
| Komal | Komxl |
| Xin | Xin |
+----------+-------------------------+
MariaDB [b16]> select ename, replace(ename, 'ek','x') from emp;
+----------+--------------------------+
| ename | replace(ename, 'ek','x') |
+----------+--------------------------+
| Vivek | Vivx |
| Abhishek | Abhishx |
| Shivam | Shivam |
| Neha | Neha |
| Akshay | Akshay |
| Omkar | Omkar |
| Kavita | Kavita |
| Gaurav | Gaurav |
| Pooja | Pooja |
| Komal | Komal |
| Xin | Xin |
+----------+--------------------------+
substr -> output the n char after given index (col, index, n )
MariaDB [b16]> select city, substr(city, 1,3) from emp;
+------------+-------------------+
| city | substr(city, 1,3) |
+------------+-------------------+
| Pune | Pun |
| Mumbai | Mum |
| Delhi | Del |
| Kashmir | Kas |
| Nagpur | Nag |
| Savedi | Sav |
| Shirdi | Shi |
| Ahmednagar | Ahm |
| Pune | Pun |
| Bengaluru | Ben |
| China | Chi |
+------------+-------------------+
substring_index -> split using given pattern
MariaDB [b16]> select email, substring_index(email, '@',1) from emp;
+--------------------+-------------------------------+
| email | substring_index(email, '@',1) |
+--------------------+-------------------------------+
| vivek@gmail.com | vivek |
| abhishek@gmail.com | abhishek |
| shivam@gmail.com | shivam |
| neha@gmail.com | neha |
| akshay@gmail.com | akshay |
| omkar@gmail.com | omkar |
| kavita@gmail.com | kavita |
| gaurav@gmail.com | gaurav |
| pooja@gmail.com | pooja |
| komal@gmail.com | komal |
| xin@gmail.com | xin |
+--------------------+-------------------------------+
10 Nov
round -> Rounds up to nearest integer
MariaDB [b16]> select round(190.12323);
+------------------+
| round(190.12323) |
+------------------+
| 190 |
+------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select round(190.92323);
+------------------+
| round(190.92323) |
+------------------+
| 191 |
+------------------+
1 row in set (0.001 sec)
format -> Rounds up and make the value precise
MariaDB [b16]> select format(190.1232, 2);
+---------------------+
| format(190.1232, 2) |
+---------------------+
| 190.12 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select format(190.9232, 2);
+---------------------+
| format(190.9232, 2) |
+---------------------+
| 190.92 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select format(190.9232, 3);
+---------------------+
| format(190.9232, 3) |
+---------------------+
| 190.923 |
+---------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select format(190.9632, 1);
+---------------------+
| format(190.9632, 1) |
+---------------------+
| 191.0 |
+---------------------+
1 row in set (0.001 sec)
coalesce -> Returns the first non-NULL value in a list of arguments
MariaDB [b16]> select *, coalesce(ccity,pcity,'Not Present') as City from cust;
+------+-------+--------+-------------+
| id | ccity | pcity | City |
+------+-------+--------+-------------+
| 1 | Pune | Mumbai | Pune |
| 2 | Delhi | NULL | Delhi |
| 3 | NULL | Surat | Surat |
| 4 | NULL | NULL | Not Present |
+------+-------+--------+-------------+
now-> Current date & time
MariaDB [b16]> select now();
+---------------------+
| now() |
+---------------------+
| 2024-11-10 09:14:33 |
+---------------------+
1 row in set (0.001 sec)
current_date -> Returns Current Date
MariaDB [b16]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-11-10 |
+----------------+
1 row in set (0.001 sec)
current_time -> Return Current Time
MariaDB [b16]> select current_time();
+----------------+
| current_time() |
+----------------+
| 09:21:13 |
+----------------+
year, month, monthname, day, dayname, hour, minute, second
MariaDB [b16]> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2024 |
+-------------+
1 row in set (0.001 sec)
MariaDB [b16]> select month(now());
+--------------+
| month(now()) |
+--------------+
| 11 |
+--------------+
1 row in set (0.001 sec)
MariaDB [b16]> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| November |
+------------------+
MariaDB [b16]> select day(now());
+------------+
| day(now()) |
+------------+
| 10 |
+------------+
1 row in set (0.001 sec)
MariaDB [b16]> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Sunday |
+----------------+
1 row in set (0.001 sec)
MariaDB [b16]> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 9 |
+-------------+
1 row in set (0.001 sec)
MariaDB [b16]> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 31 |
+---------------+
1 row in set (0.000 sec)
MariaDB [b16]> select second(now());
+---------------+
| second(now()) |
+---------------+
| 19 |
+---------------+
1 row in set (0.001 sec)
dateformat(col, format) -> Change format of given col
select date_format(now(), '%Y');
| Specifier | Description | Example Output |
| ------------ | -------------------------------------------------------------------- | ------------------- |
| %Y
| Year as a four-digit number | 2023
|
| %y
| Year as a two-digit number | 23
|
| %m
| Month as a numeric (01-12) | 04
|
| %M
| Month as a full name | April
|
| %b
| Month as a short name | Apr
|
| %d
| Day of the month (01-31) | 05
|
| %H
| Hour (00-23) | 14
|
| %h
or %I
| Hour (01-12) | 02
|
| %i
| Minutes (00-59) | 30
|
| %s
| Seconds (00-59) | 45
|
| %p
| AM or PM | PM
|
| %W
| Weekday name (full) | Wednesday
|
| %w
| Day of the week (0=Sunday, 6=Saturday) | 3
(for Wednesday) |
| %j
| Day of the year (001-366) | 095
|
| %U
| Week number of the year (00-53, Sunday as the first day of the week) | 14
|
| %V
| Week number of the year (01-53, Monday as the first day of the week) | 14
|
| %X
| Year for the week (same as %Y
if the week belongs to that year) | 2023
|
date_add(col, interval n day/month/year) -> Add n days/month/year to the given date
MariaDB [b16]> select date_add(now(), interval 2 day);
+---------------------------------+
| date_add(now(), interval 2 day) |
+---------------------------------+
| 2024-11-12 09:58:19 |
+---------------------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select date_add(now(), interval 2 month);
+-----------------------------------+
| date_add(now(), interval 2 month) |
+-----------------------------------+
| 2025-01-10 09:58:22 |
+-----------------------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select date_add(now(), interval 2 year);
+----------------------------------+
| date_add(now(), interval 2 year) |
+----------------------------------+
| 2026-11-10 09:58:25 |
+----------------------------------+
1 row in set (0.001 sec)
MariaDB [b16]> select date_format(date_add(now(), interval 1 month), '%Y/%M/%D');
+------------------------------------------------------------+
| date_format(date_add(now(), interval 1 month), '%Y/%M/%D') |
+------------------------------------------------------------+
| 2024/December/10th |
+------------------------------------------------------------+
1 row in set (0.001 sec)
sub_date(col, interval n) -> Reduces the given interval
MariaDB [b16]> select date_format(now(), '%Y/%M/%D') as Current, date_format(date_sub(now(), interval 2 day), '%Y/%M/%D') as 'sub_date()';
+--------------------+-------------------+
| Current | sub_date() |
+--------------------+-------------------+
| 2024/November/10th | 2024/November/8th |
+--------------------+-------------------+
1 row in set (0.001 sec)
datediff(current_date, col) -> Returns remaining days from the given date
MariaDB [b16]> select sub_date, current_date(), datediff(now(), sub_date) as Expiry from subscriber;
+---------------------+----------------+--------+
| sub_date | current_date() | Expiry |
+---------------------+----------------+--------+
| 2023-11-10 09:15:00 | 2024-11-10 | 366 |
| 2023-12-05 14:45:00 | 2024-11-10 | 341 |
| 2023-01-10 08:00:00 | 2024-11-10 | 670 |
| 2023-02-15 12:30:00 | 2024-11-10 | 634 |
| 2023-03-20 15:00:00 | 2024-11-10 | 601 |
| 2023-01-15 10:00:00 | 2024-11-10 | 665 |
| 2023-02-20 11:30:00 | 2024-11-10 | 629 |
| 2023-03-10 09:15:00 | 2024-11-10 | 611 |
| 2023-04-05 14:45:00 | 2024-11-10 | 585 |
| 2023-05-15 08:00:00 | 2024-11-10 | 545 |
| 2023-06-20 12:30:00 | 2024-11-10 | 509 |
| 2023-07-10 15:00:00 | 2024-11-10 | 489 |
| 2023-08-05 16:30:00 | 2024-11-10 | 463 |
| 2023-09-15 10:00:00 | 2024-11-10 | 422 |
| 2023-10-20 11:30:00 | 2024-11-10 | 387 |
+---------------------+----------------+--------+
Group Functions
max(col)
MariaDB [b16]> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 80000 |
+----------+
1 row in set (0.001 sec)
min(col)
MariaDB [b16]> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 20000 |
+----------+
1 row in set (0.001 sec)
sum(col)
MariaDB [b16]> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 505000 |
+----------+
1 row in set (0.001 sec)
avg(sal)
MariaDB [b16]> select avg(sal) from emp;
+------------+
| avg(sal) |
+------------+
| 45909.0909 |
+------------+
1 row in set (0.001 sec)
count(col)
- count(col) -> Does not count Null columns
- count(1) -> Counts all records including Null
MariaDB [b16]> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
| 11 |
+------------+
distinct
MariaDB [b16]> select * from emp;
+------+----------+------------+------------+-------+------+--------------------+
| eid | ename | city | doj | sal | did | email |
+------+----------+------------+------------+-------+------+--------------------+
| 1 | Vivek | Pune | 1999-01-01 | 20000 | NULL | vivek@gmail.com |
| 2 | Abhishek | Mumbai | 2000-01-01 | 30000 | NULL | abhishek@gmail.com |
| 3 | Shivam | Delhi | 1996-05-05 | 40000 | NULL | shivam@gmail.com |
| 4 | Neha | Kashmir | 1999-05-01 | 50000 | NULL | neha@gmail.com |
| 5 | Akshay | Nagpur | 1994-06-03 | 80000 | NULL | akshay@gmail.com |
| 6 | Omkar | Savedi | 2000-07-03 | 20000 | NULL | omkar@gmail.com |
| 7 | Kavita | Shirdi | 1999-01-01 | 35000 | NULL | kavita@gmail.com |
| 8 | Gaurav | Ahmednagar | 2000-01-01 | 35000 | 10 | gaurav@gmail.com |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 | pooja@gmail.com |
| 10 | Komal | Bengaluru | 1998-01-01 | 65000 | 10 | komal@gmail.com |
| 11 | Xin | China | 1998-01-01 | 65000 | 10 | xin@gmail.com |
| 1 | Vivek | Pune | 1999-01-01 | 20000 | NULL | vivek@gmail.com |
| 2 | Abhishek | Mumbai | 2000-01-01 | 30000 | NULL | abhishek@gmail.com |
| 3 | Shivam | Delhi | 1996-05-05 | 40000 | NULL | shivam@gmail.com |
| 4 | Neha | Kashmir | 1999-05-01 | 50000 | NULL | neha@gmail.com |
| 5 | Akshay | Nagpur | 1994-06-03 | 80000 | NULL | akshay@gmail.com |
| 6 | Omkar | Savedi | 2000-07-03 | 20000 | NULL | omkar@gmail.com |
| 7 | Kavita | Shirdi | 1999-01-01 | 35000 | NULL | kavita@gmail.com |
| 8 | Gaurav | Ahmednagar | 2000-01-01 | 35000 | 10 | gaurav@gmail.com |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 | pooja@gmail.com |
| 10 | Komal | Bengaluru | 1998-01-01 | 65000 | 10 | komal@gmail.com |
| 11 | Xin | China | 1998-01-01 | 65000 | 10 | xin@gmail.com |
+------+----------+------------+------------+-------+------+--------------------+
22 rows in set (0.001 sec)
MariaDB [b16]> select distinct * from emp;
+------+----------+------------+------------+-------+------+--------------------+
| eid | ename | city | doj | sal | did | email |
+------+----------+------------+------------+-------+------+--------------------+
| 1 | Vivek | Pune | 1999-01-01 | 20000 | NULL | vivek@gmail.com |
| 2 | Abhishek | Mumbai | 2000-01-01 | 30000 | NULL | abhishek@gmail.com |
| 3 | Shivam | Delhi | 1996-05-05 | 40000 | NULL | shivam@gmail.com |
| 4 | Neha | Kashmir | 1999-05-01 | 50000 | NULL | neha@gmail.com |
| 5 | Akshay | Nagpur | 1994-06-03 | 80000 | NULL | akshay@gmail.com |
| 6 | Omkar | Savedi | 2000-07-03 | 20000 | NULL | omkar@gmail.com |
| 7 | Kavita | Shirdi | 1999-01-01 | 35000 | NULL | kavita@gmail.com |
| 8 | Gaurav | Ahmednagar | 2000-01-01 | 35000 | 10 | gaurav@gmail.com |
| 9 | Pooja | Pune | 1998-01-01 | 65000 | 20 | pooja@gmail.com |
| 10 | Komal | Bengaluru | 1998-01-01 | 65000 | 10 | komal@gmail.com |
| 11 | Xin | China | 1998-01-01 | 65000 | 10 | xin@gmail.com |
+------+----------+------------+------------+-------+------+--------------------+
11 rows in set (0.001 sec)
MariaDB [b16]> select distinct sal from emp;
+-------+
| sal |
+-------+
| 20000 |
| 30000 |
| 40000 |
| 50000 |
| 80000 |
| 35000 |
| 65000 |
+-------+
7 rows in set (0.001 sec)
16 Nov
Group By
Used to perform group functions on group of records based on given column
MariaDB [b16]> select did, avg(sal) from emp group by did;
+------+------------+
| did | avg(sal) |
+------+------------+
| NULL | 39285.7143 |
| 10 | 55000.0000 |
| 20 | 65000.0000 |
| 30 | 10000.0000 |
+------+------------+
4 rows in set (0.001 sec)
Having
Use to filter output of Group By
MariaDB [b16]> select did, sum(sal)from emp group by did having sum(sal) > 50000;
+------+----------+
| did | sum(sal) |
+------+----------+
| 10 | 330000 |
| 20 | 130000 |
| 38 | 115000 |
| 92 | 80000 |
| 97 | 70000 |
+------+----------+
5 rows in set (0.001 sec)