DBMS (2) Flashcards
(32 cards)
facilitate data manipulation tasks by accepting parameters, executing computations, and delivering results.
SQL FUNCTIONS
operate on sets of values, producing a single result from multiple rows. Common aggregate functions include SUM(), AVG(), COUNT(), MIN(), and MAX(). They are instrumental in performing calculations across groups of data, often in conjunction with the GROUP BY clause.
AGGREGATE FUNCTION
operate on individual values, transforming or manipulating them in various ways. ________ functions find wide application in formatting, validation, and manipulation of data within SQL queries.
SCALAR FUNCTIONS
also known as string functions, are a subset of scalar functions in SQL that specifically operate on character or string data.
CHARACTER FUNCTION
function is a type of SQL function that operates on numeric data types, such as integers or floating-point numbers. These functions perform various mathematical operations or transformations on numeric values and return a single numeric result.
NUMERIC SCALAR
This is used to perform operations on a set of values and return a single result.
AGGREGATE FUNCTION
is a mathematical function that takes a single value as in input and produces a single value as output. Scalar functions can operate on scalar inputs using various mathematical operations like addition, subtraction, multiplication, division and etc. By using select, where and having.
SCALAR FUNCTIONS
OTHER NUMERICAL FUNCTIONS
SQRT(attribute):
CEILING(attribute):
ABS(attribute):
FLOOR(attribute)
SQUARE(attribute)
generates a date by incrementing a designated portion of a
given date by a specified value.
DATEADD
returns the difference between two parts of a date
DATEDIFF
returns the specified part of the date requested
DATEPART
returns the specified part of the date requested.
GETDATE()
emerge as versatile tools for conditional logic. These statements enable us to execute different actions based on varying conditions within our database queries.
CASE
Then for a single condition you can write the keyword WHEN followed by the condition that has to be satisfied. After that comes the keyword THEN and the value for that condition, like WHEN <condition> THEN <stuff></stuff></condition>
CASE
is a special type of stored procedure that automatically runs when an event occurs in the database server. In SQL, a ______is like a special set of instructions that automatically gets executed when certain events happen in your database. These events could be things like inserting a new row into a table, updating an existing row, or deleting a row.
TRIGGERS
3 TYPES OF DATABASE TRIGGERS
DDL TRIGGERS (DATA DEFINITION LANGUAGE)
DML (DATA MANIPULATION LANGUAGE)
LOGON TRIGGERS
SQL means?
Structured Query Language
is a component of Structured Query Language (SQL) that deals with defining and modifying the structure of database objects. It focuses on creating and managing the schema of the database.
CREATE
DROP
TRUNCATE.
ALTER
DDL or DATA DEFINITION LANGUAGE
are special stored procedures that are automatically executed or fired in response to specific ______________ events on tables or views. These events include INSERT, UPDATE, and DELETE operations.
DML triggers (Data Manipulation
Language triggers)
is fired automatically on a _____ event. It created at the server level. We can use this to control the SQL login security.
LOGON Triggers
used to concatenate two or more strings into a single string.
CONCAT():
used to converts all characters in a string to uppercase.
UPPER()
used to converts all characters in a string to lowercase.
LOWER()
used to extracts a substring from a given string
SUBSTRING()
