SQL

Question 1

What is normalization

Answer 1

Normalization is the process of organizing data in a database.
This includes creating tables and establishing relationships between those tables

according to rules designed both to protect the data and to make the database more flexible by preventing redundancy and conflicting dependency.

Question 2

What is primary Key

Answer 2

Is a constraint that uniquely identifies each row in a table.
It must contain UNIQUE values and implicitly NOT NULL

Question 3

What is Foreign Key

Answer 3

is a single or collection of fields in a table refers to the PRIMARY KEY in another table

Question 4

Normalization Rule

Answer 4

First Normal Form (1NF)

• Have a primary key
• All the columns in a table should have unique names.
• All the columns in a table should have one information

Second Normal Form (2NF)

• It should be in the First Normal form.
• And, it doesn’t have Partial Dependency

Third Normal Form (3NF)

• It is in the Second Normal form.
• And, it doesn’t have Transitive Dependency.

Question 5

What are Constraints in SQL

Answer 5

Constraints are used to specify the rules concerning data in the table
It can be applied for single or multiple fields

NOT NULL, CHECK, DEFAULT, UNIQUE, INDEX , PRIMARY KEY, FOREIGN KEY

Question 6

What is distinct

Answer 6

The SELECT DISTINCT statement is used to return only distinct (different) values

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT
column_name
FROM
table_name;

Question 7

What is difference between unique and distinct in SQL?

Answer 7

The main difference between Unique and Distinct
Unique helps to ensure that all the values in a column are different while
Distinct helps to remove all the duplicate records when retrieving the records from a table.

Question 8

What is a UNIQUE constraint?

Answer 8

A UNIQUE constraint ensures that all values in a column are different.

Question 9

What is join

Answer 9

join is used to combine records (rows) from two or more tables based on a related column between the them

Question 10

Different types of Join

Answer 10

There are four different types of JOINs in SQL

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

LEFT (OUTER) JOIN:
Retrieves all the records/rows from the left and the matched records/rows from the right table

RIGHT (OUTER) JOIN:
Retrieves all the records/rows from the right and the matched records/rows from the left table.

FULL (OUTER) JOIN:
Retrieves all the records where there is a match in either the left or right table.

Question 11

What is a View?

Answer 11

A view in SQL is a virtual table based on the result-set of an SQL statement.
The fields in a view are fields from one or more real tables in the database.

You can use views to hide table columns from users by allow them access to the view and not to the table itself.
This helps enhance database security and integrity.

CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

Question 12

What are Aggregate function

Answer 12

An aggregate function performs operations on a collection of values to return a single scalar value.
Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement

AVG() - Calculates the mean of a collection of values.
COUNT() - Counts the total number of records in a specific table or view.
MIN() - Calculates the minimum of a collection of values.
MAX() - Calculates the maximum of a collection of values.
SUM() - Calculates the sum of a collection of values.
FIRST() - Fetches the first element in a collection of values.
LAST() - Fetches the last element in a collection of values.

Question 13

What is a Cross-Join?

Answer 13

The CROSS JOIN is a join for each row of one table to every row of another table.
at end the number of rows in the result-set is the product of the number of rows of the two tables

This join type is also known as cartesian join. … The main idea of the CROSS JOIN is that it returns the Cartesian product of the joined tables.

SELECT stu.name, sub.subject
FROM students AS stu
CROSS JOIN subjects AS sub;

Unlike other JOIN operators, it does not let you specify a join clause.

Question 14

What is Denormalization?

Answer 14

Denormalization is the inverse process of normalization,

Question 15

What are the TRUNCATE, DELETE and DROP statements?

Answer 15

DELETE :statement is used to delete rows one by one from a table.
(DML command)
Allow where clause
Can be rolled back
——————————————————————
TRUNCATE : command is used to delete all the rows from the table and free the space containing the table but not delete the schema
TRUNCATE is faster than delete
(DDL Command)
Not allow where clause
can be rolled back
———————————————————————–
DROP :command is used to remove a table, all the rows in the table are deleted and the table structure is removed from the database.
(DDL Command)
Not allow where clause
can not be rolled back

Question 16

DDL, DML, DQL, TCL, DCL

Answer 16

DDL (Data Definition Language)
Deal with the structure of the table
Define the database Schema/skeleton
(Create, Drop, Alter, Truncate)
——————————————————————–
DML (Data Manipulation Language)
Deal with the records
(CRUD operation)
——————————————————————–
DQL (Data Querying Language)
Deal with fetching the record from table
(select)
——————————————————————–
TCL (Transaction Control Language)
Ensure that all queries execute or none of them will execute
(begin transaction …..query…… commit or rollback)
——————————————————————–
DCL (Data Control Language)
used to mange the security and control access to data stored in a database

Question 17

What are some common clauses used with SELECT query in SQL?

Answer 17

WHERE
used to filter records that are necessary, based on specific conditions.

HAVING
used to filter records in combination with the GROUP BY clause. It is different from WHERE, since the WHERE clause cannot filter aggregated records.

GROUP BY
used to group records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.

ORDER BY
used to sort the records based on some field(s) in ascending (ASC) or descending order (DESC).

Question 18

What are UNION, MINUS and INTERSECT commands

Answer 18

UNION, MINUS and INTERSECT are combine 2 or more select statements in a single result set

1- The UNION operator combines and returns the result-set retrieved by two or more SELECT statements.

SELECT name FROM Students /* Fetch the union of queries without duplicates */
UNION
SELECT name FROM Contacts;

SELECT name FROM Students /* Fetch the union of queries with duplicates*/
UNION ALL
SELECT name FROM Contacts;
————————————————————————————–
2- The MINUS is used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first.

SELECT name FROM Students /* Fetch names from students /
MINUS / that aren’t present in contacts */
SELECT name FROM Contacts;
————————————————————————————–

3- The INTERSECT combines the result-set fetched by the two SELECT statements where records from one match the other and then returns this intersection of result-sets.

SELECT name FROM Students /* Fetch names from students /
INTERSECT / that are present in contacts as well */
SELECT name FROM Contacts;

Question 19

List the different types of relationships in SQL.

Answer 19

One-to-One -
This can be defined as the relationship between two tables where each record in one table is associated with the maximum of one record in the other table.

One-to-Many & Many-to-One -
This is the most commonly used relationship where a record in a table is associated with multiple records in the other table.

Many-to-Many -
This is used in cases when multiple instances on both sides are needed for defining a relationship.

Self-Referencing Relationships -
This is used when a table needs to define a relationship with itself.

Question 20

What is an Alias in SQL?

Answer 20

It is a temporary name assigned to the table or table column for the purpose of a particular SQL query.

SELECT A.emp_name AS “Employee” /* Alias using AS keyword /
B.emp_name AS “Supervisor”
FROM employee A, employee B / Alias without AS keyword */
WHERE A.emp_sup = B.emp_id;

Question 21

What is Pattern Matching in SQL?

Answer 21

The LIKE operator is used in conjunction with SQL % Wildcards to fetch the required information.

SELECT *
FROM students
WHERE first_name LIKE ‘K%’

SELECT *
FROM students
WHERE first_name NOT LIKE ‘K%’

Question 22

N + 1 problem

Answer 22

The N + 1 Problem is a basically abusing the database with large number of small queries instead of one or two queries