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
   177   178   179   180   181   182   183   184   185   186   187