chapter 8: mysql revision tour … · chapter 8: mysql revision tour page 2 of 16 i. alternate key:...

Chapter 8: MySQL Revision Tour

of 16

Q1. What is a Database?

Ans: A Database is an organized collection of related information that supports for easy access,

modification and maintenance.

Q2. What is a DBMS?.

Ans: Database is managed by special software packages known as Database Management

Systems (DBMSs). The purpose of DBMSs software is to allow the user to create, modify and

administration of database.

Q3. What is a RDBMS? Name some popular RDBMS

Ans: In the Relational data model, database is represented as a collection of related Tables. Database

management software used to manage Relational Databases is called a Relational Data Base

Management System. Examples of Relational database management systems are:

MS-Access, MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, SAP, dBase, FoxPro, etc.

Q4. What are the benefits of using a DBMS?

Ans: Benefits of DBMS are:

a. Redundancy (duplicity) can be controlled

b. Inconsistency can be avoided

c. Data can be shared

d. Security restrictions can be applied.

Q5. Explain the following terms

A. Relation: A relation means a 'Table', in which data is organized in the form of rows and

columns.

Relation: Student

ADNO NAME CLASS SECTION AVERAGE

101 Anu 12 A 85

105 Balu 12 D 65

203 Leena 11 B 95

205 Madhu 10 B 70

305 Surpreeth 9 C 70

483 Usha 6 A 60

B. Tuple/Record/Row:

Horizontal subset of information in a table is called Tuple. Example:

101 Anu 12 A 85

C. Attribute /Field/Column/Key:

Vertical subset of information in a table is called Attribute. Example:

ADNO , NAME , CLASS , SECTION , AVERAGE

D. Domain: A domain is defined as the set of all unique values permitted for a column. A domain

of date column is the set of valid dates, a domain of integer column is whole numbers, a domain

of SECTION is ('A','B','C','D')

E. Cardinality: Cardinality is number of rows (tuples) in a table.

Example: Cardinality of Relation Student is 6 (Number of rows).

F. Degree: The degree is the number of attributes (columns) in a table.

Example: Degree of Relation Student is 5 (Number of columns).

G. Candidate Key: Candidate keys are all those columns in a relation which have unique values

and hence are eligible to act as a primary key. Example: In the above table, AdNo and Name

both have unique values. Therefore, both AdNo and Name are candidate keys.

H. Primary Key: A column or set of columns that uniquely identify a row within a table is called

its primary key. Example: Out of the candidate keys: AdNo and Name, only AdNo is selected

as the primary key.


of 16

I. Alternate Key: Out of the candidate keys, after selecting one primary key, the remaining

candidate keys which are not made primary key are called alternate key. Example: From the

candidate keys, AdNo is selected as the primary key, therefore the remaining candidate key is

Name and it is the Alternate key.

J. Foreign Key: A foreign key is a column in one table that uniquely identifies a row of another

table. In other words, a foreign key is a column or a combination of columns that is used to

establish a link between two tables.

Q6. What is SQL? What is its use?

Ans: Structured Query Language (SQL) is a standard language used for accessing databases.

This is a special purpose programming language used to create a table, manage data and

manipulate data.

Q7. What are the advantages of SQL?

1) SQL is portable running in all servers, mainframes, PCs, laptops, and even mobile phones.

2) High speed SQL queries can retrieve large amounts of information quickly and efficiently.

3) SQL is easy to learn and understand.

4) SQL is used with Relational databases with any vendor to create, manage and secure

databases.

6) SQL acts as both Programming language and Interactive language.

7) It provides Client Server Architecture.

8) Supports Object Based Programming and is highly flexible.

Q8. What are the different types of SQL statements?

1. Data Definition Language (DDL) statements is a set of commands that defines the different

structures in a database. DDL statements are used to create databases and table structures,

modify the existing structure of the table and remove the existing table. Some of the DDL

statements are CREATE TABLE, ALTER TABLE and DROP TABLE.

2. Data Manipulation Language (DML) statements are used to access and manipulate data in

existing tables. The manipulation includes inserting data into tables, deleting data from the

tables, retrieving data and modifying the existing data. The common DML statements are

SELECT, UPDATE, DELETE and INSERT.

3. Data Control Language (DCL): This is a category of SQL commands which are used to

control the access to databases and tables. Examples of DCL commands are - GRANT,

REVOKE.

MySQL Commands

1. CREATE DATABASE: (DDL) It creates a database with the specified name.

CREATE DATABASE databasename;

2. SHOW DATABASES: (DML) It shows a list of databases present in the system.

SHOW DATABASES;

3. USE: (DML) It opens the specified database for use.

USE databasename;

4. SELECT DATABASE() :It Shows the name of the current database (DML)

SELECT DATABASE();

5. SHOW TABLES: (DML) It shows a list of tables present in the current database.

SHOW TABLES;

6. CREATE TABLE: (DDL) It creates a table with the specified name. We need to specify the

column names, data types, size and constraints for each column. Table and Column names

cannot start with a digit but can contain both letters and numbers. Spaces and symbols are not

allowed except underscore (_).


of 16

CREATE TABLE tablename

(

columnname1 datatype(size) constraint,

columnname2 datatype(size) constraint,

….

ColumnnameN datatype(size) constraint

);

Note: Do not put a comma after last column information

Data types available in MySQL:

1. CHAR: CHAR is used for storing fix length character strings. String values will be right

padded with spaces to bring it up to some fixed size before they are stored on the disk.

Thus it wastes a lot of disk space.

Format: CHAR(n)

2. VARCHAR: Varchar is used for variable character string. It can have maximum of 2000

characters.

Format: VARCHAR (n)

3. DECIMAL or NUMERIC: Decimal and numeric data types have fixed precision and scale.

Format: DECIMAL (p , s) and NUMERIC (p , s)

'p' is the precision or the total number of significant places including one place for decimal

point. 's' is the scale or the number of digits after the decimal point.

Example, a column defined with DECIMAL(5,2) would allow the value: 123.45 (p=5, s=2)

and DECIMAL(4,2) would allow the value: 12.34 (p=4,s=2)

4. INT/INTEGER: The INT data type is the integer data type in SQL. This is used to store

whole number (without any fraction part).

5. DATE is used to store valid date values, which is ranging from January 1, 4712 BC to

December 31, 9999 AD. The date values are in the format YYYY-MM-DD

Format: DATE

Constraints available in MySQL:

PRIMARY KEY: A PRIMARY KEY constraint for a table enforces the table to accept unique

values for a specific column

NOT NULL: A NOT NULL constraint allows to specify that a column can not contain any

NULL value. Ques Create a table student with the following structure:

Fields Data Type Constraints admno numeric(5) PRIMARY KEY name varchar(20) NOT NULL class integer(2) section char(1) dob date fees numeric(7,2)

create table student ( admno numeric(5) primary key, name varchar(20) not null, class integer(2), section char(1), dob date, fees numeric(7,2) );

7. DESCRIBE : (DML) It shows the structure of a table.

DESCRIBE tablename;

Example: desc student;


of 16

+---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | admno | decimal(5,0) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | class | integer(2) | YES | | NULL | | | section | char(1) | YES | | NULL | | | dob | date | YES | | NULL | | | fees | decimal(7,2) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+

8. INSERT : (DML) It inserts a data Row into the table

INSERT INTO tablename (column1, column2, ..., column)

VALUES (value1, value2, ...valueN);

We can insert only one Row at a time. Char, Varchar and Date values are enclosed in single

quotes ‘’. Numbers are written normally. Dates are specified in the format ‘YYYY-MM-DD’. To

insert the current date into a table, MySQL's built-in function CURDATE() can be used. Ques Insert the following records in the table student: +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | | 777 | Leena | NULL | NULL | 2000-03-05 | NULL | +-------+--------------+-------+---------+------------+---------+ insert into student values (111, 'Anu Jain', 12, 'A', '2000-12-31',2500); insert into student values (222, 'Mohit Sharma', 11, 'B', '2001-12-31',4500); insert into student values (333, 'K.P. Gupta', 12, 'B', '2000-01-11',3000); insert into student values (444, 'Ajit Kumar', 10, 'A', '2002-03-04',2000); insert into student values (555, 'Nandini', 12, 'C', '2000-03-04',3000); insert into student values (666, 'Rohan Sharma', 11, 'B', '2001-12-05',2500);

Explicitly Inserting NULL Values : If a column can hold NULL values, its value can be omitted

from the INSERT INTO statement. You need to specify the corresponding column names whose

values are specified in INSERT INTO statement, NULL value will automatically be inserted in the

rest of the columns. insert into student (admno, name, dob) values (777, 'Leena ', '2000-03-05');

9. SELECT: (DML) It retrieves data from a table

SELECT * / columnnames / expressions as “alias name”

FROM tablename

WHERE condition

ORDER BY columnname ASC/DESC;

Retrieving all Columns

Ques: Display all information from student table. Select * from student; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | | 777 | Leena | NULL | NULL | 2000-03-05 | NULL | +-------+--------------+-------+---------+------------+---------+


of 16

Retrieving Selected Columns

Ques: Display name and class information from student table. Select name , class from student; +--------------+-------+ | name | class | +--------------+-------+ | Anu Jain | 12 | | Mohit Sharma | 11 | | K.P. Gupta | 12 | | Ajit Kumar | 10 | | Nandini | 12 | | Rohan Sharma | 11 | | Leena | NULL | +--------------+-------+

Using Arithmetic Operators with SELECT

Using arithmetic operators on tables does not create new columns in the tables or change the

actual data values. The results of the calculations appear only in the output.

Ques: Display fees + 500 as increased fees for all students Select admno , name , fees + 500 from student; +-------+--------------+------------+ | admno | name | fees + 500 | +-------+--------------+------------+ | 111 | Anu Jain | 3000.00 | | 222 | Mohit Sharma | 5000.00 | | 333 | K.P. Gupta | 3500.00 | | 444 | Ajit Kumar | 2500.00 | | 555 | Nandini | 3500.00 | | 666 | Rohan Sharma | 3000.00 | | 777 | Leena | NULL | +-------+--------------+------------+

Note: : The value of fees stored in the table remains the same.

Using Column Alias

Column alias gives a different heading to appear in output instead of the actual column. If a

Column alias consists of more than one word , then it should be enclosed in quotes. Using

Column Alias does not rename a column. It simply displays a different column name in the

output. The AS keyword between the column name and alias is optional

Ques: Display fees * 12 as annual fees for all students Select admno, name, fees * 12 as "annual fees" from student; +-------+--------------+--------------+ | admno | name | annual fees | +-------+--------------+--------------+ | 111 | Anu Jain | 30000.00 | | 222 | Mohit Sharma | 54000.00 | | 333 | K.P. Gupta | 36000.00 | | 444 | Ajit Kumar | 24000.00 | | 555 | Nandini | 36000.00 | | 666 | Rohan Sharma | 30000.00 | | 777 | Leena | NULL | +-------+--------------+--------------+

Ques: Display fine as 10% of fees for all students Select admno , name , fees*10/100 as fine from student;

+ Addition

- Subtraction

* Multiplication

/ Division

% Modulus ( remainder)


of 16

+-------+--------------+------------+ | admno | name | fine | +-------+--------------+------------+ | 111 | Anu Jain | 250.000000 | | 222 | Mohit Sharma | 450.000000 | | 333 | K.P. Gupta | 300.000000 | | 444 | Ajit Kumar | 200.000000 | | 555 | Nandini | 300.000000 | | 666 | Rohan Sharma | 250.000000 | | 777 | Leena | NULL | +-------+--------------+------------+

Putting text in Query output

Query output can be made more presentable by inserting items such as symbols or text in the

query output

Ques: Display details of all students in the format : Name studies in class-Section Select name, 'studies in' , class , '-' , section from student; +--------------+------------+-------+---+---------+ | name | studies in | class | - | section | +--------------+------------+-------+---+---------+ | Anu Jain | studies in | 12 | - | A | | Mohit Sharma | studies in | 11 | - | B | | K.P. Gupta | studies in | 12 | - | B | | Ajit Kumar | studies in | 10 | - | A | | Nandini | studies in | 12 | - | C | | Rohan Sharma | studies in | 11 | - | B | | Leena | studies in | NULL | - | NULL | +--------------+------------+-------+---+---------+

Retrieving specific rows using WHERE clause

WHERE clause is used to filter records. It is used to extract only those records that fulfill a

specified criterion. Where condition in the SELECT command can be formed with the following

a) Relational Operators are used to compare two values. The result of the comparison is True

or False


FROM tablename

WHERE columnname >= Value;

Ques: Display students' name, who are paying below 3000 fees. Select name from student where fees < 3000; +--------------+ | name | +--------------+ | Anu Jain | | Ajit Kumar | | Rohan Sharma | +--------------+

Ques: Display students' name, who are paying above or equal to 3000 fees. Select name from student where fees >= 3000; +--------------+ | name | +--------------+ | Mohit Sharma | | K.P. Gupta | | Nandini | +--------------+

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

!= or <> Not equal to


of 16

Ques: Display students' information, who are not in class 10 Select * from student where class != 10; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | +-------+--------------+-------+---------+------------+---------+

b) Logical Operators ( NOT, AND, OR) :

NOT negates a condition. If a condition returns a True value, adding NOT causes the condition

to return a False value and vice versa. AND and OR are used to connect relational expressions

in the WHERE clause. AND requires both conditions to be true in order to return TRUE but

OR returns TRUE If any of the comparisons are true, The symbol || can be used in place of

OR, && can be used in place of AND , ! can be used in place of NOT operator.


FROM tablename

WHERE NOT (condition);


FROM tablename

WHERE condition1 AND/OR condition2;

Ques: Display students' information, who are not in class 11 Select * from student where NOT (class = 11); +-------+------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | +-------+------------+-------+---------+------------+---------+

Ques: Display information of students in class 11B. Select * from student where class = 11 AND section = ’B’; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | +-------+--------------+-------+---------+------------+---------+

Ques: Display 11th and 12th class students' information. Select * from student where class = 11 OR class = 12; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | +-------+--------------+-------+---------+------------+---------+


of 16

c) Condition based on Range : BETWEEN/NOT BETWEEN operator is 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.


FROM tablename

WHERE columnname BETWEEN lowerValue AND UpperValue;

Ques: Display students' information, who are paying fees between 2500 and 3500. Select * from student where fees BETWEEN 2500 AND 3500; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | +-------+--------------+-------+---------+------------+---------+

d) Condition based on a List : IN/NOT IN operator is used to select values that match any

value in a list of specified values.


FROM tablename

WHERE columnname IN (value1, value2, …..valueN);

Ques: Display students' information, who are in section A,B and D. Select * from student where section IN ('A','B','C'); +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | +-------+--------------+-------+---------+------------+---------+

e) Condition based on pattern matches : LIKE/NOT LIKE clause is used for pattern

matching of string data using wildcard characters

%( to match zero or many characters) and _( to match one character) .

The symbols can be used in combinations. Some examples :

'Am%' matches any string starting with Am.

'%Singh%' matches any string containing 'Singh'

'%a' matches any string ending with 'a'

'_ _ _' matches any string that is exactly 3 characters long.

'_ _ %' matches any string that has at least 2 characters.

'_ _ _ g' matches any string that is 4 characters long with any 3 characters

in the beginning but 'g' as the 4th character.


FROM tablename

WHERE columnname LIKE ‘pattern’;

Ques: Display the names that start with letter "A". select * from student where name like 'A%'; +-------+------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+------------+-------+---------+------------+---------+ | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | +-------+------------+-------+---------+------------+---------+


of 16

Ques: Display names, whose name's second letter is 'o'. select * from student where name like '_o%'; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | +-------+--------------+-------+---------+------------+---------+

Ques: Display names, whose name has 7 characters. select * from student where name like '_ _ _ _ _ _ _'; +-------+---------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+---------+-------+---------+------------+---------+ | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | +-------+---------+-------+---------+------------+---------+

f) Checking missing values : IS NULL / IS NOT NULL is used to select rows in which the

specified column IS NULL or IS NOT NULL


FROM tablename

WHERE columnname IS NULL;

NULL represents a value that is unavailable, unassigned, unknown or inapplicable. NULL is

not same as a Zero. Since Zero (0) is an integer value but NULL is nothing. Relational

Operators (= and != ) are not allowed with NULL. If any column value involved in an

arithmetic expression is NULL, the result of the arithmetic expression is also NULL.

Ques: Display students' information, whose class is missing. select * from student where class is null; +-------+--------+-------+---------+------------+------+ | admno | name | class | section | dob | fees | +-------+--------+-------+---------+------------+------+ | 777 | Leena | NULL | NULL | 2000-03-05 | NULL | +-------+--------+-------+---------+------------+------+

Sorting the Results- ORDER BY is used to display the query output in ascending or in

descending order of a single column or multiple columns


FROM tablename

WHERE condition

ORDER BY columnname1 ASC/DESC , columnname2 ASC/DESC;

Ques: Display students' information, in descending order of fees Select * from student order by fees desc; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | | 777 | Leena | NULL | NULL | 2000-03-05 | NULL | +-------+--------------+-------+---------+------------+---------+


of 16

Ques: Display students' information, in order of class. Select * from student order by class; +-------+--------------+-------+---------+------------+---------+ | admno | name | class | section | dob | fees | +-------+--------------+-------+---------+------------+---------+ | 777 | Leena | NULL | NULL | 2000-03-05 | NULL | | 444 | Ajit Kumar | 10 | A | 2002-03-04 | 2000.00 | | 222 | Mohit Sharma | 11 | B | 2001-12-31 | 4500.00 | | 666 | Rohan Sharma | 11 | B | 2001-12-05 | 2500.00 | | 111 | Anu Jain | 12 | A | 2000-12-31 | 2500.00 | | 333 | K.P. Gupta | 12 | B | 2000-01-11 | 3000.00 | | 555 | Nandini | 12 | C | 2000-03-04 | 3000.00 | +-------+--------------+-------+---------+------------+---------+

Sorting on Column Alias: If a Column alias is defined on a column, we can use it for displaying

rows in an ascending or descending order using ORDER BY clause:

SELECT columnname1, columnname2 as alias FROM tablename

WHERE condition ORDER BY alias;

Eliminating duplicate values : DISTINCT clause is used to display unique values from a

particular column of a table.

SELECT DISTINCT (column) FROM tablename ;

DISTINCT can be used with only one column at a time. When DISTINCT keyword is specified,

only one instance of the duplicated data is shown. If we write the keyword ALL in place of

DISTINCT, then the result of SELECT query displays all the values including duplicate values

Ques: Display different classes from student table. Select distinct class from student; +-------+ | class | +-------+ | 12 | | 11 | | 10 | | NULL | +-------+

Ques: Display class in student table. Select All class from student; +-------+ | class | +-------+ | 12 | | 11 | | 12 | | 10 | | 12 | | 11 | | NULL | +-------+

Precedence of Operators

1 !

2 -(unary minus)

3 ^

4 *, /, DIV, %, MOD

5 -, +

6 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, IN

7 BETWEEN,

8 NOT

9 &&, AND

10 ||, OR

11 = (assignment)


of 16

10. UPDATE: (DML) It updates/modifies data values in a table. UPDATE statement can be used to

update one or more columns together. WHERE clause helps in updation of particular rows in a

table

UPDATE tablename

SET columnname1 = value1, columnname2 = value2…

WHERE condition;

Ques: Increase fees value by 500 for all students Update student set fees = fees + 500;

Ques: Increase the fees value by 100 for admno 222 Update student set fees = fees + 100 where admno = 222;

11. DELETE :(DML)It Deletes a Row from a table. DELETE removes the entire row, not the

individual column values

DELETE FROM tablename WHERE condition;

Ques: Remove admno 444 information. Delete from student where admno = 444;

DELETE can be used to delete all rows of the table by skipping the where clause.

Ques: Remove all records. Delete from student;

12. ALTER TABLE :(DDL) It Modifies the structure of a table

ALTER TABLE tablename ADD columnname datatype(size);

ALTER TABLE tablename DROP columnname;

ALTER TABLE tablename MODIFY columnname newdatatype(size);

Ques: Add one new column totalfees with numeric(10,2) Alter table student add totalfees numeric(10,2);

After execution of the above ALTER TABLE statement, the totalfees column is added and a

NULL value is automatically assigned to all the existing rows in this column.

Ques: Change totalfees datatype as numeric(12,2) Alter table student modify totalfees numeric(12,2);

Ques: Remove totalfees column. Alter table student drop totalfees;

13. DROP TABLE :(DDL)It removes the entire structure of the table and information

DROP TABLE tablename; Example: Drop table student;

14. DROP DATABASE :(DDL)It removes the entire database from system.

DROP DATABASE databasename;

Differentiate between Update and Alter commands

Alter Table Update

It is a DDL statement It is a DML statement

Used to restructure the table Used to change the data values in

specified columns of all those records

which satisfy the specified condition

Differentiate between Delete and Drop commands

Drop Table Delete

It is a DDL statement It is a DML statement

Used to remove entire table structure

with contents

Used to remove the records which

satisfy the specified condition


of 16

Single Row Functions Multiple Row Functions

Single row functions operate on a single value

to return a single value

Multiple row functions operate on a set of rows to

return a single value.

When applied on a table, they return a single

result for every row of the table.

When applied on a table, they return a single

aggregate result for a group of rows in the table

Examples include POWER(), ROUND() and

LENGTH()

Examples include SUM(), AVG() and COUNT()

Worksheet of Single Row Functions in MySQL:

POWER(x,y) or POW(x,y) : Returns the value of x raised to the power of y.

1. SELECT POW (2, 4); Output:____________________

2. SELECT POW (2, -2); Output:____________________

3. SELECT POW (-2, 3); Output:____________________

ROUND( x )

:

Rounds the argument x to 0 decimal places

1. SELECT ROUND (-1.58); Output: -2

2. SELECT ROUND (-1.23); Output:____________________

3. SELECT ROUND (1.43); Output:____________________

ROUND( x, d )

:

Rounds the argument x to d decimal places. ( If the digit after the digit you want to round is 5 or greater then you increase the digit by 1 otherwise it remains same )

1. SELECT ROUND (6.298, 1); Output:____________________





6. SELECT ROUND (56.235, -1); Output: 60

7. SELECT ROUND (546.258, -1); Output:____________________

8. SELECT ROUND (546.258, -2); Output: 500

TRUNCATE( x , d )

:

Truncates the argument x to d decimal places.

1. SELECT TRUNCATE (6.298, 1); Output:____________________


3. SELECT TRUNCATE (56.235, -1); Output:____________________






ASCII(str)

:

Returns the ASCII value of the first character of

the string str .

Range ASCII Code

0 to 9 48 to 57

A to Z 65 to 90

a to z 97 to 122

Returns 0 if str is the empty string. Returns NULL

if str is NULL.

1. SELECT ASCII('2'); Output:____________________

2. SELECT ASCII('dx'); Output: 100

3. SELECT ASCII('A'); Output:____________________


of 16

LENGTH(str)

:

Returns the length of a string or a column

1. SELECT LENGTH('Informatics'); Output:____________________

CONCAT(str1,str2,str3...)

:

Returns the string by concatenating the

arguments.

1. SELECT CONCAT ('My', 'S', 'QL'); 2. Output:____________________

3. SELECT CONCAT ('Class', NULL, 'XI'); 4. Output: NULL

INSTR(str,substr)

:

Returns the position of the first occurrence of 2nd

string in 1st string

1. SELECT INSTR('Informatics', 'for'); Output:____________________

2. SELECT INSTR('Computers', 'pet'); Output: 0

LOWER(str) or LCASE(str)

:

Returns the argument str in lowercase.

1. SELECT LOWER('INFORMATICS'); Output:____________________

UPPER(str) or UCASE(str)

:

Returns the argument str in uppercase

1. SELECT UPPER('Informatics'); Output:____________________

LEFT(str,n)

:

Returns n characters from the left of string str

1. SELECT LEFT('Informatics', 3); Output:____________________

RIGHT(str,n)

:

Returns the n characters from right of the string str

1. SELECT RIGHT('Informatics', 4); Output:____________________

LTRIM(str)

:

Removes leading spaces, i.e. removes spaces from

the left side of the string str.

1. SELECT LTRIM(' Informatics'); Output:____________________

RTRIM(str)

:

Removes trailing spaces, i.e. removes spaces from

the right side of the string str.

1. SELECT RTRIM('Informatics '); Output:____________________

TRIM(str)

:

Removes both leading and trailing spaces

1. SELECT TRIM(' Informatics '); Output:____________________

SUBSTRING(str,m,n) or MID(str,m,n)

:

Returns n characters starting from the mth

character of the string str .

1. SELECT MID('Informatics', 3,4); Output:____________________

2. SELECT SUBSTRING('Informatics',4,3); Output:____________________

SUBSTR(str,m)

:

If the third argument is missing, then starting from

mth position, the rest of the string is returned.

1. SELECT SUBSTRING('Informatics',3); Output:____________________

SUBSTR(str,-m,n)

:

If m is negative, the beginning of the substring is

the mth character from the end of the string.

Returns n characters starting from the mth

character of the string str.

1. SELECT SUBSTRING('Computers', -3); Output:____________________

2. SELECT SUBSTRING('Computers', -5, 3); Output: Ute

(Starts at 5th char from back)


of 16

CURDATE()

:

Returns the current date in YYYY-MM-DD format

1. SELECT CURDATE(); Output:____________________

NOW()

:

Returns the current date and time in 'YYYY-MM-DD

HH:MM:SS'

1. SELECT NOW(); Output:____________________

SYSDATE()

:

Returns the current date and time in 'YYYY-MM-DD

HH:MM:SS'

1. SELECT SYSDATE(); Output:____________________

2. SELECT NOW(), SLEEP(5), NOW(); 2016-08-31 09:12:10 0 2016-08-31 09:12:10

(Static)

3. SELECT SYSDATE(), SLEEP(5), SYSDATE(); 2016-08-31 09:12:10 0 2016-08-31 09:12:15

(Dynamic )

DATE(expr)

:

Extracts the numeric date part of the specified

date, in the range 0 to 31.

1. SELECT DATE('2018-08-26 01:02:03'); Output:____________________

2. SELECT DATE(CURDATE()); Output:____________________

MONTH(date)

:

Returns the numeric month from the specified

date, in the range 0 to 12.

1. SELECT MONTH('2018-08-26'); Output:____________________

2. SELECT MONTH(CURDATE()); Output:____________________

YEAR(date)

:

Returns the year for specified date in the range 0

to 9999. Returns values like 1998, 2010, 1996 etc.

1. SELECT YEAR('2018-08-26'); Output:____________________

2. SELECT YEAR(CURDATE()); Output:____________________

DAYNAME(date)

:

It returns the name of the weekday for the

specified date. Eg Sunday, Monday and so on.

1. SELECT DAYNAME('2018-07-01'); Output:____________________

2. SELECT DAYNAME(CURDATE()); Output:____________________

DAYOFMONTH(date)

:

Returns the day of the month in the range 0 to 31.

1. SELECT DAYOFMONTH('2018-08-21'); Output:____________________

2. SELECT DAYOFMONTH(CURDATE()); Output:____________________

DAYOFWEEK(date)

:

Returns the day of week in number as 1 for

Sunday, 2 for Monday and so on.

1. SELECT DAYOFWEEK('2018-07-08'); Output:____________________

2. SELECT DAYOFWEEK(CURDATE()); Output:____________________

DAYOFYEAR(date)

:

Return the day of the year for the given date in

numeric format in the range 1 to 366

1. SELECT DAYOFYEAR('2018-08-21'); Output:____________________

2. SELECT DAYOFYEAR('2018-01-01'); Output:____________________

3. SELECT DAYOFYEAR(CURDATE()); Output:____________________


of 16

Revision Assignment

Consider a database LOANS with the following table:

Write SQL commands for the tasks 1 to 35

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.

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)


of 16

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)

Using LIKE Operator

22. Display the AccNo, Cust_Name, and Loan_Amount for all the loans for which the Cust_Name

ends with 'Sharma'.


ends with 'a'.


contains 'a'


does not contain 'P'.


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.

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);

chapter 8: mysql revision tour … · chapter 8: mysql revision tour page 2 of 16 i. alternate key:...

Documents