Introduction to SQL
Basics
Release 1 of the SQL specs is 1986. Subsequent releases are SLQ/92 and SQL/99.
Relational databases are built on tables linked by unique primary and foreign keys.
SQL commands are grouped according to their usage:
- Data Definition Language (DDL; eg. CREATE, ALTER, DROP)
- Data Manipulation Language (DML; eg. INSERT, UPDATE, DELETE)
- Data Query Language (DQL; eg. SELECT)
- Data Control Language (DCL; ALTER PASSWORD, GRANT, REVOKE, CREATE SYNONYM)
- Commands to administrate data (CAD; eg. START/STOP AUDIT)
- Commands for transactional control (CCT; eg. COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION)
_TBL is traditionally used as suffix for tables, and _INX for indexes.
A primary key is a unique record ID.
A schema is a collection of objects associated with a users, eg. user1.employees_tbl.
To create a table in a database: CREATE TABLE EMPLOYEES_TBL (ID_EMP CHAR(9) NOT NULL, NAME VARCHAR2(40) NOT NULL);
"The relational model is thus named, not because you can relate tables to one another (a popular misconception), but as a reference to the relationships between the columns in a table. These are the relationships that give the model its name; in other words, relational means that if several values belong to the same row in a table, they are related. The way columns are related to each other defines a relation, and a relation is a table (more exactly, a table represents one relation)." The Art of SQL By Stéphane Faroult, Peter Robson
Reading Notes
Sams Teach Yourself SQL in 10 Minutes, Fourth Edition
SELECT DISTINCT vend_id FROM Products;
SELECT prod_name FROM Products LIMIT 5;
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
SELECT prod_name -- this is a comment
FROM Products;
# This is a comment
SELECT prod_name FROM Products;
/* SELECT prod_name, vend_id
FROM Products; */
SELECT prod_name FROM Products;
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
Note:
- When specifying an ORDER BY clause, be sure that it is the last clause in your SELECT statemen
- Although more often than not the columns used in an ORDER BY clause will be ones selected for display, this is
actually not required. It is perfectly legal to sort data by a column that is not retrieved.
The biggest advantage of IN is that the IN operator can contain another SELECT statement, enabling you to build highly dynamic WHERE clauses. You’ll look at this in detail in Lesson 11, “Working with Subqueries.”
Calculated fields
concat: SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')' AS vend_title FROM Vendors ORDER BY vend_name;
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
Unlike SQL statements, SQL functions are not portable.
Aggregate Functions: AVG(), COUNT(), MIN(), MAX(), SUM()
COUNT() can be used two ways:
• Use COUNT(*) to count the number of rows in a table, whether columns contain values or NULL values.
• Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULL values.