---

SQL: Odds and Ends....

---

• More feature of SQL
  • The following a features in SQL that are easy to learn (so I put them in this webpage)

    ---

    ---

  • Overview:

    General expression in the SELECT clause Sorting the output of a query

  ---

  ---

  ---

  ---

• Arithmetic Expressions in SELECT clause
  • The SELECT clause can contain arithmetic expressions

  • Example:

    For each employee, show his/her salary after a 10% increase in salary

    SELECT fname, lname, salary, 1.1*salary FROM employee Output: FNAME LNAME SALARY 1.1*SALARY ------ -------- ---------- ---------- John Smith 30000 33000 Frank Wong 40000 44000 Alice Miller 25000 27500 Jack Wallace 43000 47300 John Doe 38000 41800 Joyce English 25000 27500 Jake Jones 25000 27500 James Borg 55000 60500

  ---

  ---

  ---

  ---

• Limiting the number of row output
  • Syntax:

    LIMIT N

    will output at most N rows of tuples

    ---

  • Example:

    select * from works_on limit 4 Outputs: essn pno hours --------- ----------- ------- 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0

  ---

  ---

  ---

  ---

  ---

  ---

• Sorting the output tuples
  • There is a ORDER BY clause in the SQL SELECT command that can be used to sort the output tuples.

  • Syntax:

    ORDER BY attribute-list [ASC | DESC]

    The default ordering is ascending

    ---

    ---

  • Example:

    Sort employees by their salary value

  • Solution:

    SELECT fname, lname, salary FROM employee ORDER BY salary Output: FNAME LNAME SALARY ------ -------- ---------- Alice Miller 25000 <---- Last names Joyce English 25000 <---- out of order Jake Jones 25000 <---- John Smith 30000 John Doe 38000 Frank Wong 40000 Jack Wallace 43000 James Borg 55000

    ---

  • Example:

    Sort employees by their salary figures and within the same salary figure, by their last name:

  • Solution:

    SELECT fname, lname, salary FROM employee ORDER BY salary, lname Output: FNAME LNAME SALARY ------ -------- ---------- Joyce English 25000 <---- Last names Jake Jones 25000 <---- in alphabetic Alice Miller 25000 <---- order John Smith 30000 John Doe 38000 Frank Wong 40000 Jack Wallace 43000 James Borg 55000

    ---

    ---

  • Example:

    Sort employees by their salary figures in descending salary:

  • Solution:

    SELECT fname, lname, salary FROM employee ORDER BY salary DESC Output: FNAME LNAME SALARY ------ -------- ---------- James Borg 55000 Jack Wallace 43000 Frank Wong 40000 John Doe 38000 John Smith 30000 Alice Miller 25000 Joyce English 25000 Jake Jones 25000

    ---

    ---

  • You can find the highest salary of the employees as follows:

    select salary from employee order by salary DESC limit 1