SQL - Basics

• SELECT - extracts data from a database
• UPDATE - updates data in a database
• DELETE FROM - 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

NOT

SELECT * FROM Customer WHERE NOT City = "Berlin";

ORDER BY

SELECT * FROM Customers ORDER BY Country, City;

INSERT INTO

INSERT INTO Customers(
    CustomerName, Address,
    City, PostalCode, Country)
VALUES(
    'Hekkan Burger', 'Gateveien 15',
    'Sandnes','4306', 'Norway');

UPDATE

UPDATE Customers SET City = 'Oslo';
 
UPDATE Customers
SET City = 'Oslo', Country = 'Norway'
WHERE CustomerID = 32;

DELETE FROM

DELETE FROM Customers WHERE Country = 'Norway';

COUNT

Return the number of records that have the Price value set to 18.

SELECT COUNT(*) FROM Products WHERE Price = 18;

LIKE

Select all records where the value of the City column:

• starts with the letter "a":

SELECT * FROM Customers WHERE City LIKE 'a%';

• ends with the letter "a":

SELECT * FROM Customers WHERE City LIKE '%a';

• contains the letter "a":

SELECT * FROM Customers WHERE City like '%a%';

• starts with letter "a" and ends with the letter "b":

SELECT * FROM Customers WHERE City LIKE 'a%b';

• does NOT start with the letter "a":

SELECT * FROM Customers WHERE CITY NOT LIKE 'a%';

• second letter of City is an "a":

SELECT * FROM Customers WHERE City LIKE '_a%';

• select all records where the first letter of the City is an "a" or a "c" or an "s":

SELECT * FROM Customers WHERE City LIKE '[acs]%';

• select all records where the first letter of the City starts with anything from an "a" to an "f".

SELECT * FROM Customers WHERE City LIKE '[a-f]%';

• select all records where the first letter of the City is NOT an "a" or a "c" or an "f".

SELECT * FROM Customers WHERE City LIKE '[!acf]%';

IN

Use the IN operator to select all the records where Country is either "Norway" or "France".

SELECT * FROM Customers
WHERE Country IN ("Norway",'France');

Use the IN operator to select all the records where Country is NOT "Norway" and NOT "France".

SELECT * FROM Customers
WHERE Country NOT IN ('Norway', 'France');

Use the BETWEEN operator to select all the records where the value of the Price column is between 10 and 20.

SELECT * FROM Products
WHERE Price
BETWEEN 10 AND 20;

Use the BETWEEN operator to select all the records where the value of the Price column is NOT between 10 and 20.

SELECT * FROM Products
WHERE Price
NOT BETWEEN 10 AND 20;

Use the BETWEEN operator to select all the records where the value of the ProductName column is alphabetically between 'Geitost' and 'Pavlova'.

SELECT * FROM Products
WHERE ProductName
BETWEEN 'Geitost' AND 'Pavlova';

Select all the records from the Customers table plus all the matches in the Orders table.

SELECT *
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

GROUP BY

List the number of customers in each country.

SELECT COUNT(CustomerID),
Country
FROM Customers
GROUP BY Country;

List the number of customers in each country, ordered by the country with the most customers first.

SELECT COUNT(CustomerID),
Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

Database

Delete a database named testDB.

DROP DATABASE testDB;

Delete a table called Persons

DROP TABLE Persons;

Delete all data inside a table

TRUNCATE TABLE Persons;

Add a column of type DATE called Birthday.

ALTER TABLE Persons
ADD Birthday DATE;

Delete the column Birthday from the Persons table.

ALTER TABLE Persons
DROP COLUMN Birthday;