Intro to SQL Databases
Terminology
Table — A collection of related data, which consists of rows (instances) and columns (attributes).
Row — Represents a single item within a table. For example, in a Users table, each row represents a particular user.
Column — Represents each “attribute” for each user. For example, below is a snippet from my code. Each column is a different attribute, whether it is a user’s first name, email, or password.
Primary Key — Each row is given a non-repeating number to identify a user from another user. This key is particularly useful when it is used as a “foreign key” in other tables to provide a relationship between tables
Foreign Key — Is an attribute on a join table (connects tables together to form a relationship) For example, in a table of Tasks, a row “Clean up room” belongs to a User named “John”
SQL
SQL is a relational database and stands for ‘Structured Query Language’. A few different kinds SQL databases include Postgres, Oracle, and MySQL. They differ slightly, but all perform the main functions such as Find, Insert, Update, and Delete. In SQL, the data is stored into a Table, an example below:
Each line of this code represents a column within the Users’ Table. Each “user” is represents a row in the database, filling in each section of the column, which is called an attribute.
Basic Commands
Creating a Table-
CREATE TABLE is one of the beginning steps to creating your database. A database can be composed to multiple tables, and each table can be connected to each other by a foreign key.
This code snippet above allowed us to created a table with different attributes, first name, last name and email. Each attribute represents a column within the table. The id will always be unique, to identify a particular user
Select and From-
SELECT first_name FROM users is a search command. SELECT is a keyword to determine which column to look at. FROM is a keyword to determine which table to look into. The return value would be all the users’ first names. But if we were to use:
The return value would be the whole table of users, including id, first name, last name, and email.
Insert-
In this snippet of code, we are inserting a new row into the Users table. In the first set of parenthesis, are the columns/attributes we want to be filled in. The second set are the values of each column/attribute.
Conclusion
This is just a short intro of SQL and some of its commands. As the database grows, so will the complexity and different commands needs to filter out and show what is needed of the database.
