ASSIGNMENT – IV

SUBJECT: COMPUTER SCIENCE (083)

CLASS: XII
1 Which command is used to modify the records of the table?

a. alter b. update c. modify d. change

2 Consider the table ‘Exam’ with 5 records and 4 columns, then what will be the degree and
cardinality of the table ‘Exam’?
a. Degree=4, b. Degree=5, c. Degree=9, d. Degree=4,
Cardinality=5 Cardinality=4 Cardinality=20 Cardinality=6
3 Which clause is used with “aggregate functions” ?

a. ALTER b. SELECT c. GROUP BY d. BOTH (a) and (c)

4 The ____________command can be used to makes changes in the rows of a table in SQL.
5 Which operator is used to compare NULL value in the table?

a. != b. < > c. is d. in
6 Which clause is used to sort the records of a table?
7 Which key word is used to sort the records of a table in descending order?
8 Consider the table “Teacher” then write SQL query to display the details of teachers in descending
order of their salary.
9 Which clause is used to remove the duplicating rows of the table?
10 In SQL, write the query to display the list of databases.
11 Which query is used to find the total number of records in the table “School”.
a. SELECT COUNT( ) FROM SCHOOL;
b. SELECT LENGTH( ) FROM SCHOOL;
c. SELECT COUNT(*) FROM SCHOOL;

d. SELECT COUNT(ALL) FROM SCHOOL;

12 Which of the following group function ignore NULL values?
a. MAX( ) b. COUNT( ) c. SUM( ) d. All of these
13 Mr. Nil has created the table TEACHER in the database . Help him to write the SQL commands for
the question from (i) to (v) on the basis of table TEACHER given below:
TEACHER
TNo Tname Gender Age Department Salary DOJ
101 Ashok Kumar M 28 English 15000 2010-10-13
223 Rakesh Sharma M 32 Mathematics 25000 2008-08-23
143 Richa Saini F 27 Computer 22000 2014-07-15
402 Amit Gupta M 35 Science 17000 NULL
155 Sandeep Goyal M 42 Computer 19000 2005-12-27
165 Sangeeta F 33 English 14000 2008-11-17
237 Jyoti Sharma F 29 Hindi 12000 2012-05-13
 381 Manoj Kumar M 40 Science 21000 NULL
293 Rajendra Kapoor M 36 Art 16000 2004-04-25
109 Vinay Kumar M 32 English 18000 2011-03-10
(i) To display the Tno, Tname, Department and salary of all the teachers in ascending order of their
salary.
(ii) To remove the detail of teacher whose TNo is 155.
(iii) To display the total salary of the teachers of department English.
(iv) To display the value of maximum and minimum salary.
14 How is HAVING clause different from WHERE clause? Explain with the help of examples.
15 Consider the table given below:
Table: COMPANY
EMPID DEPARTMENT SALARY
E101 MARKETING 30000
E102 ACCOUNTS 45000
E103 PRODUCTION 32000
E104 ACCOUNTS 28000
E105 MARKETING 54000
E106 ACCOUNTS 40000
Identify the error in the following SQL statements. Rewrite the correct SQL statement.
(i) DELETE EMPID E103 FROM COMPANY;
(ii) UPDATE TABLE COMPANY SALARY = SALARY+1000;
16 Consider the table “Candidate”.
RNO NAME AGE ADDRESS
1 AMIT KUMAR 15 SHIVALIK NAGAR
2 VAIBHAV SHARMA 17 BHEL
3 ASHU SINGH 14 KANKHAL
4 TUSHAR 18 JWALAPUR
5 ABHINAV SHARMA 20 HARIDWAR
(i) Write a query to display the following result.

NAME
AMIT
VAIB
ASHU
TUSH
ABHI
(ii) Write the result of the following query.
Select name from candidate where name like ‘%A’;
17 What is the difference between the order by and group by clause when used alongwith the select
statement. Explain with an example.
18 How is ALTER command different from UPDATE command? Explain with suitable example.
19 Explain the Equi join in SQL with suitable example.
20 Consider the table STORE. Write the commands in SQL for (i) to (iii).
STORE
StoreId Name Location City Emp DateOpened SalesAmount
S101 Planet Fashion KarolBagh Delhi 7 2014-10-16 30000
S102 Trends Nehru Nagar Mumbai 11 2015-08-09 40000
S103 Vogue Vikas Vihar Haryana 10 2016-06-27 20000
 S104 Fashion Hub Darya Ganj Delhi 8 2015-02-18 45000
S015 Fashion Rage Bandra Mumbai 5 2016-09-22 60000

(i) To display the Name, Location, City, SalesAmount of stores in descending order of
SalesAmount.
(ii) To display store name, Location and Date opened of stores that were opened in 2015.
(iii) To display the detail of the stores of the cities Mumbai , Kolkata and Delhi.
21 Consider the table Vehicle. Write the following SQL queries:
VEHICLES

(i) To Display the average price of each type of vehicle having quantity more than 20.
(ii) To Count the type of vehicles manufactured by each company.
(iii) To Display the total price of all the types of vehicles.
22 Consider the tables “TEACHER” and “DEPARTMENT” as shown below. Write the statements in
SQL for (i) to (iii).
TEACHER
TeacherId Tname Gender Age Salary DeptId DOB
T11 Ashok Kumar M 28 25000 D1 1997-10-13
T12 Rakesh Sharma M 32 42000 D2 1998-08-23
T13 Richa Saini F 27 38000 D3 1990-07-15
T14 Amit Gupta M 35 27000 D2 NULL
T15 Sandeep Goyal M 42 39000 D4 1995-12-27
T16 Sangeeta F 33 24000 D1 1991-11-17
T17 Jyoti Sharma F 29 32000 D4 1999-05-13
T18 Manoj Kumar M 40 21000 D3 NULL

DEPARTMENT
DeptId Dname PhoneNo
D1 English 223344
D2 Mathematics 213435
D3 Hindi 342345
D4 Science 425432
(i) To display teacher’s Id, teacher’s name, department Id and name of the department of all the
teachers.
(ii) To remove the column DOB from the table TEACHER.
(iii) To increase the salary by 2000 of all the teachers.
(iv) To display teacherId, teacher’s name , salary and bonus(10% of salary) of all ths teachers.
 (v) To add a new column Address in the table TEACHER.
23 Consider the tables given below:
Table: FACULTY
TeacherId Name Address State PhoneNo
T101 Deepak Gupta A-15, Shivalik Nagar Uttrakhand 9866565432
T102 Sarita Sharma P-50, Adarsh Nagar Delhi 7876564323
T103 Kartik Goel K-5/52, Vikas Vihar Mumbai 9988776787
T104 Megha S-33, Greater Kailash Delhi 9528898976
T105 Ankit Chauhan Modern Colony Mumbai 7543276543

Table: COURSE
CourseId Subject TeacherId Fee
C11 Computer Science T103 3500
C12 Physics T101 4500
C13 Informatics Practices T103 4000
C14 Chemistry T102 3000
C15 Mathematics T105 5000
(i) To display TeacherId, Teacher name, CourseId and Subject of teachers living in Delhi.
(ii) To display TeacherId, Teacher name, Phone number, Subject and Fee of the teachers whose fee
is more than 4000.
(iii) To display TeacherId, Name of teacher, Subject of the teachers whose name contains ‘r’ as
third alphabet.
(iv) To display TeacherId, teacher name, subject of those teachers whose fee is between 3000 and
4000.
(v) Write the primary key and foreign key for the table COURSE.