CBSE Class 12 Informatic Practices MySQL

Downloaded from www.studiestoday.
com
A PTE
H
8
R
C
MySQL - REVISION TOUR

Learning Objectives
After studying this lesson the students will be able to:
• Recall the terminology used in RDBMS concepts.
• Recall and effectively use the SQL commands and clauses learnt in class XI.
• Realize that much more is to be learnt for effective use of databases.
In the previous class, you have learnt some database concepts and SQL commands.
You have also learnt how to access data from a table using various clauses of
SELECT command. In this lesson we are going to revise the database concepts and
SQL studied in class XI. That will make the base for further reading of the concepts
and some more SQL commands which are really very useful in practical
applications.
Database Concepts
You have already studied the following database concepts in class XI:
1. Database: A database is an organised collection of data.
Software used to manage databases is called Data Base Management System (DBMS).
2. Relational Database: A database in which the data is stored in the form of relations
(also called tables) is called a Relational Database. In other words a Relational
Database is a collection of one or more tables.
3. RDBMS: A DBMS used to manage Relational Databases is called an RDBMS (Relational

Data Base Management System). Some popular RDBMS software available are:
Oracle, MySQL, Sybase, Ingress
244
Downloaded from www.studiestoday.com
4. Benefits of using a DBMS are:

a. Redundancy can be controlled
b. Inconsistence can be avoided
c. Data can be shared
d. Security restrictions can be applied.
5. MySQL: It is an Open Source RDBMS Software. It is available free of cost.
6. Relation/Table: A table refers to a two dimensional representation of data arranged
in columns (also called fields or attributes) and rows (also called records or tuples).
The tables in a database are generally related to each other to facilitate efficient
management of the database. Interrelated tables also reduce the chances of errors
in the database.
7. Key: A column or a combination of columns which can be used to identify one or
more rows (tuples) in a table is called a key of the table.
8. Primary Key: The group of one or more columns used to uniquely identify each row
of a relation is called its Primary Key.
9. Candidate Key: A column or a group of columns which can be used as the primary
key of a relation is called a candidate key because it is one of the candidates
available to be the primary key of the relation
10. Alternate Key: A candidate key of a table which is not made its primary key is called
its Alternate Key.
Let us now revise MySQL concepts studied in class XI with the help of a Library database.
Note: As this is a revision lesson, all variations of SQL commands are not covered
here. It is suggested that they are revised through practical assignments.
Think of the functioning of a computerised school library.
In a school library, there are books which are issued to the students and the staff. These
books are also returned after a certain period of time. For proper functioning of this
system the librarian maintains a database (an organised collection of data) which
contains the relevant data of books, students, and staff. In a relational database, this
data may be organised as a collection of the following tables:
245
BOOKS
AccNo Title Author Publisher Edition Price

1001 Basic Economics J.P. Mudgil APH 2009 125
1002 MicroEconomics Kavi Prakash KPH 2009 175
1003 MacroEconomics Kavi Prakash KPH 2010 200
AccNo is Accession Number of a book.
STUDENTS
AdmNo LibCardNo AccNo IssueDate ReturnDate
2010001 101 1002 2009-12-02 2009-12-08
2009012 102 1050 2010-02-16 NULL
2009013 103 1060 2010-02-16 NULL
STAFF
EmpNo AccNo IssueDate ReturnDate
1 1001 2009-04-13 NULL
2 NULL NULL NULL
2 NULL NULL NULL
These three related tables collectively form the Library database.
Using the data stored in these tables the librarian can find out any type of relevant
information regarding the books at any time. For example, she can find out:
• The list of books in the library which are published by any specific publisher or
author.
• The list of books with price in a specified range.
246
• Admission numbers of the students who have not returned the books issued to
them.
• The names of books issued to a particular staff member or student.
For the above tables the primary keys may be set as follows:
Table Primary Key Reason
BOOKS AccNo No two books have the same accession number.
STUDENTS AdmNo No two students have the same admission number.
STAFF -- As there is no column for which each row has a

unique value, there is no primary key of this table.
In the STUDENTS table LibCardNo can also be the primary key (Why?). It means that there
are two columns in the table STUDENTS that are unique for each row and can be set as the
primary key of the table. These two columns are candidate keys as they are the
candidates for primary key. Out of these two candidate keys we have chosen AdmNo as
the primary key. The other candidate key, i.e., LibCardNo, becomes the Alternate Key of
the table. Had we chosen LibCardNo as the primary key, AdmNo would have been called
the alternate key of the table.
In class XI we have studied that degree of a table is the number of columns in the table,
and the number of rows in a table is called its cardinality.
Can you tell what are the degrees and cardinalities of tables of Library database?
Libraries function without computers also. Is there any advantage of setting up a
computerised Library Database using an RDBMS? Discuss it with your teacher.
We shall be using MySQL for Library database.
Once MySQL is procured (downloaded from net, or copied from somewhere, or received
free with some book), it can be installed and the process of creating Library database can
be started.
247
Working with MySQL

You have already studied the following SQL concepts in class XI:
1. SQL (Structured Query Language): It is the language used to manipulate and
manage databases and tables within them using an RDBMS.
2. DDL (Data Definition Language): This is a category of SQL commands. All the
commands which are used to create, destroy, or restructure databases and tables
come under this category. Examples of DDL commands are - CREATE, DROP, ALTER.
3. DML (Data Manipulation Language): This is a category of SQL commands. All the
commands which are used to manipulate data within tables come under this
category. Examples of DML commands are - INSERT, UPDATE, DELETE.
4. DCL (Data Control Language): This is a category of SQL commands. All the
commands which are used to control the access to databases and tables fall under
this category. Examples of DCL commands are - GRANT, REVOKE.
Different SQL commands studied in class XI are summarized below:
SNO Command, Syntax, and Purpose Category

1. Command: CREATE DATABASE DDL
Syntax: CREATE DATABASE <databasename>;
Purpose: Creates a database with the specified name.
2. Command: CREATE TABLE DDL
Syntax: CREATE TABLE <tablename>
(<column name1> <data type1>
[,<column name2> <data type2>,
.
.
<column nameN> <data typeN>]
);
Purpose: Creates a table with the specified name.
248
3. Command: ALTER TABLE DDL
Syntax: ALTER TABLE <tablename>
ADD <columnname> <datatype>;
ALTER TABLE <tablename>
DROP <columnname>;
ALTER TABLE <tablename>
MODIFY <column> <new_definition>;
Purpose: Modifies the structure of a table
4. Command: USE DML
Syntax: USE <databasename>;
Purpose: Opens the specified database for use.
5. Command: SELECT DATABASE() DML
Syntax: SELECT DATABASE();
Purpose: Shows the name of the current database
6. Command: SHOW TABLES DML
Syntax: SHOW TABLES;
Purpose: Shows a list of tables present in the current

database.
7. Command: INSERT DML
Syntax: INSERT INTO <tablename>
[<column1>, <column2>, ..., <columnn>]
VALUES (<value1>, <value2>, ... <value n>);
Purpose: Inserts data into a table
249
8. Command: SELECT DML
Syntax: SELECT <* / column name / expression> ,
[<column name/Expression list>]
FROM <table name>
[WHERE <condition>]
[ORDER BY <column name / expression>

[ASC/DESC]];
There are multiple ways to use SELECT.

These will be explained with the help of
examples in this lesson.
Purpose: Retrieves data from a table
9. Command: DESCRIBE DML
Syntax: DESC[RIBE] <tablename>;
Purpose: Shows the structure of a table.
10. Command: UPDATE DML
Syntax: UPDATE <tablename>
SET <column name> = <value>

[,<column name> = <value>, …]
[WHERE <condn>];
Purpose: Updates/Modifies data in a table
11. Command: DELETE DML
Syntax: DELETE FROM < tablename>

[ Where < condition>];
Purpose: Deletes data from a table
250
Following are the clauses which can be used with SELECT command:
a. DISTINCT Used to display distinct values from a column of a table.
b. WHERE Used to specify the condition based on which rows of a table are
displayed.
c. BETWEEN Used to define the range of values within which the column
values must fall to make a condition true. Range includes both
the upper and the lower values.
d. IN Used to select values that match any value in a list of Specified

values.
e. LIKE Used for pattern matching of string data using wildcard

characters % and _ .
f. IS NULL / Used to select rows in which the specified column is NULL (or is
NOT NULL NOT NULL)
g. ORDER BY Used to display the selected rows in ascending or in descending

order of the specified column/expression.
Now, let us revise these commands with the help of Library database discussed above.
• After starting MySQL, our first job is to create a database in which all the tables
will be stored. For this we give the statement:
CREATE DATABASE Library;
This statement will create a database with the name Library using the
command CREATE DATABASE.
• Now we have to open this database so that we can create tables and do other
tasks in it. For this we give the following statement
USE Library;
After creating the database and opening it, we have to create tables. Before we create a
table, we should very carefully plan columns that we want to have in the table and type of
data to be stored in each column. The type of data decides what data types should be
251
specified for different columns of a table. There are many data types available in MySQL,
but a few which are used most often are:
Class Data Type
Text CHAR(size)
VARCHAR(size)
NUMERIC DECIMAL(p,s)
INT(size) or INTEGER(size)
date DATE
After planning, we arrive at the following data types for columns of our tables in the
Library database. Here the column sizes are based on our assumption. These may be
different for different library databases.
BOOKS
Coulmn Data Type Reason
AccNo INTEGER(5) Accession number of a book is an integer in the range

1 to 99999.
Title VARCHAR(30) Title of a book may be maximum upto 30 characters

long
Author VARCHAR(30) An author name may be maximum upto 30 characters

long
Publisher VARCHAR(10) A publisher name may be maximum upto 10

characters long.
Edition INTEGER(4) Year of edition of the book
Price DECIMAL(7, 2) We assume that the price of a book will not exceed
Rs. 99999.99
252
STUDENTS
AdmNo VARCHAR(7) Here we assume that admission number consists of 4

digit year of admission followed by 3 digit admission
serial number. Therefore, AdmNo 2009012 means
that the students was admitted in the school in the
year 2009 and he/she was the 12th student (in
sequence) to take admission in that year.
LibCardNo INTEGER(4) Library card number is an integer in the range 1 to

9999.

1 to 99999.
IssueDate DATE IssueDate is the date of issue of the book.
ReturnDate DATE ReturnDate is the date on which the book was

returned.
STAFF
EmpNo INTEGER(3) Employee number is an integer in the range 1 to 999.

1 to 99999.
IssueDate DATE It is the date of issue of the book.
ReturnDate DATE It is the date on which the book was returned.
After deciding the columns to be kept in each table and their data types, we are ready to
create the tables.
253
The statements to create these tables are as follows:
• To create the BOOKS table:

CREATE TABLE Books
(AccNo INTEGER(5),
Title VARCHAR(30),
Author VARCHAR(30),
Publisher VARCHAR(10),
Edition INTEGER(4),
Price DECIMAL(7,2));
• To create the STUDENTS table:

CREATE TABLE Students
(AdmNo VARCHAR(7),
LibCardNo INTEGER(4),
AccNo INTEGER(5),
IssueDate DATE,
ReturnDate DATE);
• To create the STAFF table:

CREATE TABLE Staff
(EmpNo INTEGER(3),
AccNo INTEGER(5),
IssueDate DATE,
ReturnDate DATE);
• To verify that the tables are created, we give the statement:

SHOW TABLES;
254
• To further verify that the tables have been created as per the required
specifications, we view the structure of each table by giving the statements:
DESC Books;
DESC Students;
DESC Staff;
• If the structure of a table is not as per the required specifications, we can

modify it by using the ALTER TABLE command. Following are three examples of
this:
(i) Suppose we decide to categorise the books into categories 'A', 'B' and 'C'
based on some criteria. Then a new column Category of type CHAR(1) can
be added to the table by the statement:
ALTER TABLE Books ADD Category CHAR(1);
(ii) Suppose we wish to change the size of column 'publisher' of Books table
from 10 characters to 20 characters, then we can give the statement:
ALTER TABLE Books MODIFY Publisher VARCHAR(20);
(iii) Suppose we decide to remove the ReturnDate column from the table
STAFF. We can do so by the statement:
ALTER TABLE Staff DROP ReturnDate;
• Once the tables are created as per the required specifications, we can
populate these tables with the given sample data as follows:
à
For Books table:
INSERT INTO Books VALUES
(1001, 'Basic Economics', 'J.P.Mudgil',
'APH', 2009, 125);
INSERT INTO Books VALUES
(1002, 'Micro Economics', 'Kavi Prakash',
'KPH', 2009, 175);
255
à
For Students table:
INSERT INTO Students VALUES
('2010001', 101, 1002, '2009-12-02', '2009-12-08');
INSERT INTO Students VALUES
('2009012', 102, 1050,'2010-02-16', NULL);
à
For Staff table:
INSERT INTO Staff VALUES
(1, 1001,'2009-04-13', NULL);
INSERT INTO Staff (EmpNO) VALUE (2);
• Now we can check whether the data has been entered into the tables correctly
or not by entering the statements:
SELECT * FROM Books;
SELECT * FROM Students;
SELECT * FROM Staff;
We can use various clauses with SELECT command to display the data. Examples are
shown below:
• To check the names (without repetition) of various publishers whose books are
present in the library, we enter the statement:
SELECT DISTINCT Publisher FROM Books;
• To check the books of publisher 'APH' present in the library, we enter the
statement:
SELECT * from Books WHERE Publisher = 'APH';
• To display the books list for which the price is between ` 250 and ` 500, we
enter the statement:
SELECT * FROM Books WHERE Price > 250 and Price < 500;
256
• To display the books list for which the price is from ` 250 to ` 500, we enter the
statement:
SELECT * FROM Books WHERE Price >= 250 and Price <= 500;
OR
SELECT * FROM Books WHERE Price BETWEEN 250 and 500;
• To display the details of books from the publishers 'APH' 'JPH', or 'ABD', we enter
the statement:
SELECT * from Books
WHERE Publisher = 'APH'
OR Publisher = 'JPH'
OR Publisher = 'ABD';
OR
SELECT * from Books
WHERE Publisher IN ('APH', 'JPH', 'ABD');
• To list the AccNo, Title, and Price of all the books whose Title contains the word
'History', we can enter the statement:
SELECT AccNo, Title, Price from Books
WHERE Title LIKE '%History%';
• To display the AccNo of all the books which have been issued to students but not
returned by them, we enter the statement:
SELECT AccNo FROM Students
WHERE IssueDate IS NOT NULL and ReturnDate IS NULL;
• To Display a List of all the Books in the alphabetical ascending order of Titles,
we enter the statement:
SELECT * FROM Books ORDER BY Title;
257
• To get the same list in descending order of Titles, we enter the statement:
SELECT * FROM Books ORDER BY Title DESC;
• Now suppose a student returns a Book with AccNo 1245 on 12 August 2010, then
this information can be recorded in the Students table as follows:
UPDATE Students
SET ReturnDate = '2010-08-12'
WHERE AccNo = 1245;
• If we want to delete the records of all the books by the Publisher 'PPP' with
edition year earlier than 1990, we can enter the statement:
DELETE FROM Books
WHERE Publisher = 'PPP' AND Edition < 1990;
Suppose for some analysis we decide to keep track of how many times each book is issued.
This can be done if there is an extra column 'IssueFreq' in the table Books to keep this
count. Every time a book is issued, the corresponding value in this column is incremented
by 1. To add this extra column in the table, we enter the statement:
ALTER TABLE Books ADD IssueFreq INTEGER(3);
Functions in MySQL
Let us now revise the following single-row SQL functions which are available in MySQL:
Numeric Functions:
Sno Name & Syntax Description
1. POWER(x,y) Returns the value of x raised to the power of y.
OR
POW(x,y)
2. ROUND(x) Rounds the argument x to the nearest INTEGER.
3. ROUND(x,d) Rounds the argument x to d decimal places.
4. TRUNCATE(x,d) Truncates the argument x to d decimal places.
258
String Functions:
SNo Name & Syntax Description

1. LENGTH(str) Returns the length of a column or a string in bytes.
2. CONCAT(str1,str2,...) Returns the string that results from concatenating
the arguments. May have one or more arguments.
3. INSTR(str,substr) Returns the position of the first occurrence of
substring <substr> in the string <str>.
4. LOWER(str) Returns the argument <str> in lowercase. i.e., It
or LCASE(str) changes all the characters of the passed string to
lowercase.
5. UPPER(str) Returns the argument <str> in uppercase. i.e., It
or UCASE(str) changes all the characters of the passed string to
uppercase.
6. LEFT(str,n) Returns the first <n> characters from the string <str>
7. RIGHT(str,n) Returns the last <n> characters from the string <str>
8. LTRIM(str) Removes leading spaces, i.e., removes spaces from
the left side of the string <str>.
9. RTRIM(str) Removes trailing spaces, i.e., removes spaces from
the right side of the string <str>.
10. TRIM(str) Removes both leading and trailing spaces from the
string <str>.
11. SUBSTRING(str,m,n) Returns <n> characters starting from the mth
OR character of the string <str>.
SUBSTR(str, m, n) If the third argument <n> is missing, then starting
OR from mth position, the rest of the string is returned.
MID(str,m,n) If <m> is negative, the beginning of the substring is
the mth character from the end of the string.
12. ASCII(str) Returns the ASCII value of the first character of the
string <str>. Returns 0 if <str> is the empty string.
Returns NULL if <str> is NULL.
259
Date and Time Functions:
SNo Name & Syntax Description
1 CURDATE() Returns the current date in YYYY-MM-DD format or

YYYYMMDD format, depending on whether the
function is used in a string or numeric context.
2 NOW() Returns the current date and time in 'YYYY-MM-DD

HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format,
depending on whether the function is used in a string
or numeric context.
3 SYSDATE() Returns the current date and time in 'YYYY-MM-DD

HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format,
depending on whether the function is used in a string
or numeric context.
4 DATE(expr) Extracts the date part of a date or datetime expression

<expr>.
5 MONTH(date) Returns the numeric month from the specified date, in

the range 0 to 12. It returns 0 for dates such as '0000-
00-00' or '2010-00-00' that have a zero month part.
6 YEAR(date) Returns the year for specified date in the range 0 to

9999. It returns 0 for the "zero" date. Returns values
like 1998, 2010,1996 etc.
7 DAYNAME(date) It returns the name of the weekday for the specified

date.
8 DAYOFMONTH(date) Returns the day of the month in the range 0 to 31.
9 DAYOFWEEK(date) Returns the day of week in number as 1 for Sunday, 2

for Monday and so on.
10 DAYOFYEAR(date) Return the day of the year for the given date in
numeric format in the range 1 to 366.
260
A few examples of these functions are given below:

1. Suppose a book with AccNo 1002 is lost by a student. The school charges a fine for the
lost book. The fine is 10% of the book price. We wish to write a statement to
calculate the fine rounded up to 2 DECIMAL places. The statement is
SELECT AccNo, ROUND(Price*10/100, 2) AS Fine
FROM Books WHERE AccNo = 1002;
2. Suppose we want to create another table and one column in that table will be
'Author'. In order to optimally utilize the storage space, we wish to find out what is
the maximum length of 'Author' in the table 'Books'. This value will guide us in
deciding the field width of 'Author' in the new table. So we enter the statement:
SELECT Author, LENGTH(Author)
FROM Books ORDER BY LENGTH(Author);
3. In order to produce a report we wish to display the book titles in upper case letters of
the alphabet. So we enter the statement:
SELECT AccNo, UCASE(Title) FROM Books;
4. Suppose a book with accession number 1050 is returned and we wish to enter the
current date as the ReturnDate in the table Students. We can give the statement:
UPDATE Students
SET ReturnDate = CURDATE()
WHERE AccNo = 1050;
5. Suppose we wish to find out which staff members got the books issued in the
previous calender year, then we can give the statement:
SELECT * FROM Staff WHERE YEAR(IssueDate) =
YEAR(CURDATE())-1;
What Next?
So far you have learnt how to work with single table at a time. None of the operations
discussed in class XI or in this lesson required us to access data simultaneously from
multiple tables. But in real life database implementations we have to access data from
multiple tables simultaneously, as will be clear from the following examples.
261
Try to write queries for the following tasks in Library database:

(i) For all the books which have not been returned by the students, display the
AccNO and Title of the book.
(ii) Display the details of all the books by the publisher 'APH' which are issued to
the Staff.
(iii) Display the Price of each book which has been issued to the students but has
not been returned.
If you observe carefully, you will find that these queries need to access data from two
tables simultaneously. You have not yet studied how to do this.
In class XII, you will study SQL commands using which you will be able to do all such jobs
and a lot more. For example:
(iv) Display the highest and the lowest values from Price column of the table
Books.
(v) Display the Total number of books in the library whose edition is before 2001.
(vi) To cancel the operations done while working in MySQL.
(vii) To make the operations done permanent so that even in the case of system
failure the work done stays.
(viii) To make sure that duplicate values are not accepted in the primary key
column of a table.
(ix) To make sure that NULL is not accepted for any column in which we do not
want to allow NULLs.
Summary:
• In an RDBMS data is stored in tables.
• A table refers to a two dimensional representation of data arranged in columns
and rows.
• SQL (Structured Query Language) is used to manage databases in an RDBMS.
• SQL commands are divided into different categories: DDL, DML, and DCL.
• CREATE command is used to create databases and tables.
262
• The DESCRIBE command is used to view the structure of a table.

• ALTER TABLE statement is used to change the structure of a table ie. to add,
remove or change its column(s).
• The SELECT command is used to fetch data from tables.
• The keyword DISTINCT is used to eliminate display of duplicate data.
• The WHERE clause is used to select specific rows.
• The BETWEEN operator defines the range of values that a column values must
fall into to make the condition true.
• The IN operator selects values that match any value in the given list of values.
• % and _ are two wild card characters. The percent (%) symbol is used to
represent any sequence of zero or more characters. The underscore (_) symbol
is used to represent a single character.
• NULL means a value that is unavailable, unassigned, unknown or inapplicable.
• The results of the SELECT command can be displayed in the ascending or
descending values of a column or columns using ORDER BY clause.
• UPDATE command is used to modify existing data in a table.
• DELETE command is used to delete rows from a table.
EXERCISES
Complete the following cross-word puzzle using SQL commands and clauses:
1 2 3
4 5 6
7 8
10
263
Clues:
Across Down
4. Remains same until you change 1. Show the table structure

the no. of columns in the table.
7. Show me the strings following a 2. Destroy the table

specific pattern.
9. It contains data 3. Display the table data
10. To add another column to the table 5. I don't want data in this table
6. It contains tables
8. Populate the table
MULTIPLE CHOICE QUESTIONS
1. MySQL is a /an
a. Relational Database b. Database
c. RDBMS d. Table
2. USE <databasename> command
a. is not used in MySQL b. is given before quitting MySQL
c. opens a table d. opens a database
3. Number of columns in a table is called
a. Power b. Degree
c. Cardinality d. Design
4. A database
a. Contains tables b. Is a part of a table
c. Is same as a table d. Removes data from a table
264
5. Number of rows in a table is called
a. Power b. Degree
c. Cardinality d. Design
6. ORDER BY clause is used to sort data
a. in ascending order b. in desceding order
c. both (a) and (b) d. None of the above
7. A table can have
a. Only one candidate key b. Only one primary key
c. Only one alternate key d. No alternate key
8. Wild card characters are used
a. In LIKE clause b. In ORDER BY clause
c. In BETWEEN clause d. In IN clause
9. DDL is
a. a part of SQL b. a part of DML
c. a part of DCL d. None of the above
10. LIKE clause is used
a. For pattern matching b. For table matching
c. For inserting similar data in a table d. For deleting data from a table
265
LAB EXERCISES
Consider a database LOANS with the following table:
Table: Loan_Accounts
AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date Interest
1 R.K. Gupta 300000 36 12.00 19-07-2009
2 S.P. Sharma 500000 48 10.00 22-03-2008
3 K.P. Jain 300000 36 NULL 08-03-2007
4 M.P. Yadav 800000 60 10.00 06-12-2008
5 S.P. Sinha 200000 36 12.50 03-01-2010
6 P. Sharma 700000 60 12.50 05-06-2008
7 K.S. Dhall 500000 48 NULL 05-03-2008
Write SQL commands for the tasks 1 to 35 and write the output for the SQL commands 36
to 40:
Create Database and use it
1. Create the database LOANS.
2. Use the database LOANS.
Create Table / Insert Into
3. Create the table Loan_Accounts and insert tuples in it.
Simple Select
4. Display the details of all the loans.
5. Display the AccNo, Cust_Name, and Loan_Amount of all the loans.
Conditional Select using Where Clause
6. Display the details of all the loans with less than 40 instalments.
7. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.
8. Display the Int_Rate of all the loans started after 01-04-2009.
266
Using NULL
9. Display the details of all the loans whose rate of interest is NULL.
10. Display the details of all the loans whose rate of interest is not NULL.
Using DISTINCT Clause
11. Display the amounts of various loans from the table Loan_Accounts. A loan amount
should appear only once.
12. Display the number of instalments of various loans from the table Loan_Accounts. An
instalment should appear only once..
Using Logical Operators (NOT, AND, OR)
13. Display the details of all the loans started after 31-12-2008 for which the number of
instalments are more than 36.
14. Display the Cust_Name and Loan_Amount for all the loans which do not have number
of instalments 36.
15. Display the Cust_Name and Loan_Amount for all the loans for which the loan amount
is less than 500000 or int_rate is more than 12.
16. Display the details of all the loans which started in the year 2009.
17. Display the details of all the loans whose Loan_Amount is in the range 400000 to
500000.
18. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
Using IN Operator
19. Display the Cust_Name and Loan_Amount for all the loans for which the number of
instalments are 24, 36, or 48. (Using IN operator)
Using BETWEEN Operator
20. Display the details of all the loans whose Loan_Amount is in the range 400000 to
500000. (Using BETWEEN operator)
21. Display the details of all the loans whose rate of interest is in the range 11% to 12%.
(Using BETWEEN operator)
267
Using LIKE Operator
22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the
Cust_Name ends with 'Sharma'.
Cust_Name ends with 'a'.
Cust_Name contains 'a'
Cust_Name does not contain 'P'.
Cust_Name contains 'a' as the second last character.
Using ORDER BY clause
27. Display the details of all the loans in the ascending order of their Loan_Amount.
28. Display the details of all the loans in the descending order of their Start_Date.
29. Display the details of all the loans in the ascending order of their Loan_Amount and
within Loan_Amount in the descending order of their Start_Date.
Using UPDATE, DELETE, ALTER TABLE
30. Put the interest rate 11.50% for all the loans for which interest rate is NULL.
31. Increase the interest rate by 0.5% for all the loans for which the loan amount is more
than 400000.
32. For each loan replace Interest with (Loan_Amount*Int_Rate*Instalments) 12*100.
33. Delete the records of all the loans whose start date is before 2007.
34. Delete the records of all the loans of 'K.P. Jain'
35. Add another column Category of type CHAR(1) in the Loan table.
268
Find the Output of the following queries
36. SELECT cust_name, LENGTH(Cust_Name), LCASE(Cust_Name), UCASE(Cust_Name)

FROM Loan_Accounts WHERE Int_Rate < 11.00;
37. SELECT LEFT(Cust_Name, 3), Right(Cust_Name, 3), SUBSTR(Cust_Name, 1, 3) FROM

Loan_Accounts WHERE Int_Rate > 10.00;
38. SELECT RIGHT(Cust_Name, 3), SUBSTR(Cust_Name, 5) FROM Loan_Accounts;
39. SELECT DAYNAME(Start_Date) FROM Loan_Accounts;
40. SELECT ROUND(Int_Rate*110/100, 2) FROM Loan_Account WHERE Int_Rate > 10;
Write the output produced by the following SQL commands:
41. SELECT POW(4,3), POW(3,4);
42. SELECT ROUND(543.5694,2), ROUND(543.5694), ROUND(543.5694,-1);
43. SELECT TRUNCATE(543.5694,2), TRUNCATE(543.5694,-1);
44. SELECT LENGTH("Prof. M. L. Sharma");
45. SELECT CONCAT("SHEIKH", " HAROON") "FULL NAME";
46. SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE());
47. SELECT DAYOFYEAR(CURDATE()), DAYOFMONTH(CURDATE()),

DAYNAME(CURDATE());
48. SELECT LEFT("Unicode",3), RIGHT("Unicode",4);
49. SELECT INSTR("UNICODE","CO"), INSTR("UNICODE","CD");
50. SELECT MID("Informatics",3,4), SUBSTR("Practices",3);
269

CBSE Class 12 Informatic Practices MySQL

Uploaded by

Copyright:

Available Formats

CBSE Class 12 Informatic Practices MySQL

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

CBSE Class 12 Informatic Practices MySQL

Uploaded by

Copyright:

Available Formats

Downloaded from www.studiestoday.

MySQL - REVISION TOUR

After studying this lesson the students will be able to:

• Recall the terminology used in RDBMS concepts.

• Realize that much more is to be learnt for effective use of databases.

1. Database: A database is an organised collection of data.

3. RDBMS: A DBMS used to manage Relational Databases is called an RDBMS (Relational

MySQL - REVISION TOUR

4. Benefits of using a DBMS are:

Think of the functioning of a computerised school library.

MySQL - REVISION TOUR

AccNo Title Author Publisher Edition Price

AccNo is Accession Number of a book.

AdmNo LibCardNo AccNo IssueDate ReturnDate

2010001 101 1002 2009-12-02 2009-12-08

2009012 102 1050 2010-02-16 NULL

2009013 103 1060 2010-02-16 NULL

EmpNo AccNo IssueDate ReturnDate

1 1001 2009-04-13 NULL

2 NULL NULL NULL

2 NULL NULL NULL

These three related tables collectively form the Library database.

MySQL - REVISION TOUR

Table Primary Key Reason

BOOKS AccNo No two books have the same accession number.

STUDENTS AdmNo No two students have the same admission number.

STAFF -- As there is no column for which each row has a

MySQL - REVISION TOUR

Working with MySQL

SNO Command, Syntax, and Purpose Category

MySQL - REVISION TOUR

3. Command: ALTER TABLE DDL

Syntax: ALTER TABLE <tablename>

ADD <columnname> <datatype>;

ALTER TABLE <tablename>

ALTER TABLE <tablename>

MODIFY <column> <new_definition>;

Purpose: Modifies the structure of a table

4. Command: USE DML

Syntax: USE <databasename>;

Purpose: Opens the specified database for use.

5. Command: SELECT DATABASE() DML

Syntax: SELECT DATABASE();

Purpose: Shows the name of the current database

6. Command: SHOW TABLES DML

Syntax: SHOW TABLES;

Purpose: Shows a list of tables present in the current

7. Command: INSERT DML

Syntax: INSERT INTO <tablename>

[<column1>, <column2>, ..., <columnn>]

VALUES (<value1>, <value2>, ... <value n>);

Purpose: Inserts data into a table

MySQL - REVISION TOUR

8. Command: SELECT DML

Syntax: SELECT <* / column name / expression> ,

[<column name/Expression list>]

FROM <table name>

[ORDER BY <column name / expression>

There are multiple ways to use SELECT.