SQL W3

SELECT prod_name, prod_price FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BSR01'

The IN operator enables you to embed another sql statement instead of just specifying the values

SELECT * FROM Customers WHERE Country='Mexico';

The following SQL statement selects all the customers from the country "Mexico", in the "Customers" table:

Which statement is true about the DROP TABLE statement?

The DROP TABLE statement enables you to delete a table from a database. Correct The DROP TABLE statement enables you to delete a table from a database and if the table did not exist then attempting to DROP it would cause an error to occu

SELECT column1, column2, ... FROM table_name;

The Select statement is used to select data from a database.

Definition of 'fields'

The columns within a table

Definition of 'records'

The rows within a table

SELECT Name, Continent, Population FROM Country WHERE Name LIKE '_a%' ORDER BY Name;

Where the second character is a The first character can be anything

DROP TABLE test;

Dropping a table

CREATE TABLE test ( id INTEGER PRIMARY KEY, a INTEGER, b TEXT );

Creates an ID column along with the other columns

CREATE TABLE test ( a TEXT, b TEXT);

Creating a table with two columns named a and b

SELECT Name, Continent, Population FROM Country WHERE Name LIKE '%island&' ORDER BY Name;

% & Anything before and anything after

Breakdown of a SQL Database Table

- Every table is broken up into smaller entities called fields. . The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country. . A field is a column in a table that is designed to maintain specific information about every record in the table. - A record, also called a row, is each individual entry that exists in a table. . For example, there are 91 records in the above Customers table. . A record is a horizontal entity in a table. - A column is a vertical entity in a table that contains all information associated with a specific field in a table.

SELECT sub.* FROM ( SELECT * FROM tutorial.sf_crime_incidents_2014_01 WHERE day_of_week = 'Friday' ) sub WHERE sub.resolution = 'NONE'

- First, the database runs the 'inner query' - the part between the parentheses - Once the inner query runs, the outer query will run using the results from the inner query as its underlying table:

What's the difference between SQL and MySQL?

- SQL is a language for querying databases . SQL is used for accessing, updating and maintaining data in a database - MySQL is an open-source database product . MySQL is a Relational Database Management System that allows users to keep the data that exists in a database organized

WHERE (Employee.DepartmentId, Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )

- Sub-Query Outline

ALTER TABLE test ADD dd TEXT;

Adds a column with null values

SELECT Name, Continent, Population FROM Country WHERE Name LIKE 'island%' ORDER BY Name;

Anything that starts with the word 'island'

How can a constraint be placed on a table where a field will contain the value "Mickey" if nothing is provided?

CREATE TABLE People (FirstName TEXT DEFAULT 'Mickey', address TEXT, city TEXT);

SELECT Count(*) AS DistinctCountriesFROM (SELECT DISTINCT Country FROM Customers);

Here is the workaround for MS Access

INSERT INTO Example The following SQL statement inserts a new record in the "Customers" table:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Insert Data Only in Specified Columns It is also possible to only insert data in specific columns. The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

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

INSERT INTO Syntax It is possible to write the INSERT INTO statement in two ways: 1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

SELECT * FROM table_name;

If you want to select all the fields available in the table, use the following syntax

WHERE Statement

It is used to extract & filter only those records that fulfill a specified condition

Are SQL words Case-Sensitive?

No

SELECT Name, Continent FROM Country ORDER BY Continent, Name;

Orders by continent first then by name

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'Fish%'

Percent Sign (%) Wildcard# The most frequently used wildcard is the percent sign (%). Within a string, the % means, match any number of number of occurrences of any character. The following sql statement finds all products with name that begins with Fish following by any character

SELECT * FROM Customers;

Retrieves the data from the Customer's Table

ORDER BY Several Columns Example 2 The following SQL statement selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:

SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;

ORDER BY DESC Example The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

SELECT * FROM Customers ORDER BY Country DESC;

ORDER BY Several Columns Example The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName:

SELECT * FROM Customers ORDER BY Country, CustomerName;

ORDER BY Example The following SQL statement selects all customers from the "Customers" table, sorted by the "Country" column:

SELECT * FROM Customers ORDER BY Country;

Combining AND, OR and NOT You can also combine the , and operators.AND ORNOT The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "München" (use parenthesis to form complex expressions):

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

ORDER BY Syntax

SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;

NOT Syntax

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

AND Syntax

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

OR Syntax

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

SELECT * FROM Customers WHERE CustomerID=1;

SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes

SELECT CustomerName, City FROM Customers;

Selects CustomerName and City columns from the Customers table

SELECT * FROM Customers;

Selects all columns from Customers table

SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee JOIN Department ON Employee.DepartmentId = Department.Id

Selects the name column in the Department table and gives the alias 'Department' Selects the name column in the Employee table and gives the alias 'Employee - Provides the first table with From - Joins the department table - Going to join on the departmentid column

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%bean bag%'

The % does not match a NULL value. Be careful when using it with columns that have a NULL value as it will not match it

SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10

The BETWEEN keyword is used to retrieve values between two values, and the AND keyword is used to separate the two values. The values will be matched inclusive of the start and end values.

SELECT prod_name, prod_price FROM products WHERE vend_id in ('DLL01', 'BSR01'

The IN operator is used to specify a range of operators, any of which can be matched. The IN takes a comma separated list of valid values, all enclosed in parentheses. The IN operator works the same as using multiple OR statements. We could have returned the same results using the OR operator as follows :

The SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

SELECT cust_contact FROM customers WHERE cust_contact LIKE '[JM]%'

The brackets will match either of the characters inside the brackets. The following statement will match any customer contacts that have a name that either starts with with J or M followed by any character.

The SQL AND, OR and NOT Operators

The clause can be combined with , , and operators.WHERE ANDOR NOT The and operators are used to filter records based on more than one condition:ANDOR The operator displays a record if all the conditions separated by are TRUE.AND AND The operator displays a record if any of the conditions separated by is TRUE.OR OR The operator displays a record if the condition(s) is NOT TRUE.NOT

SELECT COUNT(DISTINCT Country) FROM Customers;

The following SQL statement lists the number of different (distinct) customer countries

SELECT cust_contact FROM customers WHERE cust_contact LIKE '[^JM]%'

The wildcard can also be negated to not match the characters in the brackets using the ^ character, e.g

CREATE TABLE test ( a TEXT UNIQUE, b TEXT, c TEXT DEFAULT 'panda');

Unique constraint only allows unique values in column a or it'll fail NULL Value is exempt

CREATE TABLE test ( a TEXT UNIQUE NOT NULL, b TEXT, c TEXT DEFAULT 'panda');

Unique constraint with Not Null constraint

SELECT DISTINCT Statement

Used to return only distinct (different) values.