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

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

Plus Two Computer Application Structured Query Language 1 Mark Important Questions

Question 1.
______ keyword is used in SELECT query to eliminate duplicate values in a column. (MARCH-2016)
UNIQUE
b) DISTINCT
NOT NULL
d) PRIMARY
Answer:
DISTINCT

Question 2.
______ clause of SELECT query is used to apply condition to form groups of records. (MAY-2016)
a) orderby
(b) groupby
(c) having
(d) where
Answer:
(b) groupby

Question 3.
_______ command in SQL is used to display the structure of a table.(MAY-2017)
a) LIST
b) STRUCT
c) DESCRIBE
d) SHOW
Answer:
c) DESCRIBE

Plus Two Computer Application Structured Query Language 2 Marks Important Questions

Question 1.
How will you add a new column to an existing table using SQL statement ? (MARCH-2016)
Answer:
Alter command with add keyword is used to add a new column to an existing table. Avertable <table name> add <column name> <datatype> [<size>] [<constraint>][FIRST/AFTER<column name>];
Eg : Avertable ACCOUNTS add Type varchar(10) AFTER Name;

Question 2.
What is a view? How can we create a view using SQL statement? (MAY-2016)
Answer:
A view is a virtual table. That doesnot really exists but is derived from one or more tables. It is used to view a small part of the entire database.
Create view command is used to create a view. Syntax eg:- create view <view name> as select * from <table name> [Where <condition>];
eg:- create view studentView as select from student;

Question 3.
Explain primary key constraint with an example. (MAY-2017)
Answer:
Primary Key: A primary key is one of the Candidate Keys. It is a set of one or more attributes that can uniquely identify tuples in a relation. Rollno, AdmNo, EmpCode etc are the examples of primary key.

Plus Two Computer Application Structured Query Language 3 Marks Important Questions

Question 1.
Answer the following questions . (MARCH-2016)

a) Write SQL statements to do the following :
i) Display all the details of accounts with amount greater than 50000 in Emakulam branch.
ii) Display Acc. No., Branch and Amount in the descending order of amount.
iii) Display the number of accounts in each branch.
Answer:
i) Select * from ACCOUNTS where Amount>50000 and Branch = “Emakulam”
ii) Select Acc.No, Branch, Amount from ACCOUNTS order by Amount desc;
iii) Select Branch, count (*) from ACCOUNTS group by branch.

b) Write SQL statements to do the following
i) Add a new record into the table.
ii) Update the amount of Sanjay to 100000.
iii) Delete the details of Anil.
Answer:
i) Insert into ACCOUNTS values (1006, ‘Alvis’, ‘Thrissur’, 50000);
ii) Update ACCOUNTS set Amount = 100000 where Name = ‘Sanjay’;
iii) Delete from ACCOUNTS where Name = ‘Anil’;

Question 2.
a) Explain SQL statements used to insert and delete data from a table. (MAY-2016)
b) Explain any two DDL commands
Answer:
a) Insert command is used to insert new records into a table,. The keyword used with insert is into
Syntax: Insert into <table name>[column1, Column2,………, column N] values [value1,Value 2,……… value N];
eg:- Insert into student (Regno, name) Values(101,‘Jose’);

b) delete This command is used to delete one or all records from a table
Syntax: delete from <table name> [where con-dition];
eg:- delete from student; -This command deletes all records.

b) DDL Commands

1) Create table :- This command is used to create a table.
Syntax: create table <table name>
(column name> <data type>[<constraint>]
[, column name> <data type>,]……… );
eg:- create table student (Rno int primary key, name varchar(20));

2) Alter table :- This command is used to change the structure or add a new column to an existing table.
Modify, Add are the keywords used.
Syntax: Alter table <table name> modify column name><data type>
[<size>] [constraint];
eg:- Alter table student modify name varchar (30);
Syntax: Alter table <table name>
Add <new column name> <data type>
[<size>] [constraint>] [first j After <column-name>];
eg:- Alter table student add grade varchar (2);

3) Drop table:- This command is used to delete the structure of the table.
Syntax: Drop table <table name>;
eg:- Drop table student;

Question 3.
Write SQL for (MAY-2017)
a) Create a table student with the data [nafne_char(20), rollno number(3),marks number(3)].
b) List name and rollno of all students
c) List name and rollno of students having marks>600.
Answer:
a) create table student(name varchar(20) primary key.rollno int,marks int);
b) select name.rollno from student;
c) select name.rollno from student where marks>600;

Question 4.
An employee table contains name, empno, basicpay, desig. (MAY-2017)
Write SQL for
a) Display name, empno and basicpay of all managers,(desig=”manager”)
b) Display empno and salary of all employees
(salary=basicpay + da)
(da=basicpay  *  1.15)
c) Display name and empno of all the employees whose basicpay<10000.
Answer:
a) select name,empno,basicpay from employee where design=”manager”;
b) select empno,basicpay + basicpay * 1.15 from employee;
c) select name,empno from employee where basicpay< 10000;

Plus Two Computer Application Structured Query Language  5 Marks Important Questions

Question 1.
The structure of the table ‘EMPLOYEE’ is given below. (MARCH-2017)

Write SQL statement for the following
a) Insert a record into the table.
b) Update DA with 60% basic pay
c) Display the details of employees whose basic pay is greaterthan 20000.
d) Rename the table EMPLOYEE to EMPDETAILS
Answer:
INSERT INTO EMPLOYEE VALUES
(101,’Alvis’,25000,NULL,NULL);
b) UPDATE EMPLOYEE SET DA= Basicpay * 6;
c) SELECT * FROM EMPLOYEE WHERE Basicpay > 20000;
d) ALTER TABLE EMPLOYEE RENAME TO EMPDETAILS;

Plus Two Computer Application Chapter Wise Previous Questions and Answers