Module 2

Data : Data are raw or known facts, i.e. the data that has not been processed.Information : Processed data is called information.

DBMSMySQL is used for Relational Database Management System (RDBMS). Database is a collection of data and management system means Creating file, adding records, deleting records, editing records, sorting records, query records, display records, etc. The DBMS also enforces necessary access restrictions and security measures in order to protect the database.Various types of control systems within the DBMS make sure that the database continues to function properly. They include Integrity system, Security system, Concurrency control system and Recovery control system.

Advantages of DBMSRedundancy is controlledUnauthorized access is restrictedProvides multiple user interfacesEnforces integrity constraintsProvides backup and recovery of data.

Limitations of DBMSUses extra computer time and resourcesIt is expensive to install new systems.Requires well qualified and expert personnel to operate the system.If it is not designed for concurrent access to many users it will be of little use.Authority of ownership rights over the data have to be fixed, so as to determine who is eligible for using the data.

Database : A database is a collection of data that is related in some way. A list of students enrolled for the commerce stream, the list of names, addresses and telephone numbers , etc. are all examples of databases. A database can be compared to a filing cabinet which contains many files. The files are tables containing data. The size of a database can be vary large and can have varying levels of complexity. While today most databases are maintained on computers, some of them may be manually maintained. Generally a database contains tables.

Database : Collection of related tables makes a database i.e. a database is a set of related information.

Table (File): Collection of related rows makes a table i.e. all rows together is called as a table. Table contains rows and columns.

Row (Record): Collection of related columns makes a row (record) i.e. name, city, DOB, telephone_no of a person together will make a row.

Column (Field): Particular information is called a column i.e. there is one column for name, one column for city and so on.

Doctor

Patient

Doctor

PatientPatient Patient

Doctor DoctorDoctor

Patient

Doctor DoctorDoctor

PatientPatient Patient

Relationship in DatabaseTo make it easy for the end user who uses the database for various purposes, the following relationships are used.One-to-One RelationshipIn one-to-one relationship there is one parent record is associated with only one child record. Example one doctor in a hospital will take care of one patient.

One-to-Many RelationshipIn one-to-many relationship there is one parent record is associated with many child records. Example one doctor in the hospital will take care of many patients.

Many-to-One RelationshipIn many-to-one relationship there is two or more parent records are associated with one child record. Example many doctors will be attending to a single patient.

Many-to-Many RelationshipIn many-to-many relationship there is two or more parent records are associated with two or more child records. Example two or more doctors will be attending to or more patients.

Database StructureThe logical arrangement/organisation of records and their relationship is called as Database structure. The traditional database structures areHierarchical Database StructureNetwork Database StructureRelational Database Model

Mumbai University

Science

Physics B Com EnglishBMSB SC IT

ArtsCommerce

Economics

Teacher 1

Science

Physics B Com EnglishBMSB SC IT

ArtsCommerce

Economics

Teacher 2 Teacher 3 Teacher 4

Hierarchical Database Structure / Hierarchical Model In this model the different records are related through inverted tree like structure. A parent record can

have many child records, but a child record can have only one parent record. It uses one-to-one and one-to-many relationships. In a hierarchical database, records contain groups of parent/child relationships. This model has been widely used,it is not suitable for many application areas because its structure is

inflexible and is not suitable for complex relationships.

Advantages The structure is simple to construct and operate As the batch operations like payroll are done at maximum speed they permit day to day structured

operations rapidlyDisadvantages

Many-to-many relationships cannot be handled in this model Its structure is quite rigid and hence adding a field is very complicated often resulting in making a new

database. Deleting a parent record is complex as the entire branch of child records associated with it has to be first

deleted. Ad hoc queries are not supported.

Network Model In this model a parent record can have many child records and a child record can have many parent

records. It uses many-to-many relationships. The records are physically linked through linked lists. It supports more complex queries and relationships. It is very complex i.e. while accessing the database the users have to be familiar with the structure and

keep a track of where they have reached and how they got there. If any change is to be made to the structure, it would affect the application that interacts with the database.

Advantages It has a higher level of flexibility as compared to Hierarchical Database. They are ideal for handling many to many relationships. Complex data relationships can be represented more effectively

Disadvantages The database structure is difficult to change. Operation and maintenance of this structure is quite complicated. Relationships cannot be added to the existing database easily.

RDBMS / Relational database model In relational database management system there are no physical links. All the data is kept in tables which contain rows and columns. The data in two tables are linked through the columns. Relational database have become a predominant choice for the storage of information in new databases

used for financial records, manufacturing and logistical information, personnel data and much more. As computer processing power has increased, the inefficiencies of relational databases, which made them

impractical in earlier times, have been outweighed by their ease of use. The relational model is most widely implemented model in modern business systems.

Primary key: A column in the table that uniquely identifies each row in that table is called the primary key. Primary key may be selected for convenience. A primary key can be combination of columns. In a table there can be only one primary key.Candidate Key: In a table there could be more than one column that uniquely identifies a row in the table. Then such columns are called candidate keys. From among the candidate keys, we can choose one to be the primary key, or a combination of them to be the primary key.Foreign (Referential) key: It is a column in one table, whose value matches with the primary key in another table. A foreign key establishes a relationship, or constraint, between two tables. A table can have more than one foreign key, if it is related to another table.

SQL statementsThe SQL statement can be broadly categorized into the following:

Data Definition Language (DDL) This language is used by the designers and programmers of the database to indicate the content and the

structure of the database. It is used to indicate the physical structure of the database i.e. field names, their types etc. and also the

record relationships. Examples CREATE, ALTER and DROP statements.

Data Manipulation Language (DML) This language is used primarily for data manipulation and processing. It involves retrieving the data, arranging the data, deleting the data and displaying the data etc. A user queries the database and receives the required reports. Example SELECT, UPDATE, INSERT and DELETE statements.

Data Control Language (DCL) This is used for controlling the data and access to the database. It is used to address security issues and restrict the access to the database. Example GRANT, REVOKE statements.

Transaction Control Statements These statements are used to handle transactions. They are essentially a group of DML statements executed together as one unit. They are used to begin, end and rollback transactions. Example COMMIT and ROLLBACK statements.

Rules for naming Database/Table/Column It can be at the most 64 characters long. It should not be a key word. (such as select, show, table, from, etc.) It should be unique. It should always begin with alphabet. Only underscore( _ ) is the special character use for naming. For naming database, table or column we can use alphabets, digits and underscore.

Valid database/table/column names Employeename Empname Emp_name Emp_name1 Emp1_name, etc.

Invalid database/table/column names _empname 1empname &employeename Emp,name Emp_name%1, etc.

Data typesData type is the type or fromat in which the data are stored. For each column in a table we have to select one data type i.e. data type is a constraint that is placed on a specific column to limit the values that can be stored in that column. The different data type in MySQL are:

Character Data type / Text Data type / String Data typeThe character data is use to store a character or set of character is called as string. It is a data type that cannot be used for mathematical calculations and manipulation. Example Names, Address, Designation, Qualification, etc. Data can be stored either as fixed-length or variable-length string. Char data type is use to store fixed length string. Example char(10) has the capacity to store 10 characters. Column declaration using char Gender char(1)Varchar data type is use to store variable length string. Example varchar(20) has the capacity to store 20 characters. Column declaration using char Name varchar(30)Values in char and varchar columns are sorted and compared in case-insensitive fashion.

Numeric Data Types (Integer and Real)Numeric data means numbers and they can be positive or negative or zero; with decimal point or without decimal point. If the data is without decimal point then it is integer and if data is with decimal point then it is real (float).

Integer data type can be used as Std_id Integer or Std_id Integer(12) by default it store 11 digits but it is possible to specify capacity as it is specified in second declaration.Decimal data type can be used as Decimal(10,2) means total 10 digits, out of which 2 digits are after decimal point and remaining 8 digits are before decimal point.Unsigned argument can be used with only numeric data columns. Unsigned attribute means no sign i.e. your value can not be negative.

Boolean Data Types (True or False)Boolean data are represented by the constants true and false which evaluate to 1 and 0, respectively.

Temporal Data typesTemporal data means Date and/or time. The different data type in the temporal data is date, year, time, datetime and timestamp. In datetime the allowable values are from year 1000 to year 9999 where as in timestamp allowable values are from year 1970 to year 2069.If century is not given then century is assumed from 1970 to 2069 i.e. if you give year as 69 then it is taken as 2069 whereas if you give year as 70 then it is taken as 1970.

Constraint / Column Specification / AttributesNot Null : This attribute can be used with any column. If column is said to be Not Null then we must give value for that column while inserting the data. By default the column is said to be Null.

Default value: This attribute is use to specify a default value. This default value will be used for a column if you do not give any value for the column. It cannot be used with the auto_increment and primary key constraint or attribute. It is useful if most of the data is having same value.

Auto_increment: This attribute can be used with only numeric column. This attribute can be used only once in a table. The column which is using Auto_increment attribute must be a Primary key column. Default value attribute cannot be used with Auto_increment. This attribute will create automa tic number for the column if we do not provide value for the column. By default it starts with 1.

Primary Key: In relational database every table has one column whose values uniquely identify each row in the table. This column is called as primary key of the table. This attribute can be used only once in a table. The primary key has a different unique value for each row in a table. Primary key will help you to display records in ascending order. Defining primary key is better but not compulsory.

Unique: Each table have only one primary key. Primary key column must have unique values. If we require any other column with unique values then we can use this attribute with that column. Unique will make sure that values in that particular column are unique. If we try to input duplicate value in unique column then MySQL will give you an error.

Foreign key: A column in one table whose value matches the primary key in some other table is called a foreign key. The value in the foreign key column should have a matching value in the primary key column. A primary key and a foreign key together create a parent/child relationship between the tables that contains them. A table can contain more than one foreign key if it is related to more than one table.

Start MySQL in windowsClick on Start All Programs MySQL MySQL Command Line Client the MySQL command prompt will get open, enter the password and continue with the program.

Quit/Exit CommandThis command is used to quit/exit out of MySQL. This command will close your database in MySQL and return you to Windows. To exit MySQL type a command QUIT; or EXIT; at mysql command prompt. Example mysql> Exit;

Database Related CommandsMySQL is not a case sensitive i.e. SHOW, show, Show are same.

Show databasesThis statement is used to list all or particular database names in the alphabetical order from MySQL Server.mysql> show databases;above command will display the name of all the databases available in MySQL

Creating a DatabaseCreate database is a statement used to create a new database with a given name, on MySQL server. Only one database can be created at a time.Syntax:mysql> create database database_name; Example:mysql> create database college; above command will create a new database with name as college.If we try to create a database with the name which is already existing then will get an error. We cannot have two databases with the same name.

Using a databaseUse statement is used to select and open the existing database from MySQL server. We have to give name of an existing database with this command. If database does not exist then MySQL will give us an error. Once this command is executed properly, it will display a message ‘Database Changed’. Semicolon is optional with this command.Syntaxmysql> use database_name;Example :mysql> use college;above command will open the database college.Without selecting a database, no table can be created or used.

Dropping / Deleting a databaseDrop database statement is used to permanently delete a database along with all tables in the database from MySQL server. Syntax:mysql> drop database database_name;Example:mysql> drop database college;Above command will delete the database college.

Table Related Commands Show tables

This statement is used to list all or particular table names, in the alphabetical order, from current database or from given database.mysql> use college;Database changedmysql> show tables;the above command will display the list of all the tables available in database college.

Creating a TableCreate table statement is used to create a new table with a given name, in the current database. At a time we can create only one table.Syntax:Create table Table_name(Column_name1 datatype attribute / constraint,Column_name2 datatype attribute / constraint,Column_name3 datatype attribute / constraint,

::

Column_namen datatype attribute / constraint);

ExampleCreate table employee(emp_no, emp_first_name, D_O_B, Salary,dept) with proper constraint.Create table department(dept_id,dept_name,location,capacity,budget) with proper constraint

Create table Department(dept_id int unsigned auto_increment primary key,dept_name varchar(20) not null,location varchar(20) not null,capacity int not null,budget decimal(15,2) not null);the above statement will create a department table with constraint.

Create table Employee(emp_no int unsigned auto_increment primary key,emp_first_name varchar(15) not null,D_O_B date not null,Salary decimal(10,2) not null,dept int not null,Constraint con_fk Foreign Key(dept) References Department(dept_id));the above statement will create an employee table with constraint.

Create table employee(emp_no, emp_first_name, D_O_B, Salary) without constraint.Create table Employee (emp_no int, emp_first_name varchar(15), D_O_B date, Salary decimal(10,2), dept int);the above statement will create an employee table without constraint.we can interchange between auto_increment and primary key but when unsigned is used, it’s must be used after the data type.

Describe / Desc / Show ColumnDescribe or desc or show column statement is used to display column names, their type and their attribute, (structure) from a given table in column form. All the three statement i.e. Describe, Desc and Show Columns from are the similar.Syntax:mysql> Describe tablename; OR Desc tablename; OR Show Columns from tablename;Examplemysql> Describe employee; ORmysql> Desc employee; ORmysql> Show columns from employee; the above statement will display field names, their type, attribute etc. of employee table.

Show Create TableShow create table statement is used to display the column names, their type, their attributes (if any) in paragraph form which is similar to Create Table statement.Syntax:mysql> show create table tablename;Example:mysql> show create table Employee;

Alter TableAlter table statement is used to add new column to an existing table, to change the type or size or attribute, to change the column name and to delete a column. This command has 4 options ADD, MODIFY, CHANGE & DROP.Syntax:Alter table table_name ADD|MODIFY|CHANGE|DROP column_name datatype attribute;

ADD: This option is used to add a new column to a table. A column can be added anywhere in a table i.e. at the beginning (use FIRST at the end of the command) or at the end (by default) or somewhere in the middle (use AFTER column_name at the end of the command).Syntax:Alter table table_name ADD column_name datatype attribute after column_name|First;

Example:Alter table Employee ADD designation varchar(20) not null ;The above command will add a new column designation at the end of table employee with not null attribute.Alter table Employee ADD designation varchar(20) after D_O_B;The above command will add a new column designation after the column D_O_B in employee table.

MODIFY: This option is used to make changes in the data type or size or attributes of a column. With this option you have to write existing column name, required type and required attributes.Syntax:Alter table table_name MODIFY column_name datatype attribute;

Example:Alter table Employee MODIFY salary decimal(9,2);The above code will change the salary column to decimal(9,2).Alter table Employee MODIFY salary decimal(9,2) default 10000;The above code will change the salary column to decimal(9,2) with default value 10000.

CHANGE: This option is used to change the column name, with this option you have to give current column name, new column name, its type and attributes if any.Syntax: Alter table Employee CHANGE old_column_name new_column_name datatype attribute;ExampleAlter table Employee CHANGE D_O_B Birth_Date Date not null;The above command will change the name of column D_O_B as Birth_date with date datatype and not null attribute.

DROP: This option is used to remove any existing column from a table. A column is removed along with the data in the column. Only one column will be deleted at a time.Syntax: Alter table Employee DROP column_name;ExampleAlter table Employee DROP dept;The above command will remove a dept column from the table employee.

Rename Table:Rename table statement is used to change name of one or more existing tables from current database.Syntax:Rename table old_table_name TO new_table_name, old_table_name TO new_table_name,…………so on;Example:Rename table Empoyee TO Emp;The above command will change the name of the table from Employee to emp.

Table can be renamed using alter table command also.Syntax:Alter table old_table_name RENAME to new_table_name;Example:Alter table Employee RENAME to Emp;The above command will change the name of the table from Employee to emp using alter table command.

Drop Table:Drop table statement is used to delete existing table/s from current database. Once the table is deleted it cannot be rollback. This statement will delete both data and structure of the table.Syntax:Drop table table_name;Example:Drop table Employee;

DML statement / Row (Record) Related Commands

InsertInsert statement is used to add new rows to an existing table. Adding rows to a table is also called as populating table. We can add one or more rows (records) with one command.Syntax:Insert into table_name(Column_name1, Column_name2, . … .,Column_nameN)values(Value1, Value1,. . . . . . . ., ValueN);in the above syntax the column name is optional.Example:To insert value in all the column of tableInsert into Employee (emp_no ,emp_first_name,D_O_B ,Salary,dept)values(01,’Ajay’,’1992-10-15’,25000,2);the above statement will insert a record in Employee table where all the column name and related values are used.Insert into Employee values(01,’Ajay’,’1992-10-15’,25000,2);the above statement will insert a record in Employee table where all the related values are entered in the sequence of column available in the table.

To insert value in some columns of tableInsert into Employee (emp_no ,emp_first_name,D_O_B )values(01,’Ajay’,’1992-10-15’);the above statement will insert a record in only emp_no, emp_first_name and D_O_B column of Employee table and remaining column will contain null value.

To see all the records available in Employee table we have to write the statement as:mysql> Select * from Employee;

Update Update statement is used to make changes in the data which is already entered into a table. If you have made a mistake while inserting the data then you can use this statement to make correction. This statement can be used to change or increase or decrease the data values.Syntax: -Update table_nameset column_name = new_valuewhere condition;

Example:Update EmployeeSet D_O_B = ‘1992-06-26’Where Emp_no = 10;The above statement will update the employee table and change the D_O_B to 26th june 1992 for the employee no. 10;

Update EmployeeSet Salary = Salary + Salary * 0.1;The above statement will increment the salary of the entire employee by 10%.

Update EmployeeSet Salary = Salary - Salary * 0.1;The above statement will decrement the salary of the entire employee by 10%.

Update EmployeeSet D_O_B = ‘1992-06-26’ , Salary = 25000 Where Emp_no = 10;The above statement will update the Employee table and change D_O_B to 26th June, 1992 and Salary to 25000 of employee no. 10.

DeleteDelete statement is used to delete row/s (record/s) from a given table. With ‘Where’ clause it will delete only those records which are satisfying a given condition and without ‘Where’ clause it will delete all the records from a table. This statement will not delete a structure of a table.

Syntax: -Delete From table_name Where column_name = condition;

Example: -DeleteFrom employee;The above statement will delete all the records/rows from the employee table.

DeleteFrom employeeWhere emp_no = 7;The above statement will delete the record/row of employee no. 07 from the employee table.

Select statementSelect statement is used to display rows (few or all) from a given table, from the current database. By default the display will be in the ascending order of primary key. This statement is also called as query statement.Syntax: - SELECT *|{[DISTINCT] column|expression [alias],...} FROM source WHERE condition(s) GROUP BY expression | Column_name HAVING condition ORDER BY expression | Column_nameLIMIT value

SELECT is a list of one or more columns* selects all columnsDISTINCT Remove duplicates rowscolumn|expression selects the named column or the expressionAlias gives selected columns different headingsFROM table specifies the table containing the columnsWHERE condition(s) the condition in where clause is use to refine the output of the

queryGROUP BY It is use to group the value of a column in the table.HAVING condition the condition in having is always used with group by clause to

refine the output of group by clauseORDER BY this clause is use to arrange the output of the query in ascending or

descending order on the basis of expression | Column_nameLIMIT This clause control number of rows that are displayed.

Example: - Selecting All ColumnsSelect * from Employee;The above command will display all the records and columns of employee table. By default it will display all the rows in the ascending order of primary key.

Choosing Specific ColumnsSelect emp_first_name, salary from Employee;The above command will display all the records of column emp_first_name and Salary of employee table.

Changing Column Name in OutputSelect emp_first_name AS Name, D_O_B AS Birth_Date from Employee;The above command will display all the records with column name emp_first_name as Name and D_O_B as Birth_date of employee table.

Calculating ColumnsWe can calculate the column/s to display. We can calculate the column by using mathematical operators. The calculated column is only displayed on the screen, it is not added to the table, and the columns in the table remain unaffected.Select emp_first_name, salary*0.1 AS Commission from Employee;The above command will display all the records of column emp_first_name and calculated commission (10% of Salary) of employee table.

Select emp_first_name, salary*12 AS Annual_Salary from Employee;The above command will display all the records of column emp_first_name and calculate annual salary of each employee from employee table.

Built-In FunctionsThe various built-in functions are categorized in to 3 different categories:

String Functions Numeric Functions Date a& Time Functions

From clause is optional for all the built-in functions.

String Functions:LCASE/LOWERThe LCASE/LOWER (String) function is use to change the string in lowercase.Syntax:Select lower(string)/lcase(string) from Table_name;Example:Select lower(‘DATABASE’);The output of the above query is database.Select lower(emp_first_name) from Employee;The above query will display name of entire employee in lowercase.

UCASE/UPPERThe UCASE/UPPER (String) function is use to change the string in uppercase.Syntax:Select upper(string)/ucase(string) from Table_name;Example:Select upper(‘database’);The output of the above query is DATABASE.Select upper(emp_first_name) from Employee;The above query will display name of entire employee in uppercase.

LEFTLEFT(string,n) function will returns the leftmost ‘n’ characters from the string. If ‘n’ is zero or negative then empty string is returned.Syntax:Select LEFT(String,n) from Table_name;Example:Select LEFT(‘DATABASE’,4);The output of the above query is DATA.Select LEFT(‘DATABASE’,5);The output of the above query is DATAB.Select LEFT(emp_first_name,3) from Employee;The above query will display first 3 alphabet of entire employee name.

RIGHTRIGHT(string,n) function will returns the rightmost ‘n’ characters from the string. If ‘n’ is zero or negative then empty string is returned.Syntax:Select RIGHT(String,n) from Table_name;

Example:Select RIGHT(‘DATABASE’,4);The output of the above query is BASE.Select RIGHT(‘DATABASE’,5);The output of the above query is ABASE.Select RIGHT(emp_first_name,3) from Employee;The above query will display last 3 alphabet of entire employee name.

SUBSTRING/MIDSUBSTRING/MID(string,m[,n]) function returns a substring of ‘n’ characters long from given string, starting at position ‘m’. if ‘n’ is not given then all the characters starting from ‘m‘ position is returned. If ‘m’ is zero then empty string is returned. If ‘m’ is negative then position is taken from the end of the string. If ‘n’ is zero or negative then empty string is returned.Syntax:Select substring(string,m,n) from table_name; ORSelect mid(string,m,n) from table_name;Example:Select substring(‘Computer’,4,3) ; OR Select mid(‘Computer’,4,3);The above query will extract the 3 character starting from the 4th position. The output of the above query is put.Select substring(emp_first_name,2,5) from Employee; ORSelect mid(epm_first_name,2,5) from Employee;The above query will extract the 5 character from employee name starting from the 2nd position.

LTRIMLTRIM(string) function returns the string with removing leading(starting) spaces.Syntax:Select ltrim(string) from table_name;Example:Select ltrim(‘ abc’);The above query will remove the leading blank spaces from the string. The output of the above query is abc.Select ltrim(emp_first_name) from Employee;The above query will remove the leading blank spaces from entire name in the employee table.

RTRIMRTRIM(string) function returns the string with removing trailing(ending) spaces.Syntax:Select rtrim(string) from table_name;Example:Select rtrim(‘abc ’);The above query will remove the trailing blank spaces from the string. The output of the above query is abc.Select rtrim(emp_first_name) from Employee;The above query will remove the trailing blank spaces from entire name in the employee table.

TRIM

TRIM([[BOTH|LEADING|TRAILING][remstr] From] string) function returns the string with removing all the leading and trailing blank spaces.

Syntax:Select TRIM([[BOTH|LEADING|TRAILING][remstr] From] string) from table_name;Example:Select trim(‘ xyz ‘);The above query will remove leading as well as trailing blank spaces from the string. The output of the above query is xyz.Select trim(emp_first_name) from Employee;The above query will remove leading and trailing blank spaces from entire name in the employee table.

LENGTHLENGTH(string) function returns the length of a string i.e. number of characters within a string.Syntax:Select length(string) from table_name;Example:Select length(‘Hello MySQL’);The above command will count and display the number of character in the string is 11.Select length(emp_first_name) from Employee;The above command will display the length of entire row of emp_first_name column from Employee table.

REVERSEREVERSE(string) function returns the string in the reverse order of the characters.Syntax:Select reverse(string) from table_name;Example:Select reverse(‘Hello MySQL’);The above command will display the character in reverse order as LQSyM olleH.Select length(emp_first_name) from Employee;The above command will display the names in reverse order of characters of emp_first_name column from Employee table.

CONCATCONCAT(string1, string2) function returns a string, that result from concatenating the arguments given in the function. It returns Null if any argument is Null. We can have more than 2 arguments. A numeric argument is converted to the equivalent string form.Syntax:Select concat(string1, string2,……..) from table_name;Example:Select concat(‘Hello’,’My’,’SQL’);The above command will display the output as HelloMySQL.Select concat(emp_first_name, ‘ is ’, designation) from Employee;The above command will display the name and designation of entire Employee as ‘Jay is Manager’.

Date Function:Now()This command will returns the current system date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format depending on whether this function is used in a string or numeric format.Syntax:MySQL> Select Now();Example:MySQL> Select Now();The above command will give the output as ‘2001-10-11 16:19:46’.

Time(Expression)This command will extract the date from the datetime expressionSyntax:Select Time(Expression);Example:Select Time(‘2001-10-11 16:19:46’);The above command will return the output as 16:19:46.

Date(Expression)Thie command will returns the date part of the date or datetime expression.Syntax:Select Date(Expression);Example:MySql> Select Date(‘2001-10-11 16:19:46’);The above command will return the output as 2001-10-11.

CurDate() or CurrentDate()This command will return the current system date as a value in ‘YYYY-MM-DD’ or YYYYMMDD depending on whether the function is used in a string or numeric context.Syntax:Select curdate();Example:Select curdate();The above command will give the output as 2001-10-11.

Day(Date)This command will return the day of the month for the date, as number from 1 to 31.Syntax:Select Day(Date);Example:Select Day(‘2001-10-11’);The above command will return the output as 11.

Month(Date)This command will return the month for the date, as number from 1 to 12.Syntax:Select Month(Date);

Example:Select Month(‘2001-10-11’);The above command will return the output as 10.

Year(Date)This command will return the output for the date, as number in the range 1000 to 9999.Syntax:Select Year(Date);Example:Select Year(‘2001-10-11’);The above command will return the output as 2001

Dayname(Date)This command will return the name of the weekday for the date.Syntax:Select Dayname(Date);Example:Select Dayname(‘2001-10-11’);The above command will return the output as Thursday.

Monthname(date)This command will return the name of the month for the date.Syntax:Select Monthname(Date);Example:Select Monthname(‘2001-10-11’);The above command will return the output as October.

Numeric Function

Abs(x)This command will return the absolute value of X, i.e. value without its sign.Syntax:Select Abs(x);Here x is any number positive or negative.Example:Select Abs(-1254);The above command will return the output as 1254Select Abs(1254);The above command will return the output as 1254

Pow(x,y)

This command will return the value of xy

Syntax:Select Pow(x,y);Here x and y both are number, but x is a number whose power is y.

Example:Select Pow(2,3);The above command will return the output as 8.

Mod(x,y)This command will returns the remainder of the division of x by y. The answer will take the sign of x.Syntax:Select Mod(x,y);Here x and y are the numbers, but x is a dividend and y is the divisor i.e. x/y.Example:Select Mod(7,4);The above command will return the output as 3.Select Mod(-7,4);The above command will return the output as -3.Select Mod(7,- 4);The above command will return the output as 3.

Round(n,d)This command will returns the number n rounded to d decimals. If the d is omitted or 0 it returns nearest integer. If d is -1 then it rounds n to the nearest 10.Syntax:Select Round(n,d);Here n is the actual number and d is the number of digits will display after decimal point.Example:Select Round(2.358);The above command will return the output as 2Select Round(15.325648,4);The above command will return the output as 15.3256Select Round(792,-1);The above command will return the output as 790.

Sqrt(x)This command will return the positive square root of x.Syntax:Select sqrt(number/x);Here number or x is any integer number whose square root is to be calculated.Example:Select sqrt(25);The above command will return the output as 5;Select sqrt(-144);The above command will return the output as 12.

WHERE Clause (to select rows to be displayed)WHERE clause is used along with the SELECT statement to choose the rows that are to be displayed. The WHERE clause is followed by a condition. Only those rows which satisfy that condition are displayed.Syntax:Select *

From TablenameWhere Condition;Example:Select * from EmployeeWhere salary>10000;

Relational OperatorsThe various relational operators used with where clause is as follows:Operator Meaning= Is equal to< > Or != Not equal to< Is less than> Is greater than>= Is greater than or equal to<= Is less than or equal toIs NULL Is equal to NULL< = > Null safe equal toExpr BETWEEN lowest and highest expr>=lowest and expr<=highestExpr IN(value1,value2,……….) Expr=value1or expr=value2………..Expr LIKE pattern expr matching that pattern

Example:Select * from Employee where salary>10000;The above command will display the details of the employee who were earning the salary greater than 10000.

Select * from Employee where dept is null;The above command will display the details of the employee whose department is not assigned.

Logical Operators:

Logical operators are used when we want to write complex queries. Complex queries are formed when we combine two or more conditions with the use of Logical operators.Logical Operators InterpretationAND True when both the conditions before and after it are

true.OR True if any one of the conditions before and after it is

true.NOT (!) True if the condition written after it is false and vice

versa.

Example:Select * from Employee where emp_id = 2 AND City=Mumbai;The above command will display the employee details whose employee ID is 2 and he stay in the City ‘Mumbai’. While using And Operator both the condition included in the query must satisfy.

Select * from Employee where salary >= 25000 OR City=Mumbai;The above command will display the employee details whose salary is greater than 25000 Or he stay in the City ‘Mumbai’. While using OR Operator if any of the condition in the query is true the output is displayed.

Select * from Employee where dept != 5;The above command will display the employee details that were not working in the Department 5.

IN OperatorThe IN operator is used to select rows, for a given column having values matching with a given set of values i.e. the in operator will check the multiple values included into a single bracket( ) separated by comma( , ).Example:Select * from EmployeeWhere Salary IN (10000, 20000, 30000);The above query will display the employee details whose salary is either 10000 OR 20000 OR 30000.Select * from EmployeeWhere City IN (‘Mumbai’, ’Pune’, ’Indore’);The above query will display the employee details whose stay in city either Mumbai OR Pune OR Indore.

Between..And OperatorThe Between..And operator is used to select the rows, for a given column having values matching with a given range of values.Syntax:Select *from TablenameWhere column_name Between Value and Value;Example:Select * from employee where Salary Between 10000 and 20000;The above statement will display the employee details that have salary between 10000 and 20000.

Like Operator (Pattern Matching/Searching)The LIKE operator can be used to match string containing wild card characters % and _, where ( % ) represents one or more characters and ( _ ) represents a single character. The following table gives various formats in which we can use these characters and their meaning.Pattern Represents‘B%’ The string value starting with B or b.‘%b’ The string value ending with b or B‘%b%’ The string value which contains B or b in any position‘_ _ _ _ _’ It contains any 5 characters‘_a_’ Has 3 characters and the second character is a or A‘_ _c%’ Third character is c or C‘%c_’ The second last character is c or C‘a%c’ First character is a or A and the last character is c or C‘%1999’ All the dates contains year 1999‘%-10-%’ All the dates of month October.

Syntax:Select * from Table_name where Column_name LIKE pattern;Example:Select * from Employee where name LIKE ‘A%’;The above statement will return the employee details that names begin with letter ‘A’ or ‘a’.