Mar 13

ORDERING OF THE QUERY RESULT

The ORDER BY clause is used to force the query result to be sorted based on one or more column values.  You can select either ascending or descending sort for each named column.

Example 1

List the names and IDs of all faculty members arranged in alphabetical order.
SELECT FACID, FACNAME FROM FACULTY ORDER BY FACNAME;

FACID FACNAME
F101 Adams
F110 Byrne
F221 Smith
F202 Smith
F105 Tanaka

Continue reading

Mar 13

COLUMN FUNCTIONS (AGGREGATE FUNCTIONS)

Aggregate functions allow you to calculate values based upon all data in an attribute of a table. The SQL aggregate functions are:  Max, Min, Avg, Sum, Count, StdDev, Variance.  Note that AVG and SUM work only with numeric values and both exclude NULL values from the calculations.

Example 1

How many students are there?
SELECT COUNT(*) FROM STUDENT;

COUNT(*)

6

NOTE: COUNT can be used in two ways.  COUNT(*) is used to count the number of tuples that satisfy a query.  COUNT with DISTINCT is used to count the number of unique values in a named column.

Example 2

Find the number of departments that have faculty in them.
SELECT COUNT(DISTINCT DEPT) FROM FACULTY;

COUNT(DISTINCT)

4

Continue reading

Mar 13

MORE COMPLEX SINGLE TABLE RETRIEVAL

The WHERE clause can be enhanced to be more selective.  Operators that can appear in WHERE conditions include:

  • =, <> ,< ,> ,>= ,<=
  • IN
  • BETWEEN…AND…
  • LIKE
  • IS NULL
  • AND, OR, NOT

Example 1

Find the student ID of all math majors with more than 30 credit hours.
SELECT STUID FROM STUDENT WHERE MAJOR = ‘Math’ AND CREDITS > 30;

STUID

S1015

S1002

Example 2

Find the student ID and last name of students with between 30 and 60 hours (inclusive).
SELECT STUID, LNAME FROM STUDENT WHERE CREDITS BETWEEN 30 AND 60;

this is the same as…
SELECT STUID, LNAME FROM STUDENT WHERE (CREDITS >= 30)  AND  (CREDITS <= 60);

STUID LNAME
S1015 Jones
S1002 Chin

Continue reading

Mar 13

SIMPLE SINGLE TABLE RETRIEVAL

Example 1

Retrieve all information about students (‘*’ means all attributes)
SELECT * FROM STUDENT;

STUID LNAME FNAME MAJOR CREDITS
S1001 Smith Tom History 90
S1010 Burns Edward Art 63
S1015 Jones Mary Math 42
S1002 Chin Ann Math 36
S1020 Rivera Jane CIS 15
S1013 McCarchy Owen Math 9

Example 2

Find the last name, ID, and credits of all students
SELECT LNAME, STUID, CREDITS FROM STUDENT;

LNAME STUID CREDITS
Smith S1001 90
Burns S1010 63
Jones S1015 42
Chin S1002 36
Rivera S1020 15
McCarthy S1013 9

Continue reading

Mar 13

SQL DATA MANIPULATION LANGUAGE (DML)

The DML component of SQL is the part that is used to query and update the tables (once they are built via DDL commands or other means). By far, the most commonly used DML statement is the SELECT.  It combines a range of functionality into one complex command.

Used primarily to retrieve data from the database.  Also used to create copies of tables, create views, and to specify rows for updating.

General Format: Generic overview applicable to most commercial SQL implementations – lots of potential combinations.  There are several variations available in Oracle.

SELECT {field-list | * | ALL | DISTINCT | expression}
FROM table-list
WHERE expression
GROUP BY group-fields
HAVING group-expression
ORDER BY field-list;

Only the SELECT and the FROM clauses are required.  The others are optional.

FROM

A required clause that lists the tables that the select works on.  You can define “alias” names with this clause to speed up query input and to allow recursive “self-joins”.

WHERE

An optional clause that selects rows that meet the stated condition.  A “sub-select” can appear as the expression of a where clause.  This is called a “nested select”.

GROUP BY

An optional clause that groups rows according to the values in one or more columns and sorts the results in ascending order (unless otherwise specified).  The duplicate rows are not eliminated, rather they are consolidated into one row.  This is similar to a control break in traditional programming.

HAVING

An optional clause that is used with GROUP BY.  It selects from the rows that result from applying the GROUP BY clause.  This works the same as the WHERE clause, except that it only applies to the output of GROUP BY.

ORDER BY

An optional clause that sorts the final result of the SELECT into either ascending or descending order on one or more named columns.

There can be complex interaction between the WHERE, GROUP BY, and HAVING clauses.  When all three are present the WHERE is done first, the GROUP BY is done second, and the HAVING is done last.

Example 1: Select all employees from the ‘ACCT’ department.
SELECT * FROM EMPLOYEES WHERE EMP-DEPT = ‘ACCT’;

Example 2: Show what salary would be if each employee recieved a 10% raise.
SELECT LNAME, SALARY AS CURRENT, SALARY * 1.1 AS PROPOSED FROM EMPLOYEES;

Mar 13

SQL DATA DEFINITION (DDL)

TABLES

CREATE TABLE

Define the structure of a new table

Format:
CREATE TABLE tablename ({col-name type [(size)][constraint],…});

The ‘constraint’ clause in the CREATE TABLE statement is used to enforce referential integrity.  Specifically, PRIMARY KEY, FOREIGN KEY, and CHECK integrity can be set when you define the table.  The syntax for key and check constraints is shown below.

{PRIMARY KEY | FOREIGN KEY} (local-field) [REFERENCES foreign-field] }

for attribute constraints…

CHECK (condition)

Example: Define the student table
CREATE TABLE STUDENT
( STUID                      CHAR(5),
LNAME                     CHAR(10) NOT NULL,
FNAME                     CHAR(8),
MAJOR                     CHAR(7) CHECK (MAJOR IN (‘HIST’,’ART’,’CIS’)),
CREDITS                  INTEGER CHECK (CREDITS > 0),
PRIMARY KEY (STUID)); Continue reading

Mar 13

Structured Query Language

Many database management systems support some version of structured query language (SQL). In some DBMSs (i.e., ORACLE) SQL is the primary data manipulation interface.  Consequently, SQL is a very important topic.  The purpose of this document is to introduce you to the major SQL statements and to show you how they work.  This document will concentrate primarily on ORACLE SQL; however, some attention also will be given to other versions of SQL.  This is not a complete reference of SQL.  If you are interested in a more detailed coverage I can suggest several good textbooks for outside reading.

SQL commands can be broken into the 3 following functional groups: .

Data Definition Language (DDL)

used to define the schema (structure) of the database