SQL

·       What is Database?

o   A database is an organized collection of structured information, or data, typically stored electronically in a computer system.

o   A database is usually controlled by a database management system (DBMS).

o   The data can then be easily accessed, managed, modified, updated, controlled, and organized.

·       What is SQL?

o   SQL is a standard language for accessing and manipulating databases.

o   SQL stands for Structured Query Language

o   SQL lets you access and manipulate databases

o   SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

·       What SQL can do?

o   SQL can execute queries against a database

o   SQL can retrieve data from a database

o   SQL can insert records in a database

o   SQL can update records in a database

o   SQL can delete records from a database

o   SQL can create new databases

o   SQL can create new tables in a database

o   SQL can create stored procedures in a database

o   SQL can create views in a database

o   SQL can set permissions on tables, procedures, and views

·       What is RDBMS?

o   RDBMS stands for Relational Database Management System.

o   RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

o   The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries, and it consists of columns and rows.

o   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.

o   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.

o   A column is a vertical entity in a table that contains all information associated with a specific field in a table.

 

·       Database Tables

o   A database most often contains one or more tables. Each table is identified by a name (e.g., "Customers" or "Orders"). Tables contain records (rows) with data.

o   Below is a selection from the "Customers" table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

 

·       The SQL Statement

o   Most of the actions you need to perform on a database are done with SQL statements.

o   The following SQL statement selects all the records in the "Customers" table:
SELECT * FROM Customers;

o   SQL keywords are NOT case sensitive: select is the same as SELECT

o   Some database systems require a semicolon at the end of each SQL statement.

o   Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

o   Some of The Most Important SQL Commands:

§  SELECT - extracts data from a database

§  UPDATE - updates data in a database

§  DELETE - deletes data from a database

§  INSERT INTO - inserts new data into a database

§  CREATE DATABASE - creates a new database

§  ALTER DATABASE - modifies a database

§  CREATE TABLE - creates a new table

§  ALTER TABLE - modifies a table

§  DROP TABLE - deletes a table

§  CREATE INDEX - creates an index (search key)

§  DROP INDEX - deletes an index

·       The SELECT Statement

o   The SELECT statement is used to select data from a database.

o   The data returned is stored in a result table, called the result-set.

SELECT column1, column2, ... FROM table_name;

o   Here, column1, column2, ... are the field names of the table you want to select data from.

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

SELECT * FROM table_name;

o   Example:

SELECT CustomerName, City FROM Customers;

SELECT * FROM Customers;

·       The SELECT DISTINCT Statement

o   The SELECT DISTINCT statement is used to return only distinct (different) values.

o   Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ... FROM table_name;

·       The WHERE Clause

o   The WHERE clause is used to filter records.

o   It is used to extract only those records that fulfill a specified condition.

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

o   The WHERE clause is not only used in SELECT statements, but also in UPDATE, DELETE, etc.

o   Example: SELECT * FROM Customers WHERE Country=’Mexico’;

o   Text Fields vs. Numeric Fields

§  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 * FROM Customers WHERE CustomerID=1;

·       Operators in The WHERE Clause

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

 

 

·       The AND, OR and NOT Operators

o   The WHERE clause can be combined with AND, OR, and NOT operators.

o   The AND and OR operators are used to filter records based on more than one condition:

o   The AND operator displays a record if all the conditions separated by AND are TRUE.

o   The OR operator displays a record if any of the conditions separated by OR is TRUE.

o   The NOT operator displays a record if the condition(s) is NOT TRUE.

o   AND Syntax

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

o   OR Syntax

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

o   NOT Syntax

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

o   AND Example

§  The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":

SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin';

o   OR Example

§  The following SQL statement selects all fields from "Customers" where city is "Berlin" OR "München":

SELECT * FROM Customers WHERE City='Berlin' OR City='München';

o   NOT Example

§  The following SQL statement selects all fields from "Customers" where country is NOT "Germany":

SELECT * FROM Customers WHERE NOT Country='Germany';

o   Combining AND, OR and NOT

§  You can also combine the AND, OR and NOT operators.

§  The following SQL statement selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "Pune" (use parenthesis to form complex expressions):

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

§  The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA":

SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';

·       The ORDER BY Keyword

o   The ORDER BY keyword is used to sort the result-set in ascending or descending order.

o   The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

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

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

SELECT * FROM Customers ORDER BY Country;

o   The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:

SELECT * FROM Customers ORDER BY Country DESC;

o   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;

o   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;

·       The INSERT INTO Statement

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

o   It is possible to write the INSERT INTO statement in two ways:

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

o   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, ...);

·       The NULL Values

o   A field with a NULL value is a field with no value.

o   If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

o   A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

o   It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

o   We will have to use the IS NULL and IS NOT NULL operators instead.

§   SELECT column_names
FROM table_name
WHERE column_name IS NULL;

§   SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

·       The UPDATE Statement

o   The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

o   Be careful when updating records. If you omit the WHERE clause, ALL records will be updated!

·       The DELETE Statement

o   The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;

o   If you omit the WHERE clause, all records in the table will be deleted!

o   It is possible to delete all rows in a table without deleting the table.

DELETE FROM table_name;

·       The SELECT TOP Clause

o   The SELECT TOP clause is used to specify the number of records to return.

o   The SELECT TOP clause is useful on large tables with thousands of records. Returning many records can impact performance.

o   NOTE: Not all database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM.

§   SELECT TOP number|percent column_name(s)
FROM table_name WHERE condition;

§   SELECT TOP 50 PERCENT * FROM Customers;

o   cc

·       The MIN() and MAX() Functions

o   The MIN() function returns the smallest value of the selected column.

o   The MAX() function returns the largest value of the selected column.

§   SELECT MIN(column_name)
FROM table_name WHERE condition;

§   SELECT MAX(column_name)
FROM table_name WHERE condition;

·       The COUNT(), AVG() and SUM() Functions

o   The COUNT() function returns the number of rows that matches a specified criterion.

SELECT COUNT(column_name)
FROM table_name WHERE condition;

o   The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name WHERE condition;

o   The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name WHERE condition;

·       The LIKE Operator

o   The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

o   A wildcard character is used to substitute one or more characters in a string. Following are the wildcards used in conjunction with the LIKE operator:

§  The percent sign (%) represents zero, one, or multiple characters

§  The underscore sign (_) represents one, single character

§  The [] represents any single character within the brackets. h[oa]t finds hot and hat, but not hit.

§  The ^ represents any character not in the brackets. h[^oa]t finds hit, but not hot and hat.

§  The - represents any single character within the specified range. c[a-b]t finds cat and cbt

o   NOTE: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_).

SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

·       The IN Operator

o   The IN operator allows you to specify multiple values in a WHERE clause.

o   The IN operator is a shorthand for multiple OR conditions.

§   SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

§   SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

·       The BETWEEN Operator

o   The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

o   The BETWEEN operator is inclusive: begin and end values are included.

§   SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

§   SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

·       The Aliases

o   SQL aliases are used to give a table, or a column in a table, a temporary name.

o   Aliases are often used to make column names more readable.

o   An alias only exists for the duration of that query.

o   An alias is created with the AS keyword.

§   SELECT column_name AS alias_name FROM table_name;

§   SELECT column_name(s) FROM table_name AS alias_name;

o   Note: It requires double quotation marks or square brackets if the alias name contains spaces:

o   Aliases can be useful when:

§  There are more than one table involved in a query

§  Functions are used in the query

§  Column names are big or not very readable

§  Two or more columns are combined

·       The SQL JOIN

o   A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

o   Different Types of SQL JOINs

§  (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

   

 

o   The INNER JOIN keyword selects records that have matching values in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

o   The LEFT JOIN keyword returns all records from the left table, and the matching records from the right table. The result is 0 records from the right side if there is no match. The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table.

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

o   The RIGHT JOIN keyword returns all records from the right table, and the matching records from the left table. The result is 0 records from the left side if there is no match. The RIGHT JOIN keyword returns all records from the right table (Employees), even if there are no matches in the left table.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

o   The FULL OUTER JOIN keyword returns all records when there is a match in left or right table records. FULL OUTER JOIN and FULL JOIN are the same. FULL OUTER JOIN can potentially return very large result-sets!

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name

o   A Self join is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

 

·       The UNION Operator

o   The UNION operator is used to combine the result-set of two or more SELECT statements.

o   Every SELECT statement within UNION must have the same number of columns

o   The columns must also have similar data types

o   The columns in every SELECT statement must also be in the same order

o   The column names in the result-set are usually equal to the column names in the first SELECT statement.

§   SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

§   SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

o   UNION selects only distinct values. Use UNION ALL to also select duplicate values!

·       The GROUP BY Statement

o   The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

o   The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

·       The HAVING Clause

o   The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

·       The EXISTS Operator

o   The EXISTS operator is used to test for the existence of any record in a subquery.

o   The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

·       The ANY and ALL Operators

o   The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

o   The ANY operator:

§  returns a boolean value as a result

§  returns TRUE if ANY of the subquery values meet the condition

§  ANY means that the condition will be true if the operation is true for any of the values in the range.

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name FROM table_name WHERE condition);

o   The ALL Operator:

§  returns a boolean value as a result

§  returns TRUE if ALL of the subquery values meet the condition

§  is used with SELECT, WHERE and HAVING statements

§  ALL means that the condition will be true only if the operation is true for all values in the range.

SELECT ALL column_name(s) FROM table_name WHERE condition;

·       The SELECT INTO Statement

o   The SELECT INTO statement copies data from one table into a new table.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

o   SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

·       The INSERT INTO SELECT Statement

o   The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

o   The INSERT INTO SELECT statement requires that the data types in source and target tables match.

o   Note: The existing records in the target table are unaffected.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

·       The CASE Statement

o   The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

o   If there is no ELSE part and no conditions are true, it returns NULL.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

·       The IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

o   The MySQL IFNULL() function lets you return an alternative value if an expression is NULL

§   SELECT Name, UnitPrice * (Stock + IFNULL(Order, 0)) FROM Products;

§   SELECT Name, UnitPrice * (Stock + COALESCE(Order, 0)) FROM Products;

§   SELECT Name, UnitPrice * (Stock + ISNULL(Order, 0)) FROM Products;

§   SELECT Name, UnitPrice * (Stock + IIF(IsNull(UnitsOnOrder), 0, Order)) FROM Products;

§   SELECT Name, UnitPrice * (Stock + NVL(Order, 0)) FROM Products;

·       What is a Stored Procedure?

o   A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

o   You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

§   CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

§   EXEC procedure_name;

§   CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

§   EXEC SelectAllCustomers @City = 'London';

·       The SQL Comments

o   Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

o   Single line comments start with --.

o   Any text between -- and the end of the line will be ignored (will not be executed).

o   Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored.

·       SQL Operators

o   SQL Arithmetic Operators

Operator	Description
+	Add
-	Subtract
*	Multiply
/	Divide
%	Modulo

 

o   SQL Bitwise Operators

Operator	Description
&	Bitwise AND
|	Bitwise OR
^	Bitwise exclusive OR

 

 

 

 

 

 

 

 

 

o   SQL Comparison Operators

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

o   SQL Compound Operators

Operator	Description
+=	Add equals
-=	Subtract equals
*=	Multiply equals
/=	Divide equals
%=	Modulo equals
&=	Bitwise AND equals
^-=	Bitwise exclusive equals
|*=	Bitwise OR equals

o   SQL Logical Operators

Operator	Description
ALL	TRUE if all of the subquery values meet the condition
AND	TRUE if all the conditions separated by AND is TRUE
ANY	TRUE if any of the subquery values meet the condition
BETWEEN	TRUE if the operand is within the range of comparisons
EXISTS	TRUE if the subquery returns one or more records
IN	TRUE if the operand is equal to one of a list of expressions
LIKE	TRUE if the operand matches a pattern
NOT	Displays a record if the condition(s) is NOT TRUE
OR	TRUE if any of the conditions separated by OR is TRUE
SOME	TRUE if any of the subquery values meet the condition