SQL: sneak peek

show databases;

use mysql;

show tables;

select * from component;

describe component;

create database sql_intro;

show databases;

use sql_intro;

create table emp_details (Name varchar(25), Age int, gender char(1), doj date, city varchar(15), salary float);

describe emp_details;

insert into emp_details 

values("Jimmy",35,"M","2005-05-30","Chicago",70000),

("Shane",30,"M","1999-06-25","Seattle",55000),

("Marry",28,"F","2009-03-10","Boston",62000),

("Dwayne",37,"M", "2011-07-12","Austin", 57000),

("Sara",32,"F","2017-10-27","New York",72000),

("Ammy",35,"F","2014-12-20","Seattle",80000);

select * from emp_details;

select distinct city from emp_details;

select count(name) as count_name from emp_details;

select avg(salary) from emp_details;

select name, age, city from emp_details;

select * from emp_details where age > 30;

select name, gender from emp_details where gender='F';

select * from emp_details where city = 'Chicago' or city = 'Austin';

select * from emp_details where city in ('Chicago', 'Austin');

select * from emp_details where doj between '2000-01-01' and '2010-12-31';

select * from emp_details where age > 30 and gender = 'M';

select gender, sum(salary) as total_salary from emp_details group by gender;

select * from emp_details order by salary desc;

select (10-20) as subtract;

select length('Bangladesh') as total_len;

select repeat('@',10);

select lower('BANGLADESH');

select curdate();

select day(curdate());

select now();

# String functions

select upper('Bangladesh') as upper_case;

select lower('Bangladesh') as lower_case;

select lcase('BANGLADESH') as lower_case;

select character_length('Bangladesh') as total_len;

select name,char_length(name) as total_len from emp_details;

select concat("Bangladesh"," is"," in Asia") as merged;

select name,age, concat(name,"_",age) as name_age from emp_details;

select reverse('Bangladesh');

select reverse(name) from emp_details;

select replace("Orange is a vegetable","vegetable","fruit");

select length("        Bangladesh           ");

select ltrim("        Bangladesh           ");

select length(ltrim("        Bangladesh           "));

select position("fruit" in "Orange is a fruit") as name;

select ascii('a');

select ascii('4');

# Group by and having examples:

drop table emp_details;

create table employees (Emp_Id int primary key, Emp_name varchar(25), Age int, Gender char(1), Doj date, Dept varchar(20), City varchar(15), Salary float);

describe employees;

insert into employees values 

(101,"Jimmy", 35, "M", "2005-05-30","Sales", "Chicago",70000),

(102,"Shane", 30, "M", "1999-06-25","Marketing", "Seattle",55000),

(103,"Marry", 28, "F", "2009-03-10","Product", "Boston",62000),

(104,"Dwayne", 37, "M", "2011-07-12","Tech", "Austin",57000),

(105,"Sara", 32, "F", "2017-10-27","Sales", "New York",72000),

(106,"Ammy", 35, "F", "2014-12-20","IT", "Seattle",80000),

(107,"Jack", 40, "M", "2012-07-14","Finance", "Houston",10000),

(108, "Angela", 36, "F", "2007-02-04","Tech", "New York",11000),

(109, "Marcus", 25, "M", "2010-07-18","HR", "Boston",90000),

(110, "David", 34, "M", "2009-08-25","Product", "Miami",75000),

(111, "Rose", 28, "F", "2011-02-27","Tech", "Chicago",60000),

(112, "Sophia", 33, "F", "2013-09-21","HR", "Houston",65000),

(113, "Amelia", 30, "F", "2018-10-15","Finance", "Austin",55000),

(114, "Robert", 40, "M", "2015-12-18","Sales", "Detroit",95000),

(115, "William", 36, "M", "2016-04-20","IT", "Chicago",83000),

(116, "John", 32, "M", "2004-08-09","Marketing", "Miami",67000),

(117, "Bella", 29, "F", "2002-06-11","Tech", "Detroit",72000),

(118, "Maya", 25, "F", "2018-10-15","IT", "Houston",48000),

(119, "Alice", 37, "F", "2019-05-28","Product", "Seattle",76000),

(120, "Joseph", 45, "M", "2016-11-23","Sales", "Chicago",115000);

select * from employees;

select distinct dept from employees;

select avg(age) from employees;

# avg age in each department 

select dept, round(avg(age),1) as average_age from employees group by dept;

select dept, sum(salary) as total_salary from employees group by dept;

select count(emp_id), city from employees group by city order by count(emp_id) desc;

select year(doj) as year, count(emp_id) from employees group by year(doj);

# you can use group by to join two or more table togerther.

create table sales (product_id int, sell_price float, quantity int, state varchar(20));

show tables;

select * from sales;

insert into sales values

(121,320.0,3,'California'),

(121,320.0,6,'Texas'),

(121,320.0,4,'Alaska'),

(123,290.0,2,'Texas'),

(123,290.00,7,'California'),

(123,290.00,4,'Washington'),

(121,320.0,7,'Ohio'),

(121,320.0,2,'Arizona'),

(123,290.00,8,'Colorado');