Basics of SQL

SQL stands for Structured Query Language, and is used to communicate with a database. This article will go through some of the basics of SQL!

1. Create Database

CREATE DATABASE database_name
CREATE DATABASE hero_database

2. Create Table

CREATE TABLE table_name (
   id INTEGER PRIMARY KEY,
   name TEXT,
   age INTEGER
);
CREATE TABLE heroes(
   id INTEGER PRIMARY KEY,
   name TEXT,
   age INTEGER,
   ability TEXT
);

3. Constraints

Used when a table is created (or altered later) to limit the type of data put inside. There are column level and table level restraints.

CREATE TABLE table_name (
   id INTEGER PRIMARY KEY,
   name TEXT constraint,
   age INTEGER constraint
);
CREATE TABLE heroes (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   age INTEGER NOT NULL,
   ability TEXT NOT NULL
);

Some common constraints are:

NOT NULL
# column cannot have a null value
UNIQUE
# all values in column are different
PRIMARY KEY
# combination of NOT NULL and UNIQUE
FOREIGN KEY
# uniquely identifies row in another table
CHECK
# ensures all values in column satisfy a specific condition
DEFAULT
# sets a default value for the column when no value is specified
INDEX
#  Used to create and retrieve data from the database quickly

4. Add column

ALTER TABLE table_name ADD COLUMN column_name TEXT;
ALTER TABLE heroes ADD COLUMN gender TEXT;

5. Drop Table

DROP TABLE table_name
DROP TABLE heroes

6. Select

SELECT column_name FROM table_name
SELECT ability FROM heroes

7. Distinct

Only selects unique values.

SELECT DISTINCT column_name FROM table_name
SELECT DISTINCT ability FROM heroes

8. Where

Selects data based on specified conditions.

SELECT column_name FROM table_name WHERE condition
SELECT name FROM heroes WHERE name = "Batman"

Can use AND and OR to specify more conditions as well.

SELECT name FROM heroes WHERE name = "Batman" OR "Superman"

9. Count

Gives you the amount of records that meets a specified condition.

SELECT COUNT(column_name) FROM table_name WHERE condition
SELECT COUNT(ability) FROM heroes WHERE ability = "ESP"

10. Order By

Orders the result in either ascending (default) or descending order of a column’s values.

SELECT "column_name" FROM "table_name" WHERE "condition" ORDER BY column_name ASC|DESC
SELECT name FROM heroes WHERE age > 16 ORDER BY age DESC

11. Group By

Groups results by a specified column.

SELECT COUNT(column_name) FROM table_name GROUP BY column_name
SELECT COUNT(ability) FROM heroes GROUP BY ability

12. Insert Into

Insert data into the table.

INSERT INTO table_name (name, age, gender, ability) VALUES ("name_value", age_value, "gender_value")
INSERT INTO heroes (name, age, gender, ability) VALUES ("Spiderman", 18, "male", "spider things")

13. Update

Update the information for data inside the table.

UPDATE table_name SET name = "new_name" WHERE name = "old_name";
UPDATE heroes SET name = "spoderman" WHERE name = "Spiderman";

14. Delete

Delete a row from the database.

DELETE FROM table_name WHERE condition
DELETE FROM heroes WHERE name="spoderman"

15. Select Into

Copies data from one table into another table.

SELECT column_name(s) INTO new_table FROM old_table WHERE condition
SELECT name INTO people FROM heroes WHERE name = "Batman"

16. Stored Procedure

For when you have common code that you run, you can store it and just call it.

To store it:

CREATE PROCEDURE procedure_name
   AS
sql_statement
   GO;
CREATE PROCEDURE select_psychics
   AS
SELECT * FROM heroes WHERE ability = "ESP"
   GO;

To call it:

EXEC procedure_name

17. Joins

There are four types of joins.

• INNER JOIN — returns all rows when there is at least one match in BOTH tables
• LEFT JOIN — Returns all rows from the left table, and the matched rows from the right table
• RIGHT JOIN — Returns all rows from the right table, and the matched rows from the left table
• FULL JOIN — Returns all rows when there is a match in ONE of the tables

SELECT column_name FROM table_name_1 INNER JOIN table_name_2 ON table_name_1.column_name = table_name_2.column.name

Typically one table has a column containing the foreign key for another table, like if you had a table of books and a table of authors, the table of books might have a column of author_ids. You then join that column on the id column of the other table.

You might query a joined table by doing:

SELECT heroes.name, people.name 
FROM
heroes
INNER JOIN 
people 
ON 
heroes.id = people.heroes_id

These are the basics on how to create a table, query information from it, and change things in it. Good luck working with tables whether its making your own, or using somebody else’s!

• *Edit

One that stumped me on an interview is apparently called the self join.

Say you had a table like this:

EMPLOYEE
emp_id | emp_name | emp_supervisor_id
00001    Ed              00003
00002    Edd             00003
00003    Eddy            00004
00004    Fred               -
00005    Fredd           00004
00006    Freddy          00004

Here Fred is the big boss. His subordinates are Fredd, Freddy, and Eddy. Eddy is also the boss of Edd and Ed. You are to return a table with the list of employees and the name of their employers. But for each row you do not see the name of the employee’s advisor, only their advisor’s id number. To get the advisors name you need to use a self join.

SELECT a.emp_id as “Emp_ID”, a.emp_name AS “Employee Name”
SELECT b.emp_id as “Sup_ID”, b.emp_name AS “Supervisor Name”
FROM employee a, employee b
WHERE a.emp_supervisor_id = b.emp_id

Here we are joining a table with itself on where emp_supervisor_id and emp_i overlap. Then in the first line you are getting all the employees and getting two columns, the employee id (aliased as Emp_ID) and the names of the employees (aliased as Employee Name). Then for the second table we get the employee ids (this time aliased as Sup_ID) and the employee names (aliased to Supervisor Name). The end result will be:

Emp_ID | Employee Name | Sup_ID | Supervisor Name
00001    Ed              00003         Eddy
00002    Edd             00003         Eddy
00003    Eddy            00004         Fred
00004    Fred               -
00005    Fredd           00004         Fred
00006    Freddy          00004         Fred