Question 1

SELECT and WHERE

Accepted Answer

SELECT , FROM WHERE =

Question 2

AND/OR

Accepted Answer

SELECT * FROM Persons WHERE LastName='Svendson'
AND (FirstName='Tove' OR FirstName='Ola')

Question 3

ORDER BY

Accepted Answer

SELECT * FROM Persons
ORDER BY LastName DESC

Question 4

INSERT INTO _____ VALUES

Accepted Answer

INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

INSERT INTO Persons (P_Id, LastName, FirstName)
VALUES (5, 'Tjessem', 'Jakob')

Question 5

UPDATE _______
SET

Accepted Answer

UPDATE Persons
SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'

Question 6

DELETE

Accepted Answer

DELETE FROM table_name
WHERE some_column=some_value

Question 7

TOP

Accepted Answer

SELECT TOP 2 * FROM Persons
SELECT TOP 50 PERCENT * FROM Persons

ORACLE equiv: SELECT *
FROM Persons
WHERE ROWNUM <=5

Question 8

LIKE

Accepted Answer

SELECT * FROM Persons
WHERE City LIKE '%tav%'

SELECT * FROM Persons
WHERE City NOT LIKE '%tav%'

Question 9

%

Accepted Answer

A substitute for zero or more characters

Question 10

_

Accepted Answer

A substitute for exactly one character

Question 11

[^charlist]
or

[!charlist]

Accepted Answer

Any single character not in charlist

Question 12

IN

Accepted Answer

SELECT * FROM Persons
WHERE LastName IN ('Hansen','Pettersen')

Question 13

BETWEEN

Accepted Answer

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

Question 14

ALIAS

Accepted Answer

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

Question 15

JOIN or INNER JOIN

Accepted Answer

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Question 16

LEFT JOIN

Accepted Answer

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Question 17

RIGHT JOIN

Accepted Answer

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Question 18

FULL JOIN

Accepted Answer

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Question 19

UNION (only selects distinct values)

Accepted Answer

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Question 20

UNION ALL

Accepted Answer

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

Question 21

INTO

Accepted Answer

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons

Question 22

CREATE DATABASE

Accepted Answer

CREATE DATABASE database_name

Question 23

CREATE TABLE

Accepted Answer

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

Question 24

NOT NULL

Accepted Answer

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

Question 25

UNIQUE

Accepted Answer

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Question 26

UNIQUE (multiple columns)

Accepted Answer

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

Question 27

UNIQUE (to add after table already created [alter] table)

Accepted Answer

ALTER TABLE Persons
ADD UNIQUE (P_Id)

for multiple columns:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

Question 28

UNIQUE (to remove constraint)

Accepted Answer

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

Question 29

PRIMARY KEY

Accepted Answer

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Question 30

PRIMARY KEY (multiple columns)

Accepted Answer

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

Question 31

(To add key after table already created):
PRIMARY KEY

Accepted Answer

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

Question 32

(To remove constraint):
PRIMARY KEY

Accepted Answer

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

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

"Know" box contains:
Time elapsed:
Retries:

SQL