sql

Question 1

Standard numerical operators

Answer 1

=, !=, < <=, >, >=

Question 2

Number is [not] within range of two values (inclusive)

Answer 2

… WHERE col_name [NOT] BETWEEN … AND …;

Question 3

Number exists in a list

Answer 3

… WHERE col_name IN (2, 4, 6);

Question 4

Number does not exist in a list

Answer 4

… WHERE col_name NOT IN (1, 3, 5);

Question 5

Case sensitive exact string [inequality] comparison

Answer 5

… WHERE col_name [!]= “abc”;

Question 6

Case insensitive exact string [inequality] comparison

Answer 6

… WHERE col_name [NOT] LIKE “abc”;

Question 7

Match a single character

Answer 7

… WHERE col_name [NOT] LIKE “_bc”;

Question 8

String [does not] exists in a list

Answer 8

… WHERE col_name [NOT] IN (“A”, “B”, “C”);

Question 9

Match a sequence of zero or more characters

Answer 9

… WHERE col_name [NOT] LIKE “%abc”;

Question 10

Database normalization is useful because

Answer 10

minimizes duplicate data in any single table, and allows for data in the database to grow independently of each other

Question 11

Database normalization is useful because

Answer 11

minimizes duplicate data in any single table, and allows for data in the database to grow independently of each other

Question 12

Database normalization trade-off

Answer 12

Queries get slightly more complex since they have to be able to find data from different parts of the database, and performance issues can arise when working with many large tables.

Question 13

inner join - only contains data that belongs in both of the tables

Answer 13

SELECT title, domestic_sales, international_sales
FROM movies
INNER JOIN boxoffice ON movies.id=boxoffice.movie_id;

Question 14

Stored procedure

Answer 14

Create once, store and call for several times whenever it is required, it can be executed only in the database and utilizes more memory in the database server.

Question 15

A view

Answer 15

A virtual table, Restricting access to data, Making complex queries simple.

Question 16

Alias

Answer 16

A name given to table or column.

Question 17

‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE

Answer 17

Having Clause is only used with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query.

Question 18

MERGE statement

Answer 18

UPDATE if a row exists, or an INSERT if the row does not exist.

Question 19

What is the need for group functions in SQL?

Answer 19

Group functions work on the set of rows and returns one result per group. AVG, COUNT, MAX, MIN, SUM

Question 20

“Trigger” in SQL

Answer 20

Stored procedures that are defined to execute automatically in place or after data modifications.

Question 21

ACID

Answer 21

Atomic: all or nothing,
Consistency: (?)
Isolation: concurrency controll
Durability: after commit you have it

Question 22

Normalization

Answer 22

Organizing data to avoid duplication and redundancy:

1NF, 2, 3

Question 23

DROP and TRUNCATE

Answer 23

no rollback, deletes a tables rollback, deletes all rows

Question 24

Index

Answer 24

Performance tuning method of allowing faster retrieval of records from the table.