Scribd
Upload
29 views

Structured Query Language SQL PART 2

This document provides an overview of various SQL functions including: - Date functions such as DATE_FORMAT, YEAR, MONTH, DAY, ADDDATE, and DATE_ADD. - Numeric functions including ABS, ROUND, CEIL, FLOOR. - String functions like CONCAT, UPPER, LOWER, SUBSTRING, LENGTH. - Conversion functions to convert between data types like CAST and CONVERT. - The CASE function to return different values based on conditions. - DDL commands ALTER, DROP, UPDATE, and DELETE to modify database, table, and data.

Uploaded by

markj.mendoza.1019
Copyright
© © All Rights Reserved
Available Formats
Download as PDF, TXT or read online on Scribd
29 views

Structured Query Language SQL PART 2

This document provides an overview of various SQL functions including: - Date functions such as DATE_FORMAT, YEAR, MONTH, DAY, ADDDATE, and DATE_ADD. - Numeric functions including ABS, ROUND, CEIL, FLOOR. - String functions like CONCAT, UPPER, LOWER, SUBSTRING, LENGTH. - Conversion functions to convert between data types like CAST and CONVERT. - The CASE function to return different values based on conditions. - DDL commands ALTER, DROP, UPDATE, and DELETE to modify database, table, and data.

Uploaded by

markj.mendoza.1019
Copyright
© © All Rights Reserved
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 19

ICS2607 INFORMATION MANAGEMENT

Structured Query Language (SQL)

Instructor:
Mr. Francis Noel I. Alarcon, LPT, MS
Date and Time Functions – DATE_FORMAT
• Returns a character string or a formatted string from a date value
• DATE_FORMAT(date_value, fmt) *fmt = format used; can be:
• %M: name of month
• %m: two-digit month number
• %b: abbreviated month name
• %d: number of day of month
• %W: weekday name
• %a: abbreviated weekday name
• %Y: four-digit year
• %y: two-digit year
Date and Time Functions – YEAR, MONTH, DAY
• Returns a four-digit year
• YEAR(date_value)

• Returns a two-digit month code


• MONTH(date_value)

• Returns the number of the day


• DAY(date_value)
Date and Time Functions – ADDDATE
• Adds a number of days to a date
• ADDDATE(date_value, n)
• n = number of days
Date and Time Functions – DATE_ADD
• Adds a number of days, months, or years to a date.
• This is similar to ADDDATE except it is more robust.
• It allows the user to specify the date unit to add.
• DATE_ADD(date, INTERVAL n unit)
• n = number to add
• unit = date unit, can be:
• DAY: add n days
• WEEK: add n weeks
• MONTH: add n months
• YEAR: add n years
Date and Time Functions – LAST_DAY
• Returns the date of the last day of the month given in a date
• LAST_DAY(date_value)
Numeric Functions – ABS, ROUND
• Returns the absolute value of a number
• ABS(numeric_value)

• Rounds a value to a specified precision (number of digits)


• ROUND(numeric_value, p)
• p = precision
Numeric Functions – CEIL/CEILING/FLOOR
• Returns the smallest integer greater than or equal to a number or
returns the largest integer equal to or less than a number, respectively
• CEIL(numeric_value) Oracle or MySQL
• CEILING(numeric_value) MS SQL Server or MySQL
• FLOOR(numeric_value)
String Functions – Concatenation CONCAT()
• Concatenates data from two or more different character columns and
returns a single column.
• CONCAT(strg_value, strg_value)
String Functions – UPPER, UCASE, LOWER, LCASE
• Returns a string in all capital or all lowercase letters
• UPPER(strg_value)
• UCASE(strg_value)
• LOWER(strg_value)
• LCASE(strg_value)
String Functions – SUBSTRING, SUBSTR
• Returns a substring or part of a given string parameter
• SUBSTR(strg_value, p, l)
• SUBSTRING(strg_value,p,l)
• p = start position
• l = length of characters
• If the length of characters is omitted, the functions will return the remainder of
the string value.
String Functions – LENGTH
• Returns the number of characters in a string value
• LENGTH(strg_value)
Conversion Functions – String to Number
• Returns a number from a character string

• CAST (value-to-convert as numeric-data type)


• that in addition to the INTEGER and DECIMAL(l,d) data types, Oracle supports
NUMBER and MS SQL Server supports NUMERIC.

• CONVERT(value-to-convert, decimal(l,d))
• Other than the data type to be converted into, these functions operate the
same as described above.
Conversion Functions – CASE
• Compares an attribute or expression with a series of values and returns
an associated value or a default value if no match is found
• CASE When condition
THEN value1 ELSE value2 END
Conversion Functions – CASE
• Compares an attribute or expression with a series of values and returns
an associated value or a default value if no match is found
• CASE When condition
THEN value1 ELSE value2 END
ALTER - Columns
• It is used to add, delete, or modify columns in an existing table.

• ALTER TABLE table_name


ADD column_name datatype;

• ALTER TABLE table_name


DROP COLUMN column_name;

• ALTER TABLE table_name


MODIFY COLUMN column_name datatype;
DROP
• To delete a database
• DROP DATABASE databasename;

• To delete a table
• DROP TABLE table_name;
UPDATE
• UPDATE tablename
SET columnname = expression [, columnname = expression]
[WHERE conditionlist ];
DELETE
• DELETE FROM tablename
[WHERE conditionlist ];

You might also like