SQL

Question 1

Explain what SQL is. What are some SQL databases?

Answer 1

SQL stands for Structured Query Language and it allows the user to access and manipulate databases.

Some SQL Databases:
MySQL
MariaDB
Oracle
PostgreSQL
MSSQL

Question 2

What are the 5 sublanguages of SQL? Which commands correspond to each of them?

Answer 2

DDL - Data Definition Language: CREATE, ALTER, DROP
DML - Data Manipulation Language: INSERT, UPDATE, DELETE
DQL - Data Query Language: SELECT
DCL - Data Control Language: GRANT, REVOKE
TCL - Transaction Control Language: COMMIT, ROLLBACK, SAVEPOINT

Question 3

What is the difference between DELETE, DROP, and TRUNCATE commands?

Answer 3

DELETE (DML) - Used to delete one or more rows of a table

DROP (DDL) - Used to drop a whole table

TRUNCATE (DDL) - Used to delete all rows in a table in one go. Cannot be used with a where clause

Question 4

What are some SQL clauses you can use with SELECT statements?

Answer 4

WHERE - Specifies criteria to retrieve from the results of the SELECT statement

HAVING - Filters the results of GROUP BY, HAVING uses the same restrictions as the WHERE clause

GROUP BY - Groups the selected rows based on identical values in a column or expression

ORDER BY - Allows you to specify the columns by which the results table is to be sorted

Question 5

What is the difference between WHERE and HAVING?

Answer 5

WHERE - Filters the results of a SELECT statement before GROUP BY can be performed

HAVING - Filters the results of a SELECT statement after GROUP BY is performed

Question 6

Explain what the ORDER BY and GROUP BY clauses do

Answer 6

GROUP BY - Groups selected rows that share identical values and can act as a summary of values if using aggregate functions

ORDER BY - Used to sort values by columns or expressions, does not group values

Question 7

Explain the concept of relational integrity

Answer 7

Used to ensure accuracy and consistency of data in the relational database. It has three key concepts:

Relational Database - A type of database that stores information in the form of a 2-D table.
Information integrity - The trustworthiness and dependability of information.
Integrity constraints - Sets of rules that can help maintain the quality of information that is acquired

Question 8

List the integrity constraints

Answer 8

Entity Integrity Constraints - Constraint which ensure that every entity is unique - Primary Key Constraint, Unique Constraint, Auto_Increment Constraint

Domain Constraints - Constraints which ensure a type of data in a column - Type Constraint, Not null constraint, DEFAULT value Constraint, ENUM constraint

Referential Integrity Constraint - Foreign Key Constraint

User Defined Constraint - CHECK Constraint

Question 9

Define the word “schema”

Answer 9

Schema is the structure of a database

Question 10

What is a candidate key? What about a surrogate key?

Answer 10

Candidate key is a single or group key that uniquely identifies rows in a table.

Surrogate key- A surrogate key is a system generated (could be GUID, sequence, unique identifier, etc.) value with no business meaning that is used to uniquely identify a record in a table. The key itself could be made up of one or multiple columns (i.e. Composite Key).

Question 11

What conditions lead to orphan records?

Answer 11

Its a record whose foreign key value references a non-existent primary key value

Question 12

What are some SQL data types?

Answer 12

CHAR
VARCHAR
DATE
TIME
DateTime
INT

Question 13

What is normalization? What are the levels?

Answer 13

Normalization is the process to eliminate data redundancy and enhance data integrity in the table. Normalization also helps to organize the data in the database.

1NF: There are only single valued attributes, there is a unique name for each column, order does not matter

2NF: All columns depend on primary key column, partial dependencies placed in separate table

3NF: Non Primary Key columns should not depend on non pk columns

Question 14

What are the properties a transaction must follow?

Answer 14

Transaction must follow the ACID properties.

A- Atomicity - By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all.

C-Consistency - This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.

I-Isolation - makes sure that multiple transactions do not interfere with one another.

D- Durability - Make sure that changes in data made by transactions are saved even if a system failure occurs

Question 15

What is the difference between joins and set operators?

Answer 15

Set Operators - the columns in the tables must be the same.

Joins - if one column is same its enough to join two or more tables.

Question 16

What are the types of joins? Explain the differences.

Answer 16

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

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

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

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

Question 17

Explain the difference between UNION, UNION ALL, and INTERSECT?

Answer 17

Union-combines the results of two queries into a single set of all matching rows. Duplicates removed

Union All- combines two or more results into a single set, including all duplicates.

Intersect-takes the rows from both the result sets which are common in both

Question 18

What is a cascade delete?

Answer 18

When we create a foreign key using this option, it deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key.

Question 19

What is the purpose of a view? What about an index?

Answer 19

Views are used to focus, simplify, and customize the perception each user has of the database.

Index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes.

Question 20

What’s the difference between a clustered and non-clustered index?

Answer 20

Clustered index is a type of index in which table records are physically reordered to match the index.

A non-clustered index is a in which the logical order of index does not match physical stored order of rows on disk.

Question 21

How would you setup a primary key that automatically increments with every INSERT statement?

Answer 21

Give the primary key the auto_increment data type

Question 22

What’s the difference between implicit and explicit cursors?

Answer 22

Implicit cursors are automatically created when select statements are executed. Explicit cursors need to be defined explicitly by the user by providing a name. They are capable of fetching a single row at a time. Explicit cursors can fetch multiple rows

Question 23

What is a trigger? Give the syntax for creating a trigger.

Answer 23

A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs

1. create trigger [trigger_name]: Creates or replaces an existing trigger with the trigger_name.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.

Question 24

What is the difference between scalar and aggregate functions? Give examples of each

Answer 24

A scalar function returns a single value. It might not even be related to tables in your database.

• MID()
• ROUND()
• UCASE()
• LCASE()

An aggregate-valued function returns a calculation across the rows of a table – for example summing values.

• AVG()
• COUNT()
• FIRST()
• LAST()
• MAX()
• MIN()
• SUM()