SQL Data Types and Schemas

Date

• a data type in SQL used to store calendar dates. It represents a specific day in the format of year-month-day, such as 2024-11-10.

• This type allows you to store and manage date information in a standardized way, making it easy to perform operations like sorting, comparing, or calculating differences between dates.

Time

• a data type in SQL used to store the time of day.

• It represents a specific time in the format of hours:minutes:seconds, such as 14:30:00 for 2:30 PM.

• This type helps store and manage time information so you can perform operations like comparing or calculating differences between times.

Timestamp

• a data type in SQL that stores both date and time together.

• It represents a specific moment, such as 2024-11-10 14:30:00, which includes both the calendar date and the time of day.

• This type is useful for tracking when events happen or changes are made, allowing for detailed time-based operations like sorting or comparing specific moments.

Conversions

• Refers to changing data from one type to another.

• For example, you might convert a string representing a number, like '123', into an actual numeric type so that you can perform calculations with it.

• This process helps ensure that data can be used correctly in various operations and comparisons.

coalesce

• A function in SQL that returns the first non-null value from a list of given values.

• If all values are null, it returns null. This function is useful for handling situations where data might be missing, as it allows you to provide a fallback value.

• For example, _________(value1, value2, 0) will return value1 if it's not null, otherwise value2 if it's not null, and if both are null, it will return 0.

to_char

• a function in SQL that converts a number or date to a string format.

• This function is helpful when you want to display numbers or dates in a specific way, like adding leading zeros or formatting a date as DD-MM-YYYY.

• For example, ______(123, '0000') would display 123 as 0123, and _______(current_date, 'DD-MON-YYYY') could display today's date as 10-NOV-2024.

to_number

• a function in SQL that converts a string to a numeric type.

• This is useful when you have numbers stored as text and want to perform calculations or numeric operations on them.

• For example, if a column contains the value '123' as a string, using _____________('123') will convert it to the numeric value 123.

to_date

• a function in SQL that converts a string representing a date into an actual date type.

• This is useful when you have a date stored as text and want to use it as a proper date for calculations or comparisons.

• For example, _______('10-11-2024', 'DD-MM-YYYY') would convert the string '10-11-2024' into a date format that SQL can understand and work with.

decode

• a function in SQL that works like a simple if-then-else statement.

• It compares a given value against a list of possible match values and returns a corresponding result if a match is found; otherwise, it returns a default value.

• For example, ______(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') checks if status is 'A' and returns 'Active', or 'I' and returns 'Inactive'.

• If it doesn’t match any value, it returns 'Unknown'. This helps simplify conditional logic within SQL queries.

default

• a setting in SQL that automatically assigns a specified value to a column if no value is provided when a new row is added.

• This ensures that the column always has a value, even if the user doesn't explicitly enter one.

• For example, if a "status" column has a _______ value of 'Active', then any new record will have 'Active' in the "status" column unless another value is specified. This helps maintain data consistency and reduce the need for manual input.

insert

• an SQL command used to add new rows of data to a table in a database.

• When you use the _____ command, you specify the table and the values you want to add. For example, _______ into students (id, name, age) values (1, 'John', 20); adds a new student with ID 1, name 'John', and age 20 to the "students" table.

• This command helps populate the table with data.

large-object data types

• Special data types used to store very large amounts of data, such as images, videos, documents, or long text.

• These types include BLOB (Binary Large Object) for storing binary data like images and CLOB (Character Large Object) for storing large text data.

• They allow databases to handle and store data that is too big to fit into standard data types like VARCHAR.

clob

• A data type in SQL used to store very large amounts of text data, such as long documents or large text files.

• It can hold more text than standard data types like VARCHAR, making it useful for storing things like articles, reports, or any other text content that exceeds typical size limits.

blob

• A data type in SQL used to store large amounts of binary data, such as images, audio, videos, or any multimedia files.

• It can handle data that isn't plain text and is stored as raw bytes, making it suitable for managing files and media within a database.

user-defined data types

• custom data types that a user can create to fit specific needs not covered by standard data types.

• For example, if you want to have a column that stores monetary values in a specific format (like dollars or euros), you can create a _______________ type for that. This helps ensure consistency and clarity when using specialized data in your

distinct types

• are user-defined data types that are based on existing data types but are treated as separate and unique.

• They help prevent errors by ensuring that certain types of data are not mixed up, even if they have the same underlying format.

• For example, you could create a _____ _____ for Dollars and another for Pounds, both based on numeric, to avoid accidentally using a dollar value where a pound value is needed.

structured data types

• complex data types that allow you to create attributes made up of multiple fields, similar to a structure or record in programming.

• These types can include nested data, arrays, or combinations of different data types within a single type.

• For example, you could create a _________ ____ _____for an "Address" that includes fields like "street," "city," and "zip code," grouping them together as one attribute. This helps organize related data more efficiently in the database.

type

• Refers to the kind of data that can be stored in a column, such as INTEGER for whole numbers, VARCHAR for text, DATE for dates, and so on.

• _____ define the format and properties of the data, helping the database manage and validate it properly.

• For example, defining a column as DATE ensures that only valid date values can be stored in it.

domain

• A set of rules or a defined range of valid values that a column can have.

• It acts like a custom data type that can include constraints such as not null or specific value restrictions.

• For example, you could create a ______ called ageDomain that only allows whole numbers between 0 and 120.

• This ensures that any column using ageDomain will only store valid ages.

serial

• A data type in SQL that automatically generates unique sequential numbers for a column, typically used for primary keys.

• When you insert a new row, the database automatically assigns the next number in the sequence.

• This helps ensure each row has a unique identifier without manual input.

always

• used in SQL with the generated always as clause to indicate that a column's value is automatically generated by the database each time a new row is added.

• For example, if you have a column that needs a unique ID, using generated always as identity ensures the database will automatically fill in a new, unique value whenever a new record is inserted.

• This helps maintain consistent and automatic value generation without user input.

auto increment

• a feature in SQL used to automatically generate a unique number for a column whenever a new row is added to a table.

• This is commonly used for primary key columns to ensure each row has a unique identifier without manually entering it.

• For example, if a column is set to ____ ____________, the database will automatically assign the next number in the sequence (e.g., 1, 2, 3, etc.) when a new row is inserted.

generated always as identity

• an SQL feature that automatically assigns a unique value to a column whenever a new row is added to a table.

• This is commonly used for columns that act as unique identifiers, like primary keys.

• It ensures that the database generates the value for you, so you don't have to input it manually.

• For example, each new row would automatically get a number like 1, 2, 3, etc., making sure each value is unique.

create sequence

• An SQL command used to create a sequence object that generates a series of unique numbers.

• This is useful when you need to generate unique identifiers or auto-incrementing values for columns in multiple tables.

• For example, you can _____ _______ that starts at 1 and increments by 1 each time it’s used. This helps maintain consistent and unique values across various parts of a database without duplication.

catalogs

• top-level organizational structures in a database system that contain schemas.

• They act like containers that group related database objects, such as tables, views, and schemas.

• You can think of a _______ as a folder in a computer's file system, where each catalog can hold multiple schemas, and each schema contains the tables and data structures.

• This helps organize and manage large databases by separating different parts of the database into manageable sections.

schemas

• blueprints or containers within a database that organize and group related database objects, such as tables, views, and procedures.

• They help structure the database by keeping these objects logically separated, making it easier to manage and control access to different parts of the database.

• For example, in a university database, you might have separate ______ for "Students," "Courses," and "Instructors" to keep data organized.