MySQL

1
MySQL
2
Introduction

• MySQL is a DBMS (DataBase Management System) i.e.
  it is a databse software.
• MySQL is 5.0 can be downloaded from the website
  http//dev.mysql.com/downloads.
• MySQL runs on all major platforms such as
  Windows, Linux, Solaris, Mac OSX etc.

3

• What is SQL?
• Full form of SQL is Structured Query Language.
  SQL is a language designed for communicating with
  the databases. It is a basic tool for accessing
  data in a relational database.

4

• SQL is made up of very few words. It provides a
  simple and efficient way to read and write the
  data from a database.
• SQL is not a proprietary language of any DBMS.
  Learning this one language enables us to interact
  with any DBMS.
• SQL is easy to learn. With the help of SQL we can
  perform very complex database operations.

5

• MySQL is client-server-based databases. Client
  server applications are split into two parts. The
  server portion is a piece of software that is
  responsible for data access and manipulation.
• Client is the piece of software with which the
  user interacts.
• To use MySQL we need a client, an application
  that you use to interact with MySQL(to give
  commands to be executed)

6

• There are three tools used as client application
• MySQL command line utility
• MySQL Query Browser
• MySQL Administrator.
• ( MySQL command line utility is a part of core
  MySQL installation, whereas MySQL Query Browser
  and MySQL Administrator come under GUI tools of
  MySQL)

7

• Database
• Database is collection of data stored in some
  organized fashion. We can consider it to be
  similar to a filing cabinet.
• We have files in the filing cabinet and related
  data is stored in specific files. A file is
  called as a table in DBMS.
• Hence a database is container of number of tables.

8

• Relational Database
• When information can be presented in the
• form of tables in a database, regardless of
• how it is stored, the arrangement of data is
• called as relational database. The term
• relational database comes from the fact that
• data columns are related to each other by
• virtue of their values.

9

• Every table in a database has a unique name that
  identifies it. That means no other table in that
  database can have the same name.
• The database name and the table name together
  make the table name unique. This means one can
  not use the same table name in the same database
  but one can reuse the table names in different
  databases.

10

• Tables are made up of columns. A column contains
  a particular piece of information. For e.g. in a
  table called CUST we can have a column containing
  customer name, customer address etc.
• Each column in a table has an associated
  datatype. A datatype defines what type of data a
  column can contain.

11

• We will mainly use the following datatypes
• 1. Numeric Datatype Numeric datatypes store
  numbers. We will following two datatypes to
  store numbers.
• INT This datatype stores integer values and
  supports numbers from -2147483648 to 2147483647.
• DECIMAL(or DEC) It stores floating point values
  with varying levels of precision.

12

• SMALLINT Stores integer value,
• supports numbers from -32768 to 32767
• (or 0 to 65535 if UNSIGNED)
• BIGINT Stores integer value supports numbers
  from -9223372036854775808 to
• 9223372036854775807
• (or 0 to 18446744073709551615 if UNSIGNED)

13

• TINYINT Stores integer value from -128 to 127
  (or 0 to 255 if unsigned).
• BOOLEAN ( or BOOL) This data type is used to
  store value 0 or 1. BOOLEAN
• data type is converted into TINYINT.

14

• DECIMAL(or DEC) It stores floating point
  values with varying levels of precision. It can
  store maximum of 65 digits and supports up to 30
  decimals.
• FLOAT Stores small four byte single-precision
  floating point values. Precision of floating
  point number is between 0 to 24.
• DOUBLE Stores normal eight byte
  double-precision floating point values. Precision
  is between 25 to 53.

15

• 2. Date and Time datatypes
• DATE Date from 1000-01-01 to 9999-12-31 in the
  format YYYY-MM-DD.
• TIME Time from -8385959 to 8385959 in the
  format HHMMSS
• DATETIME A combination of DATE and TIME in the
  format YYYY-MM-DD HHMMSS. This type does not
  have automatic update features as TIMESTAMP.

16

• TIMESTAMP Equivalent to DATETIME but with a
  smaller range. Range is 1970-01-01 000000 to
  sometime in 2037. TIMESTAMP is automatically
  updated to current date and time when a new row
  is inserted or updated.
• YEAR A 2 or 4 digit year 2 digit year supports a
  range of 70(1970) to 69(2069), 4 digit year
  support a range of 1901 to 2155.

17

• 3. String datatypes
• CHAR Fixed length string from 1 to 255
  characters long. Its size must be specified at
  the time of creating a table. If the length is
  not specified then MySQL assumes it to be 1.
• VARCHAR Same as CHAR but stores text of variable
  size length. Maximum value of width is 65,535
  characters.

18

• TEXT Variable length text with maximum size of
  64K
• MEDIUMTEXT Same as TEXT but with maximum size of
  16K.
• LONGTEXT Same as TEXT but with a maximum size of
  4GB.

19

• Fixed length string are datatypes that are
  defined to accept a fixed number of characters.
  It will not accept more than the specified number
  of characters. Fixed length string i.e. CHAR
  can contain 1 to 255 characters.
• MySQL can sort and manipulate fixed length
  datatypes more quickly than variable length.

20

• Data in a table is stored in rows. Every row in a
  table should have a column(or columns) that
  uniquely identifies it. This column is called as
  the primary key.
• For e.g. a table called CUST may have cust_id
  (unique numeric customer ID) as the primary key.

21

• Any column can be defined as the primary key as
  long as it satisfies the following conditions.
• No two rows can have same primary key value.
• Every row must have a primary key value.
• (Primary key columns will not allow NULL
  values.)

22

• Generally we have one primary key column for a
  table. But we may use multiple columns together
  as primary key.
• The rules defined for a primary key column
• must apply to columns that make up the
• primary key

23

• Foreign Key A column in one table that contains
  primary key values from another table is called
  as the foreign key. It defines relationship
  between two tables.
• For e.g. In BILL table CUST_ID can be used as the
  foreign key, CUST_ID is primary key in CUST
  table.

24

• We need a default database to work with. We can
  create a new database with help of command
• CREATE DATABASE
• Or
• CREATE SCHEMA
• The keywords in the commands are not case
  sensitive.
• For e.g. CREATE DATABASE SHOP

25

• List of databases can be seen with the help of
  command
• SHOW DATABASES
• List of tables within a database can be
• obtained with the help of statement
• SHOW TABLES FROM SHOP
• To use a database called SHOP we give the command
• USE SHOP

26

• To create a table called CUST following set of
  SQL statements is used.
• CREATE TABLE CUST
• (CUST_ID INT NOT NULL,
• NAME CHAR(20) NOT NULL,
• CITY CHAR(20) NOT NULL,
• PHONE CHAR(20) NULL,
• PRIMARY KEY (CUST_ID))

27

• To create a table called BILL following set of
  SQL statements is used.
• CREATE TABLE BILL
• (CUST_ID INT NOT NULL,
• BILLNO INT NOT NULL AUTO_INCREMENT,
• AMT DECIMAL(10,2) NOT NULL,
• PRIMARY KEY(BILLNO))

28

• Only one auto_increment column will be allowed
  per table and it must be made primary key.
• If we want to use a specific value for
  auto_increment column then it can be entered
  using INSERT statement. This value will be
  accepted as long as it is unique. Subsequent
  incrementing will start using the value manually
  inserted.

29

• The column name can not contain any comma or
  blank spaces or any other special symbol (except
  and _).
• Column name can be up to 64 characters long.
  Digits from 0 to 9 will be allowed within a
  column name.
• Keywords can not be used as column names. Column
  name may begin with a digit or . Same rules are
  applicable to the table names. Column names and
  table names are case insensitive.

30

• We can create BILL table by defining foreign key
  as follows
• CREATE TABLE BILL
• (CUST_ID INT NOT NULL,
• BILLNO INT NOT NULL AUTO_INCREMENT,
• AMT DECIMAL(10,2) NOT NULL,
• PRIMARY KEY(BILLNO),
• FOREIGN KEY (CUST_ID) REFERENCES CUST (CUST_ID))

31

• The two tables CUST and BILL will be stored in
  the database SHOP.
• To see the list of columns from table CUST we use
  the command
• SHOW COLUMNS FROM CUST
• Columns CUST_ID and NAME in CUST must not be
  empty as NOT NULL is specified, whereas address
  and phone columns may not be filled.

32

• We may use any name to store CUST_ID in the table
  BILL and still define it as foreign key.
• For e.g. if we use CNO instead of CUST_ID in BILL
  then we define foreign key statement as
• FOREIGN KEY (CNO) REFERENCES CUST (CUST_ID))
• SHOW COLUMNS FROM BILL
• displays columns as

33
Field Type Null Key Default Extra
CUST_ID int(11) NO MUL    
BILLNO int(11) NO PRI   auto_increment
AMT decimal(10,2) NO      
34

• MUL against column CUST_ID means that
• multiple occurrences in this column will be
• allowed.
• If foreign key is defined then we can not
• delete or update column CUST_ID in CUST
• table.

35

• To look at the code used for creating a table
  CUST, we use statement
• SHOW CREATE TABLE CUST
• MySQL server status information can be seen with
  the help of command
• SHOW STATUS

36

• MySQL enables us to specify default values to be
  used if no values are specified when a row is
  inserted. Default values are specified using the
  DEFAULT keyword in the column definition in the
  CREATE TABLE statement. For e.g.

37

• CREATE TABLE EMP
• (EMP_ID INT NOT NULL,
• NAME CHAR(20) NOT NULL,
• DEPTID CHAR(10) NOT NULL,
• DOJ DATE NOT NULL,
• CITY CHAR(10) NOT NULL,
• PERM BOOL NOT NULL DEFAULT 1,
• PRIMARY KEY (EMP_ID))

38

• BOOLEAN data type is usually taken as
• TINYINT. Value 1 is true and 0 is false.
• The order of NOT NULL and DEFAULT can
• be interchanged.

39

• We may use DESCRIBE as shortcut for SHOW COLUMNS
  FROM
• For e.g.
• DESCRIBE CUST

40

• To add data to the columns in the table CUST we
  use the command INSERT as follows.
• INSERT INTO CUST
• VALUES
• ('1',
• 'ANAND',
• 'MUMBAI',
• NULL)

41

• It is important to note that values must be
  inserted in the order as the order of columns
  specified in the table CUST. One may write an
  entire SQL statement in a single line.

42

• One can use INSERT command by changing order of
  the columns in the table. Order in which values
  are to be inserted must be specified. Also one
  can omit some of the columns provided the columns
  are defined to contain NULL values or some
  default values.
• For e.g.
• INSERT INTO CUST
• (NAME,CUST_ID,CITY)
• VALUES('BEENA',3, 'PUNE')

43

• We may insert values in a table without
  specifying the auto increment value.
• For e.g. In BILL table BILLNO is an auto
  increment column. To generate the next BILLNO we
  specify the other column names and their values
  in INSERT statement as follows.
• INSERT INTO BILL(CUST_ID,AMT)
• VALUES (4,4000)

44

• We can insert two or more in a table by using a
  single INSERT statement
• For e.g. we insert two rows in BILL table as
  follows
• INSERT INTO BILL (CUST_ID,AMT)
• VALUES(3,5000),
• (5,9000)

45

• ALTER STATEMENT
• ALTER statement is used to change the
• structure of a table after it has been created.
• ALTER statement is used to add columns or
• to delete columns from the table. Also it is
• used to change type and width of the
• columns and to rename the columns.

46

• For e.g.
• ALTER TABLE BILL
• ADD DT_PUR DATE NULL
• A new column with name DT_PUR will be
• added to the table BILL.
• ALTER TABLE CUST
• ADD GENDER CHAR(1) NOT NULL

47

• To drop the column DT_PUR we can give the command
• ALTER TABLE BILL
• DROP COLUMN DT_PUR
• The keyword COLUMN in above two
• statements is optional.

48

• We can rename a column using ALTER statement
• e.g.
• ALTER TABLE CUST CHANGE COLUMN
• PHONE CPHONE CHAR(20) NOT NULL
• ALTER TABLE CUST CHANGE COLUMN
• CPHONE PHONE CHAR(10) NOT NULL
• Column name, its type and width can all be
• changed in a single statement.

49

• It is advisable not to change data type as we
• may lose data.(It is advisable to specify null
• or not null in column definition). Keyword
• COLUMN is optional.
• To make AMT column in BILL table contain
• default value as 0 we use ALTER statement as
• follows.
• ALTER TABLE BILL CHANGE
• COLUMN AMT AMT DEC(10,2) NOT NULL
• DEFAULT 0

50

• We can make alterations to two or more columns at
  a time using ALTER TABLE statement for e.g.
• ALTER TABLE CUST DROP GENDER, ADD PIN CHAR(10)
  NULL
• ALTER TABLE CUST CHANGE NAME CNAME CHAR(20) NOT
  NULL, CHANGE CPHONE PHONE CHAR(10) NULL

51

• We can rename a table using RENAME
• statement
• For e.g.
• RENAME TABLE CUST TO CUST1

52

• To see the contents of all the columns from table
  CUST give command
• SELECT FROM CUST
• or
• SELECT FROM SHOP.CUST
• To display contents of specific columns give
  command
• SELECT CUST_ID,NAME,CITY FROM CUST

53
CUST_ID NAME CITY
1 BEENA MUMBAI
2 GEETA NAGPUR
3 ANAND PUNE
4 ATUL MUMBAI
5 RAHUL MUMBAI
6 LATA PUNE
7 AJAY MUMBAI
9 DEEPA MUMBAI
54

• SELECT CUST.NAME FROM SHOP.CUST
• To return first few rows we use LIMIT clause for
  e.g.
• SELECT FROM CUST LIMIT 5
• It will display the first five rows of table CUST
• SELECT FROM CUST LIMIT 5,4
• It will display rows from 6 to 9 of table CUST.

55

• DISTINCT
• To get distinct CUST_ID values from table BILL we
  use statement
• SELECT DISTINCT CUST_ID FROM BILL
• If DISTINCT is not specified then all the rows
  will be retrieved. That means ALL is the default
  value.

56

• ORDER BY
• Data in a table can be sorted in ascending
• or descending order with help of clause
• ORDER BY
• For e.g.
• SELECT FROM CUST ORDER BY NAME
• SELECT FROM CUST ORDER BY NAME
• DESC

57

• Sorting can be done on multiple fields for e.g.
• SELECT FROM CUST ORDER BY CITY,NAME
• SELECT FROM CUST ORDER BY CITY DESC, NAME
• SELECT FROM CUST ORDER BY CITY,NAME DESC

58

• We can combine ORDER BY and LIMIT clause
• SELECT FROM CUST
• ORDER BY CITY LIMIT 4
• SELECT FROM CUST
• ORDER BY CITY LIMIT 2,3

59

• WHERE clause WHERE clause is used to retrieve
  those rows which match the specified condition.
• SELECT FROM CUST WHERE CITY'MUMBAI' AND
  CUST_IDlt3
• SELECT FROM CUST WHERE CITY'MUMBAI' OR
  CUST_IDlt3
• SELECT FROM BILL WHERE AMTgt5000

60

• SELECT DISTINCT CUST_ID FROM BILL WHERE
  AMTgt10000

CUST_ID
1
2
3
4
7
61

• SELECT CUST_ID,AMT FROM BILL WHERE AMTgt10000
  ORDER BY CUST_ID

CUST_ID AMT
1 11000
2 11000
3 16000
3 11000
4 16000
7 11000
62

• SELECT CUST_ID,NAME,CITY FROM CUST WHERE
  CITYltgt'MUMBAI'
• SELECT CUST_ID,NAME,CITY FROM CUST WHERE
  CITY!'MUMBAI'
• SELECT FROM CUST WHERE NAMElt'G' ORDER BY NAME
• SELECT FROM CUST WHERE NAMEgt'G' ORDER BY NAME

63

• LIKE operator
• SELECT FROM CUST WHERE NAME LIKE 'A'
• The above command gives list of all rows for
  which NAME begins with letter A
• SELECT FROM CUST WHERE NAME LIKE 'a'
• The above command gives list of all rows for
  which NAME begins with letter a.

64

• SELECT FROM CUST WHERE NAME LIKE 'A'
• The above command gives list of all rows for
  which NAME contains letter A.
• One can combine LIKE operator and ORDER BY
  clause.
• e.g.
• SELECT FROM CUST WHERE CITY LIKE 'D' ORDER BY
  NAME

65

• We can combine LIKE operator and AND operator for
  e.g.
• SELECT FROM CUST WHERE CNAME LIKE 'A' AND
  CITY'MUMBAI'

66

• SELECT FROM CUST WHERE CITY'MUMBAI' ORDER BY
  NAME
• SELECT FROM BILL WHERE DT_PUR BETWEEN
  '2000-05-05' AND '2002-09-09'
• The above statement is equivalent to
• SELECT FROM BILL
• WHERE DT_PUR gt'2000-05-05' AND
• DT_PUR lt'2002-09-09'
• Date must be enclosed in quotes.

67

• SELECT BILLNO, AMT FROM BILL WHERE AMT BETWEEN
  5000 AND 7000
• The above statement is equivalent to
• SELECT BILLNO, AMT FROM BILL WHERE AMTgt5000 AND
  AMTlt7000

68

• IN operator
• IN is a keyword used in WHERE clause to specify
  a list of values to be matched using an OR
  comparison.
• e.g. SELECT CUST_ID, NAME
• FROM CUST WHERE CUST_ID IN
• (2,4) ORDER BY NAME
• It returns CUST_ID and NAME for those rows for
  which CUST_ID has value 2 OR 4 in alphabetical
  order of NAME.

69

• Same task can be accomplished with help of OR
  operator as follows
• SELECT CUST_ID, NAME
• FROM CUST WHERE CUST_ID 2 OR CUST_ID4 ORDER BY
  NAME
• SELECT CUST_ID,NAME,CITY FROM
• CUST WHERE CITY IN ('MUMBAI','PUNE')

70

• NOT operator It is used to negate a condition.
  e.g.
• SELECT CUST_ID, NAME
• FROM CUST WHERE CUST_ID NOT IN (2,4) ORDER BY
  NAME
• It returns CUST_ID and NAME for all those rows
  for which CUST_ID is other than 2 or 4.

71

• This task can also be accomplished with the help
  of AND operator as follows
• SELECT CUST_ID, NAME
• FROM CUST WHERE CUST_ID !2 AND CUST_ID!4
  ORDER BY NAME
• SELECT CUST_ID,CNAME,CITY FROM
• CUST WHERE CITY NOT IN
• ('MUMBAI','NAGPUR') ORDER BY NAME

72

• Another example of IN operator
• SELECT FROM CUST WHERE CUST_ID IN (2,3,4)
• SELECT FROM CUST WHERE CUST_ID NOT IN (2,3,4)

73

• UPDATE statement
• To modify data in a table UPDATE statement is
  used.
• It can be used in two ways.
• 1. To update specific rows in a table.
• 2. To update all the rows in a table.

74

• Basic format of UPDATE statement consists of
  three parts.
• 1. The table to be updated
• 2. The column names and their new values
• 3. The condition which determines which rows to
  be updated.

75

• To update specific rows in a table we WHERE
  clause
• For e.g.
• UPDATE BILL
• SET DT_PUR '2007-05-05' WHERE BILLNO237
• (ii) UPDATE CUST
• SET CITY'MUMBAI',
• PHONE'4567889' WHERE CUST_ID5

76

• If we omit the WHERE clause in UPDATE statement
  then every row in the table gets updated with the
  new values.
• UPDATE CUST SET CITY'MUMBAI'
• For every row ADDRESS gets updated as
• MUMBAI.
• UPDATE BILL SET AMTAMT1000
• Each amt will be increased by 1000.

77

• DELETE statement
• DELETE is used to
• 1. To delete specific rows from a table
• 2. To delete all rows from a table

78

• For e.g.
• DELETE FROM CUST
• WHERE CUST_ID5
• DELETE FROM BILL WHERE AMTgt5000
• If we execute DELETE statement without WHERE
  clause then all the contents of a table get
  deleted.

79

• We can use ORDER BY and LIMIT clause along with
  DELETE statement as follows
• DELETE FROM BILL ORDER BY CUST_ID
• LIMIT 5
• It deletes first 5 rows from the BILL table
• after arranging the table in ascending order
• of CUST_ID.

80

• DROP statement
• It is used to delete entire table.
• e.g. DROP TABLE CUST
• This statement deletes table CUST. There is no
  confirmation nor there is an undo.
• It can also be used to delete entire database
• e.g. DROP DATABASE SHOP

81

• Aggregate functions
• Aggregate functions are used to summarize the
  data without actually retrieving it.
• AVG() It returns average value of a column.

82

• COUNT() It returns number of rows in a column.
• MAX() and MIN() return the maximum and minimum
  values of a column.
• SUM() returns total for a column.

83

• GROUP BY clause
• The GROUP BY clause is used to sort the data into
  groups for the purpose of aggregation.
• Suppose we want the number of times each customer
  has purchased from the shop then for this we use
  GROUP BY clause as follows

84

• SELECT CUST_ID,COUNT()
• FROM BILL GROUP BY CUST_ID
• SELECT AVG(AMT) FROM BILL
• GROUP BY CUST_ID

85

• SELECT CUST_ID,SUM(AMT) FROM BILL GROUP BY
  CUST_ID
• SELECT CUST_ID, COUNT(),SUM(AMT) FROM BILL WHERE
  AMTgt10000
• GROUP BY CUST_ID ORDER BY CUST_ID DESC

86
CUST_ID COUNT() SUM(AMT)
7 1 11000
6 1 12000
5 2 20000
4 1 16000
3 2 27000
2 2 21000
1 1 11000
87

• SELECT CUST_ID, MAX(AMT) FROM BILL WHERE
• AMTgt10000 AND AMTlt15000
• GROUP BY CUST_ID ORDER BY CUST_ID DESC
• SELECT CUST_ID, MIN(AMT) FROM BILL WHERE
• DT_PURgt '2009-01-01'
• GROUP BY CUST_ID ORDER BY CUST_ID DESC

88

• A column name can be called by some other name
  called as column alias. e.g.
• SELECT , .10AMT AS DISCOUNT FROM BILL
• SELECT , AMT-.10AMT AS NSP FROM BILL
• Keyword AS is optional.

89

• MySQL functions
• CONCAT() function is used to combine two
• or more strings.
• SELECT
• CONCAT(NAME , 'STAYS IN' ,CITY)

90

• SELECT
• CONCAT(TRIM(' MONDAY '),' 'TUESDAY')
• SELECT
• CONCAT(RTRIM(' MONDAY '),' ','TUESDAY')
• SELECT
• CONCAT(LTRIM(' MONDAY '),'
  ','TUESDAY')
• SELECT MID('COMPUTER',4,3)
• SELECT SUBSTRING('COMPUTER',4,3)

91

• SELECT RIGHT(CNAME,3) FROM CUST
• Returns 3 rightmost characters from CNAME
• SELECT LEFT(CNAME,3) FROM CUST
• Returns 3 leftmost characters from CNAME
• SELECT UPPER(CNAME) FROM CUST
• Returns CNAME in uppercase letters. We may use
  UCASE instead of UPPER.

92

• SELECT LOWER(CNAME) FROM CUST
• Returns CNAME in lowercase letters. We may use
  LCASE instead of LOWER.
• LENGTH()
• This function returns the number of
• characters contained in the given string.
• SELECT LENGTH('COMPUTER')

93

• SELECT NOW()
• Returns the current date and time.
• SELECT TIME(NOW())
• Returns the current time.
• SELECT TIME('2009-12-14 193000')
• Returns the time. i.e. 193000
• SELECT CURDATE()
• Returns the current date.

94

• SELECT DATE('2009-09-14 193000')
• Returns the date i.e. 2009-09-14
• SELECT DAY('2009-09-14 193000')
• Returns day value i.e. 14
• SELECT DAYNAME('2009-09-25 193000')
• Returns day name Friday

95

• MONTH()
• Returns month from given date
• SELECT MONTH('2009-09-10')
• MONTHNAME()
• Returns name of the month from given date
• SELECT MONTHNAME('2009-09-10')
• YEAR()
• Returns year from given date
• SELECT YEAR('2009-09-10')

96

• SELECT MONTH('2009-12-14 193000')
• Returns month 12
• SELECT MONTHNAME('2009-12-14 93000')
• Returns December
• SELECT YEAR('2009-12-14 193000')
• Returns year i.e. 2009.
• ABS()
• Returns absolute value of the given number.
• SELECT ABS(-10)

97

• POW() or POWER()
• Returns a number raised to a power.
• SELECT POW(3,2)
• MOD()
• Returns remainder after dividing first number by
  the second
• SELECT MOD(5,4)

98

• ROUND()
• It rounds off the number to specified number of
  decimal places.
• SELECT ROUND(123.5678,2)
• SQRT()
• Returns square root of a positive number
• SELECT SQRT(9)
• EXP()
• Returns e raised to specified power.
• SELECT EXP(2)

99

• HAVING clause
• HAVING clause is used to add additional control
  to the aggregation of rows in a GROUP BY
  operation.
• Suppose we want list of all those customers who
  have purchased at least twice from the shop we
  use HAVING clause as follows
• SELECT CUST_ID, COUNT() FROM BILL GROUP BY
  CUST_ID HAVING COUNT()gt2

100

• A WHERE clause does not work here as filtering is
  done based on groups and not based on individual
  rows.
• SELECT CUST_ID,SUM(AMT) FROM BILL GROUP BY
  CUST_ID HAVING SUM(AMT)gt10000 ORDER BY CUST_ID

101
CUST_ID SUM(AMT)
1 11000
2 26000
3 36000
4 25000
5 20000
6 12000
7 16000
102

• SELECT CUST_ID,MAX(AMT) FROM BILL GROUP BY
  CUST_ID
• HAVING SUM(AMT)gt15000 AND SUM(AMT)lt50000
  ORDER BY CUST_ID
• SELECT CUST_ID,COUNT(),SUM(AMT) FROM BILL WHERE
  DT_PURgt'2009-01-01'
• GROUP BY CUST_ID HAVING SUM(AMT)gt10000 ORDER
  BY CUST_ID

103
CUST_ID COUNT() SUM(AMT)
1 1 11000
3 2 20000
4 2 25000
6 1 12000
104

• SELECT CUST_ID,COUNT(),SUM(AMT) FROM BILL
• WHERE DT_PURgt'2009-01-01'
• GROUP BY CUST_ID HAVING SUM(AMT)gt10000 ORDER
  BY SUM(AMT)

105
CUST_ID COUNT() SUM(AMT)
1 1 11000
6 1 12000
3 2 20000
4 2 25000
106

• JOIN
• Relational Tables
• The two tables discussed so far are relational
  tables. Table CUST contains information about
  customers visiting a shop such as customer name,
  address etc. Customer identity (CUST_ID) is the
  primary key.

107

• Another table BILL contains information regarding
  details about bill amount, date of purchase, bill
  number (bill number forms the primary key as this
  table contains multiple entries). The two tables
  can be related to each other through a common
  value which is CUST_ID in this case.

108

• We have split the information into two tables so
  that having multiple occurrences of the same data
  ( i.e. CUST_ID, CITY) can be avoided. This is the
  basic concept of a relational database design.
• Breaking data into multiple tables makes storage
  of data more efficient, data is easy to
  manipulate and large amount of data is easy to
  handle.

109

• When data is stored in multiple tables, we can
  access the data in these tables with the help of
  a mechanism called JOIN.
• For creating a join we have to specify all the
  tables to be included and how they are related to
  each other

110

• When columns in different tables are joined, we
  have to qualify column names with their
  corresponding table names.
• e.g.
• SELECT NAME,AMT
• FROM BILL,CUST WHERE CUST.CUST_IDBILL.CUST_ID
• ORDER BY NAME
• Order of tables specified with FROM can be
  changed.

111

• The join we have used here is called as Equijoin
  or Inner Join. A slightly different syntax can
  also be used as follows.
• SELECT NAME ,AMT
• FROM CUST INNER JOIN BILL
• ON BILL.CUST_IDCUST.CUST_ID ORDER BY NAME

112

• SELECT NAME ,AMT
• FROM BILL INNER JOIN CUST
• ON BILL.CUST_IDCUST.CUST_ID ORDER BY NAME
• SELECT NAME,AMT
• FROM BILL,CUST WHERE CUST.CUST_IDBILL.CUST_ID
  AND AMTgt10000

113

• SELECT NAME,AMT
• FROM BILL INNER JOIN CUST ON
  CUST.CUST_IDBILL.CUST_ID AND AMTgt10000

114
NAME AMT
GEETA 11000
GEETA 10000
ANAND 16000
ANAND 11000
RAHUL 10000
RAHUL 10000
LATA 12000
AJAY 11000
115

• SELECT CUST.CUST_ID,NAME ,AMT
• FROM CUST INNER JOIN BILL
• ON BILL.CUST_IDCUST.CUST_ID ORDER BY NAME

116
CUST_ID CNAME AMT
7 AJAY 11000
7 AJAY 5000
3 ANAND 16000
3 ANAND 11000
4 ATUL 16000
4 ATUL 9000
2 GEETA 11000
6 LATA 12000
5 RAHUL 10000
117

• To access data in all the columns of each of the
  tables CUST and BILL we use SELECT in JOIN as
  follows
• SELECT CUST., BILL.
• FROM BILL,CUST WHERE
  CUST.CUST_IDBILL.CU
  ST_ID
• ORDER BY NAME

118

• or
• SELECT CUST. ,BILL.
• FROM BILL INNER JOIN CUST
• ON BILL.CUST_IDCUST.CUST_ID ORDER BY NAME

119

• Same task can be accomplished with the help of
  statement
• SELECT C. , B.
• FROM BILL AS B INNER JOIN CUST AS C
• ON B.CUST_IDC.CUST_ID ORDER BY NAME
• B is called as alias of BILL table and C is an
  alias of CUST table. Aliases are used to improve
  readability of MySQL statements. We need not type
  table name every time.

120
CUST_ID NAME CITY PHONE CUST_ID BILLNO AMT DT_PUR
7 AJAY MUMBAI   7 252 11000 2007-08-04
7 AJAY MUMBAI   7 265 5000  
3 ANAND PUNE 4545545 3 262 16000  
3 ANAND PUNE 4545545 3 263 11000  
4 ATUL MUMBAI   4 259 16000 2009-09-09
4 ATUL MUMBAI   4 264 9000  
2 GEETA NAGPUR 24444444 2 261 11000  
6 LATA PUNE   6 266 12000 2009-09-09
5 RAHUL MUMBAI   5 251 10000 2007-10-10
121

• Subqueries
• A query that is wrapped within another query. It
  is also known as inner query.
• A subquery is a SELECT statement within
• another statement. Subqueries always
• appear as part of WHERE clause or
• HAVING clause.

122

• In WHERE clause subqueries select the
  individual rows. A subquery is always enclosed in
  the parentheses. A subquery must produce a single
  column of data as its result, that means we can
  not use SELECT in a subquery.

123

• Outer Query
• A query which contains an inner query or
• subquery.
• Inner Query
• A query inside another query. It is also
• known as subquery.

124

• The ORDER BY clause can not be used in a
• subquery. Since the results of a subquery
• are used internally and are not displayed
• ordering does not make much sense. We
• can specify ORDER BY in the main query or
• outer query. e.g.
• SELECT CUST_ID,NAME,
• (SELECT SUM(AMT) FROM BILL
• WHERE CUST.CUST_IDBILL.CUST_ID)
• FROM CUST ORDER BY NAME

125

• In this example SELECT CUST_ID,NAME is
• called as the outer query or main query and
• (SELECT SUM(AMT) FROM BILL
• WHERE CUST.CUST_IDBILL.CUST_ID)
• is called as the subquery.
• The above example is a correlated
• subquery as reference to the table CUST is
• made in it. Table name CUST appears in the
• outer query.

126

• Noncorrelated Subquery
• A subquery that stands alone and does not
• reference anything from the outer query.
• Correlated Subquery
• A subquery that relies on values returned
• from the outer query

127

• A subquery can be constructed using IN
• clause, for e.g.
• SELECT CUST_ID, NAME, CITY
• FROM CUST WHERE CUST_ID IN
• (SELECT CUST_ID FROM BILL WHERE AMTgt10000) ORDER
  BY NAME

128
CUST_ID NAME CITY
7 AJAY MUMBAI
3 ANAND PUNE
4 ATUL MUMBAI
2 GEETA NAGPUR
6 LATA PUNE
5 RAHUL MUMBAI
129

• This query displays CUST_ID and NAME
• from CUST table in alphabetical order of
• name for those customers whose AMT is
• greater than or equal to 10000.

130

• To retrieve CUST_ID, AMT for those
• customers who have AMTgtAVG(AMT) we
• use a subquery as
• SELECT CUST_ID, AMT FROM BILL
• WHERE
• AMTgt(SELECT AVG(AMT) FROM BILL)

131

• Subquery using HAVING clause
• Suppose we want to retrieve CUST_ID,
• AVG(AMT) for all those customers who have
• AVG(AMT) more than AVG(AMT) of the
• entire table then we type a subquery using
• HAVING clause as

132

• SELECT CUST_ID,AVG(AMT) FROM BILL
• GROUP BY CUST_ID HAVING
• AVG(AMT)gt(SELECT AVG(AMT)FROM
• BILL)

133

• Transaction processing is used to maintain
• database integrity. It is used to ensure that
• the batches of MySQL operations execute
• completely or not at all. For e.g. if a row is
• wrongly deleted then this can be undone
• with the help of ROLLBACK.
• Transaction processing is used to manage
• INSERT, UPDATE and DELETE
• statements. We can not roll back CREATE,
• DROP or SELECT statements.

134

• To start a transaction we use the statement
• START TRANSACTION
• For e.g.
• SELECT from CUST
• START TRANSACTION
• DELETE FROM CUST
• SELECT FROM CUST
• ROLLBACK
• The deleted rows are retrieved using ROLLBACK.

135

• MySQL statements are usually executed and written
  directly to database tables. This is called
  implicit commit. This does not happen within a
  transaction block.
• We can undo the previous MySQL statements that
  appear within START TRANSACTION. This is done
  with help of ROLLBACK.

136

• To force explicit commit, the commit statement is
  used
• For e.g.
• START TRANSACTION
• SELECT FROM CUST
• DELETE FROM CUST WHERE CITY 'PUNE'
• SELECT FROM CUST
• COMMIT
• SELECT FROM CUST
• Rows with city name PUNE will be deleted.
• Transaction block ends after COMMIT or
• ROLLBACK.