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

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

Plus Two Computer Science Structured Query Language One Mark Questions and Answers

Question 1.
………………. form of SQL is designed for use within general-purpose programming languages such as COBOL, C, etc.
Answer:
Embedded SQL.

Question 2.
What is TCL?
Answer:
Transaction Control Language- component of SQL includes commands for specifying transactions.

Question 3.
Data dictionary is a special file in DBMS. What is it used for?
Answer:
Table details are stored in this file.

Question 4.
What does the following statement mean?
Name VARCHAR(30)
Answer:
Field Name can store up to 30 characters. It is a column definition.

Question 5.
Is there any data type available in SQL to store your date of birth information?
Answer:
Yes. DATE data type

Question 6.
Pick the odd one out.
(DEC, NUMBER, INT, DATE)
Answer:
DATE.

Question 7.
How do you ensure that the field ‘name’ will have some value always?
Answer:
using the constraint NOT NULL.

Question 8.
How to set the default value of column District in a table to Thrissur?
Answer:
District VARCHAR(30) DEFAULT ‘Thrissur’

Question 9.
…………… symbol is used as substitution operator in SQL.
Answer:
&

Question 10.
Is there any method to find the strings starting with letter ‘a’ from a field in SQL?
Answer:
Use LIKE operator, LIKE ‘a%’

Question 11.
How to check whether a particular field contains null values or not?
Answer:
Use operator IS NULL

Question 12.
‘ORDER BY’ clause is used for …………
Answer:
Sorting the results of a query by ascending(Asc) or Descending (Desc)

Question 13.
The built-in functions in SQL that return just a single value for a group of rows in a table are called ………..
Answer:
Summary functions or aggregate functions

Question 14.
How to find the number of values in a column in the table?
Answer:
Using the function COUNT()

Question 15.
………………. is the clause in SQL used for categorization.
Answer:
GROUP BY

Question 16.
Thomas wants to remove a table that he were using. How could you help him doing this?
Answer:
Using DROP TABLE command.

Question 17.
Pick the odd one out.
(SELECT, UPDATE, DELETE, DROP TABLE)
Answer:
DROP TABLE

Question 18.
Name the aggregate function that can be used to find the total number of records.
Answer:
COUNT()

Question 19.
Which of the following is an essential clause used with SELECT command?
(GROUP BY, ORDER BY, WHERE, FROM)
Answer:
FROM

Question 20.
Which of the following is not a column constraint?
(CHECK, DISTINCT, UNIQUE, DEFAULT)
Answer:
DISTINCT

Question 21.
Which of the following is a DDL command?
(SELECT, UPDATE, CREATE TABLE, INSERT INTO)
Answer:
CREATE TABLE

Question 22.
What are the logical operators used in SQL?
Answer:
And, Or, Not.

Question 23.
The …………. operator pf SQL is used to match a pattern with the help of %. February 2009
(a) BETWEEN
(b) AND
(c) LIKE
(d) OR
Answer:
(c) LIKE

Question 24.
The structure of a table Book is given below.

Write SQL query to Insert an additional column Purchase date of date type to the Books table.
Answer:
Alter table Book add(PurchaseDate Date);

Question 25.
Suppose we want to include a column in a table in which serial numbers are to be stored automatically on adding new records. Which constraint is to be used for that column during table creation?
Answer:
The constraint Autojncrement is used.

Question 26.
Which of the following cannot be used to name a table in SQL? Give the reason.
Answer:
(a) Studnt50
(b) Table
(c) $Employee
(d) Stock_123
Answer:
(b) Table. This is a keyword hence it cannot be used.

Question 27.
Which of the following commands is used to view the structure of a table?
(a) SHOW TABLES
(b) DESC
(C) SELECT
(d) DISPLAY
Answer:
(b) DESC

Question 28.
The command to eliminate the table CUSTOMER from a database is:
(a) REMOVE TABLE CUSTOMER
(b) DROP TABLE CUSTOMER
(c) DELETE TABLE CUSTOMER
(d) UPDATE TABLE CUSTOMER
Answer:
(b) DROP TABLE CUSTOMER

Question 29.
Which SQL command is used to open a database?
(a) OPEN
(b) SHOW
(c) USE
(d) CREATE
Answer:
(c) USE

Question 30.
Which is the keyword used with SELECT command to avoid duplication of rows in the selection?
Answer:
DISTINCT

Question 31.
Pick odd one out and write reason:
(a) WHERE
(b) ORDER BY
(c) UPDATE
(d) GROUP BY
Answer:
UPDATE. It is a command and others are clauses.

Question 32.
Which of the following clause is not used with SELECT command in SQL?
(a) GROUP BY
(b) WHERE
(c) SET
(d) ORDER BY
Answer:
(c) SET. This clause is used with UPDATE.

Question 33.
……………… operator in SQL is used with wild card characters for selection of records.
(a) LIKE
(b) IN
(c) NOT IN
(d) IN and NOT IN
Answer:
(a) LIKE

Plus Two Computer Science Structured Query Language Two Mark Questions and Answers

Question 1.
How is SQL different from other computer high-level languages?
Answer:
SQL means Structured Query Language. It is a relational database language, not a programming language like other high-level languages. It provides facilities to create a table, insert data into a table, retrieve information from a table, modify data in the table, etc.

Question 2.
Distinguish the SQL keywords UNIQUE and DISTINCT.
Answer:
1. Unique – It ensures that no two rows have the same value in a column while storing data. It is used with create command.

2. Distinct – This keyword is used to avoid duplicate values in a column of a table while retrieving data. It is used with select command.

Question 3.
Identify errors in the following SQL statement and rewrite it correctly. Underline the corrections.
CREATE student TABLE (admno PRIMARY KEY, roll no INT, name CHAR);
Answer:
The correct SQL statement is as follows.
CREATE TABLE student( admno INT PRIMARY KEY, roll_no INT, name CHAR(3));

Question 4.
Suppose a column named Fee does not contain any value for some records in the table named STUDENT. Write SQL statements to fill these blanks with 1000.
Answer:
UPDATE STUDENT SET Fee = 1000 WHERE Fee IS NULL;

Question 5.
Identify the errors in the following SQL statement and give reason for the error.
SELECT FROM STUDENT ORDER BY Group WHERE Marks above 50;
Answer:
In this query Group is the keyword hence it cannot be used. The correct query is as follows.
SELECT * FROM STUDENT WHERE Marks > 50 ORDER BY Marks;

Question 6.
Differentiate CHAR and VARCHAR data types of SQL.
Answer:

1. Char – It is used to store fixed number of characters. It is declared as char(size).
2. Varchar – It is used to store characters but it uses only enough memory.

Question 7.
Assume that CUSTOMER is a table with columns Cust_code, Cust_name, Mob_No and Email. Write an SQL statement to add the details of a customer who has no e-mail id.
Answer:
INSERT INTO CUSTOMER VALUES(1001, ‘ALVIS’, 9447024365, NULL);

Question 8.
Find the correct clause from the 2nd column for each SQL command in the 1_st column.

Answer:

Question 9.
Is ALL a keyword in SQL? Explain
Answer:
Yes, ALL retains all the duplicate values of a field

Question 10.
Differentiate LIKE ‘a%’ and LiKE ‘a_’
Answer:
%, it replaces a string but _ replaces only one character at a time.LIKE ‘a%’ retrieve all strings of any length that start with letter ‘a’ while LIKE ‘a_’ retrieve all two letter strings that start with letter ‘a’.

Question 11.
Is multiple sorting possible using ORDER BY clause? Explain
Answer:
Yes, by giving multiple field names separated by comma in the ORDER BY clause.

Question 12.
While inserting records into a table, Raju finds it is not possible to give more than 20 characters in the ‘name’ field. How can you help Raju solve this problem?
Answer:
Using ALTER TABLE command he can modify the width of field so that it can accommodate more than 20 characters.

Question 13.
After executing a query Mohan gets a message like ‘Table Altered’. What would have he done? Give the syntax.
Answer:
He would have used the ALTER TABLE command.
Syntax: ALTER TABLE ADD/MODIFY ();

Question 14.
During discussion one student said that DELETE and DROP TABLE commands are same. Do you agree with that? Justify.
Answer:
No, DROP TABLE removes a table entirely from the database whereas DELETE command deletes only records from an existing table.

Question 15.
1. Pick odd one out.
DROP TABLE, DELETE, ALTER TABLE, CREATE VIEW
2. Justify your answer
Answer:

1. DELETE
2. All others are DDL commands

Question 16.

1. Pick odd one out from the following.
2. Justify your answer.

Answer:

1. Group By
2. Group By is not a Constraint.

Question 17.
Consider the following SQL statements.
DELETE Name.
From Student
Where name = ‘Raju”
Find the error in the SQL if any and correct it.
Answer:
Delete from student where name = ‘Raju’

Question 18.
Consider the following Query in SQL.
From Employee Where branch = ‘Kannur’ Group By Department Having avg (salary) > 7000, Compare the ‘where’ clause and ‘Having’ Clause using the above query.
Answer:
‘where’ clause applies on single rows but Having clause applies on a group.

Question 19.
Some constraints in SQL are called column constraints. Some constraints are called table constraints. How do they differ?
Answer:
Column constraints are specified while defining each column, table constraints are specified once for the entire table at the end of table definition

Question 20.
1. Choose the odd one from the following.
Primary key, Unique, Distinct, Default, Check
2. Justify your choice
Answer:

1. Distinct
2. Distinct is used with select command while others are column constraints of create table command.

Question 21.
Name the most appropriate SQL data type required to store the following data.

1. Name of a student
2. Date of Birth of a student.
3. RollNo. of a student (in the range 1 to 50)
4. Percentage of marks obtained

Answer:

1. Varchar(70)
2. Date
3. Smallint OR Integer Or decimal
4. Dec(5, 2)

Question 22.
1. From the list given below select the names that cannot be used as a table name.
Adm_No, Date, Salary2006, Table, Column_Name, Address.
2. Justify your selection.
Answer:

1. Date, Table
2. There is a data type Date and Table is a keyword used to create a table. So these two are not used.

Question 23.
Classify the following SQL elements into two and give proper title for each category.
NOT NULL, AVG, COUNT, CHECK, SUM, DEFAULT
Answer:

Plus Two Computer Science Structured Query Language Three Mark Questions and Answers

Question 1.
As a part of your school project, you are asked to create a table Student with the fields RollNo, Name, Date of Birth and Score in IT. The constraints required are RollNo. is the primary key. Name cannot be empty.
Answer:
Create table student(RollNo decimal(2) not null primary key, Name varchar(20) not null, DOB date, Score number(2));

Question 2.
A table Employee consists of fields EmployeeNo, Name, Designation and Salary.
Consider that you are forced to give access to this table for an engineer from another company. But for security reasons you need to hide Salary from him. Employee No, Name, Designation, Salary.

1. Name the concept that provides this engineer, a facility to work on this table without viewing salary. Salary view
2. Write SQL query for implementing this SQL query

Answer:

1. Views
2. Create view Empview as select employeeno, Name, Designation from Employee.

Question 3.
Explain the keywords in the following query.
Answer:
SELECT allows to retrieve a subset of rows from the table, DISTINCT avoids duplication of courses from the table STUDENT, FROM is a keyword used to specify the name of the table

Question 4.
A table named student is given below.

Write answers for the questions based on the above table.

1. SQL statement to display the different courses available without duplication.
2. SQL statement to display the Name and Batch of the students whose percentage has a null value.
3. Output of the query select count (percentage) from Student.

Answer:

1. Select distinct batch from Student;
2. Select name, batch from Student where percent is null
3. 5

Question 5.
Once the creation of a table is over, one can perform two changes in the schema of the table. What are they? Give syntax.
Answer:
We can alter the table in two ways.
1. We can add a new column to the existing table using the following
syntax: ALTER TABLE ADD( );

2. We can also change or modify the existing column in terms of type or size using the following
syntax: ALTER TABLEMODIFY( );

Question 6.
Explain how pattern matching can be done in SQL with an example.
Answer:
pattern matching can be done using the operator LIKE while setting the condition with pattern matching. for eg., to display the names of all students whose name begins with letters ‘ma’, we can write the following query, SELECT name FROM STUDENT WHERE name LIKE ‘ma%’; here the character ‘%’ – substitutes any number of characters in the value of the specified column. Another character substitutes only one character of the specified column.

Question 7.
During the discussion of study your friend say that table and view are the same. How can you correct him?
Answer:
Tables and views are different. A view is a single virtual table that is derived from other physically existing tables. When we access a view we actually access the base tables.

We can use all the DML commands with the views but care should be taken as operation actually reflects in the base tables. The advantage of view is that without sparing extra storage space, we can use same table as different virtual tables. It also implements sharing along with privacy.

Question 8.
Find the errors if any of the following code.
Create table emp (name char, RolINo int(20));
Answer:
Here the argument size of data type char of field name is missing. So we can store a character only. The second error is the data type int has no argument. The correct statement is as follows Create table emp (name char(20), RolINo int);

Question 9.
Consider the following variable Declaration in SQL.
a. name char (25)
b. name Varchar (25)

1. Considering the utilisation of memory, which variable declaration is more suitable.
2. Justify your answer

Answer:
1. name varchar (25)
2. Because char data type is fixed length. It allocates maximum memory i.e, here it allocates memory for 25 characters maybe there is a chance of memory wastage. But Varchar allocates only enough memory to store the actual size.

Question 10.
Mr. Dilip wants to construct a table and implement some restrictions on the table.

1. Column can never have empty values.
2. One of the columns must be a key to identify the rows etc.

Can you help him to create that table satisfying the above restrictions with an example
Answer:

1. Use of create table, Not NULL.
2. Primary key Eg: Create table employee(RollNo decimal(3) not null primary key,Name Varchar(70) not null, Design Varchar(30), DOB Date,Salary Dec(7,2));

Question 11.
Write SQL query to construct a table student with fields Reg No, Stud Name, Sex, Course, grade, etc. As per the following conditions

1. Reg No should not be empty and using this column any rows in that table can be identified. Reg. No.
2. Student name should have some value.
3. Default value of sex be Female.
4. Grade should be any of the values: A+, A, B+, B, C+, C, D+, D, E

Answer:
Create table Student(RollNo decimal(3) not null primary key, Name Varchar(70) not null, sex char default ‘Female’,Grade char(2));

Question 12.
Ramu create Table employee with fields empId, empname, Designation, salary using SQL statements. Later he found that data type of emPId is typed as Integer instead of character and missed a field ‘Department’. Can you help him to solve this problem without recreating ‘employee’ table?
Answer:
Alter Table Employee Modify(Empld char(4)); AlterTable Employee Add(Dept char(15));

Question 13.
While creating a table Alvis give “Emp Details” for table name. Is it Possible? Write down the rules for naming a Table.
Answer:
It is not possible because there is a space between Emp and Details. The rules are given below:

1. It must not be a keyword(Keywords are reserved words and have predefined meaning)
2. It must begin with alphabets
3. Digits can be used followed by alphabets
4. Special characters cannot be used except underscore
5. We cannot give the name of another table

Plus Two Computer Science Structured Query Language Five Mark Questions and Answers

Question 1.
Consider the table ITEMS.

a) SELECT ITEMCODE, NAME FROM ITEMS WHERE CATEGORY = ‘Stationery’;
b) SELECT * FROM ITEMS WHERE SALES_ PRICE < UNIT_PRICE;
c) SELECT CATEGORY, COUNT(*) FROM ITEMS GROUP BY CATEGORY;
Answer:

Question 2.
Write SQL queries to SQL Query to
1. Create a table Employee with the fields given below.

2. List the name of all employees whose name’s second letter is ‘a’.
3. List the Name and Designation of employees whose Designation is not ‘Manager’
4. Increase the salary of ail employees by 10 percent.
5. Remove all managers whose salary is less than Rs. 10,000 from the table.
Answer:

1. Create table employee(Name Varchar(70), Desgn Varchar(30), DOB Date, Salary Dec(7,2));
2. Select Name from Employee where Name like
3. Select Name, Desgn from Employee where Desg no’Manager’;
4. Update Employee set Salary = Salary + Salary * .01;
5. Delete from Employee where Desgn = ‘Manager’ and Salary < 10000;

Question 3.
A table Student consists of fields Roll No, Name, Batch and Percent. Write SQL statements to

1. Display RolINo and Name of students whose percentage is Iessthan90 and greater than 70.
2. Display RolINo and Name of all students in science batch whose percentage is more than 90.
3. Display Names of all students in commerce and science batches.
4. Display the average Percent of students in each batch.
5. Display RolINo and Name in the ascending order of Batch and descending order of Percent.

Answer:

1. Select RollNo, Name from Student where percent < 90 and percent > 70;
2. Select RollNo, Name from Student where Batch = ’Science’ and percent > 90;
3. Select Name from Student where batch = ’Science’ Or ’batchCommerce’;
4. Select batch, Avg(percent) from Student group by batch;
5. Select RollNo, Name from Student order by batch, percent desc;

Question 4.
1. Classify the following SQL commands.
Create table, Insert Into, AlterTable, Delete, Up-date, Drop Table, Select.
2. List the features of each category.
Answer:
1. DDL – Create Table, Alter Table, Drop Table.
DML- Insert Into, Delete, Update, Select.

2. DDL – DDL means Data Definition Language. It is used to create the structure of a table, modify the structure of a table and delete the structure of a table. DML -DML means Data Manipulation Language. It is used to insert records into a table, modify the records of a table, delete the records of a table and retrieve the records from a table.

Question 5.
Explain the available database integrity constraints.
Answer:
1. NOT NULL: it specifies that a column can never have null values, i.e., not empty

2. UNIQUE: it ensures that no two rows have same value in the specified column.

3. PRIMARY KEY: it declares a column or a set of columns as the primary key of the table. This constraint makes a column NOT NULL and UNIQUE.

4. DEFAULT: it sets a default value for a column when the user does not enter a value for that column.

5. Auto_increment: This constraint is used to perform auto increment the values in a column. That automatically generates serial numbers. Only one auto-increment column per table is allowed.

Question 6.
Consider a table student with fields Reg No, Stud Name, Sex, Course, total score.
Write SQL queries for the following:
RegNo, Studname, Sex, Course, Total score.

1. Enter a Record
2. List the Detail’s of all students
3. Display Details of the student whose name ends with ‘Kumar’.
4. List all Female students who got more than 50 marks.
5. List all students who are studying either science or in Commerce group.
6. List details of those students who are studying Humanities in the Descending order of their names.

Answer:

1. Insert into Student values (52, ‘JOSE’, ‘Male’, ‘Science’, 500);
2. Select*from Student;
3. Select* from Student where name like ’%Kumar’;
4. Select * from Student where sex=’Female’ and Total >50;
5. Select * from Student where course = ‘science’ or course =‘commerce’;
6. Select* from Student where course-‘Humanities’ order by name desc;

Question 7.
Construct a Table Product with the following fields using SQL.
Product code – Consist of Alphanumeric code.
Product name – Consist of maximum of 30 Alphabets
Unit price – Numeric values
Quantity – Numeric values
Product price – Numeric Values
Write Query for the following:

1. Enter 5 records in the table (not give values for product prices)
2. Calculate product price (unit price × quantity)
3. List all product whose unit price ranging from 10 to 20
4. Calculate total price of all products.

Answer:
Create table Product(ProdCode Varchar(20) not null primary key, ProdName char(30), UPrice decimal(7,2), Qty decimal(6), ProdPrice decimal(7,2));

1. Insert into Product(Product Code,Product Name, Unit Price, Quantity) values (‘101 ’, ‘LUX’, 29.50,500); Similarly insert four more records
2. Update Product set ProdPrice=Qty * UPrice;
3. Select ProdName from Product where UPrice be-tween 10 and 20;
4. Select sum(ProdPrice) from Product;

Question 8.
Mr. Wilson wants to store the details of students. The details consist of different types of data. Explain different data types used in SQL to store data.
Answer:
The different data types are
1. Char (Fixed):
It is declared as char (size). This data type is used to store alphanumeric characters. We have to specify the size. If no size is specified, by default we can store only one character. Eg: name char(20)

2. Variable Character:
It is declared as varchar (size). This data type is also used to store alphanumeric characters. But there is a slight difference. lt allocates only enough memory to store the actual size.
Eg: name varchar(20)

3. Decimal:
It is declared as Dec(size, scale), where size is the number of digits and scale is the maximum number of digits to the right of the decimal point.
Eg: weight dec(3, 2)

4. Integer:
It is declared as int. It does not have any arguments. It takes more memory. Eg: ROllNO int.

5. Small Integer:
It is declared as smallint. It takes less memory ROllNO int.

6. Date:
It is used to store date. Eg: DOB date.

7. Time:
It is used to store time. Eg: Joining_Time Time.

Question 9.
A company wants to create a table to store its employees details. Write SQL Commands for the following :

1. Create a table with EMP table having fields EMPNO primary key varchar(10), Name varchar(20), Salary number(6), Department varchar(3)
2. Insert values to table
3. List all employees whose salary > 10,000 Salary 10,000
4. Display name and salary in the order of name.

Answer:

1. Create table EMP (EMPNO varchar(10) not null primary key, Name varchar(20), Salary decimal(6), Deptvarchar(3));
2. Insert into EMP values (‘1001 ’, ‘ALVIS’, 50000, ‘Sales’);
3. Select * from EMP where salary > 10000;
4. Select name, salary from EMP order by name;

Question 10.
Create a table ‘Savings’ with the following fields. Acc No (Integer), Name (char), age (Integer) and balance (Number) where Acc No is the primary key. Write SQL commands for the following.

1. Insert data in all the fields for 3 records inserted
2. Display the list of account holders in the ascending order Of their names.
3. Display the list of all account holders having age between 20 and 30
4. Display the name and Acc No of customers having a balance > 10 lakhs

Answer:
Create table Savings(Accno decimal(4) not null primary key, name char(2), age decimal(3), Balance decimal(8, 2));

1. Insert into savings values (501, ‘Andrea’, 18,45000)
2. Select * from Savings order by name
3. Select * from Savings where age between 20 and 30
4. Select name.accno from Savings where balance >100000

Question 11.
Which are the components of SQL? How do they help to manage database?
Answer:
The components of SQL are given below.
a. DDL commands ( 3 commands):

1. Create table – Used to create a table.
2. Alter table – Used to modify existing column or add new column to an existing table. There are 2 keywords used ADD and MODIFY.
3. Drop table – Used to remove a table from the memory.

b. DML commands ( 4 commands):

1. Select – Used to select rows from a table. The keyword From is used with this. Where clause is used to specify the condition.
2. Insert – Used to insert new records into a table. So the keyword used is INTO.
3. Delete – Used to delete records in a table.
4. Update – Used to modify the records in a table the keyword used is set.

c. DCL (Data Control Language) commands:

1. Grant – It grants permission to the users to the database
2. Revoke – It withdraws user’s rights given by using Grant command

Plus Two Computer Science Chapter Wise Questions and Answers