HOLIDAY HOMEWORK (12 C,I.P)
A table STUDENT has 5 rows and 3 columns. Table ACTIVITY has 4 rows and 2 columns. What will be the cardinality and degree of the Cartesian product of them ? 1
1. Name the SQL commands used to : 2
(i) Physically delete a table from the database.
(ii) Display the structure of a table.
2. Write one similarity and one difference between UNIQUE and PRIMARY KEY constraints. 2
3. Write one similarity and one difference between CHAR and VARCHAR data types. 2
4. Write the UPDATE command to increase the commission (Column name : COMM) by 500 of all the Salesmen who have achieved Sales (Column name : SALES) more than 200000. The
table’s name is COMPANY. 1
5. While using SQL pattern matching, what is the differencebetween ‘_’ (underscore) and ‘%’ wildcard symbols ? 2
6. Saumya had previously created a table named ‘Product’ in a database using MySQL.Later on she forgot the table structure. Suggest her suitable MySQL commandthrough which she can check the structure of the already created table. 1
7. Amit works as a database administrator in a Multinational bank. He wants to undothe changes made in the current transaction. Suggest to him a suitable MySQLcommand for the same. 1
8. (a)Observe the table ‘Club’ given below: 2
Club
Member_id Member_name Address Age Fees
M001 Sumit New Delhi 20 1000
M002 Nisha Gurgaon 19 1500
M003 Niharika New Delhi 21 2100
M004 Sachin Faridabad 18 1500
i.What is the cardinality and degree of the above given table?
ii. If a new column contact_no has been added and two more members have joined the club then how these changes will affect the degree and cardinality of the above given table.
(b)Write the output of the following SQL queries: 2
i. SELECT INSTR(‘INTERNATIONAL’, ‘NA’);
ii. SELECT LENGTH(CONCAT(‘NETWORK’,’ING’));
iii.SELECT ROUND(563.345,-2);
iv. SELECT DAYOFYEAR(‘2014-01-30’);
9. Write SQL query to create a table ‘Bank_Customer’ with the following structure: 2
10. ) In a Bank’s database, there are two tables ‘Customer_info’ and‘Transaction__Detail’ as shown below. 2
i. Is it possible to have primary key and foreign key in one table? Justify your
answer.
ii. A table can have maximum how many primary keys and foreign keys?
11. Distinguish between ALTER TABLE and UPDATE commands of MySQL. 1
12. Write two examples of DBMS software. 1
13. What is meant by NULL value in MySQL ? 1
14. How is NULL value different from 0 (zero) value ? 1
15. Write two examples of DML commands of SQL. 1
16.
Class-XII
Subject: Informatics Practices(065)
Unit 3 – RELATIONAL DATABASE MANAGEMENT SYSTEM
Preparation of probable questions for AISSCE - 2016
Questions that have been repeated one or more times.
1. Sharmila wants to make the database named ‘COMPANY’ active and display thenames of all the tables in it. Write MySQL commands for it. 1
2. What is MySQL ? 1
3. Distinguish between Single Row and Aggregate functions of MySQL. Write one example of each. 2
4. What will be the output of the following queries on the basis of Employee table: 2
+------+------+------+
| EmpId | EName | Salary |
+------+------+------+
| A001 | Bob | 5600 |
| A002 | John | NULL |
| A003 | Tom | 5000 |
+------+------+------+
(i)Select avg(Salary) from Employee;
(ii) Select Salary+100 from Employee where EmpId='A002';
5. Mention two categories in which MySQL commands are broadlyclassified. 1
6. Give two characteristics of Primary Key. 1
7. Consider the following table named ‘‘EXAM’’ with details ofmarks. Write command of MySQL for (i) to (iv) and output for(v) to (vii). 6
(i) To display all information of the students of humanities indescending order of percentage.
(ii) To display Adno, Name, Percentage and Stream of thosestudents whose name is less than 6 characters long.
(iii) To add another column Bus_Fees with datatype and size asDecimal (8,2).
(iv) To increase percentage by 2% of all the Humanities students.
(v) SELECT COUNT(*) FROM Exam;
(vi) SELECT SName, Percentage FROM EXAMWHERE Name LIKE "N%";
(vii) SELECT ROUND(Percentage,0) FROM EXAMWHERE Adno="R005";
8. State difference between date functions NOW( ) and SYSDATE( ) of MySql. 2
9. Name a function of MySql which is used to remove trailing and leading spaces from a string. 1
10. Write the UPDATE statement in MySQL to increase commission by 100.00 in the ‘‘Commission’’ column in the ‘Emp’ table. 1
TOPIC - RELATIONAL DATABASE MANAGEMENT SYSTEM
1. a). Explain the purpose of DDL and DML commands used in SQL. Also give two examples of each.
[2011]
b) What is the Purpose of Drop Table command in SQl?How is it different form Delete Command?
[2011]
c) What is the purpose of ORDER BY clause in MYSQL? How is it different from GROUP BY clause ?
[2012]
d) . What is the use of UPDATE statement in SQL ? How is it different from ALTER statement ?
[2013]
e) Write one similarity and one difference between CHAR and VARCHAR data types? [2014]
f) Distinguish between Single Row and Aggregate functions of MySQL. Write one example of each.
[2014]
2. a) Write the output of the following SQL queries: [2011]
i) SELECT ROUND(6.5675, 2);
ii) SELECT TRUNCATE(5.3456, 1);
iii) SELECT DAYOFMONTH('2009-08-25');
iv) SELECT MID('Class 12', 2,3);
b) Principal Name “ is a column in a Table “Schools “, The SQL queries Select count (*) from Schools;
and Select give Count (Principal ) from Schools; the result 28 and 27 respectively, what may be
the possible reason for this? How many records are present in the table -27 or 28? [2011]
c) Table school has 4 rows and 5 columns. What is the Cardinality and Degree of this table ?
[2012]
d)Mr Shankar created a table VEHICLE with 3 ros and 4 column. HE added 1 more rows to it and
deleted one column. What is the Cardinality and Degree of the Table VEHICLE? [2013]
3. a) Consider the table TEACHER given below. Write commands in SQL for (1) to (4) and
output for (5) to (8) [2011]
TEACHERID / Name / Department / Hiredate / Category / Gender / Salary
1 / Tanya Nanda / SocialStudies / 3/17/1994 / TGT / F / 25000
2 / Saurabh Sharma / Art / 2/12/1990 / PRT / M / 20000
3 / NanditaArora / English / 5/16/1980 / PGT / F / 30000
4 / James Jacob / English / 10/16/1989 / TGT / M / 25000
5 / JaspreetKaur / Hindi / 8/1/1990 / PRT / F / 22000
6 / DishaSehgal / Math / 3/17/1980 / PRT / F / 21000
7 / SiddharthKapoor / Science / 9/2/1994 / TGT / M / 27000
8 / Sonali Mukherjee / Math / 11/17/1980 / TGT / F / 24500
i. To display all information about teachers of PGT category.
ii. To list the names of female teachers of Hindi department.
iii. To list names, departments and date of hiring of all the teachers in ascending
order of date of joining
iv. To count the number of teachers in English department.
v. SELECT MAX(Hiredate) FROM Teacher;
vi. SELECT DISTINCT(category) FROM teacher;
vii. SELECT COUNT(*) FROM TEACHER WHERE Category = "PGT"
viii. SELECT AVG(Salary) FROM TEACHER group by Gender;