WD - SQL

• To select data in an sql data base what 3 commands are needed and what they do

  SELECT selects columns that will be ouputted if the where is met

  FROM Database being acessed

  WHERE conditional statement
• If you want to have multiple conditions need to be met what must follow the WHERE command?

  AND
• YOu can include a select data inside a conditional, how is this done? (use population >) and select name for first pop for seconf and all from world database

  SELECT name

  FROM world

  WHERE population >

  (SELECT pop

  FROM world

  WHERE name='Algeria')
• If you want to have multiple conditions and that if any condition is met the corresponding data is selected?

  OR
• LIKE is used in a WHERE clause to search for a pattern
• LIKE searches for a pattern, what characters have different values than usual in a pattern being specified, and what do they represent?

  percent any number of characters as low as 0

  Underscore one character
• Patterns in LIKE clauses are surrounded with?

  quotation marks
• To insert data into a database what is the syntax:

  INSERT INTO <database name>

  (<column1 value> <column2 value> <column3 value>)

  VALUES (<value1>,<value2>, <value3>)
• TWO rules of the INSERT INTO clause?

  Unless you have a full record you must specify column names

  Any string values must be specified with quotation marks
• To delete data what is the syntax?

  DELETE FROM <database name>

  WHERE <field name> = <condition>
• Updating data syntax?

  UPDATE <database name>

  SET <field name1> = <value1>, <field name2> = <value2>, <...> = <...>

  WHERE <field name> = <condition>
• You can select data in a table without specifing the table by using the following formatting

  SELECT <table name>.<column name>
• Joining tables syntax?

  JOIN <table1 name>

  ON <table2 name>.<table2 value> = <table1 name>.<table2 value>

  WHERE <condition>
• DROP TABLE <table name> does what?

  DEletes the entire table and all stored data
• Dates are there own type within SQL and therefore can be compared with logical operators
• ORDER BY sorts results of the preceding clause in either ascending or descending order, ASC and DESC.You must specify the column it uses to assess an order
• How would you create a table (books) with columns (book,author,release date) in sql?

  CREATE TABLE books (book TEXT, author TEXT, release date TEXT);
• After each full command/statement in SQL you must have a semi-colon so the next section is recognized as seperate
• SUM can be used to sum data, often used in select statements
• • (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
• HOw to add column to table:ALTER TABLE table_nameADD [COLUMN] column_definition;
• SUBSTR extracts a substring
• SUBSTR syntax

  SUBSTR (target, startindex, extractnumbers)
• INSTR returns the position of the first occurrence of a string in another string