DBD

• SQL SELECT

  The SELECT statement is used to select data from a database
• SELECT Statement Syntax:

  SELECT columnName, columnName, ... FROM tableName, tableName;
• Example SELECT: Return the Customer Name and City they are from, from Customers table


  • SELECT CustomerName, City FROM Customers
• Example SELECT: Return all columns from the Customers table


  • SELECT * FROM Customers
• SQL SELECT DISTINCT

  The SELECT DISTINCT statement is used to return only distinct values
• SELECT DISTINCT Syntax:

  • SELECT DISTINCT columnName, columnName, ... FROM tableName, tableName;
• Example DISTINCT: Select the different countries from the Customers table


  • SELECT DISTINCT Country FROM Customers
• Example DISTINCT in COUNT(): Count Distinct Countries

  • SELECT COUNT(DISTINCT Country) FROM Customers
• SQL WHERE

  The WHERE Clause is used to filter records. Used to extract only those records that fulfill a specific condition.
• WHERE Clause Syntax: SELECT columnName, columnName, ... FROM tableName, tableName WHERE condition;
• Example WHERE: Select all customers from Mexico


  • SELECT * FROM Customers WHERE Country = 'Mexico'
• Text Fields vs. Numeric Fields: text fields require 'single quotes', numeric fields don't require single quotes, so you can just type the number
• Example WHERE: Select all customers where CustomerID = 1


  • SELECT * FROM Customers WHERE CustomerID = 1
• Operators in the WHERE Clause


  • = Equal to
  • < Less Than
  • > Greater Than
  • <= Less than Equal to
  • >= Greater than Equal to
  • <> Not equal to
  • BETWEEN
  • LIKE
  • IN
• SQL ORDER BY

  The ORDER BY keyword is used to sort the result-set in ascending or descending order
• ORDER BY Syntax: SELECT columnName, columnName, ... FROM tableName, tableName ORDER BY columnName, columnName, ... ASC|DESC;
• Example ORDER BY: Sort the products by price

  • SELECT * FROM Products ORDER BY Price
• ORDER BY sorts records in default order (ascending). To sort records in descending order use DESC.
• For string values the ORDER BY keyword will order alphabetically (a - z) which means DESC(z - a)
• Example: ORDER BY several columns (1st column then, 2nd column if 1st column contains duplicates)

  • SELECT * FROM Customer ORDER BY Country, CustomerName
• Example ORDER BY: You can use both ASC & DESC, to sort 1 column ASC, and 1 column DESC


  • SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC
• SQL AND

  The WHERE clause can contain one or many AND operators. The AND operator is used to filter records based on more than one condition.
• AND in WHERE Syntax: SELECT columnName, columnName, ... FROM tableName, tableName, ... WHERE condition1 AND condition2 AND condition3 ...
• Example: select all customers from Spain that starts with the letter 'G'

  • SELECT * FROM Customers WHERE Country = 'Spain' AND CustomerName LIKE 'G%'
• AND operator displays a record if ALL the conditions are TRUE. OR operator displays a record if ANY of the conditions are TRUE.
• Example AND in WHERE: the following SQL selects all fields from Customers where country is "Germany" AND City is "Berlin" AND PostalCode is higher than 1200


  • SELECT * FROM customers WHERE Country = 'Germany' AND City = 'Berlin', AND PostalCode > 1200
• Example AND + Nested OR: select all Spanish customers that start with either "G" or "R"


  • SELECT * FROM Customers WHERE Country = 'Spain' AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%')
• SQL OR

  OR operator is used to filter records based on more than one condition
• OR in WHERE Syntax: SELECT columnName, columnName, ... FROM tableName, tableName, ... WHERE condition1 OR condition2 OR condition3 ...
• Example OR in WHERE: select all customers from Germany or Spain


  • SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'Spain'
• SQL NOT

  NOT operator is used in combination with other operators to give the opposite result, also called the negative result.
• NOT Syntax: SELECT columnName, columnName, ... FROM tableName, tableName WHERE NOT condition;
• Example NOT: select only the customers that are NOT from Spain


  • SELECT * FROM Customers WHERE NOT Country = 'Spain'
• Example NOT: select customers that does not start with the letter 'A'


  • SELECT * FROM Customers WHERE CustomerName NOT LIKE 'A%'
• Example NOT, AND & BETWEEN: select customers with a CustomerID not between 10 and 60


  • SELECT * FROM Customers WHERE CustomerID NOT BETWEEN 10 AND 60
• Example NOT IN: select customers that are not from Paris or London

  • SELECT * FROM Customers WHERE City NOT IN ('Paris', 'London')
• Example NOT: select customers with a CustomerID not greater than 50

  • SELECT * FROM Customers WHERE NOT CustomerID > 50
• SQL INSERT INTO

  Used to insert new records into a table
• Syntax: INSERT INTO tableName (columnName, columnName, ...) VALUES (value1, value2, ...); INSERT INTO tableName VALUES (value1, value2, value3, ...);
• Example: insert a new record in the customers table


  • INSERT INTO Customers (CustomerName, City, Country) VALUES('Cardinal', 'Stavenger', 'Norway')