Manoj presented a lecture focused on the Structured Query Language [SQL] upon our return from the easter break.  The tutorial was closely tied to the lecture enabling us to work through the code examples shown. With that in mind, shown below are the types of queries covered in the lecture and tutorial with an example and description:

CREATE – create tables (or Databases), specifying the table name and column values. Constraints can be applied to the attributes such as Primary Key, Foreign Key, Default value and Not Null however it is probably easier to add constraints using the ‘ALTER TABLE’ query. CREATE is part of DDL.

ALTER TABLE – As mentioned, can be used for adding constraints to attributes. Can also be used with ‘Add’, ‘Drop’, ‘Modify’ and ‘Rename’.

INSERT – Inserting values into the table, can be used with WHERE condition for aimed insertion.

DELETE – rows of data to be removed WHERE a condition is met.

SELECT – Select specified data from the table WHERE conditions are met. (Projection = choosing columns, Selection = choosing rows). SELECT *  -> select all columns . Order By EMP_NUM enables sorting.

Finding existing primary key – If you are not sure if a table has a primary key (and using Oracle) run the following query.

Queries using Join – If you need to make a selection based on conditions that need to check multiple tables.

Subqueries were also covered in the lecture, a good example of which can be found here:

Finally predicates where also introduced in the lecture:

  • Comparison predicates: =, !=, <, >, <=, >=
  • ‘like’ predicate: surname like “McA%”; ( ‘_’ is single character wild card)
  • ‘between’ predicate: a between c AND d;
  • ‘in’ predicate: used with subqueries:
    • SELECT * from pupil WHERE suburb IN(select suburb from pupil where surname = ‘Brown’) ;
  • ‘any/all’ predicate: more information here:
  • ‘Exists’ predicate: select surname, initials from student where exists (select * from course where c_code = student.c_code and f_id = ‘INFOTECH’);
  • ‘IS NULL’ predicate: ‘=’ cannot be used to evaluate null

Set Functions came next. These functions can be applied to entire columns or sets of data… distinct, count, sum, avg, max and min. There are of course a great deal of combinations of the above and SQL has many other functions. So, in short, SQL is not as simple as I first thought.

Pearl of the week – Use join queries with SQL rather than pulling unnecessary data into the application and sorting it there.