SQL

Question 1

Get all records from a table

Answer 1

SELECT * FROM table;

Question 2

Get specific fields from a table

Answer 2

SELECT field1,field2 FROM table;

Question 3

Get only different values from a table

Answer 3

SELECT DISTINCT field FROM table;

Question 4

Get records with a field equal to a string value and another equal to a numeric value

Answer 4

SELECT * FROM table WHERE field1=’value’ AND field2=1;

Question 5

Get all records from a table sorted by two fields, ascending

Answer 5

SELECT * FROM table ORDER BY field1,field2;

Question 6

Get all records from a table sorted by a field, descending

Answer 6

SELECT * FROM table ORDER BY field DESC;

Question 7

Add a record to a table by filling all columns

Answer 7

INSERT INTO table VALUES (value1, value2, …);

Question 8

Add a record to a table by filling some columns

Answer 8

INSERT INTO table (field1, field2) VALUES (value1, value2);

Question 9

Change values for records conditionally

Answer 9

UPDATE table SET field1=value1,field2=value2 WHERE condition;

Question 10

Remove records conditionally

Answer 10

DELETE FROM table WHERE condition;

Question 11

Get a certain number of records

Answer 11

SELECT TOP count field1,field2 FROM table;

Question 12

Get a certain percentage of records

Answer 12

SELECT TOP pcnt PERCENT field1,field2 FROM table;

Question 13

Get records with field1 starting with ‘happy’

Answer 13

SELECT * FROM table WHERE field1 LIKE ‘happy%’;

Question 14

Get records with field1 not ending with ‘happy’

Answer 14

SELECT * FROM table WHERE field1 NOT LIKE ‘%happy’

Question 15

Pattern for many unknown characters

Answer 15

%

Question 16

Pattern for single unknown character

Answer 16

_

Question 17

Pattern for character set

Answer 17

[ab-d]

Question 18

Pattern for NOT character set

Answer 18

[!abc]

Question 19

Get all records with field1 equal to any values in a set

Answer 19

SELECT * FROM table WHERE field1 IN (‘a’,’b’);

Question 20

Get all records with field1 alphabetically between two strings

Answer 20

SELECT * FROM table WHERE field1 BETWEEN ‘astring’ AND ‘bstring’;

Question 21

Create a short name for a table

Answer 21

SELECT o.Field1, c.Field2 FROM table1 as o, table2 as c WHERE condition;

Question 22

Get associated records from two tables where there is at least one match in both

Answer 22

SELECT t1.f1,t2.f2 FROM t1 INNER JOIN t2 ON t1.f=t2.f

Question 23

Get associated records from two tables showing all records from first table

Answer 23

SELECT t1.f1,t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f2

Question 24

Get associated records from two tables showing all records from second table

Answer 24

SELECT t1.f1,t2.f2 FROM t1 RIGHT JOIN t2 ON t1.f1=t2.f2

Question 25

Get all records from two tables and line up matches

Answer 25

SELECT t1.f1, t2.f2 FROM t1 FULL JOIN t2 ON t1.f1=t2.f2

Question 26

Use two SELECTs in one statement to get distinct values

Answer 26

SELECT f1 FROM t1 UNION SELECT f2 FROM t2;

Question 27

Use two SELECTs in one statement to get all values

Answer 27

SELECT f1 FROM t1 UNION ALL SELECT f2 FROM t2;

Question 28

Copy data to another table

Answer 28

SELECT * INTO newtable [IN db] FROM oldtable

Question 29

Make a new database

Answer 29

CREATE DATABASE name

Question 30

Create a new table

Answer 30

CREATE TABLE name (fieldname type constraint, fieldname type constraint)

Question 31

Constraint so that field will not accept null values

Answer 31

NOT NULL

Question 32

Constraint so that field will will have unique values

Answer 32

UNIQUE

Question 33

Add a constraint to existing field

Answer 33

ALTER TABLE table ADD constraint (fieldname)

Question 34

Drop a constraint from existing field

Answer 34

ALTER TABLE table DROP CONSTRAINT field

Question 35

Constraint for primary key

Answer 35

PRIMARY KEY

Question 36

Constraint for key that maps to another table

Answer 36

FOREIGN KEY

Question 37

Add foreign key constraint to existing field

Answer 37

ALTER TABLE tablename ADD FOREIGN KEY fieldname REFERENCES othertable(otherfield)

Question 38

Constraint on a field to restrict values of that field (e.g. int above 0)

Answer 38

CHECK (field>0)

Question 39

Constraint for multiple columns in a table (when creating table)

Answer 39

CREATE TABLE name (..., CONSTRAINT checkname CHECK (field>0 AND field='Test');

Question 40

Add constraint for single column in a table (existing table)

Answer 40

ALTER TABLE tablename ADD CHECK (fieldname>0)

Question 41

Add constraint for multiple columns in a table (existing table)

Answer 41

ALTER TABLE tablename ADD CONSTRAINT checkname CHECK (field>0 AND field='test');

Question 42

Drop a check constraint

Answer 42

ALTER TABLE table DROP CONSTRAINT checkname

Question 43

Default value constraint

Answer 43

DEFAULT defaultvalue

Question 44

Add default value constraint

Answer 44

ALTER TABLE tablename ALTER COLUMN fieldname SET DEFAULT defaultvalue

Question 45

Drop default value constraint

Answer 45

ALTER TABLE tablename ALTER COLUMN fieldname DROP DEFAULT

Question 46

Add an index to a table that allows duplicate values

Answer 46

CREATE INDEX indexname ON tablename (fieldname)

Question 47

Add an index to a table that doesn't allow duplicates

Answer 47

CREATE UNIQUE INDEX indexname ON tablename (fieldname)

Question 48

Remove an index

Answer 48

DROP INDEX tablename.indexname

Question 49

Delete a table

Answer 49

DROP TABLE tablename

Question 50

Delete a database

Answer 50

DROP DATABASE dbname

Question 51

Clear all data in a table

Answer 51

TRUNCATE TABLE tablename

Question 52

Add a field to a table

Answer 52

ALTER TABLE tablename ADD fieldname datatype

Question 53

Delete a field from a table

Answer 53

ALTER TABLE tablename DROP COLUMN fieldname

Question 54

Change a field's data type

Answer 54

ALTER TABLE tablename ALTER COLUMN fieldname newtype

Question 55

Auto-increment constraint

Answer 55

IDENTITY(start,delta)

Question 56

Create a view of a query

Answer 56

CREATE VIEW [viewname] AS SELECT...

Question 57

Get all records from a view

Answer 57

SELECT * FROM [viewname]

Question 58

Get rid of a view

Answer 58

DROP VIEW [viewname]

Question 59

Function for current date and time

Answer 59

GETDATE()

Question 60

Function for single part of a date/time

Answer 60

DATEPART(whichpart,date)

Question 61

Function to add/subtract time from date

Answer 61

DATEADD(whichpart,number,date)

Question 62

Function to get time interval between two dates

Answer 62

DATEDIFF(whichpart,startdate,enddate)

Question 63

Function to change formats of types (e.g. date to string)

Answer 63

CONVERT(VARCHAR(19),GETDATE(),10) datetype,expression,style

Question 64

Get records where a field is null

Answer 64

SELECT field FROM tablename WHERE field IS NULL

Question 65

Get records where a field is not null

Answer 65

SELECT field FROM tablename WHERE field IS NOT NULL

Question 66

Function to safely handle when field is null

Answer 66

ISNULL(field,valuetoreturnifnull)

Question 67

Data type - fixed length character string

Answer 67

char(n)

Question 68

Data type - variable length char string 8k characters

Answer 68

varchar(n)

Question 69

Data type - variable length char string 1bn characters

Answer 69

varchar(max)

Question 70

Data type - variable length char string 2GB data

Answer 70

text

Question 71

Data type - fixed length unicode character string 4k characters

Answer 71

nchar(n)

Question 72

Data type - variable length unicode character string 4k characters

Answer 72

nvarchar(n)

Question 73

Data type - variable length unicode character string 500k characters

Answer 73

nvarchar(max)

Question 74

Data type - variable length unicode char string 2GB data

Answer 74

ntext

Question 75

Data type - boolean 0/1/null

Answer 75

bit

Question 76

Data type - fixed-length binary data, 8kb

Answer 76

binary(n)

Question 77

Data type - variable length binary data, 8kb

Answer 77

varbinary(n)

Question 78

Data type - variable length binary data, 2gb

Answer 78

varbinary(max) or image

Question 79

Data type - 1 byte integer, unsigned

Answer 79

tinyint

Question 80

Data type - signed 2 byte integer

Answer 80

smallint

Question 81

Data type - signed 4 byte integer

Answer 81

int

Question 82

Data type - signed 8 byte integer

Answer 82

bigint

Question 83

Data type - fixed-point number (5-17 bytes)

Answer 83

decimal(totaldigits, fractionaldigits) or numeric(t, f)

Question 84

Data type - 4 byte monetary data

Answer 84

smallmoney

Question 85

Data type - 8 byte monetary data

Answer 85

money

Question 86

Data type - floating point number (4 or 8 bytes)

Answer 86

float(24) or float(53)

Question 87

Data type - floating point number (4 bytes)

Answer 87

real

Question 88

Data type - 8 byte date and time

Answer 88

datetime or datetime2 or datetimeoffset

Question 89

Data type - 4 byte date and time

Answer 89

smalldatetime

Question 90

Data type - 3 byte date only

Answer 90

date

Question 91

Data type - 3-5 byte time only

Answer 91

time

Question 92

SQL Aggregate functions (calculated from multiple values) 5x

Answer 92

AVG, COUNT, MAX, MIN, SUM

Question 93

SQL Scalar functions (calculated from single input) 7x

Answer 93

UPPER, LOWER, MID, LEN, ROUND, NOW, FORMAT

Question 94

Like a pivot table - grouping aggregate function for sum

Answer 94

SELECT field, func(field) FROM table WHERE field>0 GROUP BY field

Question 95

Get records with values conditionally tested against aggregate function

Answer 95

SELECT field FROM table GROUP BY field HAVING SUM(field)<2000