PRABHUJEE ENGLISH MEDIUM SCHOOL

WORKSHEET – 2
STD: XII Subject: Informatics Practices
MySQL Revision Tour
1. A relational database can have how many types of keys? Name them.
2. A primary key is selected from a set of ______________
a. Composite keys
b. Determinants
c. Candidate keys
d. Foreign keys
3. Which of the following attributes cannot be considered as a choice for Primary Key?
a. Id
b. License number
c. Dept_id
d. Street
4. Which of the following is not a legal sub language of SQL?
a. DDL b. DML c. QAL d. TCL
5. Which of the below given tasks cannot be performed through data manipulation language(DML)
commands?
a. Create table in a database b. Insert a record into a table
c. Delete a record from a table d. Modify a record from a table
6. The following query has used two clauses- SELECT and WHERE. The ________ clause of the following
query must be added with keyword ________ to display the fields given in the select list as per given
condition.
SELECT ID,dept name,salary * 1.1 ________ where instructor=1005;
a. Where,having b. select,from c. where,from d. where,select
7. Name the operator that performs pattern matching?
8. The pattern “_ _ _” matches any string of _________ three characters,”_ _ _%” matches any string of
__________ three characters.
a. atleast,exactly b. exactly,atleast
c. atleast,all d. all,exactly
9. Consider the following table namely Employee:
Employee_id Name Salary
1001 Misha 6000
1009 Khushi 4500
1018 Japneet 7000

Which of the names will not be displayed by the below given query?
Select name from employee where Employee_id>1009;
a. Misha,Khushi b. Khushi,Japneet
c. Japneet d. Misha,Japneet
10. Which statement would add a column GRADE to an already existing table Student?
a. Alter table student add new column(Grade number(3,1));
b. Alter table student Grade number(3,1);
c. Alter table student Add(Grade Number(3,1));
d. Both a and c
11. What is the role of the following constraints:
a. Unique constraint b. NOT NULL constraint c. Primary Key Constraint
12. Which of the following is not a valid datatype of SQL?
a. char(n) b. String(n) c. varchar(n) d. float(n)
13. What does the following query do?
Update Employee
Set salary=salary*1.10;
a. It increases the salary of all the employees by 10%
b. It decreases the salary of all the employees by 10%
c. It increases the salary of all the employees by 110%
d. It is syntactically incorrect.
14. Which of the following query will drop a column from a table?
a. delete column column_name;
b. drop column column_name;
c. Alter table table_name drop column column_name;
d. None of these
15. Which of the following types of table constraints will prevent the entry of duplicate rows?
a. Unique b. distinct c. Primary key d. NULL
16. A table Table1 has two text fields defined as follows:
.
.
Name1 varchar(20),
Name2 char(20),
.
.
If Name1 stores value as ‘Ana’ and Name2 stores value as ‘Anuj’, then Name1 will consume _______
Characters space and Name2 will consume ________ characters space.
a. 3,20 b. 20,4 c. 20,20 d. 3,4
17. Consider the following statements, what type of statements are these:
CREATE TABLE employee(name VARCHAR,id INTEGER)ate
SELECT * FROM employee;
(a) DDL,DCL
(b) DDL,DML
(c)DML,DDL
(d) DDL,DDL
18. Create a table Employee with the following columns:
* EmpId(integer, primary key)
* EmpName(varchar 50, not null)
* Salary(float, must be greater than 0)
* Department(varchar 30)
19. Create a table Library with the following structure:
* BookId(Integer Primary key)
* Title(varchar 100 not null)
* Author(varchar 50)
* Price(float)
* Published year(integer)
20. Create a table Orders with the following fields:
* orderId(integer , primary key)
* CustomerName(varchar 40)
* Orderdate(Date default current_Date)
* Amount float
21.
 i. DISPLAY ALL THE DETAILS OF EMP TABLE
ii. DISPLAY THE NAME AND DESIGNATION OF THE EMPLOYEES.
iii. DISPLAY THE EMPLOYEE NUMBER, NAME AND HIREDATE OF THE EMPLOYEES.
iv. DISPLAY THE DETAILS OF ALL EMPLOYEES WHO ARE ‘MANAGER’ S.
v. DISPLAY THE NAME OF THE EMPLOYEES WHO ARE ‘SALESMAN’
vi. DISPLAY THE NAME AND DESIGNATION OF THOSE EMPLOYEES WHOSE MGR CODE IS 7698.
vii. DISPLAY THE DETAILS OF THOSE EMPLOYEES BELONGING TO DEPARTMENT NUMBER 30.
viii. DISPLAY THE DETAILS OF THOSE EMPLOYEES WHOSE SALARY IS MORE THAN 1500.
ix. DISPLAY THE NAME,DESIGNATION AND HIREDATE OF THOSE EMPLOYEES WHOSE SALARY IS MORE
THAN 2000.
x. DISPLAY THE EMPLOYEE NUMBER AND NAME OF THE EMPLOYEES WHOSE SALARY IS IN THE
RANGE OF 1500 AND 5000;
xi. DISPLAY THE DETAILS OF THOSE EMPLOYEES WHOSE SALARY BETWEEN 3000 AND 5000.
xii. DISPLAY THE NAME OF THOSE EMPLOYEES WHO ARE MANAGER OR SALESMAN.
xiii. DISPLAY THE NAME ,EMPLOYEE NUMBER AND SALARY OF MANAGERS,SALESMAN,ANALYST.
xiv. DISPLAY THE DETAILS OF THOSE EMPLOYEES WHO ARE GETTING A SALARY OF
3000,1500,1600,5000.
xv. DISPLAY THE NAME OF THOSE EMPLOYEES WHO ARE NOT GETTING ANY COMMISSION.
xvi. DISPLAY THE DETAILS OF THOSE EMPLOYEES WHO ARE GETTING COMMISION.
xvii. DISPLAY THE NAME AND HIREDATE OF THOSE EMPLOYEES WHO ARE NOT MANAGER S.
xviii. DISPLAY THE NAME OF THOSE EMPLOYEES WHOSE SALARY IS NOT IN THE RANGE OF 3000 AND
5000.
xix. DISPLAY THE NAME OF THOSE EMPLOYEES WHOSE NAMES HAVE EXACTLY 5 CHARACTERS.
 xx. DISPLAY THE DETAILS OF THOSE EMPLOYEES WHOSE FIRST LETTER BEGINS WITH ‘S’
xxi. DISPLAY THE NAME AND DESIGNATION OF THOSE EMPLOYEES WHO HAVE CHARACTER ‘A’ IN THE
MIDDLE OF THEIR NAMES.
xxii. DISPLAY THE DETAILS OF THOSE EMPLOYEES WHO WERE HIRED IN THE YEAR ‘81’
xxiii. DISPLAY THE NAMES,DEPTNO OF THOSE EMPLOYEES WHO ARE GETTING COMMISSION.

TABLE NAME: STUDENT

WRITE A SQL COMMAND TO INSERT THE FOLLOWING DATA INTO THE ‘STUDENT’ TABLE.
(a)(0023,’ASHOK’,45,’LUCKNOW’)
(b) (0021,’SAMEER’,42,’DELHI’)
(c) (0022,’ANJALI’,45)

xxiv. DISPLAY THE DETAILS OF THE EMP TABLE IN THE ORDER OF EMPLOYEE NAMES.
xxv. DISPLAY THE NAME AND JOB OF THE EMPLOYEES IN THE ORDER OF THEIR SALARIES.
xxvi. DISPLAY THE EMPNAME,DESIGNATION AND SALARY OF THE MANAGERS IN DESCENDING ORDER
OF THEIR SALARIES.
xxvii. MODIFY THE SALARY OF ALL THE EMPLOYEES BY INCREASING THEM BY RS 2000.
xxviii. MODIFY THE SALARY OF ALL ‘MANAGER’ S BY MAKING THEM TWICE THE EXISTING SALARY.
xxix. INCREASE THE SALARY OF ‘MANGER’ AND ‘SALESMAN’ BY 1000.
xxx. DELETE ALL THE DATA FROM EMP TABLE.
xxxi. DELETE THE DATA OF THOSE EMPLOYEES WHO ARE GETTTING SALARY OF MORE THAN 2000
xxxii. DELETE THE RECORD OF THOSE EMPLOYEES WHOSE HIRE DATE IS NOT IN THE YEAR 1981.
xxxiii. ADD A NEW COLUMN EMP_AGE TO EMP TABLE OF DATATYPE INTEGER.
xxxiv. MODIFY THE DATATYPE OF THE COLUMN EMP_AGE TO CHAR(2).
xxxv. CHANGE THE NAME OF THE COLUMN EMP_AGE TO ‘EMPLOYEE_AGE”