Database Technology
Unit 4 Assignment
WEEK 3 DATABASE TECHNOLOGY ASSIGNMENT
In-built functions using SQL with examples.
Answer:
In SQL, built-in functions are pre-defined functions that perform operations on the data stored in tables or views. These functions can be used in SQL queries to manipulate and retrieve data in various ways. Some common types of built-in functions in SQL include aggregate functions, string functions, numeric functions, and date/time functions. In this explanation, I will provide some examples of each type of function:
Aggregate Functions: These functions are used to perform calculations on groups of rows, and return a single result for each group. Some common aggregate functions include:
COUNT(): Counts the number of rows that match a specified condition.
Example: SELECT COUNT(*) FROM orders;{codeBox}
SUM(): Calculates the sum of values in a column.
Example: SELECT SUM(price) FROM orders;{codeBox}
AVG(): Calculates the average of values in a column.
Example: SELECT AVG(price) FROM orders;{codeBox}
String Functions: These functions are used to manipulate character strings, such as text fields. Some common string functions include:
CONCAT(): Concatenates two or more strings together.
Example: SELECT CONCAT(first_name, ' ', last_name) FROM customers;{codeBox}
UPPER(): Converts all characters in a string to uppercase.
Example: SELECT UPPER(product_name) FROM products;{codeBox}
LENGTH(): Returns the length of a string.
Example: SELECT LENGTH(product_description) FROM products;{codeBox}
Numeric Functions: These functions are used to perform mathematical operations on numeric values. Some common numeric functions include:
ROUND(): Rounds a numeric value to a specified number of decimal places.
Example: SELECT ROUND(price, 2) FROM products;{codeBox}
ABS(): Returns the absolute value of a number.
Example: SELECT ABS(quantity) FROM orders;{codeBox}
SQRT(): Calculates the square root of a number.
Example: SELECT SQRT(total_sales) FROM sales;{codeBox}
Date/Time Functions: These functions are used to manipulate date and time values. Some common date/time functions include:
NOW(): Returns the current date and time.
Example: SELECT NOW() FROM orders;{codeBox}
DATE(): Extracts the date from a date/time value.
Example: SELECT DATE(order_date) FROM orders;{codeBox}
MONTH(): Returns the month from a date/time value.
Example: SELECT MONTH(order_date) FROM orders;{codeBox}
These are just a few examples of the many built-in functions available in SQL. By using these functions, you can manipulate and retrieve data in a variety of ways, making SQL a powerful tool for data analysis and reporting.