SQL Basics: Everything You Need to Know in One Short Guide

SQL, or Structured Query Language, is an essential tool for managing and manipulating data in relational databases. With its wide range of commands, SQL allows you to view, create, and delete databases and tables, modify data, and select data using various commands. In this comprehensive guide, we will cover the basics of SQL, including data modification, selection, and viewing, as well as more advanced concepts like joins. Whether you're a beginner or an experienced programmer, this guide will provide you with everything you need to know to get started with SQL.

Viewing, Creating, and Deleting Data

SHOW DATABASES - view available databases:

SHOW DATABASES;

CREATE DATABASE - create a new database:

CREATE DATABASE databasename;

DROP DATABASE - delete an entire database:

DROP DATABASE databasename;

SHOW TABLES - view all tables in a database:

SHOW TABLES;

CREATE TABLE - create a new table:

CREATE TABLE CUSTOMERS(

ID INT NOT NULL,

NAME VARCHAR (20) NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR (25),

SALARY DECIMAL(18, 2),

PRIMARY KEY (ID),

FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE

);

A FOREIGN KEY parameter can be added to indicate a relationship with a specific field of another (main) table. For FOREIGN KEY constraints, you can set up a CONSTRAINT, and add expressions for ON DELETE and ON UPDATE. These expressions indicate what will happen to the table if the FOREIGN KEY value changes in the related table.

Possible values for ON DELETE and ON UPDATE:

1. CASCADE: automatically deletes or updates rows in the dependent table when related rows in the main table are deleted or updated.
2. SET NULL: sets the value NULL for the foreign key column when a row in the main table is deleted or updated. (In this case, the foreign key column must support setting NULL)
3. RESTRICT or NO ACTION: rejects the deletion or modification of rows in the main table if there are related rows in the dependent table.
4. SET DEFAULT: sets the default value for the foreign key column when a related row in the main table is deleted. The default value is specified using the DEFAULT attribute.

DROP TABLE deletes a table:

DROP TABLE tablename;

CREATE VIEW creates a view that is similar to a regular table, but only a representation of it:

CREATE VIEW <view_name> AS

SELECT <col_name1>, <col_name2>, …

FROM <table_name>

WHERE <condition>;

DROP VIEW deletes a view.

DROP VIEW <view_name>;

Tables and views are both ways to represent data in a relational database, but they have some key differences. Tables are the primary way to store data in a database and can be modified using SQL commands. Views, on the other hand, are virtual tables that are based on the results of a SQL query. While views can be used to simplify data access and provide a convenient way to view data from multiple tables, they cannot be modified directly like tables can. Instead, modifications to a view are made by modifying the underlying tables.

Data Modification

INSERT INTO <table_name> - adds new information to the database.

INSERT INTO <table_name>

VALUES (<value1>, <value2>, <value3>, …);

UPDATE <table_name> SET - changes data in the table.

UPDATE <table_name>

SET <col_name1> = <value1>, <col_name2> = <value2>, ...

WHERE <condition>;

DELETE FROM <table_name> - deletes data from the table.

DELETE FROM Customers

WHERE CustomerName='Alex Cadence';

Data Selection

SELECT FROM and SELECT DISTINCT FROM are used to select data from a database. The former selects all data from a table, while the latter selects non-repeating data.

SELECT <col_name1>, <col_name2>, …

FROM <table_name>;

SELECT DISTINCT <col_name1>, <col_name2>, …

FROM <table_name>;

WHERE is used to specify a condition for UPDATE or SELECT.

SELECT * FROM course WHERE dept_name='Comp. Sci.';

SELECT * FROM course WHERE credits>3;

SELECT * FROM course WHERE dept_name='Comp. Sci.' AND credits>3;

HAVING is used to specify a condition for UPDATE or SELECT.

SELECT COUNT(course_id), dept_name

FROM course

GROUP BY dept_name

HAVING COUNT(course_id)>1;

 Unlike WHERE, HAVING can be used together with aggregate functions.

BETWEEN *<value1>* AND *<value2>* is used to select values within a certain range.

SELECT * FROM instructor

WHERE salary BETWEEN 50000 AND 100000;

LIKE is used together with WHERE to specify a pattern for selecting values.

SELECT * FROM course WHERE title LIKE '%to%';

SELECT * FROM course WHERE course_id LIKE 'CS-___';

IN is used together with WHERE to specify multiple values for WHERE instead of one.

SELECT * FROM student

WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Elec. Eng.');

GROUP BY is used to group output values. It is used together with aggregate functions COUNT, MAX, MIN, SUM, AVG, etc.

SELECT <col_name1>, <col_name2>, …

FROM <table_name>

GROUP BY <col_namex>;

ORDER BY is used to sort results by a certain value. Additionally, ASC or DESC can be specified to sort in ascending or descending order.

SELECT * FROM course ORDER BY credits;

SELECT * FROM course ORDER BY credits DESC;

Table Joins

(INNER) JOIN - used to join tables and select data that exists in both tables.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

LEFT (OUTER) JOIN - used to join tables and select all data that exists in the first (left) table, regardless of whether they exist in the second (right) table.

SELECT u.id, u.name, d.name AS d_name

FROM users u

LEFT OUTER JOIN departments d ON u.d_id = d.id;

RIGHT (OUTER) JOIN - used to join tables and select data that exists in the second (right) table, regardless of whether they exist in the first (left) table.

SELECT u.id, u.name, d.name AS d_name

FROM users u

RIGHT OUTER JOIN departments d ON u.d_id = d.id;

FULL OUTER JOIN - used to join tables and select all data from both tables.

SELECT Customers.CustomerName, Orders.OrderID

FROM Customers

FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID

ORDER BY Customers.CustomerName;

INNER JOIN returns only the matching records from both tables. LEFT OUTER JOIN returns all records from the left table and only the matching records from the right table. RIGHT OUTER JOIN returns all records from the right table and only the matching records from the left table. FULL OUTER JOIN returns all records from both tables.

Additional commands

USE <database name> - select the database to work with.

SOURCE <file.sql> - execute commands that are in the sql file.

DESCRIBE <table name> - view information about the table.

Conclusion

SQL is a powerful tool for managing and manipulating data in relational databases, and it plays an essential role in many industries. In this guide, we've covered the basics of SQL, including data modification, selection, and viewing, as well as more advanced concepts like joins. Whether you're a beginner or an experienced programmer, this guide has provided you with everything you need to know to get started with SQL. By learning and mastering SQL, you will be able to effectively manage and analyze data, which is a valuable skill in today's data-driven world.

Thanks for reading and happy creating!

I hope this article has been helpful for you. Thank you for taking the time to read it.

To keep the inspiration flowing, check out my other articles. Let's continue to learn and grow together!