Prerna R.
Sharma
Ch-4(INTRODUCTION TO MYSQL)
EXERCISE
1. Write a short note on MySQL.
Ans- MySQL is a relational database management system (RDBMS). It is pronounced as "My
Sequel". MySQL was originally founded and developed in Sweden by David Axmark, Allan Larsson
and Michael Widenius, who had worked together since the 1980s.
2. Mention features of a DBMS.
Ans- 1.It stores data in a structured way.
2. Queries can be applied on the database
3. We can sort and manipulate the data in the database
4. We can validate the data entered and check for inconsistencies
5. It produces flexible reports, both on screen and on paper.
3. What is the difference between DBMS and RDBMS?
Ans-
DBMS RDBMS
1. Data is stored in a file 1. Data is stored in the form of tables.
2. DBMS supports a single user. 2. RDBMS supports multiple users.
3. Low levels of data security 3. There exists multiple levels of data security in an
RDBMS.
4. Data elements need to be accessed 4. Data elements can be easily accessed using SQL queries.
individually. You can also access multiple data elements with ease.
5. There is no relationship between 5. Data is stored in the form of tables which are related to
different data elements in a DBMS. each other using foreign keys.
6. DBMS does not support integrity 6. RDBMS supports integrity constraints.
constants.
4. List some features of MySQL.
Ans- 1. It requires no cost or payment for its usage.
2. MySQL has superior speed, is easy to use and is reliable.
3. MySQL uses a standard form of the well-known ANSI-SQL standards.
1
Prerna R. Sharma
4. MySQL is a platform independent application which works on many operating systems like
Windows, UNIX, LINUX etc. and has compatibility with many languages including JAVA , C++, PHP,
PERL, etc.
5. MySQL is an easy to install RDBMS and is capable of handling large data sets.
6. How is Primary Key different from Candidate Key?
ans-- Primary Key is a unique and non-null key which identify a record uniquely in table. A table can
have only one primary key. Candidate key is also a unique key to identify a record uniquely in a table but
a table can have multiple candidate keys.
Primary Key is a candidate key. A Candidate key may or may not be a primary key.
6. Define the key(s) used in MySQL.
- Ans- Primary Key – is a column or group of columns in a table that uniquely identify every row in that
table.
Candidate Key – is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key
with no repeated attributes.
Alternate Key – is a column or group of columns in a table that uniquely identify every row in that table.
Foreign Key – is a column that creates a relationship between two tables. The purpose of Foreign keys is
to maintain data integrity and allow navigation between two different instances of an entity.
7. State the similarity and difference between the Primary Key, Candidate Key, Alternate Key and
Foreign Key
Ans-
Similarities
- All these Keys are very important part of Relational database model. They are used to establish and
identify relationships between tables and also to uniquely identify any record or row of data inside a
table.
Differences
Primary Key is a unique and non-null key which identify a record uniquely in table.Candidate key is also a
unique key to identify a record uniquely in a table but a table can have multiple candidate keys. ...
There can be only one Primary key for a table. Therefore all the remaining Candidate keys are known as
Alternate or Secondary keys. They can also uniquely identify tuples in a table, but the database
administrator chooses one key as the Primary key.
The main difference between the primary key and foreign key is that it identifies each record in the
table, whereas the foreign key is used to link two tables together. A foreign key always matches the
primary key column in another table. It means a foreign key column in one table refers to the primary
key column of another table.
2
Prerna R. Sharma
8. Which statement is used to select a database and make it current?
Ans-To open the database and to make it current and work on it ‘USE’ statements are required.
USE <databasename>;
9. How is a database related to table(s)?
Ans- A table is an object inside a database. A database has tables , views, indexes and programs. A
database can have 10 or thousands of tables.
database is a collection of several components like tables, indexes, stored procedures and so on. A table
is a two dimensional structure that contains columns and rows. It contains data in the form of rows and
columns
10. Write SQL statement to view names of all the tables contained in the current database
Ans- To see a list of tables present in the current database we will use SHOW TABLES.
Syntax: SHOW TABLES;
11.In a database there is a table Cabinet. The data entry operator is not able to put NULL in a column
of Cabinet? What may be the possible reason(s)?
Ans-Because NOT NULL constraint is applied on that column in which data entry operator is trying to put
NULL value.
12.In a database there is a table Cabinet. The data entry operator is not able to put duplicate values in
a column of Cabinet? What may be the possible reason(s)?
Ans- Because Unique key constraint is applied on that column in which data entry operator is trying to
put duplicate value
13.Do Primary Key column(s) of a table accept NULL values?
Ans-NO,Primary key column of the table doesn’t accept Null Values.
14.There is a table T1 with combination of columns C1, C2, and C3 as its primary key? Is it possible to
enter:
a. NULL values in any of these columns?
b. Duplicate values in any of these columns?
Ans- a. No.Primary key column does not allow null values in it.
b. No, because duplicate values are not allowed in the table.
15. At the time of creation of table X, the data base administrator specified Y as the
Primary key. Later on he realized that instead of Y, the combination of column P and
Q should have been the primary key of the table. Based on this scenario, answer the
following questions:
3
Prerna R. Sharma
a. Is it possible to keep Y as well as the combination of P and Q as the primary key?
b. What statement(s) should be entered to change the primary key as per the
requirement.
Ans(i) No - A table can have only one primary key.
(ii) ALTER TABLE X DROP PRIMARY KEY
ALTER TABLE X ADD PRIMARY KEY (P,Q)
17. Does MySQL allow to change the primary key in all cases? If there is some special
case, please mention.
Ans- Yes, MySQL allow to change the primary key in all cases
18. What are the differences between DELETE and DROP commands of SQL?
Ans-DELETE Command is used to remove some or all the records from the table. while
DROP TABLE command is used to delete tables.Delete is DML while Drop is DDL
19. Which statement is used to modify data in a table?
A. CHANGE
B. MODIFY
C. UPDATE
D. SAVE AS
20. Which SQL statement is used to delete data from a table?
A. DELETE
B. DROP
C. TRUNCATE
D. REMOVE
Fill up the blanks :
1, ____INSERT__ command is used to add records in a relation.
2. Select is a type of __DML _________ command.
3. ______UPDATE___ command is used to modify records in a table.
4. ____LIKE_____ is used to match string patterns.
5. BETWEEN operator includes ____UPPER_BOUND_ and _LOWER BOUND__values given in the range.
4
Prerna R. Sharma
