DML:-Data Manipulation Language DDL:-Data Definition Language
SELECT( display/list/show the records) DESC/DESCRIBE (show structure)
INSERT(add new record) CREATE TABLE (create table)
DELETE (delete/remove the records) DROP TABLE (remove table)
UPDATE(change/increase/decrease the records) DROP-(ADD,MODIFY,DROP,CHANGE)
Select with operators(Arithmetic , Relational ,logical)
SELECT COLUMNS FROM TABLE WHERE ROWS
Arithmetic- + - * / %
i. SELECT SALARY, SALARY*10/100 AS “BONUS” FROM EMPLOYEE;
ii. SELECT SALARY, SALARY+500 AS “INCREMENT” FROM EMPLOYEE;
Relational - > < >= <= <> =
i. SELECT NAME,SALARY FROM EMPLOYEE WHERE SALARY>5000;
ii.SELECT ID,NAME FROM EMPLOYEE WHERE DESIG=”CLERK”;
Logical- and,or
i. SELECT * FROM EMPLOYEE WHERE SALARY>5000 AND DESIG=”CLERK”;
ii. SELECT * FROM EMPLOYEE WHERE DESIG=”CLERK” OR DESIG=”MANAGER”;
Select with Clause
Between and clause- this is used for range concept
i. SELECT * FROM EMPOYEE WHERE SALARY BETWEEN 5000 AND 10000;
ii.SELECT NAME FROM EMPLOYEE WHERE DOJ BETWEEN ‘2000-01-01’ AND ‘2000-12-31’;
iii. SELECT * FROM EMPOYEE WHERE SALARY NOT BETWEEN 5000 AND 10000;
In clause- this is used for list from one field/column only
i. SELECT * FROM EMPOYEE WHERE DESIG IN(‘CLERK’,’MANAGER’);
ii.SELECT NAME FROM EMPLOYEE WHERE ID IN(1,5,8); id=1 or id=5 or id=8
iii. SELECT NAME FROM EMPLOYEE WHERE ID NOT IN(1,5,8);
Null value- IS NULL used for null value search and IS NOT NULL used for except null values
i. SELECT * FROM EMPOYEE WHERE NAME IS NULL;
ii.SELECT NAME FROM EMPLOYEE WHERE NAME IS NOT NULL;
Like – for pattern matching (%) it points to any number of characters),(_) it points to single character.
i. SELECT * FROM EMPOYEE WHERE NAME LIKE ‘%A’;
ii.SELECT NAME FROM EMPLOYEE WHERE NAME LIKE ‘A%’;
iii. SELECT NAME FROM EMPLOYEE WHERE NAME LIKE ‘%A%’;
iv. SELECT NAME FROM EMPLOYEE WHERE NAME NOT LIKE ‘A%’;
DISTINCT – used in column place for display the unique values of any one column.
i. SELECT DISTINCT(DESIG) FROM EMPOYEE;
ORDER BY- It is used after where clause for ascending or descending the records
i. SELECT * FROM EMPOYEE WHERE NAME LIKE ‘%A’ ORDER BY NAME;
ii.SELECT NAME,SALARY FROM EMPLOYEE ORDER BY SALARY DESC;
iii. SELECT * FROM EMPLOYEE ORDER BY DESIG ASC,SALARY DESC;
Select with Single Row Function-
A single row function returns a result for every row of queried table. (String,Numeric & Date)
String Functions:-
Upper/Ucase Converts string/column value in uppercase(one argument)
SELECT UPPER(NAME) FROM EMPLOYEE;
SELECT UCASE(“welcome to india”);
Lower/Lcase Converts string/column value in lowercase (one argument)
SELECT LOWER(NAME) FROM EMPLOYEE;
SELECT LCASE(“WELCOME TO INDIA”);
Ltrim Remove leading/Left space of string/column value (one argument)
SELECT LTRIM(NAME) FROM EMPLOYEE;
SELECT LTRIM(“ welcome to india”);
Rtrim Remove Trailing/Right space of string/column value (one argument)
SELECT RTRIM(NAME) FROM EMPLOYEE;
SELECT RTRIM(“welcome to india ”);
Trim Remove both side space of string/column value (one argument)
SELECT TRIM(NAME) FROM EMPLOYEE;0
SELECT TRIM(“ welcome to india ”)
Select name from employee where TRIM(NAME)=’NEHA’;
‘ NEHA’, ‘NEHA ‘, ‘ NEHA ‘ ,’NEHA’
Length Return size/number of letters of string/column value (one argument)
SELECT Length(NAME),Name FROM EMPLOYEE; Neha Yadav
SELECT Length(“welcome to india”);
SELECT LENGTH(TRIM(“ HELLO”));
SELECT NAME FROM EMPLOYEE WHERE LENGTH(NAME)>5;
Left Return substring from left side of string/column value (two argument)
SELECT LEFT(NAME,3) FROM EMPLOYEE WHERE LEFT(NAME,2)=’Ri’; ritu,Rima
Rit
Rim
SELECT LEFT(“welcome to india”,7); “welcome t“
Right Return substring from right side of string/column value (two argument)
SELECT RIGHT(NAME,5) FROM EMPLOYEE;
SELECT RIGHT(“welcome to india”,5); india
SUBSTR/MID Return substring from whole string/column value (three/two argument)
SELECT SUBSTR(NAME,5,3) FROM EMPLOYEE;
#“welcome to india” print this from main string “come to”
SELECT SUBSTR(“welcome to india”,4,7);
SELECT MID(“welcome to india”,4);
SELECT MID(“welcome to india”,-8,5);
Instr Return position of the substring from the whole string/column value (two argument)
SELECT INSTR(NAME,’A’) FROM EMPLOYEE;
SELECT INSTR(“welcome to india come”,’COME’); 4
SELECT INSTR(“welcome to india”,’Da’); 0
Math functions: It works on numeric data
Pow Return power of the number (two argument)
Select pow(5,2); 25
Select pow(-5,3); -5*-5*-5 -125
Select pow(5,-2); 1/25 0.04 (1/25)
Select pow(5.2,2); 27.04
Sqrt Return square root of the number (one argument)
SELECT SQRT(16); 4
MOD Return remainder of the number (two argument)
Select mod(5,2); 1
Select mod(5.97,2); 1.97
Select mod(-5.8,2); -1.8
Round Return rounding off of the number (two argument)
Select round(128.78); 129
Select round(128.78,0); 129
Select round(128.78,1); 128.8
Select round(128.78,2); 128.78
Select round(128.78,3); 128.780
Select round(128.78,-1); 130
Select round(128.78,-2); 100
Select round(128.78,-3); 0
Select round(128.78,-4); 0
Select round(999.99,-1); 999+1= 1000
989.99,-1 990
Select round(999.99,-2); 9+100 1000 1000
Select round(999.99,-3); 1000
Select round(999.99,-4); 0
Truncate Select truncate(128.78,0); 128
Select truncate(128.78,1); 128.7
Select truncate(128.78,2); 128.78
Select truncate(128.78,3); 128.780
Select truncate(128.78,-1); 120
Select truncate(128.78,-2); 100
Select truncate(128.78,-3); 0
Select truncate(128.78,-4); 0
Select truncate(999.99,-1); 990
Select truncate(999.99,-2); 900
Select truncate(999.99,-3); 0
Select truncate(999.99,-4); 0
Date Functions: It works on date type data
Now Returns current date & time
Select now(); 2021-01-22 17:05:27
Date Return date from Date & Time
Select date(now());
Select date(“2017-01-14 13:45:50”); 2017-01-14
Month Return month number from the date
Select month(“2018-05-07”); 5
Select month(doj) from employee;
Select doj from employee where month(doj)=7;
Monthname Returns month name
Select monthname(“2018-05-07”); May
Select monthname(doj) from employee;
Select name from employee where
monthname(doj)=”March”;
Year Return year number from the date
Select year(“2018-05-07”); 2018
Select year(doj) from employee;
Select name from employee where
year(doj)=2017;
Day Return day number from the date
Select day(“2018-05-07”); 7
Select day(doj) from employee;
Select name from employee where day(doj)=7;
Dayname Returns the day name of the date
Select dayname(“2021-01-22”); Friday
Select dayname(doj) from employee;
Select name from employee where
dayname(doj)=”Monday”;
Dayofweek Returns the weekday index of the argument
select DAYOFWEEK('2010-07-21'); 4
(Sunday is counted as 1)
Select with group/aggregate/multiple row functions
(max,min,avg,sum,count(*),count(distinct),count(field)
These functions works with a group of rows and return single result for that group.
Max Returns maximum value of the argument
Select max(salary) from employee;
Select max(salary) from employee where salary>5000;
Min Returns minimum value of the argument *All group functions
Select min(salary) from employee; Ignores the NULL
values.
Avg Returns average value of the argument
Select avg(salary) from employee; *Count(*) counts the
record not affected by
Sum Returns sum value of the argument NULL values
Select sum(salary) from employee;
Count(*) Returns total number of records
Select count(*) from employee;
Count(distinct) Returns total number of unique values of argument
Select count(distinct desig) from employee;
Count(field) Returns total number of values of argument
Select count(name) from employee;
Select with group by clause:
It combines all those records that have identical values in a particular field or group of fields. It returns
summary result per group.
i. Select desig,count(*) from employee group by design;
ii. Select dept, avg(salary),sum(salary) from employee group by dept;
iii. Select dept,count(*) from employee group by dept having count(*)>2;
(it display the dept and number of records for those dept group whose count more than 2)
iv. Select dept,sum(salary) from employee where salary>5000 group by dept having
sum(salary)>50000;
(this statement includes those salary in each group for sum which is more than 5000 and then
filter that group whose sum of salary of each group is more than 50000.)
Having used for group selection
Where use for record selection in each group
Select with more than one table-
i. Select name,dept_name from employee,department where
employee.deptno=department.deptno;
ii. Select name,dept_name from employee,department where
employee.deptno=department.deptno and name like ‘A%’;
UPDATE COMMAND (DML)
It is used to make changes in the values of a table
i. Update employee set salary=salary+500;
ii. Update employee set salary=salary+500 where salary>7000;
iii. Update employee set name=’neha’,salary=salary+200 where name is null;
DELETE COMMAND(DML)
It is used to remove the records
i. Delete from employee;
ii. Delete from employee where id in(2,7,9);
INSERT COMMAND(DML)
i. Insert into employee values(3,’neha’,’accts’,’clerk’,’2017-01-01’,7800);
ii. Insert into employee (id,name,salary) values(4,’karan’,8000);
DDL(Data Definition Language)
To handle structure of Table
Database Show Databases; Database is the container to store the related tables.
Create database OFFICE;
Drop database OFFICE;
Show Tables;
Create table This command used for CREATE TABLE EMPLOYEE
create new table structure (ID INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
SALARY INT DEFAULT 0);
ALTER This command used for ALTER TABLE ADD DESIGN VARCHAR(20);
TABLE add,modify,drop and change ALTER TABLE MODIFY NAME VARCHAR(30);
name of the column ALTER TABLE DROP DESIGN;
ALTER TABLE CHANGE NAME EMP_NAME VARCHAR(30);
Drop table To remove permanently DROP TABLE EMPLOYEE;
table from the memory
Desc , To show the structure of the DESC EMPLOYEE;
Describe table.
