UNIT TEST-6

Chapter: Table creation and Manipulation, Grouping and Simple Queries

Q.No Question Marks
1. Command that makes the changes permanent. 1
2. In a table in MYSQL database, an attribute A of datatype varchar(20) has the value 1
“Keshav”. The attribute B of datatype char(20) has value “Meenakshi”. How many
characters are occupied by attribute A and attribute B?
a. 20,6 b. 6,20 c. 9,6 d. 6,9
3. Which of the following statements is FALSE about keys in a relational database? 1
a. Any candidate key is eligible to become a primary key.
b. A primary key uniquely identifies the tuples in a relation.
c. A candidate key that is not a primary key is a foreign key.
d. A foreign key is an attribute whose value is derived from the primary key of another
relation.
4. Which of the following is not a legal constraints for a Create Table command? 1
a. Primary Key b. Foreign Key c. Unique d. Distinct
5. Write the command to delete the table employee. 1
6. Name the constraint used to have the unique values in a particular column. 1
7. Can a column defined with NOT NULL constraint, be skipped in an INSERT 1
command?
8. In MySql ____________ represents the unknown value. 1
9. Which command will associate NOT NULL constraints with the attribute Sname of the 1
table Student?
a. alter table student add Sname varchar(20) Not Null;
b. alter table student modify Sname varchar(20) Not Null;
c. alter table modify Sname varchar(20) Not Null;
d. alter table student change Sname varchar(20) Not Null;
10. Find the query to display details of all employees who have been given a salary (i.e., 1
Salary is not null) and works in the department D1.
a. select * from employee where salary = not null and dept_id=”D1”;
b. select * from employee where salary is not null and dept_id=”D1”;
c. select * from employee where salary = not null and dept_id=”D1”;
d. select * from employee where salary is not null or dept_id=”D1”;
11. Write a query to display details of all those employee whose name contains ‘A’. 1
a. select * from employee where ename = “_A_”;
b. select * from employee where ename like “A%”;
c. select * from employee where ename = “%A%”;
d. select * from employee where ename like “%A%”;
12. Which of the following provides a correct order of clauses used in SQL? 1
a. Where, Order By, Group By, Having b. Group By, Order By, Having
c. Group By, Where d. Where, Group By, Having, Order By
13. How many columns or functions can be specified or included in a single GROUP BY 1
clause?
a. Only one b. Less than five by default c. Two to ten d. One or more
14. Consider the given SQL Query: 1
SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 5
GROUP BY department;
Saanvi is executing the query but not getting the correct output. Write the correction
15. What is the meaning of “HAVING” clause in Mysql? 1
a. To filter out the row values b. To filter out the column values
c. To filter out the row and column values d. None of the mentioned
16. In SQL, the aggregate function which will display the cardinality of the table is 1
a. sum () b. count () c. avg() d. sum ()
17. Deepika wants to remove all rows from the table BANK. But she needs to keep the 1
structure of the table. Which command is used to implement the same?
18. State True or False: Rows can be copied from one table to another table using 1
INSERT INTO statement.
19. Consider the following relation: 1
Instructor (Id, Name, City, Country);
Using SQL, how to insert a row with Id number 5, name Ravi.
a. Insert into Instructor values (5, ‘Ravi’);
b. Insert into Instructor values (5, Ravi);
c. Insert into Instructor values (‘5’, ‘Ravi’);
d. Insert into Instructor (Id, Name) values (5, ‘Ravi’);
Q20 and Q21 are Assertion (A) and Reason(R) based questions. Mark the correct choice as:
a. Both A and R are True and R is the correct explanation for A.
b. Both A and R are True and R is not the correct explanation for A.
c. A is True but R is False.
d. A is False but R is True
20. Assertion (A): Both between and in operators can choose from a list of values. 1
Reasoning (R): The value ranges and a list of values are interpreted in the some way in

SQL.
21. Assertion (A): A group by query can also include functions. 1
Reason(R): All SQL functions can be used in group by query.
Section-B (2 Marks)
22. Write the difference between delete and drop commands. 2
23. a) Write an SQL command to remove the Primary Key constraint from a table, named 2
MOBILE. M_ID is the primary key of the table.
b) Write an SQL command to make the column M_ID the Primary Key of an already
existing table, named MOBILE.
24. Zack is working in a database named SPORT, in which he has created a table named 2
“Sports” containing columns SportId, SportName, no_of_players, and category. After
creating the table, he realized that the attribute, category has to be deleted from the table
and a new attribute TypeSport of data type string has to be added. This attribute
TypeSport cannot be left blank. Help Zack write the commands to complete both the
tasks.
25. Charu has to create a database named MYEARTH in MYSQL. She now needs to create 2
a table named CITY in the database to store the records of various cities across the
globe. The table CITY has the following structure:
Table: CITY
FIELD NAME DATA TYPE REMARKS
CITYCODE CHAR(5) Primary Key
CITYNAME CHAR(30)
SIZE INTEGER
AVGTEMP INTEGER
POLLUTIONRATE INTEGER
POPULATION INTEGER
Help her to complete the task by suggesting appropriate SQL commands.
26. (a) Consider a table EMPL, write a query to increase the size of the column empname 2
to 40.
(b) Consider the table student, write a query to change the column name Fname as
FirstName.
27. What is the difference between where clause and having clause? 2
Section-C (3 Marks)
28. Consider the table Projects given below: 3
Projects

P_id Pname Language Startdate Enddate

P001 School Management System Python 2023-01-12 2023-04-03
P002 Hotel Management System C++ 2022-12-01 2023-02-02
P003 Blood Bank Python 2023-02-11 2023-03-02
P004 Payroll Management System Python 2023-03-12 2023-06-02
Based on the given table , Write SQL Queries for the following:
(i) Add the constraints , Primary Key to column P_id in the existing table Projects.
(ii) To change the language to Python of the project whose id is P002?
(iii) To delete the table Projects from MySql database along with its data.

29. Rohan is learning to work upon Relational Database Management System (RDBMS) 3
Application. Help him to perform the following task.
(a) To open the database named “Library”.
(b) To display the names of all the tables stored in the opened database.
(c) To display the structure of the table “BOOKS” existing in the already opened
database “Library”.
30. Consider the table ORDERS as given below 3
 Write the following queries:
(i) To display the total Quantity for each Product, excluding Products with total
Quantity less than 5.
(ii) Display the sum of Price of all the orders for which the quantity is null.
(iii) To display the distinct customer names from the Orders table.
Section-D (4 Marks),
31. Consider the table Personal given below: Table: Personal 4

P_ID NAME DESIG SALARY ALLOWANCE

P01 Rohit Manager 89000 4800
P02 Kashish Clerk NULL 1600
P03 Mahesh Superviser 48000 NULL
P04 Salil Clerk 31000 1900
P05 Ravina Superviser NULL 2100
Based on the given table, write SQL queries for the following:
(i) Increase the salary by 5% of personals whose allowance is known.
(ii) Display Name and Total Salary (sum of Salary and Allowance) of all personals. The
column heading ‘Total Salary’ should also be displayed.
(iii) Delete the record of personals who have salary greater than 25000
(iv) Display the total salary designation wise.
32. Write queries (a) to (d) based on the tables EMPLOYEE 4

(i) To display the average salary of all employees, department wise.

(ii) To display name each employee whose salary is more than 50000.
(iii) To display the names of employees whose salary is not known, in alphabetical
order.
(iv) To display DEPTID from the table EMPLOYEE without repetition.