10 Questions
What is the purpose of the LIKE operator?
To search for a specified pattern in a WHERE clause
What does the IN operator allow you to do?
Specify multiple values in a WHERE clause
Which of the following is NOT an aggregate function?
LIKE()
What does the SUM() function do?
Calculates the sum of all values in a column
What does the AS keyword do in SQL?
Gives a temporary column name
Which aggregate function returns the maximum value of a column?
MAX()
What is the data dictionary used for?
To store metadata about the structure of a database
Which aggregate function calculates the average value of a column?
AVG()
What is the purpose of arithmetic operations in SQL?
To perform calculations on column values
Which of the following is NOT a valid aggregate function?
LIKE()
Study Notes
Data Dictionary
- Describes the structure of the whole database
- Organized in a spreadsheet format
- Includes the names and descriptions of the tables and the fields contained in each table along with information of the data type, field length
- Provides metadata, or information about data
- No one set standard in terms of layout or information
Entity-Relationship Modeling
- A separate table is created for each entity of the database
Constraints
- Used to specify rules for the data in a table
- Ensure the accuracy and reliability of the data
- Types of constraints:
- PRIMARY KEY: column that uniquely identifies each row in the table
- FOREIGN KEY: column that refers to the primary key in another table, relates two tables
- UNIQUE: ensure that all the values in a column are unique/different
- NOT NULL: ensure that a column cannot have a NULL value
- DEFAULT: set a default value for a column when no value is specified
- AUTO_INCREMENT: allows a unique number to be generated automatically when a new record is inserted into a table
Auto-Increment
- Allows a unique number to be generated automatically when a new record is inserted into a table
- Starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record
- Useful for generating a unique primary key for each new record inserted into a table
SQL - WHERE Clause
- Used to extract only those records that fulfill a specified criteria
- Filter/search data
- Operators:
- Arithmetic Operators (+, -, *, /, %)
- Comparison Operators (=, <>, !=, etc.)
- Logical Operators (AND, OR, NOT)
- BETWEEN: selects values within a range
- IN: performs membership check
- LIKE: searches for a specified pattern
Aggregate Functions
- SUM(): sum of all the values in a column
- MAX(): maximum value of a column
- MIN(): minimum value of a column
- AVG(): average value of a column
Learn about the data dictionary, which describes the database structure in a spreadsheet format. It includes table and field names, data types, and more. Understand its role in providing metadata about data.
Make Your Own Quizzes and Flashcards
Convert your notes into interactive study material.
Get started for free