SQL Queries

Once you are happy with your private database you can do the following SQL exercises.

1. Run the following queries, and explain in an English sentence what the query means.

    SELECT *
    FROM Emp;
   
    SELECT ENAME, JOB
    FROM Emp;
   
    SELECT ENAME, JOB, SAL
    FROM Emp
    WHERE SAL > 2500;
   
    SELECT ENAME, DNAME
    FROM Emp e, Dept d
    WHERE e.DEPTNO = d.DEPTNO;
   

2. Write queries to obtain the following information.
   Hint 1: It's easiest to create and edit SQL queries in a text editor window (e.g. emacs), and then copy and paste them into your MySQL window.
   Hint 2: You can also cut and paste the result of an MySQL query into the editor window. As an aid for later study you're advised to maintain a file containing your SQL queries and the corresponding tables.
   1. Print the contents of the dept table.
   2. Print the name and location of all departments.
   3. Print the department nos of of all employees.
   4. Without repetition print the department nos of of all employees.
   5. Print the name of all employees, together with the department no they work in.
   6. Print the name of all employees, together with the name of the department they work in.
      Hint: You need to join the Emp and Dept tables.
   7. Print the name of all employees who work in the sales department.
   8. Print the salary bill for department 20.
      Hint: Use an aggregate function.
   9. Print the department no and salary bill for all departments.
      Hint: Use grouping.
   10. Print the name of all employees who are on salary grade 1.
   11. Print the name and department name of all employees who are on salary grade 1.
   12. Print the department name and the number of employees who are on salary grade 1.
   13. Write some queries that use join, e.g. find all staff working in Chicago.
   14. List the employees alphabetically by name.