HSLC Computer Science Solution: Chapter 12 (MySQL)

Contents:

• Objective Type Questions
  1. Fill in the blanks
  2. True and False
  3. Choose the correct option
• Descriptive Type Questions
  1. Short Answer Questions
  2. Long Answer Questions
• Application-based Questions
  

• Objective Type Questions:

1. Fill in the blanks:

a. A ………………….. is a collection of objects that you need to store and manipulate data such as tables and views.
ans: database

b. A ………………….. key is a column or a set of columns that uniquely identifies each row in the table.
ans: primary

c. ………………….. are some rules that help ensure the validity of the data while entering it in a table.
ans: Constraints

d. The ………………….. TABLE command is used to modify the table structure.
ans: ALTER

e. To select a specific row(s), ………………….. clause is used in the query.
ans: where

f. The ………………….. clause is used to compare one string value with another.
ans: like

2. True and False

a. The SHOW DATABASES; query will display all the tables.
ans: False

b. The USE command is used to activate a database.
ans: True

c. The query DESC student; will display information on the fields in the table ‘student’.
ans: False

d. To display only unique values, the DISTINCT clause is used.
ans: True

e. The two wildcard characters used with the LIKE clause are * and ?.
ans: False

3. Choose the correct option.

a. The MySQL query that will display the structure of the ‘Customer’ table is
i.DISPLAY customer;
ii. DESCRIBE customer;
iii. SHOW customer;
iv. STRUCTURE customer;

ans: ii. DESCRIBE customer;

b. The command used to modify the contents of a table are:
i. ALTER TABLE
ii. INSERTUPDATE
iii. UPDATE
iv. SELECT

ans: i. ALTER TABLE

c. The keyword used to sort the column in descending order is
i. DESCENDING
ii. DESC
iii. DES
iv. REVERSE

ans: ii. DESC

d. Which one is true about a Primary key column
i. It has duplicate values
ii. It has NULL values
iii.It has unique values
iv. All of these

ans: iii.It has unique values

e. The functions used in the GROUP BY clause are
i. SUM( )
ii. MAX( )
iii. MIN( )
iv. All of these

ans: iv. All of these

• Descriptive Type Questions

1. Short Answer Questions

a. Write the query to add the Primary key constraint to EmpID column of the Employee table.
ans: ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMPID);

b. Which query will remove the Primary key constraint of AdmissionNo in the Student table?
ans: ALTER TABLE STUDENT DROP PRIMARY KEY will remove the Primary key constraint of AdmissionNo in the Student table.

c. Is it possible to add a NULL or NOT NULL constraint to a table already loaded with data?
ans: In MySql, it is not possible to add a NULL or NOT NULL constraint explicitly after the creation of the table. But it can be done using the MODIFY clause of the ALTER TABLE command.
Suppose we do not want to accept NULL values in the attribute Student Class, then this query will be:
ALTER TABLE student MODIFY (Student Class VARCHAR(50) NOT NULL);

d. How will you view all the tables in the database named Company?
ans: SHOW COMPANY;

e. Write the query to activate the database named Hospital.
ans: USE Hospital;

2. Long answer questions

a. What is a constraint? Name any two constraints.
ans: Constraints are the rules that help ensure the validity of the data while entering it in a tablr. Two constraints are:
(a) Primary Key (b) NOT NULL

b. What is the difference between CHAR and VARCHAR data types?
ans:

c. What is a group function? Explain with an example.
ans: In MySQL, group functions or aggregate functions are applied on a group of values as input and return a single value as the result. Some of the group functions are:
(i) SUM( ) – Returns the sum of values of specified columns/expressions.
(ii)MAX( ) – Returns the maximum value of a set of values of specified columns/expressions.
(iii) MIN( ) – Returns the minimum value of a set of values of specified columns/expressions.
(iv) AVG( ) – Returns the average of values of specified columns/expressions.
(v) COUNT( ) – Returns the number of values in specified columns/expressions.
(vi) COUNT(*) – Returns the number of rows in the table.

For example, let us consider a table employeedetails . The following queries will explain the working of aggregate functions.
• SELECT SUM(EmpSalary), AVG(EmpSalary), MAX(EmpSalary), MIN(EmpSalary) FROM employeedetails;
The result of this queries shows the sum of values EmpSalary.

d. What is the difference between the following two statements:
• DELETE FROM s1;
• DROP TABLE s1;
ans:

e. What is the difference between the WHERE and HAVING clauses?
ans:

f. Pair the equivalent SQL statements (that give the same output) from the following:
i. SELECT * FROM club WHERE salary between 20000 and 30000;
ii. SELECT * FROM club WHERE salary IN (20000, 30000) ;
iii.SELECT * FROM- club WHERE salary >= 20000 and salary <=30000;
iv. SELECT * FROM club WHERE salary = 20000 OR salary = 30000;

ans: Group A
i. SELECT * FROM club WHERE salary between 20000 and 30000;
iii.SELECT * FROM- club WHERE salary >= 20000 and salary <=30000;

Group B
ii. SELECT * FROM club WHERE salary IN (20000, 30000) ;
iv. SELECT * FROM club WHERE salary = 20000 OR salary = 30000;

• Application-based Questions

1. Write the MySQL query to create a table Coach with the structure given in the table alongside. Then perform the following functions on this table in MySQL:

a. Change the data type of CoachName to VARCHAR(20).
ans: ALTER TABLE Coach MODIFY CoachName VARCHAR(20);

b. Add a column Gender after CoachName that can hold the value ‘M’ or ‘F’.
ans: ALTER TABLE Coach ADD Gender VARCHAR(5) AFTER Coach Name;

c. Remove the primary key constraint from the column CoachlD.
ans: ALTER TABLE Coach DROP PRIMARY KEY;

d. What will you do to see that the constraint in (c) has been removed or not?
ans: DESC Coach;

2. Jagriti wrote the following query but it did not give the desired result.
SELECT MemberName, Amount FROM CLUB
WHERE Game = NULL OR Game = ‘Table Tennis’;
Help Jagriti run the query by removing the error and writing the correct query.

ans: Correct Query:
SELECT Member Name, Amount FROM CLUB
WHERE Game IS NULL OR Game= ‘Table Tennis’;

3. Consider the table CARDEN given alongside and give the output of the following queries:
a. SELECT CarName FROM CARDEN where Color LIKE `or’;
b. SELECT Color FROM CARDEN WHERE CarName LIKE ‘IV ;

ans:

4.Consider the same table CARDEN and differentiate between the following two queries:
• SELECT Color FROM CARDEN;
• SELECT DISTINCT (Color) FROM CARDEN;

ans:

5. Meenal has created a table Sales in MySQL shown alongside. She has written the following queries:
a. SELECT COUNT(Comm) FROM Sales;
b. SELECT COUNT(*) FROM Sales; Explain the output of the two queries given above

ans:

Vishal Junak Das