Revature SQL

Question 1

What is SQL?

Answer 1

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases.

Question 2

What is a relational database management system?

Answer 2

A Relational Database Management System (RDBMS) is a SQL database which organize data into tables with rows and columns.

Question 3

What is a database?

Answer 3

A Database is an application that provides long term data storage and functionality to efficiently access the stored data

Question 4

What are the sublanguages of SQL?

Answer 4

SQL sublanguages include DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

Question 5

What is cardinality?

Answer 5

Cardinality in data modeling refers to the number of unique values in a column or the nature of relationships between entities (e.g., one-to-one, one-to-many).

Question 6

What is a candidate key?

Answer 6

A candidate key is an attribute, or a set of attributes, that uniquely identifies a record in a table and could serve as a primary key.

Question 7

What is referential integrity?

Answer 7

Referential integrity ensures that foreign keys in a database remain consistent with the primary keys they reference.

Question 8

What are primary keys? Foreign keys?

Answer 8

A primary key uniquely identifies a record in a table. A foreign key is an attribute in one table that references the primary key of another table.

Question 9

What are some of the different constraints on columns?

Answer 9

Constraints on columns include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

Question 10

What is an entity relation diagram?

Answer 10

An Entity-Relationship Diagram (ERD) is a visual representation of the relationships between entities in a database.

Question 11

What are the differences between WHERE vs HAVING?

Answer 11

WHERE filters rows before grouping, while HAVING filters groups after grouping.

SELECT
Region,
SUM(Revenue) AS TotalRevenue
FROM
Sales
GROUP BY
Region
HAVING
SUM(Revenue) > 10000;

Question 12

What are the differences between GROUP BY and ORDER BY?

Answer 12

GROUP BY groups rows sharing a property, while ORDER BY sorts the results of a query.

Question 13

What does LIKE do?

Answer 13

The LIKE operator is used for pattern matching in SQL, allowing partial matches using wildcards (% for multiple characters, _ for a single character).

Query 1: Find employees whose names start with ‘J’.

SELECT
EmployeeID, Name
FROM
Employees
WHERE
Name LIKE ‘J%’;

Question 14

How do I use sub queries?

Answer 14

Subqueries are nested queries within a main query, used to retrieve intermediate results or filter data.

EX: Greater than average

SELECT
EmployeeID, Name, Salary
FROM
Employees
WHERE
Salary > (SELECT AVG(Salary) FROM Employees);

Question 15

How does BETWEEN work?

Answer 15

The BETWEEN operator is used to filter values within a range, inclusive of the start and end values.

Question 16

What is the order of operations in an SQL statement?

Answer 16

FROM

Specifies the source table(s) or subqueries.
Joins between tables are resolved at this stage.
WHERE

Filters rows based on specified conditions.
Only rows that satisfy the WHERE clause condition proceed further.
GROUP BY

Groups the data into summary rows based on specified column(s).
HAVING

Filters groups (created by GROUP BY) based on conditions.
Operates on aggregated data.
SELECT

Evaluates the columns or expressions to return in the result.
Includes functions, calculations, or derived fields.
DISTINCT

Removes duplicate rows from the result set.
ORDER BY

Sorts the result set based on specified column(s) and sort order (ASC/DESC).
LIMIT / FETCH / TOP

Restricts the number of rows returned in the final result.The order of SQL operations is FROM, WHERE, GROUP BY, HAVING, ORDER BY, and SELECT.

Question 17

What is the difference between an aggregate function and a scalar function?

Answer 17

Aggregate functions operate on sets of values and return a single result (e.g., SUM, AVG), while scalar functions operate on individual values (e.g., UPPER, ROUND).

Question 18

What are examples of aggregate and scalar functions?

Answer 18

Examples of aggregate functions: SUM, AVG, COUNT. Examples of scalar functions: UPPER, LOWER, ABS.

Question 19

What are the different joins in SQL?

Answer 19

SQL joins include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN.

Question 20

What are the different set operations in SQL? Which set operations support duplicates?

Answer 20

Set operations are used to join the result of two or more quieries.
UNION, INTERSECT, UNION ALL.

UNION ALL supports duplicates.

Question 21

What is the difference between joins and set operations?

Answer 21

Joins combine data from multiple tables based on conditions, while set operations combine results of multiple queries.

Question 22

How can I create an alias in SQL?

Answer 22

An alias is created using the AS keyword to provide a temporary name for a table or column.

Question 23

What does the AS keyword do in a query?

Answer 23

The AS keyword is used to create aliases for tables or columns for readability or convenience.

Question 24

What is a transaction?

Answer 24

A transaction is an interaction with a database

Question 25

What are the properties of a transaction?

Answer 25

Transactions have ACID properties: Atomicity, Consistency, Isolation, Durability.

Question 26

What are the transaction isolation levels and what do they prevent?

Answer 26

Isolation levels include READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE,. They are used to determine the degree of isolation between each transactions. There is a trade off between data consistency and system performace/concurrency.

Question 27

What are dirty reads

Answer 27

non repeatable reads

A dirty read occurs when a transaction reads data that has been modified by another uncommitted transaction. This can lead to inconsistent or incorrect data being read because the changes might be rolled back in the other transaction.

Question 28

What is normalization?

Answer 28

Normalization is the process of organizing a database to reduce redundancy and improve integrity.

Question 29

What are the requirements for different levels of normalization?

Answer 29

1NF: Atomic columns. 2NF: No partial dependency. 3NF: No transitive dependency. BCNF: Each determinant is a candidate key.

Question 30

What is a view?

Answer 30

A view in SQL is a virtual table that provides a way to simplify complex queries by storing a predefined SELECT statement. Views don’t store actual data; instead, they store a query that generates results when accesse

Consider a Customers table and a Orders table. We can create a view to show customer order details.

CREATE VIEW CustomerOrders AS
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.OrderDate
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Question 31

What is a DAO?

Answer 31

A DAO (Data Access Object) is a design pattern that provides an abstraction layer between the application and the datasource.

EX: A collection of CRUD methods

Question 32

What is the danger of putting values directly into our queries?

Answer 32

Directly inserting values risks SQL injection attacks and reduces maintainability.

Question 33

How do we specify dependencies using sbt?

Answer 33

Dependencies are specified in the build.sbt file using libraryDependencies += <dependency>.</dependency>

Question 34

What is a port number?

Answer 34

A port number is a communication endpoint used to identify specific processes on a server.

Question 35

What’s the default port for Postgres?

Answer 35

The default port for PostgreSQL is 5432.

Question 36

What is an Index?

Answer 36

An index is a database object that provides quick lookups of a column improving the speed of data retrieval

Question 37

What advantages does creating an Index give us? Disadvantages?

Answer 37

Advantages: Faster data retrieval. Disadvantages: Increased storage and slower writes.

Question 38

What is CRUD?

Answer 38

CRUD stands for Create, Read, Update, Delete and they are operations used on databases

Question 39

What does it mean that an operation or transaction on a data store is atomic?

Answer 39

An atomic operation is indivisible, ensuring all steps succeed or none at all.

Question 40

What does ACID stand for?

Answer 40

ACID stands for Atomicity, Consistency, Isolation, Durability.

Question 41

What does BASE stand for?

Answer 41

BASE stands for Basically Available, Soft state, Eventual consistency.

Basically Available : reads and writes are available as much as possible, using multiple machines in a cluster

Soft state : records stored in the database may be inconsistent across a cluster, some may be more up to date than others

Eventual consistency : if the databases runs long enough, it will achieve consistent data across the cluster BASE favors Availability and Partition Tolerance in a distributed database.

Question 42

Atomicity

Answer 42

Atomicity : a transaction succeeds or fails and a single unit

Question 43

Consistency

Answer 43

Consistency : a transaction takes the database from one valid state to another (NOT the same as CAP Consistency)

Question 44

Isolation

Answer 44

Isolation : multiple transactions taking place at the same time don’t interfere with each other

Question 45

Durability

Answer 45

Durability : transactions satisfy ACID even in the case of disaster (power shut off to DB) It’s useful to start thinking about ACID as a property of SQL databases, but really SQL and NoSQL databases both implement useful features of the other.

Question 46

What is a materialized view

Answer 46

A materialized view is similar to a regular view, but with one significant difference: it stores the result of the query physically. The data in a materialized view is precomputed and saved, so

CREATE MATERIALIZED VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;