Different Mysql Queries

 Retrieve the birthdate and address of the employee whose name is 'John B. Smith'.

Q0: SELECT            BDATE, ADDRESS
                FROM                      EMPLOYEE
                WHERE                   FNAME='John' AND MINIT='B’
                                                AND LNAME='Smith’;

Retrieve the name and address of all employees who work for the 'Research' department.

Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.DNUMBER = DEPARTMENT.DNO WHERE DNAME='Research’;

 For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.

Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION=‘Stafford’;

For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.

Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S
WHERE   E.SUPERSSN=S.SSN;

Retrieve the SSN values for all employees.

Q9: SELECT SSN FROM EMPLOYEE

Retrieve ssn and dname values for all employee and department.

Q10: SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT;

With the use of *, retrieve all the attribute values of the selected tuples.

Q1c: SELECT * FROM EMPLOYEE WHERE DNO=5;

SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER WHERE DNAME='Research’ ;

For example, the result of Q11 may have duplicate SALARY values whereas Q11A does not have any duplicate values

Q11A: SELECT DISTINCT SALARY FROM EMPLOYEE;

Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project.

Q4: (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION    (SELECT  PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith') ;

Retrieve the name and address of all employees who work for the 'Research' department.

QQ1. SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN  (SELECT  DNUMBER FROM DEPARTMENT WHERE DNAME='Research' );

Retrieve the name of each employee who has a dependent with the same first name as the employee.

SELECT                  E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND E.FNAME=DEPENDENT_NAME)

Retrieve the names of employees who have no dependents.

SELECT                  FNAME, LNAME FROM EMPLOYEE WHERE     NOT EXISTS   (SELECT * FROM  DEPENDENT WHERE SSN=ESSN);

Retrieve the social security numbers of all employees who work on project number 1, 2, or 3.

SELECT                  DISTINCT ESSN FROM WORKS_ON WHERE PNO IN  (1, 2, 3) ;

These are different examples of JOIN.

QQ2. SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM (EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S   ON  E.SUPERSSN=S.SSN);

 SELECT FNAME, LNAME, ADDRESS FROM  (EMPLOYEE JOIN DEPARTMENT ON DNUMBER=DNO) WHERE       DNAME='Research’;

SELECT PNUMBER, DNUM,                LNAME, BDATE, ADDRESS FROM (PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON MGRSSN=SSN WHERE      PLOCATION='Stafford’

Find the maximum salary, the minimum salary, and the average salary among all employees.

SELECT                  MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE;

 Find the maximum salary, the minimum salary, and the average salary among employees who work for the 'Research' department.

SELECT MAX(SALARY), MIN(SALARY),  AVG(SALARY) FROM     EMPLOYEE JOIN DEPARTMENT ON DNO=DNUMBER
WHERE   DNAME='Research‘;

Queries 17 and 18: Retrieve the total number of employees in the company (Q17), and the number of employees in the 'Research' department (Q18).

SELECT                  COUNT (*) FROM  EMPLOYEE;

For each department, retrieve the department number, the number of employees in the department, and their average salary.

Qq5. SELECT          DNO, COUNT (*), AVG (SALARY) FROM EMPLOYEE GROUP BY DNO;

For each project, retrieve the project number, project name, and the number of employees who work on that project.

SELECT PNUMBER, PNAME, COUNT(*) FROM                PROJECT JOIN WORKS_ON ON PNUMBER=PNO GROUP BY PNUMBER;

For each project on which more than two employees work , retrieve the project number, project name, and the number of employees who work on that project.

SELECT PNUMBER, PNAME, COUNT                (*) FROM                PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2 ;

SELECT FNAME, LNAME, SALARY, 1.1*SALARY FROM EMPLOYEE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProductX’;

Retrieve a list of employees and the projects each works in, ordered by the employee's department, and within each department ordered alphabetically by employee last name.

SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND        PNO=PNUMBER ORDER BY                DNAME, LNAME