Introduction to SQL Database
What is a SQL database?
SQL databases, also known as relational databases, are systems that store collections of tables and organize structured sets of data in a tabular columns-and-rows format, similar to that of a spreadsheet.
The databases are built using structured query language (SQL), the query language that not only makes up all relational databases and relational database management systems (RDBMS), but also enables them to “talk to each other”.
Why do SQL Databases exist?
The history of database technology / relational databases
SQL was invented as a language in the early 1970s, which means SQL databases have been around for as long as the Internet itself. Dubbed the structured English query language (SEQUEL), SQL was created to streamline access to relational database systems and to assist with the processing of information. Today, SQL remains one of the most popular and widely used query languages in open-source database technology due to its flexibility, ease of use, and seamless integration with a variety of different programming languages. You’ll find SQL being used throughout all types of high-performing, data-centric applications.
What’s the difference between SQL and NoSQL databases?
SQL databases organize data in columns and rows. The rows contain data sets that apply to each item, while the columns define the different properties of those items, such as product ID, name, quantity, and unit price. In contrast, NoSQL databases store all incoming data in a single document with no overt structure. This makes SQL databases conducive to storing structured forms of data while NoSQL databases are equipped to handle large volumes of structured, semi-structured, and unstructured data from non-traditional sources.
What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulate databases
- SQL became a standard of the American National Standards Institute (ANSI) in 1986 and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
Some of The Most Important SQL Commands
- SELECT – extracts data from a database
- UPDATE – updates data in a database
- DELETE – deletes data from a database
- INSERT INTO – inserts new data into a database
- CREATE DATABASE – creates a new database
- ALTER DATABASE – modifies a database
- CREATE TABLE – creates a new table
- ALTER TABLE – modifies a table
- DROP TABLE – deletes a table
- CREATE INDEX – creates an index (search key)
- DROP INDEX – deletes an index
The SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;
The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:
Syntax
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
Look at the following two tables:
“Persons” table:
Syntax
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
The SQL WHERE Clause
The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, … FROM table_name WHERE condition;
Example
SELECT * FROM Customers WHERE Country=’Mexico’;
SELECT * FROM Customers WHERE CustomerID=1;
The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
- The NOT operator displays a record if the condition(s) is NOT TRUE.
AND Syntax
SELECT column1, column2, … FROM table_name WHERE condition1 AND condition2 AND condition3 …;
OR Syntax
SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3 …;
NOT Syntax
SELECT column1, column2, … FROM table_name WHERE NOT condition;
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
