Home > Uncategorized > SQL basics

SQL basics

For one to many relationships use one to crows feet many
For many to many relationships, use junction or linking table (intermediary table)
A – Atomic (all or nothing rule)
C – Consistent
I – Isolated (allow only 1 txn access)
D – Durable

CRUD
Create
Read
Update
Delete

1NF -> 2NF -> 3NF
1NF = no repeating values, no repeating groups
2NF = no non-key values based on just part of the composite key
3NF = no non-key values based on other non-key values

SELECT * FROM Employee WHERE Name = ‘Green%’ and name = ‘Red%’;
SELECT * FROM Employee WHERE Name in (‘John’,’Tom’);
SELECT * FROM Employee WHERE Name is NULL;
SELECT Description, ListPrice, Color FROM Product ORDER BY ListPrice DESC|ASC;
SELECT Employee WHERE Salary > 50000 ORDER BY LastName, FirstName;
SELECT COUNT(*) FROM Employee WHERE Salary > 50000;
SELECT MAX(ListPrice) FROM Product;
SELECT MIN(ListPrice) FROM Product;
SELECT AVG(ListPrice) FROM Product;
SELECT SUM(TotalDue) FROM Order WHERE CustomerID = 854;
SELECT COUNT(*), Color FROM Product GROUP BY Color;

Joining tables:
INNER JOIN Example:
Will match from DepartmentID that isn’t null and only if DepartmentID exists
from both tables.
Employee (ID, FirstName, LastName, HireDate, DepartmentID, …)
Department (DepartmentID, Name, Location, BudgetCode, …)
SELECT FirstName, LastName, HireDate, Employee.DepartmentID, Name, Location
FROM Employee INNER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

LEFT OUTER JOIN Example:
Will match from all Employee.DepartmentID and show from both tables.
Employee (ID, FirstName, LastName, HireDate, DepartmentID, …)
Department (DepartmentID, Name, Location, BudgetCode, …)
SELECT FirstName, LastName, HireDate, Employee.DepartmentID, Name, Location
FROM Employee LEFT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

RIGHT OUTER JOIN Example:
Will match from all Department.DepartmentID and show from both tables (NULL included).
Employee (ID, FirstName, LastName, HireDate, DepartmentID, …)
Department (DepartmentID, Name, Location, BudgetCode, …)
SELECT FirstName, LastName, HireDate, Employee.DepartmentID, Name, Location
FROM Employee RIGHT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

DML (data manipulation language; SELECT, INSERT, UPDATE, DELETE)
INSERT:
INSERT INTO Employee (FirstName, LastName, Department, Salary)
VALUES (‘Joe’,’Allen’,’Sales’,’45000′);

UPDATE:
UPDATE table SET column = value WHERE condition;
UPDATE Employee SET Email = ‘joe@abc.com’ WHERE EmployeeID = 734;

DELETE:
DELETE FROM table WHERE condition;
DELETE FROM Employee WHERE EmployeeID = 734;
DELETE FROM Employee; ## Will delete ALL ROWS from Employee
## Good Practice: Use SELECT to make sure you get the ROW(s) you want to UPDATE or DELETE;

DDL (Data Definition Language; CREATE, ALTER, DROP)
CREATE table (col1 def, col2 def, …)
ALTER TABLE Employee ADD Email VARCHAR(100);

DROP TABLE Employee;

Indexes (cost to writes; speeds reads)
Clustered Index – Sorts by PrimaryKey
Non-clustered index – PrimaryKey and add’l column
If you have 3 clustered indexes and a write op is performed, there will be 4 write ops

Pessimistic locking – table or record is blocked until txn is completed
Optimistic locking – add’l txns are able to read, but error (dirty read, rollback) may be
issued if data changes during txn

Stored Procedure
CREATE PROCEDURE HighlyPaid()
SELECT * FROM Employee
WHERE Salary > 50000
ORDER BY LastName, FirstName
END;

CALL HighlyPaid();

Stored Procedures can have parameters
CREATE PROCEDURE EmployeesInDept (IN dept varchar(50))HighlyPaid()
SELECT * FROM Employee
WHERE Department = dept <== parameter dept
ORDER BY LastName, FirstName
END;

CALL EmployeesInDept(‘Accounting’);
## Use stored procedures to prevent sql injection attacks

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: