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

