Page 182 - Informatics_Practices_Fliipbook_Class12
P. 182
CREATE TABLE: creates a table in the database which is in use. It defines:
Ø
• attribute names, default values
• Attribute types
• Primary key
• Constraints: NOT NULL, foreign key, values allowed
Constraint: Constraints restrict the values an attribute can take.
Ø
DESCRIBE table_name: gives the structure of the table- displays attribute names, their data type and
Ø
constraints.
INSERT: Used to insert a row in a table.
Ø
UPDATE: Used to update an attribute value in a table for the tuple(s), typically based on a criterion specified
Ø
in a WHERE clause.
DELETE: Used to delete tuples from a table, typically based on a criterion specified in a WHERE clause.
Ø
DROP TABLE: DROP TABLE statement deletes an existing table in a database. The data stored in the
Ø
table is lost.
SELECT statement: Used to retrieve attribute values from the table, typically based on a criterion specified
Ø
in a WHERE clause.
ORDER BY clause: ORDER BY clause sorts data based on the specified attribute(s). By default, sorting is
Ø
done in ascending order. If required DESC should be specified to sort the data in descending order.
Aggregate Functions: Apply to an aggregate of tuples instead of a single tuple, for example,
Ø
• COUNT(): Displays the count of the tuples, typically based on a criterion specified in a WHERE clause.
• MAX(attribute-name), MIN(attribute-name), SUM(attribute-name),
AVG(attribute-name) : Displays the maximum, minimum, sum, or average value of an attribute from
the set of tuples.
Math functions: can be used to perform various mathematical calculations and manipulate numerical data.
Ø
POWER(x, y): calculates the value of x raised to the power of y.
ROUND(x, d): used to round a number to a specified number of decimal places.
MOD(x, y): yields the remainder when x is divided by y.
Text functions: can be helpful for manipulating and extracting information from text data.
Ø
UCASE() / UPPER(): is used to convert a string to uppercase.
LCASE() / LOWER():is used to convert a string to lowercase.
MID() / SUBSTRING() / SUBSTR():used to extract a portion of a string based on the specified
starting position and length.
LENGTH(): yields the length of a string in terms of characters.
LEFT():extracts a specified number of characters from the left side of a string.
RIGHT(): extracts a specified number of characters from the right side of a string.
INSTR(): yields the position of the first occurrence of a substring within a string.
LTRIM(): removes leading spaces (or specified characters) from a string.
RTRIM(): removes trailing spaces (or specified characters) from a string.
TRIM():removes leading and trailing spaces (or specified characters) from a string.
Date functions: can be helpful for working with date and time information.
Ø
168 Touchpad Informatics Practices-XII

