Basics

Question 1

Update statement

Answer 1

UPDATE
SET =, =
WHERE

Question 2

insert statement

Answer 2

INSERT INTO table_name (column1, column2, column3, …)

VALUES (value1, value2, value3, …);

Question 3

Declare variable

Answer 3

DECLARE @ =

Question 4

varchar(n)

Answer 4

Variable width character string

Question 5

nvarchar(n)

Answer 5

Variable width Unicode string

Question 6

Bit

Answer 6

Integer that can be 0, 1, or NULL

Question 7

tinyint

Answer 7

Allows whole numbers from 0 to 255

Question 8

Smallint

Answer 8

Allows whole numbers between -32,768 and 32,767

Question 9

Int

Answer 9

Allows whole numbers between -2,147,483,648 and 2,147,483,647

Question 10

bigint

Answer 10

Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes
decimal(p,s)

Question 11

COALESCE()

Answer 11

returns the first non-null value in a list

COALESCE(val1, val2, …., val_n)

Question 12

CAST()

Answer 12

Converts a value of any type into a specific datatype

CAST(value AS DataType)

Question 13

ISNULL()

Answer 13

Checks to see if the first value is null, if so it is the second argument gets returned
ISNULL(expression, value)

Question 14

ISNUMERIC()

Answer 14

Returns 1 if the value is a number, otherwise 0

Question 15

CONCAT()

Answer 15

Adds two or strings together

Question 16

Create table statement

Answer 16

Declare the table name, then list the columns and datatypes
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Question 17

Create temp table

Answer 17

It is the same as a create table statement, the table name should bee declared with #.

Question 18

Primary key

Answer 18

The PRIMARY KEY constraint uniquely identifies each record in a table.
you can declare a primary key when creating a table
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
    PRIMARY KEY (column1)
);

Question 19

CONSTRAINT

Answer 19

SQL constraints are used to specify rules for data in a table.
Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Uniquely identifies a row/record in another table
CHECK - Ensures that all values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column when no value is specified
INDEX - Used to create and retrieve data from the database very quickly

Question 20

CLUSTERED index

Answer 20

The rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index.

It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.

Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

Question 21

NON CLUSTERED index

Answer 21

There is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index will increase the time it takes to write new records.

Question 22

ALTER TABLE ADD COLUMN

Answer 22

ALTER TABLE table_name

ADD column_name datatype;

Question 23

ALTER TABLE DROP COLUMN

Answer 23

ALTER TABLE table_name

DROP COLUMN column_name;

Question 24

Get current date

Answer 24

DECLARE @Today date = GETDATE();

Question 25

with (NOLOCK)

Answer 25

Allows other operations to be run on the table

Question 26

CTE

Answer 26

;WITH  AS
(
  SQL Statement (SELECT...)
)
Only use a CTE if you are going to use it once otherwise us a temp table

Question 27

%

Answer 27

Wildcard symbol

WHERE name LIKE ‘%an%’ – returns ‘Dan’, ‘Sandra’

Question 28

IN

Answer 28

is equivalent to multiply OR clauses

WHERE column_name IN (value1, value2, …);