Introduction to SQL

The work is provided in hard copy, but it is also available from the web site. If you open the web site version in a browser, you can cut and paste SQL commands to save typing them.

Work through the sections in the book. Some paragraphs explain what we are trying to achieve (Aim), others show typical SQL commands to achieve these aims, others provide additional information (Comment) and the remainder are work for you to do (Task).

Each Task is numbered. You will need to record your work on each task as you go through the course. This record will be a record of what you have done. It will allow the instructor to comment on that work and to assess your overall progress as well as provide a record of your own progress.

Back to Contents

2 Database Management System (DBMS) Variations

This set of exercises is written to show the differences and similarities in three SQL implementations:

MYSQL 5.0

Oracle 8

Microsoft Access.

There are minor variations between these in:

the syntax of SQL commands

the type of data which can be stored

the facilities for interaction

It is educational to work the exercise using more that one of these implementations, but We will mainly use the MySQL DBMS for learning about retrieval of data from the database. An interactive interface is provided for MySQL.

The same database has been created in all three systems.

2.1 Minor syntax differences

In the example SQL given below, I put fixed words in UPPERCASE and words you supply in lowercase. Generally SQL doesn’t care which case you use, except in strings – characters entered into the database are held in mixed case. In MySQL, table names have to be in the correct case, but it doesn’t matter in Oracle or Access.

Spaces must separate words in the SQL command but the command can be spread over multiple lines. Commands end with a semi-colon (;). Access doesn’t care but it is required in Oracle and MySQL command line interfaces. The Web-based interface to MySQL doesn’t care.

Unless otherwise stated, the same commands apply to MySQL and Oracle.

In all versions of SQL, watch out for problems with:

Matching parentheses

Matching quotes

Reserved words (these vary from version to version)

2.2 Starting instructions

[MySQL] Start the Web interface to the MYSQL database

http://www.cems.uwe.ac.uk/~cjwallac/sql/mysql/emp-dept/queryemp.php

Enter commands into the panel and click ‘submit query’. This interface runs under a user with only read access rights so you should not be able to damage the database. In the event of any problems, email the author.

[Access] Locate the test database and save to your PC. www.cems.uwe.ac.uk/~cjwallac/sql/access/emp.mdb

Start access (START/Programs/ Microsoft Office/Access) and load this database. The default version of SQL is SQL-92 on this database. Normally Access uses an older version SQL-89.

[Oracle] Start SQL PLUS (START/Programs/Database/Oracle/SQL PLUS) –

** need to check if the Oracle db is still available ** Usercode is CEMSSCOT, password is WALLACE, service is CEMS901

Enter commands at the > prompt. Press return to execute the command.

Back to Contents

3 An overview of the EMP-DEPT database

Aim: To find out what tables exist in the database.

[MySQL] SHOW TABLES;

[Access] Click the Tables tab.

[Oracle] SELECT * FROM cat;

Aim: To examine the table definitions to see what columns are defined, what types of data they hold, which column must have a unique value for each row – the PRIMARY KEY.

[MySQL/Oracle] DESCRIBE tablename;

[Access] Right-click Table name/ Design View

e.g.

DESCRIBE dept;

Task 3.1: In this application there are three tables: emp, dept and salgrade. Examine each table definition.

Comment: You need to be familiar with the structure of the database to know which table to examine for any data. This table listing is not really enough to tell you all you need to know – names are abbreviated, not explained but it is a start.

Question: Where is an employee’s commission recorded? How do we know what department an employee works in? What period does sal cover? [it’s actually a month, not a year!]

Aim: To examine the full set of rows in a table.

[MySQL] SELECT * FROM tablename;

[Access] Left Double click table name

e.g.

SELECT * FROM emp;

Task 3.2: Examine the rows in all tables. Answer the following questions by inspecting the data by eye – later we will use SQL to retrieve this data.

How many departments are there? How many employees? What is the salary of the employee named SMITH? Which department does she work in? What is her Manager’s name? Her Manager’s Department?

Back to Contents

4 The SELECT statement

Aim: To display selected data from the Database.

Comment: Normally the data would be far too extensive to get a full listing. In this University there are over 2000 employees, so we would have to restrict the listing to just those employees of interest.

The SQL command for this is the SELECT Command. It allows an extensive range of options. The full command looks something like:

SELECT [DISTINCT] column| expression [AS name], column..

FROM table, table.

WHERE cond [AND | OR cond..

GROUP BY column.

HAVING cond | expression

ORDER BY column, column,

;

We will introduce the parts of this complex statement one at a time. Note that the order of the phrases matters.

The Command line and GUI systems differ in the facilities for querying the database. In Access2000, there is the added ability to use a GUI drag-n-drop query builder in addition to defining queries with SQL. We will concentrate here on SQL alone.

To execute a query:

[MySQL/Oracle] type or cut and paste a SELECT statement and execute it

[Access] Click Queries tab/Double Click Create Query in Design View – now we can use the query builder, but we will be using SQL directly, so close the Show Table panel, right click and select SQL View. In this panel you can type (or cut and paste) SQL commands and execute them by clicking the ! in the menu bar. To go back to the SQL view, right click, then select SQL view.

Back to Contents

5 Selecting COLUMNs to display

Aim: Limit the columns displayed by listing the columns you require, in the order required:

SELECT empno, ename,job FROM emp;

SELECT deptno, ename FROM emp;

Task 5.1: list the department numbers and their location

Aim: To list the rows in a specific order:

SELECT * FROM emp ORDER BY sal;

This will be in increasing (Ascending) order. To get the highest first:

SELECT * FROM emp ORDER BY sal DESC;

Task 5.2: List the departments in alphabetical order of Location.

Comment: We can combine parts of the SELECT command to build more powerful commands. So we can restrict the columns listed and order them at the same time:

Task 5.3: List the employees and their department numbers, in department number order

Question: Can we sort on a column we don’t show? Can we sort on several columns, say deptno and then ename within department?

Task 5.4: List the employee names sorted by job within manager no

Back to Contents

6 Computing values

Aim: We often need to compute values based on the values stored in the database. A principal of databases is that you store only the base data and compute additional values from that basic data. For example, the year’s pay for an employee is calculated as 12 times the monthly salary plus the commission. It would be pointless to store this value as well as the monthly salary. We can use normal arithmetic expressions and the additional functions (see Appendix) to define a new derived column:

SELECT *, 12*sal+comm FROM emp;

Task 6.1: List just the employee names and their annual salaries in alphabetic order of name.

Comment: Some employees don’t have commission (the value is NULL) and this has resulted in a null year’s pay. To give NULL the value 0 we can use the expression:

[MySQL] IFNULL(comm,0)

[Access] NZ(comm,0)

[Oracle] NVL(comm,0)

Task 6.2: use this function to show the year’s pay of each employee, together with their name.

Comment: The new, computed column has no useful name. We can give it a good name with the AS keyword:

SELECT ename, 12*sal + IFNULL(comm,0) AS "Years pay" FROM emp;

Comment: There is a wide range of functions which can be used in SQL. Which ones are available depends on the version of SQL being used. In fact you can use SELECT to calculate the value of any expression:

[MySQL] SELECT sqrt(2);

[Access] SELECT sqr(2);

[Oracle] SELECT sqrt(2) FROM DUAL;

Task 6.3: Try a few expressions using the functions shown in the list of functions in Appendix 2.

Aim: To extract parts of a string.

To extract the first three characters of a department’s location:

[MySql] SELECT deptno, substring(location, 1, 3) FROM dept;

[Oracle] SELECT deptno, substr(location,1, 3) FROM dept;

[Access] SELECT deptno, mid(location,1, 3) FROM dept;

Aim: To join strings together.

To display the department name and location in one column:

[MySQL] SELECT deptno, CONCAT(dname, ' in ' , loc) FROM dept;

[Oracle] SELECT deptno, dname || ' in ' || loc FROM dept;

[Access] SELECT deptno, dname + ' in ' + loc FROM dept;

Task 6.4: For each employee, display their name and an abbreviation of their job made from the first and last letters of the name of the job.

Back to Contents

7 Selecting ROWS to display

Aim: Usually there are far too many rows to be displayed. One way to restrict the output is to show only selected rows.

Aim: To select specific rows. We restrict which rows are displayed with the WHERE clause. In this clause, we can put a condition to describe those rows of interest:

SELECT * FROM emp WHERE deptno = 10;

Lists only the employees in department number 10.

Task 7.1: List the employees in department 20

Task 7.2: List those managed by employee number 7698;

Comment: We compare numbers by typing the value. To compare strings, we need to enclose the string in single quotes:

SELECT * FROM emp WHERE job= 'CLERK’;

Task 7.3: List the Analysts;

Comment: In MySQL and Access, tests on strings ignore the case – so CLERK matches ‘clerk’ and ‘CLERK’ and any mixture. However in Oracle, the test is case-sensitive.

Comment: Rows which contain a NULL value (i.e. no data) in a column can be selected by testing for NULL;

SELECT * FROM emp WHERE comm IS NULL;

Task 7.4: List all the employees who do not have a manager.

Comment: We can use any of the comparison operators to create a complex expression, and join expressions with AND or OR, and negate an expression with NOT.

Task 7.5: List the Analysts in department 10

Task 7.6: List the Employees who have a salary greater than 2000

Task 7.7: List the Clerks who earn between 900 and 1000

Task 7.8: List the employees who do NOT work in department 20.

Comment: the LIKE operator allows comparison with a pattern of characters. A pattern contains special characters which match a single character(_), or zero or more characters(%).

Suppose we want to track down all employees with an ‘A’ as the second letter of their name (don’t ask why you would want to do this!).

SELECT * FROM emp WHERE ename LIKE '_A%';

Task 7.9: Find all employees with an ‘S’ in their name

Task 7.10: Find all those whose name begins with ‘J’;

Task 7.11: All those whose name ends with an ‘S’;

Aim: When the result from a SELECT does not include the primary key, there may be duplicate rows. It would be useful to show only the unique rows.

SELECT deptno FROM emp;

This shows just the department numbers for each employee. If we want to show the distinct department numbers, i.e. to list the departments with employees, then:

SELECT DISTINCT deptno FROM emp;

Task 7.12: List the employee numbers of all the managers. How many are there? [Note that there are two possible definitions of ‘Manager’ here – an employee with the job ‘MANAGER’ and employees whose number appears in the mgr column of an employee. Use both.]

Back to Contents

8 Multiple Tables

Aim: To combine data from several tables.

We can combine two or more tables by listing them in the FROM clause:

SELECT * FROM dept, emp;

However, this combines every row on the dept table with every row on the emp table i.e. 4 * 14 = 56 rows. As you can see this is usually going to be useless. Only certain row combinations are interesting.

Task 8.1: Which rows do you think are meaningful? Identify some.

The combination of employee SMITH with the ‘Research’ Department is interesting because that’s the department SMITH works in. Generally, we might be interested in just those rows in the combined result where the employee’s deptno and the deptno of a department are the same. Here the two columns have the same name (DEPTNO) so to distinguish between them, we prefix the column name with the table name: DEPT.DEPTNO and EMP.DEPTNO. Now we can use the WHERE clause to select only those where combined rows have matching numbers:

SELECT * FROM dept, emp WHERE dept.DEPTNO = emp.DEPTNO;

Now each emp row is paired with just one dept row, so there are just 14 rows in the result.

Task 8.2: List the employee names and the location of their department.

Aim: Of course we can use all the other parts of the select statement to restrict the rows displayed even further. For example we may want to list only the clerks with their department names:

SELECT ename, dname FROM emp, dept WHERE dept.deptno = emp.deptno AND job='CLERK';

Task 8.3: List the employees who earn over 1000 per month with their departments

Aim: Sometimes it IS meaningful to select rows where there is no equal value. Consider the emp and salgrade tables. The salgrade table shows the grade associated with a salary range.

Task 8.4: List every employee with every salary range

This should produce a result with 14 * 5 rows.

Which rows are meaningful here? We are only interested in those where the employee’s salary falls inside the range. i.e. where sal between losal and hisal.

Task 8.5: List every employee by name with their monthly salary and salary grade. Check that there is a row in the result for each employee.

Aim: Suppose we wanted to find the name of an employee’s manager. Look at the data in the emp table and identify the name of SMITH’s manager. The mgr column contains the empno of the employee’s manager so we can use that.

We can do it easily just one employee at a time, but we want to do it quickly for all of them. There’s a bit of a trick here. Take two ‘copies’ of the table [actually no copying is done, its just convenient to look at it this way]. We have to give them each a ‘alias’]. Now we can combine each row of one copy with each row of the other.

SELECT * FROM emp e, emp m;

DON’T do this on Oracle – it just takes forever – Why ? How many rows would be produced?

We are interested in certain combinations only. To answer the question about the manager’s name, we are interested in those rows where the MGR value in one row is equal to the EMPNO in the other:

SELECT * FROM emp e, emp m WHERE e.mgr = m.empno;

Now we can restrict the columns to view. We have to use the table alias to specify which table the column is to come from.

SELECT e.ename, m.ename FROM emp e, emp m WHERE e.mgr = m.empno;

Task 8.6: Show the employee name and manager name for all employees in department 20.

Aim: To related data in more than two tables.

We can simply list all the tables required in the FROM clause and write a WHERE clause which selects the required combined rows.

Eg Show the employee name, the managers name and the employee’s department:

SELECT e.ename, m.ename, d.dname

FROM emp e, emp m , dept d

WHERE e.mgr = m.empno AND e.deptno = d.deptno;

Task 8.7: Show the employee name, the manager name and the manager’s department name.

Task 8.8: Show the employee name, manager name and the department names of both.

Back to Contents

9 Summarising and Grouping

We often want to reduce the volume of data displayed. We can do this by summarising the values in a column and by grouping rows together.

Aim: to summarise data. SQL provides several aggregation functions. These calculate a single value summarising all rows selected:

To count the number of rows:

SELECT count(*) FROM emp;

To get the number, average (rounded), min and max salaries for Managers:

SELECT count(*), round(avg(sal)), min(sal), max(sal) FROM emp WHERE job='MANAGER';

Comment: Min and max also work with character data.

Task 9.1: Find the employee whose name is first in the alphabet.

Aim: To summarise groups of data. If we want the analysis of salary by job, it would be useful to be able to do all jobs at once. The GROUP clause serves this function

SELECT job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp

GROUP BY job;

We can restrict the rows included with a WHERE clause: to do the same analysis but omitting employees on commission:

SELECT job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp

WHERE comm is NULL

GROUP BY job;

We can restrict the GROUPS included with the HAVING clause: to limit the groups to those where the number of employees is greater than 1:

SELECT job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp

GROUP BY job

HAVING count(*) > 1;

We can group by several columns at the same time – if we want the same analysis by dept, and job within dept:

SELECT deptno, job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp

GROUP BY deptno, job;

Task 9.2: Analyse the employees’ salaries by department alone.

Task 9.3: Analyse the employees’ salaries by manager.

Task 9.4: Do managers manage staff in different department? Do managers in different departments have the same number of staff?

Back to Contents

10 Handling Dates

Business databases have to be able to enter date (and time) information in various formats, display it in different formats and to be able to perform comparisons and calculations.

Unfortunately, date formats and date handling vary greatly between different SQL versions.

Aim: To extract parts of dates:

[Access] SELECT datepart('yyyy',hiredate),datepart('m',hiredate), datepart('d',hiredate) FROM emp

[MySQL] SELECT year(hiredate), month(hiredate), dayofmonth(hiredate) FROM emp;

[Oracle] SELECT EXTRACT(YEAR FROM hiredate), EXTRACT(MONTH FROM hiredate), EXTRACT(DAY FROM hiredate) FROM emp;

Aim: To compare dates.

To list all employees who were hired before 1 jan 1996:

[Oracle] SELECT * FROM emp WHERE hiredate < '1-jan-96';

[Access] SELECT * FROM emp WHERE hiredate < #1-jan-96#;

[MySQL] SELECT * FROM emp WHERE hiredate < '96-01-01';

Task 10.1: List all analysts hired after 30 april 1996

Aim: To compute dates.

To list the employees with their 5 year service dates.

[Access] SELECT ename, hiredate+5*365 FROM emp ;

[Oracle] SELECT ename,add_months(hiredate,5*12) FROM emp;

[MySQL] SELECT ename, date_add(hiredate,interval 5 year) FROM emp ;

Aim: To get the current date and time :

[Oracle] SELECT current_date, current_timestamp FROM DUAL;

[Access] SELECT date(), time(), now();

[MySQL] SELECT current_date, current_timestamp;

Aim: To compute date intervals – the interval of time between two dates. How many days has each employee worked?

[Oracle] SELECT trunc(sysdate – hiredate) FROM emp;

[Access] SELECT date() - hiredate FROM emp;

[MySQL] SELECT to_days(current_date) - to_days(hiredate) FROM emp;

Task 10.2: How many weeks has each employee worked

Task 10:3: What’s the average number of years employees have worked, analysed by department?

Back to Contents

11 User Interaction

Aim: To allow the user to enter part of a SELECT statement at the time the statement is executed.

Comment: It is often useful to allow the user to input part of a query at the time it is executed. This allows us to write a general query which can be reused for different cases. For example to display the details of a specific Employee

[Access] SELECT * FROM emp WHERE ename=[Enter Employee Name];

[Oracle] SELECT * FROM emp WHERE ename=&Employee_Name;

[MySQL] not available with this web interface

In Oracle, the name must be entered with the enclosing single quotes; In Access they are not required

Task 11.1: List all employees with a given department number

Task 11.2: List all employees with a given department name.

Back to Contents

12 Installing an Oracle Database

Before we can update a database, we will have to install a version which we have update rights to. So you will now install your own copy of the EMP-DEPT database on ORACLE.

12.1 Logging in

See https://www.cems.uwe.ac.uk/wiki/index.php/Oracle

and linked pages for details on the CEMS Oracle installation

12.2 Creating the database

In a Web browser, locate the file of SQL commands to create the emp database:

www.cems.uwe.ac.uk/~cjwallac/sql/oracle/defempdb.txt

There are two techniques for executing these statements:

A. Select, copy and paste each section into the SQLPlus window.

B.1 Save the file to your PC – ‘Save as’ from the Browser. It will be saved to the folder ‘My Documents’ which is also mapped to drive h.

B.2 enter this command into SQLPlus:

@h:dbempdb.txt

This will execute the statements in the file.

The database should now be created in your account. Check it with the commands in the section ‘An overview of the Database’.

In general, you can use this approach to creating databases: type up the commands in a file, check that there are no typos, and save the file. Then copy sections into SQLPlus or in Oracle, execute the file using the @ command. If something goes wrong and you need to redo the work, you only have to correct the text and recopy. Some design tools such as QSEE, Rational Rose, Select SSADM will allow you to model the database as a diagram and then to export the database schema as a file.

Comment: The file contains commands to DROP each table before it is CREATED. This is convenient if you need to change the database definition. However, all the data in the table is lost, so this technique would not work if you have a large volume of data already stored. SQL has the ALTER statement to change the definition of an existing table but we shall not look at that command on this course.

Task 12.1 Install the EMP-DEPT database in your oracle account

Back to Contents

13 Installing a MySQL database

Before we can update a database, we will have to install a version which we have update rights to. So you will now install your own copy of the EMP-DEPT database on MySQL.

13.1 MySQL account

For the latest details see the CEMS Wiki: https://www.cems.uwe.ac.uk/wiki/index.php/MySQL

In CEMS, there are two installations of MySQL, version 4.0 on shares, version 5 on stocks.

Provided you have been registered as a user (see the Help desk if not), you can log in directly to this server using your UNIX username and password. A MySQL database should also have been created, with the same username and password. In this account you have one database (which can contain multiple tables) with the same name as your username.

You can use this database either by logging into the server and starting the MySQL command line interface, or by using a Web admin tool. Both approaches are described below. The Web interface will be adequate for these exercises and can also be used from outside the University.

The full MySQL schema is in appendix 1.

13.1 PHPMySQLAdmin Interface

This interface the facilities to create and update a MySQL account from the web

Version 5.0 : http://isa.cems.uwe.ac.uk/phpmyadmin/

You will need to login, then select your database (with the same name as the username).

This program is commonly provided on Web hosts so it is useful to gain a little familiarity with its interface.

13.2 Command Line interface

13.2.1 Login

The home directory is the same as the home directory on UNIX and all files are shared with the MySQL server. You may need to CD to get to the directory in which your database files are located.

Start the MYSQL command line interface by entering

mysql -u unixusername -p

at the command prompt e.g.

mysql –u cjwallac –p

You will be prompted for the password.

Now select the database:

use dbname

e.g.

use cjwallac

You can now enter any SQL command. Remember to end every command with a semi-colon (;). You can enter the command over several lines.

To exit, enter EXIT or QUIT

13.2.1 Saving the database creation script and the data files

Find the Documentation on this example.

Save the creation script and the three data files to your chosen directory. (right click in the Browser window and ‘Save Target as .. ) Locate the directory on your windows drive and save the file. Keep the same filename for ease of reference.

13.2.2 Creating the Database

You can create a database by simply entering SQL commands into the command line interface, but this is not efficient if you need to correct any errors, or provide documentation on the table definition or the data. It is usually better to create a text file first, using an editor such as Notepad Plus (on Windows) or to generate the schema from a design tool like QSEE. Then you can execute the script by entering at the command line:

SOURCE filename

13.2.3 Loading the database

The script file includes commands to load data from a file into a table. This command is specific to MySQL but makes table loading from other data sources easy. For example you can keep your data in Excel and export it to CSV

LOAD DATA LOCAL INFILE 'dept.txt' INTO TABLE dept FIELDS TERMINATED BY ';';

13.3 Your Task

Task 13.1 Install the EMP-DEPT database in your MySQL account.

Back to Contents

14 Changing the data in a database

There are three SQL commands which change data in tables:

INSERT – to insert new rows in a table

UPDATE - to change values in a row of a table

DELETE – to delete rows from a table

Aim: To add new a new row to a table:

To add a new department, use the INSERT statement.

INSERT INTO dept VALUES (50, 'Information Systems', 'London');

Here the values of each field are listed in the same order as the columns in the table definition.

Aim: To update one or more values in a row.

To change the location of department 50 to Bristol:

UPDATE dept SET loc = ‘Bristol’ WHERE deptno=50;

Aim: To remove one or more rows:

To remove department number 50:

DELETE FROM dept WHERE deptno=50;

Task 14.1: Add yourself as a new employee. You will have to invent a unique employee number, and suitable values for the other columns.

Task 14.2: Increase your sal by 1000.

Task 14.3: Delete yourself.

Back to Contents

Appendix 1 – EMP-DEPT database

The MySQL schema for this database

DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS salgrade;
DROP TABLE IF EXISTS emp;

CREATE TABLE salgrade(
   grade int(4) not null primary key,
   losal decimal(10,2),
   hisal decimal(10,2));

CREATE TABLE dept(
   deptno int(2) not null primary key,
   dname varchar(50) not null,
   location varchar(50) not null);

CREATE TABLE emp(
   empno int(4) not null primary key,
   ename varchar(50) not null,
   job varchar(50) not null,
   mgr int(4),
   hiredate date,
   sal decimal(10,2),
   comm decimal(10,2),
   deptno int(2) not null);

insert into dept values (10,'Accounting','New York');

insert into dept values (20,'Research','Dallas');

insert into dept values (30,'Sales','Chicago');

insert into dept values (40,'Operations','Boston');

insert into emp values (7369,'SMITH','CLERK',7902,'93/6/13',800,0.00,20);

insert into emp values (7499,'ALLEN','SALESMAN',7698,'98/8/15',1600,300,30);

insert into emp values (7521,'WARD','SALESMAN',7698,'96/3/26',1250,500,30);

insert into emp values (7566,'JONES','MANAGER',7839,'95/10/31',2975,null,20);

insert into emp values (7698,'BLAKE','MANAGER',7839,'92/6/11',2850,null,30);

insert into emp values (7782,'CLARK','MANAGER',7839,'93/5/14',2450,null,10);

insert into emp values (7788,'SCOTT','ANALYST',7566,'96/3/5',3000,null,20);

insert into emp values (7839,'KING','PRESIDENT',null,'90/6/9',5000,0,10);

insert into emp values (7844,'TURNER','SALESMAN',7698,'95/6/4',1500,0,30);

insert into emp values (7876,'ADAMS','CLERK',7788,'99/6/4',1100,null,20);

insert into emp values (7900,'JAMES','CLERK',7698,'00/6/23',950,null,30);

insert into emp values (7934,'MILLER','CLERK',7782,'00/1/21',1300,null,10);

insert into emp values (7902,'FORD','ANALYST',7566,'97/12/5',3000,null,20);

insert into emp values (7654,'MARTIN','SALESMAN',7698,'98/12/5',1250,1400,30);

insert into salgrade values (1,700,1200);

insert into salgrade values (2,1201,1400);

insert into salgrade values (3,1401,2000);

insert into salgrade values (4,2001,3000);

insert into salgrade values (5,3001,99999);

The Sample data

emp Table

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1993-06-13	800.00	0.00	20
7499	ALLEN	SALESMAN	7698	1998-08-15	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1996-03-26	1250.00	500.00	30
7566	JONES	MANAGER	7839	1995-10-31	2975.00		20
7698	BLAKE	MANAGER	7839	1992-06-11	2850.00		30
7782	CLARK	MANAGER	7839	1993-05-14	2450.00		10
7788	SCOTT	ANALYST	7566	1996-03-05	3000.00		20
7839	KING	PRESIDENT		1990-06-09	5000.00	0.00	10
7844	TURNER	SALESMAN	7698	1995-06-04	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1999-06-04	1100.00		20
7900	JAMES	CLERK	7698	2000-06-23	950.00		30
7934	MILLER	CLERK	7782	2000-01-21	1300.00		10
7902	FORD	ANALYST	7566	1997-12-05	3000.00		20
7654	MARTIN	SALESMAN	7698	1998-12-05	1250.00	1400.00	30

dept Table

deptno	dname	location
10	Accounting	New York
20	Research	Dallas
30	Sales	Chicago
40	Operations	Boston

salgrade Table

grade	losal	hisal
1	700.00	1200.00
2	1201.00	1400.00
4	2001.00	3000.00
5	3001.00	99999.00
3	1401.00	2000.00

Appendix 2 – SQL

SQL Command summary

Reserved words are CAPITALIZED, user defined words in lower case

Task	Example
List all the columns of a table	SELECT * FROM emp
List specific columns of a table	SELECT ename, empno FROM emp
List selected rows of a table	SELECT * FROM emp WHERE job=’analyst’ AND deptno=’10

Order the rows of a table	SELECT * FROM emp ORDER BY ename
List distinct values in a column	SELECT DISTINCT job FROM emp
Computing a new column	SELECT 12*sal + comm AS total FROM emp

Joining two tables	SELECT * FROM emp NATURAL JOIN dept
or	SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno
Joining table to itself	SELECT * FROM em

Grouping	SELECT job, count(*),avg(sal) FROM emp GROUP BY job
Selecting Groups	SELECT job, avg(sal) FROM emp GROUP BY job HAVING avg(sal) > 2000

Inserting data	INSERT INTO dept VALUES (50, 'Information Systems', 'London')
Updating data	UPDATE emp SET job=’REP’ WHERE job=’SALESMAN’
Deleting data	DELETE from emp WHERE empno=7900

Creating table	CREATE TABLE loc ( location VARCHAR(50) PRIMARY KEY )
Deleting table	DROP TABLE loc
Updating table	ALTER TABLE loc ADD COLUMN latitude DECIMAL(10,2) )

Arithmetic expressions

+ addition

- substraction

* multiplication

/ division

% modulus [ mod in Access]

( ) parentheses

Comparison operators

= equal

> greater than

>= greater than or equal

< less than

<= less than or equal

<> not equal

BETWEEN min AND max in range

IN list equal to one of the values in the list

LIKE matches a character pattern –

% stands for 0 or more chars

stands for a single character

IS NULL value is null

Logical Operators

NOT not

AND both expressions true

OR either expression true

Aggregation Functions

function	name	Access	MySQL	Oracle
Number of values	COUNT	y	y	y
Average value	AVG	y	y	y
Minimum value	MIN	y	y	y
Maximum value	MAX	y	y	y
Total	SUM	y	y	y
Standard Deviation	STDDEV	n	y	y
Variance	VARIANCE	n	n	y

In Oracle, these functions can be modified by ALL (the default) or DISTINCT (just include unique values) i.e. COUNT (DISTINCT sal)

Arithmetic and String Functions

purpose	Access	MySQL	Oracle
Square root	SQR(val)	SQRT(val)	SQRT(val)
Absolute value	ABS(val)	ABS(val)	ABS(val)
Round to a whole number	ROUND(val)	ROUND(val)	ROUND(val)
Whole part	TRUNC(val)	FLOOR(val)	FLOOR(val)
X to the power Y		POWER(val,n)	POWER(val,n)
Lower whole number	INT(val)	FLOOR(val)	FLOOR(val)

Convert null to value	NZ(col,val)	IFNULL(col,val)	NVL(col,val)


Change string to uppercase	UCASE(str)	UCASE(str) UPPER(str)	UPPER(str)
Change string to lowercase	LCASE(str)	LCASE(str) LOWER(str)	LOWER(str)
Get length of String	LEN(str)	LENGTH(str)	LENGTH(str)
Extract sub string	MID(str,start,length)	SUBSTRING(str, start,length)	SUBSTR(str, start,length)
Concatenate strings	str + str	CONCAT(str,str)	CONCAT(str,str) str \|\| str
Trim blanks form both ends	TRIM(str)	TRIM(str)	TRIM(str)

Date Functions

function	access	mysql	Oracle
Get year of date	datepart('yyyy',date)	year(date)	Extract(year from date)
Get month of date	datepart('m',date)	month(date)	Extract(month from date)
Get day of month	datepart('d',date)	dayofmonth(date)	Extract(Day from date)
Current Date	Date()	Current_date	Current_date
Current Time		Now()
Add days to date	Date + n	Date_add(date, interval n day)	Date + n
Subtract days	Date - ns	Date_add(date,interval -n day)	Date - 1
Subtract dates	Date1 – date2	To_days(date1) – to_days(date2)	Date1 – date2
Add months to Date	?	Date_add(date, interval n month)	Add_months(date, n)
Add years to Date	?	Date_add(date, Interval n year)	Add_months(date, n*12)

Data Types

Relational Databases have a limited number of types of data. These vary with different databases.

Type of Data	Access 2000	MySQL	ORACLE

Fixed length string	-	CHAR(n)	CHAR(n)
Variable length String	TEXT	VARCHAR(n)	VARCHAR(n)
Whole Number	LONG INTEGER	INT(n)	NUMBER(n)
Fixed Decimal number	LONG INTEGER	DECIMAL(n,d)	NUMBER(n,d)
Approximate number	DOUBLE	FLOAT	FLOAT
Date	DATE/TIME	DATE	DATE
Binary Large Object	OLE Object?	BLOB	BLOB

Housekeeping tasks

Task	Access 2000	MySQL	ORACLE
Executing a script	n/a	SOURCE filename	@ filename (no blank lines)
Listing the tables in a database	n/a	SHOW TABLES	SELECT * FROM CAT
Listing a table definition	n/a	DESCRIBE tablename	DESCRIBE tablename
Loading table from file	n/a	LOAD DATA LOCAL INFILE 'filename' INTO TABLE mytable FIELDS TERMINATED BY ',';	??