1. SQL Tutorial (2) Syntax, SELECT, WHERE, AND/OR/NOT

SQL Syntax

Database Tables

Database contains 1 or more tables

• each table is identified by a name
• tables contain records w/ data

Keywords are NOT case sensitive: select == SELECT

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

---

SELECT Statement

SELECT: used to select data from a database

• data returned is stored in a result table, called the result-set

Syntax

SELECT column1, column2, ...
FROM table_name;

• selecting column1, column2, ... from the table
  SELECT DISTINCT Statement
  : used to return only distinct values (exclude duplicate values)

SELECT DISTINCT column1, column2, ...
FROM table_name;

Number of different customer countries:

SELECT COUNT(DISTINCT Country) FROM Customers;

---

WHERE Clause

: to filter records → extract only records that fulfill a specified condition

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• can be used in UPDATE, DELETE, etc.
  Example:

SELECT * FROM Customers
WHERE Country = 'Mexico';

Text Fields vs. Numeric Fields

SQL requires single quotes around text values

Numeric fields should NOT be enclosed in quotes

SELECT * FROM Customers
WHERE CustomerID = 1;

Operators in WHERE clause
= Equal, > Greater than, < Less than, >= Greater than or equal, <= Less than or equal, <> (!=) Not equal,

BETWEEN Between a certain range, LIKE Search for a pattern, IN To search multiple possible values for a column

---

AND, OR and NOT Operators

WHERE clause can be combined w/ AND, OR, and NOT operators

• AND & OR operators can filter records based on more than 1 condition
• NOT operator displays a record if the condition is NOT TRUE

AND:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

NOT:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

---

This post is based on W3School's articles about SQL.