Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Job Interviews > SQL > Flashcards

SQL Flashcards

1
Q

Get all records from a table

A

SELECT * FROM table;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Get specific fields from a table

A

SELECT field1,field2 FROM table;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Get only different values from a table

A

SELECT DISTINCT field FROM table;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

SELECT * FROM table ORDER BY field1,field2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

SELECT * FROM table ORDER BY field DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Add a record to a table by filling all columns

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Add a record to a table by filling some columns

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Change values for records conditionally

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Remove records conditionally

A

DELETE FROM table WHERE condition;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Get a certain number of records

A

SELECT TOP count field1,field2 FROM table;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Get a certain percentage of records

A

SELECT TOP pcnt PERCENT field1,field2 FROM table;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Get records with field1 starting with ‘happy’

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Get records with field1 not ending with ‘happy’

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Pattern for many unknown characters

A

%

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Pattern for single unknown character

A

_

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Pattern for character set

A

[ab-d]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Pattern for NOT character set

A

[!abc]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Get all records with field1 alphabetically between two strings

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Create a short name for a table

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Get all records from two tables and line up matches

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Use two SELECTs in one statement to get distinct values

A

SELECT f1 FROM t1 UNION SELECT f2 FROM t2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Use two SELECTs in one statement to get all values

A

SELECT f1 FROM t1 UNION ALL SELECT f2 FROM t2;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Copy data to another table

A

SELECT * INTO newtable [IN db] FROM oldtable

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Make a new database

A

CREATE DATABASE name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Create a new table

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Constraint so that field will not accept null values

A

NOT NULL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Constraint so that field will will have unique values

A

UNIQUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Add a constraint to existing field

A

ALTER TABLE table ADD constraint (fieldname)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Drop a constraint from existing field

A

ALTER TABLE table DROP CONSTRAINT field

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Constraint for primary key

A

PRIMARY KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Constraint for key that maps to another table

A

FOREIGN KEY

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Add foreign key constraint to existing field

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

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

A

CHECK (field>0)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

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

A

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

40
Q

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

A

ALTER TABLE tablename ADD CHECK (fieldname>0)

41
Q

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

A

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

42
Q

Drop a check constraint

A

ALTER TABLE table DROP CONSTRAINT checkname

43
Q

Default value constraint

A

DEFAULT defaultvalue

44
Q

Add default value constraint

A

ALTER TABLE tablename ALTER COLUMN fieldname SET DEFAULT defaultvalue

45
Q

Drop default value constraint

A

ALTER TABLE tablename ALTER COLUMN fieldname DROP DEFAULT

46
Q

Add an index to a table that allows duplicate values

A

CREATE INDEX indexname ON tablename (fieldname)

47
Q

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

A

CREATE UNIQUE INDEX indexname ON tablename (fieldname)

48
Q

Remove an index

A

DROP INDEX tablename.indexname

49
Q

Delete a table

A

DROP TABLE tablename

50
Q

Delete a database

A

DROP DATABASE dbname

51
Q

Clear all data in a table

A

TRUNCATE TABLE tablename

52
Q

Add a field to a table

A

ALTER TABLE tablename ADD fieldname datatype

53
Q

Delete a field from a table

A

ALTER TABLE tablename DROP COLUMN fieldname

54
Q

Change a field’s data type

A

ALTER TABLE tablename ALTER COLUMN fieldname newtype

55
Q

Auto-increment constraint

A

IDENTITY(start,delta)

56
Q

Create a view of a query

A

CREATE VIEW [viewname] AS SELECT…

57
Q

Get all records from a view

A

SELECT * FROM [viewname]

58
Q

Get rid of a view

A

DROP VIEW [viewname]

59
Q

Function for current date and time

A

GETDATE()

60
Q

Function for single part of a date/time

A

DATEPART(whichpart,date)

61
Q

Function to add/subtract time from date

A

DATEADD(whichpart,number,date)

62
Q

Function to get time interval between two dates

A

DATEDIFF(whichpart,startdate,enddate)

63
Q

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

A

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

64
Q

Get records where a field is null

A

SELECT field FROM tablename WHERE field IS NULL

65
Q

Get records where a field is not null

A

SELECT field FROM tablename WHERE field IS NOT NULL

66
Q

Function to safely handle when field is null

A

ISNULL(field,valuetoreturnifnull)

67
Q

Data type - fixed length character string

A

char(n)

68
Q

Data type - variable length char string 8k characters

A

varchar(n)

69
Q

Data type - variable length char string 1bn characters

A

varchar(max)

70
Q

Data type - variable length char string 2GB data

A

text

71
Q

Data type - fixed length unicode character string 4k characters

A

nchar(n)

72
Q

Data type - variable length unicode character string 4k characters

A

nvarchar(n)

73
Q

Data type - variable length unicode character string 500k characters

A

nvarchar(max)

74
Q

Data type - variable length unicode char string 2GB data

A

ntext

75
Q

Data type - boolean 0/1/null

A

bit

76
Q

Data type - fixed-length binary data, 8kb

A

binary(n)

77
Q

Data type - variable length binary data, 8kb

A

varbinary(n)

78
Q

Data type - variable length binary data, 2gb

A

varbinary(max) or image

79
Q

Data type - 1 byte integer, unsigned

A

tinyint

80
Q

Data type - signed 2 byte integer

A

smallint

81
Q

Data type - signed 4 byte integer

A

int

82
Q

Data type - signed 8 byte integer

A

bigint

83
Q

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

A

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

84
Q

Data type - 4 byte monetary data

A

smallmoney

85
Q

Data type - 8 byte monetary data

A

money

86
Q

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

A

float(24) or float(53)

87
Q

Data type - floating point number (4 bytes)

A

real

88
Q

Data type - 8 byte date and time

A

datetime or datetime2 or datetimeoffset

89
Q

Data type - 4 byte date and time

A

smalldatetime

90
Q

Data type - 3 byte date only

A

date

91
Q

Data type - 3-5 byte time only

A

time

92
Q

SQL Aggregate functions (calculated from multiple values) 5x

A

AVG, COUNT, MAX, MIN, SUM

93
Q

SQL Scalar functions (calculated from single input) 7x

A

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

94
Q

Like a pivot table - grouping aggregate function for sum

A

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

95
Q

Get records with values conditionally tested against aggregate function

A

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

Job Interviews (5 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions