Tamilnadu State Board New Syllabus Samacheer Kalvi 12th Computer Science Guide Pdf Chapter 12 Structured Query Language (SQL) Text Book Back Questions and Answers, Notes.
Tamilnadu Samacheer Kalvi 12th Computer Science Solutions Chapter 12 Structured Query Language (SQL)
12th Computer Science Guide Structured Query Language (SQL) Text Book Questions and Answers
I. Choose the best answer (I Marks)
Question 1.
Which commands provide definitions for creating table structure, deleting relations, and modifying relation schemas.
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
II. Answer the following questions (2 Marks)
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:
| Unique Key Constraint | Primary Key Constraint |
| The constraint ensures that no two rows have the same value in the specified columns. | This constraint declares a field as a Primary Key which helps to uniquely identify a record. |
| The UNIQUE constraint can be applied only to fields that have also been declared as NOT NULL. | The Primary Key does not allow NULL values and therefore a field declared as Primary Key must have the NOT NULL constraint. |
Question 3.
Write the difference between table constraint and column constraint?
Answer:
Column constraint:
Column constraints apply only to an individual column.
Table constraint:
Table constraints 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:
Creating a table: CREATE command of DML ( Data Manipulation Language) is used to create a table. Inserting values into tables :
INSERT INTO command of DDL (Data Definition Language) is used to insert values into
the table.
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 an RDBMS.
III. Answer the following questions (3 Marks)
Question 1.
What is a constraint? Write short note on Primary key constraint.
Answer:
Constraint:
- Constraint is a condition applicable on a field or set of fields.
- 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 Constraint:
- Primly Constraint declares a field as a Primary key which helps to uniquely identify a recor<}ll
- Primary Constraint 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.
Question 2.
Write a SQL statement to modify the student table structure by adding a new field.
Answer:
Syntax:
ALTER TABLE < table-name > ADD < column name >< data type >< size > ;
Example:
ALTER TABLE student ADD (age integer (3)).
Question 3.
Write any three DDL commands.
Answer:
a. CREATE TABLE Command
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 the Savepoint command with an example.
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.
Example:
The following is the existing table.
Table 1:
| Admno | Name | Gender | Age | Place |
| 105 | Revathi | F | 19 | Chennai |
| 106 | Devika | F | 19 | Bangalore |
| 103 | Ayush | M | 18 | Delhi |
| 101 | Adarsh | M | 18 | Delhi |
| 104 | Abinandh | M | 18 | Chennai |
INSERT INTO Student VALUES (107, ‘Beena’, ‘F’, 20, ‘Cochin’);
COMMIT;
In the above table if we apply the above command we will get the following table
Table 2:
| Admno | Name | Gender | Age | Place |
| 105 | Revathi | F | 19 | Chennai |
| 106 | Devika | F | 19 | Bangalore |
| 103 | Ayush | M | 18 | Delhi |
| 101 | Adarsh | M | 18 | Delhi |
| 104 | Abinandh | M | 18 | Chennai |
| 107 | Beena | F | 20 | Cochin |
We can give save point using the following command.
UPDATE Student SET Name = ‘Mini’ WHERE Admno=105; SAVEPOINT A;
Table 3:
| Admno | Name | Gender | Age | Place |
| 105 | Mini | F | 19 | Chennai |
| 106 | Devika | F | 19 | Bangalore |
| 103 | Ayush | M | 18 | Delhi |
| 101 | Adarsh | M | 18 | Delhi |
| 104 | Abinandh | M | 18 | Chennai |
| 107 | Beena | F | 20 | Cochin |
INSERT INTO Student VALUES(108, ‘Jisha’, ‘F’, 19, ‘Delhi’); SAVEPOINT B;
Table 4:
| Admno | Name | Gender | Age | Place |
| 105 | Mini | F | 19 | Chennai |
| 106 | Devika | F | 19 | Bangalore |
| 103 | Ayush | M | 18 | Delhi |
| 101 | Adarsh | M | 18 | Delhi |
| 104 | Abinandh | M | 18 | Chennai |
| 107 | Beena | F | 20 | Cochin |
| 108 | Jisha | F | 19 | Delhi |
After giving the rollback command we will get Table 3 again.
ROLLBACK TO A;
Table 3:
| Admno | Name | Gender | Age | Place |
| 105 | Mini | F | 19 | Chennai |
| 106 | Devika | F | 19 | Bangalore |
| 103 | Ayush | M | 18 | Delhi |
| 101 | Adarsh | M | 18 | Delhi |
| 104 | Abinandh | M | 18 | Chennai |
| 107 | Beena | F | 20 | Cochin |
Question 5.
Write a SQL statement using a DISTINCT keyword.
Answer:
The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data.
Example:
SELECT DISTINCT Place FROM Student;
will display the following data as follows :
| Place |
| Chennai |
| Bangalore |
| Delhi |
IV. Answer the following questions (5 Marks)
Question 1.
Write the different types of constraints and their functions.
Answer:
The different type of constraints are:
- Unique Constraint
- Primary Key Constraint
- Default Constraint
- Check Constraint.
- Table Constraint:
1. 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 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).
2. 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 ULI values and therefore a field declared as primary key must have the NOT NULL constraint.
CREATE TABLE Student:
(
Admno integer NOT NULL PRIMARY KEY, → Primary Key constraint
Name char(20)NOT NULL,
Gender char(1),
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.
3. Default Constraint:
- The DEFAULT 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 PRIMARYKEY,
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.
4. Check Constraint:
- Check 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 Students
(
Admno integer NOT NULL PRIMARYKEY,
Name char(20)NOT NULL,
Gender char(l),
Age integer (CHECK< =19), —> CheckConstraint
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.
The check constraint may use relational and logical operators for condition.
Table Constraint:
- When the constraint is applied to a group of fields of the table, it is known as the Table constraint.
- The table constraint is normally given at the end of the table definition.
- For example, we can have a table namely Studentlwith the 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 questions.(i) to (v).
| EMP CODE | NAME | DESIG | PAY | ALLOWANCE |
| S1001 | Hariharan | Supervisor | 29000 | 12000 |
| PI 002 | Shaji | Operator | 10000 | 5500 |
| PI 003 | Prasad | Operator | 12000 | 6500 |
| C1004 | Manjima | Clerk | 8000 | 4500 |
| M1005 | Ratheesh | Mechanic | 20000 | 7000 |
Answer:
i) To display the details of all employees in descending order of pay.
SELECT * FROM Employee ORDER BY PAY DESC;
ii) To display all employees whose allowance is between 5000 and 7000.
SELECT FROM Employee WHERE ALLOWANCE BETWEEN 5000 AND 7000;
iii) To remove the employees who are mechanics.
DELETE FROM Employee WHERE DESIG=’Mechanic’;
iv) To add a new row.
INSERT INTO Employee VALUES(/C1006,/ ‘Ram’, ‘Clerk’,15000, 6500);
v) To display the details of all employees who are operators.
SELECT * FROM Employee WHERE DESIG=’Operator’;
Question 3.
What are the components of SQL? Write the commands in each.
Answer:
Components of SQL:
The various components of SQL are
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Transactional Control Language (TCL)
- Data Control Language (DCL)
Data Definition Language (DDL):
- 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.
- The DDL provides a set of definitions to specify the storage structure and access methods used by the database system.
A DDL performs the following functions:
- It should identify the type of data division such as data item, segment, record and database file.
- It gives a unique name to each data item type, record type, file type, and database.
- It should specify the proper data type.
- It should define the size of the data item.
- It may define the range of values that a data item may use.
- It may specify privacy locks for preventing unauthorized data entry.
SQL commands which come under Data Definition Language are:
| Create | To create tables in the database. |
| Alter | Alters the structure of the database. |
| Drop | Delete tables from the database. |
| Truncate | Remove all records from a table, also release the space occupied by those records. |
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.
- 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.
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 are needed without specifying how to get it.
SQL commands which come under Data Manipulation Language are:
| Insert | Inserts data into a table |
| Update | Updates the existing data within a table |
| Delete | Deletes all records from a table, but not the space occupied by them. |
Data Control Language:
- A Data Control Language (DCL) is a programming language used to control the access of data stored in a database.
- It is used for controlling privileges in the database (Authorization).
- The privileges are required for performing all the database operations such as creating sequences, views of tables etc.
SQL commands which come under Data Control Language are:
| Grant | Grants permission to one or more users to perform specific tasks. |
| Revoke | Withdraws the access permission given by the GRANT statement. |
Transactional Control Language:
- 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.
SQL command which comes under Transfer Control Language are:
| 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. |
Data Query Language:
The Data Query Language consists of commands used to query or retrieve data from a database.
One such SQL command in Data Query Language is
Select: It displays the records from the table.
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.
Table: student
| Admno | Name / | Gender | Age | Place |
| 100 | Ashish | M | 17 | Chennai |
| 101 | Adarsh | M | 18 | Delhi |
| 102 | Akshith | M | 17 | Bangalore |
| 103 | Ayush | M | 18 | Delhi |
| 104 | Abinandh | M | 18 | Chennai |
| 105 | Revathi | F | 19 | Chennai |
| 106 | Devika | F | 19 | Bangalore |
| 107 | Hema | F | 17 | Chennai |
SELECT statement using GROUP BY clause:
GROUP BY clause:
- The GROUP BY clause is used with the SELÆCT 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.
Syntax for the GROUP BY clause:
SELECT < column-names >FROM GROUP BY HAVING condition;
Example:
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:
| Gender |
| M |
| F |
The point to be noted is that only two results have been returned. This is because we only have two gender types ‘Male’ and ‘Female:
- The GROUP BY clause grouped all the ‘M’ students together and returned only a single row for it. It did the same with the ‘F’ students.
- For example, to count the number of male and female students in the student table, the following command is given:
SELECT Gender, count(*) FROM Student GROUP BY Gender;
| Gender | count(*) |
| M | 5 |
| F | 3 |
The GROUP BY applies the aggregate functions independently to a series of groups that are defined by having a field, the value in common. The output of the above SELECT statement gives a count of the number of Male and Female students.
ii) SELECT statement using ORDER BY clause.
ORDER BY clause:
- The ORDER BY clause in SQL is used to sort the data in either ascending or descending based on one or more columns.
- By default ORDER BY sorts the data in ascending order.
- 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 – name1 > ,< column – name2 > , < FROM < table-name > ORDER BY
< column 1 > , < column 2 > ,… ASC | (Pipeline) DESC;
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:
| Admno | Name | Gender | Age | Place |
| 104 | Abinandh | M „ | 18 | Chennai |
| 101 | Adarsh | M | 18 | Delhi |
| 102 | Akshith | M | 17 | Bangalore |
| 100 | Ashish | M | 17 | Chennai |
| Admno | Name | Gender | Age | Place |
| 103 | Ayush | M | 18 | Delhi |
| 106 | Devika | F | 19 | Bangalore |
| 107 | Hema | F | 19 | Chennai |
| 105 | Revathi | F | 19 | Chennai |
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:
Table: emp
CREATE TABLE emp
(
empno integer NOTNULL,
empfname char(20),
emplname char(20),
Designation char(20),
Basicpay integer,
PRIMARY KEY (empfname,emplname) → Table constraint
);
12th Computer Science Guide International Economics Organisations Additional Important Questions and Answers
I. Choose the best answer (1 Mark)
Question 1.
The SQL was called as …………………….. in the early 1970s.
(a) squel
(b) sequel
(c) seqel
(d) squeal
Answer:
(b) sequel
Question 2.
Which of the following language was designed for managing and accessing data in RDBMS?
a) DBMS
b) DDL
c) DML
d) SQL
Answer:
d) SQL
Question 3.
SQL stands for
a) Standard Query Language
b) Secondary Query Language
c) Structural Query Language
d) Standard Question Language
Answer:
c) Structural Query Language
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.
The latest SQL standard as of now is
a) SQL 2008
b) SQL 2009
c) SQL 2018
d) SQL 2.0
Answer:
a) SQL 2008
Question 6.
In database objects, the data in RDBMS is stored
a) Queries
b) Languages
c) Relations
d) Tables
Answer:
d) Tables
Question 7.
DDL expansion is
a) Data Defined Language
b) Data Definition Language
c) Definition Data Language
d) Dictionary Data Language
Answer:
b) Data Definition Language
Question 8.
Identify which is not an RDBMS package …………………….
(a) MySQL
(b) IBMDB2
(c) MS-Access
(d) Php
Answer:
(d) Php
Question 9.
……………. component of SQL includes commands to insert, delete and modify tables in
database
a) DCL
b) DML
c) TCL
d) DDL
Answer:
b) DML
Question 10.
…………………. command removes all records from a table and also release the space occupied by
these records.
a) Drop
b) Truncate
c) ALTER
d) Delete
Answer:
b) Truncate
Question 11.
WAMP stands for
a) Windows, Android, MySQL, PHP
b) Windows, Apache, MySQL, Python
c) Windows, APL, MySQL, PHP
d) Windows, Apache, MySQL, PHP
Answer:
d) Windows, Apache, MySQL, PHP
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.
……………. is a collection of related fields or columns in a table.
a) Attributes
b) SQL
c) Record
d) Relations
Answer:
c) Record
Question 14.
SQL standard recognized only Text and Number data type.
a) ANSI
b) TCL
c) DML
d) DCL
Answer:
a) ANSI
Question 15.
…………………… data type is same as real expect the precision may exceed 64?
a) float
b) real
c) double
d) long real
Answer:
c) double
Question 16.
………………….. column constraint enforces a field to always contain a value.
a) NULL
b) “NOT NULL”
c) YES
d) ALWAYS
Answer:
b) “NOT NULL”
Question 17.
………………… is often used for web development and internal testing.
a) Windows
b) Google
c) WAMP
d) Google Chrome
Answer:
c) WAMP
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.
………………..is not a SOL TCL command.
a) Commit
b) Rollback
c) Revoke
d) Savepoint
Answer:
c) Revoke
Question 20.
………………. provides a set of definitions to specify the storage structure used by the database system.
a) DML
b) DQL
c) DDL
d) TCL
Answer:
c) DDL
Question 21.
Which among the following is not a WAMP?
(a) PHP
(b) MySQL
(c) DHTML
(d) Apache
Answer:
(c) DHTML
Question 22.
…………… command used to create a table.
a) CREATE
b) CREATE TABLE
c) NEW TABLE
d) DDL TABLE
Answer:
b) CREATE TABLE
Question 23.
……………….. keyword is used to sort the records in ascending order.
a) ASCD
b) ASD
c) ASCE
d) ASC
Answer:
d) ASC
Question 24.
Which command changes the structure of the database?
(a) update
(b) alter
(c) change
(d) modify
Answer:
(b) alter
Question 25.
………………… clause is used to divide the table into groups.
a) DIVIDE BY
b) ORDER BY
c) GROUP BY
d) HAVING
Answer:
c) GROUP BY
Question 26.
……………. command is used to gives permission to one or more users to perform specific tasks.
a) GIVE
b) ORDER
c) GRANT
d) WHERE
Answer:
c) GRANT
Question 27.
How many types of DML commands are there?
(a) 1
(b) 2
(c) 3
(d) 4
Answer:
(b) 2
II. Answer the following questions (2 and 3 Marks)
Question 1.
Write a note on RDBMS?
Answer:
RDBMS stands for Relational Database Management System. Oracle, 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 is SQL?
Answer:
- The Structured Query Language (SQL) is a standard programming language to access and manipulate databases.
- SQL allows the user to create, retrieve, alter, and transfer information among databases.
- It is a language designed for managing and accessing data in a Relational Database Management System (RDBMS).
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 are needed without specifying how to get it.
Question 4.
How can you create the database and work with the database?
Answer:
- To create a database, type the following command in the prompt:
- CREATE DATABASE database_name;
- Example; To create a database to store the tables:
CREATE DATABASE stud; - To work with the database, type the following command.
USE DATABASE; - Example: To use the stud database created, give the command USE stud;
Question 5.
Write short notes on WAMP?
Answer:
WAMP stands for “Windows, Apache, MySQL, and PHP” It is often used for web development and internal testing, but may also be used to serve live websites.
Question 6.
Write a SQL statement to create a table for employees having any five fields and create a table constraint for the employee table. (March 2020)
Answer:
Employee Table with Table Constraint:
CREATE TABLE Employee(EmpCode Char(5) NOT NULL, Name Char(20) NOT NULL, Desig Char(1O), Pay float(CHECK>=8000), Allowance float PRIMARY KEY(EmpCode,Name));
Question 7.
Explain DDL commands with suitable examples.
Answer:
DDL commands are
- Create
- Alter
- Drop
- Truncate
Create: To create tables in the database.
Syntax:
CREATE TABLE < table-name >
( < column name >< data type > [ < size > ]
( < column name >< data type > [ < size > ]…
);
Example:
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),
);
Alter:
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. It is used in the following way:
ALTER TABLE ADD
< data type >< size > ;
- To add a new column <<Address>> of type to the Student table, the command is used as
ALTER TABLE Student ADD Address char; - To modify, an existing column of the table, the ALTER TABLE command can be used with MODIFY clause likewise:
ALTER < table-name > MODIFY < data type >< size >;
ALTER TABLE Student MODIFY Address char (25);
Drop: Delete tables from the database. - The DROP TABLE command is used to remove a table 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. But there is a condition for dropping a table; it must be an empty table.
- Remove all the rows of the table using the DELETE command.
- To delete all rows, the command is given as;
DELETE FROM Student; - Once all the rows are deleted, the table can be deleted by the DROP TABLE command in the following way:
DROP TABLE table-name; - Example: To delete the Student table:
DROP TABLE Student; - Truncate: Remove all records from a table, also release the space occupied by those records.
- The TRUNCATE command is used to delete all the rows from the table, the structure remains and space is freed from the table.
- The syntax for the TRUNCATE command is: TRUNCATE TABLE table-name;
Example:
To delete all the records of the student table and delete the table the SQL statement is given as follows:
TRUNCATE TABLE Student;
The table Student is removed and space is freed.
Question 8.
Write a note on SQL?
Answer:
- The Structured Query Language (SQL) is a standard programming language to access and manipulate databases.
- SQL allows the user to create, retrieve, alter, and transfer information among databases.
- It is a language designed for managing and accessing data in a Relational Database Management System (RDBMS).
Question 9.
Write short notes on basic 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 10.
Explain DML commands with suitable examples.
Answer:
i) INSERT:
insert: Inserts data into a table
Syntax:
INSERT INTO < table-name > [column-list] VALUES (values); Example:
INSERT INTO Student VALUES(108, ‘Jisha’, ‘F, 19, ‘Delhi’);
ii) DELETE:
Delete: Deletes all records from a table, but not the space occupied by them. Syntax:
DELETE FROM table-name WHERE condition;
Example:
DELETE FROM Employee WHERE DESIG=’Mechanic’;
iii) UPDATE:
Update: Updates the existing data within a table.
Syntax:
UPDATE < table-name > SET column- name = value, column-name = value,..
WHERE condition;
Example:
UPDATE Student SET Name = ‘Mini’
WHERE Admno=105;
Question 11.
What are the various processing skills of SQL?
Answer:
The various processing skills of SQL are:
- Data Definition Language (DDL): The SQL DDL provides commands for defining relation schemes (structure), deleting relations, creating indexes, and modifying relation schemes.
- Data Manipulation Language (DML): The SQL DML includes commands to insert, delete, and modify tuples in the database.
- Embedded Data Manipulation Language: The embedded form of SQL is used in high-level programming languages.
- View Definition: The SQL also includes commands for defining views of tables.
- Authorization: The SQL includes commands for access rights to relations and views of tables.
- Integrity: SQL provides forms for integrity checking using conditions.
- Transaction control: The SQL includes commands for file transactions and control over transaction processing.
Question 12.
Write short notes on DCL (Data Control Language).
Answer:
Data Control Language (DCL): Data Control Language (DCL) is a programming language used to control the access of data stored in a database.
DCL commands:
- Grant Grants permission to one or more users to perform specific tasks
- Revoke: Withdraws the access permission given by the GRANT statement.
Question 13.
Write short notes on Data Query Language(DQL).
Answer:
- The Data Query Language consists of commands used to query or retrieve data from a database.
- One such SQL command in Data Query Language is ‘1Select” which displays the records from the table.
Question 14.
How will you retain duplicate rows of a table?
Answer:
The ALL keyword retains duplicate rows. It will display every row of the table without considering duplicate entries
SELECT ALL Place FROM Student;
Question 15.
What are the functions performed by DDL?
Answer:
A DDL performs the following functions :
- It should identify the type of data division such as data item, segment, record, and database file.
- It gives a unique name to each data item type, record type, file type, and database.
- It should specify the proper data type.
- It should define the size of the data item.
- It may define the range of values that a data item may use.
- It may specify privacy locks for preventing unauthorized data entry.
Question 16.
Differentiate IN and NOT IN keywords.
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.
NOT IN keyword:
The NOT IN keyword displays only those records that do not match in the list
Question 17.
Explain Primary Key Constraint with suitable examples.
Answer:
- 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 ULI values and therefore a field declared as the primary key must have the NOT NULL constraint.
CREATE TABLE Student:
(
Admno integer
NOT NULL PRIMARYKEY,→
Primary Key constraint
Name char(20)NOT NULL,
Gender char(1),
Age integer,
Place char(10)
);
In the above example, the Admno field has been set as the 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.
Question 18.
Explain GROUP BY and HAVING clauses.
Syntax:
SELECT < column-names > FROM GROUP BY < column-name > HAVING condition;
The HAVING clause can be used along with the GROUP BY clause in the SELECT statement to place conditions on groups and can include aggregate functions on them.
Example:
To count the number of Male and Female students belonging to Chennai. SELECT Gender, count( *) FROM Student GROUP BY Gender HAVING Place = ‘Chennai’,
Question 19.
List the data types used in SQL.
Answer:
Data types used in SQL:
- char (Character)
- varchar
- dec (Decimal)
- Numeric
- int (Integer)
- Small int
- Float
- Real
- double
Question 20.
Write notes on a predetermined set of commands of SQL?
Answer:
A predetermined set of commands of SQL:
The SQL provides a predetermined set of commands like Keywords, Commands, Clauses, and Arguments to work on databases.
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 21.
Explain how to set a primary key for more than one field? Explain with example.
Answer:
Table Constraint:
- When the constraint is applied to a group of fields of the table, it is known as the Table constraint.
- The table constraint is normally given at the end of the table definition.
- For example, we can have a table namely Studentlwith the 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 22.
Explain how to generate queries and retrieve data from the table? Explain?
Answer:
- A query is a command given to get a desired result from the database table.
- The SELECT command is used to query or retrieve data from a table in the database.
- It is used to retrieve a subset of records from one or more tables.
- The SELECT command can be used in various forms:
Syntax:
SELECT < column-list > FROM < table-name >;
- Table-name is the name of the table from which the information is retrieved.
- Column-list includes one or more columns from which data is retrieved.
III. Answer the following questions (5 Marks)
Question 1.
Write the processing skills of SQL.
Answer:
The various processing skills of SQL are:
- Data Definition Language (DDL): The SQL DDL provides commands for defining relation schemas (structure), deleting relations, creating indexes, and modifying relation schemas.
- Data Manipulation Language (DML): The SQL DML includes commands to insert, delete, and modify tuples in the database.
- Embedded Data Manipulation Language: The embedded form of SQL is used in high-level programming languages.
- View Definition: The SQL also includes commands for defining views of tables
- Authorization: The SQL includes commands for access rights to relations and views of tables.
- Integrity: SQL provides forms for integrity checking using conditions.
- Transaction control: The SQL includes commands for file transactions and control over transaction processing.
Question 2.
Explain ALTER command in detail.
Answer:
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. It is used in the following way:
ALTER TABLE ADD < data type >< size >;
To add a new column «Address» of type to the Student table, the command is used as
ALTER TABLE Student ADD Address char;
To modify, an existing column of the table, the ALTER TABLE command can be used with MODIFY clause likewise:
ALTER < table-name > MODIFY < data type>< size>;
ALTER TABLE Student MODIFY Address char (25); The above command will modify the address column of the Student table to now hold 25 characters.
The ALTER command can be used to rename an existing column in the following way:
ALTER < table-name > RENAME oldcolumn- name TO new-column-name;
Example:
- To rename the column Address to City, the command is used as:
ALTER TABLE Student RENAME Address TO City; - The ALTER command can also be used to remove a column .or all columns.
Example:
- To remove a-particular column. the DROP COLUMN is used with the ALTER TABLE to remove a particular field. the command can be used as ALTER DROP COLUMN;
- To remove the column City from the Student table. the command is used as ALTER. TABLE Student DROP COLUMN City;