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 identifles 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. Write T for the true statement and F for the false one.
a.The SHOW DATABASES; query will display all the tables.
Ans. F
b.The USE command is used to activate a database.
Ans. T
c.The query DESC student; will display information on the flelds in the table ‘student’,
Ans. T
d.To display only unique values, the DISTINCT clause is used.
Aris. T
e.The two wildcard characters used with the LIKE clause are and ?
Ans. F

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) INSERT
(iii) UPDATE
(iv) SELECT
Ans. (1) ALTER TABLE

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

d.Which one is true about a Primary key column
(1) 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)MIN()
(iii) MAX()
(iv) All of these
Ans. (iv) All of these

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;

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 or drop NOT NULL constraint explicitly after the creation of the table. But it can be done by using the MODIFY clause of the ALTER TABLE command. Suppose we do not want to accept NULL values in StuName field, then the query for this will be:
ALTER TABLE student MODIFY StuName VARCHAR (20) NOT NULL;

d. How will you view all the tables in the database named Company?
Ans. SHOW TABLES;

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 some rules that help ensure the validity of the data while entering
data in a table.
The name of two constraints is PRIMARY KEY, NOT NULL
b. What is the difference between CHAR and VARCHAR data types?
Ans:-CHAR
It is used to store a fixed-length string of 1 to 255 characters. The data is right padded with spaces to the specified length.
For example, if a field, say City, is of size CHAR(30) and contains just 10 Characters, then it is padded with 20 spaces on the right.

VARCHAR
It is used to store a variable length of 1 to 255 characters.
For example, if a field, say City, is of size CHAR(25), and if the value is ‘New Delhi’, this datatype takes the declared length of characters and removes the trailing spaces.

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

For example, assume a table employeedetails. The following queries will explain the working of group functions, SELECT SUM(EmpSalary), AVG(EmpSalary), MAX(EmpSalary), MIN(EmpSalary) FROM employeedetails;
The result of this queries shows the sum, average, maximum, and minimum values of EmpSalary.

d.What is the difference between the following two statements:
(a)DELETE FROM s1;
(b) DROP TABLE s1;
Ans. DELETE FROM s1; command delete the record from the table s1, and it removes the table space which is allocated by the database, and returns number of rows deleted.

DROP TABLE s1; command delete the table s1, and its structure from the database.

Basically, DELETE is used to delete one or several rows from the table but DROP TABLE is used to remove the entire table from the database.

e.What Is the difference between the WHERE and HAVING clauses?
Ans:-WHERE clause
i.WHERE clause helps us to filter out records based on the data available in a Database table
ii.. Aggregate functions cannot be used with WHERE clause.

HAVING Clause
i. HAVING clause helps us to filter records on the basis of results of Aggregate or Groups Functions.
ii. Aggregate functions can be used with HAVING clause.

f.Pair the equivalent 5QL 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 WBERE salary >= 20000 and salary <=30000;
(Iv) SELECT * FROM club WHERE salary 20000 OR salary = 30000;
Ans. Equivalent pair statements are given below:
a.i. SELECT * FROM club WHERE salary between 20000 and 30000;
iii. SELECT * FROM club WHERE salary >= 2000 and salary <= 30000;

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

4 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here