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 ALL Age FROM student WHERE Age < 18 ORDER BY Age;

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. The primary key constraint declares a field as a Primary key which helps to uniquely identify a record. The primary key is similar to a unique constraint except that only one field of a table can be set as the primary key.

Question 3.
Write the difference between table constraint and column constraint?
Answer:
Table constraint:
Table constraint is applied to a group of fields of the table
Column constraint:
Column constraint can be applied only to individual column

Question 4.
Which component of SQL lets inserts 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.
• MySQL is a database management system, like SQL Server, Oracle, Informix, Postgres.

PART – III
III. Answer The Following Questions

Question 1.
What is a constraint? Write a short note on the 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 a unique constraint except that only one field of a table can be set as the primary key. The primary key does not allow NULL values and therefore a field declared as the 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
Answer:
DELETE:

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

Syntax:
DELETE FROM table-name WHERE condition;

TRUNCATE:
The TRUNCATE command is used to delete all the rows, the structure remains in the table and free the space containing the table.
Syntax:
TRUNCATE TABLE table-name;
DROP:

• The DROP command is used to remove an object from the database.
• After dropping 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.

Syntax:
DROP TABLE table-name;

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 any time 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 the 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 a unique constraint except that only one field of a table can be set as the primary key. The primary key does not allow NULL values and therefore a field declared as the 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)NOT NULL,
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)NOT NULL,
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 conditions.

(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 Student1 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 payment.
2. To display all employees whose allowance is between 5000 and 7000.
3. To remove the employees who are mechanics.
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) consists 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 come 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 come 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) has 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 employees 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 an 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 the 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.
Which of the following is a standard programming language to access and manipulate databases?
a) MySQL
b) SQL
c) PHP
d) Python
Answer:
b) SQL

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

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

Question 4.
SQL originally called as
a) DBMS
b) RDBMS
c) Sequel
d) SQLITE
Answer:
c) Sequel

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.
DML stands for
a) Data Manipulation language
b) Data Meaningful Language
c) Directional Manipulate Language
d) Data Management Language
Answer:
a) Data Manipulation language

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 consists of rows and columns.
Answer:
table

Question 12.
The DQL command ……………….. is used to display all the records from the table.
a) Select
b) display
c) Show
d) Select all
Answer:
a) Select

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

Question 14.
DDL means …………………………
Answer:
Data Definition 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, MySQL, and PHP

Question 18.
Double data type precision may exceed
a) 74
b) 54
c) 14
d) 64
Answer:
d) 64

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.
…………….. command is used to delete all the rows in the table.
a) DELETE ALL FROM tablename
b) DELETE tablename
c) DELETE FROM tablename
d) DELETE * FROM tablename ALL
Answer:
c) DELETE * FROM tablename

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.
………………….. command is used to withdraw the access permission given by the GRANT statement is
a) WITHDRAWN
b) REMOVE
c) DELETE
d) REVOKE
Answer:
d) REVOKE

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.
Pick the odd one
a) Commit
b) Rollback
c) Savepoint
d) Revoke
Answer:
d) Revoke

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) savepoint
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.
The 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 the 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 the select command includes an upper value and a lower value.
Answer:
between

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.
What are the different categories of SQL commands? (March 2020)
Answer:

• DDL – Data Definition Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL – Transaction Control Language
• DQL – Data Query Language

Question 2.
What does data manipulation mean?
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 is RDBMS?
Answer:

• 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.
• Oracle, MySQL, MS SQL Server, IBM DB2, and Microsoft Access are RDBMS packages.

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 the same type.

Question 5.
Write about ALL keywords 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 the place field from every row of the table without considering the duplicate entries.

PART – III
III. Answer The Following Questions

Question 1.
Write short notes on Data Manipulation Language.
Answer:

• A Data Manipulation Language (DML) is a computer programming language used for adding (inserting), removing (deleting), and modifying (updating) data in a database.
• In SQL, the data manipulation language comprises the SQL-data change statements, which modify stored data but not the schema of the database table.
• After the database schema has been specified and the database has been created, the data can be manipulated using a set of procedures which are expressed by DML.

Question 2.
Write short notes on TCL(Transactional Control Language).
Answer:
Transactional Control Language (TCL):

• Transactional Control Language (TCL) commands are used to manage transactions in the database.
• These are used to manage the changes made to the data in a table by DML statements.

TCL commands:

• Commit: Saves any transaction into the database permanently.
• Rollback: Restores the database to the last commit state.
• Savepoint: Temporarily save a transaction so that you can rollback.

Question 3.
How to create and work with a 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.
Differentiate BETWEEN and NOT BETWEEN keywords.
Answer:
BETWEEN:
The BETWEEN keyword defines a range of values the record must fall into to make the condition true.

NOT BETWEEN:
The NOT BETWEEN is reverse of the BETWEEN operator where the records not satisfying the condition are displayed.

Question 5.
Name the SQL Commands under TCL. Explain?
Answer:
SQL command which comes 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 keywords and arguments.
Arguments They are the values given to make the clause complete.

Question 7.
Write a note on the 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 defines 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 a 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 clause. 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 a note on Roll Back?
Answer:
ROLLBACK command
The ROLLBACK command restores the database to the last committed state. It is used with the SAVEPOINT command to jump to a particular savepoint location. The syntax for the ROLLBACK command is :
ROLLBACK TO Savepoint 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 conditions 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.