SELECT WHERE

• SQL


  Structure Query Language - an international standard language to create, read, update, delete data from a database and its entities
• Many data-driven applications/websites use SQL to retrieve, format, report, insert, delete and to modify data for users
• SQL is designed to be human-readable
• SQL commands


  • SELECT name FROM Customer WHERE customerNo = 12345;
  • UPDATE Student SET Status = "Confused" WHERE studentNo = 789123;
  • INSERT INTO Pet(name, type) VALUES("Fido","Doggo");
  • DELETE FROM Minions WHERE Death = 1;

  
• 3 Main Parts of SQL


  • Data Manipulation Language (DML)
  • Data Definition Language (DDL)
  • Data Control Language (DCL)
• Data Manipulation Language (DML)


  Commands that maintain and query a database, to maintain data (Create, Read, Update, Delete) and data query (through the use of SELECT statements to read information)
• Data Definition Language (DDL)


  Schema creation and modification, used to define and manage database structures (CREATE TABLE, ADD INDEX, etc.)
• Data Control Language (DCL)


  For creating user accounts, managing permissions, security, etc.
• Data Query and Manipulation


  • Data Query (Fetching data sets depending on certain criteria, using SELECT statements)
  • Data Manipulation (Insert, Update, Delete)
• SELECT


  The most common SQL statement, reads information from the database that matches the specified criteria
• SELECT * FROM... retrieves an entire table, with * meaning 'everything'
• SELECT column(s) FROM table(s) [ WHERE query ] is the general syntax for SELECT statements
• SELECT NAME FROM Pet; retrieves a single column of records

  
• SELECT PetID, NAME FROM Pet; retrieves multiple columns of records

  
• WHERE clause


  Used to create conditions for the output data to match
• SELECT * FROM Pet WHERE TYPE = 'Dog';


  • Retrieves all columns, but only for records where the Type is 'Dog'

  
• SELECT * FROM Pet WHERE AGE = 3;

  • Retrieves all columns, but only for records where the Age is 3
• The WHERE clause is not only used in SELECT statements, but also in UPDATE, DELETE, etc.
• The WHERE clause can be combined with AND and OR relational operators
• AND


  Represents an intersection (A Ç B) of the data sets
• OR


  Represents the union (A È B) of the data sets
• WHERE is not exclusive to SELECT
• The WHERE clause is not only used in SELECT statements
• It is also used in UPDATE, DELETE, etc.
• Learn to query – using online tool on MySQL
• make sure this part shows 'mySQL' and not 'SQL'
• Relational Operators in the WHERE clause

  • The WHERE clause can be combined with AND and OR relational operators
  • AND, OR are used to display records in the entity table if all the conditions are TRUE
• AND


  Represent an intersection (A Ç B) of the data sets
• OR

  To represent the union (A È B) of the data sets
• SELECT * FROM Pet WHERE (Type = 'Turtle') OR (Type = 'Owl')


  Retrieve all relevant records, where Type = 'Turtle', 'Owl' or both (UNION condition) is met
• SELECT NAME FROM Pet WHERE (TYPE = 'Dog') AND (Age > 5)


  Retrieve all Names, where Type = 'Dog' and Age >5 (INTERSECTION condition) is met

  
• SQL keywords and Statements are NOT case sensitive
• Text Fields vs. Numeric Fields: SQL requires single quotes '…' around text values, numeric fields should not be enclosed in quotes
• Semicolon (;) after SQL statements is optional for a single SQL statement, must use ; with multiple SQL statements
• SELECT * FROM Pet WHERE TYPE = 'DDog'

  
• SELECT * FROM Pet WHERE TTYPE = 'Dog'