Practice #1

• Creating Table

CREATE TABLE dboCustomer (
    CustomerID INTEGER AUTO_INCREMENT PRIMARY KEY, 
    FirstName NVARCHAR(20) NOT NULL, 
    MiddleName NVARCHAR(20) NULL, 
    LastName NVARCHAR(20) NULL, 
    AccountOpened DATETIME DEFAULT NOW(), 
    CreditLimit DECIMAL(6, 2) DEFAULT 1000.00)
);

• Adding Content (Solve the Wrong Statements)

INSERT INTO dboCustomer VALUES(10, 'Dan', "D", "Drayton", "2017-3-30 10:10:10", 500.00);

INSERT INTO dboCustomer VALUES(20, 'Matt', NULL, "Damon", DEFAULT, DEFAULT);

INSERT INTO dboCustomer (FirstName, LastName) VALUES ("Naomi", "Sharp");

INSERT INTO dboCustomer VALUES(30, NULL, "F", "Formiga", "2017-3-30 10:10:10", 500);

INSERT INTO dboCustomer VALUES(40, "Patrick", "P", "Pesticide", "2017-3-30 10:10:10", NULL);

• Querying Data

SELECT * FROM dboCustomer;

SELECT 
    CustomerID, FirstName, LastName 
FROM dboCustomer;

Select 
    CustomerID, 
    FirstName + " " + LastName AS FullName, 
    TIMESTAMPDIFF(DAYS, AccountOpened, NOW()) AS AccountDays 
FROM dboCustomer;

SELECT 
    CustomerID, FirstName, LastName, CreditLimit 
FROM dboCustomer 
WHERE CreditLimit > 500;