PowerPoint 4 Flashcards
What is Data Retrieval Language(DRL)/Data Selection Language(DSL)?
It is a set of commands which are used to retrieve data from a database server.
It manipulates the data in a database for display purposes like aggregate functions(SUM, AVG, MIN, MAX, COUNT)
The SELECT statement is a DML command used by users to retrieve specific information they desire from an operational database.
What is a query?
A query is a command that manipulates data within a database.
Write a query that’ll allow you to view data from a specific table from a specific set of columns
SELECT <column_name(s)>
FROM <table_name>;</table_name>
What’s the difference between WHERE and HAVING?
The WHERE clause is used to filter rows based on specific conditions. It is typically used with SELECT, UPDATE, DELETE, or MERGE statements.
The HAVING clause is used to filter rows based on conditions involving aggregate functions, typically in conjunction with the GROUP BY clause.
Basically:
Both the WHERE clause and HAVING clause are used to filter rows based on specific condition, but the WHERE clause can’t use aggregate functions(avg(), sum(), etc.)
What is the asterisk(*) used for?
The asterisk(*) is a wildcard symbol, which displays all columns from a table.
What is the select list?
The list of expressions that appears between the statements SELECT and FROM
What can you use the CONCAT() operator for?
The CONCAT() function in MySQL is used to concatenate two or more strings or values together.
It can be used with character data types like strings, as well as with numeric data types.
What is the function of column aliases?
A column alias is used to give statements in the select list a cleaner, shorter or more descriptive heading.
Are you obligated to use the AS while giving a select statement within the select list a column alias?
No, you can leave the AS statement out when giving a select statement or column a column alias
What is the DISTINCT keyword used for?
The DISTINCT keywords can be used to eliminate duplicate values in a column and show only the unique values.
What are arithmetic operators?
They are arguments that can be used to either, add, subtract, multiply or divide numeric values.
What does the WHERE clause do?
The WHERE clause allows you to create conditions that rows must meet in order to be returned by the query.
The WHERE clause evaluates whether a condition is true or not.
What is a row level condition?
Row-level conditions refer to conditions or constraints that are applied to individual rows in a table.
These conditions determine whether a specific row meets certain criteria or satisfies specific rules.
examples:
PRIMARY KEY CONSTRAINT,
FOREIGN KEY CONSTRAINT,
UNIQUE,
NOT NULL
What can be used to check which rows are in the result set?
- Logical operators such as BETWEEN, AND, OR, NOT, IN and NOT IN
This can be a simple condition or a combination of multiple conditions - Common comparison operators (=, <>, <, >, <=, >=, !=)
These used to compare column values with constants or other column values.
What is the LIKE condition used for?
The LIKE operator is primarily used for pattern matching on string values.
What do wildcards do?
The use of wildcards (% and _) allows for flexible and powerful pattern matching with the LIKE condition.
What are aggregate functions and which aggregate functions exist?
Aggregate functions in SQL that perform calculations on a set of values and return a single result.
(use with HAVING)
SUM() - calculates the sum of all the values in a given column or expression
AVG() - calculates the average (mean) of the values in a given column or expression.
MIN() - returns the minimum value from a column or expression.
MAX() - returns the maximum value from a column or expression.
COUNT() - counts the number of rows that match a specified condition or the number of non-null values in a column.
What is the function of the ORDER BY clause and in which ways can you order results?
The ORDER BY clause of a SQL query allows you to determine the sort order of the rows returned by the operation.
When the ORDER BY clause is used, it must always be the last clause of the SQL statement.
ASC - Specifies that the results should be returned in ascending order.
DESC - Specifies that the results should be returned in descending order.
example:
ORDER BY <column_name> ASC;</column_name>
What is the function of the ORDER BY clause and in which ways can you order results?
The ORDER BY clause of a SQL query allows you to determine the sort order of the rows returned by the operation.
When the ORDER BY clause is used, it must always be the last clause of the SQL statement.
ASC - Specifies that the results should be returned in ascending order.
DESC - Specifies that the results should be returned in descending order.
example:
ORDER BY <column_name> ASC;</column_name>
What is the function of the GROUP BY clause?
The GROUP BY clause in SQL is used to group rows based on one or more columns. It allows you to divide the result set into groups and perform aggregate functions on each group.
for a better explanation, check out this video:
https://www.youtube.com/watch?v=nNrgRVIzeHg
