DATABASE _ CREATE

https://www.w3schools.com/mysql/default.asp

Create a new database

Write the correct SQL statement to create a new database called testDB.
CREATE DATABASE testDB;

Delete a database

Write the correct SQL statement to delete a database named testDB.
DROP DATABASE testDB;

Create a new table

Write the correct SQL statement to create a new table called Persons.
CREATE TABLE Persons
(PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255) );

Delete a table

Write the correct SQL statement to delete a table called Persons.
DROP TABLE Persons;

Truncate

Use the TRUNCATE statement to delete all data inside a table.
TRUNCATE TABLE Persons;

Alter add column

Add a column of type DATE called Birthday.
ALTER TABLE Persons ADD Birthday DATE;

Alter delete column

Delete the column Birthday from the Persons table.
ALTER TABLE Persons DROP COLUMN Birthday;

DATABASE_Constraints

• 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 on CREATE TABLE

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);

UNIQUE

The UNIQUE constraint ensures that all values in a column are different.

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
    UNIQUE (ID)
);

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.

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

FOREIGN KEY

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.

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

CHECK

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

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’)
);

DEFAULT

The DEFAULT constraint is used to set a default value for a column.

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()
);

AUTO INCREMENT

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;

CREATE INDEX

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.

CREATE INDEX idx_pname ON Persons (LastName, FirstName);

CREATE VIEW

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

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = ‘Brazil’;

WORKING WITH DATES

• 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

SQL TUTORIAL I (BASIC)