CHAPTER :12

MYSQL REVISION TOUR

1)How are SQL commands classified ?

Answer =

SQL COMMANDS ARE CLASSIFIED INTO:DDL (Data Definition Language)DML (Data Manipulation
Language)DQL (Data Query Language)DCL (Data Control Language)

Q 2 = Differentiate between DDL and DML commands.

Answer =

DDL

1 = DDL is Data Definition Language

2 = These are used to define data structure

3 = It is used to define database structure or schema

4 = Commands are: CREATE, ALTER, DROP, TRUNCATE, RENAME

5 = It works on whole table

6 = It do not have a where clause to filter

7 = Changes done by DDL commands cannot be rolled back

8 = Example -- Drop table student

DML –

1 = It is Data Manipulation Language

2 = It is used to manipulate the existing databases.

3 = It is used for managing data within schema objects

4 = Commands are: SELECT, INSERT, DELETE, UPDATE, MERGE, CALL

5 = It works on one or more rows

6 = It have where clause to filter records

7 = Changes can be rolled back

8 = It is further classified as procedural and non procedural

9 = Example --- Select * from student

3(a) What is the use of UPDATE statement in SQL ? How is it different from ALTER statement

(b) Mr. Shankar created a table VEHICLE with 3 rows and 4 columns. He added 1 more row to it
deleted one column. What is the Cardinality and Degree of the Table VEHICLE ?

(c) Consider the following table named "GYM" with details about fitness items being sold in
the store. Write command of SQL for (i) to (iv).

(i) To display the names of all the items whose name starts with "A".

(ii) To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore.

(iii) To change the Brandname to "Fit Trend India" of the item, whose ICODE as "G101".

(iv) Add a new row for new item in GYM with the details :

"G107", "Vibro exerciser", 21000, "GTCFitness"Answer =(a)The UPDATE statement in SQL is

used to update the data of an existing table in database .ALTER is a DDL (Data Definition
Language) statement. Whereas UPDATE is a DML (Data Manipulation Language) statement.
ALTER is used to update the structure of the table (add/remove field/index etc). Whereas
UPDATE is used to update data.(b)number of cardinality = 4Number of degree = 3(c)(i)select *
from GYMWhere INAME like “A%”(ii)select ICODE , INAME from GYMWhere BRANDNAME in (
“Reliable” , “Coscore” )(iii)update GYMSet BRANDNAME = “Fit Trend India ”Where ICODE =
“G101” ;(iv)insert into GYM values ("G107", "Vibro exerciser", 21000, "GTCFitness") ;
Q 4.

(a) Mr. James created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it
and deleted one column. What is the Cardinality and Degree of the Table CLIENT ?

(b) Consider the following table FITNESS with details about fitness products being sold in the
store. Write command of SQL for (i) to (iv).

(i) To display the names of all the products with price more than 20000.

(ii) To display the names of all products by the manufacturer "Aone".

(iii) To change the price data of all the products by applying 25% discount reduction.

(iv)To add a new row for product with the details

"P7", "Vibro Exerciser", 28000, "Aone".

Answer =

(a) Cardinality = 4

Degree = 3

(b)

(i) select * from FITNESS

Where price < 20000 ;

(ii) select pname from FITNESS

Where manufacturer = “Aone”;

(iii) update FITNESS

Set price = price * 0.75 ;

(iv) insert into FITNESS values ( “P7” , “Vibro Exerciser”, 28000, "Aone" ) ;

Q 5 = Write SQL commands for the following on the basis of given table CLUB.

(a) To show all information about the swimming coaches in the club.

(b) To list names of all coaches with their date of appointment (DATOFAPP) in descending
order.

(c) To display a report, showing coachname, pay, age and bonus (15% of pay) for all the
coaches.

Answer =

(a) Select * from CLUB

Where sports = “SWIMMING” ;

(b) select coachname from CLUB

Order by datofapp DESC ;

(c) select coachname , pay , age , pay * 0.15 as Bonus from CLUB ;

Q 6 = Write SQL commands for the following on the basis of given table STUDENT1.

(a) Select all the Nonmedical stream students from STUDENT1.

(b) List the names of those students who are in class 12 sorted by Stipend.

(c) List all students sorted by AvgMark in descending order.

Answer =

a) select * from student1

where stream = “nonmedical” ;

b)select name from student

where class like “12%”

order by stipend ;

c)select * from student1

order by AvgMark desc;

Q 7 = What is foreign key ? How do you define a foreign key in your table ?

Answer :

A foreign key is a column or group of columns in a relational database table that provides a
link between data in two tables. It acts as a cross-reference between tables because it
references the primary key of another table, thereby establishing a link between them.

A FOREIGN KEY is a key used to link two tables together.A FOREIGN KEY is a field (or
collection of fields) in one table that refers to the PRIMARY KEY in another table.

Foreign key ( <column name>) REFERENCES <parent table name > ( < Column name > )

Q 8 = How is FOREIGN KEY commands different from PRIMARY KEY command ?

Answer =

1) Primary key can not accept null values . while foreign key can accept multiple null values .

2)only one primary key in a table while more than one foreign key in a table .

3) primary key uniquely identify a record in the table while foreign key is a field in the table
that is primary key in another table .
Q 9 = How is FOREIGN KEY commands related to the PRIMARY KEY ?

Answer =

A primary key-foreign key relationship defines a one-to-many relationship between two tables
in a relational database. A foreign key is a column or a set of columns in one table that
references the primary key columns in another table. The primary key is defined as a column
(or set of columns) where each value is unique and identifies a single row of the table.

Q 10 = What are table constraints ? What are column constraints ? How are these two different
.

Answer =

A Constraint is a condition or check applicable on field or set of fields .

The two basic type of constraints are Column constraints and table constraints. The difference
between the two is that column constraints apply only to individual columns , whereas table
constraints apply to group of one or more columns .

Q 11 = Insert all those records of table Accounts into table Pending where amt_outstanding is
more than 10000

Answer =

Create table Pending as

Select * from Accounts

Where amt_outstanding > 10000 ;

Q 12 = Increase salary of employee records by 10% (table employee).

Answer =

Update employee

Set salary = salary + salary * 0.10 ;

Q 13 = Add a constraint (NN-Grade) in table Empl (given before assignment) that declares
column Grade not null.

Answer =

Alter table Empl modify column grade int(1) Not null ;

Q 14 = Drop the table Empl.

Answer =

Drop table Empl ;

Q 15 = Differentiate between:

(i) DROP TABLE, DROP DATABASE

(ii) DROP TABLE, DROP clause of ALTER TABLE.

Answer =

(i) drop table = it delete table or relation from database

Drop database = it delete the database from MYSQL server.

(ii) drop table = it delete table or relation from database

DROP clause of ALTER TABLE = it delete a particular column or filed from table or relation .

Q16. Mr. Mittal is using a table with following columns :

Name, Class, Stream_ld, Stream_name

He needs to display names of students who have not been assigned any stream or have been
assigned stream_name that ends with "computers".

He wrote the following command, which did not give the desired result.

SELECT Name, Class FROM Students

WHERE Stream_name = NULL OR Stream_name = "%computers" ;

Help Mr. Mittal to run the query by removing the error and write correct query.Answer =SELECT
Name, Class FROM StudentsWHERE Stream_name is NULL OR Stream_name like "%computers"
;

Q17. The Doc_name Column of a table Hospital is given below:

Doc_name

Avinash

Hariharan

Vinayak

Deepak

Sanjeev

Based on the information, find the output of the following queries:

(i) SELECT doc_name FROM HOSPITAL WHERE Doc_name like "%v";

(ii) SELECT dcc_name FROM HOSPITAL WHERE doc_name like "%e%";

Answer

(a)

Doc_name

Sanjeev

(b)
Doc_name

Deepak

Sanjeev

Q 18 = Sarthak, a student of class XII, created a table "Class". Grade is one of the columns of
this table. 10 i the details of students whose Grades have not been entered, he wrote the
following MySql query, which did not give the desired result :

SELECT * FROM Class WHERE Grade = "Null";

Help Sarthak to run the query by removing the errors from the query and write the correct
query .

Answer =

SELECT * FROM Class WHERE Grade is Null ;

Q 20= What is the purpose of DROP TABLE command in MySql ? How is it different from
DELETE Command ?

Answer = DROP is a Data Definition Language (DDL ) command. The DROP command
removes the named elements of the schema like relations, domains or constraints,
you can even remove an entire schema using DROP command.
 BASIS FOR
DELETE DROP
COMPARISON

DROP can remove entire

DELETE remove some or all the schema, table, domain, or

Basic
tuples from a table. constraints from the

database.

DELETE is a Data Manipulation DROP is a Data Definition

Language
Language command. Language command.

WHERE clause can be used along No clause is used along with

Clause
with the DELETE command. DROP command.

Actions performed by DELETE can Actions performed by DROP

Rollback
be roll-backed. can not be rollbacked.

Even if you delete all the tuples of

Table deleted using DROP
the table using DELETE, space
Space frees the table space from
occupied by the table in the
memory.
memory is not freed.