Structured Query Language

September 28, 2022

SQL

SQL is the language we use to communicate to our database. SQL statements can be craed to read, create, update, or delete data from the database, as well as be used to define and alter the underlying table structures.

DATA MANIPULATION LANGUAGE (DML)

The Data Manipulation Language (DML) is a collection of SQL statements that query, create, update, and delete records

SELECT - used to query data from the database

INSERT - used to add records to the database

UPDATE - used to update records in the database

DELETE - used to delete records in the database

SELECT

SELECT <some fields>

FROM <some table>

WHERE <conditions>

GROUP BY <some fields>

HAVING <conditions on an aggregate>

ORDER BY <some fields>

SELECT <some fields>

Select all fields: *

Aggregates:

COUNT(*), SUM(fieldname), AVG(fieldname), MIN(fieldname), MAX(fieldname)

Rename field: AS

Remove duplicate rows: DISTINCT

FROM <some table>

Tables to pull data from;

multiple tables can be specified when doing a “join”

WHERE <conditions>

Text and dates go in single quotes Comparison operators: < , <= , = , > , >= , <>

Logical operators: AND, OR, NOT

Special conditions: IN(), LIKE, IS NULL, IS NOT NULL

GROUP BY <some fields>

Used to group by fields when using aggregate functions

HAVING <conditions on an aggregate>

Conditions on aggregate results go here instead of WHERE

ORDER BY <some fields>

Ascending is default and does not need to be specified; for descending, use DESC

Sample SELECTS

SELECT lastname, firstname from employees
SELECT count(*) FROM Customers
SELECT lastName, firstName, birthdate FROM Employees where birthdate > ‘1960-01-01’
SELECT lastName, firstName, birthdate FROM Employees where lastName LIKE ‘D%’
SELECT Country, count(*) FROM Customers group by Country;
SELECT department, ROUND(AVG(salary),2) FROM employees GROUP BY department HAVING AVG(salary) > 55000

Insert

The INSERT statement will allow us to add new records to the database

INSERT INTO tablename (field1, field2) VALUES (value1, value2)

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Donna', 'Smith', 'Information Technology', 67443)

Insert multiples

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Fred', 'Bailer', 'Finance', 83232),
('Selma', 'Dornwood', 'Accounting', 73241),
('Andy', 'Black', 'Information Technology', 32000)

Update

The UPDATE statement will allow us to update existing records in the database

UPDATE tablename SET field1=value1, field2=value2 WHERE fieldname3 = value3

UPDATE employees SET salary = 92303 WHERE id = 1

Delete

The DELETE statement will allow us to delete existing records in the database

DELETE FROM tablename WHERE field1 = value1

DELETE FROM employees WHERE id = 1

Multi Table Queries

Queries can take advantage of the relationships between tables by joining them together to return combined, related data.

Question to the database: List the names of the customers who purchased an Umbrella.

Where Style

SELECT first_name, last_name, item
  FROM customers, orders
WHERE customers.id = orders.customer_id AND item = 'Umbrella'

Join Style

SELECT first_name, last_name, item
  FROM customers INNER JOIN orders ON customers.id = orders.customer_id
 WHERE item = 'Umbrella