Linux Fun

SQL allows the nesting of one query inside another, but only in the WHERE and the HAVING clauses.  In addition, SQL permits a subquery only on the right hand side of an operator.

Example 1

Find the names and IDs of all faculty members who teach a class in room ‘H221’. You could do this with 2 queries as follows:
SELECT FACID FROM CLASS WHERE ROOM = ‘H221’;
—> RESULT:  F101, F102

SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (F101, F102);

or you could combine the 2 into a nested query:
SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (SELECT FACID FROM CLASS WHERE ROOM = ‘H221’);

Note that the nested SELECT is executed first and its results are used as the argument to the outer SELECTs IN clause.

Readmore…

A JOIN operation is performed when more than one table is specified in the FROM clause.  You would join two tables if you need information from both. You must specify the JOIN condition explicitly in SQL.  This includes naming the columns in common and the comparison operator.

Example 1

Find the name and courses that each faculty member teaches.
SELECT FACULTY.FACNAME, COURSENUM FROM FACULTY, CLASS WHERE FACULTY.FACID = CLASS.FACID;

When both tables have an attribute name in common, you must specify which version of the attribute that you are referring to by preceding the attribute name with the table name and a period.  (e.g., table‑name.col‑name).  This is called “qualification”.

It is sometimes more convenient to use an “alias” (an alternative name) for each table.  SQL specifies alias names in the FROM clause immediately following the actual table.  Once defined, you can use the alias anywhere in the SELECT where you would normally use the table name. Readmore…

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;

Readmore…

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;

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;

Readmore…

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;

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);

Readmore…

Example 1

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

Example 2

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

Readmore…

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;

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)); Readmore…

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

• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• DROP TABLE
• DROP INDEX Readmore…

Manajemen Struktur Basis Data

Tanggung jawab DBA dalam menangani struktur basisdata adalah :

1. Merancang skema
   DBA biasanya tidak terlibat dalam perancangan basisdata mulai dari awal. Oleh karena itu, setiap terjadi perubahan struktur basisdata yang berpengaruh pada skema / relasi antar tabel harus selalu dicatat
2. Mengawasi terjadinya redundancy
   Redundancy dapat terjadi pada dua hal, yaitu performance dan data integrity. DBA harus menetapkan prosedur tertentu untuk melakukan rekonsiliasi data untuk menghindari terjadinya redundancy
3. Melakukan pengawasan konfigurasi permintaan atas perubahan struktur basisdata
   DBA bertugas menyusun laporan secara berkala mengenai pemakai yang aktif, serta file dan data yang dipakai, dan metode akses yang digunakan. Disamping itu juga dicatat terjadinya kesalahan. Hal tersebut diperlukan untuk menentukan apakah diperlukan adanya perubahan struktur basisdata demi peningkatan performance
4. Menjadwalkan dan mengadakan pertemuan apabila terjadi perubahan struktur basisdata
5. Menerapkan perubahan skema
   Perubahan harus dilakukan pada basisdata ujicoba, agar pemakai dapat mengujinya sebelum diterapkan pada sistem yang sesungguhnya
6. Merawat dokumentasi pemakai
   Merawat dokumentasi DBA – untuk memperoleh informasi tentang perubahan yang telah dilakukan, bagaimana dan kapan dilakukan Readmore…