on 12-Nov-2019 (Tue)

Example - Select all fields from one table

Let's look at how to use a SQL Server SELECT query to select all fields from a table.

 SELECT *
FROM inventory
WHERE quantity > 5 ORDER BY inventory_id ASC; 

Example - Select individual fields from one table

Example - Select individual fields from one table

You can also use the SQL Server SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_id >= 555
AND inventory_type = 'Software'
ORDER BY quantity DESC, inventory_id ASC;

Example - Select fields from multiple tables

You can also use the SQL Server SELECT statement to retrieve fields from multiple tables by using a join.

For example:

SELECT inventory.inventory_id, products.product_name, inventory.quantity
FROM inventory
INNER JOIN products
ON inventory.product_id = products.product_id
ORDER BY inventory_id;

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(3)
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

The syntax for the FROM clause in SQL Server (Transact-SQL) is:

FROM table1
[ { INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN } table2
ON table1.column1 = table2.column1 ]

Example - Two tables with INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Example - Two Tables with OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees
LEFT OUTER JOIN contacts
ON employees.employee_id = contacts.contact_id
WHERE employees.first_name = 'Sarah';

This SQL Server FROM clause example uses the FROM clause to list two tables - employees and contacts. And we are using the FROM clause to specify a LEFT OUTER JOIN between the employees and contacts tables based on the employee_id column in both tables.

DO THIS !

SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name in the employees table is 'Sarah'. And the employees and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table.

Example - Joining Tables

Let's look at how to use the WHERE clause when we join multiple tables together.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees
INNER JOIN contacts
ON employees.employee_id = contacts.contact_id
WHERE employees.first_name = 'Sarah';

This SQL Server WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all rows where the first_name in the employees table is 'Sarah'. And the employees and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table.

Traditionally write this SQL as follows using a proper INNER JOIN.

DO THIS

shows how the AND condition can be used to join multiple tables in a SELECT statement.

Our next SQL Server AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees, contacts
WHERE employees.employee_id = contacts.contact_id
AND employees.first_name = 'Sarah';

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

DO:

This SQL Server AND condition example would return all rows where the first_name in the employees table is 'Sarah'. And the employees and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table. You will notice that all of the fields are prefixed with the table names (ie: contacts.last_name). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the employees and the contacts tables.

In this case, the result set would only display the employee_id and last_name fields (as listed in the first part of the SELECT statement.).

SELECT employees.employee_id, contacts.last_name
FROM employees
INNER JOIN contacts
ON employees.employee_id = contacts.contact_id
WHERE employees.first_name = 'Sarah';

This SQL Server AND condition example would return all rows where the first_name in the employees table is 'Sarah'. And the employees and contacts tables are joined on the employee_id from the employees table and the contact_id from the contacts table. You will notice that all of the fields are prefixed with the table names (ie: contacts.last_name). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the employees and the contacts tables.

In this case, the result set would only display the employee_id and last_name fields (as listed in the first part of the SELECT statement.).

DO:

This SQL Server AND condition example would insert into the contacts table, all employee_id, last_name, and first_name records from the employees table where the first_name is 'Joanne' and the employee_id is greater than or equal to 800.

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE first_name = 'Joanne'
AND employee_id >= 800;

This SQL Server AND condition example would insert into the contacts table, all employee_id, last_name, and first_name records from the employees table where the first_name is 'Joanne' and the employee_id is greater than or equal to 800.

DO THIS:

This SQL Server AND condition example would insert into the contacts table, all employee_id, last_name, and first_name records from the employees table where the first_name is 'Joanne' and the employee_id is greater than or equal to 800.

For example:

UPDATE employees
SET last_name = 'Johnson'
WHERE last_name = 'TBD'
AND employee_id < 300;

This SQL Server AND condition example would update all last_name values in the employees table to 'Johnson' where the last_name is 'TBD' and the employee_id is less than 300.

DO THIS:

This SQL Server AND condition example would insert into the contacts table, all employee_id, last_name, and first_name records from the employees table where the first_name is 'Joanne' and the employee_id is greater than or equal to 800.

DELETE FROM employees
WHERE first_name = 'Darlene'
AND last_name = 'Henderson';

This SQL Server AND condition example would delete all records from the employees table whose first_name is 'Darlene' and last_name is 'Henderson'.

DO THIS:

This SQL Server IN condition example would return all rows from the employees table where the last_name is either 'Smith', 'Anderson', or 'Johnson'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

SELECT *
FROM employees
WHERE last_name IN ('Smith', 'Anderson', 'Johnson');

This SQL Server IN condition example would return all rows from the employees table where the last_name is either 'Smith', 'Anderson', or 'Johnson'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

DO THIS:

This SQL Server IN condition example would return all rows from the employees table where the first_name is not 'Sarah', 'John', or 'Dale' Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SELECT *
FROM employees
WHERE first_name NOT IN ('Sarah', 'John', 'Dale');

This SQL Server IN condition example would return all rows from the employees table where the first_name is not 'Sarah', 'John', or 'Dale' Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

LIKE Pattern

Wildcard Explanation ?

SELECT *
FROM employees
WHERE last_name LIKE '%o%';

In this SQL Server LIKE condition example, we are looking for all employees whose last_name contains the letter 'o'.

SELECT *
FROM employees
WHERE first_name LIKE 'Ad_m';

This SQL Server LIKE condition example would return all employees whose first_name is 4 characters long, where the first two characters is 'Ad' and the last character is 'm'. For example, it could return employees whose first_name is 'Adam', 'Adem', 'Adim', 'Adom', 'Adum', etc.

You might find that you are looking for an employee_number, but you only have 3 of the 4 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return employees whose employee numbers are:

1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239

SELECT *
FROM employees
WHERE employee_number LIKE '123_';

You might find that you are looking for an employee_number, but you only have 3 of the 4 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return employees whose employee numbers are:

1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239

SELECT *
FROM employees
WHERE first_name LIKE 'Sm[iy]th';

This SQL Server LIKE condition example would return all employees whose first_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th', and the third character is either 'i' or 'y'. So in this case, it would match on either 'Smith' or 'Smyth'.

SELECT *
FROM employees
WHERE first_name LIKE 'Sm[^iy]th';

This SQL Server LIKE condition example would return all employees whose first_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th', and the third character is neither 'i' or 'y'. So in this case, it would match on values such as 'Smath', 'Smeth', 'Smoth', etc. But it would not match on either 'Smith' or 'Smyth'.

SELECT *
FROM employees
WHERE secret_hint LIKE '123!%455' ESCAPE '!';

This SQL Server LIKE condition example identifies the ! character as an escape character. This statement will return all employees whose secret_hint is 123%455.

SELECT *
FROM employees
WHERE secret_hint LIKE 'H%!%' ESCAPE '!';

This SQL Server LIKE condition example returns all employees whose secret_hint starts with H and ends in %. For example, it would return a value such as 'Help%'.

SELECT *
FROM employees
WHERE secret_hint LIKE 'H%!_' ESCAPE '!';

This SQL Server LIKE condition example returns all employees whose secret_hint starts with H and ends in _. For example, it would return a value such as 'Help_'.

SELECT *
FROM employees
WHERE first_name NOT IN ( 'John', 'Dale', 'Susan' );

This SQL Server NOT example would return all rows from the employees table where the first_name is not 'John', 'Dale', or 'Susan'. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SELECT *
FROM employees
WHERE last_name IS NOT NULL;

This SQL Server NOT example would return all records from the employees table where the last_name does not contain a NULL value.

SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name NOT LIKE 'A%';

By placing the SQL Server NOT Operator in front of the LIKE condition, you are able to retrieve all employees whose last_name does not start with 'A'.

SELECT *
FROM employees
WHERE employee_id NOT BETWEEN 200 AND 250;

This SQL Server NOT example would return all rows from the employees table where the employee_id was NOT between 200 and 250, inclusive. It would be equivalent to the following SQL Server SELECT statement:

SELECT *
FROM employees
WHERE NOT EXISTS (SELECT * FROM contacts WHERE employees.last_name = contacts.last_name AND employees.first_name = contacts.first_name);

This SQL Server NOT example would return all records from the employees table where there are no records in the contacts table for the matching last_name and first_name.

the K-means clustering algorithm produces :

• A final estimate of cluster *** (i.e. their ***)

• An assignment of each point to their respective ***

the K-means clustering algorithm produces :

• A final estimate of cluster centroids (i.e. their coordinates)

• An assignment of each point to their respective cluster