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.
CREATE TABLE DEPARTMENT (DEPT_NUM INTEGER, DEPT_NAME varchar(20), CREATION_DATE DATE);
ALTER TABLE – As mentioned, can be used for adding constraints to attributes. Can also be used with ‘Add’, ‘Drop’, ‘Modify’ and ‘Rename’.
ALTER TABLE Department ADD PRIMARY KEY (Dep_Num);
INSERT – Inserting values into the table, can be used with WHERE condition for aimed insertion.
INSERT into SKILL(S_CODE, S_DESCRIPTION)
VALUES ('S21', 'Analysis');
DELETE – rows of data to be removed WHERE a condition is met.
DELETE from Employee WHERE (SURNAME = 'Tests1');
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.
SELECT Initials FROM Employee WHERE (EMP_NUM = 'E11') OR (EMP_NUM = 'E12');
Finding existing primary key – If you are not sure if a table has a primary key (and using Oracle) run the following query.
select * from ALL_CONSTRAINTS where CONSTRAINT_TYPE = 'P' and TABLE_NAME = 'EMPLOYEE';
Queries using Join – If you need to make a selection based on conditions that need to check multiple tables.
SELECT e_s.EMP_NUM, e_s.S_CODE, e.SURNAME, s.S_DESCRIPTION FROM EMPLOYEE_SKILL e_s, EMPLOYEE e, SKILL s // aliases e_s, e and s are created and used above. WHERE e.emp_num = e_s.emp_num AND e_s.S_CODE = s.S_CODE ORDER BY e.SURNAME;
Subqueries were also covered in the lecture, a good example of which can be found here: http://www.1keydata.com/sql/sql-subquery.html
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: http://www.sql-ex.ru/help/select7.php
- ‘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.