DB_Commands

Select Quries

select * from `testdb`.`employees` order by firstName asc ;

select e.firstName, o.officeCode, o.phone from `testdb`.`employees` as e, `testdb`.`offices` as o where e.firstName='Andy' and e.officeCode=o.officeCode ;

select e.firstName, o.officeCode, o.phone from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode='4' and e.officeCode=o.officeCode ;

select count(e.firstName), o.country from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode=o.officeCode group by o.country;

select count(e.firstName), o.country from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode=o.officeCode group by o.country order by o.country asc;

select e.firstName, o.officeCode, o.phone from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode='4' and e.officeCode=o.officeCode ;

TOP Command: SELECT * FROM `testdb`.`customers` LIMIT 3;

SELECT * FROM `testdb`.`customers` LIMIT 3;

MIN command: SELECT MIN(amount) AS SmallestPrice FROM `testdb`.`payments`;

INNER JOIN:   update `testdb`.`offices` as o  inner join `testdb`.`employees` as e on e.officeCode=o.officeCode and e.firstName='Andy' set o.phone='+65-85001934' ;

SET COmmand: update `testdb`.`offices` set phone='+65-85022222' where officeCode=4;

select e.firstName, o.officeCode, o.phone from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode='4' and e.officeCode=o.officeCode ;

 COUNT GROUP BY Command: select count(e.firstName), o.country from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode=o.officeCode group by o.country;

 COUNT ORDER BY Command: select count(e.firstName), o.country from `testdb`.`employees` as e, `testdb`.`offices` as o where e.officeCode=o.officeCode group by o.country order by o.country asc;

MIN Command:SELECT MIN(amount) AS SmallestPrice FROM `testdb`.`payments`;

MAX Command:  SELECT MAX(amount) AS LargestPrice FROM `testdb`.`payments`;

AVG Command: SELECT AVG(amount) FROM `testdb`.`payments`;

SUM Command: SELECT SUM(orderNumber) FROM `testdb`.`orders`;

IN Command: SELECT * FROM `testdb`.`customers` WHERE country IN ('USA', 'France', 'UK');

NOT IN Command: SELECT * FROM `testdb`.`customers` WHERE country NOT IN ('USA', 'France', 'UK');

BETWEEN: SELECT * FROM `testdb`.`offices` WHERE officeCode BETWEEN 1 AND 4;

NOT BETWEEN: SELECT * FROM `testdb`.`offices` WHERE officeCode  NOT BETWEEN 1 AND 4;

DELETE: DELETE FROM `testdb`.`customers` WHERE customerName='Atelier graphique';

SELECT * FROM `testdb`.`offices` WHERE NOT country='UK';
 

Tags:
   

Get Connected