SQL Fundamentals Quick Summary

Inner Join

Contains only rows with associated relationships

SELECT field1, RTRIM(field2) AS f2
FROM table_one INNER JOIN table_two
ON table_one.table_two_id = table_two.id
GROUP BY field1, f2
HAVING COUNT(table_two.credit) > 2
WHERE condition1 AND condition2 AND field1 LIKE '%@hotmail.com'
ORDER BY field3;

Self Join

Can be used as an alternative to subqueries

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers AS c1, customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim';

Outer Join

Includes rows that have no associated relationships

• LEFT refers to selecting all rows from the table on the left side of OUTER JOIN

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

UNION

Connects multiple queries, but each query must have the same columns

INSERT INTO

INSERT INTO table1(
    field1,
    field2
)
VALUES('a', 2);

VALUES can be replaced with SELECT to insert retrieved data

SELECT INTO can be used to export data from one table to another table

Table Operations

CREATE TABLE ...
DROP TABLE ...

ALTER TABLE table1
ADD field CHAR(20)
DROP COLUMN deletedField

Views

Used for reusing SQL statements, generally used to simplify complex query statements or filter data

CREATE VIEW cv AS
SELECT ...
WHERE ...

SELECT field1, field2
FROM cv
WHERE ...

- The two where clauses are automatically combined

Transactions

Batch SQL statements either execute completely or none at all, or rollback to a specific statement

Used to ensure data integrity

Transactions can be used to manage insert, delete, and update statements

START TRANSACTION
...
SAVEPOINT delete1
...
ROLLBACK TO delete1
COMMIT;

- You can COMMIT where you want to end, and determine whether an error occurred to perform rollback

Important Concepts

Primary Key Unique identifier for each row, values cannot be reused, NULL is not allowed, users cannot modify it, automatically generated, keyword PRIMARY KEY

Foreign Key Ensures that the referenced value exists in another table’s column associated with this column, otherwise an error will occur, keyword

- In definition
REFERENCE table1 (field1)
- In modification
ALTER TABLE table1
ADD CONSTRAINT
FOREIGN KEY (user_id) REFERENCES user (id)

Unique Constraint Guarantees that values in the column are unique, can contain NULL values, can be modified and updated, uses keyword UNIQUE

Check Constraint Restricts the range of values

ADD CONSTRAINT CHECK ( field > 0 )

Index Pre-sorts appropriately, when searching for this sorted column it will be very fast, primary key data itself is sorted, improves search performance, but reduces insert, delete, and update performance because indexes are dynamically updated during these operations

Keyword CREATE INDEX

Trigger Associated with insert, delete, and update operations on specific tables, constraints have better performance, consider using constraints first before using triggers, can be used to maintain database integrity