SQL Tutorial

My conspectus of a w3schools SQL tutorial.

…

…

...

SELECT col1, col2, col3 FROM tab1;

SELECT DISTINCT col1, col2, col3 FROM tab1;

SELECT COUNT (DISTINCT col1) FROM tab1;

SELECT COUNT (*) 
FROM (SELECT DISTINCT Country FROM Customers);

SELECT * FROM Customers 
WHERE Country='Mexico';

• Operators:
  • =
  • >
  • <
  • >=
  • ⇐
  • <>
  • !=
  • BETWEEN
  • LIKE
  • IN

SELECT * FROM tab1 
ORDER BY col1 ASC | DESC;

...

...

...

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

...

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

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

The SELECT INTO statement copies data from one table into a new table.

SELECT * INTO CustomersBackup2017
FROM Customers;

...

-- create stored procedure
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;
 
-- execute stored procedure
EXEC SelectAllCustomers;

...

CREATE DATABASE testDB; 

DROP DATABASE testDB;

...

CREATE TABLE Persons (
    PersonID INT,
    LastName VARCHAR(255),
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255)
);

DROP TABLE Shippers;

-- truncate table deletes all data in the table, but not the table itself
TRUNCATE TABLE TABLE_NAME;

ALTER TABLE Customers
ADD Email VARCHAR(255);
 
ALTER TABLE Customers
DROP COLUMN Email;
 
-- change data type
ALTER TABLE TABLE_NAME
ALTER COLUMN column_name new_datatype;

...

CREATE TABLE TABLE_NAME (
    column1 datatype CONSTRAINT,
    column2 datatype CONSTRAINT,
    column3 datatype CONSTRAINT,
    ....
);

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

...

-- one column as PK
CREATE TABLE Persons (
    ID INT NOT NULL PRIMARY KEY,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT
); 
 
-- multiple columns as PK
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 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 PRIMARY KEY,
    OrderNumber INT NOT NULL,
    PersonID INT FOREIGN KEY REFERENCES Persons(PersonID)
);

...

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 view_name AS
SELECT column1, column2, ...
FROM TABLE_NAME
WHERE condition;

SQL injection is a code injection technique that might destroy your database.

SQL injection is one of the most common web hacking techniques.

SQL injection is the placement of malicious code in SQL statements, via web page input.

-- this statement delivers not the data only from user 105, but the full table
SELECT * FROM Users WHERE UserId = 105 OR 1=1;

...