CBSE Class 11 Informatics Practices Chapter 9 MySQL

Class Notes of Chapter 9: MySQL

Class 11th Informatics Practices

MySQL

      Topics:

• Important Features of MySQL
• MySQL Data Types
• SQL Commands
• Working With SQL

Important Features of MySQL

1. Released under open source and available free of cost.
2. Easy to learn and use.
3. Fast processing speed and easy in installation. Occupy very less space.
4. Supports standards-based SQL.
5. Provides portability.
6. High Security.
7. Provides many data types.
8. Handles large database.

MySQL Data Types
Every column (or data item) should belong to a unique domain (known as data type). These data types help to describe the kind of information a particular column holds. MySQL supports the ANSI SQL data types. Some of the commonly used data types along with their characteristics are as follows:

• Numeric Data Types

1. INT − A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
2. FLOAT(M, D) − A floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 10,2, where 2 is the number of decimals and 10 is the total number of digits (including decimals). Decimal precision can go to 24 places for a FLOAT.
3. DOUBLE(M, D) − A double precision floating-point number that cannot be unsigned. You can define the display length (M) and the number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a synonym for DOUBLE.
4. DECIMAL(M, D) − An unpacked floating-point number that cannot be unsigned. In the unpacked decimals, each decimal corresponds to one byte. Defining the display length (M) and the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.

• Date Types

1. DATE − A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, December 30th, 1973 would be stored as 1973-12-30.
2. YEAR(M) − Stores a year in a 2-digit or a 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be between 1970 to 2069 (70 to 69). If the length is specified as 4, then YEAR can be 1901 to 2155. The default length is 4.

• String Types

1. CHAR(M) − A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
2. VARCHAR(M) − A variable-length string between 1 and 255 characters in length. For example, VARCHAR(25). You must define a length when creating a VARCHAR field.

SQL Commands

SQL commands can be classified into the following:

Data Definition Language (DDL): A database scheme is defined by a set of definitions, which are expressed, by a special set of commands called Data Definition Language (DDL). They are used to create tables, databases, identify data items, provide unique names to the data items and to define the length and provide the range of values that each data item can assume. They are CREATE TABLE, ALTER TABLE and DROP TABLE commands.


Working With SQL

To work on MySQL, you need to open or create the database first:
To Create/Open Database:

mysql> CREATE DATABASE <name of database>; Now the database with the given name will be created. One must be connected to the database before using it, as below:
mysql> use <name of database>;

Creating Tables

Tables are defined with the CREATE TABLE command. When tables are created its columns are named, data types and sizes supplied for each column. At least one column must be specified.

Syntax:

CREATE TABLE <TableName>(<ColumnName1> <Data Type1>,
<ColumnName2> <Data Type2>,….. ….,<ColumnNameN> <Data Type N>);

Example:

mysql> CREATE TABLE Students ( RollNo DECIMAL(3), Name VARCHAR(25) );
Once the table is created we can insert the record in it, edit or delete existing records, and also we can search for the desired record in a very comprehensive way using the SQL Select statement.

Creating tables with SQL Constraints

• A Constraint is a condition or checks applicable on a field or set of fields.
• Data constraints are the rules that are defined when a table is created.
• They can also be defined or modified after creating the tables.
• When constraints are defined any data entering in the table is first checked to satisfy conditions the specified in particular constraint if it is, only then table data can be updated.

If data updation/ insertion is violating the defined constraints, the database rejects the data (entire record is rejected).

• When a constraint is applied to a single column, it is called a column level constraint but if a constraint is applied on a combination of columns it is called a table constraint.

Following constraints can be defined on a table in SQL

Constraints name Description

• PRIMARY KEY to create a primary key
• NIQUE to create a unique key
• NOT NULL to define that column will not accept null values.
• FOREIGN KEY/ REFERENCES to define referential integrity with another table.
• DEFAULT to define the columns default value.
• CHECK to define the custom rule.
• NOT NULL and DEFAULT constraints can be applied only at column level rest all constraints can be applied on both column level and table levels.

Use of Constraint

• CREATE TABLE student (Srollno integer NOT NULL, …);
• CREATE TABLE student (Srollno integer UNIQUE, …);
• CREATE TABLE student (SRNo integer NOT NULL, Sclass integer, Sname varchar(30), Sclass DEFAULT 12);
• CREATE TABLE student (Srollno integer CHECK (Srollno>0), Sclass integer, Sname varchar(30));
• CREATE TABLE student (Srollno integer NOT NULL PRIMARY KEY, Sclass integer, Sname varchar(30));
• CREATE TABLE teacher (Tid integer NOT NULL, FOREIGN KEY (Studentid ) REFRENCES student (Sid));

Inserting the Record in Existing Table

The INSERT INTO command append a new record to an existing table and initializes it to desired values.

Syntax:

>> INSERT INTO table_name (column_name [,column_name]) VALUES (value [,value]);

Example :

>> INSERT INTO Student (RollNo,Name) VALUES (12333,’Anu’);
 
Inserting NULL Values:

INSERT INTO Student (RollNo,Name, Class, Grade) VALUES (12333,’Anu’,11, NULL);

Inserting Dates:

INSERT INTO Student (RollNo,Name, Class, DOB) VALUES (12333,’Anu’,11, ‘1998-02-24’);

Inserting Data from another Table:

INSERT INTO Marks SELECT * FROM Student WHEREClass>10;

NOTE: Column names can be omitted if the values are entered in the same order in which they appear in the table. Insert into will give you an error if you omit to enter a mandatory value (non-null).

Deleting Existing records from the table :

The DELETE command deletes one, many, or even all records in a table, depending on the conditions that you specify.

Syntax:

DELETE FROM tablename WHERE search_conditions;

For example:

DELETE FROM Students WHERE RollNo >11255;

Note:

The delete command is VERY dangerous. If run without conditions, it will delete ALL records in a table. In addition, SQL has no undo function. For instance, DELETE FROM Students; Will delete all records from Students table. This is not likely to be what you want.

Modifying the Contents of Records:

The UPDATE command changes one, many, or even all records in a table, depending on the conditions that you specify

Syntax:

UPDATE tablename SET column_name = expression [,column_name = expression..] [WHEREsearch_conditions];
for example(assuming a customer table)
UPDATE customer
SET f_name = ‘Thomas’
WHERE l_name = ‘Smith’ and date_of_birth = ‘3/2/1985’;
An expression can be either a constant value (e.g., ‘Thomas’) or an operation done on another column or columns (see the example below, assuming a loan table with column rate.).
UPDATE TABLE loan
SET rate = rate + 1.5;

Because there is no condition (i.e., no WHERE ) all records will be updated. All rates will be increased by 1.5.

Selecting Data from Existing Table:

The SQL SELECT statement is a comprehensive statement used to search/select records from one or more tables. All the analysis done on a database usually involves some form of the select statement.

• Choosing all fields (columns) : Use a asterisk (*) to indicate all fields with the select statement: SELECT * FROM table_name;

For example :

SELECT * FROM customer;

• Choosing a selected list of fields (columns)
SELECT column_name [,column_name] FROM table_name;
SELECT f_name, l_name, date_of_birth FROM customer;

NOTE: The order in which you list the columns affects their order in the resulting output. Items within [ ] are optional.

• Temporarily renaming columns in query results
SELECT column_heading AS column_name [,column_heading AS column_name] FROM table_name;

Example:

SELECT f_name as “Name” FROM customer;

• Including calculated columns in the results
SELECT date_due, rate, principal, rate * principal FROM loan;

NOTE: If necessary, use parentheses to clarify the order of precedence.

• Eliminating duplicate query results with distinct If you use the keyword distinct after the keyword SELECT, you will only get unique rows.

Example:

SELECT rate, FROM loan;

(above will display all rate values might be repeated)
SELECT distinct rate FROM loan;
(above will display only unique rate values, no repetition)

Selecting from all the Rows:

SELECT ALL rate, FROM loan;
(above query will display all rate values)

Selecting Rows:

WHERE clause is used to specify the condition for searching. Only those records will be retrieved that satisfy the condition given with where clause.

SELECT SELECT_list FROM table_list
WHEREsearch_conditions;

Example:

SELECT * FROM customer WHERE f_name = ‘Carl’;

Possible Search Conditions:

Comparison operators (=, <, >, != .<>, <=, >=)
SELECT * FROM loan WHERE principal > 100000000;

• Ranges (between and not between; inclusive)
SELECT * FROM loan WHERE rate BETWEEN 7.5 AND 8.5;
Or you can write the following statement for the same:
SELECT * FROM loan WHERE rate NOT BETWEEN 7.5 AND 8.5;

• Lists (in and not in)
SELECT * FROM Customer
WHERE city IN (‘Ahmedabad’, ‘Baroda’, ‘Delhi’,’Mumbai’,’Chennai’);
Or you can Not with IN as:
SELECT * FROM Customer
WHERE city NOT IN (‘Ahmedabad’, ‘Baroda’, ‘Delhi’,’Mumbai’,’Chennai’);

• Null values
SELECT * FROM Customer WHERE city is NULL;
SELECT * FROM Customer WHERE city IS NOT NULL;

• Character matches (like and not like)
SELECT f_name, l_name FROM customer WHERE l_name LIKE ‘Fos%’;
SELECT f_name, l_name FROM customer WHERE l_name LIKE ‘_oster’;

Note: “%” (matches any string of zero or more characters) and “_” (matches any one character). In addition to those, brackets can be used to include either ranges or sets of characters. Combinations of previous options using logical operators and, or, and not etc.:
SELECT f_name, l_name FROM customer
WHERE l_name LIKE ‘San%’ AND City NOT IN (‘Baroda’,‘Delhi’)

• Some more examples:
• ‘Am%’ matches any string starting with Am.
• ‘%Singh%’ matches any string containing ‘Singh’
• ‘%a’ matches any string ending with ‘a’
• ‘_ _ _’ matches any string that is exactly 3 characters long.
• ‘_ _ %’ matches any string that has at least 2 characters long.
• ‘_ _ _g’ matches any string that is 4 characters along with 3 characters in the beginning but ‘g’ as the 4th character.
• Viewing a tables structures Describe/ Desc statement is used to see the structure of a table: Desc <tablename> ;
Describe <tablename>;

• Sorting records
The output of a SELECT query can be sorted in ascending or descending order on one or more columns, the default is ascending. This is important to note that the data in the table is not sorted, only the results that appear on the screen are sorted.

Syntax:

SELECT <column name> [,<column name>, ….] FROM <table name>
[WHERE <condition>]
[ORDER BY <column name> [, <column name>…]];

Example:

(Sorting on the single column) SELECT * FROM EMPL ORDER BY ENAME;
Example :

(Sorting on Multiple columns) SELECT * FROM EMPL
ORDER BY ENAME, JOB;

Adding a column:

The ALTER TABLE command is used to change definitions of existing tables . It can add columns, delete columns or change their size.

Syntax:

ALTER TABLE <table name>
ADD (<column name> <data type with size> <constraints>);

Example:

To add age column in the student table.

ALTER TABLE Students
ADD ( age NUMBER (2) CHECK (age > 5));

Modify a column :

Syntax :

ALTER TABLE <table name>
MODIFY ( column name newdatatype (newsize));

Example:

ALTER TABLE Students MODIFY ( age NUMBER (1));

Changing a Column Name:

ALTER TABLE <table name>
CHANGE <old_column_name> <new_column_name> <column definition> ;

Example:

ALTER TABLE Students
CHANGE age s_age NUMBER (2)

• Removing table components
• To remove primary key constraints
ALTER TABLE Students
DROP primary key;

• To remove the column from the table
ALTER TABLE Students
DROP COLUMN age ;

• Drop a table from the database:
DROP TABLE <table name> ;