MySql

Connecting to MySql
mysql.exe location: c:\xampp\mysql\bin\mysql.exe
host: localhost
username: root
password: ***
-> modify your host location, username, password accordingly.
-> i am using dos prompt as client. you can use phpmyadmin or anything else you like.
-> just copy & paste the bellow code in ms-dos prompt
c:\xampp\mysql\bin\mysql -hlocalhost -uroot -p

---

Connect MySQL Database
CREATE DATABASE sample;

---

Creating Table
CREATE TABLE person ( ID int(11) default NULL, Name varchar(100) default NULL, Age int(11) default NULL );

---

Inserting Rows
INSERT INTO person(ID ,Name,Age) VALUES (1 , 'Sivaram', 22), (2 , 'Prasad', 23), (3 , 'Ramesh', 28), (4 , 'Kiran',28 ), (5 , 'Santhosh', 22));

---

Selecting Data From Table
SELECT * FROM person;
SELECT * FROM person WHERE Name LIKE '%ira%'; -> Select using Like
SELECT * FROM test ORDER BY Name ASC;
->Selecting by Order

---

Updating Data in Table
UPDATE person SET Age=22;

---

Delete Data from Table
DELETE FROM person;
DELETE FROM person WHERE Name='sivaram';

---

Drop Table
DROP TABLE person;

---

Drop Database
DROP Database sample;

---

EMP-DEPT Example

CREATE TABLE `dept` (
`deptno` tinyint(4) NOT NULL,
`dname` varchar(10) NOT NULL,
`loc` varchar(10) NOT NULL, PRIMARY KEY (`deptno`)
) ENGINE=InnoDB ;



CREATE TABLE `emp` (
`empno` int( 4 ) NOT NULL ,
`ename` varchar( 20 ) NOT NULL ,
`deptno` tinyint( 4 ) NOT NULL ,
`hiredate` date NOT NULL ,
`sal` int( 5 ) default ’0',
`bonus` int(3) NULL,
PRIMARY KEY ( `empno` ) ,
FOREIGN KEY ( deptno ) REFERENCES dept( deptno )

) ENGINE = InnoDB ;


Queries:

Display the employee details from the emp table.

SELECT * FROM EMP;

Display the employee name and salary of all the employees in department no 10.

SELECT ename, sal FROM EMP WHERE deptno=10;

Display employee name, department number and salary of all the employees who are working for department no 20 and drawing salary in excess of 2500.

SELECT ename, deptno, sal FROM emp WHERE deptno=20 AND sal>2500;

Display employee details of all the employees who are drawing salary in the range 2000 and 3000.

SELECT * FROM emp WHERE sal BETWEEN 2000 AND 3000;

Display employee details of all the employees who are either working in department no 10, 20 or 30.

SELECT * FROM emp WHERE deptno IN (10, 20, 30);

Display emplyoyee details whose names are starting with ‘A’.

SELECT * FROM emp WHERE ename LIKE ‘A%’;

Display the employee details whose names are having a ‘S’ somewhere in them.

SELECT * FROM emp WHERE ename LIKE ‘%S%’;

Display the employee details for all the employees whose names have an ‘A’ in the second place.

SELECT * FROM emp WHERE ename LIKE ‘-A%’;

Display the employee name and salary of the employees in the descending order of their salaries.

SELECT ename, sal FROM emp ORDER BY sal DESC;

Display the number of employees in each department for the employee table.

SELECT COUNT(*) AS empcount FROM emp GROUP BY deptno;

Display the average salary, maximum salary and minimum salary for the table emp.

SELECT AVG(sal) AS avgsal, MIN(sal) AS minsal, MAX(sal) AS maxsal

FROM emp;

Display the department number and average salary of employees in each department.

SELECT deptno, AVG(sal) AS avgsal FROM emp

GROUP BY deptno;

Display the sum of all the salaries in the employee table.

SELECT SUM(sal) AS sumsal FROM emp;

Display the employee number, employee name and salary of all the employees who are located at ‘Mumbai’.

SELECT emp.empno, emp.ename, emp.sal

FROM emp INNER JOIN dept ON

emp.deptno=dept.deptno

AND

dept.loc=’BOMBAY’;

Display the employee name and department name for all the employees from emp and dept tables.

SELECT emp.ename, dept.dname

FROM emp INNER JOIN dept ON

emp.deptno=dept.deptno;