Week 7 Flashcards
What is the UNIQUE Constraint in MySQL?
Ensures values in a column or group of columns are different
What is the CHECK Constraint in MySQL?
Ensures that values stored in a column or group of columns satisfy a Boolean expression
What is the NOT NULL Constraint in MySQL?
Ensures values in a column or a group of columns are different
What is a Foreign Key (Constraint)?
Refers to Primary Key in another table
Is a column or set of columns used to establish a link between the data in two tables
These columns must match the columns in the referenced table (the parent table) and must have the same data type
What are Constraints in MySQL?
SQL Constraints are rules applied to columns in a table to ensure data integrity and consistency
They help to define the type of data that can be inserted into a table and prevent invalid data
What is a Primary Key (Constraint)?
Implicitly means NOT NULL + UNIQUE
A table can have one and only one primary key
What is SQL?
SQL is a standard language for interaction with a relational database
declarative language (focuses on the what, not how)
What is SELECT in SQL?
The SQL command used to retrieve data from a database
It specifies which columns and rows to fetch from one or more tables. Example: SELECT column1, column2 FROM table_name;
What is SELECT- DISTINCT in SQL?
The SQL command used to retrieve unique (non-duplicate) values from a column or set of columns in a table
It removes duplicate rows from the result
Example: SELECT DISTINCT column1 FROM table_name;
What is SELECT - WHERE in SQL?
The SQL command used to retrieve data from a table based on a specific condition
The WHERE clause filters the rows that meet the given condition
Example: SELECT column1 FROM table_name WHERE condition;
This returns only the rows where the condition is true
eg.
SELECT CustomerName, City, Country
FROM Customers
WHERE Country = ‘Spain’
What are the 5 basic Selection conditions in SQL?
Comparison: =, <>, (!=), <, <=, >=, >, OR, AND, NOT
Range: BETWEEN … AND, NOT BETWEEN.. AND
Set membership: IN, NOT IN
Pattern Match: LIKE, NOT LIKE
IS NULL, IS NOT NULL
What is SELECT - BETWEEN/IN
SELECT - BETWEEN/IN:
BETWEEN: Filters rows where a column value is within a specified range (inclusive).
Example: SELECT column1 FROM table_name WHERE column1 BETWEEN 10 AND 20;
IN: Filters rows where a column value matches any value in a specified list.
Example: SELECT column1 FROM table_name WHERE column1 IN (10, 20, 30);
What is SELECT - LIKE/NOT LIKE
SELECT - LIKE/NOT LIKE:
LIKE: Filters rows where a column value matches a specified pattern, often using wildcard characters (% for any sequence of characters, _ for a single character)
Example: SELECT column1 FROM table_name WHERE column1 LIKE ‘A%’; (finds values starting with “A”)
NOT LIKE: Filters rows where a column value does not match a specified pattern
Example: SELECT column1 FROM table_name WHERE column1 NOT LIKE ‘A%’; (finds values that don’t start with “A”)
What is SELECT - IS NULL/ IS NOT NULL
IS NULL: Filters rows where a column value is NULL (unknown or missing).
Example: SELECT column1 FROM table_name WHERE column1 IS NULL;
IS NOT NULL: Filters rows where a column value is not NULL (i.e., has a value).
Example: SELECT column1 FROM table_name WHERE column1 IS NOT NULL;
What is SELECT - ORDER BY
SELECT - ORDER BY: The SQL command used to sort the results of a query by one or more columns, either in ascending (ASC) or descending (DESC) order.
Example:
SELECT column1 FROM table_name ORDER BY column1 ASC; (sorts in ascending order)
SELECT column1 FROM table_name ORDER BY column1 DESC; (sorts in descending order)
What are the 5 Aggregate Functions?
COUNT
MIN
MAX
SUM
AVG
What does COUNT do?
An SQL aggregate function that returns the number of rows that match a specified condition or the total number of rows in a table.
Example:
SELECT COUNT(*) FROM table_name; (counts all rows in the table)
SELECT COUNT(column1) FROM table_name WHERE condition; (counts rows where column1 is not NULL)
What is MIN and MAX in SQL?
MIN and MAX: SQL aggregate functions used to find the smallest (MIN) and largest (MAX) values in a specified column.
MIN: Returns the smallest value in a column.
Example: SELECT MIN(column1) FROM table_name;
MAX: Returns the largest value in a column.
Example: SELECT MAX(column1) FROM table_name;
What is SUM in SQL?
An SQL aggregate function that returns the total sum of values in a numeric column
Example:
SELECT SUM(column1) FROM table_name;
This adds up all the values in column1.
What is AVG in SQL?
An SQL aggregate function that calculates the average (mean) value of a numeric column
Example:
SELECT AVG(column1) FROM table_name;
This returns the average of all values in column1.
What is GROUP BY in SQL?
GROUP BY in SQL is used to organize data into groups based on one or more columns.
It aggregates rows that have the same values in specified columns, often used with aggregate functions like SUM, AVG, COUNT, etc
What is a Subquery (or nested query)?
A complete SELECT statement embedded in another query
