L-8 Intoduction to MySQL

● MySQL is a open source Relational Database Management System. MySQL is very

fast reliable and flexible Database Management System. It provides a very high
performance and it is multi threaded and multi user Relational Database management
system.
● SQL - Structured Query Language. A non-procedural UGL used for querying upon
relational database.
● DDL - Data Definition Language. SQL part-language that facilitates defining creation/
modification etc. of database objects such as tables, indexes, sequences etc.
● DML - Data Manipulation Language. SQL part-language that facilitates manipulation
(addition/ deletion/ modification) of data residing in database object.
# CREATE DATABASE (Database name) ; - creates a new database
# CREATE TABLE <Table name> (Field1 datatype(size) constraint, field2 datatype(size)
constraint);
- creates a new table
# The INSERT INTO Statement
The INSERT INTO statement is used to insert a new row in a table.
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.
The first form doesn't specify the column names where the data will be inserted, only their
values:
INSERT INTO table_name
VALUES (value1, value2,
value3,...)
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2,
column3,...)
VALUES (value1, value2, value3,...)
# SQL SELECT Statement
The SELECT statement is used to select data from a database. The result is stored in
a result table, called the result-set. Syntax
SELECT
column_name(s)
FROM table_name
and
SELECT * FROM
table_name

# The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified
criterion.
SQL WHERE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator
value
# Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEE
Between an inclusive range
N
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the
columns
Note: In some versions of SQL the <> operator may be written as !=

#The AND & OR Operators

The AND & OR operators are used to filter records based on more than one condition. The
AND operator displays a record if both the first condition and the second condition is true.
And OR operator displays a record if either the first condition or the second condition is true.

# The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.The ORDER
BY keyword sort the records in ascending order by default. If you want to sort the records in
a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|
DESC
Questions:
1. Match the following clauses with their respective functions.
ALTER Insert the values in a table
UPDATE Restrictions on columns
DELETE Table definition
INSERT INTO Change the name of a column
Update existing information
CONSTRAINTS in a table
Delete an existing row from a
DESC table
CREATE Create a database

2. What are views? How are they useful?

3. What is SQL? What are different categories of commands available in SQL?
 4. What is MySQL? What are its functions and features?

Application based Questions:

1. Suppose your school management has decided to conduct cricket matches between
students of class XI and Class XII. Students of each class are asked to join any one of
the four teams — Team Titan, Team Rockers, Team Magnet and Team Hurricane.
During summer vacations, various matches will be conducted between these teams.
Help your sports teacher to do the following:

a) Create a database “Sports”.

b) Create a table “TEAM” with following considerations:
i) It should have a column TeamID for storing an integer value between 1 to 9, which
refers to unique identification of a team.
ii) Each TeamID should have its associated name (TeamName), which should be a
string of length not less than 10 characters.
c) Show the structure of the table TEAM using SQL command.
d) As per the preferences of the students four teams were formed as given below. Insert
these two rows in TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
e) Show the contents of the table TEAM.

2. Write command to create table Employee with the following structure:

Name of ID First_Name Last_Name User_ID Salary
Column
Type Number(4) Varchar(30) Varchar(30) Varchar(10) Number(9,2)

Ensure the following specification in created table:

● ID should be declared as Primary Key
● User_ID should be unique
● Salary Must be greater than 5000
● First_Name and Lst_Name must not remain Blank

3. Consider the table Hospital given below.

Hospital

No Name Age DEPARTMENT DateOfAdm Charges Sex

1 Sandeep 64 Surgery 23/02/2008 300 M
2 Ravina 24 Orthopedic 20/01/2008 200 F
3 Karan 45 Orthopedic 10/02/2008 200 M
4 Tarun 12 Surgery 01/01/2008 300 F
5 Zubin 36 ENT 12/01/2008 250 M
6 Ketaki 16 ENT 12/02/2008 300 F
7 Ankita 29 Cardiology 20/02/2008 800 F
 8 Zareen 45 Gynecology 22/02/2008 Null F
9 Kush 19 Cardiology 13/01/2008 800 M
10 Shailya 31 Medicine 19/02/2008 400 F

Write commands in SQL for (1) to (4).

1. To show all information about the patients of cardiology department.

2. To list the names of female patients who are in orthopaedic department.
3. To display the details of patients with Age > 30.
4. To display the records of patients whose name starts with ‘K’.

Find out the output for SQL commands (5) to (6).

5. SELECT Department FROM HOSPITAL ;

6. SELECT Name,Age FROM HOSPITAL WHERE SEX=’M’;

4. Consider the below given table and write queries for (i) to (iii) and output of (iv) – (vii):
Table Name: Pet
Name Owner Species Gender Age
Monty Aditya Dog M 4
Badal Dev Horse M 4
Moti Motisingh Dog M 3
Mittu Harsh Parrot M 2
Pinky Kartvya Cat F 1
Sweety Vyas Cat F 2

i) Display name, owner and gender for all dogs.

ii) Display Name, owner and age of all pets whose age is more 2 years.
iii) Display name, owner, gender, and age for all pets.
iv) select name, owner from pet where owner name like ‘%ya’;
v) select name, age from pet where species = dog and age between 1 and 3;
vi) select * from pets where species in (‘horse’,’parrot’);
vii) select name, owner, species from pets where gender =’M’ or gender = ‘F’;

5. Consider a database LOANS with the following table:

Table: Loan_Accounts

AccNo Cust_Name Loan_Amount Instalments Int_Rate Start_Date

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
Answer the following questions.

1. Create Database and use it

2. Create Table / Insert Into

3. Display the details of all the loans.

4 Display the details of all the loans with less than 40 instalments.
5. Display the AccNo and Loan_Amount of all the loans started before 01-04-2009.

6. Display theInt_Rate of all the loans started after 01-04-2009.

7. Display the details of all the loans whose rate of interest is NULL.

8. Display the details of all the loans whose rate of interest is not NULL.