click below
click below
Normal Size Small Size show me how
SQL
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 |