Revature SQL Flashcards
What is SQL?
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases.
What is a relational database management system?
A Relational Database Management System (RDBMS) is a SQL database which organize data into tables with rows and columns.
What is a database?
A Database is an application that provides long term data storage and functionality to efficiently access the stored data
What are the sublanguages of SQL?*
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
What is cardinality?
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
What is a candidate key?
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.
What is referential integrity?
Referential integrity ensures that foreign keys in a database remain consistent with the primary keys they reference.
What are primary keys? Foreign keys?
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.
What are some of the different constraints on columns?
Constraints on columns include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.
What is an entity relation diagram?
An Entity-Relationship Diagram (ERD) is a visual representation of the relationships between entities in a database.
What are the differences between WHERE vs HAVING?
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;
What are the differences between GROUP BY and ORDER BY?
GROUP BY groups rows sharing a property, while ORDER BY sorts the results of a query.
What does LIKE do?
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%’;
How do I use sub queries?*
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);
How does BETWEEN work?
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;
What is the order of operations in an SQL statement?
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.
What is the difference between an aggregate function and a scalar function?
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
What are examples of aggregate and scalar functions?
Examples of aggregate functions: SUM, AVG, COUNT. Examples of scalar functions: UPPER, LOWER, ABS.
What are the different joins in SQL?
SQL joins include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN.
What are the different set operations in SQL? Which set operations support duplicates?
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;
What is the difference between joins and set operations?*
Joins combine data from multiple tables based on conditions, while set operations combine results of multiple queries.
How can I create an alias in SQL?
An alias is created using the AS keyword to provide a temporary name for a table or column.
What does the AS keyword do in a query?
The AS keyword is used to create aliases for tables or columns for readability or convenience.
What is a transaction?
A transaction is an interaction with a database
What are the properties of a transaction?
Transactions have ACID properties: Atomicity, Consistency, Isolation, Durability.
What are the transaction isolation levels and what do they prevent?*
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.
What are dirty reads*
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.
What is normalization?*
Normalization is the process of organizing a database to reduce redundancy and improve integrity.
What are the requirements for different levels of normalization?*
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.
What is a view?
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;
What is a DAO?
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
What is the danger of putting values directly into our queries?
Directly inserting values risks SQL injection attacks and reduces maintainability.
What is a port number?
A port number is a communication endpoint used to identify specific processes on a server.
What is an Index?*
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);
What advantages does creating an Index give us? Disadvantages?
Advantages: Faster data retrieval. Disadvantages: Increased storage and slower writes.
What is CRUD?
CRUD stands for Create, Read, Update, Delete and they are operations used on databases
What does it mean that an operation or transaction on a data store is atomic?
An atomic operation is indivisible, ensuring all steps succeed or none at all.
What does ACID stand for?
ACID stands for Atomicity, Consistency, Isolation, Durability.
What does BASE stand for?
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.
Atomicity
Atomicity : a transaction succeeds or fails and a single unit
Consistency
Consistency : a transaction takes the database from one valid state to another (NOT the same as CAP Consistency)
Isolation
Isolation : multiple transactions taking place at the same time don’t interfere with each other
Durability
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.
What is a materialized view
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;
