MYSQL COMMANDS - ( CREATE TABLES AND PERFORM QUERY )

 STEP 1 : CREATE OR USE AN EXISTING DATABASE

   CREATE DATABASE NAMEOFDATABASE;

STEP 2 : GET THE LIST OF ALREADY AVAILABLE DATABASES:

   SHOW DATABASES;

STEP 3 : USE A DATABASE OF YOUR CHOICE IN WHICH YOU WANT TO WORK

   USE NAMEOFDATABASE;

STEP 4: CREATE A TABLE 

   CREATE TABLE EMPLOYEE

    ( EMPNO INT(6) PRIMARY KEY , 

      ENAME VARCHAR(20) NOT NULL,

      JOB VARCHAR(20) , 

      HIREDATE DATE, 

      SAL FLOAT ,

       DOB DATE , 

      DEPTNO INT(5));

STEP 5 : INSERT RECORDS INTO TABLE

INSERT INTO EMPLOYEE VALUE(1234, 'PREM' , 'CLERK' ,'1977-02-11' , 12000, '1950-12-31' , 20); 

INSERT INTO EMPLOYEE VALUE(6574, 'GITA','SALESMAN', '1999-06-11',17000,'1961-06-11',30);

## SORT CUT WAY- INSERTING MULTIPLE RECORDS IN A SINGLE COMMAND ##

INSERT INTO EMPLOYEE VALUES

(6754, 'SITA' , 'MANAGER', '1999-03-12' ,39000 , '1968-03-12',50),

(7839, 'REA' , 'MANAGER' , '1998-12-12' , 50000 , '1951-01-15',10),

(8976, 'REETA' , 'SALESMAN' , '1988-09-10' , 9000, '1951-01-01',30),

(8988, 'REENA', 'SALESMAN' , NULL , NULL , '1951-01-12' ,30),

(9876, 'HONEY' , 'CLERK' , '2000-10-07' , 12500 , '1970-05-13',20),

(9899, 'RAM' , 'CLERK' ,  NULL , NULL, '1970-01-12',20);


                                           EXERCISE 1 :

Write queries to perform the following on the above given EMPLOYEE Table :

Q1. Display employee name and their Date of Birth.

Q2. Display names of all employees along with their annual salary (Salary*12). 

While displaying query result, rename EName as Name also rename Salary*12 column as 

Annual Salary.

Q3.To display unique department number for all the employees.

Q4.Display all the employees who are earning more than 5000 and work in DeptNO 20.

Q5.Displays records of all the employees except HONEY and PREM.

Q6. Display name and department number of all those employees who are earning salary 

between 20000 and 50000 (both values inclusive).

Q7. Display details of all the employees who are working either in Dept No 20 or 30 or 40

Q8. Display details of all those employees who are not getting any salary.

Q9. Display names of all the employees who have been getting some salary.

Q10. Displays details of all those employees whose name starts with 'K'

Q11. Displays details of all those employees whose name ends with 'a'

Q12. Displays names of all the employees containing 'se' as a substring in name

Q13. Displays names of all employees containing 'a' as the second character

Q14. Display name of youngest employee.

Q15. Display name of eldest employee.

Q16. Display name of employee getting highest salary.

Q17. Display name of employee getting lowest salary.

Q18. Display name of employees whose HIRE DATE is not given ( it is unknown / NULL).

Q19. Display name of employees whose name contains exactly 5 characters.

Q20. Display name of clerks who are working in Deptt No 20

EXERCISE 2 :

Write queries to perform the following on EMPLOYEE Table :

Q1. Display employee name and their Date of Birth in ascending order of their salary.

Q2. Display details of clerks and managers in descending order of department number.

Q3. To display department wise number of the employees.

Q4. Displays sorted records of all the employees except HONEY and PREM in descending order of their salary.

Q5. Display department wise count of all the employees who are working either in Dept No 20, 30 or 40

Q6. Display minimum, maximum and average salary of clerks

Q7. Display details of employees in ascending order of department number and in descending order of salary.

Q8. Display name of employees in their order of seniority.

Q9. Display name of employees in ascending order of their age.


++++++++++++++++++++++++++++++++++++++++

Run the following commands and find out  the output.

++++++++++++++++++++++++++++++++++++++++

mysql> select * from employee;

mysql> select * from employee ORDER BY SAL;

mysql> select * from employee ORDER BY SAL DESC;

mysql> select * from employee ORDER BY SAL ASC;

mysql> select * from employee ORDER BY DEPTNO, SAL DESC;

mysql> select * from employee ;

mysql> select * from employee WHERE JOB='MANAGER' ORDER BY SAL DESC;

mysql> select * from employee WHERE DEPTNO IN (30,40) ORDER BY SAL ;

mysql> select * from employee WHERE DEPTNO =30 OR DEPTNO=40 ORDER BY SAL ;

mysql> SELECT MIN(SAL) , DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;

mysql> SELECT MIN(SAL) , JOB FROM EMPLOYEE GROUP BY JOB;

mysql> SELECT MIN(SAL) FROM EMPLOYEE GROUP BY JOB;

mysql> SELECT MIN(SAL) FROM EMPLOYEE GROUP BY DEPTNO;

mysql> SELECT MIN(SAL),DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;

mysql> SELECT AVG(SAL) FROM EMPLOYEE GROUP BY JOB;

mysql> SELECT COUNT(*),JOB FROM EMPLOYEE GROUP BY JOB;

mysql> SELECT COUNT(*),DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;

mysql> SELECT COUNT(*),DEPTNO, JOB  FROM EMPLOYEE GROUP BY DEPTNO HAVING JOB='CLERK';

mysql> SELECT COUNT(*),DEPTNO, JOB  FROM EMPLOYEE GROUP BY DEPTNO HAVING JOB='MANAGER';

mysql> SELECT COUNT(*),DEPTNO, SAL  FROM EMPLOYEE GROUP BY DEPTNO HAVING SAL BETWEEN 10000 AND 40000;

mysql> SELECT COUNT(*),DEPTNO FROM EMPLOYEE GROUP BY DEPTNO HAVING SAL BETWEEN 10000 AND 40000;

ERROR 1054 (42S22): Unknown column 'SAL' in 'having clause'

mysql> SELECT COUNT(*),DEPTNO , SAL FROM EMPLOYEE GROUP BY DEPTNO HAVING SAL BETWEEN 10000 AND 40000;

mysql> SELECT ENAME,JOB,DEPTNO FROM EMPLOYEE WHERE DEPTNO <> 20;

mysql> SELECT ENAME,JOB,DEPTNO FROM EMPLOYEE WHERE DEPTNO IN (20,30,40);

mysql> SELECT ENAME,JOB,DEPTNO FROM EMPLOYEE WHERE DEPTNO NOT IN (20,30,40);

mysql> SELECT ENAME,JOB,DEPTNO FROM EMPLOYEE WHERE DEPTNO NOT IN (20);

mysql> SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE "A%";

mysql> SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE "A*";

mysql> SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE "R%";

mysql> SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE "H%";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "H%";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "%I%";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "____";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "_I_A";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE LEFT(ENAME,1) LIKE "H";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE RIGHT(ENAME,1) LIKE "Y";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE RIGHT(ENAME,1) LIKE "A";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "R%A";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "R_A";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "R__A";

mysql> SELECT DISTINCT ENAME FROM EMPLOYEE WHERE ENAME LIKE "R___A";

mysql> SELECT ENAME AS "EMPLOYEE NAME"  FROM EMPLOYEE WHERE ENAME LIKE "R___A";

mysql> SELECT ENAME AS 'EMPLOYEE NAME'  FROM EMPLOYEE WHERE ENAME LIKE "R___A";

mysql> SELECT ENAME AS  EMPLOYEE  NAME  FROM EMPLOYEE WHERE ENAME LIKE "R___A";

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NAME  FROM EMPLOYEE WHERE ENAME LIKE "R___A"' at line 1

mysql> SELECT ENAME AS  EMPLOYEE_NAME  FROM EMPLOYEE WHERE ENAME LIKE "R___A";

mysql> SELECT ENAME AS  EMPLOYEENAME  FROM EMPLOYEE WHERE ENAME LIKE "R___A";

mysql> SELECT ENAME AS  EmployeeName  FROM EMPLOYEE WHERE ENAME LIKE "R___A";

mysql> select ename from employee where sal=min(sal);

ERROR 1111 (HY000): Invalid use of group function

mysql> select ename from employee where sal in select min(sal) from employee;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select min(sal) from employee' at line 1

mysql> select ename from employee where sal in (select min(sal) from employee);

mysql> select ename from employee where sal in (select max(sal) from employee);

mysql> select ename,sal from employee where sal beteween 10000 and 30000;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'beteween 10000 and 30000' at line 1

mysql> select ename,sal from employee where sal between 10000 and 30000;

mysql> select ename,sal from employee where sal >=10000 and sal <= 30000;

mysql> select ename,sal from employee where sal >=10000 and <= 30000;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<= 30000' at line 1

mysql> select ename,sal from employee where sal >=10000 and sal<= 30000;

mysql> select ename,sal from employee where sal >=10000 and sal<= 30000;

Popular posts from this blog

XII- CS-- SOLVED SQL queries (SQL PRACTICAL ASSIGNMENS)

                  Assignment - SQL XII-COMPUTER SCIENCE  1.                                                                     Table : EMP EMPNO ENAME JOB HIREDATE SAL DOB DEPTNO 7839 REA MANAGER 12-DEC-98 5000 10-SEP-48 10 1234 PREM CLERK 11-FEB-77 12000 12-DEC-50 20 6754 SITA MANAGER 12-MAR-99 39000 12-MAR-68 50 6574 GITA SALESMAN 11-JUN-99 7000 11-JUN-61 30 ...
Read more

SQL--HOME ASSIGNMENTS(XII Class)

Image
Assignment - SQL 1.                                                                     Table : EMP EMPNO ENAME JOB HIREDATE SAL DOB DEPTNO 7839 REA MANAGER 12-DEC-98 5000 10-SEP-48 10 1234 PREM CLERK 11-FEB-77 12000 12-DEC-50 20 6754 SITA MANAGER 12-MAR-99 39000 12-MAR-68 50 6574 GITA SALESMAN 11-JUN-99 7000 11-JUN-61 30 9876 HONEY ...
Read more

Python-MySQL Connectivity

 ****************************************** Python 3.6   and MySQL Server 5.1  Connectivity ******************************************* STEP 1 :  First let us find the directory where Python is installed. ****************************************************** >>> import sys >>> sys.path ['', 'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\Lib\\idlelib',  'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\python38.zip',  'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\DLLs',  'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\lib',  'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32',  'C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32\\lib\\site-packages'] >>>  The output shows that Python Interpreter is installed in the folder located at path (location) : C:\\Users\\Hari\\AppData\\Local\\Programs\\Python\\Python36-32...
Read more