Master Code

SELECT * FROM Customers;

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE - deletes data from a database
• INSERT INTO - inserts new data into a database
• CREATE DATABASE - creates a new database
• ALTER DATABASE - modifies a database
• CREATE TABLE - creates a new table
• ALTER TABLE - modifies a table
• DROP TABLE - deletes a table
• CREATE INDEX - creates an index (search key)
• DROP INDEX - deletes an index

CREATE DATABASE databasename;

DROP DATABASE databasename;

              
  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
  );
              
            

              
  CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
  );
              
            

              
  CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;
              
            

              
  CREATE TABLE TestTable AS
    SELECT customername, contactname
    FROM customers;
              
            

DROP TABLE table_name;

TRUNCATE TABLE table_name;

              
  CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
  );
              
            

• 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

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

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

              
  ALTER TABLE Persons
  DROP INDEX UC_Person;
              
            

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

              
  ALTER TABLE Persons
  DROP PRIMARY KEY;
              
            

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

• Restrict : Nothing gonna be delete if there is a child row
• Cascade : the child row will be delete / update too
• Set Null : the child column will be set to null if you delete the parent
• No action : The child row will not be concern of the delete / update

              
  CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
  );
  CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
  );
              
            

              
  ALTER TABLE Orders
  DROP FOREIGN KEY FK_PersonOrder;
              
            

              
  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
  DROP CHECK CHK_PersonAge;
              
            

              
  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 DROP DEFAULT;
              
            

              
  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;

              
  SELECT column1, column2, ...
  FROM table_name;
              
            

SELECT * FROM table_name;

SELECT CustomerName, City, Country FROM Customers;

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

              
  SELECT * FROM Customers
  WHERE Country = 'Mexico';
              
            

              
  SELECT column_name AS alias_name
  FROM table_name;
              
            

              
  SELECT CustomerID AS ID, CustomerName AS Customer
  FROM Customers;
              
            

              
  SELECT CustomerName AS Customer, ContactName AS "Contact Person"
  FROM Customers;
              
            

              
  SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address
  FROM Customers;
              
            

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

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

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

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

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

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

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

              
  SELECT * FROM Customers
  ORDER BY Country DESC;
              
            

              
  SELECT * FROM Customers
  ORDER BY Country ASC, CustomerName DESC;
              
            

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

              
  SELECT SupplierName
  FROM Suppliers
  WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
              
            

              
  SELECT column_names
  FROM table_name
  WHERE column_name IS NULL;
              
            

              
  SELECT column_names
  FROM table_name
  WHERE column_name IS NOT NULL;
              
            

1. Specify both the column names and the values to be inserted:

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

2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

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

              
  INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
  VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
              
            

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

              
  UPDATE Customers
  SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
  WHERE CustomerID = 1;
              
            

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

DELETE FROM table_name;