SQL Basics

Tutoring > Databases > SQL Basics

SQL (Structured Query Language) is a language for querying databases and manipulating data.

Syntax

SQL is not case sensitive. You can write SELECT or select, they are the same.
SQL statements end with a semi colon ";" though not all database systems require this, it's good to remember.
This is an example of a statement:

SELECT * FROM Customers;

This would select all fields from the table called "Customers". The star "*" means "all of something" or "every field".

When dealing with text fields, surround the text with quotes 'like this'. However, numbers do not require quotes. 1234

SELECT

Syntax : SELECT column_name,column_name

FROM table_name;

Select returns or "selects" fields from a specified table or tablles. You can be more specific if you want certain fields and/or certain tables.

For example:

SELECT First name, Last name, Phone number FROM Customers, Employees;

would select and display first and last names and phone numbers from two seperate tables, employees and customers.

DELETE

Syntax : DELETE FROM table_name
WHERE some_column=some_value;

Delete does just that, it removes whatever you specify.

For example : DELETE Address FROM Customers;

wil remove all Address fields from the table Customers.

If you want to remove all rows from a table but keep the table itself:

DELETE FROM table_name;
or
DELETE * FROM table_name;

FROM

From specifies which table you want to perform each operation. In the examples above we are working on the Customers and Employees tables.

WHERE

Syntax : SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

The WHERE clause is used to extract only those records that fulfill a specified criterion.

For example : SELECT * FROM Customers
WHERE Country='England';

would select all rows from the Customers table where the Country field says 'England'.

DISTINCT

Syntax : SELECT DISTINCT column_name,column_name
FROM table_name;

Distinct returns only values that are unique, so if two or more columns are the same, only one will be shown.

For example : SELECT DISTINCT City,Phone number

From Customers;

will select only unique cities and phone numbers from the Customers table, no duplicates.

AND & OR

The AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.

You can combine them using parenthesis().

For example : SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');

ORDER BY

Syntax : SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

The ORDER BY keyword will sort the results you ask for into an order, either descending or ascending. If the results are text, the ordering will be alphabetic, if the results are numerical, they will be sorted by value.

For example : SELECT City FROM Customers
ORDER BY Country;

will select cities from the Customers table and present them in alphabetic order.

SELECT * FROM Customers
ORDER BY Country DESC;

will select all from Customers and sort them in descending alphabetic order(Z -> A).

SELECT * FROM Employees
ORDER BY Country,CustomerName;

will select all from Employees table and sort them by Country first, then Customer name.

GROUP BY

Syntax : SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

GROUP BY will group the results of a query into a column or columns.

We put the column name we want to operate on in parenthesis ().

For example : SELECT City, Country, SUM(Sales)

FROM Customers

WHERE Country='Ireland'

GROUP BY City;

will group cities in Ireland from Customers table and display the sum of Sales.

An aggregate function will operate on results and display a single value based on that operation:

AVG() - Returns the average value of the results
COUNT() - Returns the total number of rows
FIRST() - Returns the first value
LAST() - Returns the last value
MAX() - Returns the largest value
MIN() - Returns the smallest value
SUM() - Returns the sum, adds them all together.

Syntax : SELECT AVG(column_name) FROM table_name

This will take the average of the column_name column and return that value.

For example : SELECT AVG(Height) AS HeightAverage FROM Buildings;

will return the average Height from the Buildings table and store it as HeightAverage.

As creates an alias for an operation you perform, kind of like a new variable to store data.

JOINS

Syntax : SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

Joins combine rows from two or more tables. We use the dot "." to specify a table, then a column.

Types of joins are:

INNER JOIN (same as JOIN) - will return all rows when there is at least one match in BOTH tables.
FULL JOIN - will return all rows when there is a match in ONE of the tables.
LEFT JOIN - will return all rows from the left table, and the matched rows from the right table.
RIGHT JOIN - wil return all rows from the right table, and the matched rows from the left table.

For example : SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

will display OrderIDs and OrderDate from the Orders table, and CustomerName from the Customers table.

INSERT INTO

Syntax : INSERT INTO table_name
VALUES (value1,value2,value3,...);

or : INSERT INTO table_name (column1,column2,column3,...)

VALUES (value1,value2,value3,...);

Inserts new rows into a specified table. The second syntax example above specifies which column the values go into. The column corresponds with the value, value1 will go into column1 etc.

For example : INSERT INTO Employyees(EmployeeName, EmployeeID, Address, City, PostalCode, Country)
VALUES ('Apple','Fowler','1234','Worcester','WR8 0EE','UK');

will insert the values into their corrsponding columns.

UPDATE

Updates rows in a table according to what you set.

For example : UPDATE Suppliers

SET Supplier_ID = 3456

WHERE Supplier_name = 'Orange';

will update the Supplier_ID to 3456 Suppliers table where the supplier name is Orange.

UNION

Syntax : SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Union combines the result of two or more SELECT statements.

For example: SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

will select all distinct cities from Customers and Suppliers tables, that is, if a city appears in both tables, it will only appear once.

If you want every city instnce to appear theen just write UNION ALL instead of UNION.

CREATE TABLE

Syntax : CREATE TABLE table_name (

column_name1 data_type(size),

column_name2 data_tyoe(size),

column_name3 data_type(size)

. . . . .

);

CREATE TABLE lets you create a new table in the database, specifying column names and what kind of data should go into the cells in each column.

For example : CREATE TABLE Students (

Student_ID int,

First_name varchar(255),

Last_name varchar(255),

Course varchar(255)

);

wil create a table with 4 columns in it.

Data types vary among which kind of SQL you use. A few data types you can use are:

int - a whole number (e.g. 47).
varchar(size) - variable type with maximum size indicated
decimal - a number with a decimal point (e.g. 3.14).
bit - allows for 0, 1 or NULL

Learn Computer Science

Developing a more interesting World

Free beginner - intermediate level computer science tutorials

SQL Basics