Plus Two Computer Science Chapter Wise Previous Questions Chapter 9 Structured Query Language

Kerala Plus Two Computer Science Chapter Wise Previous Questions and Answers Chapter 9 Structured Query Language

Question 1.
The keyword is used in a SELECT statement to avoid duplicate rows. [March – 2008] (1)
Answer:
Distinct

Question 2.
The columns used in a table are called ……………………….. [March – 2008] (1)
Answer:
Attributes

Question 3.
Manju wants to create a table for a new inventory application. The table contains the details of products in a shop. The details are item code, description, stock quantity, reorder level. Write SQL commands to create this table with necessary constraints. [March – 2008] (3)
a) Itencode should not be empty for any row and it must be primary key.
b) Description should have some value.
c) The reorder level should be at least 10.
Answer:
Create table inventory item code mt not null primary key, despite char(30) not null.ock_qty mt, rol mt);

Question 4.
Name any two aggregate functions. [March – 2008] (1)
Answer:
SUM().AVG()

Question 5.
A village hospital has maintained a database for pa-tients with fields IPNO primary key varchar(10), Patient Name varchar(20), Age number(3), Roomno number(3) Write SQL queries to do the following: [February – 2008] (5)
a) Modify the table by adding field Doctor name not null
b) Update Doctor name field with a value ‘LINDA’ for a particular record with IPNO=30
c) Display all patients group by Doctors name.
d) Display all patients in the age group 20 to 30 years age
e) Display details of all patients whose name start with ‘An’
Answer:
a) Alter table patient add(Doctorname char(20) not null);
b) Update patient set Doctorname= ‘LINDA’where IPNO=30;
c) Select ‘from patient group by Doctorname;
d) Select * from patient where age between 20 and 30;
e) Select * from patient where PatientName like ‘An%’;

Question 6.
Duplication of data is called __________ [February – 2008] (1)
Answer:
Data Redundancy

Question 7.
Explain aggregate functions. [February – 2008] (3)
OR
Write any four Aggregate functions in SQL and its use.
Answer:
1) Sum() – To find the total of column name specified as the argument.
2) Avg()- To find the average of column name speci-fied as the argument.
3) Min() – To find the smallest value of column name specified as the argument.
4) Max() – To find the largest value of column name specified as the argument.
5) Count()- To find the number of values in the col-umn specified as the Argument.

Question 8.
Which command is used to remove a table from a database? [February – 2008] (1)
Answer:
DROP TABLE <table name>

Question 9.
View can be used just like a table. Then what is the purpose of creating view? [February – 2008] (2)
Answer:
A view is a virtual table. That means it really does not exist. We can create a view based upon a table. If we create a view all users cannot see the entire data base.

It gives high security.

Question 10.
Ceena wants to delete the records or all commerce Students having marks less than 35 from the table named Student. Write the SQL command for this. Name, Reg No,subject and marks are the fields of the table. [March – 2009] (2)
Answer:
Delete from Student where Subject-Commerce’ and Marks<35.

Question 11.
Which key word can be used with SELECT command to avoid duplication of rows in the selection? [March – 2009] (1)
Answer:
Distinct

Question 12.
A company wants to create a table to store the stock details.Write SQL commands forthe following. [June – 2009] (5)
1) Create a table with name stock and fields item code, name, qty and unit price.
2) Insert 2 records into the table.
3) Display the items in the ascending order of name.
4) List all the items where qty is less than 10
5) List all items with price greater than 100.
Name field is char type and others are numeric.
Answer:
1) Create table stock(itemcode decimal(4), name char(25),qty decimal(4),pricedecimal(8,2));
2) insert into stock values (101, ‘Pears’, 1000,48);
3) select name from stock order by name;
4) select name from stock where qty < 10;
5) select name from stock where price < 100;

Question 13.
What are the features of Data manipulation language which makes it attractive? [June – 2009] (2)
Answer:
DML related to the instance of the data base, provides commands for inserting rows into table, deleting rows from the table. It also facilitates the modification of the Contents of the tables and it allows the retrieval of information from the data base. The DML commands are insert, update, delete and select.

Question 14.
Prabha created a table in SQL with 10 records. Which DML command is used to change the values in a column of specified rows? Write the format also. [June – 2009] (2)
Answer:
The update is the DML command used to change the values in a column of specified rows. The keyword set is used and the clause where is also used to change the specified row. The syntax is given below: Update <table name> set <column name> = value where <condition>;

Ex: update Student set age =17 where rollno = 1;

Question 15.
Write a query on the CUSTOMER table whose out put will exclude all customers with a RATING <=100, unless they are located in Kochi. [February – 2009] (2)
Answer:
Select * from CUSTOMER where RATING <=100 and Place= ‘Kochi’

Question 16.
A table BANK consists of fields A/c. No, Customer Name, age,Type of deposits (Savings, Current, Fixed), Deposited Amount.

Write SQL statements to [March – 2010] (5)
a) Display A/c. No. and Customer Name whose age is greater than 60.
b) Display A/c.No. and Customer Name of those who having Savings A/c.
c) Display the Customer Names which starting with the alphabet A.
d) Display the A/c. No. and Customers Names of those who deposited above 1 lakh rupees as Fixed Deposit.
e) To include a new field Address.
Answer:
a) Select Acc No, Name from Bankwhere age>=60;
b) Select accno, name from Bank where type_of_deposits=’savings’;
c) Select name from Bank where name like‘A%’;
d) Select accno, name from Bank where type_of_deposits=‘savings’and amount>100000;
e) Alter table Bank add (Address char(30))

Question 17.
Find the odd one out. [March – 2010] (1)
(a)Oracle
(b) SQLServer
(c) DDL
(d) MySql
Answer:
DDL

Question 18
Can you find any alternate keys in the following table? Justify your answer. [March – 2010] (2)

Answer:
The candidate key that is not the primary key is called the alternate key. Here the candidate keys are Reg.No, Roll No.+year, Roll No. + name. If we set Reg.No. as the primary key then the other keys are alternate keys.

Question 19.
A table Student consists of fields Rollno, Name, Batch and Mark. [June – 2010]
Write SQL statements to
a) Display Rollno and Name of Students where mark is less than 90 and greater than 70.
b) Display Rollno and Name of all Students in science batch whose mark is more than 90.
c) Display Names of all Students in Commerce and Humanities batches.
d) Display the Rollno and Name in the ascending order of batch and descending order of Mark.
e) Display the number of Students in each batch. (5)
Answer:
a) Select RollNo.Name from Student where Mark<90 and Mark > 70;
b) Select RollNo.Name from Student where . Batch-Science’ and Mark > 90;
c) Select Name from Student where Batch- Com-merce’ OrBatch=’Humanities‘;
d) Select RollNo.Name from Student order by Batch, Mark desc;
e) Select Batch,Count(Mark) from Student group by Batch;

Question 20.
Write the essential clause required for each of the following SQL command. [June – 2010]
a) Insert Into
b) Select
c) Update
Answer:
a) Insert Into – Values
b) Select – From
c) Update – Set

Question 21
Write the syntax of Create Table Command. [June – 2010] (2)
Answer:
Create table <table name>(<column name> <datatype> [(size)] [<column constraints-],<column names <datatype> [(size)] [<column constraints>],);

Question 22.
Consider the following table Student: [March -2011] (3)

a) Write a query to display names and percent of all mate Students in the Science branch.
b) Write an UPDATE command to set the Course attribute with ‘Humanities’ whose NAME is “Shiji”.
Answer:
a) select Name, Per from Student where Sex=’M’ and Course=’Science’;
b) Update Student set Course=’Humanities’ where Name-Shiji’;

Question 23.
The SQL Data Manipulation Language (DML) includes a Query language based on relational algebra. [March -2011] (3)
a) Give any four commands used in DML.
b) Write an SQL statement to INSERT values to the fields (adm_no, name, course, percent) with values (101, “Anoop”, “Science”, 75) for the table STUD.
Answer:
a) Insert, delete, select and update
b) insert into STUD values(101,’Anoop’,’Science’,75);

Question 24.
Tina wants to create a t^ble named mark with the fields name, regno, mark 1, mark 2 and total. Write the SQL commands to create the table with regno as primary key. [March – 2012] (3)
Answer:
Create table mark(name char(20), regno decimal(4) not null primary key,mark1 decimal(3), mark2 decimal^), total decimal(3));

Question 25
While naming a table in SQL, Reena typed ‘Select’ as thetable name. Isthere any mistake? Give reason. [March – 2012] (3)
Answer:
Yes, The Key words (Reserved words) or commands cannot used to name a table. Select is a keyword.

Question 26.
Give the correct syntax of the queries in SQL for the following: [March – 2016]
a) Renaming a table
b) Deleting rows from a tble .
C) Changing definition of a column
d) Removing columns from a table
e) Addinganewcolumn (5)
Answer:
a) Alter table <table name>
Rename to <New table name>

b) Delete from <table name>
[where <condition>];

c) Alter table <table name>
modify <column name>cdata type>
[<size>] [<constraint>];

d) Altertable <table narne>drop <column name>;

e) Altertable<table name> add <column name>
<data type> [<size>] [<constraint>];

Question 27.
Give the output obtained with the pattern match ‘i—”In the string board. [March – 2016] (1)
Answer:
“_ _ _” matches any string of exactly 3 characters without any space in between them, Hence “board” will not select.

Question 28.
What happens when we use DELETE FROM command without AWHERE clause? [Say – 2016] (1)
Answer:
All the tu pies (rows/records) are deleted from the table.

Question 29.
Ifa table named mark” has field’s regNo. subcode and marks write SQL statements for the following: [Say – 2016]
a) List the subject codes eliminating duplicates.
b) List the marks obtained by Students with subject codes 3001 and 3002.
c) Arrange the table based on marks for each source
d) List all the Students who have obtained marks above 90 for the subject codes 3001 and 3002.
e) List the contents of the table in the descending order of marks. (5)
Answer:
a) Select distinct subcode from mark;
b) Select marks from mark where subCode= 3001 orsubCode = 3002;
c) Select * from mark order by subcode, marks;
d) Select * from mark where subcode in (3001,3002) and marks >90;
e) Select *from mark order by marks desc;

Question 30
Distinguish between DDL and DML and give examples for each type. [Say – 2016] (5)
Answer:
Data Definition Language(DDL) – It is used to define the structure of a table.

Data Definition Language is used to specify the definitions of Database Schema. The result of the compilation of DDL statements is a set of tables stored in a special file called data dictionary.

DDL Commands are Create table, Alter table and Drop table.

Data Manipulation Language(DML) – It is used to add, retrieve, modify and delete records in a database.lt is a language that enable users to access or manipulate data in the database. It also provides interfaces with programming languages.

DML Commands are select, insert, update and delete

Question 31.
Null values in tables are specified as “null”. State whether true or false. [Say – 2016] (1)
Answer:
False. (without double quotes i.e. null is then it is true.)

Question 32.
Which command is used to delete the table? [Say – 2017]
a) delete from
b) drop table
c) deletetable
d) dropview (1)
Answer:
b) drop table

Question 33.
Differentiate between CHAR and VARCHAR data types in SQL. [Say – 2017] (3)
Answer:
Char – It is used to store fixed number of characters. It is declared as char(size).

Varchar – It is used to store-characters but it uses only enough memory.

It is declared as varchar(size).

Question 34.
Name the most appropriate SQL data type required to store the following data: [Say – 2017]
a) Name of a Student (maximum 70 characters)
b) Date of Birth of a Student
c) Percentage of marks obtained (correct to 2 decimal places) (3)
Answer:
a) varchar
b) date
c) decimal

Question 35.
As part of your school project you are asked to create a relation Student.contains the details of 10 Students with the fields Roll No., Name, Date of Birth and Score in IT. The constraints required are – Roll No. is the primary key, name cannot be empty and score in IT should be less than 60. Based on this table Student answerthe following queries in relational algebra.
a) Display the details of Students whose-score is greater than 50.
b) Display the name of Students whose score lies between 45 and or equal to 60. [Say – 2017] (5)
Answer:
Student

a) σIT_Score > 50 (Student)

b) π Name (σ IT_Score > 45 and IT_Score<=60 (Student))

Plus Two Computer Science Chapter Wise Practice Questions and Answers

Question 1.
The structure of a table is given to store the details of marks scored by Students in an examination. (5 Mark)

Write SQL statements for the creation of the table and the following requirements:
a) Insert data into the fields (at least 10 records).
b) Display the details of all Students.
c) List the details of Science group Students.
d) Count the number of Students in each course.
e) Add a new column named Total to store the total marks.
Fill the column Total with the sum of the six marks of each Student.
g) Display the highest total in each group.
h) Find the highest, lowest and average score in Subject 6 in Commerce group.
i) Display the names in the alphabetical order in each course.
j) Display the name of the Student with the highest total.
Answer:

a)

b)

c) mysql>seljept * from Student where course=’Science’;
d) mysql> select course,count(*) from -> Studentl group by course;
e) mysql>altertable Studentl add(total int);
f) mysql>update Studentl set total=mark1+ . mark2+mark3+mark4+mark5+mark6;
g) mysql>select course,max(total) from Studentl group by course;
h) mysql>selectmax(mark6),min(mark6), avg(mark6) from Studentl;
i) mysql>select course,name from Studentl order by course,name;
j) mysql>select name from Studentl where total=(selectmax(total) from Studentl);

Question 2.
The structure Qf a table is given to store the details of items in a computer shop. (5 Mark)

Write SQL statements for the creation of the table and the following requirements.
a) Insert data into the fields (at least 10 records).
b) Display the details of all items in the table.
c) Display the names of items and total price of each.
d) List the items manufactured by a company (specify the name) available in the table,
e) Find the number of items from each manufacturer.
f) Display the details of items with the highest price.
g) List the names of items whose price is more than the average price of all the items. ‘
h) Display the names of items purchased after 1 -1 -2015.
i) Get the details of items manufactured by two or three companies (specify the names) available in the table.
j) Display the details of items from a company (specify the name) with a stock of more than 20 pieces.
Answer:
mysql>create table shop (ItemNo int primary key, name char(30) not null,
DOP date,
UnitPricefloat(8,2),
Qty int,
mfrer char(30));

a) mysq!>insert into shop values(1,’Keyboard’, ‘2014-08-21 ’,300.00,100,’Tech Com’); mysql>insert into shop values(2,’Mouse’, ‘2014-08-21’,300.00,100,’Tech Com’); mysql>insert into shop values(3,’Speaker’,’2015- 08-21’,550.00,100,’I Ball’); mysql>insert into shop values(4,’CPU’,’2015-07- 21’,3500.00,100,’AMD’); mysql>insert into shop values(5,’RAM’, ‘2015-08-1 ’,1300.00,100,’Hynix’);
b) mysql>select * from shop;
c) mysql>select name, UnitPrice*Qty from shop;
d) mysql>select name from shop where mfrer=’Tech Com’;
e) mysql>select mfrer,count(*) from shop group by mfrer;
1) mysql>select * from shop where UnitPrice = (select max(UnitPrice) from shop);
g) mysql>select name from shop where UnitPrice > (select avg(UnitPrioe) from shop);
h) mysql> select* from shop where DOP>’2015-1-1 ’;
i) mysql> select name from shop where mfrer=’l Ball’and Qty>20;

Question 3.
The structure of a table is given to store the details of higher secondary school teachers. (5 Mark)

Write SQL statements for the creation of the table and the following requirements:
a) Insert data into the fields (at least 10 records).
b) Display the details of all female teachers in the table.
c) List the details of male teachers in the Science department.
d) Display the names and basic pay of teachers in the Language department whose basic pay is Rs. 21000/-or more.
e) Display the names and 71 % of basic pay of the teachers.
f) Find the numbe of teachers in each department.
g) Display the details of teachers whose basic pay is less than the average basic pay.
h) List the male teachers who joined before 1-1-2010.
i) Increment the basic pay of all teachers by Rs. 1000/-.
j) Delete the details of teachers from the Language department.
Answer:
mysql>create table hsst (Teache rld in primary key, name varchar(30) not null,
gender char,
DOJ date,
Dept varchar(15),
BP float(8,2));
a) mysql>insert into hsst values(1,’Jose’,’M’,‘2002-01-01 ‘,‘Science’,25660);
mysql> insert into hsst values(2’Christy’,’F’,‘2012-01-01 ‘,‘Commerce’,20740);
mysql>insert into hsst values(3,’Geejo George’,’M’, ‘2007-01-01 ‘,‘Humani’ties’,22360);

b) mysql>select * from hsst where gender=’F’;

c) mysql>select * from hsst where gender=’M’ and
Dept= Science’;

d) mysql>select name, BP from hsst where
dept=’Language’ and BP >21 000;

e) mysql>select name, BP*.71 from hsst;
f) mysql>select Dept,count(*) from hsst group by Dept;
g) mysql>select * from hsst where BP < (select avg(BP) from hsst);
h) mysql>select * from hsst where gender=’M’ and DOJ<2010-01-01’;
i) mysql>update hsst set BP=BP+1000;
j) mysql>delete from hsst where Dept=’Language’;

Question 4.
The structure of a table s given to store the details of customers in a bank. (5 Mark)

Write SQL statements for the creation of the table and the following requirements:
a) insert data into the fields (at least lo records).
b) Display the details of customers having SB ac count.
c) Display the names of customers with a balance among greater than Rs. 5000/-.
d) Display the details of female customers with a balance amount greater than Rs. 10000/-
e) Cpunt the number of male and female customers.
f) Display the names of customers with the highest balance amount.
g) Display the names of customers whose names end with ‘kumar.
h) Update the balance amount of a particular cus tornerwith a deposit amount of Rs. 2000/-.
i) Display the details of customers with a tax deduction of 2% of the balance amount for those wtio have Rs. 2,00,000/- ¡n their account.
j) Delete the details of customers with current acco un
Answer:
mysql>create table customer
AccNo mt primary key,
name varchar(30),
gender char,
DOJ date,
TypeOfAcc char(8),
Balance double(10,2));

a) mysql>inserl ¡ nb customer values
(1001 ,‘Adeline’,’F’,’2008-11-26’’SB’,50000.O0);

mysql>insert into customer values
(1 002,’Aivis’ ‘M’, ‘2007-05-19 ‘Current50O000.00);
mysql>insert ¡rito customer values
(1003, ‘Andrea’,’F’, 2012-07-29’, ‘SB’450000.00);

b) mysql>select from customer where TypeofAcc=’SB’;
c) mysql>select name from customer where Balance>5000;
d) mysql>select name from customer where gender=’F’ and Balance>1 0000;
e) mysql>select gender,count(*) from customer group by gender;

mysql>select name from customer where Balance=(select max(Balance) from customer);
g) mysqi> select name from customer where name like “%kumar”;
h) mysql’update customer set Balance= Balance+ 2000whereAccno=1001;
i) mysql>select Accno,name, Balance*.02 from customer where Balance>=200000;
j) mysql > delete from customer where Type OtAcc = ‘Current’;

Plus Two Computer Science Chapter Wise Assess Questions and Answers

Question 1.
The command to remove rows from a table ‘CUSTOMER’ is: (1 Mark)
a) Remove From Customer
b) Drop Table Customer
c) Delete From Customer
d) Update Customer
Answer:
c) Delete From Customer

Question 2.
If values for some columns are unknown, how is a row inserted? (2 Mark)
Answer:
In this occasion the column list must be included, following the table name.
Eg. INSERT INTO <TABLE NAME> (COLUMN NAME1, COLUMN NAME2, ….) VALUES (VALUE1, VALUE2,… .);

Question 3.
Distinguish between CHAR and VARCHAR data types of SQL. (2 Mark)
Answer:
Char- It is used to store fixed number of characters. It is declared as char(size).
Varchar – It is used to store characters but it uses only enough memory.
It is declared as varchar(size).

Question 4.
What is the difference between PRIMARY KEY and UNIQUE constraints? (2 Mark)
Answer:
Unique – It ensures that no two rows have the same value in a column.
Primary key – Similar to unique but it can be used only once in a table.

The strings
(i) and
(iv) only

Question 5.
What do you mean by NULL value in SQL? (1 Mark)
Answer:
Null is a key word in SQL that represents an empty * value.

Question 6.
Which of the following is the correct order of clauses for the SELECT statements? (1 Mark)
a) Select, From, Where, Order By
b) Select, From, Order By, Where
c) Select, Where, From, Order By
d) Select, Where, Order By, From
Answer:
a. Select From, Where, Order By

Question 7.
The SQL operator …………….. is used with pattern matching. (1 Mark)
Answer:
Like Operator

Question 8.
Read the following strings : (1 Mark)
i) ‘Sree Kumar’
ii) ‘Kumaran’
iii) ‘Kumar Shanu’
iv) ‘Sreekumar’
Choose the correct option that matches with the pattern ‘%Kumar’, when used with LIKE operator in a SELECT statement.
a) Strings (i) and (ii) only
b) Strings (i), (iii) and (iv) only
c) Strings (i) and (iii) only
d) All the strings
Answer:

Question 9.
List any five built-in functions of SQL arid the value- returned by each. (2 Mark)
Answer:
Aggregate functions
1. Sum()- find the total of column.
2. Avg() – find the average of a column.
3. Min()- find the smallest value of a column.
4. Max() – find the largest value of the column.
5. Count() -find the number of values in a column.

Question 10.
Distinguish between WHERE clause and HAVING clause. (2 Mark)
Answer:
Where clause is used to specify the condition.
Syntax: Select * from Student where roll=1;

Having clause is used with Group By to give conditions and to ofrm groups of records, not individual rows.

Question 11.
Write any four DML commands in SQL. (2 Mark)
Answer:
The four DML commands are
1) Insert
2) Update
3) Select
4) Delete

Question 12.
Write the essential clause required for each of the following SQL command. (2 Mark)
a) Insert Into
b) Select
c) Update
Answer:
a) Insert Into – Values
b) Select – From
c) Update – Set

Question 13.
Consider the given table Cùstomer and write the out put of the following SQL queries: (5 Mark)

a) SELECT * FROM customer WHERE
Amount>25000:

b) SELECT Name FROM customer
WHERE Branch IN (‘Calicut, ‘Kannur’);

c) SELECT COUNT (*) FROM customer WHERE
Amount < 20000;

d) SELECT Name FROM customer WHERE Name like %m%;
e) SELECT * FROM customer ORDER BY Amount DESC;
Answer:

Question 14.
Distinguish between COUNT (*) and COUNT (column-name). (2 Mark)
Answer:
Count() – find the number of non null values in a column.
Cou nt(*) This is used to find the number of records with at least one field.

Question 15.
Consider the given table ITEMS. (5 Mark)

a) Suggest a suitable primary key for the above table. Give justification.
b) write SQL statements for the following:
i) To list all stationery items.
ii) To list itern code. name and profit of all items.
iii) To count the number of items in each category.
iv) To list all stationery items in the descending order of their unit price.
v) To find the item with the highest selliñg price.
vi) To, create a view that contains the details of all stationery items.
Answer:
a) Item code is the primary key for the table

b) (i)

(ii)

(iii)

(iv)

(v)

(vi)

Question 16.
What are the different modifications that can be made on the structure of a table? Which is the SQL corn mand required for this? Specify the clauses needs for each type of modification. (3 Mark)
Answer:
Alter table command is used to modify existing column or add new column to an existing table. There are 2 keywords used ADD and MODIFY.

We can alter the table in two ways.

We can add a new column to the existing table using the following syntax,

ALTER TABLE <tabiename>ADD(<cloumnname> <type> <constraint>);

We can also cha rige or modify the existing column in terms of type or size using the following syntax,
ALTER TABLE<tablename>MODIFY(<column> <newtype>);

Question 17.
A table is created in SQL with 10 records. Which SQL command is used to change the values in a column of specified ¿ows? Write the format. (2 Mark)
Answer:
UPDATE command s used for this.
Syntax : UPDATE <table name> set <column name>=value where condition.

Question 18.
Name the keyword used with SELECT command to avoid duplication of values in a column. (1 Mark)
Answer:
DISTINCT

Question 19.
Distinguish between DISTINCT and UNIQUE in SQL. (2 Mark)
Answer:
DISTINCT – This keyword is used to avoid duplicate values in a column of a table.
Unique – It ensures that no two rows have the same value in a column.

Question 20.
Pick the odd one out and give reason: (1 Mark)
a) CREATE
b) SELECT
c) UPDATE
d) INSERT
Answer:
a) CREATE, It is a DDL command the others are DML commands

Plus Two Computer Science Chapter Wise Previous Questions