What is rdbms?

RDBMS stands for Relational Database Management System.

RDBMS is a program used to maintain a relational database.
RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL
Server, Oracle, and Microsoft Access.

A table is a collection of related data entries, and it consists of columns and rows.
A column holds specific information about every record in the table.
A record (or row) is each individual entry that exists in a table.
A relational database defines database relationships in the form of tables. The
tables are related to each other - based on data common to each.

SQL is the standard language for dealing with Relational Databases.

SQL is used to insert, search, update, and delete database records.
SQL keywords are NOT case sensitive
Semicolon should be kept at the end of each statement;

Select:-
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT * FROM table_name;
SELECT CustomerName, City, Country FROM Customers;

The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you
only want to list the different (distinct) values.
SELECT DISTINCT Country FROM Customers;
SELECT COUNT(DISTINCT Country) FROM Customers;

Where:-
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
Note: The WHERE clause is not only used in SELECT statements, it is also used
in UPDATE, DELETE, etc.!
SELECT * FROM Customers WHERE Country = 'Mexico';
SELECT * FROM Customers
WHERE CustomerID = 1;
Operators used in where clause:-
And, or and not:-
 The AND operator displays a record if all the conditions separated by AND are
TRUE.
 The OR operator displays a record if any of the conditions separated by OR is
TRUE.
NOT operator displays a record if the condition(s) is NOT TRUE.
SELECT * FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';
SELECT * FROM Customers
WHERE City = 'Berlin' OR City = 'Stuttgart';
SELECT * FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
SELECT * FROM Customers
WHERE NOT Country = 'Germany';
SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');
SELECT * FROM Customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA';

Order by:-
The ORDER BY keyword is used to sort the result-set in ascending or descending
order.
The ORDER BY keyword sorts the records in ascending order by default.
SELECT * FROM Customers
ORDER BY Country;
SELECT * FROM Customers
ORDER BY Country DESC;
This means that it orders by Country, but if some rows have the same Country, it
orders them by CustomerName:
SELECT * FROM Customers
ORDER BY Country, CustomerName;
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Insert into:-
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

Null values:-
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record
without adding a value to this field. Then, the field will be saved with a NULL value.
Note: A NULL value is different from a zero value or a field that contains spaces. A
field with a NULL value is one that has been left blank during record creation!
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;

Update:-
The UPDATE statement is used to modify the existing records in a table.
Note: Be careful when updating records in a table! Notice the WHERE clause in
the UPDATE statement. The WHERE clause specifies which record(s) that should be
updated. If you omit the WHERE clause, all records in the table will be updated!
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;
UPDATE Customers
SET PostalCode = 00000
WHERE Country = 'Mexico';
UPDATE Customers
SET PostalCode = 00000;

Delete:-
The DELETE statement is used to delete existing records in a table.
Note: If you omit the WHERE clause, all records in the table will be deleted!
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
It is possible to delete all rows in a table without deleting the table. This means that
the table structure, attributes, and indexes will be intact:
DELETE FROM table_name;

Limit:-
The LIMIT clause is used to specify the number of records to return.
The LIMIT clause is useful on large tables with thousands of records. Returning a
large number of records can impact performance.
SELECT * FROM Customers
LIMIT 3;
After first three rows below code is used:-
SELECT * FROM Customers
LIMIT 3 OFFSET 3;
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;
SELECT * FROM Customers
ORDER BY Country
LIMIT 3;

Min and max functions:-

The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
SELECT MIN(Price) AS SmallestPrice
FROM Products;
SELECT MAX(Price) AS LargestPrice
FROM Products;

Count, avg and sum:-

The COUNT() function returns the number of rows that matches a specified criterion.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.
SELECT COUNT(ProductID)
FROM Products;
SELECT AVG(Price)
FROM Products;
SELECT SUM(Quantity)
FROM OrderDetails;
Note: NULL values are ignored for all three.

Like:-
The LIKE operator is used in a WHERE clause to search for a specified pattern in a
column.
There are two wildcards often used in conjunction with the LIKE operator:
 The percent sign (%) represents zero, one, or multiple characters
 The underscore sign (_) represents one, single character
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers

WHERE CustomerName NOT LIKE 'a%';

Wildcards:-
A wildcard character is used to substitute one or more characters in a string.
SELECT * FROM Customers
WHERE City LIKE 'ber%';
SELECT * FROM Customers
WHERE City LIKE '%es%';
SELECT * FROM Customers
WHERE City LIKE '_ondon';
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

In:-
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

Between:-
The BETWEEN operator selects values within a given range. The values can be
numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun
Seasoning"
ORDER BY ProductName;
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di
Giovanni'
ORDER BY ProductName;
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

Aliases:-
Aliases are used to give a table, or a column in a table, a temporary name.
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
Note: Single or double quotation marks are required if the alias name contains
spaces:
SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;
The following SQL statement creates an alias named "Address" that combine four
columns (Address, PostalCode, City and Country):
SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City,
Country) AS Address
FROM Customers;
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

Joins:-
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
Inner join and join are same
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
 INNER JOIN: Returns records that have matching values in both tables
 LEFT JOIN: Returns all records from the left table, and the matched records
from the right table
 RIGHT JOIN: Returns all records from the right table, and the matched records
from the left table
 CROSS JOIN: Returns all records from both tables

Inner join:-
The INNER JOIN keyword selects records that have matching values in both tables.
Note: The INNER JOIN keyword selects all rows from both tables as long as there is
a match between the columns. If there are records in the "Orders" table that do not
have matches in "Customers", these orders will not be shown!
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
Left join:-
The LEFT JOIN keyword returns all records from the left table (table1), and the
matching records (if any) from the right table (table2).
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the left table (Customers),
even if there are no matches in the right table (Orders).

Right join:-
The RIGHT JOIN keyword returns all records from the right table (table2), and the
matching records (if any) from the left table (table1).
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the right table (Employees),
even if there are no matches in the left table (Orders).

Cross join:-
The CROSS JOIN keyword returns all records from both tables (table1 and table2).
Note: CROSS JOIN can potentially return very large result-sets!
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders;
Note: The CROSS JOIN keyword returns all matching records from both tables
whether the other table matches or not. So, if there are rows in "Customers" that do
not have matches in "Orders", or if there are rows in "Orders" that do not have
matches in "Customers", those rows will be listed as well.
If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS
JOIN will produce the same result as the INNER JOIN clause
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
CROSS JOIN Orders
WHERE Customers.CustomerID=Orders.CustomerID;

Self join:-
A self join is a regular join, but the table is joined with itself.
SELECT A.CustomerName AS CustomerName1,
B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

Union operator:-
The UNION operator is used to combine the result-set of two or
more SELECT statements.
 Every SELECT statement within UNION must have the same number of
columns
 The columns must also have similar data types
 The columns in every SELECT statement must also be in the same order
The UNION operator selects only distinct values by default. To allow duplicate
values, use UNION ALL
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
Note: If some customers or suppliers have the same city, each city will only be
listed once, because UNION selects only distinct values. Use UNION ALL to also
select duplicate values!
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

Group by:-
The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more
columns.
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM
Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

Having:-
The HAVING clause was added to SQL because the WHERE keyword cannot be used
with aggregate functions.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

Exists:-
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID =
Suppliers.supplierID AND Price < 20);

Any, all:-
The ANY and ALL operators allow you to perform a comparison between a single
column value and a range of other values.
The ANY operator:
 returns a boolean value as a result
 returns TRUE if ANY of the subquery values meet the condition
ANY means that the condition will be true if the operation is true for any of the
values in the range.
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <,
or <=).
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
The ALL operator:
 returns a boolean value as a result
 returns TRUE if ALL of the subquery values meet the condition
  is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values
in the range.
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
SELECT ALL ProductName
FROM Products
WHERE TRUE;
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);

Insert into select statement:-

The INSERT INTO SELECT statement copies data from one table and inserts it into
another table.
The INSERT INTO SELECT statement requires that the data types in source and
target tables matches.
Note: The existing records in the target table are unaffected.
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

Case:-
The CASE statement goes through conditions and returns a value when the first
condition is met (like an if-then-else statement). So, once a condition is true, it will
stop reading and return the result. If no conditions are true, it returns the value in
the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);

Null functions:-
SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;
In the example above, if any of the "UnitsOnOrder" values are NULL, the result will
be NULL.
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;

Comments:-
Comments are used to explain sections of SQL statements, or to prevent execution
of SQL statements.
-- Select all:
SELECT * FROM Customers -- WHERE City='Berlin';
-- SELECT * FROM Customers;
/*Select all the columns
of all the records
in the Customers table:*/
SELECT CustomerName, /*City,*/ Country FROM Customers;
SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;

Operators:-
Arithmetic operators:-

Bitwise operators:-

Comparison operators:-
Compound operators:-

Logical operators:-
Mysql database:-
For doing anything in mysql database we need to have admin privileges.
Create db:-
The CREATE DATABASE statement is used to create a new SQL database.
CREATE DATABASE testDB;
Tip: Make sure you have admin privilege before creating any database. Once a
database is created, you can check it in the list of databases with the following SQL
command: SHOW DATABASES;

Drop db:-
The DROP DATABASE statement is used to drop an existing SQL database.
Note: Be careful before dropping a database. Deleting a database will result in loss
of complete information stored in the database!
DROP DATABASE testDB;

Create table:-
The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
A copy of an existing table can also be created using CREATE TABLE.
The new table gets the same column definitions. All columns or specific columns
can be selected.
If you create a new table using an existing table, the new table will be filled with the
existing values from the old table.
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

Drop table:-
The DROP TABLE statement is used to drop an existing table in a database.
DROP TABLE Shippers;
The TRUNCATE TABLE statement is used to delete the data inside a table, but not
the table itself.
TRUNCATE TABLE table_name;

Alter table:-
The ALTER TABLE statement is used to add, delete, or modify columns in an existing
table.
The ALTER TABLE statement is also used to add and drop various constraints on an
existing table.
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE Customers
DROP COLUMN Email;
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
ALTER TABLE Persons
ADD DateOfBirth date;
ALTER TABLE Persons
MODIFY COLUMN DateOfBirth year;
ALTER TABLE Persons
DROP COLUMN DateOfBirth;

Constraints:-
Constraints can be specified when the table is created with the CREATE
TABLE statement, or after the table is created with the ALTER TABLE statement.
The following constraints are commonly used in SQL:
 NOT NULL - Ensures that a column cannot have a NULL value
 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very
quickly

Not null constraint:-

By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert
a new record, or update a record without adding a value to this field.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
ALTER TABLE Persons
MODIFY Age int NOT NULL;

Unique constraint:-
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness
for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY
KEY constraint per table.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
ALTER TABLE Persons
ADD UNIQUE (ID);
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
ALTER TABLE Persons
DROP INDEX UC_Person;

Primary key constraint:-

The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can
consist of single or multiple columns (fields).
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However,
the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must
have been declared to not contain NULL values (when the table was first created).
ALTER TABLE Persons
DROP PRIMARY KEY;

Foreign key constraint:-

The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the
primary key is called the referenced or parent table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the
foreign key column, because it has to be one of the values contained in the parent
table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
 CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

Check constraint:-
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 column it will allow 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.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
ALTER TABLE Persons
ADD CHECK (Age>=18);
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Default constraint:-
The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CURRENT_DATE()
);
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
ALTER TABLE Persons
ALTER City DROP DEFAULT;

Create index:-
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database more quickly than otherwise.
The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without
(because the indexes also need an update). So, only create indexes on columns that
will be frequently searched against.
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX idx_lastname
ON Persons (LastName);
CREATE INDEX idx_pname
ON Persons (LastName, FirstName);
ALTER TABLE table_name
DROP INDEX index_name;

Auto increment field:-

Auto-increment allows a unique number to be generated automatically when a new
record is inserted into a table.
Often this is the primary key field that we would like to be created automatically
every time a new record is inserted.
MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1
for each new record.
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
ALTER TABLE Persons AUTO_INCREMENT=100;
When we insert a new record into the "Persons" table, we do NOT have to specify a
value for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

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.
MySQL comes with the following data types for storing a date or a date/time value
in the database:
 DATE - format YYYY-MM-DD
 DATETIME - format: YYYY-MM-DD HH:MI:SS
 TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
 YEAR - format YYYY or YY
Note: The date data type are set for a column when you create a new table in your
database!
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
Note: Two dates can easily be compared if there is no time component involved!
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with no time
portion.
Tip: To keep your queries simple and easy to maintain, do not use time-components
in your dates, unless you have to!

Views:-
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 statements and functions to a view and present the data as if the
data were coming from one single table.
Note: A view always shows up-to-date data! The database engine recreates the
view, every time a user queries it.
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
Query the view by: SELECT * FROM [Brazil Customers];
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
SELECT * FROM [Products Above Average Price];
A view can be updated with the CREATE OR REPLACE VIEW statement.
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
DROP VIEW [Brazil Customers];

String data types:-

Numeric data types:-
Date and time data types:-

Functions:-