MySQL assignment 4 #

Note

• Login to MySQL server using csus username (ssh)
• MySQL on this server is case sensitive

SQL cont. #

Aliases #

Aliases are other names for a table.

Consider our normal company schema,

SELECT DNAME, L.DLOCATION
FROM DEPARTMENT AS D,
     DEPT_LOCATION AS L
WHERE D.DNUMBER=L.DNUMBER;

Here

• D is an alias for DEPARTMENT
• L is an alias for DEPT_LOCATION

To make 2 aliases of one entity:

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

Note: MySQL requires the keyword AS for aliases

Unspecified WHERE clause #

The WHERE clause is optional. Omitting it is the same as WHERE TRUE.

To emulate a Cartesian product,

SELECT SSN,DNAME
FROM EMPLOYEE, DEPARTMENT;
-- no where clause

* operator #

If we want to view all the columns of a table we can use the * operator, ie

SELECT *
FROM STUDENT;

If we want to just see all the tables,

SHOW TABLES

Use of DISTINCT #

To convert a bag into a set, we can remove all duplicates. In SQL, we can use the keyword DISTINCT to remove any duplicate tuples.

SELECT SALARY
FROM EMPLOYEE; -- returns a bag

SELECT DISTINCT SALARY
FROM EMPLOYEE; -- returns a set

Set operations #

EXCEPT is the same as set difference. Note that entities must be union compatible, meaning

• both entities must have the same attributes, and
• both entities attribute’s must appear in the same order

Note: UNION ALL, EXCEPT ALL, and INTERSECT ALL are bag operations, and need not be studied in 134.

UNION example #

Using our same company schema,

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

• the result is in the union between workers and managers

Arithmetic operations #

Show the salary of employees with a 10% raise who work on the ProductX project:

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

• this will calculate 1.1 * SALARY an assign it the alias INCEASED_SAL, and it will be a column in the selected table

So are the salaries actually different after the query has been executed? The answer is no, this is just a temporary calculated column. SELECT is a read operation.

Substring pattern matching #

LIKE is a powerful operator to do pattern matching.

• The % is like a wildcard character
• The _ is a single character wildcard

ORDER BY #

A new clause, ORDER BY is used to sort the selected tuples. After a WHERE clause you can add a ORDER BY clause to specify the sort of the returned table.

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

• DESC means descending
• ASC means ascending, the default
• the tuples always remains together when being sorted
  • if there is a tie between 2 attributes in DNAME, then the tie is broken between LNAME
  • if there is a tie in LNAME, then the tie is broken between FNAME

Nesting of queries #

Retrieve the name and address of all employees who work for the ‘Research’ department

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

The IN operator allows nesting of queries. Notice that the sub query will return a bag of tuples, and the result should be a single attribute, DNUMBER. When the outer SELECT is going through tuples, it is going to check if the current tuple’s DNO is in the bag of tuples returned by the sub query.