I wanted to do a brief overview on SQL since I have not utilized it in a while.
SQL (Structured Query Language) is a language for managing data in a relational database. In a relational database, each table contains one or more data categories in columns. Each table has a unique primary key, which identifies the information in a table. The relationship between tables can be set with foreign keys.
Three Operators Used to Manipulate Database Schema
CREATE TABLE is used to create table in the database. It consists of the names of the columns and the data type the column will be accepting.
CREATE TABLE movies ( id INTEGER PRIMARY KEY, name TEXT );
ALTER TABLE is used to modify the table.
ALTER TABLE movies ADD rating INTEGER AFTER name;
DROP TABLE is used to delete a table.
Four Main Data Manipulation
- Retrieve values from one or more rows
If we want to select all the names from the movies table:
SELECT name FROM movies
If we want to retrieve all the columns of data from the table, we use * in place of listing all the column names individually.
SELECT * FROM movies
- Insert a row into the table
INSERT INTO movies VALUES (1, “Up”, 98);
- Update values in one or more existing rows
UPDATE movies SET rating = 91 WHERE ID = 1;
- Delete one or more rows
DELETE FROM movies WHERE ID = 1;
JOIN is used to combine rows from two or more tables. There are different types of
LEFT JOIN aka
LEFT OUTER JOIN
- Returns all records from the left table and matched records from right table
- Result is NULL from right side if there is no match
RIGHT JOIN aka
RIGHT OUTER JOIN
- Returns all records from the right table and matched records from left table
- Result is NULL from left side when there is no match
FULL OUTER JOIN
- Return all records there is a match in either left or right table records
- Return records that have matching values in both tables
To learn further about SQL, here are some links: