SQL Flashcards
Get all records from a table
SELECT * FROM table;
Get specific fields from a table
SELECT field1,field2 FROM table;
Get only different values from a table
SELECT DISTINCT field FROM table;
Get records with a field equal to a string value and another equal to a numeric value
SELECT * FROM table WHERE field1=’value’ AND field2=1;
Get all records from a table sorted by two fields, ascending
SELECT * FROM table ORDER BY field1,field2;
Get all records from a table sorted by a field, descending
SELECT * FROM table ORDER BY field DESC;
Add a record to a table by filling all columns
INSERT INTO table VALUES (value1, value2, …);
Add a record to a table by filling some columns
INSERT INTO table (field1, field2) VALUES (value1, value2);
Change values for records conditionally
UPDATE table SET field1=value1,field2=value2 WHERE condition;
Remove records conditionally
DELETE FROM table WHERE condition;
Get a certain number of records
SELECT TOP count field1,field2 FROM table;
Get a certain percentage of records
SELECT TOP pcnt PERCENT field1,field2 FROM table;
Get records with field1 starting with ‘happy’
SELECT * FROM table WHERE field1 LIKE ‘happy%’;
Get records with field1 not ending with ‘happy’
SELECT * FROM table WHERE field1 NOT LIKE ‘%happy’
Pattern for many unknown characters
%
Pattern for single unknown character
_
Pattern for character set
[ab-d]
Pattern for NOT character set
[!abc]
Get all records with field1 equal to any values in a set
SELECT * FROM table WHERE field1 IN (‘a’,’b’);
Get all records with field1 alphabetically between two strings
SELECT * FROM table WHERE field1 BETWEEN ‘astring’ AND ‘bstring’;
Create a short name for a table
SELECT o.Field1, c.Field2 FROM table1 as o, table2 as c WHERE condition;
Get associated records from two tables where there is at least one match in both
SELECT t1.f1,t2.f2 FROM t1 INNER JOIN t2 ON t1.f=t2.f
Get associated records from two tables showing all records from first table
SELECT t1.f1,t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f2
Get associated records from two tables showing all records from second table
SELECT t1.f1,t2.f2 FROM t1 RIGHT JOIN t2 ON t1.f1=t2.f2
Get all records from two tables and line up matches
SELECT t1.f1, t2.f2 FROM t1 FULL JOIN t2 ON t1.f1=t2.f2
Use two SELECTs in one statement to get distinct values
SELECT f1 FROM t1 UNION SELECT f2 FROM t2;
Use two SELECTs in one statement to get all values
SELECT f1 FROM t1 UNION ALL SELECT f2 FROM t2;
Copy data to another table
SELECT * INTO newtable [IN db] FROM oldtable
Make a new database
CREATE DATABASE name
Create a new table
CREATE TABLE name (fieldname type constraint, fieldname type constraint)
Constraint so that field will not accept null values
NOT NULL
Constraint so that field will will have unique values
UNIQUE
Add a constraint to existing field
ALTER TABLE table ADD constraint (fieldname)
Drop a constraint from existing field
ALTER TABLE table DROP CONSTRAINT field
Constraint for primary key
PRIMARY KEY
Constraint for key that maps to another table
FOREIGN KEY
Add foreign key constraint to existing field
ALTER TABLE tablename ADD FOREIGN KEY fieldname REFERENCES othertable(otherfield)
Constraint on a field to restrict values of that field (e.g. int above 0)
CHECK (field>0)
Constraint for multiple columns in a table (when creating table)
CREATE TABLE name (…, CONSTRAINT checkname CHECK (field>0 AND field=’Test’);
Add constraint for single column in a table (existing table)
ALTER TABLE tablename ADD CHECK (fieldname>0)
Add constraint for multiple columns in a table (existing table)
ALTER TABLE tablename ADD CONSTRAINT checkname CHECK (field>0 AND field=’test’);
Drop a check constraint
ALTER TABLE table DROP CONSTRAINT checkname
Default value constraint
DEFAULT defaultvalue
Add default value constraint
ALTER TABLE tablename ALTER COLUMN fieldname SET DEFAULT defaultvalue
Drop default value constraint
ALTER TABLE tablename ALTER COLUMN fieldname DROP DEFAULT
Add an index to a table that allows duplicate values
CREATE INDEX indexname ON tablename (fieldname)
Add an index to a table that doesn’t allow duplicates
CREATE UNIQUE INDEX indexname ON tablename (fieldname)
Remove an index
DROP INDEX tablename.indexname
Delete a table
DROP TABLE tablename
Delete a database
DROP DATABASE dbname
Clear all data in a table
TRUNCATE TABLE tablename
Add a field to a table
ALTER TABLE tablename ADD fieldname datatype
Delete a field from a table
ALTER TABLE tablename DROP COLUMN fieldname
Change a field’s data type
ALTER TABLE tablename ALTER COLUMN fieldname newtype
Auto-increment constraint
IDENTITY(start,delta)
Create a view of a query
CREATE VIEW [viewname] AS SELECT…
Get all records from a view
SELECT * FROM [viewname]
Get rid of a view
DROP VIEW [viewname]
Function for current date and time
GETDATE()
Function for single part of a date/time
DATEPART(whichpart,date)
Function to add/subtract time from date
DATEADD(whichpart,number,date)
Function to get time interval between two dates
DATEDIFF(whichpart,startdate,enddate)
Function to change formats of types (e.g. date to string)
CONVERT(VARCHAR(19),GETDATE(),10) datetype,expression,style
Get records where a field is null
SELECT field FROM tablename WHERE field IS NULL
Get records where a field is not null
SELECT field FROM tablename WHERE field IS NOT NULL
Function to safely handle when field is null
ISNULL(field,valuetoreturnifnull)
Data type - fixed length character string
char(n)
Data type - variable length char string 8k characters
varchar(n)
Data type - variable length char string 1bn characters
varchar(max)
Data type - variable length char string 2GB data
text
Data type - fixed length unicode character string 4k characters
nchar(n)
Data type - variable length unicode character string 4k characters
nvarchar(n)
Data type - variable length unicode character string 500k characters
nvarchar(max)
Data type - variable length unicode char string 2GB data
ntext
Data type - boolean 0/1/null
bit
Data type - fixed-length binary data, 8kb
binary(n)
Data type - variable length binary data, 8kb
varbinary(n)
Data type - variable length binary data, 2gb
varbinary(max) or image
Data type - 1 byte integer, unsigned
tinyint
Data type - signed 2 byte integer
smallint
Data type - signed 4 byte integer
int
Data type - signed 8 byte integer
bigint
Data type - fixed-point number (5-17 bytes)
decimal(totaldigits, fractionaldigits) or numeric(t, f)
Data type - 4 byte monetary data
smallmoney
Data type - 8 byte monetary data
money
Data type - floating point number (4 or 8 bytes)
float(24) or float(53)
Data type - floating point number (4 bytes)
real
Data type - 8 byte date and time
datetime or datetime2 or datetimeoffset
Data type - 4 byte date and time
smalldatetime
Data type - 3 byte date only
date
Data type - 3-5 byte time only
time
SQL Aggregate functions (calculated from multiple values) 5x
AVG, COUNT, MAX, MIN, SUM
SQL Scalar functions (calculated from single input) 7x
UPPER, LOWER, MID, LEN, ROUND, NOW, FORMAT
Like a pivot table - grouping aggregate function for sum
SELECT field, func(field) FROM table WHERE field>0 GROUP BY field
Get records with values conditionally tested against aggregate function
SELECT field FROM table GROUP BY field HAVING SUM(field)<2000
