WWW.APPSERVGRID.COM

A Database

A database is a collection of data that is organized in a manner that facilitates ease of access, as
well as efficient management and updating.
A database is made up of tables that store relevant information.
For example, you would use a database, if you were to create a website like YouTube, which contains
a lot of information like videos, usernames, passwords, comments.
--------------------------------------------------------------------------------------------------------
Database Tables

A table stores and displays data in a structured format consisting of columns and rows that are
similar to those seen in Excel spreadsheets.
Databases often contain multiple tables, each designed for a specific purpose. For example,
imagine creating a database table of names and telephone numbers.
First, we would set up columns with the titles FirstName, LastName and TelephoneNumber.
Each table includes its own set of fields, based on the data it will store.
--------------------------------------------------------------------------------------------------------
Primary Keys

A primary key is a field in the table that uniquely identifies the table records.

The primary key's main features:
- It must contain a unique value for each row.
- It cannot contain NULL values.

For example, our table contains a record for each name in a phone book. The unique ID number would
be a good choice for a primary key in the table, as there is always the chance for more than one
person to have the same name.

- Tables are limited to ONE primary key each.
- The primary key's value must be different for each row.
----------------------------------------------------------------------------------------------------------
What is SQL?

Once you understand what a database is, understanding SQL is easy. SQL stands for Structured Query Language.

SQL is used to access and manipulate a database.
MySQL is a program that understands SQL.

SQL can:
- insert, update, or delete records in a database.
- create new databases, table, stored procedures, views.
- retrieve data from a database, etc.
SQL is an ANSI (American National Standards Institute) standard, but there are different versions of the
SQL language.
Most SQL database programs have their own proprietary extensions in addition to the SQL standard,
but all of them support the major commands.
-------------------------------------------------------------------------------------------------------------
Basic SQL Commands

The SHOW TABLES command is used to display all of the tables in the currently selected MySQL database.
For our example, we have created a database, my_database, with a table called customers.
--------------------------------------------------------------------------------------------------------------
show tables from mysql
show databases
show columns from customers

--------------------------------------------------------------------------------------------------------------
Basic SQL Commands

SHOW COLUMNS displays information about the columns in a given table.

The following example displays the columns in our customers table:
SHOW COLUMNS FROM customers

SHOW COLUMNS displays the following values for each table column:

Field: column name
Type: column data type
Key: indicates whether the column is indexed
Default: default value assigned to the column
Extra: may contain any additional information that is available about a given column
The columns for the customers table have also been created using the PHPMyAdmin tool.
----------------------------------------------------------------------------------------------------------------
SELECT Statement

The SELECT statement is used to select data from a database.
The result is stored in a result table, which is called the result-set.

A query may retrieve information from selected columns or from all columns in the table.
To create a simple SELECT statement, specify the name(s) of the column(s) you need from the table.

Syntax of the SQL SELECT Statement:
SELECT column_list
FROM table_name

- column_list includes one or more columns from which data is retrieved
- table-name is the name of the table from which the information is retrieved

The following SQL statement selects the FirstName from the customers table:
SELECT FirstName FROM customers
----------------------------------------------------------------------------------------------------------------
Multiple Queries

SQL allows to run multiple queries or commands at the same time.

The following SQL statement selects the FirstName and City columns from the customers table:
SELECT FirstName FROM customers;
SELECT City FROM customers;

Remember to end each SQL statement with a semicolon to indicate that the statement is complete and
ready to be interpreted.
In this tutorial, we will use semicolon at the end of each SQL statement.
-----------------------------------------------------------------------------------------------------------------
Case Sensitivity

SQL is case insensitive.
The following statements are equivalent and will produce the same result:
select City from customers;
SELECT City FROM customers;
sElEct City From customers;

It is common practice to write all SQL commands in upper-case.
-----------------------------------------------------------------------------------------------------------------
Syntax Rules

A single SQL statement can be placed on one or more text lines. In addition, multiple SQL statements can
be combined on a single text line.

White spaces and multiple lines are ignored in SQL.
For example, the following query is absolutely correct.

SELECT City
FROM customers;

However, it is recommended to avoid unnecessary white spaces and lines.
Combined with proper spacing and indenting, breaking up the commands into logical lines will make your
SQL statements much easier to read and maintain.
----------------------------------------------------------------------------------------------------------------
Selecting Multiple Columns

As previously mentioned, the SQL SELECT statement retrieves records from tables in your SQL database.

You can select multiple table columns at once.
Just list the column names, separated by commas:

SELECT FirstName, LastName, City
FROM customers;

Do not put a comma after the last column name.
------------------------------------------------------------------------------------------------------------------
Selecting All Columns

To retrieve all of the information contained in your table, place an asterisk (*) sign after the SELECT command,
rather than typing in each column names separately.

The following SQL statement selects all of the columns in the customers table:

SELECT * FROM customers;

In SQL, the asterisk means all.
------------------------------------------------------------------------------------------------------------------
The DISTINCT Keyword

In situations in which you have multiple duplicate records in a table, it might make more sense to return only
unique records, instead of fetching the duplicates.
The SQL DISTINCT keyword is used in conjunction with SELECT to eliminate all duplicate records and
return only unique ones.

The basic syntax of DISTINCT is as follows:

SELECT DISTINCT column_name1, column_name2
FROM table_name;

Note that there are duplicate City names. The following SQL statement selects only distinct values from the
City column:
SELECT DISTINCT City FROM customers;

-----------------------------------------------------------------------------------------------------------------
The LIMIT Keyword

By default, all results that satisfy the conditions specified in the SQL statement are returned. However,
sometimes we need to retrieve just a subset of records. In MySQL, this is accomplished by using the LIMIT
keyword.

The syntax for LIMIT is as follows:

SELECT column list
FROM table_name
LIMIT [number of records];

For example, we can retrieve the first five records from the customers table.
SELECT ID, FirstName, LastName, City
FROM customers LIMIT 5;
------------------------------------------------------------------------------------------------------------------
The LIMIT Keyword

You can also pick up a set of records from a particular offset.
In the following example, we pick up four records, starting from the third position:

SELECT ID, FirstName, LastName, City
FROM customers LIMIT 3, 4;

The reason that it produces results starting from ID number four, and not three, is that MySQL starts
counting from zero, meaning that the offset of the first row is 0, not 1.
------------------------------------------------------------------------------------------------------------------
Fully Qualified Names

In SQL, you can provide the table name prior to the column name, by separating them with a dot.
The following statements are equivalent:

SELECT City FROM customers;

SELECT customers.City FROM customers;

The term for the above-mentioned syntax is called the "fully qualified name" of that column.
This form of writing is especially useful when working with multiple tables that may share the same column
names.
-----------------------------------------------------------------------------------------------------------------
Order By

ORDER BY is used with SELECT to sort the returned data.

The following example sorts our customers table by the FirstName column.

SELECT * FROM customers
ORDER BY FirstName;

As you can see, the rows are ordered alphabetically by the FirstName column.
By default, the ORDER BY keyword sorts the results in ascending order.
-----------------------------------------------------------------------------------------------------------------
Sorting Multiple Columns

ORDER BY can sort retrieved data by multiple columns. When using ORDER BY with more than one column, separate
the list of columns to follow ORDER BY with commas.
Here is the customers table, showing the following records:

To order by LastName and Age:

SELECT * FROM customers
ORDER BY LastName, Age;

As we have two Smiths, they will be ordered by the Age column in ascending order.
The ORDER BY command starts ordering in the same sequence as the columns. It will order by the first
column listed, then by the second, and so on.
-----------------------------------------------------------------------------------------------------------------
Drag and drop from the options below to select distinct names from the ''students'' table, ordered by name.
SELECT DISTINCT name
FROM students
ORDER BY name;
------------------------------------------------------------------------------------------------------------------
The WHERE Statement

The WHERE clause is used to extract only those records that fulfill a specified criterion.
The syntax for the WHERE clause:

SELECT column_list
FROM table_name
WHERE condition;

To SELECT a specific record:
SELECT * FROM customers
WHERE ID = 7;
------------------------------------------------------------------------------------------------------------------
SQL Operators

Comparison Operators and Logical Operators are used in the WHERE clause to filter the data to be selected.
For example, we can display all customers names listed in our table, with the exception of the one with ID 5.

SELECT * FROM customers
WHERE ID != 5;

As you can see, the record with ID=5 is excluded from the list.
------------------------------------------------------------------------------------------------------------------
The BETWEEN Operator

The BETWEEN operator selects values within a range. The first value must be lower bound and the second
value, the upper bound.

The syntax for the BETWEEN clause is as follows:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

The following SQL statement selects all records with IDs that fall between 3 and 7:

SELECT * FROM customers
WHERE ID BETWEEN 3 AND 7;

Drag and drop from the options below to build a query to select the names of students whose ids are
between 13 and 45.
SELECT id, name
FROM students
WHERE id
BETWEEN 13 AND 45;
----------------------------------------------------------------------------------------------------------------
Text Values

When working with text columns, surround any text that appears in the statement with single quotation marks (').

The following SQL statement selects all records in which the City is equal to 'New York'.
SELECT ID, FirstName, LastName, City
FROM customers
WHERE City = 'New York';
-----------------------------------------------------------------------------------------------------------------
Logical Operators

Logical operators can be used to combine two Boolean values and return a result of true, false, or null.
The following operators can be used: AND, OR, IN, NOT
When retrieving data using a SELECT statement, use logical operators in the WHERE clause to combine
multiple conditions.

If you want to select rows that satisfy all of the given conditions, use the logical operator, AND.
To find the names of the customers between 30 to 40 years of age, set up the query as seen here:

SELECT ID, FirstName, LastName, Age
FROM customers
WHERE Age >= 30 AND Age <= 40;

You can combine as many conditions as needed to return the desired results.
-----------------------------------------------------------------------------------------------------------------
OR

If you want to select rows that satisfy at least one of the given conditions, you can use the
logical OR operator.
For example, if you want to find the customers who live either in New York or Chicago, the query would like this:

SELECT * FROM customers
WHERE City = 'New York' OR City = 'Chicago';
------------------------------------------------------------------------------------------------------------------
Combining AND & OR

The SQL AND and OR conditions may be combined to test multiple conditions in a query.
These two operators are called conjunctive operators.
When combining these conditions, it is important to use parentheses, so that the order
to evaluate each condition is known.

The statement below selects all customers from the city "New York" AND with the age equal to "30" OR “35":

SELECT * FROM customers
WHERE City = 'New York'
AND (Age=30 OR Age=35);

You can nest as many conditions as you need.

Drag and drop from the options below to select customers whose ids are either 1 or 2, and whose city is ''Boston''.

SELECT * FROM customers
WHERE (id = 1 OR id = 2)
WHERE city = 'Boston'
------------------------------------------------------------------------------------------------------------------
The IN Operator

The IN operator is used when you want to compare a column with more than one value.
For example, you might need to select all customers from New York, Los Angeles, and Chicago.
With the OR condition, your SQL would look like this:

SELECT * FROM customers
WHERE City = 'New York'
OR City = 'Los Angeles'
OR City = 'Chicago';

Drag and drop from the options below to select users from NY and CA:
SELECT * FROM users
WHERE state = 'NY'
OR state = 'CA'
-----------------------------------------------------------------------------------------------------------------
The IN Operator

You can achieve the same result with a single IN condition, instead of the multiple OR conditions:

SELECT * FROM customers
WHERE City IN ('New York', 'Los Angeles', 'Chicago');

Note the use of parentheses in the syntax.
----------------------------------------------------------------------------------------------------------------
The NOT IN Operator

The NOT IN operator allows you to exclude a list of specific values from the result set.

If we add the NOT keyword before IN in our previous query, customers living in those cities will be excluded:

SELECT * FROM customers
WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago');
------------------------------------------------------------------------------------------------------------------
The CONCAT Function

The CONCAT function is used to concatenate two or more text values and returns the concatenating string.

Let's concatenate the FirstName with the City, separating them with a comma:

SELECT CONCAT(FirstName, ', ' , City) FROM customers;
------------------------------------------------------------------------------------------------------------------
The AS Keyword

A concatenation results in a new column. The default column name will be the CONCAT function.
You can assign a custom name to the resulting column using the AS keyword:

SELECT CONCAT(FirstName,', ', City) AS new_column
FROM customers;
------------------------------------------------------------------------------------------------------------------
Arithmetic Operators

Arithmetic operators perform arithmetical operations on numeric operands. The Arithmetic operators include
addition (+), subtraction (-), multiplication (*) and division (/).
The example below adds 500 to each employee's salary and selects the result:

SELECT ID, FirstName, LastName, Salary+500 AS Salary
FROM employees;

Drag and drop from the options below to select concatenated ''city'' and ''state'' columns, represented with a new custom column named ''new_address''.

SELECT CONCAT (city, ', ', state)
AS new_address
FROM customers;
------------------------------------------------------------------------------------------------------------------
The UPPER Function

The UPPER function converts all letters in the specified string to uppercase.
The LOWER function converts the string to lowercase.

The following SQL query selects all LastNames as uppercase:

SELECT FirstName, UPPER(LastName) AS LastName
FROM employees;

If there are characters in the string that are not letters, this function will have no effect on them.
------------------------------------------------------------------------------------------------------------------
SQRT and AVG

The SQRT function returns the square root of given value in the argument.

Let's calculate the square root of each Salary:

SELECT Salary, SQRT(Salary)
FROM employees;

Similarly, the AVG function returns the average value of a numeric column:

SELECT AVG(Salary) FROM employees;

Drag and drop from the options below to select the average cost from the ''items'' table.
SELECT AVG (cost)
FROM items;
------------------------------------------------------------------------------------------------------------------
The SUM function

The SUM function is used to calculate the sum for a column's values

For example, to get the sum of all of the salaries in the employees table, our SQL query would look like this:
SELECT SUM(Salary) FROM employees;

The sum of all of the employees' salaries is 31000.
------------------------------------------------------------------------------------------------------------------
Subqueries

A subquery is a query within another query.

Let's consider an example. We might need the list of all employees whose salaries are greater than the average.
First, calculate the average:

SELECT AVG(Salary) FROM employees;

As we already know the average, we can use a simple WHERE to list the salaries that are greater than that number.

SELECT FirstName, Salary FROM employees
WHERE Salary > 3100
ORDER BY Salary DESC;

The DESC keyword sorts results in descending order.
Similarly, ASC sorts the results in ascending order.

Drag and drop from the options below to select all items from the ''items'' table for which cost is greater than 463. Order the result by cost in descending order.
SELECT * FROM items
WHERE cost > 463
ORDER BY cost DESC
----------------------------------------------------------------------------------------------------------------
Subqueries

A single subquery will return the same result more easily.

SELECT FirstName, Salary FROM employees
WHERE Salary > (SELECT AVG(Salary) FROM employees)
ORDER BY Salary DESC;

Enclose the subquery in parentheses.
Also, note that there is no semicolon at the end of the subquery, as it is part of our single query.

Drag and drop from the options below to select all items from the ''items'' table for which the cost is greater than the average of costs. Use a subquery to calculate the average cost.
SELECT * FROM items
WHERE cost >
(SELECT AVG (cost)
FROM items )
-----------------------------------------------------------------------------------------------------------------
The Like Operator

The LIKE keyword is useful when specifying a search condition within your WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SQL pattern matching enables you to use "_" to match any single character and "%" to match an arbitrary number of characters (including zero characters).

For example, to select employees whose FirstNames begin with the letter A, you would use the following query:
SELECT * FROM employees
WHERE FirstName LIKE 'A%';

As another example, the following SQL query selects all employees with a LastName ending with the letter "s":
SELECT * FROM employees
WHERE LastName LIKE '%s';

The % wildcard can be used multiple times within the same pattern.
-------------------------------------------------------------------------------------------------------------------
The MIN Function

The MIN function is used to return the minimum value of an expression in a SELECT statement.
For example, you might wish to know the minimum salary among the employees.

SELECT MIN(Salary) AS Salary FROM employees;

All of the SQL functions can be combined together to create a single expression.
--------------------------------------------------------------------------------------------------------------------
Joining Tables
All of the queries shown up until now have selected from just one table at a time.
One of the most beneficial features of SQL is the ability to combine data from two or more tables.
The orders table stores information about individual orders with their corresponding amount:
In SQL, "joining tables" means combining data from two or more tables. A table join creates a
temporary table showing the data from the joined tables.
--------------------------------------------------------------------------------------------------------------------
Joining Tables

Rather than storing the customer name in both tables, the orders table contains a reference to the customer ID that
appears in the customers table. This approach is more efficient, as opposed to storing the same text values in
both tables.
In order to be able to select the corresponding data from both tables, we will need to join them on that condition.
---------------------------------------------------------------------------------------------------------------------
Drag and drop from the options below to select ''id'' from ''students''. Order the results by id, in descending order.
SELECT id FROM students
ORDER BY id DESC
---------------------------------------------------------------------------------------------------------------------
Joining Tables

To join the two tables, specify them as a comma-separated list in the FROM clause:

SELECT customers.ID, customers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE customers.ID=orders.Customer_ID
ORDER BY customers.ID;

Each table contains "ID" and "Name" columns, so in order to select the correct ID and Name, fully
qualified names are used.
Note that the WHERE clause "joins" the tables on the condition that the ID from the customers
table should be equal to the customer_ID of the orders table.

The returned data shows customer orders and their corresponding amount.
Specify multiple table names in the FROM by comma-separating them.
---------------------------------------------------------------------------------------------------------------------
Custom Names

Custom names can be used for tables as well. You can shorten the join statements by giving the tables "nicknames":

SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;

As you can see, we shortened the table names as we used them in our query.
----------------------------------------------------------------------------------------------------------------------
Types of Join

The following are the types of JOIN that can be used in MySQL:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN

INNER JOIN is equivalent to JOIN. It returns rows when there is a match between the tables.

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

Note the ON keyword for specifying the inner join condition.

Only the records matching the join condition are returned.
-----------------------------------------------------------------------------------------------------------------------
LEFT JOIN

The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.

This means that if there are no matches for the ON clause in the table on the right, the join
will still return the rows from the first table in the result.

The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

The OUTER keyword is optional, and can be omitted.

The following SQL statement will return all customers, and the items they might have:

SELECT customers.Name, items.Name
FROM customers LEFT OUTER JOIN items
ON customers.ID=items.Seller_id;

The result set contains all the rows from the left table and matching data from the right table.
If no match is found for a particular row, NULL is returned.
------------------------------------------------------------------------------------------------------------------------
RIGHT JOIN

The RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.
The basic syntax of RIGHT JOIN is as follows:
SELECT table1.column1, table2.column2...
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Again, the OUTER keyword is optional, and can be omitted.

Consider the same example from our previous lesson, but this time with a RIGHT JOIN:

SELECT customers.Name, items.Name FROM customers
RIGHT JOIN items ON customers.ID=items.Seller_id;

The RIGHT JOIN returns all the rows from the right table (items), even if there are no matches in the
left table (customers).
There are other types of joins in the SQL language, but they are not supported by MySQL.
-------------------------------------------------------------------------------------------------------------------------
Set Operation

Occasionally, you might need to combine data from multiple tables into one comprehensive dataset.
This may be for tables with similar data within the same database or maybe there is a need to
combine similar data across databases or even across servers.

To accomplish this, use the UNION and UNION ALL operators.

UNION combines multiple datasets into a single dataset, and removes any existing duplicates.
UNION ALL combines multiple datasets into one dataset, but does not remove duplicate rows.
UNION ALL is faster than UNION, as it does not perform the duplicate removal operation over the data set.
------------------------------------------------------------------------------------------------------------------------
UNION

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

All SELECT statements within the UNION must have the same number of columns. The columns must also have the same data types. Also, the columns in each SELECT statement must be in the same order.
The syntax of UNION is as follows:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SELECT ID, FirstName, LastName, City FROM First
UNION
SELECT ID, FirstName, LastName, City FROM Second;

As you can see, the duplicates have been removed.

TIP:
If your columns don't match exactly across all queries, you can use a NULL (or any other) value such as:
SELECT FirstName, LastName, Company FROM businessContacts
UNION
SELECT FirstName, LastName, NULL FROM otherContacts;
-------------------------------------------------------------------------------------------------------------------------
UNION ALL

UNION ALL selects all rows from each table and combines them into a single table.

The following SQL statement uses UNION ALL to select data from the First and Second tables:

SELECT ID, FirstName, LastName, City FROM First
UNION ALL
SELECT ID, FirstName, LastName, City FROM Second;


As you can see, the result set includes the duplicate rows as well.
--------------------------------------------------------------------------------------------------------------------------
Inserting Data

SQL tables store data in rows, one row after another. The INSERT INTO statement is used to add new rows of
data to a table in the database.
The SQL INSERT INTO syntax is as follows:

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

Make sure the order of the values is in the same order as the columns in the table.

Use the following SQL statement to insert a new row:

INSERT INTO Employees
VALUES (8, 'Anthony', 'Young', 35);

The values are comma-separated and their order corresponds to the columns in the table.

When inserting records into a table using the SQL INSERT statement, you must provide a value for every column that
does not have a default value, or does not support NULL.
------------------------------------------------------------------------------------------------------------------------
Inserting Data

Alternatively, you can specify the table's column names in the INSERT INTO statement:

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

column1, column2, ..., columnN are the names of the columns that you want to insert data into.

INSERT INTO Employees (ID, FirstName, LastName, Age)
VALUES (8, 'Anthony', 'Young', 35);

This will insert the data into the corresponding columns

You can specify your own column order, as long as the values are specified in the same order as the columns.
-------------------------------------------------------------------------------------------------------------------------
Inserting Data

It is also possible to insert data into specific columns only.
INSERT INTO Employees (ID, FirstName, LastName)
VALUES (9, 'Samuel', 'Clark');

The Age column for that row automatically became 0, as that is its default value.
-------------------------------------------------------------------------------------------------------------------------
Updating Data

The UPDATE statement allows us to alter data in the table.

The basic syntax of an UPDATE query with a WHERE clause is as follows:

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

You specify the column and its new value in a comma-separated list after the SET keyword.
If you omit the WHERE clause, all records in the table will be updated!
------------------------------------------------------------------------------------------------------------------------
Updating Data

To update John's salary, we can use the following query:

UPDATE Employees
SET Salary=5000
WHERE ID=1;

Drag and drop from the options below to update the ''students'' table by changing the university's value to ''Stanford'' if the student's name is ''John''.
UPDATE students
SET university='Stanford'
WHERE name='John';
------------------------------------------------------------------------------------------------------------------------
Updating Multiple Columns

It is also possible to UPDATE multiple columns at the same time by comma-separating them:
UPDATE Employees
SET Salary=5000, FirstName='Robert'
WHERE ID=1;

You can specify the column order any way you like in the SET clause.
------------------------------------------------------------------------------------------------------------------------
Deleting Data

The DELETE statement is used to remove data from your table. DELETE queries work much like UPDATE queries.
DELETE FROM table_name
WHERE condition;

For example, you can delete a specific employee from the table:
DELETE FROM Employees
WHERE ID=1;

If you omit the WHERE clause, all records in the table will be deleted!
The DELETE statement removes the data from the table permanently.
------------------------------------------------------------------------------------------------------------------------
SQL Tables

A single database can house hundreds of tables, each playing its own unique role in the database schema.

SQL tables are comprised of table rows and columns. Table columns are responsible for storing many different
types of data, including numbers, texts, dates, and even files.

The CREATE TABLE statement is used to create a new table.
Creating a basic table involves naming the table and defining its columns and each column's data type.
------------------------------------------------------------------------------------------------------------------------
Creating a Table

The basic syntax for the CREATE TABLE statement is as follows:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
);

- The column_names specify the names of the columns we want to create.
- The data_type parameter specifies what type of data the column can hold. For example, use int for whole numbers.
- The size parameter specifies the maximum length of the table's column.
Note the parentheses in the syntax.
-------------------------------------------------------------------------------------------------------------------------
Creating a Table

Assume that you want to create a table called "Users" that consists of four columns: UserID, LastName, FirstName, and City.

Use the following CREATE TABLE statement:
CREATE TABLE Users
(
UserID int,
FirstName varchar(100),
LastName varchar(100),
City varchar(100)
);

varchar is the datatype that stores characters. You specify the number of characters in the parentheses
after the type. So in the example above, our fields can hold max 100 characters long text.
-------------------------------------------------------------------------------------------------------------------------
Data Types

Data types specify the type of data for a particular column.

If a column called "LastName" is going to hold names, then that particular column should have a "varchar"
(variable-length character) data type.

The most common data types:
Numeric
INT -A normal-sized integer that can be signed or unsigned.
FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and
the number of decimals (D).
DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display
length (M) and the number of decimals (D).

Date and Time
DATE - A date in YYYY-MM-DD format.
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970
TIME - Stores the time in HH:MM:SS format.

String Type
CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
VARCHAR(M) - Variable-length character string. Max size is specified in parenthesis.
BLOB - "Binary Large Objects" and are used to store large amounts of binary data, such as images or other
types of files.
TEXT - Large amount of text data.
Choosing the correct data type for your columns is the key to good database design.
------------------------------------------------------------------------------------------------------------------------
Primary Key

The UserID is the best choice for our Users table's primary key.
Define it as a primary key during table creation, using the PRIMARY KEY keyword.

CREATE TABLE Users
(
UserID int,
FirstName varchar(100),
LastName varchar(100),
City varchar(100),
PRIMARY KEY(UserID)
);

Specify the column name in the parentheses of the PRIMARY KEY keyword.
------------------------------------------------------------------------------------------------------------------------
Creating a Table

Now, when we run the query, our table will be created in the database.
You can now use INSERT INTO queries to insert data into the table.
------------------------------------------------------------------------------------------------------------------------
Drag and drop from the options below to create a table with three columns: ''id'' as a primary key, username and
password of type varchar.
CREATE TABLE test (
id int ,
username varchar (30),
password varchar(20),
PRIMARY KEY (id)
);
-------------------------------------------------------------------------------------------------------------------------
SQL Constraints

SQL constraints are used to specify rules for table data.

The following are commonly used SQL constraints:

NOT NULL - Indicates that a column cannot contain any NULL value.
UNIQUE - Does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness
of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY - Enforces the table to accept unique data for a specific column and this constraint create a
unique index for accessing the table faster.
CHECK - Determines whether the value is valid or not from a logical expression.
DEFAULT - While inserting data into a table, if no value is supplied to a column, then the column gets the
value set as DEFAULT.

For example, the following means that the name column disallows NULL values.
name varchar(100) NOT NULL

During table creation, specify column level constraint(s) after the data type of that column.
-------------------------------------------------------------------------------------------------------------------------
AUTO INCREMENT

Auto-increment allows a unique number to be generated when a new record is inserted into a table.
Often, we would like the value of the primary key field to be created automatically every time a new record is inserted.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
Let's set the UserID field to be a primary key that automatically generates a new value:

UserID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (UserID)
--------------------------------------------------------------------------------------------------------------------------
Using Constraints

The example below demonstrates how to create a table using constraints.

CREATE TABLE Users (
id int NOT NULL AUTO_INCREMENT,
username varchar(40) NOT NULL,
password varchar(10) NOT NULL,
PRIMARY KEY(id)
);

The following SQL enforces that the "id", "username", and "password" columns do not accept NULL values.
We also define the "id" column to be an auto-increment primary key field.

When inserting a new record into the Users table, it's not necessary to specify a value for the id column;
a unique new value will be added automatically.
--------------------------------------------------------------------------------------------------------------------------
ALTER TABLE

The ALTER TABLE command is used to add, delete, or modify columns in an existing table.
You would also use the ALTER TABLE command to add and drop various constraints on an existing table.
The following SQL code adds a new column named DateOfBirth:

ALTER TABLE People ADD DateOfBirth date;

All rows will have the default value in the newly added column, which, in this case, is NULL.
--------------------------------------------------------------------------------------------------------------------------
Dropping

The following SQL code demonstrates how to delete the column named DateOfBirth in the People table.

ALTER TABLE People
DROP COLUMN DateOfBirth;

To delete the entire table, use the DROP TABLE command:

DROP TABLE People;
--------------------------------------------------------------------------------------------------------------------------
Renaming

The ALTER TABLE command is also used to rename columns:

ALTER TABLE People
CHANGE FirstName name varchar(100);

This query will rename the column called FirstName to name.

Renaming Tables

You can rename the entire table using the RENAME command:

RENAME TABLE People TO Users;

This will rename the table People to Users.
--------------------------------------------------------------------------------------------------------------------------
Views

In SQL, a VIEW is a virtual table that is 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.

Views allow us to:
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables and use it to generate reports.

To create a view:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

The SELECT query can be as complex as you need it to be. It can contain multiple JOINS and other commands.
--------------------------------------------------------------------------------------------------------------------------
Creating Views

Let's create a view that displays each employee's FirstName and Salary.
CREATE VIEW List AS
SELECT FirstName, Salary
FROM Employees;

Now, you can query the List view as you would query an actual table.
SELECT * FROM List;
A view always shows up-to-date data! The database engine uses the view's SQL statement to recreate
the data each time a user queries a view.

Drag and drop from the options below to create a view named ''temp'' for students with the highest marks.
CREATE VIEW temp AS
SELECT id, name, mark
FROM students
ORDER BY mark DESC
LIMIT 10;
-------------------------------------------------------------------------------------------------------------------------
Updating a View

You can update a view by using the following syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

The example below updates our List view to select also the LastName:

CREATE OR REPLACE VIEW List AS
SELECT FirstName, LastName, Salary
FROM Employees;

You can delete a view with the DROP VIEW command.

DROP VIEW List;
-------------------------------------------------------------------------------------------------------------------------
Drag and drop from the options below to remove the column ''temp'' from the table ''students''.
ALTER TABLE students
DROP COLUMN temp
-------------------------------------------------------------------------------------------------------------------------
Your boss asks you to print the list of the first one hundred customers who have balances greater than $1000 or who are from NY.
SELECT * FROM customers
WHERE balance > 1000
OR city = 'NY'
LIMIT 100
---------------------------------------------------------