Mysql Queries
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Mysql Queries as PDF for free.
More details
- Words: 603
- Pages: 5
Nth salary : select sal from emp a where &n=(select count(sal) from emp b where a.sal<=b.sal);
FOR DELETING DUPLICATE ROWS delete from a where rowid not in(select max(rowid) from b where a.column_name=b.column_name;
Max two salaries : select sal from emp a where &n >= (select count ( sal) from emp b where a.sal <= b.sal);
count dulicate vales:
Max or min average salary with depatment ID:
second highest max salary
select deptno,avg(sal) from emp group by deptno having avg(sal)= (select min(avg(sal)) from emp group by deptno); Details of highest salary earner select * from emp where sal = (select max(sal) from emp);
select sal,count(sal) from emp group by sal having count(sal)>1 ;
select max(sal) from emp where sal < (select max(sal) from emp);
Details of lowest salary earner select * from emp where sal = (select min(sal) from emp)
Using Group by
Using Order by
select deptno,sum(sal) from emp group by deptno;
select * from emp order by sal;
DISTINCT
Counts
select distinct(job) from emp;
select job, count(job) from emp group by job;
Table Structure
Creation of table
desc orders
create table orders (sal number,lastname varchar2(10));
Insertion of records
Adding Column to Table
insert into orders values (&sal,'&lastname');
alter table orders add (job varchar2(10) );
To Modify Column Definition
To Drop a Column from a Table
alter table orders modify (job number);
alter table orders drop column job;
To Unused a Column
Using NVL Function (Replaces Null value with given value)
alter table orders set unused (firstname); select empno, ename, sal,nvl (comm, 200) from emp; select ename, sal, nvl(to_char(comm),'commission not payed') from emp;
SELECT ABS(10) FROM DUAL;
SELECT LEAST(4,7,3,5,9,2) FROM DUAL;
ABS(10) ---------10 SELECT GREATEST(4,7,3,5,9,2) FROM DUAL; GREATEST(4,7,3,5,9,2) --------------------9 SELECT TRUNC(1.23456) FROM DUAL;
LEAST(4,7,3,5,9,2) -----------------2 SELECT TRUNC(1.23456,3) FROM DUAL;
TRUNC(1.23456) -------------1
SQRT(9) ---------3
SELECT ROUND(1.23456,3) FROM DUAL;
SELECT ROUND(1.23456) FROM DUAL;
ROUND(1.23456,3) ---------------1.235 SELECT POWER(10,2) FROM DUAL;
TRUNC(1.23456,3) ---------------1.234 SELECT SQRT(9) FROM DUAL;
ROUND(1.23456) -------------1 SELECT MOD(10,3) FROM DUAL;
POWER(10,2) ----------100
MOD(10,3) ---------1
SELECT LOG(100,10) FROM DUAL; LOG(100,10) ----------.5 SQL> SELECT LOG(10,100) FROM DUAL; LOG(10,100) ----------2 SELECT FLOOR(123.456) FROM DUAL;
SELECT LN(10) FROM DUAL; LN(10) ---------2.30258509
FLOOR(123.456) -------------123 SELECT CEIL(123.456) FROM DUAL; CEIL(123.456) ------------124
SELECT ABS(-10) FROM DUAL; ABS(-10) ---------10
To diplay those departments which have more than 5 employees in them we can write. SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 5 ;
SELECT ASCII('A') FROM DUAL; ASCII('A') ---------65 SELECT LTRIM('GEORGE BUSH', 'GEORGE') FROM DUAL; LTRIM ----BUSH SELECT LPAD('BCD',5,'A') FROM DUAL; LPAD( ----AABCD
SELECT * FROM DUAL; DUMMY X SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS',1,2) FROM DUAL; INSTR( -----------------------37 SELECT SUBSTR('ABCDEF',1,3) FROM DUAL; SUB --ABC SELECT LENGTH('AB CD') FROM DUAL; LENGTH('ABCD') -------------5 SELECT INITCAP('ABCDEF') FROM DUAL; INITCA -----Abcdef SELECT UPPER('abcDEfg') FROM DUAL; UPPER(' ------ABCDEFG
SELECT TRANSLATE('JOHN','H','N') FROM DUAL; TRAN ---JONN SELECT RTRIM('TONY BLAIR', 'AIR') FROM DUAL; RTRIM(' ------TONY BL SELECT RPAD('BILL ' , 12 , 'CLINTON') FROM DUAL; RPAD('BILL', -----------BILL CLINTON SELECT LPAD('BCD',4,'A') FROM DUAL; LPAD ---ABCD DESC DUAL Name Null? Type ----------------------------------------- -------DUMMY VARCHAR2(1)
SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS') FROM DUAL; 27 SELECT SUBSTR('ABCDEFG',2,3) FROM DUAL SUB --BCD SELECT LENGTH('ABCD') FROM DUAL; LENGTH('ABCD') -------------4 SELECT LOWER('ABCDEfg') FROM DUAL; LOWER(' ------abcdefg
SELECT USER FROM DUAL;
SELECT SYSDATE FROM DUAL;
USER ------SCOTT
SYSDATE --------1-JAN-05
SELECT TO_DATE('MAR 05 01','MON YY DD') FROM DUAL;
SELECT TO_DATE('01/01/05', 'DD/MM/YY') FROM DUAL;
TO_DATE(' --------01-MAR-05
TO_DATE(' --------01-JAN-05
SELECT NEXT_DAY('01-JAN-05','FRI') FROM DUAL;
SELECT TO_DATE('01 JANUARY 2005','DD MONTH YYYY') FROM DUAL;
NEXT_DAY( (Next Friday after 1-jan-05 is on 7-jan-05) --------07-JAN-05 SELECT MONTHS_BETWEEN('31-DEC05','1-JAN-05') FROM DUAL
TO_DATE(' --------01-JAN-05
MONTHS_BETWEEN('31-DEC-05','1-JAN05') -------------------------------------11.9677419 SELECT ADD_MONTHS('1-JAN-05',5) FROM DUAL;
MONTHS_BETWEEN('31-JUL-05','1-JUL-05') -------------------------------------.967741935
ADD_MONTHS -----------------01-JUN-05
SELECT MONTHS_BETWEEN('31-JUL05','1-JUL-05') FROM DUAL
SELECT LAST_DAY('1-JAN-05') FROM DUAL; LAST_DAY( --------31-JAN-05
FOR DELETING DUPLICATE ROWS delete from
Max two salaries : select sal from emp a where &n >= (select count ( sal) from emp b where a.sal <= b.sal);
count dulicate vales:
Max or min average salary with depatment ID:
second highest max salary
select deptno,avg(sal) from emp group by deptno having avg(sal)= (select min(avg(sal)) from emp group by deptno); Details of highest salary earner select * from emp where sal = (select max(sal) from emp);
select sal,count(sal) from emp group by sal having count(sal)>1 ;
select max(sal) from emp where sal < (select max(sal) from emp);
Details of lowest salary earner select * from emp where sal = (select min(sal) from emp)
Using Group by
Using Order by
select deptno,sum(sal) from emp group by deptno;
select * from emp order by sal;
DISTINCT
Counts
select distinct(job) from emp;
select job, count(job) from emp group by job;
Table Structure
Creation of table
desc orders
create table orders (sal number,lastname varchar2(10));
Insertion of records
Adding Column to Table
insert into orders values (&sal,'&lastname');
alter table orders add (job varchar2(10) );
To Modify Column Definition
To Drop a Column from a Table
alter table orders modify (job number);
alter table orders drop column job;
To Unused a Column
Using NVL Function (Replaces Null value with given value)
alter table orders set unused (firstname); select empno, ename, sal,nvl (comm, 200) from emp; select ename, sal, nvl(to_char(comm),'commission not payed') from emp;
SELECT ABS(10) FROM DUAL;
SELECT LEAST(4,7,3,5,9,2) FROM DUAL;
ABS(10) ---------10 SELECT GREATEST(4,7,3,5,9,2) FROM DUAL; GREATEST(4,7,3,5,9,2) --------------------9 SELECT TRUNC(1.23456) FROM DUAL;
LEAST(4,7,3,5,9,2) -----------------2 SELECT TRUNC(1.23456,3) FROM DUAL;
TRUNC(1.23456) -------------1
SQRT(9) ---------3
SELECT ROUND(1.23456,3) FROM DUAL;
SELECT ROUND(1.23456) FROM DUAL;
ROUND(1.23456,3) ---------------1.235 SELECT POWER(10,2) FROM DUAL;
TRUNC(1.23456,3) ---------------1.234 SELECT SQRT(9) FROM DUAL;
ROUND(1.23456) -------------1 SELECT MOD(10,3) FROM DUAL;
POWER(10,2) ----------100
MOD(10,3) ---------1
SELECT LOG(100,10) FROM DUAL; LOG(100,10) ----------.5 SQL> SELECT LOG(10,100) FROM DUAL; LOG(10,100) ----------2 SELECT FLOOR(123.456) FROM DUAL;
SELECT LN(10) FROM DUAL; LN(10) ---------2.30258509
FLOOR(123.456) -------------123 SELECT CEIL(123.456) FROM DUAL; CEIL(123.456) ------------124
SELECT ABS(-10) FROM DUAL; ABS(-10) ---------10
To diplay those departments which have more than 5 employees in them we can write. SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*) > 5 ;
SELECT ASCII('A') FROM DUAL; ASCII('A') ---------65 SELECT LTRIM('GEORGE BUSH', 'GEORGE') FROM DUAL; LTRIM ----BUSH SELECT LPAD('BCD',5,'A') FROM DUAL; LPAD( ----AABCD
SELECT * FROM DUAL; DUMMY X SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS',1,2) FROM DUAL; INSTR( -----------------------37 SELECT SUBSTR('ABCDEF',1,3) FROM DUAL; SUB --ABC SELECT LENGTH('AB CD') FROM DUAL; LENGTH('ABCD') -------------5 SELECT INITCAP('ABCDEF') FROM DUAL; INITCA -----Abcdef SELECT UPPER('abcDEfg') FROM DUAL; UPPER(' ------ABCDEFG
SELECT TRANSLATE('JOHN','H','N') FROM DUAL; TRAN ---JONN SELECT RTRIM('TONY BLAIR', 'AIR') FROM DUAL; RTRIM(' ------TONY BL SELECT RPAD('BILL ' , 12 , 'CLINTON') FROM DUAL; RPAD('BILL', -----------BILL CLINTON SELECT LPAD('BCD',4,'A') FROM DUAL; LPAD ---ABCD DESC DUAL Name Null? Type ----------------------------------------- -------DUMMY VARCHAR2(1)
SELECT INSTR('TRYING TO KEEP THE THINGS AS SIMPLE AS POSSIBLE','AS') FROM DUAL; 27 SELECT SUBSTR('ABCDEFG',2,3) FROM DUAL SUB --BCD SELECT LENGTH('ABCD') FROM DUAL; LENGTH('ABCD') -------------4 SELECT LOWER('ABCDEfg') FROM DUAL; LOWER(' ------abcdefg
SELECT USER FROM DUAL;
SELECT SYSDATE FROM DUAL;
USER ------SCOTT
SYSDATE --------1-JAN-05
SELECT TO_DATE('MAR 05 01','MON YY DD') FROM DUAL;
SELECT TO_DATE('01/01/05', 'DD/MM/YY') FROM DUAL;
TO_DATE(' --------01-MAR-05
TO_DATE(' --------01-JAN-05
SELECT NEXT_DAY('01-JAN-05','FRI') FROM DUAL;
SELECT TO_DATE('01 JANUARY 2005','DD MONTH YYYY') FROM DUAL;
NEXT_DAY( (Next Friday after 1-jan-05 is on 7-jan-05) --------07-JAN-05 SELECT MONTHS_BETWEEN('31-DEC05','1-JAN-05') FROM DUAL
TO_DATE(' --------01-JAN-05
MONTHS_BETWEEN('31-DEC-05','1-JAN05') -------------------------------------11.9677419 SELECT ADD_MONTHS('1-JAN-05',5) FROM DUAL;
MONTHS_BETWEEN('31-JUL-05','1-JUL-05') -------------------------------------.967741935
ADD_MONTHS -----------------01-JUN-05
SELECT MONTHS_BETWEEN('31-JUL05','1-JUL-05') FROM DUAL
SELECT LAST_DAY('1-JAN-05') FROM DUAL; LAST_DAY( --------31-JAN-05
Related Documents
Mysql Queries
November 2019 21
Queries
April 2020 7
Queries
May 2020 14
Queries
October 2019 20
Queries
November 2019 22
Queries
August 2019 35More Documents from "Shankar Devaramani"
Php Install
November 2019 25
Sturts
November 2019 30
Mysql Queries
November 2019 21
Serials
November 2019 20
Php 4 Reference Card
November 2019 17