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

Students can Download Chapter 9 Structured Query Language Questions and Answers, Plus Two Computer Application Chapter Wise Questions and Answers helps you to revise the complete Kerala State Syllabus and score more marks in your examinations.

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

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

Question 1.
______form of SQL is designed for use with in 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:
Ip 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.
Howto 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:
CREATETABLE

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

Question 23.
The______operator of 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.

BookNo	Integer
Title	Varchar (200)
Author	Varchar(100)
Price	Dec(5, 2)

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.
(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 strucutre 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 selction?
Answer:
DISTINCT

Question 31.
Pick odd one out and write reason:
(a) WHERE
(b) ORDER BY
(c) UPDATE
(d) GROUP BY
Answer:
(c) 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 wildcard characters for selection of records,
(a) LIKE
(b) IN
(c) NOT IN
(d) IN and NOT IN
Answer:
(a) LIKE

Plus Two Computer Application 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 statement 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 key word 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:

• 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.

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 2^nd column for each SQL command in the1 st column.

Command	Clause
INSERT	SET
SELECT	FROM
UPDATE	INTO
ALTER	ADD

Answer:

Command	Clause
INSERT	INTO
SELECT	FROM
UPDATE	SET
ALTER	ADD

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 <name> ADD/MODIFY (<column>);

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 remove 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.
   NOT NULL, Group By, Check, Unique, Default
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.
SELECT Department, avg(salary)
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 (maximum 70 characters)
2. Date of Birth of a student.
3. RollNo. of a student (in the range 1 to 50)
4. Percentage of marks obtained (correct to 2 decimal places)

Answer:

1. Varchar(70)
2. Date
3. Smallint OR Integer Or decimal(2)
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, Salary 2006, Table, Column_Name, Address.
2. Justify your selection.

Answer:

1. Date, Table
2. There is a data type Date and Table is a key word 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:
1. Aggregate functions:
AVG
COUNT
SUM

2. Column Constraints:
NOT NULL
CHECK
DEFAULT

Plus Two Computer Application 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.

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

Answer:

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

Question 3.
Explain the keywords in the following query.
SELECT DISTINCT course FROM Student;
Answer:
SELECT allows to retrieve a subset of rows from the table, DISTINCT avoids duplication of courses from the table STUDENT, FROM is a key word 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.

• We can add a new column to the existing table using the following syntax,
  ALTER TABLE <tablename>ADD(<cloumnname> <type> <constraint>);
• We can also change or modify the existing column in terms of type or size using the following syntax, ALTER TABLE<tablename>MODIFY(<column> <newtype>);

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, R0IIN0 int(20));
Answer:
Here the argument size of data type char of field name is missing. So we can’t 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), RollNo int);

Question 9.
Consider the following variable Declaration in SQL.

• name char (25)
• 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 may be 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, Desgn 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.
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 empld, 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));
• Alter Table 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 keywond(Key words 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 under score
5. We cannot give a name of another table

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

Question 1.
Consider the table ITEMS.

1. SELECT ITEMCODE, NAME FROM ITEMS WHERE CATEGORY = ‘Stationery’;
2. SELECT * FROM ITEMS WHERE SALES_ PRICE < UNIT_PRICE;
3. SELECT CATEGORY, COUNT(*) FROM ITEMS GROUP BY CATEGORY;’

Answer:
1.

ltem_Code	Name
0001	Pencil
0002	Pen
0003	Notebook
0007	Pen

2.

3.

Category	Count(*)
Stationery	4
Footwear	1
Fruits	2

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

EmpNo	Integer
Name	Character of size 70
Designation	Character size 30
Date of birth	Date
Salary	Decimal (7,2)

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 all 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 ‘_a%’;
3. Select Name, Desgn from Employee where Desgn<> ’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 RollNo and Name of students whose percentage is Iessthan90 and greater than 70.
2. Display RollNo and Name of all students in science batch whose percentage is more than
3. Display Names of all students in commerce and science batches.
4. Display the average Percent of students in each batch.
5. Display RollNo 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 batch = ’Commerce’;
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, Update, 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 is automatically generate 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:

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 x quantity)
3. List all product whose unit price ranging from 10 to 20
4. Calculate total price of all product.

Answer:
Create table Product(ProdCode Varchar(20) not null primary key, ProdName char(30), UPrice decimal(7, 2), Qty decimal(6), ProdPricedecimal(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 between 10 and 20;
4. Select sum(ProdPrice) from Product;

Question 8.
Mr. Wilson wants to store the details of students. The details consists 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 alpha numeric 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 alpha numeric characters. But there is a slight difference.lt allocates only enough memory to store the actual size.
Eg: namevarchar(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 small int. 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
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), Dept varchar(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
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.
DDL commands (3 commands)

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

DML commands (4 commands)

• Select: Used to select rows from a table. The keyword From is used with this. Where clause is used to secify the conition.
• Insert: Used to insert new records into a table. So the keyword used is INTO.
• Delete: Used to delete records in a table.
• Update: Used to modify the records in a table the keyword used is set.

DCL (Data Control Language) commands

• Grant: It grants permission to the users to the database
• Revoke: It withdraws user’s rights given by using Grant command.

Plus Two Computer Application Structured Query Language Let Us 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:

1. Insert data into the fields (at least 10 records).
2. Display the details of all students.
3. List the details of Science group students.
4. Count the number of students in each course.
5. Add a new column named Total to store the total marks.
6. Fill the column Total with the sum of the six marks of each student.
7. Display the highest total in each group.
8. Find the highest, lowest and average score in Subject 6 in Commerce group.
9. Display the names in the alphabetical order in each course.
10. Display the name of the student with the highest total.

Answer:

1.

2.

3. mysql>select * from student1 where course = ’Science’;

4. mysql>select course,count(*) from → student1 group by course;

5. mysql>altertable student1 add(total int);

6. mysql>update studentl set total=mark1 + mark2 + mark3 + mark4 + mark5 + mark6;

7. mysql>select course,max(total) from student1 group by course;

8. mysqt>select max(mark6), min(mark6), avg(mark6) from student1;

9. mysql>select course, name from studentl order by course, name;

10. mysql>select name from studentl where total=(select max(total) from student1);

Question 2.
The structure of 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.

1. Insert data into the fields (at least 10 records).
2. Display the details of all items in the table.
3. Display the names of items and total price of each.
4. List the items manufactured by a company (specify the name) available in the table.
5. Find the number of items from each manufacturer.
6. Display the details of items with the highest price.
7. List the names of items whose price is more than the average price of all the items.
8. Display the names of items purchased after 1 -1 -2015.
9. Get the details of items manufactured by two or three companies (specify the names) available in the table.
10. 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,
UnitPrice float(8, 2),
Qty int,
mfrer char(30));
1. mysql>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’);

2. mysql>select * from shop;

3. mysql>select name, UnitPrice*Qty from shop;

4. mysql>select name from shop where mfrer=’Tech Com’;

5. mysql>select mfrer,count(*) from shop group by mfrer;

6. mysql>select * from shop where UnitPrice = (select max(UnitPrice) from shop);

7. mysql>select name from shop where UnitPrice > (select avg(UnitPrice) from shop);

8. mysql> select “from shop where DOP>’2015-1-1’;

9. mysql> select name frogi 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:

1. Insert data into the fields (at least 10 records).
2. Display the details of all female teachers in the table.
3. List the details of male teachers in the Science department.
4. Display the names and basic pay of teachers in the Language department whose basic pay is Rs. 21000/-or more.
5. Display the names and 71 % of basic pay of the teachers.
6. Find the number of teachers in each department.
7. Display the details of teachers whose basic pay is less than the average basic pay.
8. List the male teachers who joined before 1-1-2010.
9. Increment the basic pay of all teachers by Rs. 1000/-.
10. Delete the details of teachers from the Language department.

Answer:
mysql>create table hsst(Teacherld int primary key, name varchar(30) not null,
gender char,
DOJ date,
Dept varchar(15),
BP float(8, 2));
1. 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’,’Humanities’, 22360);

2. mysql>select * from hsst where gender=’F’;

3. mysql>select * from hsst where gender=’M’ and Dept=’Science’;

4. mysql>select name, BP from hsst where dept=’Language’ and BP >21000;

5. mysql>select name, BP*.71 from hsst;

6. mysql>select Dept,count(*) from hsst group by Dept;

7. mysql>select * from hsst where BP < (select avg(BP) from hsst);

8. mysql>select * from hsst where gender=’M’ and DOJ<’2010-01-01’;

9. mysql>update hsst set BP=BP+1000;

10. mysql>delete from hsst where Dept= ’Language’;

Question 4.
The structure of a table is 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:

1. Insert data into the fields (at least 10 records).
2. Display the details of customers having SB account.
3. Display the names of customers with a balance among greater than Rs. 5000/-.
4. Display the details of female customers with a balance amount greater than Rs. 10000/-
5. Count the number of male and female customers.
6. Display the names of customers with the highest balance amount.
7. Display the names of customers whose names end with ‘kumar’.
8. Update the balance amount of a particular customer with a deposit amount of Rs. 2000/-.
9. Display the details of customers with a tax deduction of 2% of the balance amount for those who have Rs. 2,00,000/- in their account.
10. Delete the details of customers with current account.

Answer:
mysql>create table customer
AccNo int primary key,
name varchar(30),
gender char,
DOJ date,
TypeOfAcc char(8),
Balance double(10, 2));
1. mysql>insert into customer values (1001,’Adeline’,’F’,’2008-11-26’,’SB’, 50000.00);
mysql>insert into customer values (1002,’Aivis’.’M’,’2007-05-19’,’Current’, 500000.00);
mysql>insert into customer values (1003,’Andrea’,’F’,’2012-07-29’,’SB’, 450000.00);

2. mysql>select * from customer where TypeOfAcc=’SB’;

3. mysql>select name from customer where Balance>5000;

4. mysql>select name from customer where gender=’F’ and Balance>10000;

5. mysql>select gender, count(*) from customer group by gender;

6. mysql>select name from customer where Balance=(select max(Balance) from customer);

7. mysql> select name from customerwhere name like “%kumar”;

8. mysql>update customer set Balance= Balance+ 2000 where Accno= 1001;

9. mysql>select Accno.name, Balance*.02 from customerwhere Balance>=200000;

10. mysql > delete from customerwhere Type Of Acc = ‘Current’;

Plus Two Computer Application Structured Query Language Let Us 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.

1. Strings (i) and (ii) only
2. Strings (i), (iii) and (iv) only ,
3. Strings (i) and (iii) only
4. All the strings

Answer:

Question 9.
List any five built-in functions of SQL and the value returned by each. (2 Mark)
Answer:
Aggregate functions:

1. Sum()- find the total of a 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 to form groups of records, not conditions and 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)

1. INSERT INTO
2. SELECT
3. UPDATE

Answer:

1. INSERT INTO – VALUES
2. SELECT – FROM
3. UPDATE – SET

Question 13.
Consider the given table Customer and write the output of the following SQL queries: (5 Mark)

1. SELECT * FROM customer WHERE Amount>25000;
2. SELECT Name FROM customer
   WHERE Branch IN (‘Calicut, ‘Kannur’);
3. SELECT COUNT (*) FROM customer WHERE Amount < 20000;
4. SELECT Name FROM customer WHERE Name like “%m%”;
5. SELECT * FROM customer ORDER BY Amount DESC;

Answer:
1.

2.

3.

4.

5.

Question 14.
Distinguish between COUNT (*) and COUNT (column-name). (2 Mark)
Answer:

• Count(): find the number of non null values in a column.
• Count(*): This is used to find the number of records with at least one field.

Question 15.
Considerthe given table ITEMS. (5 Mark)

1. Suggest a suitable primary key for the above table. Give justification.
2. Write SQL statements for the following:
   • To list all stationery items.
   • To list item code, name, and profit of all items.
   • To count the number of items in each category.
   • To list all stationery items in the descending order of their unit price.
   • To find the item with the highest selling price.
   • To create a view that contains the details of all stationery items.

Answer:
1. Item code is the primary key for the table

2. SQL statements:

(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 command 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 <tablename>ADD(<cloumnname> <type> <constraint>);
• We can also change 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 rows? Write the format. (2 Mark)
Answer:
UPDATE command is 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.