Day 1 - Relational Databases and SELECT

📌 Key Takeaways

• A relational DB stores data in tables (rows × columns) and links tables with primary keys (PK) and foreign keys (FK).
• SQL is divided into DML, DDL, DCL, and TCL. SELECT is usually classified as DML, though it is sometimes called DQL separately.
• AS can be omitted in an alias, and to preserve spaces, case, or special characters you wrap the alias in double quotes.
• DISTINCT removes duplicates across the whole combination of the SELECT list. NULL also counts as a single value and is kept just once.
• Arithmetic with NULL gives NULL. But Oracle's concatenation operator || skips NULL as if it were an empty string.

The Skeleton of a Relational Database

The model you drew with entities, attributes, and relationships in Subject 1 becomes tables, columns, and keys in a real DB.

• A primary key uniquely identifies a single row. Values cannot repeat, and NULL is not allowed.
• A foreign key references the primary key of another table. EMP.DEPTNO can only hold a department number that exists in DEPT (or NULL).
• The order of rows inside a table has no meaning. If you need sorted results, you must use ORDER BY (Week 4).

All examples in this note use the three tables below.

• Dates in the example results appear as YYYY-MM-DD. Oracle's default date display depends on the environment setting (NLS), so the notes were run with the session set to this format.

Types of SQL Statements

• Some textbooks single out SELECT and call it DQL (Data Query Language). If DQL is not among the choices, treat it as DML.
• TRUNCATE deletes data but is DDL. It is easy to confuse with DELETE. The difference is covered in Week 7.
• This week covers SELECT only. The other statements are left for Week 7.

The Basic Structure of SELECT

SELECT  [DISTINCT] column, expression, ...     ← what to show
FROM    table                                   ← where to get it from

• SELECT and FROM are required. Conditions (WHERE) and sorting (ORDER BY) come after them.
• Keywords, table names, and column names are case-insensitive. select ename from emp is the same. String values inside single quotes are case-sensitive.
• The ; at the end of a statement is a marker telling the SQL tool "this is where one statement ends."

* fetches all columns in the order defined in the table.

SELECT * FROM DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

You can also list just the columns you need, separated by commas. A calculation can go in a column position too.

SELECT ENAME, SAL, SAL * 12 AS ANNUAL FROM EMP WHERE DEPTNO = 10;

ENAME             SAL     ANNUAL
---------- ---------- ----------
CLARK            2450      29400
KING             5000      60000
MILLER           1300      15600

• WHERE DEPTNO = 10 is a condition that picks only department 10. It was added to keep the example short; details come in Day 5.
• A calculation does not change the data in the table. It is a value created only on the result screen.

Aliases

If you don't name a calculation, the expression itself becomes the column heading. An alias changes the heading.

SELECT ENAME AS "Employee Name", SAL "Monthly", SAL * 12 Yearly FROM EMP WHERE DEPTNO = 10;

Employee Name    Monthly     YEARLY
------------- ---------- ----------
CLARK               2450      29400
KING                5000      60000
MILLER              1300      15600

Using an alias that contains a space without quotes causes an error. Employee is read as the alias, and FROM is expected next, but Name shows up instead.

SELECT ENAME Employee Name FROM EMP WHERE DEPTNO = 10;

SELECT ENAME Employee Name FROM EMP WHERE DEPTNO = 10
                      *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

• Double quotes are needed for: spaces, preserving case, special characters, and using a reserved word as an alias.
• Single quotes are for string values. Using them for an alias, like SAL 'MONTHLY', is an error. Remember: single quotes = values, double quotes = names.

⚠️ An alias created in SELECT cannot be used in WHERE. WHERE is processed before SELECT, so the alias does not exist yet at that point. The execution order is covered in Week 4, Day 2.

SELECT EMPNO, ENAME, SAL AS PAY FROM EMP WHERE PAY > 3000;

SELECT EMPNO, ENAME, SAL AS PAY FROM EMP WHERE PAY > 3000
                                               *
ERROR at line 1:
ORA-00904: "PAY": invalid identifier

DISTINCT — Removing Duplicate Rows

DISTINCT is written once, right after SELECT. Looking at the 14 employees' jobs without duplicates gives five.

SELECT DISTINCT JOB FROM EMP ORDER BY JOB;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

• ORDER BY was added to fix the result order. Without it, the order of DISTINCT results is not defined.

With two or more columns, rows are merged into one only when the entire combination is the same. DISTINCT does not apply to just the first column.

SELECT DISTINCT DEPTNO, JOB FROM EMP ORDER BY DEPTNO, JOB;

    DEPTNO JOB
---------- ---------
        10 CLERK
        10 MANAGER
        10 PRESIDENT
        20 ANALYST
        20 CLERK
        20 MANAGER
        30 CLERK
        30 MANAGER
        30 SALESMAN

• 14 rows became 9. The CLERKs in department 20 (SMITH, ADAMS) and the four SALESMEN in department 30 each collapsed into one row.
• There is no syntax like SELECT DEPTNO, DISTINCT JOB that puts it in the middle.

NULL means "no value," but DISTINCT treats NULLs as the same and keeps just one row.

SELECT DISTINCT COMM FROM EMP ORDER BY COMM DESC;

      COMM
----------
 
      1400
       500
       300
         0

• There are 10 employees whose COMM is NULL, but the result shows them only as a single blank line at the top. Oracle's descending sort puts NULL first.
• 0 and NULL are different values. TURNER's COMM of 0 takes its own line.

Arithmetic Operations

SELECT ENAME, SAL + 100 * 2, (SAL + 100) * 2 FROM EMP WHERE EMPNO = 7369;

ENAME       SAL+100*2 (SAL+100)*2
---------- ---------- -----------
SMITH            1000        1800

• SAL + 100 * 2 is 800 + 200, and (SAL + 100) * 2 is 900 × 2.
• Oracle computes through the decimal point even when dividing integers. 10 / 4 is 2.5, not 2.

DUAL and the Concatenation Operator ||

Even for a calculation unrelated to any table, Oracle traditionally requires a FROM. That is what DUAL is for.

• DUAL is a table with a single column DUMMY and a single row with the value 'X'. It is used to make sure exactly one row comes back.
• Oracle 23ai in the practice lab runs even without FROM, as in SELECT 1 + 2;. In versions before 23ai that is an error, so on the exam, assume the form with FROM DUAL.
• SQL Server natively uses SELECT 1 + 2 without FROM.

|| joins values into a single string. Numbers and dates are converted to text and joined.

SELECT ENAME || ' has the job ' || JOB || '.' AS INTRO FROM EMP WHERE DEPTNO = 10;

INTRO
---------------------------------------
CLARK has the job MANAGER.
KING has the job PRESIDENT.
MILLER has the job CLERK.

• SQL Server joins strings with + instead of ||. The CONCAT function works in both DBs (Day 2).

When NULL Is Involved, the Result Is NULL Too

NULL is neither 0 nor blank; it is an "unknown value." Adding anything to an unknown value gives an unknown result.

SELECT ENAME, SAL, COMM, SAL + COMM, SAL * 12 + COMM AS ANNUAL FROM EMP WHERE DEPTNO = 30;

ENAME             SAL       COMM   SAL+COMM     ANNUAL
---------- ---------- ---------- ---------- ----------
ALLEN            1600        300       1900      19500
WARD             1250        500       1750      15500
MARTIN           1250       1400       2650      16400
BLAKE            2850
TURNER           1500          0       1500      18000
JAMES             950

• BLAKE and JAMES have a NULL COMM, so both SAL + COMM and the annual figure become NULL (blank). Having a SAL value doesn't help.
• TURNER's COMM is 0, so it calculates normally. 0 is a value.
• To calculate with NULL turned into 0, use NVL(COMM, 0) (Day 4).

⚠️ Oracle's || is an exception. Unlike arithmetic, it skips NULL as if it were an empty string. 'A' || NULL || 'B' is 'AB', not NULL. Standard SQL and SQL Server's + concatenation return NULL.

SELECT ENAME || NULL || JOB FROM EMP WHERE EMPNO = 7839;

ENAME||NULL||JOB
-------------------
KINGPRESIDENT

Tomorrow covers character functions that cut and join strings, and numeric functions that round and truncate.

📖 Glossary

• Table : A set of data made of rows and columns. It is what a model's entity becomes in the DB.
• Primary key (PK) : A column that uniquely identifies a row. Duplicates and NULL are not allowed.
• Foreign key (FK) : A column that points to another table's primary key. It is the link that connects tables.
• DML·DDL·DCL·TCL : The categories of statements for data manipulation, structure definition, privilege control, and transaction control.
• Alias : A name given to a result column. AS can be omitted.
• DISTINCT : A keyword that keeps only one copy of completely identical rows in the SELECT result.
• DUAL : Oracle's default one-row, one-column table. Used for calculations without a table.
• NULL : A marker meaning no value or unknown. Different from 0 or a space character.