Samacheer Kalvi 12th Computer Science Solutions Chapter 12 Structured Query Language (SQL)

Students can Download Computer Science Chapter 12 Structured Query Language (SQL) Questions and Answers, Notes Pdf, Samacheer Kalvi 12th Computer Science Book Solutions Guide Pdf helps you to revise the complete Tamilnadu State Board New Syllabus and score more marks in your examinations.

Tamilnadu Samacheer Kalvi 12th Computer Science Solutions Chapter 12 Structured Query Language (SQL)

Samacheer Kalvi 12th Computer Science Structured Query Language (SQL) Text Book Back Questions and Answers

PART – 1
I. Choose The Best Answer

Question 1.
Which commands provide definitions for creating table structure, deleting relations, and modifying relation schemes?
(a) DDL
(b) DML
(c) DCL
(d) DQL
Answer:
(a) DDL

Question 2.
Which command lets to change the structure of the table?
(a) SELECT
(b) ORDER BY
(c) MODIFY
(d) ALTER
Answer:
(d) ALTER

Question 3.
The command to delete a table is ……………………..
(a) DROP
(b) DELETE
(c) DELETE ALL
(d) ALTER TABLE
Answer:
(a) DROP

Question 4.
Queries can be generated using …………………….
(a) SELECT
(b) ORDER BY
(c) MODIFY
(d) ALTER
Answer:
(a) SELECT

Question 5.
The clause used to sort data in a database ……………………….
(a) SORT BY
(b) ORDER BY
(c) GROUP BY
(d) SELECT
Answer:
(b) ORDER BY

PART – II
II. Answer The Following Questions

Question 1.
Write a query that selects all students whose age is less than 18 in order wise?
Answer:
SELECT * FROM STUDENT WHERE AGE <= 18 ORDER BY NAME.

Question 2.
Differentiate Unique and Primary Key constraint?
Answer:
The unique constraint ensures that no two rows have the same value in the specified columns. Primary key constraint declares a field as a Primary key which helps to uniquely identify a record. The primary key is similar to unique constraint except that only one field of a table can be set as primary key.

Question 3.
Write the difference between table constraint and column constraint?
Column constraint:
Column constraint apply only to individual column.

Table constraint:
Table constraint apply to a group of one or more columns.

Question 4.
Which component of SQL lets insert values in tables and which lets to create a table?
Answer:
Insert values in tables – DML Create a table – DDL

Question 5.
What is the difference between SQL and MySQL?
Answer:
SQL-Structured Query Language is a language used for accessing databases while MySQL is a database management system, like SQL Server, Oracle, Informix, Postgres, etc. MySQL is a RDBMS.

PART – III
III. Answer The Following Questions

Question 1.
What is a constraint? Write short note on Primary key constraint?
Answer:
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. Constraints could be either on a column level or a table level.

Primary Key Constraint:
This constraint declares a field as a Primary key which helps to uniquely identify a record. It is similar to unique constraint except that only one field of a table can be set as primary key. The primary key does not allow NULL values and therefore a field declared as primary key must have the NOT NULL constraint.
Example showing Primary Key Constraint in the student table:
CREATE TABLE Student
(
Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint
Name char(20)NOT NULL,
Gender char(l),
Age integer,
Place char(10),
);

Question 2.
Write a SQL statement to modify the student table structure by adding a new field?
Answer:
ALTER TABLE Student MODIFY Address char(25);

Question 3.
Write any three DDL commands
a. CREATE TABLE Command
Answer:
You can create a table by using the CREATE TABLE command.
CREATE TABLE Student
(Admno integer,
Name char(20), \
Gender char(1),
Age integer,
Place char(10),
);

b. ALTER COMMAND
The ALTER command is used to alter the table structure like adding a column, renaming the existing column, change the data type of any column or size of the column or delete the column from the table.
Alter table Student add address char;

c. DROP TABLE:
Drop table command is used to remove a table from the database.
DROP TABLE Student;

Question 4.
Write the use of Savepoint command with an example?
SAVEPOINT command
Answer:
The SAVEPOINT command is used to temporarily save a transaction so that you can rollback to the point whenever required. The different states of our table can be saved at anytime using different names and the rollback to that state can be done using the ROLLBACK command.
SAVEPOINT savepoint_name;
UPDATE Student SET Name = ‘Mini ’ WHERE Admno=105;
SAVEPOINT A;

Question 5.
Write a SQL statement using DISTINCT keyword?
Answer:
DISTINCT Keyword:
The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data. For Example:
SELECT DISTINCT Place FROM Student;
Will display the following data as follows :
SELECT * FROM Student;
Output
Place
Chennai
Bangalore
Delhi

PART – IV
IV. Answer The Following Questions

Question 1.
Write the different types of constraints and their functions?
Answer:
Type of Constraints
Constraints ensure database integrity, therefore known as database integrity constraints. The different types of constraints are :

(i) Unique Constraint
This constraint ensures that no two rows have the same value in the specified columns. For example UNIQUE constraint applied on Admno of student table ensures that no two students have the same admission number and the constraint can be used as:
CREATE TABLE Student
(
Admno integer NOT NULL UNIQUE, → Unique constraint
Name char (20) NOT NULL,
Gender char (1),
Age integer,
Place char (10),
);
The UNIQUE constraint can be applied only to fields that have also been declared as NOT NULL.
When two constraints are applied on a single field, it is known as multiple constraints. In the above Multiple constraints NOT NULL and UNIQUE are applied on a single field Admno, the constraints are separated by a space and at the end of the field definition a comma(,) is added. By adding these two constraints the field Admno must take some value ie. will not be NULL and should not be duplicated.

(ii) Primary Key Constraint
This constraint declares a field as a Primary key which helps to uniquely identify a record. It is similar to unique constraint except that only one field of a table can be set as primary key. The primary key does not allow NULL values and therefore a field declared as primary key must have the NOT NULL constraint.
Example showing Primary Key Constraint in the student table:
CREATE TABLE Student
(
Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint
Name char(20)NOT NULL,
Gender char(I),
Age integer,
Place char(10),
);
In the above example the Admno field has been set as primary key and therefore will help us to uniquely identify a record, it is also set NOT NULL, therefore this field value cannot be empty.

(iii) DEFAULT Constraint
The DEFA ULT constraint is used to assign a default value for the field. When no value is given for the specified field having DEFAULT constraint, automatically the default value will be assigned to the field.
Example showing DEFAULT Constraint in the student table:
CREATE TABLE Student
(
Admno integer NOT NULL PRIMARY KEY,
Name char(20)NOTNULL,
Gender char(1),
Age integer DEFAULT = “17”, → Default Constraint
Place char(10),
);
In the above example the “Age” field is assigned a default value of 17, therefore when no value is entered in age by the user, it automatically assigns 17 to Age.

(iv) Check Constraint:
This constraint helps to set a limit value placed for a field. When we define a check constraint on a single column, it allows only the restricted values on that field. Example showing check constraint in the student table:
CREATE TABLE Student
(
Admno integer NOT NULL PRIMARY KEY
Name char(20)NOTNULL,
Gender char(1),
Age integer (CHECK<=19),
→ Check Constraint
Place char(10),
);
In the above example the check constraint is set to Age field where the value of Age must be less than or equal to 19.
Note
The check constraint may use relational and logical operators for condition.

(v) TABLE CONSTRAINT
When the constraint is applied to a group of fields of the table, it is known as Table constraint. The table constraint is normally given at the end of the table definition. Let us take a new table namely Studentl with the following fields Admno, Firstname, Lastname, Gender, Age, Place: CREATE TABLE Student 1
Admno integer NOT NULL,
Firstname char(20),
Lastname char(20),
Gender char(1),
Age integer,
Place char(10),
PRIMARY KEY (Firstname, Lastname) → Table constraint
);
In the above example, the two fields, Firstname and Lastname are defined as Primary key which is a Table constraint.

Question 2.
Consider the following employee table. Write SQL commands for the qtns.(i) to (v)?
Answer:

1. To display the details of all employees in descending order of pay.
2. To display all employees whose allowance is between 5000 and 7000.
3. To remove the employees who are mechanic.
4. To add a new row.
5. To display the details of all employees who are operators.

Output:

1. SELECT * FROM employee ORDER BY DESC;
2. SELECT * FROM employee WHERE ((allowance >= 5000) AND(allowance <= 7000));
3. DELETE FROM employee WHERE desig = “Mechanic”;
4. INSERT INTO employee(Empcode, Name, desig, pay, allowance) VALUES(‘M1006’, ‘RAM’, ‘Mechanic’,22000, 8000);
5. SELECT * FROM employee WHERE desig = ‘operator’;

Question 3.
What are the components of SQL? Write the commands in each? Components of SQL?
SQL commands are divided into five categories:
Answer:

a. Data Definition Language
The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.
SQL commands which comes under Data Definition Language are:

b. Data Manipulation Language
A Data Manipulation Language (DML) is a computer programming language used for adding (inserting), removing (deleting), and modifying (updating) data in a database.
SQL commands which comes under Data Manipulation Language are :

c. Data Control Language:
A Data Control Language (DCL) is used for controlling privileges in the database SQL commands: GRANT, REVOKE

d. Transactional Control Language:
Transactional control language (TCL) is used to manage transactions i.e. changes made to the data in the database.
SQL commands: COMMIT, ROLLBACK, SAVEPOINT

e. Data Query Language:
The Data Query Language (DQL) have commands to query or retrieve data from the database.
SQL commands: SELECT.

Question 4.
Construct the following SQL statements in the student table –
(i) SELECT statement using GROUP BY clause.
(ii) SELECT statement using ORDER BY clause.

(i) GROUP BY clause
The GROUP BY clause is used with the SELECT statement to group the students on rows or columns having identical values or divide the table into groups. For example to know the number of male students or female students of a class, the GROUP BY clause may be used. It is mostly used in conjunction with aggregate functions to produce summary reports from the database.
The syntax for the GROUP BY clause is
SELECT <column-names> FROM <table-name> GROUP BY <column-name>HAVING condition];
To apply the above command on the student table :
SELECT Gender FROM Student GROUP BY Gender;
The following command will give the below given result:

SELECT Gender, count(*) FROM Student GROUP BY Gender;

(ii) ORDER BY clause
The ORDER RTclause in SQL is used to sort the data in either ascending or descending based on one or more columns.
1. By default ORDER BY sorts the data in ascending order.
2. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
The ORDER BY clause is used as:
SELECT <column-name> [,<column-name>,….] FROM <table-name> ORDER
BY <column1>,<column2>, …ASC\ DESC;
For example :
To display the students in alphabetical order of their names, the command is used as
SELECT * FROM Student ORDER BY Name;
The above student table is arranged as follows :

Question 5.
Write a SQL statement to create a table for employee having any five fields and create a table constraint for the employee table?
Answer:
CREATE TABLE EMPLOYEE
(Empcode integer NOT NULL,
Name char(20),
desig char(20),
pay integer,
allowance integer,
PRIMARY KEY(Name, desig));

Practice Programs

Question 1.
Create a query of the student table in the following order of fields name, age, place and admno?
Answer:
CREATE TABLE Student(Name char(30), age integer, place char(30), admno integer)).

Question 2.
Create a query to display the student table with students of age more than 18 with unique city?
Answer:
SELECT * FROM student WHERE age >= 18 GROUP BY city.

Question 3.
Create a employee table with the following fields employee number, employee name, designation, date of joining and basic pay?
Answer:
CREATE TABLE employee(empNo integer, ename char(30), desig char(30), doj datetime, basic integer);

Question 4.
In the above table set the employee number as primary key and check for NULL values in any field?
Answer:
CREATE TABLE employee(empno integer NOT NULL PRIMARY KEY, ename char(30) NOT NULL, desig char(30), doj datetime, basic integer).

Question 5.
Prepare a list of all employees who are Managers?
Answer:
SELECT * FROM employee WHERE desig = ’Managers’.

Samacheer kalvi 12th Computer Science Structured Query Language (SQL) Additional Questions and Answers

PART – I
I. Choose the correct answer

Question 1.
The SQL was called as …………………….. in early 1970’s.
(a) squel
(b) sequel
(c) seqel
(d) squeal
Answer:
(b) sequel

Question 2.
SQL means ………………………..
Answer:
Structured Query Language

Question 3.
RDBMS Expansion ………………………
Answer:
Relational DataBase Management System

Question 4.
Expand ANSI ………………………
(a) American North South Institute
(b) Asian North Standard Institute
(c) American National Standard Institute
(d) Artie National Standard Institute
Answer:
(c) American National Standard Institute

Question 5.
ANSI Published SQL standard in the year ………………………..
(a) 1986
(b) 1982
(c) 1984
(d) 1989
Answer:
(a) 1986

Question 6.
The latest SQL was released in ……………………….
(a) 1987
(b) 1992
(c) 2008
(d) 2012
Answer:
(c) 2008

Question 7.
The latest SQL standard as of now is …………………….
Answer:
SQL 2008

Question 8.
Identify which is not a RDBMS package …………………….
(a) MySQL
(b) IBMDB2
(c) MS-Access
(d) Php
Answer:
(d) Php

Question 9.
A ……………………… is a collection of tables.
Answer:
database

Question 10.
CRUD means ……………………
(a) creative reasoning under development
(b) create read update delete
(c) create row update drop
(d) calculate relate update data
Answer:
(b) create read update delete

Question 11.
A …………………… is a collection of related data entries and it consist of rows and columns.
Answer:
table

Question 12.
……………………….. is the vertical entity that contains all information associated with a specific field in a table
(a) Field
(b) tuple
(c) row
(d) record
Answer:
(a) Field

Question 13.
A ………………………… is a horizontal entity in the table.
Answer:
record

Question 14.
DDL means …………………………
Answer:
Data Defnition Language

Question 15.
Match the following:
1. DDL – (i) Modify Tuples
2. Informix – (ii) Create Indexes
3. DML – (iii) MySQL
4. DCL – (iv) Grant
(a) 1-ii, 2-iii, 3-i, 4-iv
(b) 1-i, 2-ii, 3-iii, 4-iv
(c) 1-iv, 2-iii, 3-ii, 4-i
(d) 1-iv, 2-i, 3-ii, 4-iiii
Answer:
(a) 1-ii, 2-iii, 3-i, 4-iv

Question 16.
The SQL used in high level programming languages is ………………………….
Answer:
Embedded Data Manipulation Language

Question 17.
WAMP stands for ……………………….
Answer:
Windows, Apache, MySQ1 and PHP

Question 18.
To work with the databases, the command used is …………………….. database
(a) create
(b) modify
(c) use
(d) work
Answer:
(c) use

Question 19.
Which is used to serve live websites?
(a) WAMP
(b) SAMP
(c) DAMP
(d) TAMP
Answer:
(a) WAMP

Question 20.
How many components of SQL are there?
(a) 3
(b) 4
(c) 5
(d) 6
Answer:
(c) 5

Question 21.
Which among the following is not a WAMP?
(a) PHP
(b) MySQL
(c) DHTML
(d) Apache
Answer:
(c) DHTML

Question 22.
Which is used to define database structure or schema?
(a) DML
(b) DDL
(c) DCL
(d) DQL
Answer:
(b) DDL

Question 23.
Identify which is not a SQL DDL command?
(a) create
(b) delete
(c) drop
(d) truncate
Answer:
(b) delete

Question 24.
Which command changes the structure of the database?
(a) update
(b) alter
(c) change
(d) modify
Answer:
(b) alter

Question 25.
Identify which statement is given wrongly?
(a) DDL statement should specify the proper data type
(b) DDL should not identify the type of data division
(c) DDL may define the range of values
(d) DDL should define the size of the data item
Answer:
(b) DDL should not identify the type of data division

Question 26.
Identify which is wrong?
DML means
(a) Insertion
(b) Retrieval
(c) Modification
(d) alter
Answer:
(d) alter

Question 27.
How many types of DML commands are there?
(a) 1
(b) 2
(c) 3
(d) 4
Answer:
(b) 2

Question 28.
Pick the odd out
Insert, update, alter, delete
Answer:
alter

Question 29.
Grant and Revoke commands comes under ……………………..
(a) DML
(b) DCL
(c) DQL
(d) DDL
Answer:
(b) DCL

Question 30.
…………………….. is a DQL command
(a) select
(b) commit
(c) update
(d) delete
Answer:
(a) select

Question 31.
Which one restores the database to last commit state?
(a) commit
(b) Grant
(c) rollback
(d) save point
Answer:
(c) rollback

Question 32.
Which is used to query or retrieve data from a database?
(a) DQL
(b) DML
(c) DCL
(d) DCM
Answer:
(a) DQL

Question 33.
Variable width character string is given by the data type ……………………….
(a) char
(b) varchar
(c) dec
(d) real
Answer:
(b) varchar

Question 34.
If the precision exceeds 64, then it is
(a) float
(b) real
(c) float
(d) decimal
Answer:
(c) float

Question 35.
…………………… have special meaning in SQL
(a) keywords
(b) commands
(c) clauses
(d) arguments
Answer:
(a) keywords

Question 36.
…………………… are the values given to make the clause complete
Answer:
Arguments

Question 37.
Each table must have at least ………………………. column
(a) 1
(b) 2
(c) 3
(d) 4
Answer:
(a) 1

Question 38.
Which one of the following ensures the accuracy and reliability of the data in the database?
(a) Arguments
(b) constraints
(c) column
(d) clauses
Answer:
(b) constraints

Question 39.
How many types of constraints are there?
(a) 2
(b) 3
(c) 4
(d) 5
Answer:
(c) 4

Question 40.
The …………………….. constraint can be applied only to fields that have also been declared a NOT Null.
Answer:
unique

Question 41.
When two constraints are applied on a single field, it is known as ……………………….. constraints.
Answer:
multiple

Question 42.
Which key helps to uniquely identify the record in the table?
(a) unique
(b) primary
(c) secondary
(d) null
Answer:
(b) primary

Question 43.
Which constraint is used to assign a default value for the field?
(a) unique
(b) primary
(c) secondary
(d) default
Answer:
(d) default

Question 44.
The check constraint may use ………………….. operators for condition.
(a) relational
(b) logical
(c) both
(d) None of these
Answer:
(c) both

Question 45.
When the constraint is applied to a group of fields of the table, then it is ………………………. constraint.
(a) table
(b) column
(c) multiple
(d) primary
Answer:
(a) table

Question 46.
The …………………….. command is used to insert, delete and update rows into the table.
(a) DCL
(b) DML
(c) DTL
(d) TCL
Answer:
(b) DML

Question 47.
If the data is to be added for all columns in a table
(a) specifying column is optional
(b) specifying column is must
Answer:
(a) specifying column is optional

Question 48.
Find the wrong statement from the following delete command
(a) permanently removes one or more records
(b) removes entire row
(c) removes individual fields
(d) deletes the record
Answer:
(c) removes individual fields

Question 49.
The update command specifies the rows to be changed using the …………………….. clause.
(a) where
(b) why
(c) what
(d) how
Answer:
(a) where

Question 50.
Set keyword in update command is used to assign new data.
True / false
Answer:
True

Question 51.
Find the wrong one about alter command
(a) remove a column
(b) remove all columns
(c) rename a column
(d) delete row
Answer:
(d) delete row

Question 52.
The keyword …………………….. is used along with the select command to eliminate duplicate rows in the table.
Answer:
distinct

Question 53.
The ……………………. keyword in select command includes an upper value and a lower value.
Answer:
betweeen

Question 54.
How many types of sorting are there?
(a) 2
(b) 3
(c) 4
(d) 5
Answer:
(a) 2

Question 55.
The default sorting order is ……………………….
Answer:
ascending

Question 56.
……………………. clause is used to filter the records.
Answer:
where

Question 57.
The ……………………… clause is used to select the group of students on rows or columns having identical values.
Answer:
group by

Question 58.
Which is to count the records?
(a) +
(b) *
(c) =
(d) /
Answer:
(b) *

PART – II
II. Answer The Following Questions

Question 1.
Write note on RDBMS?
Answer:
RDBMS stands for Relational DataBase Management System. Orqcle, MySQL, MS SQL Server, IBM DB2 and Microsoft Access are RDBMS packages. RDBMS is a type of DBMS with a row-based table structure that connects related data elements and includes functions related to Create, Read, Update and Delete operations, collectively known as CRUD.

Question 2.
What does data manipulation means?
Answer:
By Data Manipulation we mean,

1. Insertion of new information into the database
2. Retrieval of information stored in a database.
3. Deletion of information from the database.
4. Modification of data stored in the database.

Question 3.
What are the 2 types of DML?
Answer:
The DML is basically of two types:
Procedural DML – Requires a user to specify what data is needed and how to get it. Non-Procedural DML – Requires a user to specify what data is needed without specifying how to get it.

Question 4.
What is meant by data type?
Answer:
The data in a database is stored based on the kind of value stored in it. This is identified as the data type of the data or by assigning each field a data type. All the values in a given field must be of same type.

Question 5.
Write about ALL keyword in select?
Answer:
ALL Keyword
The ALL keyword retains duplicate rows. It will display every row of the table without considering duplicate entries.
SELECT ALL Place FROM Student:
The above command will display all values of place field from every row of the table without considering the duplicate entries.

PART – III
III. Answer The Following Questions

Question 1.
Write note on SQL?
Answer:

1. The Structured Query Language (SQL) is a standard programming language to access and manipulate databases.
2. SQL allows the user to create, retrieve, alter, and transfer information among databases.
3. It is a language designed for managing and accessing data in a Relational Data Base Management System (RDBMS).

Question 2.
What are the various processing skills of SQL?
Answer:
The various processing skills of SQL are :
(i) Data Definition Language (DDL) :
The SQL DDL provides commands for defining relation schemes (structure), deleting relations, creating indexes and modifying relation schemes.

(ii) Data Manipulation Language (DML) :
The SQL DML includes commands to insert, delete, and modify tuples in the database.

(iii) Embedded Data Manipulation Language :
The embedded form of SQL is used in high level programming languages.

(iv) View Defintion :
The SQL also includes commands for defining views of tables.

(v) Authorization :
The SQL includes commands for access rights to relations and views of tables.

(vi) Integrity :
The SQL provides forms for integrity checking using condition.

(vii) Transaction control :
The SQL includes commands for file transactions and control over transaction processing.

Question 3.
How to create and work with database?
Answer:
Creating Database
(i) To create a database, type the following command in the prompt:
CREATE DATABASE database_name;
For example to create a database to store the tables:
CREATE DATABASE stud;

(ii) To work with the database, type the following command.
USE DATABASE;
For example to use the stud database created, give the command
USE stud;

Question 4.
What are the functions performed by DDL?
Answer:
A DDL performs the following functions :

1. It should identify the type of data division such as data item, segment, record and database file.
2. It gives a unique name to each data item type, record type, file type and data base.
3. It should specify the proper data type.
4. It should define the size of the data item.
5. It may define the range of values that a data item may use.
6. It may specify privacy locks for preventing unauthorized data entry.

Question 5.
Name the SQL Commands under TCL. Explain?
Answer:
SQL command which come under Transfer Control Language are:

Question 6.
Write about the parts of SQL Commands?
Answer:
Keywords They have a special meaning in SQL. They are understood as instructions.
Commands They are instructions given by the user to the database also known as statements.
Clauses They begin with a keyword and consist of keyword and argument.
Arguments They are the values given to make the clause complete.

Question 7.
Write note on delete command?
Answer:
DELETE COMMAND
The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row, so no field argument is needed. The DELETE command is used as follows :
DELETE FROM table-name WHERE condition;
For example to delete the record whose admission number is 104 the command is given as follows:
DELETE FROM Student WHERE Admno=104;

The following record is deleted from the Student table.
To delete all the rows of the table, the. command is used as :
DELETE * FROM Student;
The table will be empty now and could be destroyed using the DROP command.

Question 8.
Write note on delete, truncate, drop commands?
Answer:
DELETE, TRUNCATE AND DROP statement:
The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
The TRUNCATE command is used to delete all the rows, the structure remains in the table and free the space containing the table.
The DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back.

Question 9.
Differentiate between and not between?
Answer:
BETWEEN and NOT BETWEEN Keywords
The BETWEEN keyword defies a range of values the record must fall into to make the condition true. The range may include an upper value and a lower value between which the criteria must fall into.
SELECT Admno, Name, Age, Gender FROM Student WHERE Age BETWEEN 18 AND 19;

The NOT BETWEEN is reverse of the BETWEEN operator where the records not satisfying the condition are displayed.
SELECT Admno, Name, Age FROM Student WHERE Age NOT BETWEEN 18 AND 19;

Question 10.
Differentiate IN and NOT IN
Answer:
IN Keyword
The IN keyword is used to specify a list of values which must be matched with the record values. In other words it is used to compare a column with more than one value. It is similar to an OR condition.
For example:
SELECT Admno, Name, Place FROM Student WHERE Place IN (“Chennai, “Delhi ”);

NOT IN: The NOT IN keyword displays only those records that do not match in the list. For example:
SELECT Admno, Name, Place FROM Student WHERE Place NOT IN (“Chennai”, ‘ “Delhi”);
will display students only from places other than “Chennai” and “Delhi”.

Question 11.
Write note on NULL?
Answer:
NULL Value:
The NULL value in a field can be searched in a table using the IS NULL in the WHERE c lause. For example to list all the students whose Age contains no value, the command is used as:
SELECT * FROM Student WHERE Age IS NULL.

Question 12.
Write note on Roll Back?
Answer:
ROLLBACK command
The ROLLBACK command restores the database to the last committed state. It is used with SAVEPOINT command to jump to a particular savepoint location. The syntax for the ROLLBACK command is :
ROLL BACK TO Save point name.

Question 13.
Write note on having clause?
Answer:
HAVING clause:
The HAVING clause can be used along with GROUP BY clause in the SELECT statement to place condition on groups and can include aggregate functions on them. For example to count the number of Male and Female students belonging to Chennai.
SELECT gender, COUNT(*) FROM Student GROUP BY Gender HAVING Place = ‘Chennai’;

The above output shows the number of Male and Female students in Chennai from the table student.

PART – IV
IV. Answer The Following Questions

Question 1.
Write about data type and description?
Answer:

Question 2.
Write about DML Commands?
Answer:
DML COMMANDS
Once the schema or structure of the table is created, values can be added to the table. The DML commands consist of inserting, deleting and updating rows into the table.

(i) INSERT command
The INSERT command helps to add new data to the database or add new records to the table. Syntax:
INSERT INTO <table-name> [column-list] VALUES (values);

(a) INSER T INTO Student (Admno, Name, Gender, Age, Place)
VALUES (100, ‘Ashish ’, ‘M\ 17, ‘Chennai);

(b) INSERT INTO Student (Admno, Name, Gender, Age, Place)
VALUES (10, ‘Adarsh’, ‘M’, 18, ‘Delhi);

(c) INSERT INTO Student VALUES (102, ‘Akshith \ ‘M’, ‘17, ’ ‘Bangalore);
The above command inserts the record into the student table.
To add data to only some columns in a record by specifying the column name and their data, it can be done by:

(d) INSERT INTO Student(Admno, Name, Place) VALUES (103, ‘Ayush’, ‘Delhi’);

(e) INSERT INTO Student (Admno, Name, Place) VALUES (104, ‘Abinandh ‘Chennai); The student table will have the following data:

(ii) DELETE COMMAND
The DELETE command permanently removes one or more records from the table. It removes the entire row, not individual fields of the row, so no field argument is needed. The DELETE command is used as follows:
DELETE FROM table-name WHERE condition;
For example to delete the record whose admission number is 104 the command is given as follows:
DELETE FROM Student WHERE Admno=104;

The following record is deleted from the Student table.
To delete all the rows of the table, the command is used as :
DELETE * FROM Student;
The table will be empty now and could be destroyed using the DROP command.

(iii) UPDATE COMMAND
The UPDATE command updates some or all data values in a database. It can update one or more records in a table. The UPDATE command specifies the rows to be changed using the WHERE clause and the new data using the SET keyword. The command is used as follows: UPDATE <table-name> SET column-name = value, column-name = value,… WHERE condition;
For example to update the following fields:
UPDATE Student SET Age = 20 WHERE Place = “Bangalore ”;
The above command will change the age to 20 for those students whose place is “Bangalore”.
The table will be as updated as below:

To update multiple fields, multiple field assignment can be specified with the SET clause separated by comma. For example to update multiple fields in the Student table, the command is given as:
UPDATE Student SETAge=18, Place = ‘Chennai’ WHERE Admno = 102;

The above command modifies the record in the following way.