SQL

Question 1

✅Delete (2) vs (2) Truncate

Answer 1

Delete
1 - slower
2 - you can rollback data Truncate

Truncate
1 - faster
2 - rollback not possible

Question 2

✅Explain each type of Join

Answer 2

(INNER) JOIN: Returns records that have matching values in both tables

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Question 3

✅ char vs varchar2

Answer 3

char
1 - fixed length,

eg char(10) stores 10 characters, no more no less

varchar2
1 flexible length
eg varchar2(10) stores between 1 and 10 characters

Question 4

✅ Constrains

Answer 4

Not null

unique

primary key

foreing key

check

default

index

Question 5

✅ Data Integrity

Answer 5

Refers to the accuracy and consistency of data

Question 6

✅ Subsets of SQL

Answer 6

DDL - Data Definition Language

DML - Data Manipulation Language

DCL - Data Control Language

TCL - Transaction Control Language

Question 7

Triggers (6)?

Answer 7

Before insert
After insert

Before update
After update

Before delete
After delete

Question 8

✅ Cross Join

Answer 8

Produces cartesian product of two tables

Question 9

✅ List some Aggregate Functions (6)

Answer 9

1 - AVG
2 - COUNT
3- SUM
4 - MAX
5 - MIN

Question 10

✅ Different between Scalar Functions and Aggregate Functions

Answer 10

Aggregate Functions are based on the columns

Scalar Functions are based on the input value

Question 11

✅ Merge statement

Answer 11

Syncrhonize two tables by inserting, updating or deleting rwos based on differences found

Question 12

✅ What is a SQL Clause?

Answer 12

Conditions to limit the result, like WHERE or HAVING

Question 13

✅ List the ways in which Dyamic SQL can be executed?

Answer 13

1- sp_executesql

2- EXEC

Question 14

✅ How to select unieque records from a table?

Answer 14

DISTINCT

Question 15

✅ What function would you use to fetch first the 5 characters of a string?

Answer 15

SUBSTRING

Question 16

✅ Different set operators (3)

Answer 16

1- UNION

2- INTERSECT

3- MINUS

Question 17

✅ What is a View?

Answer 17

It’s a virtual table based on the result set of an SQL statement

Question 18

✅ Clustered index (3) vs (3) non-clustered index

Answer 18

Clustered
1 - One per table
2 - Faster
3- Physical Order

Non-clustered
1 - Many per table
2 - Slower
3- Logical order

Question 19

✅ Como auto-incrementar IDs?

Answer 19

Identity

Question 20

✅ Authentication Modes

Answer 20

Central repository of information

Question 21

✅ How to add a number on each row?

Answer 21

ROW_NUMBER() OVER(ORDER BY
name
ASC)

AS row

Question 22

⚠️ How to improve query performance?

Question 23

⚠️ What is SQL Profiler?