Introduction to SQL

What is SQL?
SQL stands for Structured Query Language. SQL lets you access and manipulate databases.

What can SQL do?
SQL can execute queries against a database - SQL can retrieve data from a database - SQL can insert records in a database - SQL can update records in a database - SQL can delete records from a database - SQL can create new databases - SQL can create new tables in a database - SQL can create stored procedures in a database - SQL can create views in a database - SQL can set permissions on tables, procedures, and views

Can SQL retrieve data from a database?
Yes, SQL can retrieve data from a database

Can SQL execute queries against a database?
Yes, SQL can execute queries against a database

Can SQL can insert records in a database?
Yes, SQL can insert records in a database

Can SQL update records in a database?
Yes, SQL can update records in a database

Can SQL delete records from a database?
Yes, SQL can delete records from a database

Can SQL create new databases?
Yes, SQL can create new databases

Can SQL create new tables in a database?
Yes, SQL can create new tables in a database

Can SQL create stored procedures in a database?
Yes, SQL can create stored procedures in a database

Can SQL create views in a database?
Yes, SQL can create views in a database

Can SQL set permissions on tables, procedures, and views?
Yes, SQL can set permissions on tables, procedures, and views

What is RDBMS?
RDBMS stands for Relational Database Management System.

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

Where is the data stored in RDBMS?
The data in RDBMS is stored in database objects called tables.

What is a table?
A table is a collection of related data entries and it consists of columns and rows.

Are SQL queries case sensitive?
SQL keywords are NOT case sensitive

Is select is the same as SELECT in SQL?
Yes, select is the same as SELECT since SQL is not case sensitive

What is the function of the Semicolon in SQL?
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.

Can you have more than one statement to be executed in the same call to the server?
Yes, but a semicolon is needed to separate each SQL Statement.

What are the four basic functions of persistent storage?
The four basic functions of persistent storage can be defined by acronym CRUD

What is CRUD?
Create, Read/Retrieve (Select), Update and Delete are the four basic functions of persistent storage.

What does the C in CRUD stand for?
Create, C in CRUD, stand for Create and is on of the four basic functions of persistent storage.

What does the R in CRUD stand for?
Read/Retrieve (Select), R in CRUD, stand for Read/Retrieve (Select) and is on of the four basic functions of persistent storage.

What does the U in CRUD stand for?
Update, U in CRUD, stands for Update and is on of the four basic functions of persistent storage.

What does the D in CRUD stand for?
Delete, D in CRUD, stands for Delete and is on of the four basic functions of persistent storage.

What is the SELECT Statement for?
The SELECT statement is used to select data from a database.

What is the SELECT Statement Syntax?
SELECT column_name, column_name  - FROM table_name;  SELECT * FROM table_name;

Can a column contain duplicate Values?
Yes a column can contain Duplicate Values

How do you separate the duplicate Values from a Column?
You can list only the different (distinct) values, using the DISTINCT Statement

DISTINCT syntax
SELECT DISTINCT column_name,column_name | FROM table_name;

What is the WHERE Clause used for?
The WHERE clause is used to extract only those records that fulfill a specified criterion.

WHERE clause syntax:
WHERE Syntax:

SELECT column_name,column_name
FROM table_name  WHERE column_name operator value;

What are the AND/OR Operators used for?
The AND & OR operators are used to filter records based on more than one condition.

Example of OR operator
Example of OR Syntax:

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

What is the ORDER BY Keyword used for?
The ORDER BY keyword is used to sort the result-set by one or more columns.

SELECT column_name, column_name
FROM table_name   ORDER BY column_name ASC|DESC, column_name ASC|DESC;

What is the default order in the ORDER keyword?
The ORDER BY keyword sorts the records in ascending order by default.

Can you have ORDER by Desc value?
Yes, To sort the records in a descending order, you can use the DESC keyword.

What is the INSERT INTO Statement used for?
The INSERT INTO statement is used to insert new records in a table.

Example of INSERT INTO statement
INSERT INTO Example:  INSERT INTO table_name   VALUES (value1,value2,value3,...);

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

What is the UPDATE Statement used for?
The UPDATE statement is used to update existing records in a table.

Example of UPDATE statement
Update Example:  UPDATE table_name    SET column1=value1,column2=value2,...  WHERE some_column=some_value;

What happens if you use the UPDATE statement without the WHERE?
All Records are updated.

Why are all Records updated if you omit the WHERE in the UPDATE Statement?
The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

What it the DELETE Statement used for?
The DELETE statement is used to delete

What is the purpose of WHERE in the DELETE statement?
The WHERE clause specifies which record or records that should be deleted.

What happens if you omit the WHERE clause in the DELETE statement?
If you omit the WHERE clause, all records will be deleted!

What is the SELECT TOP Clause used for?
The SELECT TOP clause is used to specify the number of records to return.

Does the SELECT TOP clause has any utility on large tables?
Yes, the SELECT TOP clause can be very useful on large tables with thousands of records.

Why does the SELECT TOP clause be useful on large tables?
Performance - Returning a large number of records can impact on performance.

Is the SELECT TOP supported by all database systems?
No, Not all database systems support the SELECT TOP clause.

Example syntax of SELECT TOP
SELECT TOP example syntax:

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

What is the LIKE Operator used for?
The LIKE operator is used to search for a specified pattern in a column.

Example of LIKE operator syntax
LIKE operator syntax example: SELECT column_name(s)    FROM table_name   WHERE column_name IN (value1,value2,...);

SELECT * FROM Customers
WHERE City IN ('Paris','London');

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

What is the BETWEEN operator do?
The BETWEEN operator selects values within a range.

What are the values that BETWEEN operator can handle?
Numbers, Text, or Dates.  The values handled by the BETWEEN operator can be numbers, text, or dates.

Example of BETWEEN operator syntax:
BETWEEN operator syntax:

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

What are the Aliases (AS) used for?
SQL aliases are used to give a database table, or a column in a table, a temporary name.

What are the Aliases (AS) main purpose?
Readability - Basically, aliases are created to make column names more readable.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

What is the JOIN clause used for?  
SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

What is the INNER JOIN keyword do?
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Example of INNER JOIN keyword:
Example of INNER JOIN keyword:  SELECT column_name(s)  FROM table1   INNER JOIN table2    ON table1.column_name=table2.column_name;

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

What does the LEFT JOIN do?
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2).

What happens on the LEFT JOIN if there is no match in the right side?
NULL - The result is NULL in the right side when there is no match.

Example of LEFT JOIN syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

What does the RIGHT JOIN do?
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1).

What happens if there is no match on the left side with the RIGHT JOIN?
NULL - The result is NULL in the left side when there is no match.

Example of RIGHT JOIN syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

What does the FULL OUTER JOIN keyword return?
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

Is the FULL OUTER JOIN a combination of both LEFT and RIGHT join?
Yes, The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Example of FULL OUTER JOIN syntax:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

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

Does the UNION operator needs to have the same number of columns?
Yes, the UNION operator needs to have the same number of columns.
Notice that each SELECT statement within the UNION must have the same number of columns.

Besides UNION needing to have the same number of columns is there anything else that needs to be similar?
Data TYPES, and Order
Yes, The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Example of UNION syntax:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

What does the SELECT INTO do?
The SELECT INTO statement selects data from one table and inserts it into a new table.

Example of SELECT INTO syntax:
SELECT *
INTO newtable [IN externaldb]
FROM table1;

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

What does the INSERT INTO do?
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table.

In an INSERT INTO are all rows affected?
No, Any existing rows in the target table are unaffected.

Example of INSERT INTO syntax:
INSERT INTO table2
SELECT * FROM table1;

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

What is the CREATE DATABASE statement used for?
The CREATE DATABASE statement is used to create a database.

Example of CREATE DATABASE syntax:
CREATE DATABASE dbname;

What is the CREATE TABLE statement used for?
The CREATE TABLE statement is used to create a table in a database.

When creating a table how are tables organized?
Tables are organized into rows and columns; and each table must have a name.

Example of creating a table Syntax:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

What are SQL constraints used for?
SQL constraints are used to specify rules for the data in a table.

What happens on a SQL constrain if there is a violation between the constrain and the data action?
If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Can SQL Constrains be specified when a table is Created?
Yes, Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

Example of a SQL Constrain syntax:
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

What does the NOT NULL Constraint do?
The NOT NULL constraint enforces a column to NOT accept NULL values.

Does the NOT NULL Constraint always needs to contain a value?
Yes, The NOT NULL constraint enforces a field to always contain a value.

Can you INSERT / UPDATE a new record on a NOT NULL without adding a value to this field?
No, this is the whole point of NOT NULL, This means that you cannot insert a new record, or update a record without adding a value to this field.

Example of NOT NULL Syntax:
Example of NOT NULL Syntax:
CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

What does the UNIQUE Constraint do?
The UNIQUE constraint uniquely identifies each record in a database table.

What does a UNIQUE Constraint provide?
Uniqueness - The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

Does a PRIMARY KEY Constraint automatically has a UNIQUE constraint?
Yes, A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Can you have a lot of UNIQUE Constraints per table?
YES, that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

Example of UNIQUE Constrain Syntax:
Example of UNIQUE Constrain Syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

What does the PRIMARY KEY do?
The PRIMARY KEY constraint uniquely identifies each record in a database table.

Do PRIMARY KEYS must contain UNIQUE values?
Yes, Primary keys must contain UNIQUE values.

Can a PRIMARY KEY contain NULL VALUES?
No, A primary key column cannot contain NULL values.

Should most tables contain a Primary Key?
Yes, Most tables should have a primary key, and each table can have only ONE primary key.

Example of a PRIMARY KEY syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

What is a FOREIGN KEY?
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Example of a FOREIGN KEY Syntax
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

What is the CHECK Constraint used for?
The CHECK constraint is used to limit the value range that can be placed in a column.

CHECK Constrain on a Single Column:
If you define a CHECK constraint on a single column it allows only certain values for this column.

What happens if you define a CHECK constraint on a table?
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Example of CHECK constraint Syntax:
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

What is the DEFAULT Constraint used for?
The DEFAULT constraint is used to insert a default value into a column.

Where will the DEFAULT constrain add the values?
The default value will be added to all new records, if no other value is specified.

Example of DEFAULT constraint syntax:
CREATE TABLE Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);

What is the CREAT INDEX used for?
The CREATE INDEX statement is used to create indexes in tables.

What are the INDEXES used for?
Indexes allow the database application to find data fast; without reading the whole table.

Is the update time of a table with indexes the same as the update time without indexes?
No, the update time is not the same, Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).

So if there is a delay on updating a table with index when should we used indexes?
You should only create indexes on columns (and tables) that will be frequently searched against.

Example of CREATE INDEX syntax:
CREATE INDEX index_name
ON table_name (column_name)

What is the DROP statement used for?
Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

Example of DROP Syntax:
DROP INDEX table_name.index_name

DROP DATABASE database_name

What is the ALTER statement used for?
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

How would you add a column in a table with ALTER?
To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

How would you remove a column in a table with ALTER?
To remove a column in a table, use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name

What does the AUTO INCREMENT used for?
Auto-increment allows a unique number to be generated when a new record is inserted into a table.

Why would we use the AUTO INCREMENT?
Automatically create the value of Primary Key
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

Example of AUTO INCREMENT syntax:
CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

What is a View?
A view is a virtual table based on the result-set of an SQL statement

Example of a CREATE VIEW syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

What does a VIEW contain?
A view contains rows and columns, just like a real table.

What are the FIELDS in a view?
The fields in a view are fields from one or more real tables in the database.

Can you add SQL functions to a view?
Yes, You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Example of UDPATE VIEW syntax:
Example of UDPATE VIEW syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Example of DROP VIEW syntax:
Example of DROP VIEW syntax:
DROP VIEW view_name

Does SQL Dates come already with some predefined types for defining and storing a date or a date / time value?
Yes, there are some already predefined date types.

What are the types of Data types for storing a date or a date / time value in a database?
There are some types for storing a date or date / time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number

How is the type DATE in SQL structured?
The type DATE is formatted like: YYYY-MM-DD

How is the type DATETIME in SQL structured?
The type DATETIME is formatted like: YYYY-MM-DD HH:MI:SS

How is the type SMALLDATETIME in SQL structured?
The type SMALLDATETIME is formatted like: YYYY-MM-DD HH:MI:SS

How is the type TIMESTAMP in SQL structured?
The type TIMESTAMP is formatted like: A Unique Number

What is the most difficult part of working with SQL dates?
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database.

What do Null Values represent?
NULL values represent missing unknown data. By default, a table column can hold NULL values

What if a column in a table is Optional?
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

Example of NULL syntax:
Example of NULL syntax:
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

Data Type: Character
Character string. Fixed-length n

char(32)

Data Type: Variable Character
Character string. Variable length. Maximum length n

varchar(64)

Data Type: Boolean
Stores TRUE or FALSE values

boolean

Data Type: Integer
Integer numerical (no decimal). Precision p

int

Data Type: Decimal
Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal.

decimal(6,2)

Data Type: Float
Approximate numerical, mantissa precision 16

float

Data Type: Real
Approximate numerical, mantissa precision 7

real

Data Type: Date
Stores year, month, and day values

date

Data Type: Time
Stores hour, minute, and second values

time

Data Type: Timestampe
Stores year, month, day, hour, minute, and second values

timestamp

Data Type: Binary Object
Binary string. Fixed-length n

Syntax:
Blob
Text

Fixed Length Syntax:
Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)

Data Type: Aray
A set-length and ordered collection of elements

SQL Functions
SQL has many built-in functions for performing calculations on data. Aggregate functions and Scalar Functions.

What does an Average Function return?
The AVG() function returns the average value of a numeric column.

Example of an Average Function Syntax:
Example of an Average Function Syntax:
SELECT AVG(column_name) FROM table_name

What does a Count Function Return?
The COUNT() function returns the number of rows that matches a specified criteria.

What does a COUNT(column_name) function Return?
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column

Example of a COUNT function syntax:
Example of a COUNT function syntax:

SELECT COUNT(column_name) FROM table_name;

What does the First Function Return?
The FIRST() function returns the first value of the selected column. Only in MS Access

Example of First Function Syntax:
Example of First Function Syntax:
SELECT FIRST(column_name) FROM table_name;

What does the Last Function Return?
The LAST() function returns the last value of the selected column. Only in MS Access

Example of LAST Function Syntax
Example of LAST Function Syntax
SELECT LAST(column_name) FROM table_name;

What does the Max Function Return?
The MAX() function returns the largest value of the selected column.

Example of MAX Function Syntax:
Example of MAX Function Syntax:
SELECT MAX(column_name) FROM table_name;

What does the Min Function Return?
The MIN() function returns the smallest value of the selected column.

Example of MIN function Syntax:
Example of MIN function Syntax:
SELECT MIN(column_name) FROM table_name;

What does the Sum Function Return?
The SUM() function returns the SUM of the values of the selected column.

Example of Sum Function Syntax:
Example of Sum Function Syntax:

SELECT MIN(column_name) FROM table_name;

Where is the Group By Function used?
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Example of GROUP by Function Syntax:
Example of GROUP by Function Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Why was the HAVING Function added to SQL?
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Example of HAVING Function Syntax:
Example of HAVING Function Syntax:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

What does the Upper Case Function do?
The UCASE() function converts the value of a field to uppercase.

Example of UCASE Function Syntax:
Example of UCASE Function Syntax:
SELECT UPPER(column_name) FROM table_name;

What does the Lower Case Function do?
The LCASE() function converts the value of a field to lowercase.

Example of LCASE syntax:
Example of LCASE syntax:
SELECT LOWER(column_name) FROM table_name;

What is the Mid Function used for?
The MID() function is used to extract characters from a text field.

Example of mid Syntax:
Example of mid Syntax:
SELECT MID(column_name,start,length) AS some_name FROM table_name;

What is the Length Function used for?
The LEN() function returns the length of the value in a text field.

Example of Length syntax
Example of Length syntax
SELECT LENGTH(column_name) FROM table_name;

What is the Round Function used for?
The ROUND() function is used to round a numeric field to the number of decimals specified.

How is the Round Function processed? is there any rule to be aware of?
IEEE 754, Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is 'round half to even.' In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12.

Example of Round Function Syntax:
Example of Round Function Syntax:
SELECT ROUND(column_name,decimals) FROM table_name;

What does the Now Function return?
The NOW() function returns the current system date and time.

Example of Now Function Syntax
Example of Now Function Syntax
SELECT NOW() FROM table_name;

What is the Format Function used for?
The FORMAT() function is used to format how a field is to be displayed.

SELECT FORMAT(column_name,format) FROM table_name;

What is a Subquery?
A subquery is a SQL query nested inside a larger query.

Example of a Subquery Syntax
Example of a Subquery Syntax
SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid = 'V002');

What is a Intersect?
A Intersect is the Same as INNER JOIN

When do you want to use an Intersect?
Use an Intersect to return only values that are in the first query AND also in the second query.

What is a Schema?
A schema is a collection of database objects (tables) associated with one particular database username.

In a Schema what is a Database Username
This username is called the schema owner, or the owner of the related group of objects.

Can you have multiple Schemas in a Database?
Yes, You may have one or multiple schemas in a database.

When a user creates an Object did he just created his own Schema?
Yes, Basically, any user who creates an object has just created his or her own schema.

What does limit the user access to the Database?
The user access is limited by the user's privileges within the database, the user has control over objects that are created, manipulated, and deleted.

Does a Schema has a limit to the number of objects it may contain?
No, unless it's restricted by implementation, Schema does not have a limit to it's containing objects.
A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.

What does UNION do?
UNION merges the contents of two structurally-compatible tables into a single combined table.

What is the difference between UNION and UNION ALL?
The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

What are the different types of JOIN clauses supported by the ANSI- standard?
(1) Inner Join
(2) Left Join
(3) Right Join
(4) Full Join
(5) Cross Join

Describe an Inner Join
A inner join is a simple join that returns all rows for which there is at least one match in BOTH tables.

What is the default type of join if no specific JOIN type is specified?
Inner Join,
This is the default type of join if no specific JOIN type is specified.

Example of a JOIN syntax
If you have a Table A and a Table B
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.'col' = TableB.'col'
produces only the set of records that match in both A and B

Is a LEFT JOIN the same as LEFT OUTER JOIN?
Yes, a left join can also be referred as a left outer join

What does a LEFT JOIN return?
A left join returns all rows from the left table, and the matched rows from the right table;

What will be the results of a LEFT JOIN?
the results of a LEFT JOIN, will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table.

What will be the result of a LEFT JOIN if the ON clause doesn't match any records in the right table?
If the ON clause doesn't match any records in the right table, the LEFT JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

Is a RIGHT JOIN the same as RIGHT OUTER JOIN?
Yes, A RIGHT JOIN is the same as RIGHT OUTER JOIN

What does a RIGHT JOIN Return?
A RIGHT JOIN Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN;

What will be the results of a RIGHT JOIN
The results of a RIGHT JOIN will contain all records from the right table, even if the JOIN condition doesn't find any matching records in the left table.

What will the RIGHT JOIN give if the ON clause doesn't match any records in the left table?
If the ON clause doesn't match any records in the left table, the RIGHT JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

Is a FULL JOIN the same as FULL OUTER JOIN?
Yes, a FULL JOIN is the same as FULL OUTER JOIN

What does a FULL JOIN return?
A FULL JOIN Returns all rows for which there is a match in EITHER of the tables.

Could you say that a FULL JOIN is a combination of the LEFT AND RIGHT JOIN?
Yes, Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN;

What is the result of a FULL JOIN?
The result of a FULL JOIN is equivalent to performing a UNION of the results of left and right outer queries.

ex: Produces the set of all records in Table A and Table B, with matching records from both sides if available. If no match, missing side will contain null.

What does a CROSS JOIN return?
A CROSS JOIN returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables).

Is there more than one way to specify a CROSS JOIN?
Yes, a CROSS JOIN can either be specified using the CROSS JOIN syntax ('explicit join notation') or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria ('implicit join notation').

What is the SELECT Statement (def, partial) used for?
The SELECT statement is used to query the database and retrieve.

Example of a SELECT statement syntax
Example of a SELECT statement syntax
select 'column1' [,'column2',etc]
from 'tablename' [where 'condition'];
[] = optional

Conditional selections used in the where clause for SELECT
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal to
LIKE

Can you use an = equal as a conditional selection for the SELECT clause?
Yes, you can use an = equal as a conditional selection for the SELECT clause

Can you use an > greater than as a conditional selection for the SELECT clause?
Yes, you can use an > greater than as a conditional selection for the SELECT clause

Can you use an < less than as a conditional selection for the SELECT clause?
Yes, you can use an < less than as a conditional selection for the SELECT clause

Can you use an >= greater or equal than as a conditional selection for the SELECT clause?
Yes, you can use an >= greater or equal than as a conditional selection for the SELECT clause

Can you use an <= less than as a conditional selection for the SELECT clause?
Yes, you can use an <= less than as a conditional selection for the SELECT clause

Can you use an <> not equal as a conditional selection for the SELECT clause?
Yes, you can use an <> not equal as a conditional selection for the SELECT clause

Can you use an LIKE as a conditional selection for the SELECT clause?
Yes, you can use an LIKE as a conditional selection for the SELECT clause

What is Data type: char(size)
It's the Fixed-length character string.
Size specified in parentheses. 255

What is a Data type: varchar(size)
Variable-length character string.
Max size is specified in parenthesis.

What is a Data type: number(size)
Number value with a max number of column digits specified in parenthesis.

Data type: number (size,d)
Number value with a maximum number of digits of 'size' total, with a max number of 'd' digits to the right of the decimal.

Example of an INSERT INTO statement
Example of an INSERT INTO statement
insert into 'tablename'
(first_column,...last_column)
values (first_value,...last_value);

What is the DELETE statement used for?
The DELETE statement is used to delete records or rows from the table

What does the MIN Return?
Returns the smallest value in a given column

What does the MAX Return?
Returns the largest value in a given column

COUNT- Aggregate fxn
Returns the total number of values in a given column

COUNT(*) - Aggregate fxn
Returns the number of rows in a table

HAVING clause, GROUP BY
Allows you to specify conditions on the rows for each group- in other words, which rows should be selected will be based on the conditions you specify

needs to follow GROUP BY clause

Example of HAVING clause, GROUP BY syntax
Example of HAVING clause, GROUP BY syntax
SELECT column1,
SUM(column2)

FROM 'list-of-tables'

GROUP BY 'column-list'

HAVING 'condition';

Mathematical Functions
Mathematical Functions Include:
+, -, *, /, %

What does the ABS(x) return?
Returns the absolute value of x

What does the SIGN(x) return?
Returns the sign of input x as -1,0,1

What does the MOD(x,y) return?
Modulo- returns the integer remainder of x divided by y (same as x%y)

What does the FLOOR(x) return?
Returns the largest integer value that is less than or equal to x

What does the CEILING(x), CEIL(x) return?
Returns the smallest integer value that is greater than or equal to x

What does the POWER(x,y) return?
Returns the value of x raised to the power of y

What does the ROUND(x) return?
Returns the value of x rounded to the nearest whole integer

What does the ROUND(x,d) return?
Returns the value of x rounded to the number of decimal places specified by the value d

What does the SQRT(x) return?
Returns the square-root value of x

Example of TABLE JOIN syntax
SELECT 'list-of-columns'

FROM table1,table2

WHERE 'search-condition(s)'

Select ALL records in 'Customer' table
SELECT * FROM Customers

Select unique records from 'Customer' table
SELECT DISTINCT FROM Customers

Select all data for all customers living in Berlin
SELECT * FROM Customers WHERE City = 'Berlin'

Select all fields for customers NOT living in Berlin
SELECT * FROM Customers WHERE NOT City = 'Berlin'

Select all fields for customers with customer ID equal to '32'
SELECT * FROM Customers WHERE CustomerID = 32

Select all data from customers living in Berlin, having postal code of 12209
SELECT * Customers WHERE City = 'Berlin' and PostalCode = 12209

Select all data for customers in Berlin and London
SELECT * FROM Customers WHERE City = 'Berlin' OR City = 'London'

Select all fields from Customer in order by city
SELECT * FROM Customers
ORDER BY City

Select all fields from Customer in reverse alphabetical order by city
SELECT * FROM Customers
ORDER BY City DESC

Example, inserting record
INSERT INTO Customers(
CustName,
Address,
City,
PostalCode,
Country)
VALUES(
'Hekkan Burger'.....

Select all the records from Customers where data is missing
SELECT * FROM Customers WHERE PostalCode IS NULL

Select all the records from Customer where data is NOT missing
SELECT * FROM Customers WHERE PostalCode IS NOT NULL

Update location in all records to Oslo (city)
UPDATE Customers
SET City = 'Oslo'

Update location in all records to Oslo (city) when the country is Norway
UPDATE Customers
SET City = 'Oslo'
WHERE Country = 'Norway'

Update location in all records to Oslo (city) when the country is Norway, when customer id is 32
UPDATE Customers
SET City = 'Oslo'
Country = 'Norway'
WHERE CustomerID = 32

Delete all records for customers in Norway
DELETE FROM Customers WHERE Country = 'Norway'

Four basic functions of persistent storage
CREATE, RETRIEVE, UPDATE, DELETE

Syntax for SELECT
SELECT column_name, column_name, FROM table_name;

Select all
SELECT * from table_name

Identify the unique records
SELECT DISTINCT
column_names, column_name
FROM table_name;

Extract ONLY those records that fulfill specific criterion
SELECT column_name
FROM table_name
WHERE column_name operator value;

Select data for customers in Berlin or Munchen in Germany
SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Munchen');

Select data from customers in asc or desc order for specific value
SELECT * FROM Customers
ORDER BY column_name ASC
(or replace w DESC)

Select records (Customer) where name starts with 'a'
SELECT * FROM Customers

WHERE City LIKE 'a%'
;

Select all the unique records in Customers
SELECT DISTINCT FROM Customers

Select customers residing in Norway and France
SELECT * FROM Customers

WHERE Country IN

('Norway',
'France')
;

Use the IN operator to select all the records where Country is NOT 'Norway' and NOT 'France'.
SELECT * FROM Customers

WHERE Country NOT IN
('Norway', 'France');

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
SELECT * FROM Products
WHERE Price
BETWEEN 10 AND 20
;

Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.
SELECT * FROM Products
WHERE Price
NOT BETWEEN 10 AND 20
;

Use the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'.
SELECT * FROM Products
WHERE ProductName
BETWEEN 'Geitost' AND 'Pavlova'
;

Select all records where the value of the City column starts with the letter 'a'.
SELECT * FROM Customers

WHERE City LIKE 'a%';

Select all records where the value of the City column ends with the letter 'a'.
SELECT * FROM Customers

WHERE City LIKE '%a' ;

Select all records where the value of the City column contains the letter 'a'.
SELECT * FROM Customers

WHERE City LIKE '%a%' ;

Select all records where the value of the City column starts with letter 'a' and ends with the letter 'b'.
SELECT * FROM Customers

WHERE City LIKE 'a%b' ;

SOURCE https://www.w3schools.com/sql/exercise.asp
https://www.w3schools.com/sql/exercise.asp

Select all records where the second letter of the City is an 'a'.
SELECT * FROM Customers
WHERE City LIKE '_a%';

Select all records where the first letter of the City is an 'a' or a 'b' or a 'c'.
SELECT * FROM Customers
WHERE City LIKE '[abc] %';

Select all records where the first letter of the City starts with anything from an 'a' to an 'f'.
SELECT * FROM Customers
WHERE City LIKE '[a-f]%';

Select all records where the first letter of the City is NOT an 'a' or a 'b' or a 'c'.
SELECT * FROM Customers
WHERE City LIKE '[!abc]%';

Use the IN operator to select all the records where Country is either 'Norway' or 'France'.
SELECT * FROM Customers

WHERE Country IN ('Norway','France');

Use the IN operator to select all the records where Country is NOT 'Norway' and NOT 'France'.
SELECT * FROM Customers

WHERE Country NOT IN ('Norway','France');

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
................................................................................
SQL COMMANDS

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

The result is stored in a result table, called the result-set.
SELECT column_name,column_name
FROM table_name;

SELECT * FROM table_name;

The SELECT DISTINCT statement is used to return only distinct (different) values
SELECT DISTINCT column_name,column_name
FROM table_name;

The WHERE clause is used to extract only those records that fulfill a specified criterion.
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

he AND operator displays a record if both the first condition AND the second condition are true.

The OR operator displays a record if either the first condition OR the second condition is true.
SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';

The ORDER BY keyword is used to sort the result-set.
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

The INSERT INTO statement is used to insert new records in a table.
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

The UPDATE statement is used to update records in a table.
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

The DELETE statement is used to delete records in a table.
DELETE FROM table_name
WHERE some_column=some_value;

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input.

Injected SQL commands can alter SQL statement and compromise the security of a web application.
txtUserId = getRequestString('UserId');
txtSQL = 'SELECT * FROM Users WHERE UserId = ' + txtUserId;

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

The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.
SELECT TOP number|percent column_name(s)
FROM table_name;

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

% A substitute for zero or more characters
_ A substitute for a single character
[charlist] Sets and ranges of characters to match
[^charlist]
or
[!charlist] Matches only a character NOT specified within the brackets
SQL wildcards

The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

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

Basically aliases are created to make column names more readable
SELECT column_name AS alias_name
FROM table_name;

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

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

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

With SQL, you can copy information from one table into another.

The SELECT INTO statement copies data from one table and inserts it into a new table
SELECT *
INTO newtable [IN externaldb]
FROM table1;

With SQL, you can copy information from one table into another.

The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table.
INSERT INTO table2
SELECT * FROM table1;

The CREATE DATABASE statement is used to create a database.
CREATE DATABASE dbname;

The CREATE TABLE statement is used to create a table in a database.

Tables are organized into rows and columns; and each table must have a name.
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

SQL constraints are used to specify rules for the data in a table.

If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the 'P_Id' column and the 'LastName' column to not accept NULL values:
CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The following SQL creates a UNIQUE constraint on the 'P_Id' column when the 'Persons' table is created:

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

he PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain UNIQUE values.

A primary key column cannot contain NULL values.

Most tables should have a primary key, and each table can have only ONE primary key.
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

FOREIGN KEY
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The 'Persons' table:

The CHECK constraint is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
The following SQL creates a CHECK constraint on the 'P_Id' column when the 'Persons' table is created. The CHECK constraint specifies that the column 'P_Id' must only include integers greater than 0.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

The DROP INDEX statement is used to delete an index in a table
DROP INDEX index_name ON table_name

e ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
ALTER TABLE table_name
ADD column_name datatype

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

The AVG() function returns the average value of a numeric column.
SELECT AVG(column_name) FROM table_name

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column
SELECT COUNT(column_name) FROM table_name;

The FIRST() function returns the first value of the selected column.
SELECT FIRST(column_name) FROM table_name;

The LAST() function returns the last value of the selected column.
SELECT LAST(column_name) FROM table_name;

The MAX() function returns the largest value of the selected column.
SELECT MAX(column_name) FROM table_name;

The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name;

The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name) FROM table_name;

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

The UCASE() function converts the value of a field to uppercase.
SELECT UCASE(column_name) FROM table_name;

SELECT UPPER(column_name) FROM table_name;

The LCASE() function converts the value of a field to lowercase.
SELECT LCASE(column_name) FROM table_name;

SELECT LOWER(column_name) FROM table_name;

The MID() function is used to extract characters from a text field
SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;

The LEN() function returns the length of the value in a text field.
SELECT LEN(column_name) FROM table_name;

The ROUND() function is used to round a numeric field to the number of decimals specified.
SELECT ROUND(column_name,decimals) FROM table_name;

The NOW() function returns the current system date and time.
SELECT NOW() FROM table_name;

The FORMAT() function is used to format how a field is to be displayed.
SELECT FORMAT(column_name,format) FROM table_name;

How to select a cateogry that does one thing but not another.
SELECT maker, type FROM product
WHERE type = 'PC' AND type <> 'Laptop'

Unions and Joins: Return the models and prices for all the products (of any type) produced by maker B.
SELECT Product.model, price FROM Product, PC
WHERE Product.model=PC.model AND maker='B'
UNION
SELECT Product.model, price FROM Product, Laptop
WHERE Product.model=Laptop.model AND maker='B'
UNION
SELECT Product.model, price FROM Product, Printer
WHERE Product.model=Printer.model AND maker='B'

CREATE VIEW 'View name' AS 'Query'
Enter this command before your query and it will save it so you can use it over and over again.