VELAMMAL NEW GEN EDU NETWORK

Grade – 12 Computer Science

Model-1 RevisionWorksheet
DAY-4_21-09-2024 Chapter-Computer Network

1) Differentiate between char(n) and varchar(n) data types with respect to databases.
2) Which command is used to view the list of tables in a database?
3) Give one point of difference between an equi- join and a natural join.
4) A table, ITEM has been created in a database with the following fields: ITEMCODE, ITEMNAME,
QTY, PRICE.Give the SQL command to add a new field, DISCOUNT (of type integer) to the ITEM table.
5). Charu has to create a database named MYEARTH in MYSQL. She now needs to create 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 REMARKS
TYPE
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.
6) Write queries (a) to (d) based on the tables EMPLOYEE and DEPARTMENT given below:
Table : EMPLOYEE
EID NAME DOB DEPT DESIG SALA
ID RY
120 Alisha 23-Jan-1978 D001 Manager 75000
123 Nitin 10-Oct-1977 D002 AO 59000
129 Navjot 12-Jul-1971 D003 Supervisor 40000
130 Jimmy 30-Dec-1980 D004 Sales Rep
131 Faiz 06-Apr-1984 D001 Dep 65000
Manager

Table : DEPARTMENT
DEPTID DEPTNAME FLOORNO
D001 Personal 4
D002 Admin 10
D003 Production 1
D004 Sales 3
(a) To display the average salary of all employees, department wise.
(b) To display name and respective department name of each employee whose salary is more than 50000.
(c) To display the names of employees whose salary is not known, in alphabetical order.
(d) To display DEPTID from the table EMPLOYEE without repetition.
7) In SQL, name the clause that is used to display the tuples in ascending order of an attribute.
8) In SQL, what is the use of IS NULL operator?
9) Write any one aggregate function used in SQL
10) Which of the following is a DDL command?
1
 a) SELECT b) ALTER c) INSERT d) UPDATE
11) In SQL, write the query to display the list of tables stored in a database.
12)Which of the following types of table constraints will prevent the entry of duplicate rows?
a) Unique b) Distinct c) Primary Key d) NULL
13) A departmental store MyStore is considering to maintain their inventory using SQL to store the
data. As a database administer, Abhay has decided that : • Name of the database - mystore
• Name of the table - STORE
• The attributes of STORE are as follows: ItemNo – numeric
ItemName – character of size 20 Scode - numeric
Quantity – numeric
Table : STORE
ItemN ItemName Scode Quantity
o
2005 Sharpener 23 60
Classis
2003 Ball Pen 0.25 22 50
2002 Get Pen 21 150
Premium
2006 Get Pen Classic 21 250
2001 Eraser Small 22 220
2004 Eraser Big 22 110
2009 Ball Pen 0.5 21 180
(a) Insert the following data into the attributes ItemNo, ItemName and SCode respectively in the given table
STORE.
ItemNo = 2010, ItemName = “Note Book” and Scode = 25
(b) Abhay want to remove the table STORE from the database MyStore. Which command will he use from the
following:
a) DELETE FROM store;
b) DROP TABLE store;
c) DROP DATABASE mystore;
d) DELETE store FROM mystore;
(c) Now Abhay wants to display the structure of the table STORE, i.e, name of the attributes and their respective
data types that he has used in the table. Write the query to display the same.
14) Write SQL commands for the following queries (i) to (v) based on the relations Teacher and Posting given
below:
Table : Teacher
T_ Name A Department Date_of_join Salary Gender
ID g
e
1 Jugal 34 Computer Sc 10/01/2017 12000 M
2 Sharmila 31 History 24/03/2008 20000 F
3 Sandeep 32 Mathematics 12/12/2016 30000 M
4 Sangeeta 35 History 01/07/2015 40000 F
5 Rakesh 42 Mathematics 05/09/2007 25000 M
6 Shyam 50 History 27/06/2008 30000 M
7 Shiv Om 44 Computer Sc 25/02/2017 21000 M
8 Shalakha 33 Mathematics 31/07/2018 20000 F
Table : Posting
P_ID Department Place
1 History Agra
2 Mathematics Raipur
3 Computer Delhi
Science
i. To show all information about the teacher of History department.
ii. To list the names of female teachers who are in Mathematics department.
iii. To list the names of all teachers with their date of joining in ascending order.

2
iv. To display teacher‟s name, salary, age for male teachers only.
v. To display name, bonus for each teacher where bonus is 10% of salary
15) Which key word is used to sort the records of a table in descending order?
16) Which clause is used to sort the records of a table?

17) Which command is used to modify the records of the table?

18) Which clause is used to remove the duplicating rows of the table?

19) Write SQL queries for (i) to (iv), which are based on the table: STUDENT given in the previous question:

(i) To display the records from table student in alphabetical order as per the name of the student.
(ii) To display Class, Dob and City whose marks is between 450 and 551.
(iii) To display Name, Class and total number of students who have secured more than 450 marks, class wise.
(iv) To increase marks of all students by 20 whose class is “XII”.