Page 281 - Information_Practice_Fliipbook_Class11
P. 281

Ø   NOT NULL Constraint:  It applies to an attribute. No row in the table can have a  NULL value of the specified
                  attribute. For example, if we specify NOT NULL constraint for the  Salary attribute of EMPLOYEE  table,
                  then   Salary  of an employee cannot have the value NULL.
              Ø   Uniqueness Constraint: It applies to an attribute. No two rows can have same value of the specified attribute.
                  For example, in a STUDENT table, AadharNo may be defined to be UNIQUE, even if it is not the primary key.
              Ø   Domain Constraint: Domain Constraint allows you to restrict the set of values that a particular attribute  may
                  take. For example, we may constraint  attribute Salary to  take values between 8000 and 100000.
              Ø   SQL: Most popular language for creating and manipulating relational databases.
              Ø   DDL: Used for defining database attributes, their types, and constraints on attribute values.
              Ø   DML: Used to retrieve and modify data.
              Ø   Data Types:
                  •   INT or INTEGER:  32-bit integer.
                  •   SMALLINT: Typically 16-bit integer.
                  •   DECIMAL(L, D): Fixed point decimal, comprising L digits, D digits after the decimal point.
                  •   FLOAT(p): Floating point number.
                  •   CHAR(L) or CHARACTER(L): Fixed-length character strings of length L.
                  •   VARCHAR(L): Variable-length character strings of length up to L.
                  •   DATE: The standard date format is YYYY-MM-DD.
              Ø   CREATE DATABASE database-name: Creates a database table and assigns it a name.

              Ø   SHOW DATABASES: Displays names of all the existing databases.
              Ø   USE database_name: Makes the databse active for use.
              Ø   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.
              Ø   BETWEEN operator checks whether a  value is within a specified range. It may be applied to numbers, text,
                  and dates.

              Ø   IN operator checks whether a  value is within a set.
              Ø   LIKE operator uses wildcard operators % and _ to define a pattern.




                                                                Database Concepts and the Structured Query Language  267
   276   277   278   279   280   281   282   283   284   285   286