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) delete truncate drop create.
DML (Data Manipulation Language): read, insert, delete, udate
DCL (Data Control Language), and: Revoke and Grant
TCL (Transaction Control Language). Begin transactions, rollback, savepoint
DQL, SELECT

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). Helps optimize queries

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.. From where and slelcet

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.

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

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).

SELECT LENGTH(customerName) FROM customers;

SELECT SUM(orderTotal) FROM orders

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 queries.
UNION, INTERSECT, UNION ALL.
UNION ALL supports duplicates.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

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 (2 id in in table). 3NF: No transitive dependency(non key attiributes are not dependednt on other non key attributes). 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

What is a port number?

Answer 33

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

Question 34

What is an Index?*

Answer 34

An index is a database object that provides quick lookups of a column improving the speed of data retrieval. Used to optimize CREATE INDEX idx_pname ON Persons (LastName, FirstName);

Question 35

What advantages does creating an Index give us? Disadvantages?

Answer 35

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

Question 36

What is CRUD?

Answer 36

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

Question 37

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

Answer 37

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

Question 38

What does ACID stand for?

Answer 38

ACID stands for Atomicity, Consistency, Isolation, Durability.

Question 39

What does BASE stand for?

Answer 39

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 40

Atomicity

Answer 40

Atomicity : a transaction succeeds or fails and a single unit

Question 41

Consistency

Answer 41

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

Question 42

Isolation

Answer 42

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

Question 43

Durability

Answer 43

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 44

What is a materialized view

Answer 44

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;