| Question | Answer |
| SELECT and WHERE | SELECT <colName1>, <colName2> FROM <table name> WHERE <colNam3> = <param> |
| AND/OR | SELECT * FROM Persons WHERE LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola') |
| ORDER BY | SELECT * FROM Persons
ORDER BY LastName DESC |
| INSERT INTO _____ VALUES | INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob') |
| UPDATE _______
SET | UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob' |
| DELETE | DELETE FROM table_name
WHERE some_column=some_value |
| TOP | SELECT TOP 2 * FROM Persons
SELECT TOP 50 PERCENT * FROM Persons
ORACLE equiv: SELECT *
FROM Persons
WHERE ROWNUM <=5 |
| LIKE | SELECT * FROM Persons
WHERE City LIKE '%tav%'
SELECT * FROM Persons
WHERE City NOT LIKE '%tav%' |
| % | A substitute for zero or more characters |
| _ | A substitute for exactly one character |
| [^charlist]
or
[!charlist] | Any single character not in charlist |
| IN | SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen') |
| BETWEEN | SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
SELECT column_name(s)
FROM table_name
WHERE column_name
NOT BETWEEN value1 AND value2 |
| ALIAS | For tables:
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'
for columns:
SELECT column_name AS alias_name
FROM table_name |
| JOIN or INNER JOIN | SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName |
| LEFT JOIN | SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName |
| RIGHT JOIN | SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName |
| FULL JOIN | SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName |
| UNION (only selects distinct values) | SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2 |
| UNION ALL | SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2 |
| INTO | SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons |
| CREATE DATABASE | CREATE DATABASE database_name |
| CREATE TABLE | CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) |
| NOT NULL | -- can't update or add new records w/o having value for notnull constrained col, col has to have value
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) |
| UNIQUE | CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) |
| UNIQUE (multiple columns) | CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
) |
| UNIQUE (to add after table already created [alter] table) | ALTER TABLE Persons
ADD UNIQUE (P_Id)
for multiple columns:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) |
| UNIQUE (to remove constraint) | ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID |
| PRIMARY KEY | CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
) |
| PRIMARY KEY (multiple columns) | CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
) |
| (To add key after table already created):
PRIMARY KEY | ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) |
| (To remove constraint):
PRIMARY KEY | ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID |
